MySQL Bugs: #61493: Reordering columns to position FIRST can cause data to be corrupted

Bug #61493	Reordering columns to position FIRST can cause data to be corrupted
Submitted:	12 Jun 2011 15:39	Modified:	21 Jun 2011 19:00
Reporter:	Rowan Beentje	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.58	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
If a MySQL 5.1 table has columns of the same type, reordering a column later in the table to the FIRST position leaves any data already in the table mapped to its previous column position, not to the field name.

This can cause significant data corruption.

This appears to not be the case if mixed data types are stored in the table, or if columns are moved to non-FIRST positions.  Only replicated on MySQL 5.1 (5.1.29, 5.1.44, and 5.1.57); could not replicate on MySQL 5.0 or MySQL 5.5.

How to repeat:
This is easy to reproduce.  Create a table with the following syntax:

CREATE TABLE `idtest` (
  `id_a` int(11) DEFAULT NULL,
  `id_b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Add some simple data:

INSERT INTO `idtest` (`id_a`, `id_b`) VALUES (1, 2);

At this point, data is where you would expect it:

id_a	id_b
1	2

Then move id_b to be the first column in the table:

ALTER TABLE `idtest` MODIFY COLUMN `id_b` INT(11) DEFAULT NULL FIRST;

If the data is now selected from the table, it is no longer in the correct column - data has been corrupted:

id_b	id_a
1	2

Moving id_b back to the end of the table will preserve the now-incorrect mapping:

ALTER TABLE `idtest` MODIFY COLUMN `id_b` INT(11) DEFAULT NULL AFTER `id_a`;

id_a	id_b
2	1

Suggested fix:
When columns are reordered, data within those columns should always be preserved.

An observation.  The last ALTER behaves differently with the two 'variants'

ALTER TABLE `idtest` MODIFY COLUMN `id_b` INT(11) DEFAULT NULL AFTER `id_a`;
ALTER TABLE `idtest` MODIFY COLUMN `id_a` INT(11) DEFAULT NULL FIRST;

So it is an issue with the syntax variant "ALTER TABLE .. MODIFY COLUMN .. FIRST" and not "ALTER TABLE .. MODIFY COLUMN .. AFTER .." 

Peter
(not a MySQL person)

a workaround is to use "old_alter_table=1" in my.cnf

Thank you for the bug report. Verified with current mysql-5.1 from bzr on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.58-debug 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> CREATE TABLE `idtest` (
    ->   `id_a` int(11) DEFAULT NULL,
    ->   `id_b` int(11) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO `idtest` (`id_a`, `id_b`) VALUES (1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from idtest;
+------+------+
| id_a | id_b |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `idtest` MODIFY COLUMN `id_b` INT(11) DEFAULT NULL FIRST;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from idtest;
+------+------+
| id_b | id_a |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

Noted in 5.1.59, 5.5.15, 5.6.3 changelogs.

ALTER TABLE {MODIFY|CHANGE} ... FIRST did nothing except rename
columns if the old and new versions of the table had exactly the same
structure with respect to column data types. As a result, the mapping
of column name to column data was incorrect. The same thing happened
for ALTER TABLE DROP COLUMN, ADD COLUMN statements intended to
produce a new version of table with exactly the same structure as the
old version. 

CHANGESET - http://lists.mysql.com/commits/139515