MySQL Bugs: #72043: Unclean system shutdown recovery fails for schema with capital letters

Bug #72043	Unclean system shutdown recovery fails for schema with capital letters
Submitted:	15 Mar 2014 0:01	Modified:	26 Sep 2014 19:43
Reporter:	Bart Butler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
Whenever my Mac laptop crashes, my MySQL database does not recover properly. In the server log file there are a bunch of errors like this:

2014-03-14 15:44:40 7fff74395310 InnoDB: Error: table 'raw/week'
InnoDB: in InnoDB data dictionary has tablespace id 4211,
InnoDB: but the tablespace with that id has name RAW/week.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

I can recover the table data if I rename the data directories to lowercase (i.e. I rename 'ETL' to 'etl', restart mysqld, and everything works, except that my schema is now named 'etl'). Capital letters in table names are not an issue, just schema names.

How to repeat:
Create a schema with capital letters on a Mac, put a table in it, and turn the computer off with the power button. When you turn it back on the tables will appear in SHOW TABLES but not be accessible, and the error log will be full of messages like:

Suggested fix:
Do the same thing for schema names as you do with table names on non-case-sensitive filesystems like Mac OS X's.

Thank you for the bug report.

 Your MySQL connection id is 8
Server version: 5.6.16 MySQL Community Server (GPL)

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> CREATE DATABASE MYDB;
Query OK, 1 row affected (0.00 sec)

mysql> USE MYDB
Database changed
mysql> CREATE TABLE mytb (id int);
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO mytb VALUES (1), (2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytb;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

The MAC was crashed by power button and

[12:07:25][pochita:]~ miguel$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

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> USE MYDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| mytb           |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mytb;
ERROR 1146 (42S02): Table 'mydb.mytb' doesn't exist
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MYDB               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

2014-03-17 12:05:49 742 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.16'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2014-03-17 12:07:53 742 [ERROR] InnoDB: Failed to find tablespace for table '"mydb"."mytb"' in the cache. Attempting to load the tablespace with space id 6.
2014-03-17 12:07:53 742 [ERROR] InnoDB: Trying to add tablespace 'mydb/mytb' with id 6 to the tablespace memory cache, but tablespace 'MYDB/mytb' with id 6 already exists in the cache!
2014-03-17 12:07:53 11e873000 InnoDB: cannot calculate statistics for table "mydb"."mytb" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2014-03-17 12:08:38 11e873000 InnoDB: cannot calculate statistics for table "mydb"."mytb" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
[12:13:34][pochita:]~ miguel$

Repeatable with latest release:

[10:13:26][pochita:]~ miguel$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.20 MySQL Community Server (GPL)

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> CREATE DATABASE MYDB;
Query OK, 1 row affected (0.00 sec)

mysql> USE MYDB
Database changed
mysql> CREATE TABLE mytb (id int);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO mytb VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytb;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Put down the MacBook Pro pressing Power Button and re-start it:

Last login: Thu Sep  4 10:23:26 on console
[10:24:29][pochita:]~ miguel$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20 MySQL Community Server (GPL)

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> USE MYDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| mytb           |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mytb;
ERROR 1146 (42S02): Table 'mydb.mytb' doesn't exist

-

Attachment: my.cnf (application/octet-stream, text), 943 bytes.

-

Attachment: Pochita.local.err (application/octet-stream, text), 223.46 KiB.

Installing the server using the dmg package. I was able to repeat without to use the power button:

[12:10:14][pochita:]~ miguel$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.6.20 MySQL Community Server (GPL)

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> CREATE DATABASE MYDB;
Query OK, 1 row affected (0.00 sec)

mysql> USE MYDB
Database changed
mysql> CREATE TABLE mytb (id int);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO mytb VALUES (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytb;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql>  -- kill mysqld process: [12:17:00][pochita:]~ miguel$ sudo kill -9 1595
mysql> SELECT * FROM mytb;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: MYDB

ERROR 1146 (42S02): Table 'mydb.mytb' doesn't exist
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: MYDB

+--------------------+
| Database           |
+--------------------+
| information_schema |
| MYDB               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.05 sec)

Fixed as of the upcoming 5.6.22, 5.7.6 release, and here's the changelog entry:

If a database is named using uppercase letters on a MySQL server with
"lower_case_table_names=2" (which is default on Mac OS X), "InnoDB" stores
the database name as specified in "SYS_TABLES" and in lowercase on disk.
During crash recovery, the case mismatch resulted in a conflict that would
mark the tablespace ".ibd" file as missing. The patch for this bug
converts database names to lowercase on crash recovery. 

Thank you for the bug report.

$ bzr log -r 6178
------------------------------------------------------------
revno: 6178
committer: Aditya A <aditya.a@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-09-26 12:10:48 +0530
message:
  Bug #18412598 UNCLEAN SYSTEM SHUTDOWN RECOVERY FAILS
                FOR SCHEMA WITH CAPITAL LETTERS
  
  
  PROBLEM
  -------
  
  1. When using lower_case_table_names = 2 (only used in
     case insensitive file system) innodb stores database 
     name in lower case in SYS_TABLES, where as in the disk
     the database name is stored in the format specified in 
     create statement.In this case we use capital letters in 
     create statement.
  2. During crash recovery fil_load_single_table_tablespace()
     function is called which populates fil_space_t structure
     with capital database name found in the disk and stores 
     it in two hash tables,one based on name and another based
     on tablespace id. 
  3. When opening the tablespace with fil_open_single_table_tablespace()
     we first check whether the fil_space_t structure 
     is present in hash based on database name obtained from
     the SYS_TABLES. Since the database name is in small letters
     it cannot find the fil_space_t object in the hash ,then
     it tries to get the fil_space_t object using tablespace id 
     and is successful in getting it. 
  4. This is seen as a conflict and the table's ibd file is set 
     as missing and we cannot access the table.
  
  FIX
  ---
  
  1. While doing crash recovery for case insensitive systems
     we convert the database names to lower case .
  
  Approved bt Kevin [#rb 6737 ]