Bug #49745 deleteRow() for updatable result sets can cause full table scan
Submitted: 16 Dec 2009 19:40 Modified: 8 Jan 2010 10:52
Reporter: Harrison Fisk Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.9+ OS:Any
Assigned to: Mark Matthews CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[16 Dec 2009 19:40] Harrison Fisk
Description:
In certain conditions, the deleteRow() can cause a full table scan in MySQL.  Obviously this is not a good thing since it can cause dramatic performance reduction.  I would imagine that any of the updatable result set logic will all use the same WHERE and have the same problem.

This was introduced by the fix for bug #43759.

To repeat it, you need to have a few requirements:

*  Using multi-byte character set (this is needed to trigger the x'...' format instead of _binary'...' format)
*  Large values in the primary key

How to repeat:
Create a table with an auto_increment primary key column.
Insert large values into the column.
Create a connection to MySQL with a mb character set.
Create an updatable cursor.
Call deleteRow().
Notice that it is doing a full table scan.

I will attach a test case.  In the test case, you will see that Handler_read_rnd_next is much larger. 

Suggested fix:
Don't use the binary encoding introduced in bug #43759 unless the column is a string/blob.
[16 Dec 2009 19:41] Harrison Fisk
Test case, look at the Handler_read_rnd_next stats.  You could also disable the URL UTF8 setting to remove problem.

Attachment: testCase42456.java (text/x-java), 4.22 KiB.

[17 Dec 2009 0:08] Todd Farmer
Patch needs testing and review tomorrow, but solves problem for provided test case.  Need to ensure no regressions, as the patch also deals with decimal and float data types, plus NULLs, and addresses UPDATES as well as DELETES.  Furthermore, logic branches exist for single-column primary and composite keys, need to test more fully.
[18 Dec 2009 20:20] Todd Farmer
Correct final patch

Attachment: diff.txt (text/plain), 4.62 KiB.

[8 Jan 2010 10:52] Tony Bedford
An entry has been added to the 5.1.11 changelog:

The deleteRow method caused a full table scan, when using an updatable cursor and a multibyte character set.