MySQL Bugs: #49745: deleteRow() for updatable result sets can cause full table scan

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

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.

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.

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.

Correct final patch

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

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.