MySQL Bugs: #73225: If tablespace exists, can't CREATE table, but can ALTER ENGINE=InnoDB

Bug #73225	If tablespace exists, can't CREATE table, but can ALTER ENGINE=InnoDB
Submitted:	7 Jul 2014 21:03	Modified:	22 Oct 2015 14:22
Reporter:	Federico Razzoli	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If I try to CREATE a table, but the tablespace is already there, I get an error 1813.

But I can always convert MyISAM -> InnoDB, even if tablespace exists. The old tablespace is overwritten. No errors, no warnings.

Reading the docs, I wasn't able to find out if this is a bug or not. However, as a user, I can hardly understand why CREATE TABLE is safer than ALTER ... ENGINE = InnoDB.

How to repeat:
.

Thank you for the report.

I cannot repeat described behavior:

[sveta@delly mysql-test]$ Logging: ./mtr  --start innodb
...
[sveta@delly mysql-test]$ mysqlmtr test
...
mysql> set global innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(f1 int) engine=innodb;
Query OK, 0 rows affected (0.38 sec)

mysql> \q
Bye
[sveta@delly mysql-test]$ rm var/mysqld.1/data/test/t1.
t1.frm  t1.ibd  
[sveta@delly mysql-test]$ rm var/mysqld.1/data/test/t1.frm 
[sveta@delly mysql-test]$ mysqlmtr test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-debug-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(f1 int) engine=innodb;
ERROR 1813 (HY000): Tablespace for table '`test`.`t1`' exists. Please DISCARD the tablespace before IMPORT.
mysql> create table t1(f1 int) engine=myisam;
Query OK, 0 rows affected (0.10 sec)

mysql> alter table t1 engine=innodb;
ERROR 1050 (42S01): Table './test/t1' already exists

Please specify exact version of MySQL you use and provide repeatable test case.

I tried your test case and got your results. I don't know why - maybe the first time I didn't FLUSH TABLES, or maybe I was still using an older release. I really don't know, sorry.

Sorry for comments-flooding. I repeated the problem, this is the test case:

mysql> CREATE TABLE t (c INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0,48 sec)

root@this:/usr/local/mysql/data/test# mv t.ibd tmp

mysql> DROP TABLE t;
Query OK, 0 rows affected (0,20 sec)

root@this:/usr/local/mysql/data/test# mv tmp t.ibd

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)

mysql> CREATE TABLE t (c INT) ENGINE = InnoDB;
ERROR 1813 (HY000): Tablespace for table '`test`.`t`' exists. Please DISCARD the tablespace before IMPORT.
mysql> CREATE TABLE t (c INT) ENGINE = MyISAM;
Query OK, 0 rows affected (0,12 sec)

