Altering decimal sometimes causes loss of data

[Kayla]

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://bugs.mysql.com/bug.php?id=18014). I also tested this running MySQL 5.6 and had no issues. So we are thinking it could possibly be with our version of Percona MySQL. We are running tokudb-7.5.7 on Percona Server (GPL), Release 72.2, Revision 8d0f85b. Below is an example of our issue:
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)

[George Ormond Lorch III (gl-az)]

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.

[George Ormond Lorch III (gl-az)]

Reported https://tokutek.atlassian.net/browse/DB-943

[Jeff (jeffscott2)]

Here's a little more information. I'm seeing the same thing on MariaDB 10.1.14 that uses tokudb version 5.6.26-74.0 and see it in Percona Server 5.6.29 as well.

When changing a decimal column's scale but not precision on a tokudb column (and keeping everything else the same) the column's data can be corrupted.

*NB* Changing NULL/NOT NULL, SIGNED/UNSIGNED or decimal precision will all result in things working properly.

Here's another test case that can reproduce the problem:

create table dec_test(
 val decimal(9,2) NOT NULL
) ENGINE = TOKUDB;

INSERT INTO `dec_test` VALUES (0);
INSERT INTO `dec_test` VALUES (10);
INSERT INTO `dec_test` VALUES (20);

ALTER TABLE `dec_test` CHANGE COLUMN `val` `val` DECIMAL(9,4) NOT NULL;

select * from dec_test;

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2138