Release 1.1.1 of MySQL Plug-in for Oracle Enterprise Manager

Sep 11, 2009 / By Alex Gorbachev

Tags: , ,

Update 30-Aug-2012: the first 12c plug version is released.

I have just released a new version of the MySQL plug-in for Oracle Enterprise Manager — MySQL plug-in 1.1.1. This is a long overdue bug fix release.

There are no new features implemented (we have another branch in development) but just fixed number of fairly annoying bugs that I was finally able to reproduce.

The download link is on the plug-in’s home page where you can also find a data-sheet and installation guide.

Here are the changes in the 1.1.1 release:

  • Tested with Oracle Enterprise Manager Grid Control 10g Release 5 (10.2.0.5)
  • Fixed the bug with connections not closed properly
  • Fixed bug that caused collection to hang and time-out (Net::MySQL bug — not recognizing a final packet in result-set)
  • Fixed bug that caused collection processes to spin on CPU (Net::MySQL bug when zero length packet returned from the socket — very weird why it happens)
  • Fixed few bugs in Commands and Executions report – graphs produced errors from time to time
  • Removed columns Compression and Tc_log_% in Others metric
  • Changed metric Opened_tables into ratio per second

I have tested it on Linux and Windows with MySQL 5.0 and 5.1. Please do post here in the comments to confirm that it works on your release and provide the following info:

  • Oracle Grid Control Server (OMS) version
  • Oracle Agent version
  • Operating Sysytem and version
  • MySQL version and the details of the build
  • Do you monitor MySQL instance running locally (on the same host as Oracle Agent) or remotely

This will help the whole community and confirm that there are no platform/version specific issues. I will take care of summarizing your comments — don’t be afraid to duplicate the info. Thanks!

Any issues please report here as usual.

