Bug #51157 INSERT statements with blob data cause JDBC Exception (quan via proxy)

• Submitted: 13 Feb 2010 0:04
• Modified: 5 Nov 2016 10:15
• Reporter: Marcos Palacios
• Status: Won't fix
• Category: MySQL Enterprise Monitor: Server
• Severity: S3 (Non-critical)
• Version: 2.2.0.1616
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[13 Feb 2010 0:04] Marcos Palacios

Description:
Using the proxy method for quan, when we send an INSERT statement with blob data, this causes a JDBC Exception and we see the following errors.

Note: An INSERT statement similar to the one used will be put in a separate comment, albeit with a smaller amount of chars in the blob literal.

2010-02-12 15:12:17,123  WARN [pool-1-thread-1:org.hibernate.util.JDBCExceptionReporter] SQL Error: 1366, SQLState: HY000
2010-02-12 15:12:17,123 ERROR [pool-1-thread-1:org.hibernate.util.JDBCExceptionReporter] Incorrect string value: '\xF1\x90\x98\x8D\xEF\xBF...' for column 'text' at row 1
2010-02-12 15:12:17,123 ERROR [pool-1-thread-1:org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028)
	at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:366)
	at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
	at com.mysql.etools.monitor.pom.hib.HibernateExecutor.asTransaction(HibernateExecutor.java:197)
	at com.mysql.etools.monitor.pom.hib.HibernateExecutor.asTransaction(HibernateExecutor.java:141)
	at com.mysql.etools.monitor.pom.hib.SchemaMaintenanceExecutor.asTransaction(SchemaMaintenanceExecutor.java:229)
	at com.mysql.etools.monitor.pom.hib.HibPersistence.asTransaction(HibPersistence.java:1572)
	at com.mysql.etools.monitor.pom.hib.HibPersistence.put(HibPersistence.java:2002)
	at com.mysql.etools.monitor.bo.ManagerRestApi.put(ManagerRestApi.java:119)
	at com.mysql.etools.monitor.bo.RestAdaptor$4.apply(RestAdaptor.java:253)
	at com.mysql.etools.monitor.bo.RestAdaptor$4.apply(RestAdaptor.java:248)
	at com.mysql.etools.monitor.bo.RestAdaptor$6.apply(RestAdaptor.java:281)
	at com.mysql.etools.monitor.bo.RestAdaptor$6.apply(RestAdaptor.java:276)
	at com.mysql.etools.monitor.bo.RestAdaptor.foreach(RestAdaptor.java:303)
	at com.mysql.etools.monitor.bo.RestAdaptor.foreachInstance(RestAdaptor.java:275)
	at com.mysql.etools.monitor.bo.RestAdaptor.putMatching(RestAdaptor.java:247)
	at com.mysql.etools.monitor.bo.Agent$7.run(Agent.java:584)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
	at java.lang.Thread.run(Thread.java:637)
Caused by: java.sql.BatchUpdateException: Incorrect string value: '\xF1\x90\x98\x8D\xEF\xBF...' for column 'text' at row 1
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1808)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1440)
	at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
	... 24 more

How to repeat:
1- Have the agent and service manager installed and pointing to a mysql instance (db server)

2- Using the mysql client, with the proxy port in the connection, do:
       source sakila-schema.sql;
       source sakila-data-2.sql;
   - one of these files is very large, not attached here, so you can download them from:
     https://intranet.mysql.com/~mpalacios/
   - probably all we need to run trough is the INSERT statement with blob (after the DDL)

3- The quan page will show the INSERT query but the Example Query tab will only say:
     - "Configure Query Analysis to enable example and explain capture."
   even though examples are enabled

4- Look at the monitor log and observe the errors

Suggested fix:
n/a

[5 Nov 2016 10:15] Mark Leith

This is all now done via the aggregator, not proxy/agent directly, closing this bug.