From: Mattias Jonsson Date: August 25 2010 2:00pm Subject: bzr commit into mysql-trunk-bugfixing branch (mattias.jonsson:3237) Bug#55784 List-Archive: http://lists.mysql.com/commits/116748 X-Bug: 55784 Message-Id: <201008251400.o7PCbM7D030286@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3945922719737557553==" --===============3945922719737557553== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///Users/mattiasj/mysql-bzr/b55784-trunk-bf/ based on revid:holyfoot@stripped 3237 Mattias Jonsson 2010-08-25 Bug#55784: Foreign key integrity broken by alter table By using EXCHANGE PARTITION WITH TABLE it was possible to break foreign key integrity. EXCHANGE PARTITION did not verify that the table to exchange with was not referenced by other tables foreign keys. Solved by checking this with ::can_switch_engine @ mysql-test/r/partition_exchange.result updated result @ mysql-test/t/partition_exchange.test added test @ sql/share/errmsg-utf8.txt Added error message for foreign key in table to exchange with. @ sql/sql_partition_admin.cc Added check for foreign key referencing the table. modified: mysql-test/r/partition_exchange.result mysql-test/t/partition_exchange.test sql/share/errmsg-utf8.txt sql/sql_partition_admin.cc === modified file 'mysql-test/r/partition_exchange.result' --- a/mysql-test/r/partition_exchange.result 2010-08-04 11:53:45 +0000 +++ b/mysql-test/r/partition_exchange.result 2010-08-25 14:00:02 +0000 @@ -1,3 +1,16 @@ +DROP TABLE IF EXISTS t1, t2, t3, t, tp, tsp, tmp; +CREATE TABLE t1 (s1 INT PRIMARY KEY) engine=innodb; +CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) engine=innodb; +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (1),(2),(3); +CREATE TABLE t3 (s1 INT PRIMARY KEY) +engine=innodb +PARTITION BY LIST (s1) +(PARTITION p1 VALUES IN (1,2,3)); +ALTER TABLE t3 EXCHANGE PARTITION p1 WITH TABLE t1; +ERROR HY000: Table to exchange with partition have foreign key references: 't1' +DROP TABLE t2, t1, t3; +# Tests for WL#4445 CREATE TABLE t (a INT, b VARCHAR(55), PRIMARY KEY (a)) === modified file 'mysql-test/t/partition_exchange.test' --- a/mysql-test/t/partition_exchange.test 2010-08-04 11:53:45 +0000 +++ b/mysql-test/t/partition_exchange.test 2010-08-25 14:00:02 +0000 @@ -1,5 +1,29 @@ --source include/have_innodb.inc --source include/have_partition.inc + +--disable_warnings +DROP TABLE IF EXISTS t1, t2, t3, t, tp, tsp, tmp; +--enable_warnings + +CREATE TABLE t1 (s1 INT PRIMARY KEY) engine=innodb; + +CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) engine=innodb; + +INSERT INTO t1 VALUES (1),(2),(3); + +INSERT INTO t2 VALUES (1),(2),(3); + +CREATE TABLE t3 (s1 INT PRIMARY KEY) + engine=innodb + PARTITION BY LIST (s1) + (PARTITION p1 VALUES IN (1,2,3)); + +--error ER_PARTITION_EXCHANGE_FOREIGN_KEY +ALTER TABLE t3 EXCHANGE PARTITION p1 WITH TABLE t1; + +DROP TABLE t2, t1, t3; + +--echo # Tests for WL#4445 CREATE TABLE t (a INT, b VARCHAR(55), PRIMARY KEY (a)) === modified file 'sql/share/errmsg-utf8.txt' --- a/sql/share/errmsg-utf8.txt 2010-07-29 14:15:38 +0000 +++ b/sql/share/errmsg-utf8.txt 2010-08-25 14:00:02 +0000 @@ -6387,3 +6387,6 @@ ER_TABLES_DIFFERENT_METADATA ER_ROW_DOES_NOT_MATCH_PARTITION eng "Found row that does not match the partition" swe "Hittade rad som inte passar i partitionen" +ER_PARTITION_EXCHANGE_FOREIGN_KEY + eng "Table to exchange with partition have foreign key references: '%-.64s'" + swe "Tabellen att byta ut mot partition har foreign key referenser: '%-.64s'" === modified file 'sql/sql_partition_admin.cc' --- a/sql/sql_partition_admin.cc 2010-08-16 15:07:53 +0000 +++ b/sql/sql_partition_admin.cc 2010-08-25 14:00:02 +0000 @@ -145,6 +145,14 @@ static bool check_exchange_partition(TAB table->s->table_name.str); DBUG_RETURN(TRUE); } + + /* The table cannot have foreign keys constraints or be referenced */ + if(!table->file->can_switch_engines()) + { + my_error(ER_PARTITION_EXCHANGE_FOREIGN_KEY, MYF(0), + table->s->table_name.str); + DBUG_RETURN(TRUE); + } DBUG_RETURN(FALSE); } --===============3945922719737557553== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/mattias.jonsson@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: mattias.jonsson@stripped\ # xrt95u253obrxgcm # target_branch: file:///Users/mattiasj/mysql-bzr/b55784-trunk-bf/ # testament_sha1: 3028c10091e9c50e1078391372a12f1707a86cab # timestamp: 2010-08-25 16:00:17 +0200 # base_revision_id: holyfoot@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQj6O/4ABLbfgEAwevf//3// 3+C////6YAnu+obLu6ydDEuPe56vevcBEUrRT0TthJJBMFT9NJtGmJMiemTInhQA02kDI0Gho0El AmVP00CaaFPJHqPUaNNMjTQAAAABoGUo09QGgAAANAAaADQAAABIiIQQ0TxDU2mjSNNNDT1NAAAA aNADjRkyMIxAMJoMAmg0DJk0ZMhhAYSSE0aAmJkaBMIp6ao/Kn6p+qA9RiNPU/UQANlHt7FeHGRw fh5X2Ggqqr4vBnMvs/2jLxNwy+FtmD4dT2XA6Iawh30WTwbVcNrgq23kmc9JkhvDCQWbNy4cul5b SbtNHLzjh/rhxQQBO++3xX1h8SeWQxqPsNw8948A9wzpsY22uj6I6aXTC3JjdZTNrxmrSvVHI5dd n64eobtsx8TvNPlyN5iLeHfWcBS30BEOh9fyIDhhwxWWfnP08LmZWsaz55u7Pf2One6GctZiE0lO ERv81M4cM98qlVTKCggom8Aoi0awjMEeJGp84URRTtMjce4w8eDxhwwMFdJa0pAU1cigiDBD+81z XQhdPXrMPHp0vBGamgdn1cu4OadtfQk/QOSDW7rMXAUYyq6I5pFmA34v6YQrubjjVMDlvek3TLAc gVoYOtMnPgnUYKlxeGM6TQMtAkoYg85b+smKYmXEPSKhMvasaQfvOuNR+o29UrKSzY6KBPrYc79C 1vk5qznGs+oFHNcp0peRghyomIURJiRajm2odojRwPsbEpczt9AgbrtWtQYF3YgooNGQLBPZCTg2 SQTaYxcpOwTtpqg7duBBCa3Wrh4ApJw5OUXxPQIzVHzTtAlcj0bEGNwnHYLZwfAWQNBhHlmrASkv BBQy1G3lzYLInCpwp16NNdgzTkU0+6tSGlsagXMKcA3jVdTAncs5S3eDqvwkLA3loF0u/AsOe3Ok sNxmGxajMNb3mjnlB3zXwvM6hmN4MHoYIWS6MCVBw3mIvxqfqVyS8QUL00Ng+skuRzqcmau15JiU ym7vmNKjzUyWNfxDmGtrLAfJQoWkSiF0qRp0woBktlr+WsUXwT5oWFBwn7p8vBcmmBdaxmtrY7M9 PEE3ACCeGr4lpPx8HlxeTlE66bSyBRseWQgPHMeBmbkPhUrzMpHhFGgRMvxenLFYmHXRb1gQbUyd VJV0qMFqmBYg4+ao4zk4cEgWMryscquBpq39EFYqEde2U53W4ltHcOcjqHmQ8rxUb4guwqUnRwNh JN8sNqpEcK6Wc2l5Vk3bfibyY0Jlud4rXYcayMSoqGEH4rcnXlKJ6yoVy00mW2TwTMu2rypcMxtL jAkGoqYcLYdjgQwUjLxV/C2fBmgqUPNl5SRdEeXvST2WvQ1PTTkSRaOnH4GECVHxKpZoFpPCxoUu 14Wpz8NlxaXkzOsxsBTi2m7TGcpfW0YhrTx5DLXaiAyObaS7mYN7RUsjWDFLord2hVFUQ6yKoouc IvVi+lbec3tWN34hTsw5ikRqRsZM1jTT3O0DIbaGlxc4cndauWYuElrxJVHoFglgkeQMQMwJeoQE BACBzJPWEEpaZR8ScL6sHvXcFAnAu/sFOyB8+Jc7AaffEL9ET8DHOmH9QWL+HvzkCpMxSgiu5USg oooDSgMEQSMDE505IfQYJjjyJJxqAsBulp9TMMFZaTpTCUFMqgekRRmiYJXMZDFqYIVF4nj0OH7A x1hXBFyRawGpDTILgt1EQeKxJSFmTiUyRMGqcp8CQRoFlXBIihTBAcjjxqT01VZeBHCeiBGhDZxA 0HjRHisgJYVHUdAhfBfFQsyluyFmyP0NyAgIAdNWS5vYwAFMgqqrEKAtEtQ6V82/hRh9RblEofpZ rpHvGIL0DEvDR46g6XicJdMKxrNEYd5YOT3uKMvZaQFTQ9Q3jsOIjIQhKbzqZWn8DdyqIK4gG5kF Q+g+QL7hwaeyRd12kiyBcfcWJsR9v+7L5XUDUVaHeffa5HgZfzErDfM2EznAHuCsBGE8JKIp2NYZ 54FsaDe5YjUOGuFkF9fViwUiORSg4HhwLtpB4dR8C4/YYQOZin7Kkc/wsmRZkroveteR8GRxpWnV QCAPcHkyAej4Mh7w/VzhH4O3nc7lBrOm00InePHdxIsssKj0C8OgrOWbA2IFiTLyzzmNzzro2sap zKPVtQ1sdmjfmnsFGXHIK78lbXtLqqZqZEZjOwax4WFMKq9VY7ltFqdFk6xst2efTwWZDRaKgTNt MAjtlVUwiFBiQRcKk0Y+Y0ESHzPliGIDMRchkQ4UqKtn9Dkx0gwYAmLCdPEoEoB06FSnWx35AXnO JMuDIK08DtUgw6YkxYvH1pJ+AdWk3oMu5NBEhDCWbh7l1d2bbEceac9Ya1uQRXcteK6KMyStdl5e Aa2QNHCCI0oNGEQQkJoRkSpAvSz0wXTuFGdFvJilnUomSTeSnRKb1SiIMMzBSDL1/LV7CXE3R24t sbzrWK73rV20QevclLnr+pZAXg+dMK/XBfhEWi4ZrAGBmGawAcCco2wldsvPQmUExFTjzOWadJyQ MDTK7uNyGB3WtE+6CMTNG+yv6p5XEkW8YuRpA+a5ltW0Za0Ew1cM25wpiA1i3RsBATYRaJWNQ1QS gBxTIi8U3RKg9iueVIAyKBkwwqXL4Y29mB3RZ5ArhNXzG+yS3IRRWgYSYA7fTzEprFJMZMONADUk /3YROYr2mTYYsZqpu+8nIBF4RHE52DE1nRp4qOBw4gNFiMGDCtLvwrLLYwa2VbmwAcqnS5Wi5szn S8ljhMEySJuU42AoIJhQSKINf4vRNWmqa01Uq/ehxWet11OE6qAoXsCdIQPlIE1cVhDmqIuazd2h /bXejUJcoKq1ccieyvdpFjVo3ToZdWNiONHdUlAPWxdGXRTpJx2CrNFZXBzOhA1+qvFwwSTgJJdn vR4w69sThqpzYe2dmPWQyuVN2MSIv+MJDqOfIFZYpBGsBspB9Po7r4xwP2rypsmvdYN0Zb5Y8c0B ulFETDbBYs8b3TSWWwkIGiB8TpJgY4JXpxyOHYNGyYKBSbElzde3FEDiXACpp8vRtoDYXqnqLxf2 Jmp4IdtxWwKJ3LqSvLz5Rai4S0sORmreavVjVpK/RWLWCmBUBFA3csVm9ozJzA06nwZYqHJufcuF TIJV0vRV1FUEJ685/xdyRThQkAj6O/4= --===============3945922719737557553==--