Bug #67629 Changing the type of the column destroys the table. Data loss.

• Submitted: 19 Nov 2012 0:27
• Modified: 19 Nov 2012 11:44
• Reporter: Mike Roschin
• Status: Verified
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 5.1/5.5.28
• OS: FreeBSD
• CPU Architecture: Any

[19 Nov 2012 0:27] Mike Roschin

Description:
Table of specific configuration irrevocably destroyed by changing the type of one of the columns.
The availability of data in the table does not matter.
100% repeatability.
Tested on version 5.1 (under FreeBSD 9) and version 5.5.28

How to repeat:
mysql> CREATE TABLE `test`
    -> (
    ->  `id`       int(10)      unsigned NOT NULL AUTO_INCREMENT,
    ->  `ap_id`    int(10)      unsigned NOT NULL,
    ->  `ap_ident` varchar(16)  NOT NULL,
    ->  `type`     varchar(16)  NOT NULL,
    ->  `about`    varchar(64)  NOT NULL,
    ->  `freq_mhz` decimal(6,3) unsigned NOT NULL,
    ->  PRIMARY KEY (`id`),
    ->  KEY `ap_id` (`ap_id`),
    ->  KEY `ap_ident` (`ap_ident`)
    -> )
    -> ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `test` CHANGE `freq_mhz` `freq_mhz` DECIMAL( 6, 3 ) UNSIGNED ZEROFILL NOT NULL;
ERROR 1034 (HY000): Incorrect key file for table 'test'; try to repair it

mysql> repair table test;
+---------------+--------+----------+-------------------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                              |
+---------------+--------+----------+-------------------------------------------------------+
| airports.test | repair | Error    | Incorrect key file for table 'test'; try to repair it |
| airports.test | repair | error    | Corrupt                                               |
+---------------+--------+----------+-------------------------------------------------------+

[19 Nov 2012 5:08] Valeriy Kravchuk

Checked with 5.5.28 on Windows also. MyISAM-specific bug.

[19 Nov 2012 11:44] MySQL Verification Team

Thank you for the bug report.

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use d77
Database changed
mysql 5.5 >CREATE TABLE `test`
    ->  (
    ->  `id`       int(10)      unsigned NOT NULL AUTO_INCREMENT,
    ->  `ap_id`    int(10)      unsigned NOT NULL,
    ->  `ap_ident` varchar(16)  NOT NULL,
    ->  `type`     varchar(16)  NOT NULL,
    ->   `about`    varchar(64)  NOT NULL,
    ->  `freq_mhz` decimal(6,3) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `ap_id` (`ap_id`)
    ->  )
    ->  ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 >ALTER TABLE `test` CHANGE `freq_mhz` `freq_mhz` DECIMAL( 6, 3 ) UNSIGNED ZEROFILL NOT NULL;
ERROR 1034 (HY000): Incorrect key file for table 'test'; try to repair it
mysql 5.5 >repair table test;
+----------+--------+----------+-------------------------------------------------------+
| Table    | Op     | Msg_type | Msg_text                                              |
+----------+--------+----------+-------------------------------------------------------+
| d77.test | repair | Error    | Incorrect key file for table 'test'; try to repair it |
| d77.test | repair | error    | Corrupt                                               |
+----------+--------+----------+-------------------------------------------------------+
2 rows in set (0.02 sec)

mysql 5.5 >