Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL part of the 5.6 version of the rare copy error report ERROR 1837 how to deal with, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Scene:

1. Simple "one master, one slave" version of MySQL-5.6.20

2 、 master_auto_position=0

3. Enable gtid

The error is as follows:

Last_SQL_Errno: 1837

Last_SQL_Error: Error 'When @ @ SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @ @ SESSION.GTID_NEXT is' c44bd915-440d-11e6-8ea0-6c92bf24b8c0virtual 71844624. On query. Default database:'$db'. Query: 'DELETE FROM `db2`.`tb2`

After reading the wrong newspaper, you look confused, is it possible that the main library is doing something coquettish?

Check the GTID point corresponding to the main library binlog, and you can find something:

(data desensitization has been done, for example, the above two tables are distinguished by db1.tb1 and db2.tb2 respectively)

It is found that GTID has done the following between [c44bd915-440d-11e6-8ea0-6c92bf24b8c0:71844624] and [71844625]:

USE db3

DELETE FROM `db1`.`tb1`

DELETE FROM `db2`.`tb2`

There is no more between DELETE FROM `db_ 1`.`t1` and DELETE FROM `db_ 2`.`t2`: SET @ @ SESSION.GTID_NEXT.

This seems to violate the restriction of GTID that a transaction should correspond to a GTID number.

Combined with the error message, it is suspected that when the second DELETE is executed, an error will be reported because the second DELETE does not have a corresponding GTID_NEXT:

Last_SQL_Errno: 1837. Default database:'$db'. Query: 'DELETE FROM `db2`.`tb2`

Check the storage engine again and find that the tables db1.tb1 and db2.tb2 are both memory, that is, the non-transactional engine.

Maybe it has something to do with this.

Here, to fix this replication failure, do the following on the slave library:

0 SET SESSION sql_log_bin=0

0 execute unexecuted transactions manually, here is: DELETE FROM `db2`.`tb2`

0 SET SESSION sql_log_bin=1

0 STOP SLAVE sql_thread; SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC'; START SLAVE sql_thread

As to why there is an events that violates GTID when ENFORCE_GTID_CONSISTENCY is ON, I searched the bug library:

More discussions are as follows:

Https://bugs.mysql.com/bug.php?id=71695

The problem occurred in version 5.6.20 and prior to version 5.6.

And fixed this problem in versions after 5.6.21.

Find in the document:

Replication: When mysqlbinlog processed multiple binary log files into a single output file, this file was not ina useful

State for point-in-time recovery, when it failed with the error, When @ @ SESSION.GTID_NEXT is set to a GTID, you must

Explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for

Detailed explanation. Current @ @ SESSION.GTID_NEXT is' xyz'. When mysqlbinlog processes a binary log containing GTIDs

It outputs SET gtid_next statements, but gtid_next is set to undefined whenever a commit occurs; this left gtid_next

Undefined when the server had finished processing the output from mysqlbinlog. When the next binary log file started

With one or more anonymous statements or transactions, the combination of gtid_next being left undefined at the end

Of the first binary log and the second binary log containing anonymous transactions to the error described previously

(Error 1837, ER_GTID_NEXT_TYPE_UNDEFINED_GROUP)

To fix this issue, now, whenever mysqlbinlog encounters this situation, it inserts SET gtid_next = AUTOMATIC

If required to avoid leaving the previous binary log with gtid_next undefined.

In addition, as a result of this fix, mysqlbinlog no longer outputs session variable information for every binary log

Now, this value is printed only once unless it changes. (Bug # 18258933, Bug # 71695)

The general reasons are:

When mysqlbinlog processes a binlog that contains GTID, it outputs gtid_next, but when submitted, gtid_next is set to "undefined".

When the server finishes processing the output from mysqlbinlog, it leaves the binlog undefined.

To put it simply:

Because gtid_next may be set to undefined, replication occurs at 1837.

To fix this problem, the following fixes have been made in the MySQL5.6.21 version:

Whenever mysqlbinlog encounters this situation, it automatically adds the following statement:

"SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /;"

To avoid using gtid_next to retain the previous binlog undefined.

(this can be easily tested when GTID is turned on)

Later, I searched again and found that this problem may also occur when using INSERT DELAYED syntax.

Although the document description doesn't seem to look much like this case, it is rewarding:

Upgrade to a later version of MySQL.

Use the transaction engine as much as possible to avoid the possibility of operating both transaction and non-transaction tables in a transaction.

(although enforce_gtid_consistency is enabled, there may be statements that break through the restrictions on GTID and try to limit the business as much as possible.)

Avoid using INSERT DELAYED syntax as much as possible.

Thank you for reading this article carefully. I hope the article "MySQL part 5.6 rare copy error report ERROR 1837 how to deal with" shared by the editor is helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.