STATEMENT主从复制，Slave SQL执行异常

显示全部楼层 · 2011-4-15 13:42:53

本帖最后由 saup007 于 2015-6-15 14:18 编辑
mysql> select version();
+----------------------+
| version()
|
+----------------------+
| 5.1.57-community-log |
+----------------------+
1 row in set (0.09 sec)
mysql> show variables like '%binlog_format%'
-> ;
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

[ERROR] Slave SQL: Error 'Query execution was interrupted' on query. Default database: 'database_name'. Query: 'insert into t(col1,col1,....) select 'Demo1',col2,col3.... where'
Error_code: 1317

insert into PRODUCT
(ID,
STATE_DATE,
REC,
PRODUCT_ID,
VISIT_TIME,
ADD_TIMES,
ORDER_TIME,
SESSION,
IS_ID,
TYPE)
select 'Demo1',
STATE_DATE,
REC,
PRODUCT_ID,
VISIT_TIME,
ADD_TIMES,
ORDER_TIME,
SESSION,
IS_ID,
TYPE
from PRODUCT
where id = 'name'
and state_date = 20150614;

PRODUCT表上没有主键和索引
每天一个shell脚本，都要这么执行，最近每天早上都要手工处理
1、SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
2、手工在slave执行 insert into select
3、start slave;
什么原因导致的呢？按网上说是master执行一半，异常中止，slave有一致性风险，所以抛1317的error code
我可以尝试改下binlog_format 格式，明天脚本执行后，再看下效果，但出现这个问题的原因是什么呢？这个SQL在Slave有一致性风险？

千问 · 2011-4-15 13:42:53

我在线上做了一个测试
mysql> create database mysql_test;
Query OK, 1 row affected (0.04 sec)
mysql> createtable t1 (id varchar(10) default null,name varchar(10)) ;
Query OK, 0 rows affected (0.07 sec)
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| t1| CREATE TABLE `t1` (
`id` varchar(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t1('1','test1');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1','test1')' at line 1
mysql> insert into t1 values ('1','test1');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into t1 values ('2','test2');
Query OK, 1 row affected (0.00 sec)
----------
mysql> insert into t1(id,name)select '10' , name from t1 where id=1;
Query OK, 1 row affected (0.00 sec)
Records: 1Duplicates: 0Warnings: 0
在Slave查看正常同步。。。囧。。。