Bug #101267 altering table containing decimal causes corruption

• Submitted: 22 Oct 2020 2:16
• Modified: 22 Oct 2020 5:17
• Reporter: bombzj bombzj
• Status: Verified
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 5.7.31, 5.6.50, 5.7.32, 8.0.22
• OS: CentOS (7.8)
• CPU Architecture: x86

[22 Oct 2020 2:16] bombzj bombzj

Description:
mysql> CREATE TABLE `test` (
    -> `id` INT NOT NULL AUTO_INCREMENT,
    -> `n` DECIMAL(10,2) ZEROFILL NOT NULL DEFAULT 0,
    -> `t` VARCHAR(45) NOT NULL DEFAULT '',
    -> PRIMARY KEY (`id`))
    -> ENGINE=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE test
    -> CHANGE COLUMN `n` `n` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT '0' ;
ERROR 1034 (HY000): Incorrect key file for table 'test'; try to repair it

The table is now broken and cannot be repaired.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.31    |
+-----------+

How to repeat:
CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT,
`n` DECIMAL(10,2) ZEROFILL NOT NULL DEFAULT 0,
`t` VARCHAR(45) NOT NULL DEFAULT '',
PRIMARY KEY (`id`))
ENGINE=myisam;

ALTER TABLE test
CHANGE COLUMN `n` `n` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT '0' ;

Might be something to do with 'DECIMAL(10,2) ZEROFILL'

[22 Oct 2020 5:17] MySQL Verification Team

Hello!

Thank you for the report and test case.

Thanks,
Umesh