Bug #71672 Every SQL statement is checked if it contains "ON DUPLICATE KEY UPDATE" or not
Submitted: 11 Feb 2014 15:02 Modified: 16 Jul 2014 22:08
Reporter: Andrej Golovnin (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version:5.1.29 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: jdbc, ServerPreparedStatement, SQL

[11 Feb 2014 15:02] Andrej Golovnin
Description:
ServerPreparedStatement checks every SQL statement whether it contains the clause "ON DUPLICATE KEY UPDATE" or not. AFAIK "ON DUPLICATE KEY UPDATE" is only allowed for INSERT-statements. Therefore the check for SELECT, DELETE and UPDATE statements does not make sense and reduces the performance of the JDBC driver.

How to repeat:
Use debugger/profiler to very it.

Suggested fix:
The check should be performed only for INSERT-statements. See the attached patch for details. Please verify also my changes in the method StatementImpl#executeBatch(). I added code to get the first character of the SQL statement. The problem with this change is that in the line above:

updateCounts[commandIndex] = executeUpdate(sql, true, true);

the #executeUpdate method also looks up the first character of the SQL statement. Maybe this can be optimized or you may consider to remove my change in the #executeBatch()-method.

The patch also adds a new configuration option "avoidCheckOnDuplicateKeyUpdateInSQL" with the default value "false" to disable the check for "ON DUPLICATE KEY UPDATE" at all, as not all applications uses this feature of MySQL. So if an application does not use "ON DUPLICATE KEY UPDATE" at all, it may improve the performance by disabling the check.
[11 Feb 2014 15:03] Andrej Golovnin
Patch for this issue.

Attachment: OnDuplicateKeyIn.patch (application/octet-stream, text), 7.24 KiB.

[12 Feb 2014 1:11] Filipe Silva
Hi Andrej,

Thank you for this bug report. Verified by code review.
[12 Feb 2014 6:23] Alexander Soklakov
Hi Andrej,

Thank you for contribution, just please confirm that it is submitted under the terms of the OCA (Contributions tab).
[12 Feb 2014 6:51] Andrej Golovnin
Hi Filipe,

I have signed OCA for OpenJDK some time ago. Let me know, if I should sign a new OCA for MySQL Connector/J contributions. And I will do that ASAP.

Best regards,
Andrej Golovnin
[18 Feb 2014 13:49] Andrej Golovnin
Patch for this issue.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: OnDuplicateKeyIn.patch (application/octet-stream, text), 7.24 KiB.

[21 Feb 2014 9:23] Alexander Soklakov
Thanks Andrej! Accepted.
[16 Jul 2014 22:08] Daniel So
Added the following entry to the Connector/J 5.1.32 changelog:

"Connector/J checks every SQL statement in a server-side prepared statement to see whether it contains the ON DUPLICATE KEY UPDATE clause; but because the clause is only used with INSERT statements, the checks are unnecessary for other SQL statements while they reduce the performance of Connector/J. A new, boolean connection property avoidCheckOnDuplicateKeyUpdateInSQL has been added, by which the checks for the ON DUPLICATE KEY UPDATE clause can be disabled."