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: | |
Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
Version: | 5.4 (azalea) | OS: | Windows |
Assigned to: | Libing Song | CPU Architecture: | Any |
[24 Aug 2009 15:55]
Marc ALFF
[25 Aug 2009 19:33]
Sveta Smirnova
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.
[28 Aug 2009 9:33]
Marc ALFF
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()).
[7 Sep 2009 2:33]
Susanne Ebrecht
What Marc described here is true. I would not say that this is an expected behaviour. Verified as described.
[28 Sep 2010 14:56]
Luis Soares
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...
[29 Dec 2010 5:41]
Libing Song
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."