Skip to content

Instantly share code, notes, and snippets.

@gotnix

gotnix/proc.sql

Last active August 29, 2015 14:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save gotnix/d0e638b0261829dc024e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save gotnix/d0e638b0261829dc024e to your computer and use it in GitHub Desktop.
Download ZIP
测试 name_const 函数对 Replication 的影响
Raw
proc.sql
/*
问题是出现在MySQL 5.1 为Master MySQL 5.5 为Slave 的Replication 环境中，
报Bug的有很多，MySQL 5.5 之间主从复制也可能会出现。比如下面2个：
http://bugs.mysql.com/bug.php?id=34289
http://bugs.mysql.com/bug.php?id=57926
http://bugs.mysql.com/bug.php?id=69292
MySQL 参考手册：
http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html#function_cast
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat
http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_name-const
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html
MySQL 中的 name_const 对 binlog 的影响
http://www.vmcd.org/2013/09/mysql-replication-case-%E4%B8%80%E5%88%99/
《MySQL replication case 一则》 -- 解决办法
*/
USE fclub_notrans;
/*
检查存储过程是否创建
SELECT routine_name FROM information_schema.routines
WHERE routine_schema = 'fclub_notrans';
*/
DROP PROCEDURE IF EXISTS `proc_concat_pass`;
DROP PROCEDURE IF EXISTS `proc_concat_test`;
DROP PROCEDURE IF EXISTS `proc_concat_fail`;
DROP TABLE IF EXISTS `t_concat`;
-- 创建测试表
CREATE TABLE `t_concat`
(
`number` int(4) UNSIGNED NOT NULL DEFAULT 9999,
`string` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
)
ENGINE=InnoDB DEFAULT CHARSET = utf8 DEFAULT COLLATE = utf8_bin
;
-- 创建测试用存储过程
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_concat_pass`()
BEGIN
DECLARE varNum INT(4) DEFAULT 9999;
DECLARE strEN VARCHAR(20) DEFAULT ' String Variable';
DECLARE strCN VARCHAR(20) DEFAULT ' 字符串变量';
INSERT INTO t_concat(`number`, `string`)
VALUES(101, CONCAT(101, ' String')), -- 测试 数字 和 字符串 CONCAT()
(102, CONCAT(102, ' 连接字符串')), -- 测试 数字 与 中文字符串 CONCAT()
(103, CONCAT(103, strEN)), -- 测试 数字 和 英文字符串变量 CONCAT()
(104, CONCAT(104, strCN)), -- 测试 数字 和 中文字符串变量 CONCAT()
(105, CONCAT(varNum, ' String')), -- 测试 int()类型变量与英文字符串 CONCAT()
(106, CONCAT(CAST(varNum AS CHAR), ' CONCAT字符串')), -- 测试 int()类型变量 CAST(var AS char) 之后与中文字符串 CONCAT()
(107, CONCAT(CONVERT(varNum USING utf8), ' CONCAT字符串')), -- 测试 int()类型变量 CONVERT(var using utf8) 之后与中文字符串 CONCAT()
(108, CONCAT(varNum, strEN)), -- 测试 int()类型变量与英文字符串变量 CONCAT()
(109, CONCAT(varNum, strCN)); -- 测试 int()类型变量与中文字符串变量 CONCAT()
COMMIT;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_concat_test`()
BEGIN
DECLARE varNum INT(4) DEFAULT 9999;
DECLARE strEN VARCHAR(20) DEFAULT ' String Variable';
DECLARE strCN VARCHAR(20) DEFAULT ' 字符串变量';
DECLARE strContent VARCHAR(40) CHARACTER SET utf8 DEFAULT '内容：';
SET strContent = CONCAT(201, ' String');
INSERT INTO t_concat(`number`, `string`) VALUES(201, strContent);
SET strContent = CONCAT(202, ' 连接字符串');
INSERT INTO t_concat(`number`, `string`) VALUES(202, strContent);
SET strContent = CONCAT(203, strEN);
INSERT INTO t_concat(`number`, `string`) VALUES(203, strContent);
SET strContent = CONCAT(204, strCN);
INSERT INTO t_concat(`number`, `string`) VALUES(204, strContent);
SET strContent = CONCAT(varNum, ' String');
INSERT INTO t_concat(`number`, `string`) VALUES(205, strContent);
SET strContent = CONCAT(CAST(varNum AS CHAR), ' CONCAT字符串');
INSERT INTO t_concat(`number`, `string`) VALUES(206, strContent);
SET strContent = CONCAT(CONVERT(varNum USING utf8), ' CONCAT字符串');
INSERT INTO t_concat(`number`, `string`) VALUES(207, strContent);
SET strContent = CONCAT(varNum, strEN);
INSERT INTO t_concat(`number`, `string`) VALUES(208, strContent);
SET strContent = CONCAT(varNum, strCN);
INSERT INTO t_concat(`number`, `string`) VALUES(209, strContent);
SET strContent = CONCAT(varNum, ' MySQL 5.5 Replication 报错');
INSERT INTO t_concat(`number`, `string`) VALUES(210, strContent);
SET @varTemp = strContent;
INSERT INTO t_concat(`number`, `string`) VALUES(211, @varTemp);
COMMIT;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_concat_fail`()
BEGIN
DECLARE varNum int(4) DEFAULT 9999;
INSERT INTO t_concat(`number`, `string`) VALUES(301, CONCAT(varNum, ' MySQL 5.5 Replication 报错'));
-- 测试 int()类型变量与中文字符串 CONCAT()
COMMIT;
END$$
DELIMITER ;
SHOW master status;
/*
如果二进制日志是 raw 格式，想看日志中的 SQL 需要改 事务隔离等级 和 二进制日志 格式，
因为InnoDB 在READ-COMMITTED 下是不允许以statement格式写二进制日志的，会报错：
ERROR 1598 (HY000): Binary logging not possible. Message:
Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
SQL 语句如下：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET binlog_format = statement;
select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation, @@binlog_format;
查看 binlog 命令：
mysqlbinlog -vv -d fclub_notrans --start-position=11401 mysql-bin.000001 > /tmp/test_concat.sql
*/
SELECT CONCAT('CALL \`', routine_schema,'\`\.\`', routine_name, '\`\(\);') AS `CALL PROCEDURE`
FROM information_schema.routines WHERE routine_schema = 'fclub_notrans';
/*
以下内容为binlog：
t_concat.number ==> 存储过程
10x `proc_concat_pass`()
20x `proc_concat_test`()
301 `proc_concat_fail`()
前 2 个存储过程写到 binlog 中的 SQL 在MySQL 5.1 和 MySQL 5.5 上执行都没有问题，
第 3 个存储过程写到 binlog 的 SQL 只能在 MySQL 5.1 上执行，MySQL 5.5 执行报错，导致 slave sql_thread 停止运行。
############################ binlog ########################################
# at 733468891
#141024 10:37:25 server id 212005 end_log_pos 733468968 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118245/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
BEGIN
/*!*/;
# at 733468968
#141024 10:37:25 server id 212005 end_log_pos 733470308 Query thread_id=356360 exec_time=0 error_code=0
use fclub_notrans/*!*/;
SET TIMESTAMP=1414118245/*!*/;
INSERT INTO t_concat(`number`, `string`)
VALUES(101, CONCAT(101, ' String')), -- 测试 数字 和 字符串 CONCAT()
(102, CONCAT(102, ' 连接字符串')), -- 测试 数字 与 中文字符串 CONCAT()
(103, CONCAT(103, NAME_CONST('strEN',_utf8' String Variable' COLLATE 'utf8_general_ci'))), -- 测试 数字 和 英文字符串变量 CONCAT()
(104, CONCAT(104, NAME_CONST('strCN',_utf8' 字符串变量' COLLATE 'utf8_general_ci'))), -- 测试 数字 和 中文字符串变量 CONCAT()
(105, CONCAT( NAME_CONST('varNum',9999), ' String')), -- 测试 int()类型变量与英文字符串 CONCAT()
(106, CONCAT(CAST( NAME_CONST('varNum',9999) AS CHAR), ' CONCAT字符串')), -- 测试 int()类型变量 CAST(var AS char) 之后与中文字符串 CONCAT()
(107, CONCAT(CONVERT( NAME_CONST('varNum',9999) USING utf8), ' CONCAT字符串')), -- 测试 int()类型变量 CONVERT(var using utf8) 之后与中文字符串 CONCAT()
(108, CONCAT( NAME_CONST('varNum',9999), NAME_CONST('strEN',_utf8' String Variable' COLLATE 'utf8_general_ci'))), -- 测试 int()类型变量与英文字符串变量 CONCAT()
(109, CONCAT( NAME_CONST('varNum',9999), NAME_CONST('strCN',_utf8' 字符串变量' COLLATE 'utf8_general_ci')))
/*!*/;
# at 733470308
#141024 10:37:25 server id 212005 end_log_pos 733470335 Xid = 47758287
COMMIT/*!*/;
# at 733473016
#141024 10:37:30 server id 212005 end_log_pos 733473093 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733473093
#141024 10:37:30 server id 212005 end_log_pos 733473288 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(201, NAME_CONST('strContent',_utf8'201 String' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733473288
#141024 10:37:30 server id 212005 end_log_pos 733473315 Xid = 47758320
COMMIT/*!*/;
# at 733473315
#141024 10:37:30 server id 212005 end_log_pos 733473392 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733473392
#141024 10:37:30 server id 212005 end_log_pos 733473596 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(202, NAME_CONST('strContent',_utf8'202 连接字符串' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733473596
#141024 10:37:30 server id 212005 end_log_pos 733473623 Xid = 47758322
COMMIT/*!*/;
# at 733473623
#141024 10:37:30 server id 212005 end_log_pos 733473700 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733473700
#141024 10:37:30 server id 212005 end_log_pos 733473904 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(203, NAME_CONST('strContent',_utf8'203 String Variable' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733473904
#141024 10:37:30 server id 212005 end_log_pos 733473931 Xid = 47758324
COMMIT/*!*/;
# at 733473931
#141024 10:37:30 server id 212005 end_log_pos 733474008 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733474008
#141024 10:37:30 server id 212005 end_log_pos 733474212 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(204, NAME_CONST('strContent',_utf8'204 字符串变量' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733474212
#141024 10:37:30 server id 212005 end_log_pos 733474239 Xid = 47758326
COMMIT/*!*/;
# at 733474239
#141024 10:37:30 server id 212005 end_log_pos 733474316 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733474316
#141024 10:37:30 server id 212005 end_log_pos 733474512 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(205, NAME_CONST('strContent',_utf8'9999 String' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733474512
#141024 10:37:30 server id 212005 end_log_pos 733474539 Xid = 47758328
COMMIT/*!*/;
# at 733474539
#141024 10:37:30 server id 212005 end_log_pos 733474616 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733474616
#141024 10:37:30 server id 212005 end_log_pos 733474821 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(206, NAME_CONST('strContent',_utf8'9999 CONCAT字符串' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733474821
#141024 10:37:30 server id 212005 end_log_pos 733474848 Xid = 47758330
COMMIT/*!*/;
# at 733474848
#141024 10:37:30 server id 212005 end_log_pos 733474925 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733474925
#141024 10:37:30 server id 212005 end_log_pos 733475130 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(207, NAME_CONST('strContent',_utf8'9999 CONCAT字符串' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733475130
#141024 10:37:30 server id 212005 end_log_pos 733475157 Xid = 47758332
COMMIT/*!*/;
# at 733475157
#141024 10:37:30 server id 212005 end_log_pos 733475234 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733475234
#141024 10:37:30 server id 212005 end_log_pos 733475439 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(208, NAME_CONST('strContent',_utf8'9999 String Variable' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733475439
#141024 10:37:30 server id 212005 end_log_pos 733475466 Xid = 47758334
COMMIT/*!*/;
# at 733475466
#141024 10:37:30 server id 212005 end_log_pos 733475543 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733475543
#141024 10:37:30 server id 212005 end_log_pos 733475748 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(209, NAME_CONST('strContent',_utf8'9999 字符串变量' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733475748
#141024 10:37:30 server id 212005 end_log_pos 733475775 Xid = 47758336
COMMIT/*!*/;
# at 733475775
#141024 10:37:30 server id 212005 end_log_pos 733475852 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733475852
#141024 10:37:30 server id 212005 end_log_pos 733476070 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(210, NAME_CONST('strContent',_utf8'9999 MySQL 5.5 Replication 报错' COLLATE 'utf8_general_ci'))
/*!*/;
# at 733476070
#141024 10:37:30 server id 212005 end_log_pos 733476097 Xid = 47758338
COMMIT/*!*/;
# at 733476097
#141024 10:37:30 server id 212005 end_log_pos 733476174 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
BEGIN
/*!*/;
# at 733476174
#141024 10:37:30 server id 212005 end_log_pos 733476247 User_var
SET @`varTemp`:=_utf8 0x39393939204D7953514C20352E35205265706C69636174696F6E20E68AA5E99499 COLLATE `utf8_general_ci`/*!*/;
# at 733476247
#141024 10:37:30 server id 212005 end_log_pos 733476381 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118250/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(211, @varTemp)
/*!*/;
# at 733476381
#141024 10:37:30 server id 212005 end_log_pos 733476408 Xid = 47758340
COMMIT/*!*/;
# at 733527094
#141024 10:37:35 server id 212005 end_log_pos 733527171 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118255/*!*/;
BEGIN
/*!*/;
# at 733527171
#141024 10:37:35 server id 212005 end_log_pos 733527364 Query thread_id=356360 exec_time=0 error_code=0
SET TIMESTAMP=1414118255/*!*/;
INSERT INTO t_concat(`number`, `string`) VALUES(301, CONCAT( NAME_CONST('varNum',9999), ' MySQL 5.5 Replication 报错'))
/*!*/;
# at 733527364
#141024 10:37:35 server id 212005 end_log_pos 733527391 Xid = 47758437
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment