MySQL Bugs: #75614: table become unusable if crashed during ddl

Bug #75614	table become unusable if crashed during ddl
Submitted:	24 Jan 2015 1:48	Modified:	27 Jan 2015 18:21
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
root@(none) 09:35:44>create database zwx;
Query OK, 1 row affected (0.00 sec)

root@(none) 09:37:25>use zwx;
Database changed
root@zwx 09:37:27>create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)

root@zwx 09:37:29>insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

then use gdb and attach to the process ( gdb -p process_id), break function row_drop_table_for_mysql  and continue.

go back to client.

root@zwx 09:37:43>alter table t1 add column b int;

should be blocked because row_drop_table_for_mysql   is triggered. then kill -9 process_id to generate a crash. then restart it.

And you can find some temporary files left in the data dir, like :
$sudo ls -lh /u01/my575/data/zwx/
total 220K
-rw-rw---- 1 mysql dba   61 Jan 24 09:35 db.opt
-rw-rw---- 1 mysql dba 8.4K Jan 24 09:40 #sql-ae8_3f3a.frm
-rw-rw---- 1 mysql dba  96K Jan 24 09:37 #sql-ib1230-151964825.ibd
-rw-rw---- 1 mysql dba 8.4K Jan 24 09:37 t1.frm
-rw-rw---- 1 mysql dba  96K Jan 24 09:40 t1.ibd

Login the client again and you'll find table t1 become unusable:

root@(none) 09:46:04>use zwx;
Database changed
root@zwx 09:46:05>show tables;
+---------------+
| Tables_in_zwx |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

root@zwx 09:46:07>desc t1;
ERROR 1146 (42S02): Table 'zwx.t1' doesn't exist
root@zwx 09:46:11>show create table t1;
ERROR 1146 (42S02): Table 'zwx.t1' doesn't exist
root@zwx 09:46:14>select * from t1;
ERROR 1146 (42S02): Table 'zwx.t1' doesn't exist

How to repeat:
described above

Suggested fix:
I don't know... still work on this case...

workaround: rename #sql-ae8_3f3a.frm to t1.frm and restart the server.

Thank you for the report.

Verified as described.

I did test with 5.7.7-rc-debug. The behaviour is similar.

I used "break row0mysql.cc:row_drop_table_for_mysql" in gdb.

mysql-5.7.7-rc-debug> ALTER TABLE t1 FORCE, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: test

ERROR 2013 (HY000): Lost connection to MySQL server during query

$ ls -l data/test/
total 260
-rw-r----- 1 dveeden dveeden    65 May 31 13:30 db.opt
-rw-r----- 1 dveeden dveeden  8586 Jun 28 12:48 #sql-50ca_2.frm
-rw-r----- 1 dveeden dveeden 98304 Jun 28 12:46 #sql-ib44-4090430423.ibd
-rw-r----- 1 dveeden dveeden  8586 Jun 28 12:46 t1.frm
-rw-r----- 1 dveeden dveeden 98304 Jun 28 12:48 t1.ibd

Some interesting messages from the error log:
2015-06-28T10:49:27.193117Z 0 [Note] InnoDB: Ignoring data file './test/t1.ibd' with space ID 57, since the redo log references ./test/t1.ibd 
with space ID 55.
2015-06-28T10:49:27.193576Z 0 [Note] InnoDB: Ignoring data file './test/t1.ibd' with space ID 55. Another data file called ./test/#sql-ib44-40
90430423.ibd exists with the same space ID.
2015-06-28T10:49:27.193676Z 0 [Note] InnoDB: Ignoring data file './test/#sql-ib43-4090430422.ibd' with space ID 57. Another data file called .
/test/t1.ibd exists with the same space ID.
2015-06-28T10:49:27.193764Z 0 [Note] InnoDB: Ignoring data file './test/t1.ibd' with space ID 55. Another data file called ./test/#sql-ib44-40
90430423.ibd exists with the same space ID.
2015-06-28T10:49:27.193864Z 0 [Note] InnoDB: Ignoring data file './test/#sql-ib43-4090430422.ibd' with space ID 57. Another data file called .
/test/t1.ibd exists with the same space ID.
2015-06-28T10:49:27.194722Z 0 [Note] InnoDB: Database was not shutdown normally!
2015-06-28T10:49:27.194754Z 0 [Note] InnoDB: Starting crash recovery.

mysql-5.7.7-rc-debug> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME LIKE 'test/%';
+-------+---------------------------+------+-------------+----------------------+-----------+---------------+------------+
| SPACE | NAME                      | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE |
+-------+---------------------------+------+-------------+----------------------+-----------+---------------+------------+
|    55 | test/#sql-ib44-4090430423 |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |
|    57 | test/t1                   |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |
+-------+---------------------------+------+-------------+----------------------+-----------+---------------+------------+
2 rows in set (0.01 sec)

mysql-5.7.7-rc-debug> DESC test.t1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(400)        | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)