MySQL Bugs: #46896: binlog: case sensitivity of table names

Bug #46896	binlog: case sensitivity of table names
Submitted:	24 Aug 2009 15:55	Modified:	29 Dec 2010 5:41
Reporter:	Marc Alff	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.4 (azalea)	OS:	Microsoft Windows
Assigned to:	Libing Song
Triage:	Triaged: D2 (Serious)

Description:
Found with mysql-azalea-perfschema, but most likely a general issue:
When executing this query:

select count(*) > 0 from performance_schema.SETUP_INSTRUMENTS;

the binary log contains, depending on the platform

Linux:
master-bin.000001    #    Table_map    #    #    table_id: # (performance_schema.SETUP_INSTRUMENTS)

windows:
master-bin.000001    #    Table_map    #    #    table_id: # (performance_schema.setup_instruments)

The table name in the storage engine is always uppercase,
but the table name logged is altered to lowercase on windows,
even when the client query did use the proper uppercase name.

How to repeat:
See the tests perfschema.binlog_row, binlog_mix and binlog_stmt in
mysql-azalea-perfschema, and create similar tests using a regular table,
-- with a name in uppercase --,
run them on windows using mysql-azalea (now mysql-next)

Suggested fix:
N/A

Thank you for the report.

But why do you have SELECT statement in the binary log file? Please provide statement you used, because I can not repeat described behavior with generic statement.

The select is in the binlog because the underlying table, in the storage engine, is marked as unsafe to replicate (thd->lex_set_stmt_unsafe()).

What Marc described here is true.

I would not say that this is an expected behaviour.

Verified as described.

Analysis
========

Test case
---------

-- source include/have_binlog_format_row.inc
CREATE TABLE T1 (a INT);
CREATE TABLE t2 (a INT);
INSERT INTO T1 VALUES (1);

# correct case used in both table names
INSERT INTO t2 SELECT * FROM T1;
SHOW BINLOG EVENTS;
SHOW VARIABLES LIKE '%lower_case%';
DROP TABLE t2, T1;
-- exit

Results
-------

- Linux

  5.1  (5.1.52)    
    - Table_map_event stores T1 in upper case and t2 in lower case
    - lower_case_file_system  OFF
    - lower_case_table_names  0

  5.5  (5.5.7-rc)
    - Table_map_event stores T1 in upper case and t2 in lower case
    - lower_case_file_system  OFF
    - lower_case_table_names  0

  trunk (5.6.1)
    - Table_map_event stores T1 in upper case and t2 in lower case
    - lower_case_file_system  OFF
    - lower_case_table_names  0

- Windows

  5.1 (5.1.52)
    - Table_map_event stores both T1 and t2 in lower case
    - lower_case_file_system  ON
    - lower_case_table_names  1

  5.5 (5.5.7-rc)
    - Table_map_event stores both T1 and t2 in lower case
    - lower_case_file_system  ON
    - lower_case_table_names  1
  
  trunk (5.6.1)
    - Table_map_event store both T1 and t2 in lower case 
    - lower_case_file_system  ON
    - lower_case_table_names  1

Comments
========

Reading the description of lower_case_table_names in the manual,
I find [1]:

'If set to 0, table names are stored as specified and comparisons
 are case sensitive. If set to 1, table names are stored in
 lowercase on disk and comparisons are not case sensitive. If set
 to 2, table names are stored as given but compared in
 lowercase. This option also applies to database names and table
 aliases. For additional information, see Section 8.2.2,
 “Identifier Case Sensitivity”. (...)'

So we might expect Table_map_events to events to be also carrying
table names in lower case, since it reads them from
table->s->db.str.

References
==========

[1] http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lower_case_tabl...

Luis' comment is accurate.
So I think it is not a bug.
---------------------------
FYI, I checked performance_schema on Windows XP.
SHOW TABLES IN performance_schema prints lower case table names and the file names of those tables are lower case too.
It happens on both 5.5.6 and 5.5.8.
The refman says "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."