Bug #107677 Contribution by Tencent: create table like temporary table missing charset info
Submitted: 28 Jun 2022 7:41 Modified: 28 Jun 2022 8:15
Reporter: Xiaodong Huang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:8.0.29, 5.7.38 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[28 Jun 2022 7:41] Xiaodong Huang
Description:
When we execute the query "create table t2 like t1" where the t1 is temporary table, the t2 will inherit the chartset of t1 instead of the database. In addition, the query binlog is "create table t2 (.....)engine=innodb" which don't contain the charset inforation. Thus, the charset of table t2 will use the database charset. In some scenarios,  It will lead to the unconsistency in charset between source and replica then causes replica unexpected halt.

How to repeat:
1. Configuring a source-replica replication environment;
2. In source, create a database with charset utf8mb4;
3. In source, create a temporary table t1 (a varchar(256)) with utf8mb3 in the database;
4. In source, create table t2 like t1;
5. In source, insert a record ("aaa") into t2;
6. In replica, it will report a 13146 error;

A testcase file is as following:

=================================================================================
--source include/master-slave.inc
--source include/have_binlog_format_row.inc

--echo #
--echo # Prepare
--echo #

--echo [on master]
--connection master

create database test1 charset utf8mb4;
use test1;
show create database test1;
create temporary table t1 (a varchar(256)) charset utf8mb3;

--echo #
--echo # Run 
--echo #

--echo [on master]
--connection master
create table t2 like t1;
show create table t2;
insert into t2 values("aaa");

--echo [on slave]
--source include/sync_slave_sql_with_master.inc
use test1;
show create table t2;

--echo #
--echo # Cleanup 
--echo #

--echo [on master]
--connection master
drop database test1;
--source include/sync_slave_sql_with_master.inc
--source include/rpl_end.inc

=================================================================================
Run this testcase, it will report the error in the replica:

=================================================================================
**** SHOW REPLICA STATUS on server_2 ****
SHOW REPLICA STATUS;
Replica_IO_State	Waiting for source to send event
Source_Host	127.0.0.1
Source_User	root
Source_Port	13000
Connect_Retry	1
Source_Log_File	master-bin.000001
Read_Source_Log_Pos	865
Relay_Log_File	slave-relay-bin.000002
Relay_Log_Pos	375
Relay_Source_Log_File	master-bin.000001
Replica_IO_Running	Yes
Replica_SQL_Running	No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno	13146
Last_Error	Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log master-bin.000001, end_log_pos 834. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter	0
Exec_Source_Log_Pos	157
Relay_Log_Space	1293
Until_Condition	None
Until_Log_File
Until_Log_Pos	0
Source_SSL_Allowed	No
Source_SSL_CA_File
Source_SSL_CA_Path
Source_SSL_Cert
Source_SSL_Cipher
Source_SSL_Key
Seconds_Behind_Source	NULL
Source_SSL_Verify_Server_Cert	No
Last_IO_Errno	0
Last_IO_Error
Last_SQL_Errno	13146
Last_SQL_Error	Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log master-bin.000001, end_log_pos 834. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids
Source_Server_Id	1
Source_UUID	abbc72a9-f6aa-11ec-9eec-5254009f48ea
Source_Info_File	mysql.slave_master_info
SQL_Delay	0
SQL_Remaining_Delay	NULL
Replica_SQL_Running_State
Source_Retry_Count	10
Source_Bind
Last_IO_Error_Timestamp
Last_SQL_Error_Timestamp	220628 09:22:27
Source_SSL_Crl
Source_SSL_Crlpath
Retrieved_Gtid_Set
Executed_Gtid_Set
Auto_Position	0
Replicate_Rewrite_DB
Channel_Name
Source_TLS_Version
Source_public_key_path
Get_Source_public_key	0
=================================================================================

Suggested fix:
In the statement "CREATE TABLE t2 LIKE t1;", if t1 is temporary table, the statement will write the generated statement instead of the original statement into binlog. In the above testcase, the table charset of t2 inherited from t1 is utf8mb3 and the statement binlog is following:
"  
CREATE TABLE `t2` (
  `a` varchar(256) DEFAULT NULL
) ENGINE=InnoDB
/*!*/;
"
We can see the binlog don't contain the " DEFAULT CHARSET=" after the "ENGINE=InnoDB". The table charset is  utf8mb4 in the replica because the database charset is utf8mb4. Thus, the source and the replica have Inconsistent charset of table t2.
[28 Jun 2022 7:44] Xiaodong Huang
patch to fix this problem:

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Bugfix-create-table-.-like-temporary-table-missing-c.patch (application/octet-stream, text), 1017 bytes.

[28 Jun 2022 8:15] MySQL Verification Team
Hello Xiaodong,

Thank you for the report and contribution.

regards,
Umesh