MySQL plug-in 1.1 for Oracle 10g Grid Control

Posted in: Technical Track

It’s been a while since the MySQL Management Plug-in 0.42 was released. Since then, I quietly updated it to version 1.0. The changes were very few; the biggest news was that the plug-in was certified by Oracle and added to OTN Oracle 10g Grid Control Extensions Exchange (see at the bottom).

I think the next version is due, as a few people have come back to me with some issues. The biggest was compatibility with Windows. Since I used the command line MySQL client, *nix and Windows shell incompatibilities were a major headache to solve, and I still couldn’t make it work reliably. I wanted to use DBI and DBD:MySQL, but it required installing and compiling Perl packages, which makes the deployment process very inconvenient.

Finally, I found a solution — Net::MySQL is a native Perl implementation of the MySQL client. I had to fix some bugs and add a few improvements to it, and I hope to get the author to re-introduce them back to the new CPAN distribution. Net::MySQL is dependent on IO::Socket, which is a core module that comes with the standard Perl distributed with the Oracle Management Agent.

Version 1.1 turned out to be a major rewrite for the Perl collection scripts and the net result is that compatibility across platforms is greatly improved. I have successfully tested the new version on Linux and Windows Agent hosts.

So what’s new in version 1.1 compared to 0.42?

  • certified by Oracle; see OTN Extensions Exchange
  • no MySQL client is required on Agent hosts. The Perl Net::MySQL package is distributed with the plug-in
  • fully compatible with Windows
  • MySQL client path property removed
  • added support for local connection using Unix sockets
  • added connection error message when target is down — can be seen in Availability History
  • commands statistics skips collection for never-executed commands so less data is collected; thus, I could safely increased default collection frequency; command names are formated better
  • changed default collection frequency for network, joins and sort statistics
  • % command executions are collected right now — the “Questions” statistic didn’t match the total of all Com_ statistics.
  • metric “Processes by Action” now excludes the plug-in’s own connection which was always adding one to “Query”
  • a few minor typos fixed

Downloads, requirements, and installation instructions — as well as the datasheet — are available at the MySQL Plug-in for Oracle Grid Control home page.

You can also go directly to the plug-in download page by clicking here:

MySQL Plugin for Grid Control

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

45 Comments. Leave new

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.

Reply
Alex Gorbachev
June 16, 2008 1:57 pm

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

Reply
Sven’s Technik-Blog » Blog Archive » MySQL plug-in for Oracle Grid Control
June 17, 2008 2:00 am

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

Reply
Pascal Chauvier
June 20, 2008 2:59 am

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…)

Reply
Alex Gorbachev
June 20, 2008 8:23 am

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

Reply
Log Buffer #102: a Carnival of the Vanities for DBAs
June 20, 2008 11:38 am

[…] 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 […]

Reply
Tòfol Duran
June 25, 2008 3:52 am

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.

Reply
Alex Gorbachev
June 26, 2008 7:36 am

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

Reply
Tòfol Duran
July 11, 2008 8:38 am

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.

Reply
Alex Gorbachev
July 11, 2008 8:47 am

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

Reply

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”?

Reply
Alex Gorbachev
October 16, 2008 5:33 am

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

Reply

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?

Reply

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.

Reply

Hi Annie.

I’m facing the same problem as you.

Do you find any solution for it?

Our mysql version is:

mysql –version
mysql Ver 14.7 Distrib 4.1.7, for Win95/Win98 (i32)

Regards. HSR

Reply

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???

Reply

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.

Reply

Alex.

I made some changes in Net::MySQL file. The plugin is now working with mysql 4.1.7. The problem was an offset given in the procedure (sub) _get_column_name

original code:

$self->{position} += 9;

change code:

$self->{position} += 5;

regards.

HSR

Reply
Alex Gorbachev
August 18, 2010 3:18 pm

Thanks Hugo.
I had to fix few issues there as well. Can you please reference the lines in the code to be sure it’s the right place? Also, any references on why it was wrong and possibly link to what you use as a reference for the MySQL protocol.

Reply

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

Reply
Alex Gorbachev
March 8, 2009 7:09 pm

@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?

Reply

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

Reply
Alex Gorbachev
March 26, 2009 7:57 pm

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

Reply

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.

Reply
Ivan Saez Scheihing
April 7, 2009 1:04 am

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

Reply
Alex Gorbachev
April 7, 2009 8:40 pm

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

Cheers,
Alex

Reply
Ivan Saez Scheihing
April 8, 2009 8:52 am

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

Reply
Ivan Saez Scheihing
April 9, 2009 1:49 am

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

Reply
Alex Gorbachev
April 9, 2009 1:22 am

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.

Reply
Alex Gorbachev
April 13, 2009 8:10 am

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

Reply
Ivan Saez Scheihing
April 14, 2009 3:18 am

Alex,

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

regards,

Ivan

Reply
Alex Gorbachev
April 16, 2009 12:51 am

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

Reply
Ivan Saez Scheihing
April 16, 2009 6:13 am

Thanks Alex.

regards,

Ivan

Reply

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.

Reply
Sebastian Gentil
July 21, 2009 8:31 am

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: (http://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

Reply

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

Reply
Alex Gorbachev
August 28, 2009 2:44 am

Tan,

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

Alex

Reply
Alex Gorbachev
August 28, 2009 2:48 am

@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.

Reply

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.

Reply
Robin Anderson
December 2, 2009 4:28 pm

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.

Reply
Robin Anderson
December 2, 2009 4:42 pm

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

Robin

Reply
Alex Gorbachev
December 8, 2009 3:36 pm

@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.

Reply

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.

Reply
Alex Gorbachev
August 18, 2010 3:21 pm

leo5511, I can’t read Spanish. Sorry.

Reply

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

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *