MySQL Bugs: #63341: Docs again forget case insensitive platforms

Bug #63341	Docs again forget case insensitive platforms
Submitted:	20 Nov 2011 15:26	Modified:	21 Nov 2011 10:54
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-quick-start.html

"The name of the performance_schema database is lowercase, as are the names of tables within it. Queries should specify the names in lowercase. .. Before MySQL 5.5.8, the table names were uppercase, which caused problems on some systems for certain values of the lower_case_table_names system variable"

How to repeat:
This works fine on Windows with any setting of 'lower_case_table_names' for instance:

SHOW FULL TABLES FROM PerForManCe_SCHEMA;

Suggested fix:
Queries should specify the names in lowercase *on case sensitive platforms*.

Thank you for the problem report. Indeed, this is what we get on Windows:

mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.5.18                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
4 rows in set (0.02 sec)

mysql> desc `pErformance_sChema`.`thReads`;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| THREAD_ID      | int(11)      | NO   |     | NULL    |       |
| PROCESSLIST_ID | int(11)      | YES  |     | NULL    |       |
| NAME           | varchar(128) | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

Peter,

I don't understand what the complaint in the bug report is exactly.

The performance schema tables are always lowercase,
and the documentation states that an application should write
queries using lowercase.

That is correct: it is the only way to ensure that the query will work in the application regardless of the platform used, and is the best practice.

Now, it may be the case that one *can* write a query using a different naming and this query *may* (or may not) work on some platforms, but I don't see how using platform dependent behavior on purpose, only to find out that it works differently on different platforms, is the way to use the system and to document how to write an application.

"
Queries should specify the names in lowercase.
"
is absolutely correct.

The proposed fix:
"
Queries should specify the names in lowercase *on case sensitive platforms*.
"
is precisely how queries SHOULD NOT be written.

The documentation did not "forget case insensitive platforms", it explained how queries should be written and why, even for case insensitive platforms.

This is not a bug.

I disagree with Marc.

On case insensitive platforms (Windows, Mac) there is no need for recommendation of specific LETTERCASE for database and table identifiers in statements.

The current text leaves the impression that using UPPERCASE will lead to problems.  It will not on case insensitive platforms. There is no difference as compared with any other database or table identifier.  MyTable, MYTABLE and mytable will work for a table named mYtABLE on Windows and Mac.

But not a big deal of course

This exposes the confusion that comes about from the conflation of the application layer and the file system.  Case sensitivity should be solely defined at the application level regardless of what the underlying data storage method and platform thinks about case.  I think it was a huge mistate to ever rely on the file system to name tables in the first place.  If there's a bug it should be that.  I consider it a bug that case sensitivity in the MySQL application varies between platforms.