MySQL Bugs: #11084: Incorrect arguments to mysql_stmt

Bug #11084	Incorrect arguments to mysql_stmt_execute
Submitted:	3 Jun 2005 21:20	Modified:	14 Jun 2005 19:00
Reporter:	Gili	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	4.1.12-nt	OS:	Windows (WinXP sp2)
Assigned to:		CPU Architecture:	Any

Description:
There seems to be a bug in the JDBC driver. Here is my version info:

17:16:01,702  INFO SettingsFactory:77 - RDBMS: MySQL, version: 4.1.12-nt
17:16:01,702  INFO SettingsFactory:78 - JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-3.1.8 ( $Date: 2005/04/14 20:36:13 $, $Revision: 1.27.4.64 $ )

Here is a sniplet from my error log (log4j generates this when Hibernate runs):

Hibernate: select themehiber0_.id as id, themehiber0_.version as version3_, themehiber0_.name as name3_, themehiber0_.parent_id as parent5_3_, themehiber0_.class as class from theme themehiber0_ where themehiber0_.parent_id is null limit ?
17:16:08,062  WARN JDBCExceptionReporter:71 - SQL Error: 1210, SQLState: HY000
17:16:08,062 ERROR JDBCExceptionReporter:72 - Incorrect arguments to mysql_stmt_execute

The last line indicates the bug in question. I would appreciate a workaround if you can find one.

How to repeat:
It's 100% reproducible on my end, but the testcase is way too complex. Sorry :( With your help, maybe I can do further debugging on my end.

Do you use simple Statement with "select...limit ?" instead of prepareStatement ?

Could you please write here some example test case ?

I don't think the SQL statement I quoted is necessarily the one causing the problem. I suspect that the problem is that my query contains "... where list={list of values}" and I don't think it is legal to use the equality operator against lists.

It is difficult to get you a normal query statement of a prepared statement or a smaller testcase because I am using Hibernate and I don't think it gives me this level of control.

To make matters worse I've decided that what I was trying to do is likely not going to be efficient anyway (since it'll involve many lookups to find out if an entire list exists in the DB) so I've decided on abandoning this approach.

If you can't reproduce the problem, feel free to close it for now. Sorry :(

I've had the same problem while doing an insert.

Server version: 4.1.12
JDBC Connector version: 3.1.18a
OS: RedHat 9

The logged output is:
Hibernate: update form_sectors set required=?, refreshOnChange=?, tableName=?, comboLabel=?, labelTableColumns=?, valuesTableColumn=?, whereClause=?, whereSectors=?, orderByColumns=?, orderAsc=?, ignoreDefaultWhereClause=?, validationString=?, visibleCondition=?, validationErrorMessage=?, fieldDescriptor=?, sectorCode=?, sectorLabel=?, hibernateType=?, toolTip=?, defaultValue=?, titleStyle=?, labelStyle=?, textInputStyle=?, comboBoxStyle=?, checkBoxStyle=?, radioButtonStyle=?, linkStyle=?, valueBgStyle=?, valueBgImage=?, formBgImage=?, labelBgStyle=?, labelBgImage=?, bgStyle=?, bgImage=?, onViewInputStyle=?, labelPosition=?, labelWidth=?, labelAlign=?, valueAlign=?, sectorWidth=?, id_form_type=? where id_form_sector=?
15:38:23,934  WARN: SQL Error: 1210, SQLState: HY000
15:38:23,934 ERROR: Incorrect arguments to mysql_stmt_execute
15:38:23,940 ERROR: could not update: [com.mycompany.ComboBox#3290]
java.sql.SQLException: Incorrect arguments to mysql_stmt_execute
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1366)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:952)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1974)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1897)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1758)
	at com.mchange.v2.c3p0.impl.C3P0PreparedStatement.executeUpdate(C3P0PreparedStatement.java:68)
	at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)

Please use a nightly build of Connector/J 3.1.x for testing (from http://downloads.mysql.com/snapshots.php).

Are you telling the driver not to emulate prepared statements with LIMITs in them? The query you posted should not be executed as a server-side prepared statement, since prior to MySQL-5.0.7, server-side prepared statements don't support "... LIMIT ?". The driver should've detected this, so either 1) There's a bug with the detection, 2) You're not really running Connector/J 3.1.8 and you have some older version sitting around or 3) You've configured Connector/J not to detect these types of statements.

I'd suggest just turning off server-side prepared statements altogether if you're using Hibernate and MySQL-4.1 (i.e. useServerPrepStmts=false). You're most likely not going to see a performance gain from using them in that scenario.

With the nightly build:

java.sql.SQLException: Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: No operations allowed after connection closed.'.

** BEGIN NESTED EXCEPTION ** 

java.sql.SQLException
MESSAGE: No operations allowed after connection closed.

STACKTRACE:

java.sql.SQLException: No operations allowed after connection closed.
	at com.mysql.jdbc.Connection.checkClosed(Connection.java:1834)
	at com.mysql.jdbc.Connection.versionMeetsMinimum(Connection.java:5315)
	at com.mysql.jdbc.Field.<init>(Field.java:170)
	at com.mysql.jdbc.MysqlIO.unpackField(MysqlIO.java:636)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:412)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1955)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1380)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
	at com.mysql.jdbc.Connection.pingInternal(Connection.java:4069)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2675)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1477)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:264)
	at java.sql.DriverManager.getConnection(DriverManager.java:512)
	at java.sql.DriverManager.getConnection(DriverManager.java:140)
	at com.mchange.v2.c3p0.impl.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:138)
	at com.mchange.v2.c3p0.impl.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:235)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.acquireResource(C3P0PooledConnectionPool.java:65)
	at com.mchange.v2.resourcepool.BasicResourcePool.assimilateResource(BasicResourcePool.java:721)
	at com.mchange.v2.resourcepool.BasicResourcePool.acquireUntil(BasicResourcePool.java:502)
	at com.mchange.v2.resourcepool.BasicResourcePool.access$000(BasicResourcePool.java:34)
	at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:903)
	at com.mchange.v2.async.CarefulRunnableQueue$TaskThread.run(CarefulRunnableQueue.java:145)

Gili,

The auto reconnect exception is due to BUG#11259, which has been fixed for tonight's build. Please test with a new nightly snapshot after 00:00 GMT June-15.

However, I'll give you the same warning I gave the owner of BUG#11259, which is:

btw, autoReconnect is going to get deprecated in Connector/J 3.2. You should look at something that is more robust, such as the example listed at:

http://dev.mysql.com/doc/connector/j/en/cj-faq.html#id2829608

You should also configure your connection pool to not allow stale connections, and periodically test them if stale connections are an issue for your application.

The latest nightly build resolved the first issue ('Incorrect arguments to mysql_stmt_execute'), but the autoReconnect issue is still there (I had to remove it from the URL). 
Thanks a lot for the help!!
Andres