Bug #12158 ON Duplicate Key when field Truncated causes Server Hang
Submitted: 25 Jul 2005 17:32 Modified: 25 Jul 2005 17:52
Reporter: J Jorgenson Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.3 OS:Solaris (Solaris)
Assigned to: CPU Architecture:Any

[25 Jul 2005 17:32] J Jorgenson
Description:
When inserting into a table with a value that causes 'Field Truncated' error message, the record is still inserted.  

If you add ON DUPLICATE KEY UPDATE the value which should be truncated, the SQL engine/thread hangs, causing locks on the underlying table.

How to repeat:

CREATE TABLE myTable( 
id INT NOT NULL,
value DECIMAL(9,2),
PRIMARY KEY id
);

INSERT INTO myTable(id, value) VALUES (1, 6.5243) ON DUPLICATE KEY UPDATE value=VALUES(value); -- Causes table locking & SQL hang

INSERT INTO myTable(id, value) VALUES (1, Round(6.5243,2)) ON DUPLICATE KEY UPDATE value=VALUES(value); -- Works just fine...

Problem Researched: 
INSERT INTO myTable(id, value) VALUES (1, 6.5243)
   returns an 'Field Truncated' error message, but still inserts the value.

When you add the ON DUPLICATE KEY, the SQL hangs/locks the table.

Suggested fix:
Fail safe... 

Handle the truncation like a 'normal' Insert... Just report the Warning, but perform the truncation.
[25 Jul 2005 17:52] MySQL Verification Team
I was unable to repeat the behavior reported on Linux Suse
with current BK server, your server version is pretty older
and a lot of bugs were done since.:

mysql> INSERT INTO myTable(id, value) VALUES (1, 6.5243) ON DUPLICATE KEY UPDATE
    -> value=VALUES(value); 
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'value' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from myTable;
+----+-------+
| id | value |
+----+-------+
|  1 |  6.52 |
+----+-------+
1 row in set (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.11-beta |
+-------------+
1 row in set (0.00 sec)