Bug #105994 Error in Upgrade prerequisites documentation
Submitted: 28 Dec 2021 15:24 Modified: 14 Jan 15:41
Reporter: Lenny Andersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[28 Dec 2021 15:24] Lenny Andersen
Description:
In this document https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
at point 12 regarding "lower_case_table_names" it is suggested to identify tables with uppercase with this statement:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';

However; this will not return any rows because the comparison isn't case sensitive.

How to repeat:
Createing a database with tables having both uppercase and lower case.

mysql> create database test;
Query OK, 1 row affected (0.06 sec)
mysql> use test;
Database changed
mysql> create table UPPERCASE (id int, PRIMARY KEY (`id`)) engine=InnoDB;
Query OK, 0 rows affected (0.21 sec)
mysql> create table lowercase (id int, PRIMARY KEY (`id`)) engine=InnoDB;
Query OK, 0 rows affected (0.19 sec)
mysql> create table CamelCase (id int, PRIMARY KEY (`id`)) engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';
Empty set (0.01 sec)

To illustrate:
mysql> select TABLE_NAME, if(TABLE_NAME != lower(TABLE_NAME),'Yes','No') as UpperCase from information_schema.tables where TABLE_SCHEMA = 'test';
+------------+-----------+
| TABLE_NAME | UpperCase |
+------------+-----------+
| CamelCase  | No        |
| UPPERCASE  | No        |
| lowercase  | No        |
+------------+-----------+
3 rows in set (0.01 sec)

Suggested fix:
A very simple way is to compare a checksum of the values.

mysql> select TABLE_NAME, if(sha(TABLE_NAME) != sha(lower(TABLE_NAME)),'Yes','No') as UpperCase from information_schema.tables where TABLE_SCHEMA = 'test';
+------------+-----------+
| TABLE_NAME | UpperCase |
+------------+-----------+
| CamelCase  | Yes       |
| UPPERCASE  | Yes       |
| lowercase  | No        |
+------------+-----------+
3 rows in set (0.00 sec)

The SQL recommended for identifing tables with uppercase should be
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE sha(TABLE_NAME) != sha(LOWER(TABLE_NAME)) AND TABLE_TYPE = 'BASE TABLE';
[28 Dec 2021 16:06] MySQL Verification Team
Thank you for the bug report.
[14 Jan 15:41] Edward Gilmore
Updated 8.0.28 refman