I have lost my slave. To restore it I have locked tables on the master host and then have created a dump using mydumper tool. After that I have tried to restore my slave using myloader tool but during the restoration process I´ve got one critical error:

** (myloader:190XXXX): WARNING **: 18:28:15.810: Thread 1: Error restoring 1: Duplicate entry 'ТОБXX' for key 'UX_B_SEARCH_STEM'

** (myloader:190XXXX): WARNING **: 18:28:15.810: Thread 1: Retrying last failed executed statement
** Message: 18:28:15.958: Thread 3: restoring egats_tixmi.b_search_stem part 1 of 1 from egats_tixmi.b_search_stem.00000.sql. Progress 113 of 488. Tables 0 of 1263 completed

** (myloader:190XXXX): CRITICAL **: 18:28:19.072: Thread 1: Error restoring: Duplicate entry 'ТОБXX' for key 'UX_B_SEARCH_STEM'

** (myloader:190XXXX): CRITICAL **: 18:28:19.072: Error occurs between lines: 4 and 7798 on file tixmi.b_search_stem.00000.sql: Duplicate entry 'ТОБXX' for key 'UX_B_SEARCH_STEM'

** (myloader:190XXXX): CRITICAL **: 18:28:18.070: Thread 1: issue restoring tixmi.b_search_stem.00000.sql:

How critical is that? Right now my slave working without any issues but looks like the data identity between master and slave couldn't be 100% guaranteed. What would be the best strategy to be sure that all is good without repeating the whole restoration process?

We are running a MySQL DB master server version 8.0.36-0ubuntu0.22.04.1 and MySQL DB slave server version 8.0.39-0ubuntu0.22.04.1, where replication fails after a few hours with this in /var/log/mysql/error.log. It essentially states that a DELETE failed because the data to be deleted is already gone:

2024-08-14T06:06:17.465912Z 4713 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 2 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000004, end_log_pos 400124893; Could not execute Delete_rows event on table studip.files_search_index; Can't find record in 'files_search_index', Error_code: 1032; Can't find record in 'files_search_index', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log mysql-bin.000004, end_log_pos 400124893, Error_code: MY-001032
2024-08-14T06:06:17.485692Z 4711 [ERROR] [MY-010586] [Repl] Error running query, replica SQL thread aborted. Fix the problem, and restart the replica SQL thread with "START REPLICA". We stopped at log 'mysql-bin.000004' position 399886012

The corresponding binlog for the DELETE is as follows:

#240807 10:03:52 server id 43810  end_log_pos 400124460 CRC32 0xe04896d7        Anonymous_GTID  last_committed=165526   sequence_number=165528  rbr_only=yes    original_committed_timestamp=1723017832033182   immediate_commit_timestamp=1723017832033182     transaction_length=543
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1723017832033182 (2024-08-07 10:03:52.033182 CEST)
# immediate_commit_timestamp=1723017832033182 (2024-08-07 10:03:52.033182 CEST)
/*!80001 SET @@session.original_commit_timestamp=1723017832033182*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 400124460
#240807 10:03:52 server id 43810  end_log_pos 400124537 CRC32 0xc1595468        Query   thread_id=270960        exec_time=0     error_code=0
SET TIMESTAMP=1723017832/*!*/;
BEGIN
/*!*/;
# at 400124537
#240807 10:03:52 server id 43810  end_log_pos 400124613 CRC32 0x04bf430a        Table_map: studip.files_search_index mapped to number 1810
# has_generated_invisible_primary_key=0
# at 400124613
#240807 10:03:52 server id 43810  end_log_pos 400124893 CRC32 0xe5c70d05        Delete_rows: table id 1810 flags: STMT_END_F
### DELETE FROM studip.files_search_index
### WHERE
###   @1='cb866f5c9f049fe6ce7bca65761aae70'
###   @2='1.0 Einführung.mp4'
###   @3=0.992793           
### DELETE FROM studip.files_search_index
### WHERE
###   @1='cb866f5c9f049fe6ce7bca65761aae70'
###   @2=''
###   @3=0.939031           
### DELETE FROM studip.files_search_index
### WHERE
###   @1='cb866f5c9f049fe6ce7bca65761aae70'
###   @2='dozent'
###   @3=0.953527           
### DELETE FROM studip.files_search_index
### WHERE
###   @1='cb866f5c9f049fe6ce7bca65761aae70'
###   @2='Logistics Management (Vorlesung)'
###   @3=0.85378            
# at 400124893
#240807 10:03:52 server id 43810  end_log_pos 400124924 CRC32 0x9f3edfda        Xid = 27543716
COMMIT/*!*/;
# at 400124924

However, the data which the DELETE fails to find and delete is there, when you do a manual lookup:

mysql> select * from files_search_index where file_ref_id='cb866f5c9f049fe6ce7bca65761aae70';
+----------------------------------+------------------------------------+-----------+
| file_ref_id                      | text                               | relevance |
+----------------------------------+------------------------------------+-----------+
| cb866f5c9f049fe6ce7bca65761aae70 | 1.0 Einführung.mp4                 |  0.992793 |
| cb866f5c9f049fe6ce7bca65761aae70 |                                    |  0.939031 |
| cb866f5c9f049fe6ce7bca65761aae70 | dozent                             |  0.953527 |
| cb866f5c9f049fe6ce7bca65761aae70 | Logistics Management (Vorlesung)   |   0.85378 |
+----------------------------------+------------------------------------+-----------+
4 rows in set (0.00 sec)

If we delete this record manually, we run into a similar problem later, always in the same table, files_search_index. Any ideas?

We have Master-Slave replication but since +1h none changes are replicated because slave is stuck on 'System lock': (dh@DB02) [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXXXXXXXXX Master_User: XXXXXXXXXXX Master_Port: 3306 Connect_Retry: 10 Master_Log_File: XXXXXXXX-VM011-bin.008576 Read_Master_Log_Pos: 779580207 Relay_Log_File: DB-02-relay-bin.000494 Relay_Log_Pos: 377550713 Relay_Master_Log_File: XXXXXXXX-VM011-bin.008576 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 508746847 Relay_Log_Space: 977983642 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: 4998 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: System lock 1 row in set (0.00 sec) I have checked event on Master server and it is: MariaDB [(none)]> show binlog events in 'XXXXXXXX-VM011-bin.008576' from 508746847 limit 1; +---------------------------+-----------+------------+-----------+-------------+---------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------------+-----------+------------+-----------+-------------+---------------------------+ | XXXXXXXX-VM011-bin.008576 | 508746847 | Gtid | 1 | 508746889 | BEGIN GTID 0-1-6324465194 | +---------------------------+-----------+------------+-----------+-------------+---------------------------+ 1 row in set (0.00 sec) Slave: - Server version: 5.5.5-10.2.13-MariaDB-log MariaDB Server - Process list query shows only sleeping queries. I don't see any load on replication server. Can any one help me what to do?

I am building a HA Postgres db cluster with one master and multiple slaves. Please look at the below points and correct me if I am wrong: 1. For each slave node, I need to use pg_basebackup to sync and setup stream replication with the master node. 2. Setup Patroni on each node so that the leader can be elected from any of the available nodes in case of master failure (In this case, if the master fails, a slave node will be elected as master and write operations will be handled by it. Now if the master comes online and becomes the master of the cluster, then will the master sync with the previous master i.e. updated slaves). 3. Setup Etcd on a node and configure it in the Patroni files of each node.