Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.27, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:
MariaDB [(none)]> SHOW SLAVE STATUS\G
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 172.30.0.154
|
Master_User: repl
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mariadb-bin.000055
|
Read_Master_Log_Pos: 593262418
|
Relay_Log_File: ip-172-30-0-228-relay-bin.000002
|
Relay_Log_Pos: 33725828
|
Relay_Master_Log_File: mariadb-bin.000039
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: No
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 1062
|
Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 705697132
|
Relay_Log_Space: 17101184558
|
Until_Condition: None
|
Until_Log_File:
|
Until_Log_Pos: 0
|
Master_SSL_Allowed: No
|
Master_SSL_CA_File:
|
Master_SSL_CA_Path:
|
Master_SSL_Cert:
|
Master_SSL_Cipher:
|
Master_SSL_Key:
|
Seconds_Behind_Master: NULL
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 1062
|
Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...'
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 1
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 0-1-42163518
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: aggressive
|
SQL_Delay: 0
|
SQL_Remaining_Delay: NULL
|
Slave_SQL_Running_State:
|
1 row in set (0.00 sec)
|
However, when the problem is analyzed, it is clear that the duplicate key should not occur.
The configuration:
[mariadb]
|
server_id=2
|
log_bin=mariadb-bin
|
log_slave_updates
|
binlog_format=MIXED
|
slave_parallel_threads=16
|
slave_parallel_max_queued=1048576
|
slave_parallel_mode=aggressive
|
The configuration with slave_parallel_mode=optimistic was identical.
Some observations:
- When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real.
- The SQL script contains an UPDATE and a DELETE before the INSERT, so the row should have been deleted, which should have prevented the duplicate key error.
- The row still exists, so it seems that the DELETE statement had no effect.
- The values in the existing row in the table are the old non-updated values, so it seems that the UPDATE statement also had no effect.
- The slave's binary log contains both the UPDATE and DELETE statements, which should indicate that the statements were successfully executed on the slave.
- I haven't yet been able to reproduce the problem when the master has binlog_format=ROW set, so row-based replication may be immune to the problem.
Attachments
Issue Links
- blocks
-
MDEV-17814 Server crashes in is_current_stmt_binlog_format_row
-
- Open
-
-
MDEV-30225 RR isolation violation with locking unique search
-
- Closed
-
- causes
-
MDEV-6860 Parallel async replication hangs on a Galera node
-
- Closed
-
-
MDEV-33802 Weird read view after ROLLBACK of other transactions.
-
- Closed
-
- is blocked by
-
MDEV-26206 gap lock is not set if implicit lock exists
-
- Closed
-
- is duplicated by
-
MDEV-28200 Rows just inserted sometimes not returned by select afterwards
-
- Closed
-
- relates to
-
MDEV-10962 Deadlock with 3 concurrent DELETEs by unique key
-
- Closed
-
-
MDEV-14589 InnoDB should not lock a delete-marked record
-
- Closed
-
-
MDEV-18648 slave_parallel_mode= optimistic default in 10.5
-
- Closed
-
-
MDEV-19544 Remove innodb_locks_unsafe_for_binlog
-
- Closed
-
-
MDEV-20628 If temporary error occurs with optimistic mode of parallel replication, error message has false information
-
- Closed
-
-
MDEV-24224 Gap lock on delete in 10.5 using READ COMMITTED
-
- Closed
-
-
MDEV-25757 Assertion `!lock->is_waiting()' in lock_discard_for_index
-
- Closed
-
-
MDEV-26475 InnoDB must not lock delete-marked records
-
- Open
-
-
MDEV-27557 InnoDB unnecessarily commits mtr during secondary index search to preserve clustered index latching order
-
- Closed
-
-
MDEV-28422 Page split breaks a gap lock
-
- Closed
-
-
MDEV-30010 Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
-
- Closed
-
-
MDEV-16208 [Draft] Assertion `lock_get_wait(other_lock)' failed in lock_rec_queue_validate
-
- Closed
-
-
MDEV-16406 Refactor the InnoDB record locks
-
- Open
-
-
MDEV-17843 Assertion `page_rec_is_leaf(rec)' failed in lock_rec_queue_validate upon SHOW ENGINE INNODB STATUS
-
- Closed
-
-
MDEV-18706 ER_LOCK_DEADLOCK on concurrent read and insert into already locked gap
-
- In Review
-
-
MDEV-20604 Duplicate key value is silently truncated to 64 characters in print_keydup_error
-
- Closed
-
-
MDEV-22634 Assertion `dict_index_is_clust(index) || !dict_index_is_online_ddl(index)' failed in lock_rec_create_low
-
- Closed
-
-
MDEV-23197 Assertion `(index)->is_clust() || !dict_index_is_online_ddl(index)' failed. in lock_rec_create_low
-
- Closed
-
-
MDEV-24224 Gap lock on delete in 10.5 using READ COMMITTED
-
- Closed
-
-
MDEV-27025 insert-intention lock conflicts with waiting ORDINARY lock
-
- Closed
-
-
MDEV-30777 InnoDB: Failing assertion: pcur->restore_position(BTR_MODIFY_TREE, mtr) == btr_pcur_t::SAME_ALL
-
- Closed
-
- mentioned in
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
Activity
Field | Original Value | New Value |
---|---|---|
Link |
This issue relates to |
Description |
Some users are seeing duplicate key errors like the following when using aggressive mode of parallel replication:
{noformat} MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.0.154 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000055 Read_Master_Log_Pos: 593262418 Relay_Log_File: ip-172-30-0-228-relay-bin.000002 Relay_Log_Pos: 33725828 Relay_Master_Log_File: mariadb-bin.000039 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Skip_Counter: 0 Exec_Master_Log_Pos: 705697132 Relay_Log_Space: 17101184558 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-42163518 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: aggressive SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) {noformat} However, when the problem is analyzed, it is clear that the duplicate key should not occur. The configuration: {noformat} [mariadb] server_id=2 log_bin=mariadb-bin log_slave_updates binlog_format=MIXED slave_parallel_threads=16 slave_parallel_mode=aggressive {noformat} Some observations: * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real. * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error. * The row still exists, so it seems that the {{DELETE}} statement had no effect. * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect. * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave. |
Some users are seeing duplicate key errors like the following when using aggressive mode of parallel replication:
{noformat} MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.0.154 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000055 Read_Master_Log_Pos: 593262418 Relay_Log_File: ip-172-30-0-228-relay-bin.000002 Relay_Log_Pos: 33725828 Relay_Master_Log_File: mariadb-bin.000039 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Skip_Counter: 0 Exec_Master_Log_Pos: 705697132 Relay_Log_Space: 17101184558 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-42163518 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: aggressive SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) {noformat} However, when the problem is analyzed, it is clear that the duplicate key should not occur. The configuration: {noformat} [mariadb] server_id=2 log_bin=mariadb-bin log_slave_updates binlog_format=MIXED slave_parallel_threads=16 slave_parallel_max_queued=1048576 slave_parallel_mode=aggressive {noformat} Some observations: * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real. * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error. * The row still exists, so it seems that the {{DELETE}} statement had no effect. * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect. * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave. |
Summary | With aggressive mode of parallel replication, some replicated statements have no effect | With optimistic and aggressive modes of parallel replication, some replicated statements have no effect |
Description |
Some users are seeing duplicate key errors like the following when using aggressive mode of parallel replication:
{noformat} MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.0.154 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000055 Read_Master_Log_Pos: 593262418 Relay_Log_File: ip-172-30-0-228-relay-bin.000002 Relay_Log_Pos: 33725828 Relay_Master_Log_File: mariadb-bin.000039 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Skip_Counter: 0 Exec_Master_Log_Pos: 705697132 Relay_Log_Space: 17101184558 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-42163518 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: aggressive SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) {noformat} However, when the problem is analyzed, it is clear that the duplicate key should not occur. The configuration: {noformat} [mariadb] server_id=2 log_bin=mariadb-bin log_slave_updates binlog_format=MIXED slave_parallel_threads=16 slave_parallel_max_queued=1048576 slave_parallel_mode=aggressive {noformat} Some observations: * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real. * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error. * The row still exists, so it seems that the {{DELETE}} statement had no effect. * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect. * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave. |
Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:
{noformat} MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.0.154 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000055 Read_Master_Log_Pos: 593262418 Relay_Log_File: ip-172-30-0-228-relay-bin.000002 Relay_Log_Pos: 33725828 Relay_Master_Log_File: mariadb-bin.000039 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Skip_Counter: 0 Exec_Master_Log_Pos: 705697132 Relay_Log_Space: 17101184558 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-42163518 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: aggressive SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) {noformat} However, when the problem is analyzed, it is clear that the duplicate key should not occur. The configuration: {noformat} [mariadb] server_id=2 log_bin=mariadb-bin log_slave_updates binlog_format=MIXED slave_parallel_threads=16 slave_parallel_max_queued=1048576 slave_parallel_mode=aggressive {noformat} The configuration with {{slave_parallel_mode=optimistic}} was identical. Some observations: * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real. * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error. * The row still exists, so it seems that the {{DELETE}} statement had no effect. * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect. * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave. |
Affects Version/s | 10.3 [ 22126 ] |
Link |
This issue relates to |
Status | Open [ 1 ] | In Progress [ 3 ] |
Description |
Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:
{noformat} MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.0.154 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000055 Read_Master_Log_Pos: 593262418 Relay_Log_File: ip-172-30-0-228-relay-bin.000002 Relay_Log_Pos: 33725828 Relay_Master_Log_File: mariadb-bin.000039 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Skip_Counter: 0 Exec_Master_Log_Pos: 705697132 Relay_Log_Space: 17101184558 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-42163518 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: aggressive SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) {noformat} However, when the problem is analyzed, it is clear that the duplicate key should not occur. The configuration: {noformat} [mariadb] server_id=2 log_bin=mariadb-bin log_slave_updates binlog_format=MIXED slave_parallel_threads=16 slave_parallel_max_queued=1048576 slave_parallel_mode=aggressive {noformat} The configuration with {{slave_parallel_mode=optimistic}} was identical. Some observations: * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real. * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error. * The row still exists, so it seems that the {{DELETE}} statement had no effect. * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect. * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave. |
Some users are seeing duplicate key errors like the following when using the optimistic and aggressive modes of parallel replication:
{noformat} MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.0.154 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000055 Read_Master_Log_Pos: 593262418 Relay_Log_File: ip-172-30-0-228-relay-bin.000002 Relay_Log_Pos: 33725828 Relay_Master_Log_File: mariadb-bin.000039 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Skip_Counter: 0 Exec_Master_Log_Pos: 705697132 Relay_Log_Space: 17101184558 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '...' for key 'name'' on query. Default database: 'sample3'. Query: 'INSERT INTO ...' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-42163518 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: aggressive SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) {noformat} However, when the problem is analyzed, it is clear that the duplicate key should not occur. The configuration: {noformat} [mariadb] server_id=2 log_bin=mariadb-bin log_slave_updates binlog_format=MIXED slave_parallel_threads=16 slave_parallel_max_queued=1048576 slave_parallel_mode=aggressive {noformat} The configuration with {{slave_parallel_mode=optimistic}} was identical. Some observations: * When the table is queried on the slave, the row does indeed exists, so it is clear that the duplicate key error is real. * The SQL script contains an {{UPDATE}} and a {{DELETE}} before the {{INSERT}}, so the row should have been deleted, which should have prevented the duplicate key error. * The row still exists, so it seems that the {{DELETE}} statement had no effect. * The values in the existing row in the table are the old non-updated values, so it seems that the {{UPDATE}} statement also had no effect. * The slave's binary log contains both the {{UPDATE}} and {{DELETE}} statements, which *should* indicate that the statements were successfully executed on the slave. * I haven't yet been able to reproduce the problem when the master has {{binlog_format=ROW}} set, so row-based replication may be immune to the problem. |
Attachment | MDEV-20605.patch [ 49204 ] |
Component/s | Storage Engine - InnoDB [ 10129 ] | |
Assignee | Andrei Elkin [ elkin ] | Matthias Leich [ mleich ] |
Status | In Progress [ 3 ] | Stalled [ 10000 ] |
Assignee | Matthias Leich [ mleich ] | Thirunarayanan Balathandayuthapani [ thiru ] |
Attachment | thiru_20605.yy [ 49219 ] |
Link |
This issue relates to |
Status | Stalled [ 10000 ] | In Progress [ 3 ] |
Status | In Progress [ 3 ] | Stalled [ 10000 ] |
Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Marko Mäkelä [ marko ] |
Priority | Major [ 3 ] | Critical [ 2 ] |
Link | This issue blocks MDEV-17814 [ MDEV-17814 ] |
Link | This issue relates to MDEV-18706 [ MDEV-18706 ] |
Affects Version/s | 5.5 [ 15800 ] | |
Affects Version/s | 10.0 [ 16000 ] | |
Affects Version/s | 10.1 [ 16100 ] | |
Affects Version/s | 10.4 [ 22408 ] |
Assignee | Marko Mäkelä [ marko ] | Vladislav Lesin [ vlad.lesin ] |
Assignee | Vladislav Lesin [ vlad.lesin ] | Julien Fritsch [ julien.fritsch ] |
Assignee | Julien Fritsch [ julien.fritsch ] | Vladislav Lesin [ vlad.lesin ] |
Rank | Ranked higher |
Status | Stalled [ 10000 ] | In Progress [ 3 ] |
Link | This issue relates to MDEV-16406 [ MDEV-16406 ] |
Assignee | Vladislav Lesin [ vlad.lesin ] | Julien Fritsch [ julien.fritsch ] |
Assignee | Julien Fritsch [ julien.fritsch ] | Vladislav Lesin [ vlad.lesin ] |
Link |
This issue relates to |
Labels | AssistToday |
Link | This issue relates to MENT-289 [ MENT-289 ] |
Link |
This issue relates to |
Labels | AssistToday |
Link | This issue blocks MENT-289 [ MENT-289 ] |
Assignee | Vladislav Lesin [ vlad.lesin ] | Marko Mäkelä [ marko ] |
Link |
This issue blocks |
Link |
This issue relates to |
Link |
This issue relates to |
Link |
This issue relates to |
Link |
This issue relates to |
Link |
This issue relates to |
Assignee | Marko Mäkelä [ marko ] | Vladislav Lesin [ vlad.lesin ] |
Link |
This issue relates to |
Link |
This issue relates to |
Link |
This issue blocks |
Fix Version/s | 10.5 [ 23123 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31101 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31114 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31118 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31131 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31137 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31138 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31139 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31153 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31213 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31224 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31246 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31303 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31315 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31332 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31346 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31365 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31376 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31390 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31399 ] |
Labels | ServiceNow |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31412 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31420 ] |
Labels | ServiceNow | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31434 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31438 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31448 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31467 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31476 ] |
Link |
This issue is blocked by |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31487 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31487 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31503 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31527 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31535 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31556 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31570 ] |
Labels | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31592 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31605 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31619 ] |
Link | This issue relates to MDEV-26475 [ MDEV-26475 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31636 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31702 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31712 ] |
Link |
This issue relates to |
Remote Link | This issue links to "Page (Confluence)" [ 31729 ] |
Remote Link | This issue links to "Page (Confluence)" [ 31752 ] |
Remote Link | This issue links to "Page (Confluence)" [ 31801 ] |
Link |
This issue relates to |
Remote Link | This issue links to "Page (Confluence)" [ 32018 ] |
Remote Link | This issue links to "Page (Confluence)" [ 32111 ] |
Remote Link | This issue links to "Page (Confluence)" [ 32204 ] |
Remote Link | This issue links to "Page (Confluence)" [ 32223 ] |
Remote Link | This issue links to "Page (Confluence)" [ 32236 ] |
Remote Link | This issue links to "Page (Confluence)" [ 32257 ] |
Remote Link | This issue links to "Page (Confluence)" [ 32304 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32324 ] |
Attachment | MDEV-20605-restore-cursor.test [ 60381 ] |
Attachment | row0upd.cc.diff [ 60406 ] |
Attachment | MDEV-20605-deadlock.test [ 60407 ] |
Attachment |
|
Attachment | MDEV-20605-deadlock.test [ 60412 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32414 ] |
Attachment |
|
Attachment | MDEV-20605-deadlock.test [ 60437 ] |
Attachment | row0upd.cc.diff [ 60406 ] |
Link |
This issue relates to |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32508 ] |
Attachment | MDEV-20605-restore-cursor-v2.test [ 60741 ] |
Attachment | MDEV-20605-locking.test [ 60742 ] |
Link |
This issue relates to |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32613 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32627 ] |
Workflow | MariaDB v3 [ 99695 ] | MariaDB v4 [ 144523 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32636 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32650 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32659 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32674 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32688 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32721 ] |
Link | This issue relates to MENT-289 [ MENT-289 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32742 ] |
Link |
This issue relates to |
Status | In Progress [ 3 ] | In Testing [ 10301 ] |
Assignee | Vladislav Lesin [ vlad.lesin ] | Matthias Leich [ mleich ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32902 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32924 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 32932 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33018 ] |
Assignee | Matthias Leich [ mleich ] | Vladislav Lesin [ vlad.lesin ] |
Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
Status | Stalled [ 10000 ] | In Progress [ 3 ] |
Summary | With optimistic and aggressive modes of parallel replication, some replicated statements have no effect | Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration |
Component/s | Replication [ 10100 ] |
Fix Version/s | 10.5.16 [ 27508 ] | |
Fix Version/s | 10.6.8 [ 27506 ] | |
Fix Version/s | 10.7.4 [ 27504 ] | |
Fix Version/s | 10.8.3 [ 27502 ] | |
Fix Version/s | 10.2 [ 14601 ] | |
Fix Version/s | 10.3 [ 22126 ] | |
Fix Version/s | 10.4 [ 22408 ] | |
Fix Version/s | 10.5 [ 23123 ] | |
Resolution | Fixed [ 1 ] | |
Status | In Progress [ 3 ] | Closed [ 6 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33103 ] |
Link | This issue blocks MENT-1414 [ MENT-1414 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33108 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33116 ] |
Fix Version/s | 10.3.35 [ 27512 ] | |
Fix Version/s | 10.4.25 [ 27510 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33220 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31605 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33249 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33279 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 31434 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33647 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33648 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33650 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33653 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33700 ] |
Link |
This issue relates to |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33722 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33650 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33828 ] |
Link |
This issue is duplicated by |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34272 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34310 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34320 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34410 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34450 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34451 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34453 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34477 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34478 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34481 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34490 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34481 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34618 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34630 ] |
Link |
This issue relates to |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34637 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34647 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34662 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34681 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34703 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34719 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34815 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34637 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34923 ] |
Link |
This issue relates to |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34978 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35221 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35227 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35258 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33648 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 33700 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35221 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35280 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35290 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35301 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35311 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35321 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35402 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35412 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35415 ] |
Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35436 ] |
Link |
This issue causes |
Zendesk Related Tickets | 201658 136328 160775 151737 | |
Zendesk active tickets | 201658 |
Attachment | MDEV-20605-mysql.test [ 74058 ] |
Link |
This issue blocks |