Bug #91204 Crash on MacOS X when lower_case_table_names=2 and innodb_file_per_table set
Submitted: 11 Jun 2018 2:55 Modified: 18 Jun 2018 12:20
Reporter: Graham Giller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:8.0.11 OS:MacOS
Assigned to: CPU Architecture:Any

[11 Jun 2018 2:55] Graham Giller
Description:
This is from a new installation of MySQL 8.0.11 on Mac OS X High Sierra with a case insensitive file system.

With lower_case_table_names=2 (default) and innodb_file_per_table set, the statements: "CREATE DATABASE Health; CREATE TABLE Measurements;" appear to work correctly. A folder "/usr/local/mysql/data/Health" is created and a file "measurements.idb" created within the folder. Data may be loaded and stored into the table.

When the server is shutdown and restarted the following appears in the log:

2018-06-11T01:22:32.486146Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2018-06-11T01:22:35.721218Z 1 [Warning] [MY-012351] [InnoDB] InnoDB: Tablespace 2, name 'health/measurements', file './health/measurements.ibd' is missing!

The server is unstable and crashes, corrupting the metadata and requiring a filesystem rebuild.

This problem appears to be resolved when "lower_case_table_names=1" is set expliticly.

How to repeat:
Do the above.

Suggested fix:
There's obviously a place where the system should be mapping the table Health.Measurements to the path on disk "./health/measurements.idb" and it is not doing all of the case transformations properly. It is unusable without setting lower_case_table_names=1 in my.cnf.
[11 Jun 2018 2:55] Graham Giller
The .idb file is not missing!
[11 Jun 2018 3:25] MySQL Verification Team
Thank you for the bug report. See https://bugs.mysql.com/bug.php?id=90695 and below explanation of developer:

[2 May 7:29] Ståle Deraas

Posted by developer:
 
After initialization, is is not allowed to change this setting. https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_tab...
So "lower_case_table_names" needs to be set together with --initialize .
[11 Jun 2018 3:33] Graham Giller
I have never specified lower_case_table_names at all until this instability occurs, and only specified it to stop the system crashing. Initializing a Datadir and restoring a database from a backup file results in an unstable system that crashes. That’s a bug! If it’s not allowed to do something you shouldn’t let the user do it.
[12 Jun 2018 11:19] Ståle Deraas
Hi Graham, I was a bit fast there. The issue is verified, and we will investigate this further.
[18 Jun 2018 12:20] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.13 release, and here's the changelog entry:

Table names were not compared in lowercase on macOS with a
lowercase_table_names=2 setting, which caused server instability after a
restart.