Bug #18014 data loss caused by altering decimal fields
Submitted: 7 Mar 2006 9:56 Modified: 28 Jul 2006 4:15
Reporter: Ramil Kalimullin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1 OS:Any (all)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[7 Mar 2006 9:56] Ramil Kalimullin
Description:
Altering a decimal field causes field data loss.

How to repeat:
mysql> create table t1(a decimal(7,2));                                                
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(123.12);                                                  
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;                                                               
+--------+
| a      |
+--------+
| 123.12 |
+--------+
1 row in set (0.00 sec)

mysql> alter table t1 modify a decimal(10,2);                                          
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1;                                                               
+------+
| a    |
+------+
| 0    |
+------+
1 row in set, 1 warning (0.00 sec)
[7 Mar 2006 12:51] Peter Laursen
Now this is funny: Uisng Third party Client SQLyog (a MySQL C-api based client):

On My MySQL 5.1.7 (WinXP) this case results in a being changed to 31500.00 when each statement is executed one-at-a-time (reproduced several times).  However with this client you can also type in the whole sequence of queries and exectue all on one mouse-click and it returns 123.12!

When a short span of time between commands: no error, when a long span of time: error!  or ???
[7 Mar 2006 13:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3541
[10 Apr 2006 17:41] Jim Winstead
Okay to push (after second review).
[27 Apr 2006 12:59] Magnus Blåudd
Okay to push
[27 Jul 2006 12:12] Magnus Blåudd
Pushed to 5.1.12
[28 Jul 2006 4:15] Paul Dubois
Noted in 5.1.12 changelog.

Changing the definition of a DECIMAL column with ALTER TABLE
caused loss of column values.