Mysql 1864 Master-Slave error Resolution _MYSQL

The literal meaning is because the Slave_pending_jobs_size_max default value is 16777216 (16MB), but Slave received Slave_pending_jobs_size_max is 17085453 (17M) ；

Solution

Execute SQL from library as follows

Mysql>stop slave;
Mysql>set Global slave_pending_jobs_size_max=20000000;
mysql> start slave;

#在多线程复制时, the maximum memory that is occupied by the event pending in the queue is 16M, if the memory is surplus, or the delay is large, it can be adjusted properly; note

This value is larger than the max_allowed_packet of the main library.

Fault simulation:

Set from library

mysql> set global slave_parallel_workers=4;
Mysql> Show variables like ' slave_parallel_workers ';
+------------------------+-------+
| variable_name | Value |
+------------------------+-------+
|slave_parallel_workers | 4 |
+------------------------+-------+
1 row in Set (0.00SEC)
Mysql>set Global slave_pending_jobs_size_max= 1024;
Mysql> Show variables like ' slave_pend% ';
+-----------------------------+-------+
| variable_name | Value |
+-----------------------------+-------+
|slave_pending_jobs_size_max | 1024 |
+-----------------------------+-------+
1 row in Set (0.00SEC)

Main Library Operations:

mysql> Update ERP_MKPF set usnam= ' test 1864 error ';
ERROR 1197 (HY000): multi-statement transaction required more than ' max_binlog_cache_size ' bytes ofstorage; Increase this mysqld variable and try again
mysql> set global max_binlog_cache_size=8388608000000;
Query OK, 0 rowsaffected (0.00 sec)
mysql> begin;
Query OK, 0 rowsaffected (0.00 sec)
mysql> update ERP_MKPF set usnam= ' test 1864 error ';
Query OK, 70466 rowsaffected (0.38 sec)
Rows matched:70466 changed:70466 warnings:0
commit;
Query OK, 0 rowsaffected (0.08 sec)

To view the status from a library:

Mysql> show Slavestatus\g
last_sql_errno:1864
last_sql_error:cannot scheduleevent Update_rows, Relay-log name./he1-relay-bin.000005 position 494 Toworker thread because its size 8200 exceeds 1024 ofslave_pending_job S_size_max.

The above is a small set to introduce the MySQL 1864 Master-slave error solution, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!