Bug #27025 SQL errors when monitored mysqld is being backed up
Submitted: 10 Mar 2007 15:48 Modified: 1 May 2007 18:32
Reporter: Sloan Childers Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Enterprise Monitor: Agent Severity:S3 (Non-critical)
Version:1.1.0.4876 OS:Any (all)
Assigned to: Jan Kneschke CPU Architecture:Any
Tags: mer 111

[10 Mar 2007 15:48] Sloan Childers
Description:
Hi,

I upgraded our internal Merlin installation to 1.1.0-4876 a couple of  days ago since I got a problem with being out of memory in the Java  heap space.
Since then I've been getting messages like the one below regularly.  According to Jan on IRC[1] it's because the server is down which is  true. Every time I get these the server it is referring to is doing a  backup, which for the 50GB this server has takes some time.
But, shouldn't I get an alert then that the server is down and not  what looks like a complaint that I'm trying to insert NULL into a NOT  NULL column?

[1]  https://intranet.mysql.com/secure/apps/irclog.php?channel=79&start_time=2007-03-08%2013:20...

Jonas

----- Forwarded message from it-merlin@mysql.com -----
    Date: Sat, 10 Mar 2007 13:00:08 +0100 (CET)
    From: 'MySQL IT Merlin' <it-merlin@mysql.com>
Reply-To: 'MySQL IT Merlin' <it-merlin@mysql.com>
 Subject: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Column  was set to data type implicit default; NULL supplied for NOT NULL  column 'version' at row 1
      To: 'MySQL IT Merlin' <it-merlin@mysql.com>

SQL: UPDATE inv_servers SET server_name=?, version=?, host=?, port=?,  blackout=? WHERE server_id=?
PARAMS:  (String): 'sysrepl1:3306'  'null'  (String): 'sysrepl1'   (Integer): '3306'  (Boolean): 'false'  (Integer): '2'
ErrorCode: 0
com.mysql.util.jdbctemplate.SQLRuntimeException:  com.mysql.jdbc.MysqlDataTruncation: Data truncation: Column was set to  data type implicit default; NULL supplied for NOT NULL column  'version' at row 1
    at  com.mysql.util.jdbctemplate.JDBCTemplate.executeUpdate(JDBCTemplate.java:126)
    at  com.mysql.merlin.server.inventory.dao.ServerEntryOperations.updateServer(ServerEntryOperations.java:194)
    at  com.mysql.merlin.server.inventory.dao.ServerEntryOperations.access$300(ServerEntryOperations.java:20)
    at  com.mysql.merlin.server.inventory.dao.ServerEntryOperations$5.execute(ServerEntryOperations.java:161)
    at  com.mysql.merlin.server.inventory.dao.ServerEntryOperations$5.execute(ServerEntryOperations.java:160)
    at com.mysql.util.jdbctemplate.ActionExecutor.execute(ActionExecutor.java:57)
    at  com.mysql.merlin.server.db.GeneralOperations.execute(GeneralOperations.java:95)
    at  com.mysql.merlin.server.inventory.dao.ServerEntryOperations.updateServer(ServerEntryOperations.java:159)
    at  com.mysql.merlin.server.inventory.InventoryService.createOrUpdateServer(InventoryService.java:390)
    at  com.mysql.merlin.server.inventory.InventoryService.listInventory(InventoryService.java:213)
    at  com.mysql.merlin.server.event.AgentListInventoryEvent.performAction(AgentListInventoryEvent.java:20)
    at  com.mysql.merlin.server.inventory.InventoryService.processEvent(InventoryService.java:173)
    at  com.mysql.merlin.server.event.SynchronousEventDispatcher.postEvent(SynchronousEventDispatcher.java:5)
    at  com.mysql.merlin.server.event.AbstractEventDispatcher.postEvent(AbstractEventDispatcher.java:30)
    at  com.mysql.merlin.server.agent.AgentService.processRequest(AgentService.java:252)
    at  com.mysql.merlin.server.agent.AgentService.heartbeat(AgentService.java:153)
    at  com.mysql.merlin.server.agent.HeartBeatCommandProcessor.processRequest(HeartBeatCommandProcessor.java:73)
    at  com.mysql.merlin.server.MerlinServlet.processRequest(MerlinServlet.java:151)
    at com.mysql.merlin.server.MerlinServlet.doCommon(MerlinServlet.java:118)
    at com.mysql.merlin.server.MerlinServlet.doPost(MerlinServlet.java:88)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    at  org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
    at  org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
    at  org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
    at  org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
    at  org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:524)
    at  org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
    at  org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
    at  org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
    at  org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
    at  org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
    at  org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
    at  org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
    at  org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
    at java.lang.Thread.run(Thread.java:595)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Column  was set to data type implicit default; NULL supplied for NOT NULL  column 'version' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2868)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
    at  com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
    at  com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
    at  com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
    at  com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
    at  org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
    at  com.mysql.util.jdbctemplate.JDBCTemplate.executeUpdate(JDBCTemplate.java:124)
    ... 36 more

How to repeat:
See above.

Suggested fix:
It may be as simple as doing some validation on the inventory data before writing to the repository.
[11 Mar 2007 18:51] Sloan Childers
This problem occurs when the managed mysqld instance is down or being backed up and a regularly scheduled inventory scan attempts to run.  The result is an empty version string that causes a SQL exception to show up in the server logs.  A patch has already been committed that checks mysqld status before running inventory and corrects the problem.  It will be available in the first 1.1.0 bug fix release.
[11 Apr 2007 13:37] Roy Woods
What will be the behavior once the fix is applied? Currently when a MySQL instance is down, we receive a notification containing the error message identified above. We would still like to receive a notification, just a more meaningful one than the above jdbc error.
[25 Apr 2007 15:48] Mark Leith
Hi!

With this bug fixed, you will instead see the text for the 'MySQL Server Not Reachable' rule, with the 'Heat Chart' advisor section. 

This is fixed within 1.1.1 

Regards 

Mark
[1 May 2007 18:32] Peter Lavin
Added to the changelog.