MySQL Bugs: #61717: int unsigned column filled incorrectly

Bug #61717	int unsigned column filled incorrectly
Submitted:	1 Jul 2011 10:06	Modified:	3 Nov 2011 10:11
Reporter:	Cyril SCETBON	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.5.13	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	connector, int unsigned, MySQL

Description:
Hi,

When we use mysql-connector-java 5.1.16 with MySQL >= 5.5.12, int unsigned column are not filled correctly. It's the case with MySQL 5.5.9
The only way to fix it is to change the type of the column in MySQL from INT UNSIGNED to INT

here is the result with int unsigned column :

- execution 1

mysql> select freq,freqSince2007 from mytable;
+------+---------------+
| freq | freqSince2007 |
+------+---------------+
|    1 |             0 |
|    0 |             8 |
|    0 |             0 |
|    0 |             0 |
|    0 |     744846433 |
|    0 |             0 |
|    2 |     744846433 |
|    0 |             0 |
|    0 |             0 |
|    1 |             0 |
|    1 |             0 |
|    1 |             1 |
+------+---------------+
12 rows in set (0.00 sec)

- execution 2

mysql> select freq,freqSince2007 from mytable;
+-----------+---------------+
| freq      | freqSince2007 |
+-----------+---------------+
|         1 |             0 |
|         0 |     744846433 |
| 741485427 |     539570464 |
|  15720224 |             0 |
|         0 |    1158220041 |
|         0 |             1 |
|         0 |     105759088 |
|         1 |             1 |
|         1 |             0 |
|         1 |             0 |
|         1 |             0 |
|         1 |             1 |
+-----------+---------------+
12 rows in set (0.00 sec)

you see that the results change between 2 executions

Here is the result with INT instead of INT UNSIGNED :

- execution 1

mysql> select freq,freqSince2007 from mytable;
+------+---------------+
| freq | freqSince2007 |
+------+---------------+
|    1 |             0 |
|    1 |             0 |
|    4 |             2 |
|    3 |             3 |
|    1 |             1 |
|   13 |            12 |
|    2 |             0 |
|    1 |             1 |
|    1 |             0 |
|    1 |             0 |
|    1 |             0 |
|    1 |             1 |
+------+---------------+
12 rows in set (0.00 sec)

- execution 2

mysql> select freq,freqSince2007 from mytable;
+------+---------------+
| freq | freqSince2007 |
+------+---------------+
|    1 |             0 |
|    1 |             0 |
|    4 |             2 |
|    3 |             3 |
|    1 |             1 |
|   13 |            12 |
|    2 |             0 |
|    1 |             1 |
|    1 |             0 |
|    1 |             0 |
|    1 |             0 |
|    1 |             1 |
+------+---------------+
12 rows in set (0.00 sec)

results are the same

How to repeat:
I can provide you a java program that load an XML file.

I tried with another table that has only 3 fields (1 int, 2 int unsigned) and I don't meet the issue. It seems to be related to the table structure too.

Suggested fix:
check the code !

Hi Cyril and thanks for your report.

However, there is not much substance in it... Please attach small but complete test case proving your point (samples are in c/J test suite).

Something weird, is that it's correlated to a trigger AFTER INSERT.
I've replaced INT by INT UNSIGNED (like it was), and if I drop the trigger it works !
However, with MySQL 5.5.9 I had no issues, while I've this one with 5.5.12+ :(

I'll send you a dump with the code to test it. We don't have it if we use an sql script. We do not see the weird values (freq and freqSince2007) in general log.

Cyril, I think this is a server problem. So try repeating it without Java (ie. from cl client) and, if successful, please change the bug category.

As a said we don't have it when directly requesting the MySQL Server with an SQL script and the mysql client

Thank you Cyril, I will take a look somewhere during next week.

The weird thing is that if I take a look to the  general log file I see the value 1 pushed to the MySQL Server, but 15200352 is stored for example (not always the same value).

It does not happen if I execute the statement in MySQL client.

As I told you first, there are two workarounds to fix this issue :

- change column type from INT UNSIGNED to INT
- drop the trigger

FYI, I've found that the issue happens with MySQL 5.5.10+

Cyril, I reviewed the case and think this has *nothing* to do with connector Java... The mere fact that 
  o dropping the trigger solves the problem
  o general query log shows good statements sent from connector
should point to some other category. Please check BugsDB for possible problems with INT and triggers.

So, you confirm that you see the errors, that's it ? (Even if you think it's not related to jdbc)

Cyril, no. I examined your case as far as my competence goes. I do not have time to pursue every report as then you would not have any improvements in c/J. So, please do your homework and try repeating this with cl client (or using C API in other way) and do look into BugsDB (as this might be a known problem already). Then change category accordingly.
I will also inform bugs team of this report.

As I can't find another bug describing the same situation. I've already tested it with mysql client as told before, but I can't try using another API (too much time)

I've executed t1.sql uploaded and got the following results:

macbook-pro:5.5 openxs$ bin/mysql -uroot test < ~/Downloads/t1.sql 
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select freq,freqSince2007 from t1;
+------+---------------+
| freq | freqSince2007 |
+------+---------------+
|    1 |             0 |
|    0 |    1158220041 |
|    4 |             2 |
|    3 |             3 |
|    1 |             1 |
|   13 |            12 |
|    2 |             0 |
|    1 |             1 |
|    1 |             0 |
|    1 |             0 |
|    1 |             0 |
|    1 |             1 |
+------+---------------+
12 rows in set (0.00 sec)

Do you see the same results and where is the bug here (that value in second row)?

Nooooooo

You just created the structure ! Ok, there are values in the sql file but do not care about them. You need to launch the java program on your database, which inserts small values in both columns (freqxxx), and you'll get (if you're able to reproduce it but I'm near each time) big values like the one you saw. That's the bug I see. Java team said it's not related to the java layer and I see good values in statements logged in the general log, but I get bad values in the table ...

Again Java program... No, you need to insert values via cl client using trigger mimicking what Java program does.

If we claim it's a server bug, then we need a test case that shows the problem without any use of Connector/J. Do you see any problem in the results (from mysql client) I sent?

No, I don't , but as the java team said they don't see any bugs ...
I dit not reproduce it with the mysql client. I know it would be great to reproduce it with the mysql client but I can't
That's all I have

Can you investiguate this even without a FULL SQL test case ?

Hi Cyril,

Without actually testing this I'll guess you hit a known bug with CASE/WHEN in a stored routine.  It access random memory, thereby causing crashes or garbage results.

http://bugs.mysql.com/bug.php?id=61470
(private)

It should be fixed in 5.5.15. Can you try that ?  Or just try removing the CASE/WHEN clause in doublemetaphone function

you're right, it's fixed in 5.5.15

thanks