MySQL Bugs: #59240: Inconsistent case of identifiers in binlog when lower_case_table

Bug #59240	Inconsistent case of identifiers in binlog when lower_case_table_name=1
Submitted:	31 Dec 2010 0:50	Modified:	28 Jan 2011 12:16
Reporter:	Elena Stepanova	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
On server with lower_case_table_name=1, for upper-case database/table/view names some events in binary log contain the names converted into lower-case, while in others they remain upper-case. It causes a problem when the binary log is replayed on a server with lower_case_table_name=0.

It happens on 5.0, 5.1, 5.5 and 5.6; however I'm submitting the bug for 5.6 only, because 5.5 manual warns about a similar problem, but claims it has been fixed in 5.6 (http://dev.mysql.com/doc/refman/5.5/en/replication-features-variables.html):
"It is strongly recommended that you always use the same setting for the lower_case_table_names  system variable on both master and slave. In particular, when a case-sensitive filesystem is used, setting this variable to 1 on the slave, but to a different value on the master, can cause two types of problems: Names of databases are not converted to lowercase; in addition, when using row-based replication names of tables are also not converted. Either of these problems can cause replication to fail. This is a known issue, which is fixed in MySQL 5.6. "

I don't see any improvement in 5.6 in this regard.

The test case from 'How to repeat' section produces the following output:

SHOW BINLOG EVENTS;
Pos     Event_type      Server_id       End_log_pos     Info
4       Format_desc     1       114     Server ver: 5.6.2-m5-log, Binlog ver: 4
114     Query   1       193     CREATE DATABASE D1
193     Query   1       366     use `d1`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS SELECT 1
366     Query   1       522     use `d1`; ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS SELECT 2
522     Query   1       595     use `d1`; DROP VIEW V1
595     Query   1       679     use `d1`; CREATE TABLE T1 (i INT)
679     Query   1       745     BEGIN
745     Query   1       831     use `d1`; INSERT INTO T1 VALUES (1)
831     Query   1       898     COMMIT
898     Query   1       990     use `d1`; ALTER TABLE T1 ADD COLUMN f INT
990     Query   1       1092    use `d1`; DROP TABLE `t1` /* generated by server */
1092    Query   1       1169    DROP DATABASE D1
SHOW VARIABLES LIKE '%lower_case%';
Variable_name   Value
lower_case_file_system  OFF
lower_case_table_names  1

(This is from Linux; same on Windows, with the exception of lower_case_file_system value).

So,
1) for the database CREATE/DROP statements contain the correct name, but all generated 'use' statements have lowercase names;
2) for the view CREATE/ALTER statements contain lowercase names, DROP is correct;
3) for the table CREATE, INSERT and ALTER are correct, but DROP generated by server has a lowercase name.

A further attempt to apply this log to a server with lower_case_table_names=0 causes 
ERROR 1049 (42000) at line 26: Unknown database 'd1'

How to repeat:
--source include/have_log_bin.inc

CREATE DATABASE D1;
USE D1;

CREATE VIEW V1 AS SELECT 1;
ALTER VIEW V1 AS SELECT 2;
DROP VIEW V1;

CREATE TABLE T1 (i INT);
INSERT INTO T1 VALUES (1);
ALTER TABLE T1 ADD COLUMN f INT;
DROP TABLE T1;

DROP DATABASE D1;

SHOW BINLOG EVENTS;
SHOW VARIABLES LIKE '%lower_case%';

--exit

Verified with current mysql-trunk tree on Mac OS X.

ROW events also present identifiers in lower case names (note the db 
and table names in the Table_map_log_event):

Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	4	Format_desc	1	114	Server ver: 5.6.2-m5-debug-log, Binlog ver: 4
master-bin.000001	114	Query	1	193	CREATE DATABASE D1
master-bin.000001	193	Query	1	366	use `d1`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS SELECT 1
master-bin.000001	366	Query	1	522	use `d1`; ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS SELECT 2
master-bin.000001	522	Query	1	595	use `d1`; DROP VIEW V1
master-bin.000001	595	Query	1	679	use `d1`; CREATE TABLE T1 (i INT)
master-bin.000001	679	Query	1	745	BEGIN
master-bin.000001	745	Table_map	1	784	table_id: 48 (d1.t1)
master-bin.000001	784	Write_rows	1	818	table_id: 48 flags: STMT_END_F
master-bin.000001	818	Query	1	885	COMMIT
master-bin.000001	885	Query	1	977	use `d1`; ALTER TABLE T1 ADD COLUMN f INT
master-bin.000001	977	Query	1	1079	use `d1`; DROP TABLE `t1` /* generated by server */
master-bin.000001	1079	Query	1	1156	DROP DATABASE D1

