MySQL Bugs: #51509: allowMultiQueries option is ignored if used with PreparedStatement

Bug #51509	allowMultiQueries option is ignored if used with PreparedStatement
Submitted:	25 Feb 2010 15:09	Modified:	16 Dec 2011 7:55
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	allowMultiQueries, PreparedStatement

Description:
allowMultiQueries option is ignored if used with PreparedStatement.
I call pstmt.AddBatch()/pstmt.ExecuteBatch() methods.

All queries run separately:

309 Query     create table tab_batch_stmt(id int auto_increment primary key, vc varchar(32))engine=innodb
100226  2:02:14   309 Query     insert into tab_batch_stmt (id, vc) values (1, '11111111 aaaaaa')
309 Query     insert into tab_batch_stmt (id, vc) values (2, '22222222 bbbbbb')
309 Query     insert into tab_batch_stmt (id, vc) values (3, '33333333 cccccc')
309 Query     insert into tab_batch_stmt (id, vc) values (4, '44444444 dddddd')
309 Query     insert into tab_batch_stmt (id, vc) values (5, '55555555 eeeeee')

How to repeat:
see the test case uploaded below

test case

Attachment: issue_batch_stmt.java (text/x-java), 2.14 KiB.

"allowMultiQueries" is orthogonal to whether or not batched prepared statements are rewritten. To enable batch prepared statement rewriting one should use "rewriteBatchedStatements=true". 

I'm setting this to 'not a bug', then back to 'documenting' so that this can get better documented.

Mark,

Thanks for your reply.

However, it does not explain why allowMultiQueries is not working with PreparedStatement.

I should have not mentioned rewriteBatchedStatements at all (for the same reason you already mentioned).

Please look at this situation as if we are using only allowMultiQueries=true.
Thanks.

If I use allowMultiQueries with Statement the resulting query consists of several statement separated by semicolon as follows:

100226  1:14:14   303 Query     insert into tab45283 (id, vc) values (1, '11111111 aaaaaa');insert into tab45283 (id, vc) values (2, '22222222 bbbbbb');insert into tab45283 (id, vc) values (3, '33333333 cccccc');insert into tab45283 (id, vc) values (4, '44444444 dddddd');insert into tab45283 (id, vc) values (5, '55555555 eeeeee')

Here is the log fragment, which shows queries from PreparedStatement

309 Query     create table tab_batch_stmt(id int auto_increment primary key, vc
varchar(32))engine=innodb
100226  2:02:14   309 Query     insert into tab_batch_stmt (id, vc) values (1, '11111111 aaaaaa')
309 Query     insert into tab_batch_stmt (id, vc) values (2, '22222222 bbbbbb')
309 Query     insert into tab_batch_stmt (id, vc) values (3, '33333333 cccccc')
309 Query     insert into tab_batch_stmt (id, vc) values (4, '44444444 dddddd')
309 Query     insert into tab_batch_stmt (id, vc) values (5, '55555555 eeeeee')

As you can see each query is sent separately, not as the batch.

Mark,

For better understanding of the problem I want to add that in the current case allowMultiQueries is not used just to enable executing batch queries. It is used to make the driver to do something. 

This "something" is to generate the multi-statement query like "INSERT INTO tab1 ...; INSERT INTO tab2 ...; INSERT INTO tab3;"

As I previously indicated, statement.AddBatch()/statement.ExecuteBatch() generates a multi-statement query and runs all together at once. 

preparedStatement.AddBatch()/preparedStatement.ExecuteBatch() generates separate statements and runs them separately.

I hope this explains my point

@Bogdan,

"This "something" is to generate the multi-statement query like "INSERT INTO tab1 ...;INSERT INTO tab2 ...; INSERT INTO tab3;""

Almost...It's to allow someone using the driver to issue such a query from their program, not for Statements that use .executeBatch() to do so. This feature is disabled by default.

Statements that use .executeBatch() can issue multiqueries when the driver has been configured with "rewriteBatchStatements=true", irregardless of the "allowMultiQueries" option's value.

From the allowMultiQueries documentation: 

Allow the use of ';' to delimit multiple queries during one statement (true/false), defaults to 'false' (true/false), defaults to 'false', and does not affect the addBatch() and executeBatch() methods, which instead rely on rewriteBatchStatements.