MySQL Bugs: #3658: ALTER TABLE corrupts table

Bug #3658	ALTER TABLE corrupts table
Submitted:	5 May 2004 10:13	Modified:	27 May 2004 13:08
Reporter:	Johan Andersson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	mysql-4.1-alpha bk	OS:	FermiLinux 3.01
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
ALTER TABLE (drop index, add index, add column) works really bad -> corrupts tables by resetting values to the default values specified in the "create table ..." statement, but keeping the number of rows as it was before issuing ALTER TABLE

How to repeat:
1. create table foo(a integer not null, b integer not null) type=ndbcluster;
2. insert into foo(a,b) values(1,2);
3. ALTER TABLE ADD COLUMN c int not null;
4. select * from foo; -> returns 0 in all columns.

Comment:  alter table is really slow (2.5s)

Corrected synopsis

The problem occured because field->query_id was not set while data was read from source table. Thus "optimizing" away the read of non PK columns.

Changeset 1.1811

I've experienced a little bit scenario with alter table, probably it's not directly related to table corruption.
check mysql session log:

mysql> show table status;
+--------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name   | Engine | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+--------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| mytab2 | MyISAM | Fixed      |   13 |             25 |         325 |    107374182399 |         2048 |         0 |             14 | 2004-05-26 20:31:47 | 2004-05-26 20:32:21 | NULL       | latin1_swedish_ci |     NULL |                |         |
+--------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> alter table mytab2 engine=NDB;
Query OK, 13 rows affected (1.12 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select * from mytab2;
+------+-----------+
| col1 | col2      |
+------+-----------+
|    7 | xdddxxsss |
|   13 | xdddxxsss |
|   12 | xdddxxsss |
|   11 | xdddxxsss |
|    8 | xdddxxsss |
|   10 | xdddxxsss |
|    6 | xdddxxsss |
|    2 | xdddxxsss |
|    4 | xdddxxsss |
|    5 | xdddxxsss |
|    3 | xdddxxsss |
|    9 | xdddxxsss |
|    1 | xdddxxsss |
+------+-----------+
13 rows in set (0.15 sec)
mysql> insert into mytab2 (col2) values('xdddxxsss');
Query OK, 1 row affected (0.02 sec)

mysql> insert into mytab2 (col2) values('xdddxxsss');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> select * from mytab2;
+------+-----------+
| col1 | col2      |
+------+-----------+
|    0 | xdddxxsss |
|    7 | xdddxxsss |
|   13 | xdddxxsss |
|   12 | xdddxxsss |
|   11 | xdddxxsss |
|    8 | xdddxxsss |
|   10 | xdddxxsss |
|    6 | xdddxxsss |
|    2 | xdddxxsss |
|    4 | xdddxxsss |
|    5 | xdddxxsss |
|    3 | xdddxxsss |
|    9 | xdddxxsss |
|   -1 | xdddxxsss |
|    1 | xdddxxsss |
+------+-----------+
15 rows in set (0.01 sec)

mysql> desc mytab2;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| col1  | int(11)  |      | PRI | NULL    | auto_increment |
| col2  | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
mysql> show table status;                                     
+--------+------------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| Name   | Engine     | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+--------+------------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| mytab2 | ndbcluster | Fixed      |  100 |              0 |           0 |            NULL |            0 |         0 |              5 | NULL        | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+--------+------------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

It's the autoincrement counter that is not producing a "new" value when the insert is made.

Patch for teh ALTER TABLE problem pushed to mysql-4.1 repository and verified.