MySQL Bugs: #25025: Connector/J getRewriteBatchedStatements() should be more forgiving of comments

Bug #25025	Connector/J getRewriteBatchedStatements() should be more forgiving of comments
Submitted:	13 Dec 2006 3:58	Modified:	16 May 2007 19:53
Reporter:	Martin Ross	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.x, 3.1.x	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	batch, insert, REWRITEBATCHEDSTATEMENTS

Description:
Current code inside executeBatch() scans the originalSql for the INSERT value in order to decide to enable batched performance.  See fragment below...

<SNIP>
if (!this.batchHasPlainStatements
&& this.connection.getRewriteBatchedStatements()) {
if (StringUtils.startsWithIgnoreCaseAndWs(this.originalSql,
							"INSERT")) {
return executeBatchedInserts();
}
</SNIP>
BUT executeBatchInserts() is not called the case that comments placed in the SQL string ahead of the INSERT.

/* insert com.medicaltelecom.hibernate.model.Services */
/* insert com.medicaltelecom.hibernate.model.Services */ INSERT into Services()

I classify this issue as serious because Hibernate has an option (use_sql_comments) that will prepend SQL comments to the SQL string.  Thus any batch inserting will be broken on Hibernate.

How to repeat:
See above.

Suggested fix:
Allow comments to be embedded before the insert.

Hi Martin and thanks for your problem report. Same code is present in both 3.1 and 5.0 PreparedStatement.java sources preventing adding of comments.

rewriteBatchedStatements=true is a workaround for missing functionality in the server-side implementation of prepared statements (array binding). Short of putting a full SQL parser in the JDBC driver, we can't expand a lot of effort on improving the feature until either the server implements this functionality, or other more important features (JDBC-4.0 support for example) are completed.

Fair enough.  But at a minimum you should update documentation to tell hibernate users to make sure that use_sql_comments is disabled.  Otherwise Hibernate users are going to be really screwed over by this issue since statement batching is a massive performance improvement.  

Either that or code a dirty regexp that just strips out any leading /* */...  

If you are okay with including it then I can whip up a few line fix for this...

M

Notice that batching won't be broken in this case, just that performance will suffer (to clarify the comment in the original bug report).

Set to documenting.

I've added a new section to the documentation on using Hibernate and C/J together, which currently only consists of a note on this configuration issue: 

Within Hibernate, do not enable the use_sql_comments option, as this can introduce comments
before the real SQL statements that may confuse the parsing mechanism in Connector/J.

Setting status back to 'To be fixed later'

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17637

Given that Mark has committed the patch it seems that we don't need to add the documentation comment.

Martin, please recheck with your test case and confirm your troubles have gone away.

Work for me..  Will this make it into next release?

Thanks,

Martin

Hi Martin. I can't be 100% sure but it's likely to appear in next release.