Mysql 1864 master-slave error solution, mysql1864

Mysql 1864 master-slave error solution, mysql1864

The literal meaning is that slave_pending_jobs_size_max defaults to 16777216 (16 MB), but slave_pending_jobs_size_max received by slave is 17085453 (17 M );

Solution

Run the following SQL statement from the database:

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

# Maximum memory occupied by Pending events in the queue during multi-thread replication. The default value is 16 Mb. If the memory is surplus or the latency is large, you can increase the value accordingly. Note:

This value is greater than max_allowed_packet of the master database.

Fault Simulation:

Slave database settings

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)

Master database 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 againmysql> 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)

View the status from the database:

mysql> show slavestatus\GLast_SQL_Errno: 1864Last_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_jobs_size_max.

The above is a small series of Solutions to Mysql 1864 master-slave errors, I hope to help you, if you have any questions, please leave a message, the small series will reply to you in a timely manner. Thank you very much for your support for the help House website!