mysql> ALTER TABLE t ENGINE = InnoDB;
Query OK, 0 rows affected (0,69 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thank you for the feedback.

Verified as described. Debug server crashes with following backtrace:

2014-07-15 19:28:02 26693 [ERROR] InnoDB: Cannot create file './test/t.ibd'

2014-07-15 19:28:02 26693 [ERROR] InnoDB: The file './test/t.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file './test/t.ibd' under the 'datadir' of MySQL.
2014-07-15 19:28:12 7fe932ed2700  InnoDB: Assertion failure in thread 140639558510336 in file os0file.cc line 1951
InnoDB: Failing assertion: !exists
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
16:28:12 UTC - mysqld got signal 6 ; 
...
/lib64/libpthread.so.0[0x38f3e0f710]
/lib64/libc.so.6(gsignal+0x35)[0x38f3232925]
/lib64/libc.so.6(abort+0x175)[0x38f3234105]
/home/sveta/src/mysql-5.6/sql/mysqld[0xb73467]
/home/sveta/src/mysql-5.6/sql/mysqld[0xd45436]
/home/sveta/src/mysql-5.6/sql/mysqld[0xd4d348]
/home/sveta/src/mysql-5.6/sql/mysqld[0xd15a23]
/home/sveta/src/mysql-5.6/sql/mysqld[0xbfdbec]
/home/sveta/src/mysql-5.6/sql/mysqld[0xafcd1b]
/home/sveta/src/mysql-5.6/sql/mysqld[0xafcf68]
/home/sveta/src/mysql-5.6/sql/mysqld(_ZN7handler15ha_rename_tableEPKcS1_+0x6f)[0x649ba5]
/home/sveta/src/mysql-5.6/sql/mysqld(_Z18mysql_rename_tableP10handlertonPKcS2_S2_S2_j+0x400)[0x854738]
/home/sveta/src/mysql-5.6/sql/mysqld(_Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP10TABLE_LISTP10Alter_infojP8st_orderb+0x1fce)[0x85c181]
/home/sveta/src/mysql-5.6/sql/mysqld(_ZN19Sql_cmd_alter_table7executeEP3THD+0x511)[0x9a263b]
/home/sveta/src/mysql-5.6/sql/mysqld(_Z21mysql_execute_commandP3THD+0x74c5)[0x7ec865]
/home/sveta/src/mysql-5.6/sql/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x421)[0x7ef902]
/home/sveta/src/mysql-5.6/sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xc1e)[0x7e3098]
/home/sveta/src/mysql-5.6/sql/mysqld(_Z10do_commandP3THD+0x33e)[0x7e2227]
/home/sveta/src/mysql-5.6/sql/mysqld(_Z24do_handle_one_connectionP3THD+0x1b6)[0x7a84e1]
/home/sveta/src/mysql-5.6/sql/mysqld(handle_one_connection+0x33)[0x7a7ff7]
/home/sveta/src/mysql-5.6/sql/mysqld(pfs_spawn_thread+0x159)[0xdf3af0]
/lib64/libpthread.so.0[0x38f3e079d1]
/lib64/libc.so.6(clone+0x6d)[0x38f32e8b6d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fe90401af30): ALTER TABLE t ENGINE = InnoDB
Connection ID (thread ID): 4
Status: NOT_KILLED

Posted by developer:
 
Fixed as of the upcoming 5.6.28, 5.7.10, 5.8.0 release, and here's the changelog entry:

An ALTER TABLE operation that converted a table to InnoDB did not check
for unknown files with the same name as the destination .idb file,
permitting an unknown file of the same name to be overwritten.

Thank you for the bug report.

Posted by developer:
 
The changelog entry was modified as follows:

An ALTER TABLE operation that converted a table to an InnoDB
file-per-table tablespace did not check for unknown files with the same
name as the destination .idb file, permitting an unknown file of the same
name to be overwritten.

Hi all,

See this resolution, applied on 5.6.19, innodb_per_table:
Check tablespaces orphans:
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES -- WHERE NAME LIKE 'SEI/%'
where (name not in (SELECT concat(table_schema,'/',table_name) FROM INFORMATION_SCHEMA.tables))
and name not like 'sys%'

(1) Ensure database listed above, doesn't exist
(2) Create database with the same name (identify by step 1)
(3) Try to create ONE table listed above, will be error that already exist
(4) Check on datadir database directory, the table was created.
(5) Do stop mysql service
(6) Remove from system operator the .ibd (on step 4) of respective table (by step 3)
(7) Start the mysql service.
(8) Execute the querie above again, see all objetcts at result is disappear
(9) To clear references from innodb_sys_tables with tables, DROP DATABASE (created by step 2).
(10) Now you can create database if you need recover this database and objects. Or this work is done, you wont have more errors on error log like this:
2019-09-10 10:47:03 1688 [ERROR] InnoDB: Tablespace open failed for '"DB"."TABLE"', ignored.
2019-09-10 10:47:03 24fc  InnoDB: Operating system error number 3 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2019-09-10 10:47:03 1688 [ERROR] InnoDB: Could not find a valid tablespace file for 'DB/TABLE'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

Bye, 
/Fil
MYSQL and SQL Server database administrator