ANALYSIS
========

In what follows lctn means lower_case_table_names.

Generally, transfering data between MySQL servers configured with
different lctn is not very well supported and "it is best to adopt a
consistent convention, such as always creating and referring to
databases and tables using lowercase names. This convention is
recommended for maximum portability and ease of use.", in [1].

Nonetheless, in replication we did some work to make it less of a pain
to replicate from master with lctn=0 into a slave with lctn=1. This
was done in BUG#37656. And it was possible because the slave was less
strict (lctn=1) than the master (lctn=0) with respect to the case of
identifiers.

On the other hand, the scenario depicted in this bug, is such that the
master is less strict (lctn=1) than the slave (lctn=0). In fact, this
means [1]:

  - Master (lctn=1)

    1. stores table and database identifiers/aliases in lower case
    2. performs identifiers comparison in a case insensitive way

  - Slave (lctn=0)

    1. identifiers are stored as provided by the user (at CREATE time)
    2. comparisons are performed in a case sensitive way

  Therefore, there is no sane way to protect replication from breaking
  when facing such cases:

  - Case #1 (user uses different case in different queries for the
             same identifiers)

  MASTER> CREATE DATABASE db;
  MASTER> USE db;
  MASTER> CREATE TABLE T1;
  MASTER> INSERT INTO t1 VALUES (1);  # remember, comparisons are 
                                        case insensitive
  
  sync_slave_with_master

  Slave will stop because it cannot find table 't1'. Or, maybe even
  worse, the slave may have both 't1' and 'T1' and ends up updating
  the wrong one. (see [1], "For example, on Unix, you can have two
  different tables named my_table and MY_TABLE, but on Windows these
  two names are considered identical.")
  
  - Case #2 (server generates queries with identifiers in lower case,
             differing from those the user entered at CREATE time).

  There are other offending cases for master server generated queries
  for binary logging purposes (eg, implicit TEMPORARY TABLE drops at
  disconnection, special queries rewriting, ...).

  Given that the server stores identifiers in lower case, it may
  generate queries in which identifiers are set with different case
  than the one used at CREATE time. This will also cause the slave to
  stop in a similar fashion as in case #1.

WORKAROUNDS
===========

For the reported problem, the workarounds would be, either:

  1. to set all identifiers in lower case at CREATE time (and then
     stick to lower case in subsequent queries refering to those
     objects);
  2. or use lower_case_table_names=1 at the slave as well.

CONCLUSION
==========

  We, therefore, close this bug as WON'T FIX.

REFERENCES
==========

  [1] http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

Discussed with Luís IRC/mail. Set status to Documenting to prompt me to make requested changes. After this I'll set back to WONTFIX.

The note from http://dev.mysql.com/doc/refman/5.5/en/replication-features-variables.html states,

"... In particular, when a case-sensitive filesystem is used, setting this variable to 1 on the slave, but to a different value on the master, can cause two types of problems: Names of databases are not converted to lowercase; in addition, when using row-based replication names of tables are also not converted. Either of these problems can cause replication to fail. This is a known issue, which is fixed in MySQL 5.6."

This paragraph is exactly correct -- it states that, in 5.5, it is not possible, when using a case-sensitive filesystem, to set lower_case_table_names=1 on the slave and the same variable to a different value on the master without ill effect, but doing this becomes possible in 5.6. It says nothing about setting lower_case_table_names to 1 on the master and some other value on the slave, and nothing (else) that overrides the general warning about using different lower_case_table_names on master and slave.

In the 5.6 version of the same page (not yet publically available; see https://docsrva.mysql.com/docs-confidential/refman-5.6/en/html-chapter/replication.html#re...), the note reads,

"In previous versions of MySQL, when a case-sensitive filesystem was in use, setting this variable to 1 on the slave and to a different value on the master could lead to replication failure. This issue is fixed in MySQL 5.6.1. (Bug#37656)"

Therefore, I do not see any need to update the docs in this regard. Closed without further action.

Setting status back to WONTFIX as previously agreed.