Altering decimal sometimes causes loss of data
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.6 |
Triaged
|
Low
|
Unassigned | |||
5.7 |
Triaged
|
Low
|
Unassigned |
Bug Description
We're seeing data loss in some cases of altering a field type of Decimal. In some cases it results in a 0, in other cases, it results in a random number, and in other cases, there are no issues. I did come across a bug that was reported a while back about this issue and it was fixed (https:/
mysql> CREATE TABLE `testdecimal`
-> (
-> `testme` DECIMAL(6,6)
-> ,`testme2` DECIMAL(6,4)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO
-> `testdecimal`
-> (
-> `testme`
-> ,`testme2`
-> )
-> VALUES
-> (
-> .5556789
-> ,24.4444444
-> );
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql>
mysql> SELECT * FROM `testdecimal`;
+------
| testme | testme2 |
+------
| 0.555679 | 24.4444 |
+------
1 row in set (0.00 sec)
mysql>
mysql> ALTER TABLE `testdecimal` MODIFY COLUMN `testme` DECIMAL(4,3);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE `testdecimal` MODIFY COLUMN `testme2` DECIMAL(4,1);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
SELECT * FROM `testdecimal`;
+------
| testme | testme2 |
+------
| 0 | 161.6 |
+------
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> show warnings;
+------
| Level | Code | Message |
+------
| Warning | 1366 | Incorrect decimal value: '' for column '' at row -1 |
+------
1 row in set (0.00 sec)
no longer affects: | percona-server/5.5 |
Tested against PS 5.6.28-76.1 innodb:
mysql> drop database if exists test; create database test; use test;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
mysql> create table testdecimal(testme DECIMAL(6,6), testme2 DECIMAL(6,4)) engine=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into testdecimal(testme, testme2) values(.5556789, 24.4444444);
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> select * from testdecimal; ----+-- ------- + ----+-- ------- + ----+-- ------- +
+------
| testme | testme2 |
+------
| 0.555679 | 24.4444 |
+------
1 row in set (0.00 sec)
mysql> alter table testdecimal modify column testme DECIMAL(4,3);
Query OK, 1 row affected, 1 warning (0.21 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> alter table testdecimal modify column testme2 DECIMAL(4,1);
Query OK, 1 row affected, 1 warning (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> select * from testdecimal; --+---- -----+ --+---- -----+ --+---- -----+
+------
| testme | testme2 |
+------
| 0.556 | 24.4 |
+------
1 row in set (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
Tested against PS 5.6.28-76.1 tokudb:
mysql> drop database if exists test; create database test; use test;
Query OK, 1 row affected (0.07 sec)
Query OK, 1 row affected (0.02 sec)
Database changed
mysql> create table testdecimal(testme DECIMAL(6,6), testme2 DECIMAL(6,4)) engine=tokudb;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into testdecimal(testme, testme2) values(.5556789, 24.4444444);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from testdecimal; ----+-- ------- + ----+-- ------- + ----+-- ------- +
+------
| testme | testme2 |
+------
| 0.555679 | 24.4444 |
+------
1 row in set (0.00 sec)
mysql> alter table testdecimal modify column testme DECIMAL(4,3);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table testdecimal modify column testme2 DECIMAL(4,1);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from testdecimal; --+---- -----+ --+---- -----+ --+---- -----+
+------
| testme | testme2 |
+------
| 0 | 161.6 |
+------
1 row in set, 1 warning (0.00 sec)
mysql> show warnings; ---+--- ---+--- ------- ------- ------- ------- ------- ------- ------- -+ ---+--- ---+--- ------- ------- ------- ------- ------- ------- ------- -+ ---+--- ---+--- ------- ------- ------- ------- ------- ------- ------- -+
+------
| Level | Code | Message |
+------
| Warning | 1366 | Incorrect decimal value: '' for column '' at row -1 |
+------
1 row in set (0.00 sec)
This seems to be a TokuDB specific issue.