We have a master-slave replication that was running perfectly until recently. Today i've found the error:

Query caused different errors on master and slave. Error on master: message (format)='Size of tree cache used in bulk insert optimisation. Note that this is a limit per thread!' error code=126; Error on slave:actual message='no error', error code=0. Default database:'dbname'. Query:'UPDATE table SET something='0', something2='' WHERE something3=4'

We are using GTID based replication. I tried to skip 1 GTID knowing that the query wasn't important anyway, but it didn't help (the slave sql didn't want to run). Only setting ignore_error = 126 worked, but then I turned it off immediately, and another query did the same thing.

The error that the slave shows for the master doesn't seem to be an error, only a documentation for bulk insert optimization.

What's the best way to make the replication work again?

edit: I've executed a problematic query on the master, and it worked.

  • Does the table have INDEX(something3)? How many rows are to be updated by that one statement? – Rick James Jun 6 '17 at 17:12
  • What is value of VARIABLE "tmpdir"? What filesystems are on the Master? Are any tight on disk space? Which version of MySQL? Was there a bulk-insert at about the same time, especially in the same transaction? – Rick James Jun 6 '17 at 17:16
  • The table has index on the where query. It was about to update ~700 rows. tmp_dir is at /tmp, which has 500GB free. Version of master is 5.6.30-log, of slave is 5.7.18-log. I don't think there was another query in that transaction, but there might be in the same time. – TheAnosmic Jun 7 '17 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.