MySQL Bugs: #97985: create table like temporary table creates different charset on master and slave

Bug #97985	create table like temporary table creates different charset on master and slave
Submitted:	13 Dec 2019 13:59	Modified:	30 Dec 2019 6:49
Reporter:	Christian Roser	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.7.x 5.5.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	rbr temporary charset

Description:
It is easily possible to create a table which has a different charset on master and slave. 

This will trigger the following bug https://bugs.mysql.com/bug.php?id=83461 and will break the replication.

We're using utf8 as default charset on our clusters. 

No manipulations on the slave are required to create the table with different charset.
The main problem is, that if you create a temporary table with a different charset then the default and then create a regular table with create table <table> like <temporary table> the table on the master gets created with the same charset like the temporary table has.

The binlog does not contain the charset so on the slave the default charset is used.

As soon as you insert values to the newly created table the slave breaks with the same error like described in https://bugs.mysql.com/bug.php?id=83461.

Binlog:
#191213 14:34:15 server id 183837360  end_log_pos 7753 CRC32 0xe7b2f0c2         Query   thread_id=130   exec_time=0     error_code=0
SET TIMESTAMP=1576244055/*!*/;
SET @@session.pseudo_thread_id=130/*!*/;
CREATE TABLE `t2` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB
/*!*/;

t2 on Master:
mysql (test)> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

t2 on Slave:
mysql (test)> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

How to repeat:
mysql (test)> create temporary table t1 (test varchar(10)) default charset = latin1;
Query OK, 0 rows affected (0.00 sec)

mysql (test)> create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)

I also discovered, that a table t3 created from t2 which was created by the temporary table t1 also have the same problem. Even though t2 is a regular table with latin1 on the master the slave will have t2 with utf8. I can repeat this for ever and use t3 as source table for t4 and so on. It will always be the same.

However, when I create the exact same table manually on the master without using CREATE TABLE LIKE... the charset is replicated correctly.

mysql (test)> create table t3 like t2;
Query OK, 0 rows affected (0.01 sec)

mysql (test)> create table t4 like t3;
Query OK, 0 rows affected (0.01 sec)

Master:
mysql (test)> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql (test)> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Slave:
mysql (test)> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql (test)> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql (test)> CREATE TABLE `t5` (`test` varchar(10) DEFAULT NULL) DEFAULT CHARSET=latin1;

mysql (test)> create table t6 like t5;
Query OK, 0 rows affected (0.02 sec)

Master:
mysql (test)> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql (test)> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Slave:
mysql (test)> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql (test)> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Binary log:
#191213 16:09:37 server id 183837360  end_log_pos 309 CRC32 0xe0a0dc62  Query   thread_id=11    exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1576249777/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t3 like t2
/*!*/;
# at 309
#191213 16:10:00 server id 183837360  end_log_pos 374 CRC32 0x1ed5d9d0  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 374
#191213 16:10:00 server id 183837360  end_log_pos 464 CRC32 0x2e39a10c  Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1576249800/*!*/;
create table t4 like t3
/*!*/;
# at 464
#191213 16:10:34 server id 183837360  end_log_pos 529 CRC32 0x05987e1d  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 529
#191213 16:10:34 server id 183837360  end_log_pos 695 CRC32 0xc85909c7  Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1576249834/*!*/;
CREATE TABLE `t5` (   `test` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!*/;
# at 695
#191213 16:11:09 server id 183837360  end_log_pos 760 CRC32 0x72426b46  Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 760
#191213 16:11:09 server id 183837360  end_log_pos 850 CRC32 0x10165513  Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1576249869/*!*/;
create table t6 like t5
/*!*/;

Hello Christian Roser,

Thank you for the bug report and feedback.
An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

regards,
Umesh

Hi, 

thanks for your reply. 

Could you please link the bug to the original one?

regards
Christian

My apologies, missed referencing Bug #83461 in the earlier note.
I've added a note in the base bug now i.e in Bug #83461

regards,
Umesh

Hi Umesh,

I agree that the bugs are somehow related but I don't agree that they are duplicates.

One describes mainly a replication error produced by replicating "correct" data.
The other one is about working replication of "incorrect" data (missing charset information).

My bug report is mainly about the fact, that it's possible to create tables on the master which have a different charset on the slave.

The "original" bug is more or less about the problem that the replication breaks in the case of a different charset on master and slave.

83461 is a consequence of 97985.

97985 is mainly about the fact, that the charset doesn't get replicated when doing

CREATE TABLE.. like .. 

and the source is a temporary table.

"Fixing" the charset manually on the slave is not an option for various reasons. 
1. the slave is read_only and shouldn't be modified directly at all. 
2. there are thousands of master-slave setups

regards
Christian