MySQL1864 master from error solving-MYSQL

MySQL1864 master from error solving

Technical otaku 2021-12-20

Look at it literally because the default value of slave_pending_jobs_size_max is 16777216 (16MB), but the slave_pending_jobs_size_max received by the slave is 17085453 (17M);

Solution

Execute the following SQL from the library

mysql>stop slave;
mysql>set global slave_pending_jobs_size_max=20000000;
mysql> start slave;

#When multi-threaded copying, the maximum memory occupied by pending events in the queue, the default is 16M, if the memory is surplus, or the delay is large, you can increase it appropriately; 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 operation:

mysql> update erp_mkpf set usnam='Test 1864 error';
ERROR 1197 (HY000): Multi-statement transaction required more than'max_binlog_cache_size' bytes of storage; 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
mysql> commit;
Query OK, 0 rowsaffected (0.08 sec)

Check status from library:

mysql> show slavestatusG
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 of slave_pending_jobs_size_max.

The above is the Mysql 1864 master-slave error solution introduced by the editor.I hope it will be helpful to you.If you have any questions, please leave me a message.The editor will reply to you in time.Thank you very much for your support to the website!

Technical otaku

Sought technology together