MySQL plug-in 1.1 for Oracle 10g Grid Control

Jun 16, 2008 / By Alex Gorbachev

Tags: , ,

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

45 Responses to “MySQL plug-in 1.1 for Oracle 10g Grid Control”

  • Doug Burns says:

    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.

  • Sven’s Technik-Blog » Blog Archive » MySQL plug-in for Oracle Grid Control says:

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

  • Pascal Chauvier says:

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

  • Tòfol Duran says:

    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

  • Tòfol Duran says:

    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

  • Claus Gehner says:

    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

  • Viljo Hakala says:

    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?

  • Annie says:

    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.

    • Hugo says:

      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

  • kahye says:

    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.

    • Hugo says:

      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

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

  • Ashok says:

    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?

  • Brandon Cole says:

    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

  • Alex Gorbachev says:

    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

  • Brandon Cole says:

    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.

  • Ivan Saez Scheihing says:

    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

  • Alex Gorbachev says:

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

    Cheers,
    Alex

  • Ivan Saez Scheihing says:

    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

  • Ivan Saez Scheihing says:

    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

  • Alex Gorbachev says:

    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.

  • Alex Gorbachev says:

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

  • Ivan Saez Scheihing says:

    Alex,

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

    regards,

    Ivan

  • Alex Gorbachev says:

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

  • Ivan Saez Scheihing says:

    Thanks Alex.

    regards,

    Ivan

  • Eliomar says:

    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.

  • Sebastian Gentil says:

    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

  • Tan says:

    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

  • Alex Gorbachev says:

    Tan,

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

    Alex

  • Alex Gorbachev says:

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

  • John Danilson says:

    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.

  • Robin Anderson says:

    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.

  • Robin Anderson says:

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

    Robin

  • Alex Gorbachev says:

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

  • leo5511 says:

    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.

  • jd says:

    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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>