Bug #95559 Possible to change lower_case_table_names from 5.7 to 8.0

• Submitted: 29 May 2019 11:41
• Modified: 14 Nov 2019 17:14
• Reporter: Sivert Sørumgård
• Status: Closed
• Category: MySQL Server: Data Dictionary
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[29 May 2019 11:41] Sivert Sørumgård

Description:
In 5.7, on a case sensitive file system, with lower_case_table_names=0, we can create a table with an upper case table name. When upgrading, we can set lower_case_table_names=1. In this case, the table and its tablespace file will retain the upper case names. 

Up to 8.0.15, upgrade would succeed, but we would get an assert when accessing the table (e.g. when running mysql_upgrade). The assert is due to checking that we have an MDL lock on the table name, and it fails because we take a lock on the name we get from the parser (which is lower cased), whereas the actual table name is upper cased. 

In 8.0.16, the server itself executes the actions previously done by mysql_upgrade, and hence fails while running 'check table for upgrade' with the assert described above.

How to repeat:
See above.

Suggested fix:
We could prohibit changing l_c_t_n, in line with what we do for restart after 8.0, but from a 5.7 data directory, we don't really know the l_c_t_n setting.

[14 Nov 2019 17:14] Daniel Price

Posted by developer:
 
Fixed as of the upcoming 8.0.19 release, and here's the changelog entry:

Changing the lower_case_table_name setting when upgrading from MySQL 5.7
to MySQL 8.0 could cause a failure due to a schema or table name letter
case mismatch. If lower_case_table_names=1, table and schema names are now
checked by the upgrade process to ensure that all characters are
lowercase. If table or schema names are found to contain uppercase
characters, the upgrade process fails with an error. For related
information, see "Upgrade Prerequisites". 

Also updated:
Upgrade Prerequisites:
https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

Changes should appear online soon.