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

[1 Jul 2011 10:06] Cyril SCETBON
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 !
[1 Jul 2011 10:16] Tonci Grgin
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).
[1 Jul 2011 12:33] Cyril SCETBON
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.
[1 Jul 2011 12:56] Tonci Grgin
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.
[1 Jul 2011 13:21] Cyril SCETBON
As a said we don't have it when directly requesting the MySQL Server with an SQL script and the mysql client
[1 Jul 2011 16:22] Tonci Grgin
Thank you Cyril, I will take a look somewhere during next week.
[4 Jul 2011 7:57] Cyril SCETBON
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.
[4 Jul 2011 9:29] Cyril SCETBON
As I told you first, there are two workarounds to fix this issue :

- change column type from INT UNSIGNED to INT
- drop the trigger
[4 Jul 2011 13:45] Cyril SCETBON
FYI, I've found that the issue happens with MySQL 5.5.10+
[14 Jul 2011 19:17] Tonci Grgin
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.
[15 Jul 2011 9:15] Cyril SCETBON
So, you confirm that you see the errors, that's it ? (Even if you think it's not related to jdbc)
[15 Jul 2011 9:25] Tonci Grgin
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.
[15 Jul 2011 9:36] Cyril SCETBON
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)
[17 Jul 2011 9:58] Valeriy Kravchuk
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)?
[18 Jul 2011 8:07] Cyril SCETBON
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 ...
[18 Jul 2011 8:58] Tonci Grgin
Again Java program... No, you need to insert values via cl client using trigger mimicking what Java program does.
[18 Jul 2011 9:01] Valeriy Kravchuk
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?
[18 Jul 2011 9:11] Cyril SCETBON
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
[2 Aug 2011 12:26] Cyril SCETBON
Can you investiguate this even without a FULL SQL test case ?
[2 Aug 2011 12:42] MySQL Verification Team
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
[3 Nov 2011 10:11] Cyril SCETBON
you're right, it's fixed in 5.5.15

thanks