更新表数据，或者复制数据到新表出现这样问题ERROR 1180 (HY000): Got error 5 during COMMIT

最新推荐文章于 2025-04-29 09:56:16 发布

zhangxj_jack

最新推荐文章于 2025-04-29 09:56:16 发布

阅读量4.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/ahzxj2012/article/details/68483668

mysql 专栏收录该内容

27 篇文章

订阅专栏

本文介绍了如何通过调整MySQL的wsrep_max_ws_size参数来优化数据库性能，并展示了如何更改binlog_row_image参数以减少复制过程中的数据量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

解决方案如下：

mysql> select @@wsrep_max_ws_size;
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
| 1073741824 |
+---------------------+
1 row in set (0.00 sec)

mysql> set global wsrep_max_ws_size=1024*1024*1024*2;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@binlog_row_image;
+--------------------+
| @@binlog_row_image |
+--------------------+
| FULL |
+--------------------+
1 row in set (0.00 sec)

mysql> show global status like 'wsrep_replicated_bytes';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| wsrep_replicated_bytes | 65833629016 |
+------------------------+-------------+
1 row in set (0.00 sec)

mysql> select @@wsrep_max_ws_size;
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
| 2147483648 |
+---------------------+
1 row in set (0.00 sec)

mysql> select (13402597135-13211964556)/1024/1024 as ws_size;
+--------------+
| ws_size |
+--------------+
| 181.80139446 |
+--------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL binlog_row_image=minimal;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@binlog_row_image;
+--------------------+
| @@binlog_row_image |
+--------------------+
| FULL |
+--------------------+
1 row in set (0.00 sec)

mysql> select @@wsrep_max_ws_size;
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
| 2147483648 |
+---------------------+
1 row in set (0.00 sec)

mysql> create table t_exercise_chapter_record_tmp like t_exercise_chapter_record;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_exercise_chapter_record_tmp
-> (id,chapterid,quesids,right_quesids,start_date,end_date,machineId,remark,object_name,chapter_name,points,object_id,book_id,current_page)
-> select id,chapterid,quesids,right_quesids,start_date,end_date,machineId,remark,object_name,chapter_name,points,object_id,book_id,current_page
-> from t_exercise_chapter_record where start_date BETWEEN '2016-01-01 00:00:00' and '2017-03-30 10:30:00';
Query OK, 8932473 rows affected (12 min 33.60 sec)
Records: 8932473 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE t_exercise_chapter_record RENAME TO t_exercise_chapter_record_old;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE t_exercise_chapter_record_tmp RENAME TO t_exercise_chapter_record;
Query OK, 0 rows affected (0.00 sec)

修改了最大的写入量，就可以了