From: Anurag Shekhar Date: October 15 2009 9:58am Subject: bzr commit into mysql-5.0-bugteam branch (anurag.shekhar:2820) Bug#46599 List-Archive: http://lists.mysql.com/commits/86917 X-Bug: 46599 Message-Id: <0KRJ00HMFVPC8280@mail-apac.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Boundary_(ID_Wplq5s1dslieTGXrq5+KhA)" --Boundary_(ID_Wplq5s1dslieTGXrq5+KhA) 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.result updated result file (new error message for one test case). @ mysql-test/r/innodb_mysql.result updated result file. @ mysql-test/t/innodb.test This patch blocks all modification in fields participating foreign keys so error message for one case is different now. @ mysql-test/t/innodb_mysql.test Added 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.result mysql-test/r/innodb_mysql.result mysql-test/t/innodb.test mysql-test/t/innodb_mysql.test sql/share/errmsg.txt sql/sql_table.cc === modified file 'mysql-test/r/innodb.result' --- a/mysql-test/r/innodb.result 2009-04-24 11:57:53 +0000 +++ b/mysql-test/r/innodb.result 2009-10-15 09:58:15 +0000 @@ -2998,7 +2998,7 @@ INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; ALTER TABLE t2 MODIFY a INT NOT NULL; -ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150) +ERROR HY000: Can't modify a :part of foreign key t2_ibfk_1, please consult the manual for workaround DELETE FROM t1; DROP TABLE t2,t1; CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) === 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:58:15 +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.test' --- a/mysql-test/t/innodb.test 2009-04-24 11:57:53 +0000 +++ b/mysql-test/t/innodb.test 2009-10-15 09:58:15 +0000 @@ -2047,7 +2047,7 @@ INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; --replace_regex /'\.\/test\/#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error 1025 +--error ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD ALTER TABLE t2 MODIFY a INT NOT NULL; DELETE FROM t1; DROP TABLE t2,t1; === 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:58:15 +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:58:15 +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:58:15 +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_Wplq5s1dslieTGXrq5+KhA) 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: 3ba44bf586089488b5bc1d8954adf0a0e90a231d # timestamp: 2009-10-15 15:28:24 +0530 # base_revision_id: joro@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSAcZPwABqP/gF3wAAR7//// f+/f4L////pgDi76EpbDQB1X3sAPbjhuvc9Ve3tjru24ddde7FQ9pkma8JJNSZU9kpmpvUnpmptI ngp+lPKbJNDTT1NGg9EGg09Q0GgkqU8jTTTGqb0FAD1AA0AAZAA0AABoc0xGRk0yaAZDRkMmQAAA yNMjQMIZAkRJommplNhTT0000KeU/VPRqZP1R6j0QAGgDQ00BoIpIJk0p5oNTIniaZGhE2R6jUaA 0bUA0DQBoCSIENBNACaAJpqeUzKJ6JoepppoaNNNNNBoZFyZCJdrCXbMDXTfwNK9Usnhc1PS4fbU a46v1/euzPY/zWux5mfa4bN1fXMdldDfZx31K+CIeFNVd3jiLtd5rUpMJc8NdXm1KNqrcRo3CtI7 NWdOna6QfeyDJZFlH+LWzShopHm2b775jTJ+1IEjvri9Vn9FiEjBJEsO8wnQoDfPUVT0n729X4Gs osx6CT0umM+/6oOxL0i1ibbbabbBs0fwg08kf7XCdknJtgtsLpVrdkKIpJDJdJhTJBtI3eox5aG3 SfMfidAa1b6D9DlJWwipyjKc9F9YMfcZiy7C85dnsNm3u7tc4/7JvRvKed87WzV9/5RJRm689xfZ NrFInQv0xpcSmKxrUx5LPFiyLq7J2M/WPMN2txernZhJXLkhzeYoaQy9ph/mSahZivbIwPskgtUR YBRjgmwzYeQGL0yw2H+DaOLQa8L6XgrpX/joUcf/ULbYqagro0TqHf8ff5NA9zwJU9/v994b35MU 7x4r/kFNkOzyT2fmJRv5vZppaAO6BtbIhdzFTL4mNEEbXVoFRwVkeRmuQ032d4Nifi42Stmcx9Kg EA4xpWvw5RP2L6sgvVkjDnq/wEtsD5730HJHcqgHJAvczd4B5rLL0ZsT37/BUdfanTHjgWxBTDQr Q+STkliEzyXxGAthlYd8IQ05tv+bfXLrTIqshkIb2QTg+vyQG+EZJgeKPpXV1sBbpsS1VZv6oYau MTkz5+fi4qcCQWNLWyGp6visVKG58lOsgxKRY2oDCqyBTOpkiQJTnEWRxkmgTBywUCyQxIxJCmRI ick7lSB2lBisIlCgUZCJlpYAVUSAPN1EhZbIuPl/ZUqdhaYn5oFgB8gBljpBbAZSiNbnw/EnjRAy QMo/fEO+IqFm012RW0Wy2Sy14a8LS+vaSLjKqeZAV8JpZKMxZAEACGZkBetBSdd2PrvIkbDj0WkL C7wWc9xVKhkEubA8xVKkFBIHfXYz3Bn0CcyU9i0IEWLaaiuvG3BRQbYlU/gtxvOJ80Oag45fwMip 3LJY0LRWNltFLM0o6SZlJQMTIoYl1w0KINppMTIJn5FgrlYYuFOENpWrKo4LJQNvkE6jMl5SRNKZ Kl+QODT9KhBHZbiQLJIlE5GmuBEnYojaLHNx0nMaDMlZCXgCvGoB4fIiMYpcjUGNJJmOjxHM2JtM gpwfL0s7iittksF0DvcpgVxRrYmLYvEwtwHGPqDXPnPI+NzYi+xtjBjMGrhAWLnLjGUkE5I3NoSN c5AUKlvKnMpllWRSLJHdphPhrNeAKZ7arjEQBQN5McRRGmQJ3OLbeN3wV1oqbe0r0SO8sM2xAOOB jBZHIYxFM5GJYXl8TE4L4lMNGWY07RQFecQDMiXmRnbgZ35ngu878TRxvRbpNg9h5GOekliWxYgB rwpaX8aKRCEjE5EjOByAI8y5Far1LDXe7O6W9PDRmbgYfm+uOBaeNDMgjjxNlRS59XT2SQdJYrjk Xjnat79YBoXLp0Z83A4bokYM3FYkygBeusit+k7oUmN17b1hhfMYoaiRItLrKY9M7qDRIyTuRJDI yHdIIWuVKDDDFGsIm0mnSClgWCm2RxLsRh9RAvMZFQuYnF0OKrl8VETgUhGIGpQFNRdLCFKSwaEE ESFuo/U/uNbLuY8ZlaHWtOaJ4XzSI+lrDVcQ1UcDr1iyFCjYoEnyB3tmKnkSfQ8kHavH0HRFHmS7 QyqNiusWVOAT86RaZH7p/A+4bg9Zwb2K2tghhIiPUH2qRVq9J8ies7xsKwske30+xOIO+g1rUZwp U92AMAUYyQP9pBWFScRSTRB38hTYf4D9WcLkwv62TJZ9hiWkTCM8BSMkZpVEeoLloPsCgQAzJo9E jflgHOjcWqURy1DXn4mFTNAwXImaUMUAzViWktCAiaWAWotBTC0iIiV3mGoqSIsijEx9JYOkXBBH 3HCk0de6u3QXFxheKjXBmMdpPBDoZfAsI3UKwBKi12HP8s+Q1LJo8ekzxCZOKCTrFva3RMVjVEFG 19Ai4qV7fPJVhwMUMvdFwN867rg4pFMV0rAyUDUaTZNg5iIOlcw5WLhUJS7VJyYDnOSwV20CB0E4 gqpy4o11h2v3ZR9/o5+1YMugRhZ7ljz8yKKPWH9v3n0AMLCnJkA7G4A/p8JFmjFBmsl2CbpMJsTm G0cCZpXMuv6zWUBf0t53vovAUwWBA8psJ9po7pGc6DnN5YewpOBIw8K1iTLBF59Iug2KB4LSfYcj pkMULShoX8vG9TSB4KjK7PxeNuovhk61C3ac8nScR+BaUbjQZHy3lp5L1LT0cTebEerkOtlxI6TI 4o+1YGjdXni5Fv54Qaezczu1pPDJlxkpRc0GIRixgJ/plgrOhwtruAMXid54W224hNw4nnjp71eM Vtj126jWMyGgvCwBixXMX3ASjIAYkx2Fh5GRuCowjEc+dQMC4bY2r83MqmSjmEd+JYSgV9Vp1IxA 4II3I8qZxRQhw+qkY4WjdddStzlWMUiYoJNr4ySNRynSSI8CppJnT7a8/tZrTtGsJkzoPhMOODIv AtOuTLeybWhNFA2kkbYSWwKnMpBujiY93bcRnGPpJEMgbbzF1q9pY6kQIkmAGc1LAQVQtF9PwZ0w 62BKJsMU4OKYc7QchknO254QbU7OcampMpiNTgs7xA7EIApO3vhNkLmBrPRa1ahcyBdqvvUuNmSY 32ZWkgxEgw5eMBCBvUEZEv3l6mozQ1vkNhcF6WM4CSfEcIHStsBejED4HD3i39M7xdonA2m1et12 hEU2QF1rqMtC9LvNZB4UXqU3B3QFSMBt6FHhU5CQGTIqGBQxbXbVi8qF3Bi8AUK5a8hfeXE2ZvI2 Hl8o9YczUSO5pcjTyCwnIISJU7pPJNobmS5/TS/XWgeZ7R/6r1RyztTjNrlegW8fC7mul3m00s94 23UqlKu6gp7ukMwBzulW+9VbU+BsiAWwvhjiuG7WhPvtKKb3NFhXrqHr7obhREKM5z+PDsyxz8Aw XLtNNgmVgohef30mrVQuAjNnnGnmNGx+aRsMrm1GjjNJZrcDU6jOlL14ydjp1I3MbBhgGB8i0QRQ b+B4nWXHibFxPNYqHIF2YEoA3t4mXjbR7YSTBoU6WGEhQmUJf5xYbhuIs0MaGK5BC4WHRZCadOuM oLd4oNugRp3M1oYmd9EAWsE4bR6+vjNL4ngWbMcm3OeeIzXETMYlEoIs6AdRpp5NoAPL9zIQvFIh kAViha/nccgCfdqD1vuAU/Jbk6WlWOrWvAwPPpwoqkoJRCF43EZ698FC1QlSRk4EzuY/IKATMA+d kV82SHG4cS04UYIEsDPCBinZcDeX9D0vV3SncXQ6rXMJGZ4yYWcDwHPScQGtFyKyOEyUR2FYmWAM DCbEE4B8uAOHwL0kuPsLEYX2e25B8zZPPavZ2dQEzDAQN8v/MAbypaxw8HUFas5kHhfemJGxXAFc vBdms0rJSXQXLWklzmiqzxZ+rUsWBmE7jhBsTwK6SZxXtALLI2oBxyggdBbqivUoTUF0BvwbytAJ EwDOR9AhuPo4tLKEaFGuvBy5Gtg8Vp6kavdJaLS7lAVAMEYITEMtYW71K2B69x7CtE1a8bVaQ6Oj rPxzUERqOg2A9DaYkZpsqVY6knXIByaEDCj31QXwGUDoXJ3JYnfERERERERER7fEgIHEtUkMqFgx jGDEPMNQlMK1BzGTJltwGUTEfGAqR+PNX7L/wcuj7SEpBLalGSgmHOuY4MYRUFFOnWRLSQDAS6h9 tHMC6omgioJiyrMixTyK4q1RlJxQ2HhjUzImXmkDwBIQSCjEKNDQe90aAksC4oSErBBmW4gM+RUJ yOC9gQ0qfV7FmigVVVisyHV3EIkMA7FwXcZ4q48y5YfuXlRuLpKQwC3o6xy04qOQg4RDjz/RsWZS nioVlT4ntdbLXW9jpcrLE4C28jhfG1GtkzhiwOzpaJeFyuV749tjYZnY0BnefU0ucnN4BM48rc2N wUITzgEF7Yx6s82DFHSoJKwKkStPMuVbFgtNLWlQpyz6ik0AFLIxtLU8S+9XBnKroc1t9zBbo3fS zO//F3JFOFCQIBxk/A== --Boundary_(ID_Wplq5s1dslieTGXrq5+KhA)--