From: Dmitry Lenev
Date: June 16 2011 10:02pm
Subject: bzr commit into mysql-5.1 branch (Dmitry.Lenev:3654) Bug#12652385
List-Archive: http://lists.mysql.com/commits/139374
X-Bug: 12652385
Message-Id: <20110616220304.3DFD67406A5@bandersnatch>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0385445550=="

--===============0385445550==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///home/dlenev/src/bzr/mysql-5.1-12652385/ based on revid:vasil.dimov@stripped

 3654 Dmitry Lenev	2011-06-17
      Fix for bug #12652385 - "61493: REORDERING COLUMNS 
      TO POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED".
       
      ALTER TABLE MODIFY/CHANGE ... FIRST did nothing except renaming
      columns if new version of the table had exactly the same 
      structure as the old one (i.e. as result of such statement, names 
      of columns changed their order as specified but data in columns 
      didn't). The same thing happened for ALTER TABLE DROP COLUMN/ADD 
      COLUMN statements which were supposed to produce new version of
      table with exactly the same structure as the old version of table.
      I.e. in the latter case the result was the same as if old column 
      was renamed instead of being dropped and new column with default
      as value being created.
       
      Both these problems were caused by the fact that ALTER TABLE
      implementation incorrectly interpreted both these situations as 
      simple renaming of columns and assumed that in-place ALTER TABLE
      algorithm could have been used for them.
       
      This patch fixes this problem by ensuring that in cases when some
      column is moved to the first position or some column is dropped
      the default ALTER TABLE algorithm involving table copying is 
      always used. This is achieved by detecting such situations in
      mysql_prepare_alter_table() and setting Alter_info::change_level
      to ALTER_TABLE_DATA_CHANGED for them.
     @ mysql-test/r/alter_table.result
        Added test for bug #12652385 - "61493: REORDERING COLUMNS TO
        POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED".
     @ mysql-test/t/alter_table.test
        Added test for bug #12652385 - "61493: REORDERING COLUMNS TO
        POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED".
     @ sql/sql_table.cc
        Changed mysql_prepare_alter_table() to detect situations in 
        which we some column moved to the first position or some column 
        is dropped and ensure that such ALTER TABLE statements won't
        be carried out using in-place algorithm. The latter could have 
        happened before this patch if new version of table had the same 
        structure as the old one (except the column names).

    modified:
      mysql-test/r/alter_table.result
      mysql-test/t/alter_table.test
      sql/sql_table.cc
=== modified file 'mysql-test/r/alter_table.result'
--- a/mysql-test/r/alter_table.result	2009-12-18 12:00:30 +0000
+++ b/mysql-test/r/alter_table.result	2011-06-16 22:02:52 +0000
@@ -1345,4 +1345,33 @@ DROP TABLE t1;
 CREATE TABLE t1 (a TEXT, id INT, b INT);
 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST;
 DROP TABLE t1;
+#
+# Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION
+#                           FIRST CAN CAUSE DATA TO BE CORRUPTED".
+#
+drop table if exists t1;
+# Use MyISAM engine as the fact that InnoDB doesn't support
+# in-place ALTER TABLE in cases when columns are being renamed
+# hides some bugs.
+create table t1 (i int, j int) engine=myisam;
+insert into t1 value (1, 2);
+# First, test for original problem described in the bug report.
+select * from t1;
+i	j
+1	2
+# Change of column order by the below ALTER TABLE statement should
+# affect both column names and column contents.
+alter table t1 modify column j int first;
+select * from t1;
+j	i
+2	1
+# Now test for similar problem with the same root.
+# The below ALTER TABLE should change not only the name but
+# also the value for the last column of the table.
+alter table t1 drop column i, add column k int default 0;
+select * from t1;
+j	k
+2	0
+# Clean-up.
+drop table t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/alter_table.test'
--- a/mysql-test/t/alter_table.test	2009-12-18 12:00:30 +0000
+++ b/mysql-test/t/alter_table.test	2011-06-16 22:02:52 +0000
@@ -1073,4 +1073,31 @@ ALTER TABLE t1 DROP COLUMN a, ADD COLUMN
 DROP TABLE t1;
 
 
+--echo #
+--echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION
+--echo #                           FIRST CAN CAUSE DATA TO BE CORRUPTED".
+--echo #
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+--echo # Use MyISAM engine as the fact that InnoDB doesn't support
+--echo # in-place ALTER TABLE in cases when columns are being renamed
+--echo # hides some bugs.
+create table t1 (i int, j int) engine=myisam;
+insert into t1 value (1, 2);
+--echo # First, test for original problem described in the bug report.
+select * from t1;
+--echo # Change of column order by the below ALTER TABLE statement should
+--echo # affect both column names and column contents.
+alter table t1 modify column j int first;
+select * from t1;
+--echo # Now test for similar problem with the same root.
+--echo # The below ALTER TABLE should change not only the name but
+--echo # also the value for the last column of the table.
+alter table t1 drop column i, add column k int default 0;
+select * from t1;
+--echo # Clean-up.
+drop table t1;
+
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2011-05-16 20:04:01 +0000
+++ b/sql/sql_table.cc	2011-06-16 22:02:52 +0000
@@ -6170,6 +6170,12 @@ mysql_prepare_alter_table(THD *thd, TABL
     if (drop)
     {
       drop_it.remove();
+      /*
+        ALTER TABLE DROP COLUMN always changes table data even in cases
+        when new version of the table has the same structure as the old
+        one.
+      */
+      alter_info->change_level= ALTER_TABLE_DATA_CHANGED;
       continue;
     }
     /* Check if field is changed */
@@ -6247,7 +6253,14 @@ mysql_prepare_alter_table(THD *thd, TABL
     if (!def->after)
       new_create_list.push_back(def);
     else if (def->after == first_keyword)
+    {
       new_create_list.push_front(def);
+      /*
+        Re-ordering columns in table can't be done using in-place algorithm
+        as it always changes table data.
+      */
+      alter_info->change_level= ALTER_TABLE_DATA_CHANGED;
+    }
     else
     {
       Create_field *find;
@@ -6263,6 +6276,10 @@ mysql_prepare_alter_table(THD *thd, TABL
         goto err;
       }
       find_it.after(def);			// Put element after this
+      /*
+        Re-ordering columns in table can't be done using in-place algorithm
+        as it always changes table data.
+      */
       alter_info->change_level= ALTER_TABLE_DATA_CHANGED;
     }
   }


--===============0385445550==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/dmitry.lenev@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: dmitry.lenev@stripped
# target_branch: file:///home/dlenev/src/bzr/mysql-5.1-12652385/
# testament_sha1: 69d4bad01b80b9c8a84137fd78ccac27106de029
# timestamp: 2011-06-17 02:03:03 +0400
# base_revision_id: vasil.dimov@stripped\
#   j8gxg2s2a9fqwdrp
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZbToDQABWL/gFQwAIBY9///
f+feIL////pgDS25vfd3t1dvd773HUqSreri3U3ct3e3o8r1uvdvSPb1vXnKU6QSUUxNJmiaPTT0
kxlPQh6mRoABkAAAElARiDQmVT80mqD9UNpHkho9T1AD1AGgNBKqfo0JoRNqDIeptQA0DQaGTQAA
PSASIgkxU1Pwk9GU1P0UPampoB5Mpo0yeoAAARSQTRP1NCnqfqn6jEygbU2kNANDRoNDQABJEBEe
pppoajE00m1NMUTQep+qaNih6mgANHumqgIyPTpZApnOvUDzGfv9fe87Vb1PnVxzrWiaqKGRE69D
x+pZMU9yezrvbH7RFW/F4Ks0IIjqfe9MFV1hu21J8gw8HA249wF2ftAcKGebyZ1hnq70EmuZGuHZ
aPk0t9TsMqjZK/Xn/eUIwPq++NRy5wQQWARRAbDuR5xLai65RvmFrR5fnX7L3kxtsbRyWOCN25zb
Ee9CZzBtUMJGCDoQaIPQ2UHiVV0Wa0YZQruZC7tWD6puTqOlNSawhaXjoLT+1ZCkxPQn2R2nxSCo
L0x8O8cuL2ctYmvU/M6nd2OKUx3b7WFAhFEBQfRJEqPOESYBEL/COUkjzD0GJ0aIY95BXKdWwQkY
MU1JqfMcXy2S/JnFEcrLT6CUDMFd+tAUd6fCfiKBNkTc2HFKhB/MuxgQN3yJq2F54h3mOBuwy0DG
cBmkOYimdDtVqqlNpMEw4MMaCmrUiJE5MMQaquJDexEp3XkpxgQegMIrI3FSm2mWBUrFi3VFC+Pk
glLMZhpTayLyqXfUMzByECxrOhrFZXynkaST6TUWJRIhOkIYjPYKjpB5AhedYi1SXPGtLQ9B0jkQ
IPoG8eDByv6pTCtLO31rv+DRxR2h6kWgA9UjG2NsbbbY2xtjnGskvo+eQiOv6Tnax+DqM5gSlJgF
OdpHgAPM6HgL8ACwibLisoZcF0O/VEmLxloVjvUjx7z32Z+1Rj8WMctw7Rx6LPNKwfvrdGhtpOSK
jElmuf8wiIfEZ1hVlZvu7HFWrKpkVltPlXQFxhgmGxipThNT1YPEopUiUCj4ROUiCSV0lpkIyhCl
riRGA46qiCvGP/B4mikfhPBktYyYXoeFOm918r3VU4l97kjM65HjKolSu1RpXeSzPgXC9ketiFNH
HAkNr4rWa1VD6li8keeNLCxJLuYRd6uGG/iXRPbx1UCWrJcxGig8yWmzUHQf0jMn7eurszURFOqK
Zk4A14jjE4HKkxERcbxaTRKtnYQ0e5youWJc4wFB/ORyiLuVmBevngcDqTXippgjh4KIjondMhko
Kpwu0Y5MWODSQUTTiOWJGwhxv0FpZZbJFzHUJ4nYsLy4wVOG2vBdu7VkKR2CHD3FnlnnfBmTqTtt
SoO6rINFtsRfIl7/epxGU48UOrRkmLZuTBERZlvjvT7p2FXcTt5rBPutzgXD+SYlcT2Qc0XPVdqI
oVCzaiUGEQFphcsWXbtIpbPDA31K+UqzL9xGGRdWphsxEcCzFlCfrOtz4hpqmNpQhH045GPnIUxi
R1JETOlwywsyGygpPhdzgiuWhbIzIN4dVJQEd+D7H2CoOQIXmNPc2KJtsK3l8XgcriOMTLUmX5uO
q0yffmry9w9ETeCZeuXIqVpdR1bR5fmrOiGrUWkVadeuNCzPRWq73g4OEKynJE08WcIpe+QsKOys
d53Qm11jMekfD0BsKb0WPlW2GND3PM94nZ8BeA50Su06oyQp7T8p+wuAkljwlqGkwTG25R4gH20k
Rn/yiWhYMwsBCKrkLPinTUWfr+n03JnShTLTmSDEkkciQ1OL9EPSwu8kGocJTRNH/C+oxRMqAaAG
gDvpcesByeOQxfwskOBqHlBDcQIIvOSHI9SJFqyKIihPQ9DUZDkSFJD10NEPLURREWQsUxI6WTjS
8AwpUhbCxQBYlaUl0m1peBHHqUL2poOwnx9cJVKIYipJ6Weg7ybgfWrKN0ifs5L4r5DfT9hpARhf
gLb9cUl8l8yeWpmoNYcoFxYok3M+5kB+ZwPDwXdEKO9F1fJGI0MFl9DA1UFrEuO4gbkj+5WB6EHA
alvZTRgp3H30E3eGg1LBDqL/VUPnyD+FtBNawmfroZHzPf1n0XzWdx2iBMhY828InANT6oQMDYn8
hcOyHQGU0nk84k0DDAPIZiLi4ZHtHO7BhU6smZ/2NCdRKriGktSolrYgbORAYQtDMBFSLEoLpjWI
cCWwu0QoNY60KDROVKFKNSwnmUGMu7s727BKiohjyRSaIeetiTW8Mg4lD4zAxL8wsDvOCCZQlvEH
CXu7S+F7XIXBDdOYXsGMIdrTY2fj6erHQ5HiugyjAzCc3PUUgwOkYKMQaIydMqQiBYYhAY0i4wJ5
JNFQKnWMm1YzKEEMTVnH2q8MQBngIL7cplnKKH4cZDCnpREmIqTYdlb1kWMexx8zaK9AxZZUxzkU
W04HiJbuAiYtRMtqRVX4dJPZToW5WZDnRtJNYXZ7nb2Ov1qzHA/RgcobGDPux+3T6Nh6vI2iuDtR
etydhnJMT4cEZHxoIb3i4qQNcmLyCDM3KAm5uAdTxxkRuRAs16kc16RUYnTI8iIXn0GBO2tKg1eQ
K7aspCiiT9YLFw2sxwpC6EO+bDQ8T2SkpOEpm1hUUeE1bRqmG40S6Xd4GGYHIXpIXDBTtZDLM8Ql
5T3IYSYZeC89/9Ig2Ic3cUAKrNdU5AOahv7KYG2stNbmzaHRMICAhg0sbnK09sC8ke8hbDokuJnE
gSbEmxZs1vsBjKmSwoXGdZhL2lWYdi1x9UCbSyZKWyO46nG8ZKjRx1cwlZCFhEFzrdDSigYsclM2
L0LkLneRKJtl6QXDOomg7ExZSVxmYlF+UXkdrNChgC17ej323IYMEyv2n4DElPB2W3WYC8iRzDFF
jYhMtOl132OVwTWxF1SK2nK8pDCqU6xgiGSqPNgFyhu2lJX3C4FLFAUjv10PNBUuYcBlDluCIIbr
3aNbCYIUdgNySYQ6BjzNAnRJv1CNq2Io3daoQHSGL6WC0mzlUo1IS4OA6Z1jOHQZtjuKBgDOxsVS
PgmdMMMmSNUhOG8MQc+pwEc8LrkJfdxLkxdeEDTxVqf5Jrsu8S7UMlUcGs4h3RAxkShxOZtNM2FQ
9wlF+xVMUSmDLSxMVRjTJIYKiJSRQFIQY6TrXYsD2m8xW7csNQXYxmSzGDGDFJChNoGHyc9xLmlw
HFrCRcdE0TxhqYI2YC3QltATUBMTagPRhTKny84EVMqhN2YCJDB+SSxFBRIuSIapSrOiqmKMcSZD
sY8858ISw5GIPEtKEU4/KCp3Hg6ZDDKIbyWKdiur7lvFQ3KZoGNBVe4vDIRb2l9iWMZnNTog1b3j
VyxUPZXVcthL3lxi9cLO2EgNki9gKAgLuigveYlbCtaJshOwEh41zjdb7ocgqNDuQ3n5nhMtKymP
KNA3uPTJml5vKLMy3iqxZX0QjaKSBjehDGkPwU7ivkm8zIw6lJFGEQMGUHgQoAdY55VBsUME8ad3
cDmsDC9Xr8Rh7xGRrbeLPEmF/hENygEUxOizRqFeSVF0yDNty1nUoL6Lo2t3d3idW5Sykqslw1pg
aRsaGNYI2RoFOwesailn8CBNqG65mw/LmJywzGkiRNBSoOBMqVzInCZWlw6jdUpQmycgmXZC4vNk
iwg+xy06KeInQLJBsxITSVBWqFp2KYzhFTnJBtJPUXgGZEeAnzVjcDFykkamQBBlFHWUdmZbOVBl
4CKRV95gMaxOos4VBMOGhkXEcWawzOUvi133UTZhUm9y91DXxuceKsMIFiLBqGCFgcfJF6rfoWyC
ZIkjeqEIjBtUkeSt5tIXVTlQIsMHM7bqhknR9jRYWg4bkKm7vOWkNLguaDO4mS6zRM3yWOTASR53
6GvkL2YSWxkcpGxyhvWhpuLXzn/F3JFOFCQltOgNAA==


--===============0385445550==--