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: | |
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
[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