Shulou(Shulou.com)06/01 Report--

Recently, many groups of master and slave based on parallel replication (MTS) have been done, most of which are 5.6-> 5.7structure, and a few of them are 5.6-> 5.6parallel replication.

The configuration of each group of mmurs is similar, and there is a certain probability that the following errors will occur, but not all of them:

0 ERROR 1755:

Error scenario:

Master (5.6)-> Slave (5.6)

Related configuration:

Slave enables parallel replication:

Slave_parallel_workers > = 1.

Slave error message: (here 5.7Slave,5.6 is similar, but reason will be different)

…… Slave_IO_Running: Yes

Slave_SQL_Running: No

……

Last_Errno: 1755

Last_Error: Cannot execute the current event group in the parallel mode. Encountered event Gtid, relay-log name {directory} / relaylog/mysql-relay.000002, position 280408 which prevents execution of this event group in parallel mode. Reason: The master event is logically timestamped incorrectly.....

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1755

Last_SQL_Error: Cannot execute the current event group in the parallel mode. Encountered event Gtid, relay-log name {directory} / relaylog/mysql-relay.000002, position 280408 which prevents execution of this event group in parallel mode. Reason: The master event is logically timestamped incorrectly..

Replicate_Ignore_Server_Ids:

……

The error message is obvious:

Cannot execute the current event group in the parallel mode

Cannot execute the current event group in parallel mode

It is also possible to encounter this problem as a slave in 5. 6.

The error prompt and reason are clear, just turn off parallel replication:

STOP SLAVE

SET GLOBAL slave_parallel_workers=0

START SLAVE

It is also 1755 error reports. The following three reason may be given in the log collected:

① Reason:The master event is logically timestamped incorrectly (this may also have something to do with setting slave_parallel_type= "LOGICAL_CLOCK" on 5.7)

② Reason: possible malformed group of events from an old master

③ Reason:the events is a part of a group that is unsupported in the parallel execution mode.

To sum up, the reasons can be:

Under the replication structure of old version 5.6-> new version 5.6 / 5.7, the event of master does not record information about parallel replication.

It is possible to appear when Slave is 5.6and 5.7. it has been regarded as a BUG. Please refer to:

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

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

0 ERROR 1756

Error scenario:

Master (5 6)-> Slave (5 7)

Related configuration:

Slave enables parallel replication:

Slave_parallel_workers > = 1.

Slave_parallel_type='LOGICAL_CLOCK' .

Unlike 1755, there seems to be more possibility of a 1756 error.

Slave error message:

…… Slave_IO_Running: YesSlave_SQL_Running: No... Last_Errno: 1756

Last_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

…… Last_SQL_Errno: 1756

Last_SQL_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). The reason for reporting an error here:

The replication distribution object of Slave is called "logical_clock", but 5.6 is a parallel replication that only supports "database" granularity.

So why is this a problem when using logical_clock-based version 5. 7?

Because in binlog event 5.7, "last_committed" and "sequence_number" are added.

The former indicates the transaction number of the last commit when the transaction is committed. If the transaction has the same last_committed, it indicates that these transactions are in the same group and can be apply in parallel.

The emergence of these two is also the basis for the addition of parallel replication based on logical_clock.

No matter when GTID is enabled or GTID is disabled, the corresponding information will be generated.

In the 5.7source code, MYSQL_BIN_LOG defines two variables for Logical_clock:

Class MYSQL_BIN_LOG: public TC_LOG

{

...

Public:

/ * Committed transactions timestamp * /

Logical_clock max_committed_transaction

/ * "Prepared" transactions timestamp * /

Logical_clock transaction_counter

... max_committed_transaction: record the logical_clock, or last_committed, of the last committed transaction.

Transaction_counter: the logical_clock, or sequence_number, that records each transaction in the current group commit.

However, the binlog generated by 5.6does not have these records, so slave 5.7naturally cannot be replicated in parallel based on logical_clock.

In this case, it is easy to correct the problem:

STOP SLAVE

SET GLOBAL slave_parallel_type= "DATABASE"

START SLAVE; or turn off parallel replication, that is, as in 1755, set slave_parallel_workers=0

Unfortunately, there is more than one reason for the 1756 error.

For more information, please see:

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

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

.

One of the interesting things is that Yoshinori Matsunobu, the author of MHA, also encountered ERROR 1756:

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

The reason is that parallel replication does not support "slave_transaction_retries"

He found the description on rpl_slave.cc:

-

/ * MTS technical limitation no support of trans retry * /

If (mi- > rli- > opt_slave_parallel_workers! = 0 & & slave_trans_retries! = 0

Reproduce operation:

1. Set slave_transaction_retries to a higher value

two。 Enable parallel replication: slave_parallel_workers > = 0

3. On slave, hold a long-term InnoDB lock on the T1 table, such as BEGIN; UPDATE T1 SET astat100

4. Execute a conflicting statement on master and submit it to slave, such as UPDATE T1 SET astat100 WHERE id=1

The ERROR 1756 caused by this bug has been fixed in 5.7.5.

A summary of the ERROR 1755amp 1756:

Avoid parallel replication across versions.

Upgrade to a later version of 5.6.x to avoid parallel replication of the old version 5.6.

Upgrade to a later version of 5.7.x and avoid using the old version 5.7.

0 reference documentation

MySQL 5.7parallel replication implementation principle and tuning by Jiang Chengyao

Replication errors from MySQL 5.6to 5.7resolve by anonymity

Https://bugs.mysql.com/

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.