Bug #81444 | Orphan ibd file when playing with foreign keys | ||
---|---|---|---|
Submitted: | 16 May 2016 22:34 | Modified: | 18 Apr 2019 8:50 |
Reporter: | Arnaud Adant | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.7, 5.7.12, 5.6.30 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | composite foreighn keys, foreign keys |
[16 May 2016 22:34]
Arnaud Adant
[17 May 2016 4:27]
MySQL Verification Team
Hello Arnaud, Thank you for the report and test case. Observed this with 5.7.12 build. -- 5.7.12 mysql> \! ls -l /var/lib/mysql/bug_fk total 100 -rw-r----- 1 root root 98304 May 19 22:29 b.ibd -rw-r----- 1 root root 65 May 19 22:29 db.opt mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.12 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.12 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ 8 rows in set (0.00 sec) Regards, Umesh
[17 May 2016 4:36]
MySQL Verification Team
-- 5.6.30 - leaves orphan ibd file(some extra info in the error message) mysql> create table b(id int); ERROR 1813 (HY000): Tablespace for table '`bug_fk`.`b`' exists. Please DISCARD the tablespace before IMPORT. mysql> mysql> \! ls -l 81444/bug_fk total 132 -rw-rw---- 1 umshastr common 98304 May 17 06:34 b.ibd -rw-rw---- 1 umshastr common 65 May 17 06:34 db.opt mysql> mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.30 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.30-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql>
[23 Jun 2016 4:37]
Jan Lindström
Also 5.5 is effected.
[23 Jun 2016 4:38]
Jan Lindström
One possible way to fix this pug.
Attachment: fkbug.diff (text/x-patch), 11.36 KiB.
[23 Jun 2016 4:44]
Jan Lindström
Above patch for 5.5 version.
[18 Apr 2019 8:50]
Dmitry Lenev
Posted by developer: Hello! The problem is still repeatable on 5.7.27-git. Simplified test case for it looks like: CREATE DATABASE bug_fk; USE bug_fk; SET FOREIGN_KEY_CHECKS = 0; CREATE TABLE c ( b INT UNSIGNED NOT NULL, CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) ) ENGINE=InnoDB; CREATE TABLE d ( b BIGINT UNSIGNED NOT NULL, CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) ) ENGINE=InnoDB; SET FOREIGN_KEY_CHECKS = 1; CREATE TABLE b ( b BIGINT UNSIGNED PRIMARY KEY ) ENGINE=InnoDB; # The above statement fails with ERROR HY000: Cannot add foreign key constraint SET FOREIGN_KEY_CHECKS = 0; DROP TABLE c; DROP TABLE d; CREATE TABLE b(id INT); # The above statement incorrectly fails with ERROR HY000: Tablespace '`bug_fk`.`b`' exists. However, the same test case runs fine on recent 8.0 (I have used 8.0.17-git): CREATE DATABASE bug_fk; USE bug_fk; SET FOREIGN_KEY_CHECKS = 0; CREATE TABLE c ( b INT UNSIGNED NOT NULL, CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) ) ENGINE=InnoDB; CREATE TABLE d ( b BIGINT UNSIGNED NOT NULL, CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) ) ENGINE=InnoDB; SET FOREIGN_KEY_CHECKS = 1; CREATE TABLE b ( b BIGINT UNSIGNED PRIMARY KEY ) ENGINE=InnoDB; # The above statement fails with ERROR HY000: Cannot add foreign key constraint SET FOREIGN_KEY_CHECKS = 0; DROP TABLE c; DROP TABLE d; # The below statement runs fine! CREATE TABLE b(id INT); This happens because starting from 8.0.3 CREATE TABLE for InnoDB tables became atomic DDL statement. And thus all effects of failed CREATE TABLE b (b BIGINT ...) statement are properly rolled back. Therefore I am closing this bug as fixed in 8.0.3. Since the fact that CREATE TABLE for InnoDB tables is now atomic is emphasized in Release Notes for 8.0.3 already, I don't think there is anything to document additionally.