阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL主从复制出现1205错误

102次阅读
没有评论

共计 3083 个字符,预计需要花费 8 分钟才能阅读完成。

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<‘2013-07-01 00:00:00’ |

上面的 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 给打上

 

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-08/134759.htm

正文完
星哥说事-微信公众号
post-qrcode
 
星锅
版权声明:本站原创文章,由 星锅 2022-01-22发表,共计3083字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中