MySQL Bugs: #73571: 'IMPORT TABLESPACE' from corrupted file leads to permanent server failure

Bug #73571	'IMPORT TABLESPACE' from corrupted file leads to permanent server failure
Submitted:	13 Aug 2014 14:20	Modified:	14 Aug 2014 17:51
Reporter:	Юрий Евтухов	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.20	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	crash, innodb, Tablespace

Description:
There is InnoDB table with per-file data file and discarded tablespace.
Trying to import tablespace using 'ALTER TABLE tbl IMPORT TABLESPACE' using incorrect IBD-file leads to permanent Mysql server failure.

Permanent because server dies, then it is restarted by mysqld_safe, then dies immediately one more time and thats all.

Log records after 'alter table':
2014-08-13 15:06:10 5913 [ERROR] InnoDB: Tried to read 16384 bytes at offset 0. Was only able to read 8556.
2014-08-13 15:06:10 7fc5345e8700  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2014-08-13 15:06:10 5913 [ERROR] InnoDB: File (unknown): 'read' returned OS error 71. Cannot continue operation
140813 15:06:10 mysqld_safe Number of processes running now: 0
140813 15:06:10 mysqld_safe mysqld restarted
2014-08-13 15:06:10 6043 [Note] Plugin 'FEDERATED' is disabled.
2014-08-13 15:06:10 6043 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-08-13 15:06:10 6043 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-13 15:06:10 6043 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-08-13 15:06:10 6043 [Note] InnoDB: Memory barrier is not used
2014-08-13 15:06:10 6043 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-13 15:06:10 6043 [Note] InnoDB: Not using CPU crc32 instructions
2014-08-13 15:06:10 6043 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-08-13 15:06:10 6043 [Note] InnoDB: Completed initialization of buffer pool
2014-08-13 15:06:10 6043 [Note] InnoDB: Highest supported file format is Barracuda.
2014-08-13 15:06:10 6043 [Note] InnoDB: The log sequence numbers 66131092 and 66131092 in ibdata files do not match the log sequence number 66131102 in the ib_logfiles!
2014-08-13 15:06:10 6043 [Note] InnoDB: Database was not shutdown normally!
2014-08-13 15:06:10 6043 [Note] InnoDB: Starting crash recovery.
2014-08-13 15:06:10 6043 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-08-13 15:06:10 6043 [ERROR] InnoDB: Tried to read 16384 bytes at offset 0. Was only able to read 8556.
2014-08-13 15:06:10 7f00d3572740  InnoDB: Operating system error number 2 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.
2014-08-13 15:06:10 6043 [ERROR] InnoDB: File (unknown): 'read' returned OS error 71. Cannot continue operation
140813 15:06:10 mysqld_safe mysqld from pid file /tmp/mysql.pid ended

How to repeat:
1. Create any innodb table
2. run 'alter table TBL discard tablespace'
3. put any file instead of IBD-file. I've just copied corresponding FRM file. Anyway, restoring IBD-file from corrupted backup can give any content.
4. run 'alter table TBL import tablespace'

Suggested fix:
Make 'alter table .. import tablespace' return error in such case or provide methods for checking IBD-file

Thank you for the report.

Verified as described. Strictly say this is feature request, but very useful one.

Same error for me. No solution found.

Somewhat related: Bug #75187

I think the server should register the table only after an succesful import, not when the import starts. Then it won't try to access the table after a crash.

The server registers the table after a successful IMPORT, not at the start.

See row0import.cc

3718         row_mysql_lock_data_dictionary(trx);
3719 
3720         /* Update the root pages of the table's indexes. */
3721         err = row_import_update_index_root(trx, table, false, true);
3722 
3723         if (err != DB_SUCCESS) {
3724                 return(row_import_error(prebuilt, trx, err));
3725         }
3726 
3727         /* Update the table's discarded flag, unset it. */
3728         err = row_import_update_discarded_flag(trx, table->id, false, true)     ;
3729 
3730         if (err != DB_SUCCESS) {
3731                 return(row_import_error(prebuilt, trx, err));
3732         }
3733 
3734         table->ibd_file_missing = false;
3735         table->flags2 &= ~DICT_TF2_DISCARDED;
3736 
3737         if (autoinc != 0) {
3738                 ib::info() << table->name << " autoinc value set to "
3739                         << autoinc;
3740 
3741                 dict_table_autoinc_lock(table);
3742                 dict_table_autoinc_initialize(table, autoinc);
3743                 dict_table_autoinc_unlock(table);
3744         }
3745 
3746         ut_a(err == DB_SUCCESS);
3747 
3748         return(row_import_cleanup(prebuilt, trx, err));
3749 }