Release 1.1.1 of MySQL Plug-in for Oracle Enterprise Manager

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

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?

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

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

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.

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

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

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

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.
[code]
--- 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
[/code]

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!

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.

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

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.

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

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

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?

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

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

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

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?

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

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 ?

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

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.

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,

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,

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.

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.

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

thanks

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

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.

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.

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

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)

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