MySQL Bugs: #82060: mysqld will read dropped table when it start

Bug #82060	mysqld will read dropped table when it start
Submitted:	30 Jun 2016 2:55	Modified:	30 Jun 2016 7:04
Reporter:	h h	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.7.12, 5.7.13	OS:	CentOS (CentOS release 6.5 (Final))
Assigned to:		CPU Architecture:	Any
Tags:	error

Description:
when i restart my mysql server ,i find it will read the table which is already dropped, i do not know why

2016-06-30T10:13:07.735248+08:00 0 [Note] /usr/sbin/mysqld: Shutdown complete

2016-06-30T02:13:07.865216Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
2016-06-30T02:13:12.536711Z mysqld_safe Starting mysqld daemon with databases from /data/mysql
2016-06-30T10:13:12.956589+08:00 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2016-06-30T10:13:12.990700+08:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.12-log) starting as process 17599 ...
2016-06-30T10:13:13.123381+08:00 0 [Note] InnoDB: PUNCH HOLE support available
2016-06-30T10:13:13.123453+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-30T10:13:13.123464+08:00 0 [Note] InnoDB: Uses event mutexes
2016-06-30T10:13:13.123473+08:00 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2016-06-30T10:13:13.123481+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-30T10:13:13.123492+08:00 0 [Note] InnoDB: Using Linux native AIO
2016-06-30T10:13:13.124232+08:00 0 [Note] InnoDB: Number of pools: 1
2016-06-30T10:13:13.124380+08:00 0 [Note] InnoDB: Using CPU crc32 instructions
2016-06-30T10:13:13.134478+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 4G, instances = 8, chunk size = 128M
2016-06-30T10:13:13.463021+08:00 0 [Note] InnoDB: Completed initialization of buffer pool
2016-06-30T10:13:13.512875+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016-06-30T10:13:13.566722+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-06-30T10:13:13.897177+08:00 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2016-06-30T10:13:13.897237+08:00 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2016-06-30T10:13:13.897257+08:00 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2016-06-30T10:13:13.897267+08:00 0 [ERROR] InnoDB: Cannot open datafile for read-only: './autoparts/bak_0627_db_attribute_config.ibd' OS error: 71
2016-06-30T10:13:13.927929+08:00 0 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 988 was updated to use file ./recycle_bin/bak_0627_db_attribute_config.ibd.
2016-06-30T10:13:13.928064+08:00 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2016-06-30T10:13:13.928082+08:00 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2016-06-30T10:13:13.928091+08:00 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2016-06-30T10:13:13.928101+08:00 0 [ERROR] InnoDB: Cannot open datafile for read-only: './autoparts/bak_0627_db_goods.ibd' OS error: 71
2016-06-30T10:13:13.988911+08:00 0 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 1033 was updated to use file ./recycle_bin/bak_0627_db_goods.ibd.

How to repeat:
do not know how to repeat

when i do " use b;rename table a.table1 to table1 " ,and then restart mysql server ,the error will appear

Hello!

Thank you for the report.
Observed this behavior in 5.7.13.

Thanks,
Umesh

I am going to add a comment here instead of creating a new bug report since I suspect that it is the same issue.

It is a common practice to use `RENAME TABLE ... TO ...` as a way to move a table from one schema to another.
If the original table that was moved is then replaced by a new table which is put in place by a new rename, it's going to result in error messages in the mysql error log implying inconsistent data in the innodb data dictionary.

I tested this in the latest version: 5.7.16.

Way to reproduce it:

Run the following commands:
```
create database db1;
create database db2;
create database db3;
create table db1.table_a (`id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
create table db2.table_a (`id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
rename table db2.table_a to db3.table_a;
rename table db1.table_a to db2.table_a;
```

Now, restarting mysql, will result in the following entries in the mysql error log:

```
...
2016-11-20T20:33:42.419122Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2016-11-20T20:33:42.419168Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2016-11-20T20:33:42.419182Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2016-11-20T20:33:42.419194Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './db1/table_a.ibd' OS error: 71
2016-11-20T20:33:42.422171Z 0 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 25 was updated to use file ./db2/table_a.ibd.
2016-11-20T20:33:42.422295Z 0 [ERROR] InnoDB: In file './db2/table_a.ibd', tablespace id and flags are 25 and 33, but in the InnoDB data dictionary they are 26 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-11-20T20:33:42.422313Z 0 [ERROR] InnoDB: A tablespace for `db3/table_a` has been found in multiple places;
2016-11-20T20:33:42.422319Z 0 [ERROR] InnoDB: Default location: ./db3/table_a.ibd, Space ID=26, Flags=33
2016-11-20T20:33:42.422323Z 0 [ERROR] InnoDB: Dictionary location: ./db2/table_a.ibd, Space ID=25, Flags=33
2016-11-20T20:33:42.424135Z 0 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 26 was updated to use file ./db3/table_a.ibd.
...
```

Unfortunately, this issue appeared on my server instance which run 5.7.13-log version.

When will be solved it?

Faced similar issue on MySQL 5.7.16 on windows server 2012

Still an issue with 5.7.23 running on Linux

can not repeat under 5.7.24-27-log Percona Server (GPL), Release 27,