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
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.3
• OS: Solaris (Solaris)
• 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)