Description:
When creating a table with CREATE TABLE ... LIKE ... from a temporary table, the create table isn't written entirely on the binary log, causing an error on the replica, which will create the table with the charset from the default database.
Column 1 of table 'test.joinit2' cannot be converted from type 'varchar(64(bytes))' to type 'varchar(256(bytes) utf8mb4)', Error_code: MY-013146
Doesn't happen with persistent tables.
How to repeat:
Create a temporary table:
MySQL > CREATE TEMPORARY TABLE `joinit` (`i` int(11) NOT NULL AUTO_INCREMENT,`s` varchar(64) DEFAULT NULL,`t` time NOT NULL,`g` int(11) NOT NULL,PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Create a persistent table from joinit:
MySQL > CREATE TABLE joinit2 LIKE joinit;
Check binlog:
# at 9873
#230915 21:16:47 server id 42442 end_log_pos 10123 CRC32 0x1998ae04 Query thread_id=15 exec_time=0 error_code=0 Xid = 103
use `test2`/*!*/;
SET TIMESTAMP=1694812607/*!*/;
SET @@session.pseudo_thread_id=15/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `joinit2` (
`i` int NOT NULL AUTO_INCREMENT,
`s` varchar(64) DEFAULT NULL,
`t` time NOT NULL,
`g` int NOT NULL,
PRIMARY KEY (`i`)
) ENGINE=InnoDB <------ without the charset
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
Suggested fix:
Write the charset(CHARSET=XXX) on the binary log to ensure that the tables will be equal if the use of the binlog is needed.
