MySQL plug-in 1.1 for Oracle 10g Grid Control

That’s cool stuff, Alex. I downloaded it the other week and hopefully we can try it in the next week or two. I’ll let you know how it goes.

Thanks Doug. Let me know the results. I’ve got few confirmations by email already.

[…] now an official plug-in is available, created by Alex Gorbachev (Pythian Group). It can be downloaded from the “Oracle […]

Cool Plug-in, deployment easy, reports very nice.
All is running well. (Only an little error in Executions history graph, perhaps it’s my confirguration…)

Pascal,

Thanks for the feedback. There was an issue with this graph in case you are upgrading from previous version because execution names changed (I replaced “_” with ” ” for readability). However, I fixed that by introducing REPLACE in the SQL statement for the report.

Could you give me the error you see in the graph and also the errors in emoms.log from $OMS_HOME/sysman/logs.

Thanks,
Alex

[…] the worlds of MySQL and Oracle was Alex Gorbachev, who released the MySQL plug-in 1.1 for Oracle 10g Grid Control, with a bunch of new features, and good reviews […]

Dear Alex,
I have downloaded the pluggin and I have installed it on a Solaris 10 Sparc plataform.

The problems is that I only can modify the “collection schedule” on the metrics “response time” and “status”. The other metrics have the “collection schedule” value set “Real-time Only”.

It is correct? Do you think that it is a mistake of configuration?

Thank you very much in advance.

Tòfol,

Thanks for you feedback.

What you see is wrong — you should be able to modify collection schedule easily. Can you give me a bit more info?
– How do you chance collection schedule? Give me detailed instructions please so I can try to reproduce.
– Have you upgraded from a previous version of plug-in?

Please check whether you have in the following file:
$OMS_HOME/system/admin/default_collection/mysql.xml

Is there only “Response” metric or are there other metrics as well?

Alex

Thank you for attending to myself and you forgive for the delay,
it’s the first time that I install the plug-in. I have searched in the directory $OMS_HOME/sysman/admin/default_collection and I don’t find the file mysql.xml.

I have tried to change the collection schedule, but I can’t.

Thank you very much.

Tòfol.

Tòfol,

There should be nothing in $OMS_HOME.
When you install the plug-in and *deploy* it to the agent, the files are deployed on the agent side in $OMS_HOME/sysman/admin/… ($OMS_HOME/{hostname}/sysman/admin/default_collection if it’s cluster agent).

To change the collection frequency, you should not touch default collection XML file. Instead, you can use Grid Control GUI for the target to change collection frequency there.

Hope this helps,
Alex

I downloaded a trial version of the MySQL from Grid Control Plug-in.
Installation and configuration went flawlessly.
I am now trying to configure alerts to match those we currently have using MySQL Enterprise.
An initial question is:
In Setting thresholds for “Slave Statistics”, I would look for a statistic on Seconds_Behind_Master, which I don’t find.
How would one define an alert for “Slave out of synch”?

Hi Claus,
As I already mentioned in the email, there is only one version of plug-in and it’s free as well as best efforts community support here.
Monitoring slave up to date is still in the TODO list. Thanks for drawing my attention to it. If you see any other missing features or have some ideas, please let me know.
Thanks,
Alex

Hi,

We have been using your MySQL Plugin for GC for some time
now. Thanks for it.

However, are you planning to implement database
size monitoring or do you feel it should be implemented
via filesystem monitoring?

Would be nice to see the size of the different databases
in MySQL.

How about implementing user defined metrics?

Recently, I installed mysql plug-in for grid in our system.
But it doesn’t work.

First error, I get the “Time of authentication”.
because of Out system don’t have Net::MySQL module.
So, I installed it. then connect success.