58 Responses to “Release 1.1.1 of MySQL Plug-in for Oracle Enterprise Manager”

  • Tam Kyle says:

    Alex, quick question – when monitoring Sql server End to End from Oracle Grid Control you need to have the System Monitoring Plug-in for Hosts and the SQL Server plug-in for Non Oracle Databases in order to get both physical infrastructure and database monitoring. Does this also apply to your MySQL plug in, ie does it only cover the MySQL database and require us to have a separate (chargeable) Host monitoring license ?

    Best Regards,

    Tam

  • Jason Zormeier says:

    Currently on version 1.1 of the mysql plugin. in the monitoring configuration when the path to the socket is used. port filled out (and not as well) – using port 3306 and using a valid username / password the mysql database shows down. If a host is put in the mysql db shows up using tcp / port. (socket would be nice to use) any ideas?

  • Alex Gorbachev says:

    @Tam: You don’t have to use System Monitoring Plug-in for Hosts but you would loose pretty much all useful host monitoring info. See licensing guide here. Also see Linux Management Pack. The latter is available for free if you have basic support for Oracle Enterprise Linux (which is very little).

  • Alex Gorbachev says:

    @Jason: I got lost in your explanation… Sorry. Could you clearly mark two cases when it works and when it doesn’t? The logic is the following:

    If “Hostname” property is defined then TCP is used. Otherwise, socket is used on the local host. So if hostname is not defined, port doesn’t make any difference as socket is used anyway.

    Installation instructions have more details.

  • Jason Zormeier says:

    Alex : thank you for the follow up, the issue i was talking about was using the socket only on version 1.1 with a username / password did not appear to be working when firewall rules where enabled. (I discovered that the target was failed over to another agent and this was the real issue) agent A in grid was monitoring when mysql was running on agent b. Sorry for the confusion.

  • Alex Gorbachev says:

    @Jason: I have tested Unix socket it was working for me. I’ll try again and you know.

  • Heng Zhang says:

    Hi Alex,
    our team use mysql plug-in to add mysql 5 instances to the grid control. we have about 500 server instance running. but the web interface only allows me to add one instance a time. is there some way for us to script and automate the adding process?

    thanks
    Heng

  • Alex Gorbachev says:

    @Heng: We have these requirements for number of our clients and we use emcli – command-line interface.

  • Robin Anderson says:

    Alex,
    Please ignore my previous post regarding connectivity issues during the metric collection. I’m new to MySQL and the problem was caused by the value I set for the host column in mysql.user when creating the user. I changed the host field to ‘%’ and OEM is now able to connect to the MySQL server for metric collection.

    Thanks,
    Robin

  • When viewing the Executions History report I was getting an ORA-01722. This was due to an implicit cast to number on the value field when selecting from mgmt$metric_details.

    This patch seems to fix it.

    --- old/12	2009-08-26 06:58:38.000000000 -0400
    +++ new/12	2010-03-11 07:45:08.000000000 -0500
    @@ -178,7 +178,7 @@
              and metric_column = ''execs_pct''
              and key_value  ''Questions''
              and COLLECTION_TIMESTAMP between NVL('||l_dqm||'EMIP_BIND_START_DATE'||l_dqm||',SYSDATE-1) and NVL('||l_dqm||'EMIP_BIND_END_DATE'||l_dqm||',SYSDATE)
    -         and value >=1
    +	  and to_number( DECODE(REPLACE(TRANSLATE(value, ''.,0123456789'', ''111111111111''), ''1'', ''''), NULL, value, ''0'')) >=1
                union all
                select ''others'' from dual),
          a as (select decode(b.k,null,''others'',key_value) k, collection_timestamp t, value*60 v from mgmt$metric_details, b
    
  • Thanks Richard. Looks like a non numerical got collected. Thanks for the fix. I’ll include that in the next release (and maybe roll out in other reports as well).

    Could you check what is the value of the column? Wanted to see how a non numerical value got there in the first place.

    Thanks,
    Alex

  • You might want to also add a ‘-’ in the list of values to translate to account for negative numbers.

    I see various non-numeric values such as:
    UP
    DOWN
    unlimited

    SYS

  • @Richard: this is odd. What’s you OS and MySQL version?

  • It’s not mysql values that have non-numeric data. It’s data from other target types. Unfortunately Oracle is applying the ‘value >=1′ predicate before the other predicates in the query.

  • Oh, boomer! Good point. Thx!

  • Jeremy Bowland says:

    Hi Alex,

    I have just installed MySQL onto windows2008 server, I’ve managed to install the agent onto OEM and deploy the plugin as instructed in the guidance. I seem to be having issues when I try and register the plugin as a target through the agent. I keep getting a message from the metric response saying em_error=Timeout of authentication. Any thoughts or advice would help.

    • @Jeremy: What’s your MySQL version? There was an issue with older version because “Net::MySQL” had bugs in the protocol but that should have been fixed. Anything special is configured for authentication? Can you connect to MySQL from the command line?

  • Jeremy Bowland says:

    The version I am using is 5.1.36. The agent can connect to MySQL but for some reason the plug in won’t register on OEM

    • I didn’t test on w2k8 but I don’t see how it would impact it.
      How do you know that the Agent can connect to MySQL?

      What I would do is to use connection over network using hostname, port and username+password from the command line (use mysql command line tool).

      If that works, then it should work from the agent. Just make sure you use hostname and port when adding the target in EM GUI.

  • Brett M says:

    Hi i am having this problem below any ideas?

    Metric Response
    Error Type Collection Failure
    Message em_error=MySQL error #42S22Unknown column ‘alive’ in ‘field list’ at /u01/app/oracle/product/agent10g/sysman/admin/scripts/emx/mysql/mysql_response.pl line 49.

  • Brett M says:

    The MySQL version is 5.1.47 community, The main db is a slave in a replication if that makes a difference

  • Brett M says:

    Thanks for your help that was the problem i had ‘ANSI’ specified in my my.cnf, i have removed this and its working.

    I did some tests

    select ‘alive’; Works
    select “alive”; Doesnt Work

    SHOW SESSION VARIABLES LIKE ‘sql_mode’; ‘
    sql_mode’, ‘REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI’

    SET SESSION SQL_MODE=”;

    select ‘alive’; Works
    select “alive”; Works

  • David C. says:

    I have just installed MySQL 5.0.51a onto “Red Hat Enterprise Linux Server release 5.3″ server. I’ve Oracle manage agent 10.2.0.5 and OMR 10.2.0.5 installed, and runnung on the same server. Mysql plug-in 1.1.1 deployed and Mysql agent added/running O.K as the installations in the guidance. Unfortunally, I won’t be able to see monitored Mysql database displayed on target databases on grid control console web page. Is any other step I missed?

  • Padma says:

    i’ve recently downloaded mysql plugin to monitor mysql services. The plugin works fine when the mysql services goes down, but when we test “Sql running Status” metrics setting the status to “No” in critical threshhold, it doesn’t send any email alert when the slave is shutdown. I would like to know what would be value that should be set in critical and warning threshhold in order to generate an alert. Please let me know asap

  • raj says:

    HI

    I have deployed your plugin and it errors me with slave off issue saying slave is off but the slave in fact is ON. Is this a known bug with plugin.

    thanks
    Raj

  • Ali Zaidi says:

    Alex:
    Are there plans to release this plugin for OEM 11g?

    thx

  • Ali,

    It actually works with 11g EM as reported by many users and Oracle conformed there is no update needed.

    Alex

  • Scott Crouch says:

    Alex:
    My setup:
    mysql Ver 14.14 Distrib 5.5.8, for Linux (x86_64) using EditLine wrapper
    Oracle Enterprise Manager 10.2.0.5, OEM Agent 11.1.0.1

    OEM Agent is local to the MySQL database, but owned by a different OS user (oracle for OEM, mysql for MySQL) I also have a user in MySQL named DBSNMP, with PROCESS privs.

    Deployment of the plug-in went very smoothly. However, I am getting the following error when I add a MySQL Server target.
    em_error=#08S01Bad handshake at /ora01/app/oracle/product/agent11g/sysman/admin/scripts/emx/mysql/mysql_oem.pm line 45

    I am providing the Sock, the Host, the port, and the DBSNMP user and password. (sock file is not in a standard place)

    From either the Oracle account or the MySQL account I can connect and query the required statements with no issues.

    I tried to figure this out on my own, but at this point I’m flummoxed. Help?

    • Scott, I’ve had few reports of a problem with MySQL 5.5.8 as the wire protocol of MySQL seems to be changed there. Because, I’m using native Net::MySQL Perl module, it’s not up to date and I need to look into fixing it. I haven’t had time to do that yet.

  • John says:

    Alex, we’ve run into the same issue deploying our first 5.1.58 version of mysql. Prior to this we were pretty much all 5.0.x. Is there any update on this?

  • John, can you clarify? Are you getting “em_error=#08S01Bad handshake”?

  • Bernd says:

    Alex,

    I’m getting the same error as John since I upgraded MySQL from 5.0 to 5.5. Is there any workaround or fix ?

  • Ron says:

    I’m getting the same error as John re: bad handshake.

  • BC says:

    After upgrading from 5.1.54 to 5.1.59 I’m getting the error as above

    em_error=#08S01Bad handshake at /u01/app/oracle/product/agent11g/sysman/admin/scripts/emx/mysql/mysql_oem.pm line 45

    GC 11.1 and 11.1 agent (setup pretty much as described by Alex above).

    Is there a fix coming for this issue or a workaround?

    Also as GC12 is now out is a new version of the plugin required for that?

    Thanks.

  • BC, yes. That’s has become a problem due to wire protocol change. It’s also affects mysql 5.5.12+ I believe. Working on the fix but I’m under lots of other pressing things so don’t have firm ETA right now.

    12c – yes. I looked into it and will need to convert the plugin to be compatible with 12c. Luckily, there are still very few users getting to production with it right now. I have already assessed what needs to be done. It’s not that complicated but I’d focust on Bad Handshake problem first.

  • W. Scott Morrison says:

    Are there any current options with the plug-in for scheduling MySQL backups using GC 10.2 ? We use GC to schedule Oracle backups with RMAN and TSM/TDP and would like to integrate MySQL also.
    Thanks,

    • Currently, you can schedule a host job for the host that’s running MySQL (assuming that you have a local EM Agent deployed on it) – this job would then run MySQL backup script. However, it would be associated with the host target and not MySQL target.
      Would that work for you?

  • W. Scott Morrison says:

    ok, I see what you are saying. That would be creating an OS command job within GC. I will test and let you know.
    Thanks,

  • Arabinda says:

    Hi Alex,
    We are using the plugin extensively for all our mysql monitoring. If you can update the plugin it to monitor the latest release of mysql that will be great.

    Thanks.

  • Dave says:

    Any news on the em_error=#08S01Bad handshake error with the plug in? Has anyone been able to get this working or find a work around? We’re running mysql 5.5.15 with Oracle Grid 11g.

  • PerYcut says:

    Hy Alex
    Maybe any news about the em_error=#08S01Bad handshake error ?

    thanks

  • PerYcut says:

    Em 11g shows Mysql Database is up . but in alerts: Couldn’t connect to xxxxx:3303/tcp: IO::Socket::INET: connect: Connection refused.
    Any Ideas

    Thanks

    • PerYcut: not sure what’s your problem? If the plugin reports OK in GUI then you should have no problems. Not sure which “alerts” you are referring too. Please provide more details on what problem you are solving.

  • Huge news – just found the bug in Net::MySQL fixing Bad handshake error! There is a bit that shows whether database name is passed on connect and even though that bit is set, older versions of MySQL didn’t complain if database name is omitted. As of 5.5.12 – that’s not the case so at least empty string is shared.

    Long story short – will be release the new fixed version fairly soon now. Oh… and 12c Cloud Control version is coming our as well!

    Stay tuned.

  • Pete says:

    Trying the agent, and think that I may have a quick fix to the em_error=#08S01Bad handshake error.

    In the agent home, just add the following to sysman/admin/scripts/emx/mysql/mysql_oem.pm
    Add the following line at line 50:
    database => “INFORMATION_SCHEMA”,

    This appears to solve the problem with not specifying the database when connecting.

    Looking forward to trying this with 12c as well. Thanks.

  • Ashok Kondala says:

    Hi Alex:

    I downloaded the 1.1.2 version as i was getting the em_error=#28000Access denied for user ‘oracle’@'dboradev106.blue.ygrid.yahoo.com’ (using password: YES) at /ora/app/oracle/product/agent10g/sysman/admin/scripts/emx/mysql/mysql_oem.pm line 45 but i still getting the same error with 1.1.1 and 1.1.2 ysql plug-in versions.

    This is mysql version 5.1.58.0 and am on 10.2.0.5 version on RHEL 5u4.

    Could you please let me know if any work around or anything i’m missing?

    Regards,
    Ashok

    • Your error message just indicates that you didn’t create MySQL user properly. In MySQL, it’s not enough to simply specify a username – login also includes IP/hostname that user is connecting from. Your MySQL Admin should be able to help you easily on this.

      You can simply reproduce it by logging in from command line using the same connection settings and user.

  • Ashok Kondala says:

    Sorry, i meant to say – the handshake error with 1.1.1 is not showing up anymore with 1.1.2 versions. For the new 1.1.2 versions, it is giving me the error – the em_error=#28000Access denied for user ‘oracle’@’’ (using password: YES)

  • Neel says:

    Hi Alex,

    I was tesing the alert mechanism ,i killed the mysql to see whether i receive alert or not. But unfortunaley EM is continiously showing up below error and not sending the alert

    Do i need to check some configuration missing or plugin currently can not handle such events ?

    Error During Unhandled Error: An unexpected error has occurred while processing the activity MetaDataPageActivityDef[homePg] “Home”. The component mpDefault0…BarChart261 has reported the error: faultCode:DataServiceErr faultString:’Error in metric data service’ faultDetail:’MetricDataService:getRealtimeData:Error getting metric data from agent for neel1-hs01.net:mysql: oracle.sysman.emSDK.emd.comm.MetricGetException: em_error=Couldn’t connect to neel1-hs01.net:3306/tcp: IO::Socket::INET: connect: Connection refused at /var/lib/mysql/agent12c/plugins/pythian.mysql.prod.agent.plugin_12.1.0.1.2/scripts/emx/mysql/mysql_oem.pm line 45′

    Thanks

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>