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

[13 Dec 2019 13:59] Christian Roser
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)
[13 Dec 2019 15:19] Christian Roser
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
/*!*/;
[30 Dec 2019 5:13] Umesh Shastry
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
[30 Dec 2019 6:12] Christian Roser
Hi, 

thanks for your reply. 

Could you please link the bug to the original one?

regards
Christian
[30 Dec 2019 6:20] Umesh Shastry
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
[30 Dec 2019 6:49] Christian Roser
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