Bug #87209 CREATE AS SELECT lead to slave sql thread failed with error 1067

• Submitted: 26 Jul 2017 14:26
• Modified: 27 Jul 2017 6:51
• Reporter: Fungo Wang (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.6.37, 5.7.19
• OS: Any
• CPU Architecture: Any

[26 Jul 2017 14:26] Fungo Wang

Description:
CREATE AS SELECT succeed on master but failed on slave when RBR is used.

How to repeat:
## test case as below:

--source include/master-slave.inc
--source include/have_binlog_format_row.inc
set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set character_set_server = utf8;

## create a table with special Chinese characters in default
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(6) NOT NULL DEFAULT '已创建',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## switch client charset
set character_set_client = latin1;
CREATE TABLE t2 SELECT * FROM t1;

--connection slave
--let $slave_sql_errno= convert_error(ER_INVALID_DEFAULT) # 1067
--source include/wait_for_slave_sql_error.inc

sleep 3000;

## master and slave opt file include this:
--log-bin --binlog-format=row

When test case sleep, connect to slave and executing "SHOW SLAVE STATUS\G", or check the slave error log, you will see the SQL error message.

[27 Jul 2017 6:51] MySQL Verification Team

Hello Fungo Wang,

Thank you for the report and test case.

Thanks,
Umesh