MySQL Bugs: #30627: FLOAT columns in PK cause SQLException in ResultSet.updateRow

Bug #30627	FLOAT columns in PK cause SQLException in ResultSet.updateRow
Submitted:	24 Aug 2007 16:46	Modified:	31 Aug 2007 14:18
Reporter:	Christopher Schultz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have a table with a PK containing 5 columns. We recently had to change two of these columns from SMALLINT to DOUBLE and are now experiencing an unexpected error (see below).

I'm trying to update an 'order_num' column which is not part of the PK and I'm getting this odd exception:

Exception in thread "main" java.sql.SQLException: refreshRow() called on row that has been deleted or had primary key changed.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
        at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1267)
        at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1190)
        at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2285)
        at UpdateTest.main(UpdateTest.java:30)

(This stack trace comes from a test program I wrote to demonstrate the bug).

I am using an updatable ResultSet along with a '... FOR UPDATE' query. Only the 'order_num' column is being updated (this is code that only updates the order_num, so we're never even trying to update other columns).

How to repeat:
Will attach a test program and table creation script.

Suggested fix:
I can't come up with anything. I will try other column types that will meet out needs. DOUBLE seems like the right one to use, but I'll check out DECIMAL.

Short Java program to demonstrate the bug.

Attachment: UpdateTest.java (application/octet-stream, text), 2.42 KiB.

I forgot to mention that I'm using MySQL server 5.0.44 on Linux 2.6.14 kernel.

Demonstration table create script can be found in the test program source.

I have a workaround: change 'DOUBLE' column type to 'DECIMAL(5, 3)' (which meets our needs) and the code works as expected.

Column type is FLOAT, not DOUBLE. I have updated the bug description to reflect this.

I checked, and DOUBLE appears to work correctly in this particular case, but not FLOAT.

It's a bad, bad idea to use anything floating point as a primary key because the comparisons aren't exact.

The JDBC driver complains because it does an UPDATE, and in the WHERE clause it adds conditions to check for the "old" values of all elements of the primary key, and if the update doesn't return a count of "1", it considers this a failure condition.

To loosen this up would mean the JDBC driver _could_ change data that the user didn't intend, so I'm marking this "not a bug", but setting the state so that the docs team will see the issue and put it in our FAQ.

Mark,

Thanks for your comments. I agree that floats as PKs isn't generally a god idea, even when it's only /part/ of one (2/5 or our PK were floats).

I'm perfectly happy with a documentation change. I checked to see if there were any notes in the docs and didn't find any.

I also "tested" this by using the MySQL command-line tool to just do a BEGIN; SELECT .. FOR UPDATE; UPDATE ... WHERE ...; COMMIT; and the updates worked as expected. Since the MySQL command-line tool doesn't really do any checking like the JDBC driver, this isn't entirely surprising.

> The JDBC driver complains because it does an UPDATE, and in the
> WHERE clause it adds conditions to check for the "old" values of all
> elements of the primary key, and if the update doesn't return a count
> of "1", it considers this a failure condition.

So, in my case, it's probably returning ZERO rows, then? I did notice that sometimes comparing a column value to an immediate value failed even when the numbers appears to be the same. (Duh! Roundoff issues!)

I was a little thrown by the error message, since in this case I wasn't changing a PK when updating the row. Any chance of a more descriptive error message, or maybe one that isn't so specific?

As mentioned, switching to a DECIMAL column seems to work, and makes more sense since it's an "exact" decimal type.

Thanks again!

I've added the following to the C/J FAQ: 

Updating a table that contains a primary key that is either FLOAT or compound primary key that uses FLOAT fails to update the table and raises an exception.

Connector/J adds conditions to the WHERE clause during an UPDATE to check the old values of the primary key. If there is no match then Connector/J considers this a failure condition and raises an exception.

The problem is that rounding differences between supplied values and the values stored in the database may mean that the values never match, and hence the update fails. The issue will affect all queries, not just those from Connector/J.

To prevent this issue, use a primary key that does not use FLOAT. If you have to use a floating point column in your primary key use DOUBLE or DECIMAL types in place of FLOAT.