Release 1.1.1 of MySQL Plug-in for Oracle Enterprise Manager

Posted in: Technical Track

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.

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.

58 Comments. Leave new

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

Reply

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?

Reply

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

Reply

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

Reply

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.

Reply

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

Reply

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

Reply

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

Reply

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

Reply
Richard Quintin
March 11, 2010 8:30 am

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
Reply
Alex Gorbachev
March 11, 2010 8:42 am

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

Reply
Richard Quintin
March 17, 2010 9:58 am

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

Reply
Alex Gorbachev
March 17, 2010 10:06 am

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

Reply
Richard Quintin
March 19, 2010 1:02 pm

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.

Reply
Alex Gorbachev
March 19, 2010 1:10 pm

Oh, boomer! Good point. Thx!

Reply
Jeremy Bowland
June 14, 2010 9:17 am

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.

Reply
Alex Gorbachev
June 14, 2010 9:26 am

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

Reply
Jeremy Bowland
June 14, 2010 11:01 am

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

Reply
Alex Gorbachev
June 14, 2010 11:16 am

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.

Reply

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.

Reply
Alex Gorbachev
June 24, 2010 9:13 am

Brett, what MySQL version are you using?

It fails to execute this simple query:

 select "alive"; 
Reply

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

Reply
Alex Gorbachev
June 25, 2010 10:42 am

It should be not problem. Can you try the statements I provided from the command line and provide the output?

Reply
Alex Gorbachev
June 25, 2010 12:42 pm

@Brett:

I asked Sheeri about it and she explained why it can happen. It’s probably your case. Could you check please?

Reply

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

Reply
Sheeri Cabral
June 28, 2010 8:52 am

See the MySQL manual page on SQL mode to see what other modes are available, that you may want/need.

Reply

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?

Reply

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

Reply

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

Reply

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

thx

Reply
Alex Gorbachev
March 1, 2011 2:58 pm

Ali,

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

Alex

Reply
Scott Crouch
June 22, 2011 1:19 pm

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?

Reply
Alex Gorbachev
June 22, 2011 3:17 pm

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.

Reply

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?

Reply
Alex Gorbachev
July 18, 2011 5:21 pm

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

Reply

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 ?

Reply

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

Reply

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.

Reply

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.

Reply

Hello Alex
Any news about the em_error=#08S01Bad handshake error ?
thanks

Reply
W. Scott Morrison
November 7, 2011 2:41 pm

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,

Reply
Alex Gorbachev
November 7, 2011 2:57 pm

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?

Reply
W. Scott Morrison
November 9, 2011 5:36 pm

Yes, I was able to get mysqlbackup to run as an OS command job within GC.
Thanks,

Reply
W. Scott Morrison
November 7, 2011 3:58 pm

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,

Reply

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.

Reply

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.

Reply

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

thanks

Reply

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

Reply

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.

Reply

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.

Reply

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.

Reply

Pete, I have 1.1.2 version available that has a permanent fix. About to publish it.

Alex

Reply

Thank you – will download as soon as it is available.

Reply

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

Reply
Alex Gorbachev
April 30, 2012 11:14 am

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.

Reply

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)

Reply
Alex Gorbachev
April 30, 2012 1:58 pm

You still have the same problem Ashok – you can’t login with the user you are using. Most often I see it’s because you didn’t create user correctly.

Reply

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

Reply

Leave a Reply

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