Bug #19386 Multiple alter causes crashed table
Submitted: 26 Apr 2006 20:08 Modified: 9 Jun 2006 16:00
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.1.10-beta-debug OS:Linux (SUSE 10.0)
Assigned to: Jani Tolonen CPU Architecture:Any

[26 Apr 2006 20:08] Peter Gulutzan
Description:
I create a MyISAM table with two columns.
I change the columns with ALTER ... MODIFY.
The data in the table goes bad, then I see a message:
"Table ... crashed and should be repaired".

How to repeat:
mysql> create table session_status (variable_name varchar(20), variable_value bigint);
Query OK, 0 rows affected (0.27 sec)

mysql> insert into session_status values ('Select full_range_join',5);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> alter table session_status modify column variable_value float;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table session_status modify column variable_name varchar(25);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table session_status modify column variable_value float;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from session_status;
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| Select full_range_jo |   -2.87352e-16 |
+----------------------+----------------+
1 row in set (0.00 sec)

mysql> insert into session_status values ('Select full_range_join',5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from session_status;
ERROR 1194 (HY000): Table 'session_status' is marked as crashed and should be repaired
[26 Apr 2006 22:17] Miguel Solorzano
Thank you for the bug report. This bug was introduced between the
below changesets;

miguel@hegel:~/dbs/5.1> bk changes /home/miguel/dbs/mysql-5.1-new/ | head
ChangeSet@1.2368, 2006-04-18 17:16:27+02:00, kent@mysql.com
  Merge

mysql> use r5;
Database changed
mysql> create table session_status (variable_name varchar(20), variable_value
    -> bigint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into session_status values ('Select full_range_join',5);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> alter table session_status modify column variable_value float;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table session_status modify column variable_name varchar(25);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table session_status modify column variable_value float;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from session_status;
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| Select full_range_jo |              5 | 
+----------------------+----------------+
1 row in set (0.00 sec)

mysql> insert into session_status values ('Select full_range_join',5);
Query OK, 1 row affected (0.01 sec)

mysql> select * from session_status;
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| Select full_range_jo   |              5 | 
| Select full_range_join |              5 | 
+------------------------+----------------+
2 rows in set (0.00 sec)

mysql> 

--------------------------------------------------------------------------------
miguel@hegel:~/dbs/5.1> bk changes /home/miguel/dbs/mysql-5.1-new/ | head
ChangeSet@1.2367, 2006-04-27 01:41:27+04:00, sergefp@mysql.com
  Merge mysql.com:/home/psergey/bk-trees/mysql-5.0
  into  mysql.com:/home/psergey/mysql-5.1-merge

ChangeSet@1.2362.2.2, 2006-04-26 15:38:11+02:00, mskold@mysql.com
  Added test for violation of uniqueness constraint at create index

miguel@hegel:~/dbs/5.1> bin/mysql -uroot r5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table session_status (variable_name varchar(20), variable_value
    -> bigint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into session_status values ('Select full_range_join',5);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> alter table session_status modify column variable_value float;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table session_status modify column variable_name varchar(25);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table session_status modify column variable_value float;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from session_status;
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| Select full_range_jo |   -2.87352e-16 | 
+----------------------+----------------+
1 row in set (0.00 sec)

mysql> insert into session_status values ('Select full_range_join',5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from session_status;
ERROR 1194 (HY000): Table 'session_status' is marked as crashed and should be repaired
mysql>
[11 May 2006 10:51] Sergey Vojtovich
Even simplier testcase for 5.1:

create table session_status (variable_name varchar(22), variable_value bigint);
insert into session_status values ('Select full_range_join',5);
select * from session_status;
variable_name variable_value
Select full_range_join        5
alter table session_status modify column variable_name varchar(25);
select * from session_status;
variable_name variable_value
Select full_range_join        -6510616266875600896

Data gets corrupted after increasing size of varchar column.

Wasn't able to repeat this bug with 5.0.
[15 May 2006 16:58] 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/6400
[6 Jun 2006 20:02] Paul Dubois
Need three-part version number for the fix. Thanks.
[7 Jun 2006 19:33] Iggy Galarza
Fix was added for version 5.1.11
[9 Jun 2006 16:00] Paul Dubois
Noted in 5.1.11 changelog.

Altering a VARCHAR column in a MyISAM table to make it longer
could cause corruption of the following column.