MySQL Forums
Forum List  »  InnoDB

Master-Slave
Posted by: abhishek singh
Date: March 04, 2019 12:35PM

I have a master-slave setup, which was running fine with out any issues. 3 days back i have loaded close to 1 TB data in Master DB, since then Slave is having issues and i can see the errors like in slave logs. table1_trn is the biggest table and it took close to 8 hrs to load it in Master DB

Error
=========
22019-03-04T18:00:32.463342Z 2 [Warning] Slave SQL for channel '': Could not execute Write_rows event on table myschmea.table1_trn; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.005104, end_log_pos 60822466, Error_code: 1205


mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 35.245.123.137
Master_User: rep_usr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.005478
Read_Master_Log_Pos: 66834772
Relay_Log_File: dep-mysql-dr-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.005104
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1205
Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

Skip_Counter: 0
Exec_Master_Log_Pos: 60814710
Relay_Log_Space: 365616622420
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: 1205
Last_SQL_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2769629684
Master_UUID: 53275cd4-0f8b-11e9-b80e-42010aef0002
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 190304 13:09:37
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 53275cd4-0f8b-11e9-b80e-42010aef0002:43820247-46808745
Executed_Gtid_Set: 53275cd4-0f8b-11e9-b80e-42010aef0002:29983027-29983029:29985300-29985420:29986454-43820246,
ab6ea708-2966-11e9-82ec-42010aac0414:1-44202
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

Options: ReplyQuote


Subject
Views
Written By
Posted
Master-Slave
306
March 04, 2019 12:35PM
177
March 04, 2019 02:10PM
167
March 04, 2019 03:52PM
837
March 04, 2019 04:37PM
179
March 05, 2019 04:10AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.