From: Anurag Shekhar Date: October 15 2009 9:28am Subject: bzr commit into mysql-5.0-bugteam branch (anurag.shekhar:2820) Bug#46599 List-Archive: http://lists.mysql.com/commits/86912 X-Bug: 46599 Message-Id: <0KRJ001EKUCGBU80@mail-apac.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Boundary_(ID_h71rV4MPOiyS1NY2V9/R1Q)" --Boundary_(ID_h71rV4MPOiyS1NY2V9/R1Q) MIME-version: 1.0 Content-type: text/plain; CHARSET=US-ASCII Content-transfer-encoding: 7BIT Content-disposition: inline #At file:///home/anurag/mysqlsrc/mysql-5.0-bugteam-46599/ based on revid:joro@stripped 2820 Anurag Shekhar 2009-10-15 Bug #46599 ALTER TABLE causes inconsistent values for foreign keys While altering table foreign key constraint checks are suppressed which causes this behaviour. To fix this problem modification in a field which is part of foreign key is blocked. Work around to alter such field is to drop the key and recreate it after altering the column. A new error message is introduced which suggests uses to refer manual for the work around. @ mysql-test/r/innodb_mysql.result updated result file. @ mysql-test/t/innodb_mysql.test test case for bug#46599. @ sql/share/errmsg.txt Added a new error message informing caller that column participating in foreign key can't be modified. @ sql/sql_table.cc Added a new check in mysql_alter_table to check if the field being modified is part of a foreign key. An error ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD is returned if it is. modified: mysql-test/r/innodb_mysql.result mysql-test/t/innodb_mysql.test sql/share/errmsg.txt sql/sql_table.cc === modified file 'mysql-test/r/innodb_mysql.result' --- a/mysql-test/r/innodb_mysql.result 2009-06-15 15:29:26 +0000 +++ b/mysql-test/r/innodb_mysql.result 2009-10-15 09:28:51 +0000 @@ -1315,4 +1315,14 @@ id select_type table type possible_keys 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 DROP TABLE t1,t2; +# +#Bug #46599 ALTER TABLE causes inconsistent values for foreign keys +# +CREATE TABLE t1 ( +a VARCHAR(2), KEY(a)) ENGINE=InnoDB; +CREATE TABLE t2 (a VARCHAR(2), +FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB; +ALTER TABLE t2 MODIFY COLUMN a VARCHAR(2) NOT NULL DEFAULT ''; +ERROR HY000: Can't modify a :part of foreign key t2_ibfk_1, please consult the manual for workaround +DROP TABLE t2, t1; End of 5.0 tests === modified file 'mysql-test/t/innodb_mysql.test' --- a/mysql-test/t/innodb_mysql.test 2009-06-15 15:29:26 +0000 +++ b/mysql-test/t/innodb_mysql.test 2009-10-15 09:28:51 +0000 @@ -1073,4 +1073,18 @@ explain select b from t1 where a not in (select b from t1,t2 group by a) group by a; DROP TABLE t1,t2; +--echo # +--echo #Bug #46599 ALTER TABLE causes inconsistent values for foreign keys +--echo # +CREATE TABLE t1 ( + a VARCHAR(2), KEY(a)) ENGINE=InnoDB; + +CREATE TABLE t2 (a VARCHAR(2), + FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB; + +--error ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD +ALTER TABLE t2 MODIFY COLUMN a VARCHAR(2) NOT NULL DEFAULT ''; + +DROP TABLE t2, t1; + --echo End of 5.0 tests === modified file 'sql/share/errmsg.txt' --- a/sql/share/errmsg.txt 2009-07-17 08:43:53 +0000 +++ b/sql/share/errmsg.txt 2009-10-15 09:28:51 +0000 @@ -5651,3 +5651,5 @@ ER_XA_RBDEADLOCK XA102 eng "XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected" ER_TOO_MANY_CONCURRENT_TRXS eng "Too many active concurrent transactions" +ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD + eng "Can't modify %s :part of foreign key %s, please consult the manual for workaround" === modified file 'sql/sql_table.cc' --- a/sql/sql_table.cc 2009-09-07 16:35:37 +0000 +++ b/sql/sql_table.cc 2009-10-15 09:28:51 +0000 @@ -3416,8 +3416,41 @@ view_err: List_iterator alter_it(alter_info->alter_list); Alter_info new_info; // Add new columns and indexes here create_field *def; + List_iterator create_it(alter_info->create_list); /* + Check if any field in change list is part of foreign key. + */ + List f_key_list; + /* + Retrieve list of foreign keys + */ + table->file->get_foreign_key_list(thd, &f_key_list); + List_iterator_fast f_key_it(f_key_list); + FOREIGN_KEY_INFO *f_key; + while ((f_key=f_key_it++)) + { + List_iterator_fast field_names(f_key->referenced_fields); + LEX_STRING *lex_field; + while ((lex_field=field_names++)) + { + def_it.rewind(); + while ((def=def_it++)) + { + if (def->change && !my_strcasecmp(system_charset_info, + lex_field->str, def->change)) + { + /* + Cann't modify field if its part of foreign key + */ + my_error(ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD, MYF(0), def->change, + f_key->forein_id->str); + DBUG_RETURN(TRUE); + } + } + } + } + /* First collect all fields from table which isn't in drop_list */ --Boundary_(ID_h71rV4MPOiyS1NY2V9/R1Q) MIME-version: 1.0 Content-type: text/bzr-bundle; CHARSET=US-ASCII; name="bzr/anurag.shekhar@stripped" Content-transfer-encoding: 7BIT Content-disposition: inline; filename="bzr/anurag.shekhar@stripped" # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: anurag.shekhar@stripped # target_branch: file:///home/anurag/mysqlsrc/mysql-5.0-bugteam-46599/ # testament_sha1: 8bd74e28e3ff27ada18509400ee4de51abcdb543 # timestamp: 2009-10-15 14:59:04 +0530 # base_revision_id: joro@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWYiZdiYABTF/gF3wAAR7//// f+/f4L////pgC675Jdw4BkutQd26Hd33mulO3mK4t73vbbZFPbuwkiQU9NJtKNqGyZNMJphTwifq jTR6gBk0ZDTQGmgkoiYDJpU/ajUZIfqjNR6TQABo0Bpo0aBoBp6hzTEZGTTJoBkNGQyZAAADI0yN AwhkCQogp5BPQmSNqZGk9NJ6TQaGgNB6gaaANqBoDakgNCaaaNU9qnk1NqeE0I9NNR6IDQAAAAAJ JATQTCm0CaaaBNBpkKeoeo9NJp6jQbTKP1QaHqFmdCKndwluu513L8z45pHa06+30MP67P3wV6n+ ba7YQhDfpj35P+pOq3U31ceFpbtkeau63o4ZOPDjWKGFHTLGzx2zVhwFZN8636csJc2bkLxZLwvK pu5Ieq+fPzequt7s3+MHCRmtfGP5RQkWJIewXMD9I4PB/USTZnjP/n8zgQWR9B56Xiefrh4kekO4 DbbG02bOwW/b6rcbO5Q392F9hfdZY6wjGyozmOnsx02nNsPm9BzhyF35/kcpRbyWnAYh0TX8gY+h iK/ptN/R6TPr6+SOz/RJ02jhZHBWWf9gWwk48NROMyBGR6coUhEKmuXoMVZBxS9zGPaNkM03OBXu zGlmjPHTA4IxDMGM/yWKt/A2sx6LEr6hYE+yNsgxuQTp3uicplGihmdUBTJVzQJXa5y18qmUFUil HUOLNiylLwNevXVOFtMsOHLNfCKPsaDdIJ+He34rDJI43GZXHdcbHAy+RiB0WurSK5njRSzWX899 oQffhxcbJWzSYuhOEAtSz1PDREepctATrYjNm5WfJH34HpqvXLYYFOItpBenBO+hVq2lmrGvwY1K 66U6bg2ZohSDBUPbFxFXHwXrM79fY99Xdi6qzN/F6KWjIBkY+ZC1PQ5HxXDnYU76Iwr+iGasTsMc OSxAamCPV74KEOxn9vBUet4yBR5mUBBHwBwXQC4UiDx4mFyj2LBx0Exij4FZMmyAkTBWJ9aHj6qV H5/2VlZxKi09jy63EDSDK/Z9Ygq421Jkkyl4wDplRYQxVeDs5FbiRXFOoPVH9KLVAYE2otAsUKs7 euhAhSZt5tA/b2LCOosRrMSO7QYnpDIenIUwWL9N7OtDl5hOMlKCxQlUZ20UeRPJr7VuPdvnuGk+ BoG7agrJixJ1peskR7VOCcTFZRmGSTJRaXFA4uP84rIryphrBVb16wqBb95gxKwiq4Ku81MZn609 6OE3EoIhYdRhxnok43lw7RKVSxNRMsLDQsebu3OBZaL9OECV+nLRCnXmWIxgpzgrUzYqIqrlewio mospeNGHEF8sxqWxpeOLSzovkO3qVaLdg6ZCjNIPvMT5GvWm3TM2qx7NXGxgtY7wWyGsW4aWVtFa YtVNQoU6NqEwKxVkRhO4UTbYYtl7SumheNgJwmKx9l/UuJxtmiW2J2byyBZNTE645drx6dnwWcfU sVfSxkdlMa+BDKDSSkzceR9ZuUDVUykMIb/hAW84KZwKzlBeRa46Ft3azFxqczbFYXFtabpmaQUk 455AvPCnIWtO24wHEyrlpzRiPLkzkXkHJK1i4YFIcTcOJtYSJKYSSg2wnaUHuKF0SgUadHWg9xZN QByKoPFuIhKDkTk+1qnpSlCmB73e0yZdLHrkSgZ9py8joZN3vA+LK7bUOZNIaXeFBCNDiG5A3q8W PkRDVCV4NNJzHNFJoS2BljK02LMqAtf2pG1Q7A/gXqFxB1nkLtPDwJtoZ84edId/N7j3h8T9wrCg WO8fd8g1B8YnAVRrCSR9bQtCOI1I/gMKQoRmJiQ9ecmrP6kcOAZQeuWuQK+ItFITIMbhRvDEViDs CtYHyCYPSxJB60jZfcHMjWUUYDigNafUusMUDBWEjIGJpYqoWRRPQSFcqIoCkFCCCBZsLuQsjBgm xIdkVDkisHo/obZyDVXneUKFtgptQMBjqycDwZc9ULSRVSqB3u05ejTkOdY9VPo0xAygDoaR5aWd UxWNVSq2usReW2+991C1mdhHhRHaXMf1voEgp6EWDEWG+PHci9x8PURDnAe0856KaCq7KJSgmCUb vnqPbLJZrcvCT40lPJtVGK0UpDvny8i6EMlck+TITmMwXz9EGaEEsS86y2LEA4yRLFbeffAKbl1r 2QX4DdAZdBoPxIHYfj27vvqJFQis3GCuPkboDDGl69+6pQOGa5aOPKr8lIP0oUNrFq3i85CBkiXL dnOk7gXpMzpKzJde5yyr3jVGxHzXWX5yK4MQb8LGNvf31VbFblFOKR1NAIeCap9cxv6FpV1x0ttt yNuZxwshiESOe4zKzqKEhMTVKhWkXxBMRYmdNRkI+08hxPIevMb7/84WIrA9nnVmsXBRxVrOOxwR 8I4EdD3azCGvotpYRUYhk3uD2j37ztHCYFTw8tfgUmbgpnwZUFI88WORhsUDQSa8ydAyCZrUQzhc XdHGuT/VCCfoG87FKKbKA81sJN3ZK4SsSKqJPw4cljGSUUFLR0WQItIutXCuSw7DZTXmRKShrNJv gGWZBHSgrI/lpa0jkTv1q9B20kYYK/AzCYuMysIWMwFs0AoG6YSWOQdfzL1NZzP1w3CUuonHK6L6 uOpUSg1R9Qar8eoPGDisaAVgxQV9KJKRkE8ziGKs2T6yo1SGgjcOcI1Cz2bCPQZkh0iYZLUwk7TX cKNhvF5NCIrlvPN+kmvvW7lFxLISJy5ioitQmFagcnRZnFsHSMA5FnbjPQ8R2kf6U3IKRbW6u4rw iUzrCpsF2lQk3tqRASCWeMkpxBShZzGImOF5cSnSFOnlTSsWZAh01KKOTLeT8nE83Ocnchx77e69 3HQwjhz5xQ0nEEj+HtSZTUDMBtOuAS47OdfDici08xs2m+yxbzUiC8uECGOSNTaRhgGC6gk9LX5u gp5jJbLV4K8zTCyQDA0RTBdiqgGEuo5GE4bSktQwwmEUJ1v77Oi1YRV4m2PYOVoIrk9hQXkMVnbt WIwRI9CrKmIVCYiLwZMm2biyruFeMGq3Md12lgRhBDlS7qUyI66lV/05tyo1mIi2g0bls80lEibt a8E3CC3qTRldFe8RZj2ebNcsIyNpTzqT0pMEkNf7B68E30CIjESY4XsrLRQsEmTJKUvsIohHbc6f gNB52gLmUtulSTbjhgQoUZjvWm5LvpIyBeSCqRisBMzgxp6BEEvLcnB2r1RW3uqC3Ahh3ICt78Ed 3d5BPTDDBSnTNKVFGGAlV2NQOgnnGUWAekTbeA6t+8UkRkmL4I3qsVlofNpNDBskIGg8GOVhY7UK EKiUStyqpFOW8NNG8tQlGQLRH7kNq73BoZPhObV2uKwwYO1Zb0ZfmisKFe14potC0QwDE2Cfenrc H/uvnLhNXNRuPF2V9ilg3yc/KBz8OkKU1ovyvRVGoXb40LQkbVUPf0i7q8qL3wSSSSTxbklByjVa GhBi2Ng0PIagUiuS7pnz6LuYdRo9oQXV9tmdn5OpDufGK1CfAkmd0SHAxa9QUFoji8tQuLs7oZzD EHQMQHfi2i9Uusao6UvgeGbmZ0zLWG0oTMBMAmtge5z7FQsKQIs6WpdMDXQmhubDChFdyqsiQJpl QqCF3CQiIVVeWEozyqf3LNG1lwIjCDMNji/WoXCW2AbOX7thKXnT6ia9K9G+pfoWIvJ01MYKg4yR iAedTjIXWlJ5W+eUeuAhM+MrzRlOS8a1BVA2XNsfFppUaIyGUFSUy5VgC3rjW+SoFQg5obS8Nqmu RHdJjTXcyyvoslns2/Bme//4u5IpwoSERMuxMA== --Boundary_(ID_h71rV4MPOiyS1NY2V9/R1Q)--