mysql update 1205_MySQL主从复制出现1205错误

MySQL > show slave status\G;

Last_SQL_Errno: 1205

Last_SQL_Error: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'gcore'. Query: 'INSERT INTO `GC_Price_Archive` (`PKID`, `Barcode`, `Price`, `STID`, `STCode`, `CRCode`, `State`, `OptTime`, `CreatedTime`, `Remarks`) VALUES ('1625018', '6900451666012', '10.80', '10665', '02710664', '2706', '1', '2013-07-23 13:46:47', '2013-07-23 14:40:55', '3')'

查看mysql正在运行线程

mysql > show full processlist;

| 93862 | xxxxx      | 192.168.3.177:1775 | xxxxx      | Query   |     53 | Sending data                     | select count(distinct month(opttime)) into g from gcore.GC_Price_Archive A where A.barcode= NAME_CONST('barcodea',6901404321200) and A.state=1 and A.opttime>='2012-07-01 00:00:00' and A.opttime

上面的sql执行线程与出现锁等待超时的query均使用GC_Price_Archive表，对这个等待超时导致同步停掉的问题表示很不理解。

为了让同步继续运行，我kill掉了正在执行的select查询，然后 start slave SQL_Thread。同步继续运行。

如果从服务器上经常要执行报表统计查询，可以在配置文件my.cnf中修改innodb_rollback_on_timeout选项，默认值是50，改成更大的值，避免slave执行sql经常出现锁等待超时。

The timeout in seconds an InnoDB transaction waits for a row lock before giving up.The default value is

50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits

at most this many seconds for write access to the row before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is rolled back (not the entire

transaction). To have the entire transaction roll back, start the server with the --

innodb_rollback_on_timeout [1605] option. See also Section 14.3.13, “InnoDB Error Handling”.

You might decrease this value for highly interactive applications or OLTP systems, to display user

feedback quickly or put the update into a queue for processing later. You might increase this value for

long-running back-end operations, such as a transform step in a data warehouse that waits for other

large insert or update operations to finish.

innodb_lock_wait_timeout [1595] applies to InnoDB row locks only. A MySQL table lock does not

happen inside InnoDB and this timeout does not apply to waits for table locks.

The lock wait timeout value does not apply to deadlocks, because InnoDB detects them immediately

and rolls back one of the deadlocked transactions.

MySQL主从复制Error1205

主从架构。今天发现从库SQL线程报错，主从复制停止了。查看错误发现：

Last_SQL_Errno: 1205

Last_SQL_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

查看错误日志发现：

2016-05-25 07:27:09 72865 [Warning] Slave SQL: Could not execute Write_rows event on table xxx.xxx; Lock wait timeout exceeded; try restarting transaction, Error_code:

1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.000121, end_log_pos 21432849, Error_code: 1205

2016-05-25 07:27:09 72865 [ERROR] Slave SQL: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable. Error_code: 1205

2016-05-25 07:27:09 72865 [Warning] Slave: Lock wait timeout exceeded; try restarting transaction Error_code:1205

2016-05-25 07:27:09 72865 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000121' position 21432467

解决办法：

start slave;

原因：

是MySQL的一个bug，可以考虑把patch给打上