MySQL Bugs: #61673: alter table add/drop column with same name corrupts data

Bug #61673	alter table add/drop column with same name corrupts data
Submitted:	28 Jun 2011 14:04	Modified:	29 Jun 2011 9:18
Reporter:	Shailesh Humbad	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.55	OS:	FreeBSD (8.1 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	add column, ALTER TABLE, DDL, drop column

Description:
Adding and dropping a column with the same name corrupts the table data.

How to repeat:

> create table t (f1 text, f2 text);
Query OK, 0 rows affected (0.00 sec)

> desc t;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| f1    | text | YES  |     | NULL    |       |
| f2    | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

> insert into t (f1, f2) values ('ab','cd');
Query OK, 1 row affected (0.00 sec)

> select * from t;
+------+------+
| f1   | f2   |
+------+------+
| ab   | cd   |
+------+------+
1 row in set (0.00 sec)

> alter table t drop f1, add f1 text;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

> select * from t;
+------+------+
| f2   | f1   |
+------+------+
| ab   | cd   |
+------+------+
1 row in set (0.00 sec)

> status
--------------
mysql  Ver 14.14 Distrib 5.1.55, for unknown-freebsd8.1 (x86_64) using  EditLine wrapper

Suggested fix:
After the alter table statement, the table data should be as follows:

+------+------+
| f2   | f1   |
+------+------+
| cd   |      |
+------+------+

Thank you for the bug report.

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.58-Win X64-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql 5.1 >use test
Database changed
mysql 5.1 >create table t (f1 text, f2 text);
Query OK, 0 rows affected (0.14 sec)

mysql 5.1 >insert into t (f1, f2) values ('ab','cd');
Query OK, 1 row affected (0.02 sec)

mysql 5.1 >select * from t;
+------+------+
| f1   | f2   |
+------+------+
| ab   | cd   |
+------+------+
1 row in set (0.02 sec)

mysql 5.1 >alter table t drop f1, add f1 text;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.1 >select * from t;
+------+------+
| f2   | f1   |
+------+------+
| ab   | cd   |
+------+------+
1 row in set (0.00 sec)

mysql 5.1 >exit
Bye

C:\DBS>55

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, 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 test
Database changed
mysql 5.5 >create table t (f1 text, f2 text);
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 >insert into t (f1, f2) values ('ab','cd');
Query OK, 1 row affected (0.01 sec)

mysql 5.5 >select * from t;
+------+------+
| f1   | f2   |
+------+------+
| ab   | cd   |
+------+------+
1 row in set (0.00 sec)

mysql 5.5 >alter table t drop f1, add f1 text;
Query OK, 1 row affected (0.18 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.5 >select * from t;
+------+------+
| f2   | f1   |
+------+------+
| cd   | NULL |
+------+------+
1 row in set (0.01 sec)

mysql 5.5 >

Notice it wasn't repeatable with 5.5.XX version.

I am almost sure that this is a duplicate.

Peter
(not a MySQL person)

I searched the bug database for closed "ALTER TABLE" issues, and found several related ones where MySQL gave an error or crashed, but none where MySQL simply corrupted the table data.  Since it is not an issue in 5.5, maybe someone can confirm in the latest 5.1.x branch and apply a patch if needed.

this reminds me of bug #61493 !

You should always put an explicit ENGINE= ..  else we never know what the engine is.

Hello Shailesh!

This issue is fixed by the same patch as bug#61493, which was fixed in 5.1.59, 5.5.15, 5.6.3 versions of server. Therefore I am closing this bug as a Duplicate.