| 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: | |
| 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 22:17]
MySQL Verification Team
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.
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