Announcement: Release 1.1.1 of MySQL Plug-in for Oracle Enterprise Manager
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.
Category: MySQL, MySQL Plugin for Oracle Grid Control, Oracle
Tags: MySQL, MySQL Plugin for Oracle Grid Control, Oracle
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.
--- 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, bThanks 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.
@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?
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.
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, what MySQL version are you using?
It fails to execute this simple query:
The MySQL version is 5.1.47 community, The main db is a slave in a replication if that makes a difference
It should be not problem. Can you try the statements I provided from the command line and provide the output?
@Brett:
I asked Sheeri about it and she explained why it can happen. It’s probably your case. Could you check please?
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
See the MySQL manual page on SQL mode to see what other modes are available, that you may want/need.
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?
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.
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.
Hello Alex
Any news about the em_error=#08S01Bad handshake error ?
thanks
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?
Yes, I was able to get mysqlbackup to run as an OS command job within GC.
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