and then,
we get the another message when the metric collect execute the mysql_response.xml
(follow sentence :
my $result = $mysql->create_record_iterator->each->[0];
# Metric collection error is returned string is wrong
die “em_error=$select returned ‘$result’. Expected ‘alive'” if ( $result ne ‘alive’ );)

so, i execute the debug mode.
packet information include “weird” character(like broken character)

whole packet
= ^A^@^@^A^A^[^@^@^B^Cdef^@^@^@^Ealive^@^L!^@^O^@^@^@?[1m^A^@^_^@^@^A^@^@^C?[1m^F^@^@^D^Ealive^E^@^@^E?[1m^@^@^B^@]

normal 16 digit
= ^A^@^@^A^A^[^@^@^B^Cdef^@^@^@
01 00 00 01 01 1B 00 00 02 03 64 65 66 00 00 00

weird 16 digit
= ^Ealive^@^L!^@^O^@^@^@?[1m^A
05 61 6C 69 76 65 00 0C 21 00 0F 00 00 00 FE 01

thnak you.

I success to install MySSQL Plugin
but, when i see the “commands and executions” , I return the error message “invalid number”

The query is
with a as (select replace(key_value,’_’,’ ‘) k, collection_timestamp t, value*60 v from mgmt$metric_details
WHERE target_guid = :1
and metric_name = ‘commands’
and metric_column = ‘execs’
and key_value ‘Questions’
and COLLECTION_TIMESTAMP between NVL(:2,SYSDATE-1) and NVL(:3,SYSDATE)),
b as (select distinct replace(key_value,’_’,’ ‘) k from mgmt$metric_details
WHERE target_guid = :4
and metric_name = ‘commands’
and metric_column = ‘execs_pct’
and key_value ‘Questions’
and COLLECTION_TIMESTAMP between NVL(:5,SYSDATE-1) and NVL(:6,SYSDATE)
and value >=1)
select k, t, v from (
select k, t, round(v,1) v, 1 s from a where k in (select * from b)
union all
select ‘others’, t, round(sum(v),1), 2 from a where k not in (select * from b) group by t
) order by t, s, k

“value” column is varchar2(4000) type
but, that query is number type.

How come???

Sorry for delayed feedback…

@Annie: Net::MySQL is native implementation and .pm file is shipped with the MySQL plug-in — you don’t need to install anything. Plus, it uses the Perl shipped with Oracle Agent anyway. What’s your MySQL version? I got couple reports of issues with earlier version like 4.0 and need to verify them.

@kahye: “value” is the varchar2 and is implicitly transformed to number (I should have added explicit transformation – will do that). I had this issue once when upgrading from previous versions but I caught it and fixed it at that time. I think the easiest way would be to wait until that invalid info is purged or delete and add target again. Perhaps, there was an error during collection but I couldn’t understand how it may happen (collection itself would get an error). Did you solve this issue by any chance.

I tried to install and configure the plug-in with OMS 10.2.0.4 and mysql 4.0x but running into issues. Is this supported for mysql 4.0.x and 4.1x or should i be on mysql 5.x?

thanks
Ashok

@Ashok: I think there could be an issue with 4.0 in the way authentication is done. It might be that Net::MySQL doesn’t support it. I plan to verify it shortly.
To confirm, what’s the symptoms? Can you see if you have timeouts of metric collection in agent trace/log files and some hanging perl processes on the host?

We’ve installed the plugin on OMS 10.2.0.5 successfully. The MySQL server we are attempting to manage is v4.0 and we are having issues. I believe it may be due to there not being a “show global status” command in 4.0.

The error we’re getting is:
Type: MySQL Server
Metric: Table Locks
Error Type: Collection Failure
Message: Metric execution timed out in 600 seconds

Brandon,

Thanks for your feedback. I believe it’s an issue with 4.0 authentication that native Perl package (Net::MySQL) has a problem with.

show global status will turn into show status for 4.0 and 4.1 version. Your problem has been reported by others on MySQL 4.0. I’ll follow up shortly and see if we can fix it.

Authentication mechanism has changed since then to a more secure one. It might be that Net::MySQL doesn’t support legacy authentication.

Alex

FYI
ps -ef | grep agent | wc -l
161

Whatever errors the plugin is giving us right now has completely taken our grid control server out of operation. We will have to remove the plugin for now to get everything back up.

Hi,

At this moment I’ve version 1.0.1 of the Mysql plugin installed. If I install version 1.1 wil it upgrade or do I have to uninstall the previous version first?

regards,

Ivan

Ivan, import version 1.1 and then deploy it on the agents with version 1.0.1 – upgrade will happen automatically.

Cheers,
Alex

Alex,

Thank you for the answer. I’ve upgraded successfully the plugin. What are the main differences between version 1.0.1 and 1.1?

regards,

Ivan

Alex,

The use of Net::Mysql makes the code more readable. Good job!
I’ve still a lot of :

090409 7:43:18 [Warning] Aborted connection 128901 to db: ‘unconnected’ user: ‘
oem_agent’ host: ‘localhost’ (Got an error reading communication packets)

messages. Any idea how to get rid of them?

regards,

Ivan

Major rewrite internally and removing dependency from mysql client on the agent host – Net::MySQL is used instead.

Few bug s fixed. See the post for new features from 0.42 but most were already there for 1.0.1.

There are also history updates in xml and perl source code.

Hm… looks like some connections are not closed properly. Do you see any hanging perl processes by any chance?

Alex,

I do’nt see hanging perk processes. I’ve defined log-warnings=2 in my.cnf so those warnings are shown.

regards,

Ivan

Thanks Ivan. I’ll try to reproduce it one day.

Thanks Alex.

regards,

Ivan

Good Days, Alex Gorbachev

I install the last plug-in in a windows server and I can´t connect to the Mysql server it says:

“em_error=Access denied for user: ‘@localhost’ (Using password: NO)”

It’s like it doesn’t recognize the username and password.

I checked at the target.xml and they where there.

Thank you.

Hi Alex,
is there anything new concerning the “Aborted connection” problem?
I need the option “log-warnings=2” for my MySQL database but these error messages (every minute) are pretty annoying :-(
From MySQL documenation: (https://dev.mysql.com/doc/refman/5.1/en/communication-errors.html) … The client program did not call mysql_close() before exiting …
Seems the connection to check the status of the database is not ended correctly.

Thanks for any advise
Sebastian Gentil

I have installed the plugin and it seems to be working fine.

However whenever there is an alert I can see it in the Grid control but do not receive email.

Yes I checked email server, permissions etc, but still the same thing. did you encounter this

Tan,

This is not a plug-in issues. You need to setup email notification in Grid Control properly.

Alex

@Eliomar: must be an issues with your user creation. try to reproduce from command-line – easier to fix this way.

@Sebastian: yes – the hot-fix release is coming in a matter of few days.

Alex, we are very interested in deploying this plug in but it appears the download file from pythian is broken. We’ve tried multiple times to download an in all cases the resulting zip file seems corrupt or zero length. Any help would be appreciated. Feel free to contact me for the download link if you want to test it out.

Alex,
I installed the plug-in in our 10.2.0.5 OMS and successfully deployed it to 2 oracle agents (both running 10.2.0.5). One agent is on AIX 5.3 and the other is on OEL 5.

However, the metric collection for a remote mysql server fails and I don’t know how to test this from the command line.

I entered the following information when adding the mysql server to the agent:
target: devlamp1
hostname: devlamp1.jeffco.k12.co.us (also tried ip address)
database user: oraagt (created this user in the mysql database with SUPER priv)
database user password: xxxxx

The metric collection error is:
Target devlamp1
Type MySQL Server
Metric Response
Collection Timestamp Dec 2, 2009 2:11:41 PM
Error Type Collection Failure
Message em_error=#28000Access denied for user ‘oraagt’@’rnd20.jeffco.k12.co.us’ (using password: YES) at /lvexe/oracle/product/10.2.0/agent10g/sysman/admin/scripts/emx/mysql/mysql_oem.pm line 45

The ‘rnd20’ server is the server running the oracle agent.

Any help would be greatly appreciated.

Thanks.

Alex,
One more thing…our version of MySQL is:
Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu)

Robin

@Robin: Unlike Oracle, MySQL login is a pair of username itself and client machine (IP/alias/pattern).

You could create user ‘oraagt’@’rnd20.jeffco.k12.co.us’ (preferred) or ‘oraagt’@’%’ that would work for any client host.

hola alex, soy nueva en este tema pero ya he implementado el plugin 1.1.1 con exito, puedo ver ya el servidor mysql pero en la pestaña de reports al ver informes me sale la opcion de comamds and executions ingreso a esta pero solo me sale el grafico current executions celeste y a lado un cuadro del mismo color que dice others.
el grafico de historial tambien solo presenta un solo color, mi pregunta es que debo hacer para que me salga todo lo que se realiza select, insert, begin, update, etcetera, tal como se muestra en el grafico de esta pagina. espero que me puedas ayudar con esto.

leo5511, I can’t read Spanish. Sorry.

looks good. thanks for the work.

When you get back to this, working on a way to monitor the slave status would be cool. Probably some would like to compare lag to some number but I’d be happy with a binary: yes, it’s working or no, one of the slave threads is down.

anyway, thanks for the work.

jd

Hi Alex,

Sorry for asking a question via a ‘post comment’ but I did not find where to start a new thread. My question is: I have OMS 13c and the Mysql plugin version 12.1.0.4 and I’m getting lots of aborted connections; one every minute. Do I need to log a tar on Metalink for this issue or do you support version 12.1.0.4?

regards,

Ivan