I have not seen this error in a while with MySQL replication, " Lock wait timeout exceeded; try restarting transaction"
Check the slave status
show slave status \G; and noticed the following.
Slave_IO_Running: Yes
Slave_SQL_Running: No
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1205
Last_SQL_Error: Lock wait timeout exceeded; try restarting transaction
Solution
Stopped the Slave
stop slave;
Restarted the Slave
start slave;
Check the status and all is well
References
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 10.0.0.1
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.002933
Read_Master_Log_Pos: 832187423
Relay_Log_File: mysql-relay-bin.000230
Relay_Log_Pos: 832187707
Relay_Master_Log_File: binlog.002933
Slave_IO_Running: Connecting
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: 832187423
Relay_Log_Space: 832188044
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: 2003
Last_IO_Error: error reconnecting to master 'replicate@10.0.0.1:3306' - retry-time: 60 maximum-retries: 86400 message: Can't connect to MySQL server on '10.0.0.1' (110 "Connection timed out")
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:
STOP SLAVEReference
CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_LOG_FILE='binlog.002933', MASTER_LOG_POS=832187423;
START SLAVE
SHOW SLAVE STATUS
$link = @ mysql_connect('localhost', 'xxxx', 'xxxxxxxx');For starters you can see all the sleeping MySQL processes by running the command
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully'."\n";
$result = mysql_query("SHOW processlist");
while ($myrow = mysql_fetch_assoc($result)) {
if ($myrow['Command'] == "Sleep" && $myrow['Time']>0 && $myrow['User']!="root") {
//if ($myrow['Command'] == "Sleep" && $myrow['Time']>0) {
//mysql_query("KILL {$myrow['Id']}");
echo $myrow['User']." ".$myrow['Command']." ".$myrow['Time']." ".$myrow['State']."\n";
if($myrow['Time']>20){
//mysql_query("KILL {$myrow['Id']}");
echo "Killed process id: ". $myrow['Id']."\n";
}
}
}
mysql_close($link);
?>
show processlistYou will need to look out for all the threads with state being 'sleep. MySQL 5.1.7 allows filtering For e.g. you can find sleeping processes running over 5 seconds using the command below.
SELECT user, time, state, info FROM information_schema.processlist WHERE command = 'Sleep' AND time >5 ORDER BY time DESC, id;
When the network connection between a master and slave database is interrupted in a way that neither side can detect (like a firewall or routing change), you must wait until slave_net_timeout seconds have passed before the slave realizes that something is wrong. It will then try to reconnect to the master and pick up where it left off. This is bad. This could be 1 hour if the slave_net_timeout is set to 1 hour (3600 seconds)I discovered this issue when I noticed the slave was not up to date but there were no errors. However if the slave was stopped (STOP SLAVE) and started (START SLAVE), suddenly replication started again and everything was up to date.
Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'binlog.000202' at 307396531, the last event read from 'binlog.000202' at 4, the last byte read from 'binlog.000202' at 4.', Internal MariaDB error code: 1236It appear there was some issue on the server hosting the MariaDB and the server administrator opted to reboot. When the server restarted, replication stopped working.
mysqlbinlog --base64-output=decode-rows --verbose --verbose --start-position=307396531 binlog.000202I also peaked on the next log binlog.000203
mysqlbinlog --base64-output=decode-rows --verbose --verbose --start-position=0 binlog.000203On the Slave
Stop the slaveAll seems well, so I proceeded change the master log and master log position
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000203', MASTER_LOG_POS=4;
Start the slaveOnce done, check the Slave Status and all should be well.
show slave status\G;Please note that your binlog is e.g. mysql-bin.001 advance to the next log file mysql-bin-002
I foresee master server crashed or rebooted and hence binary log events not synchronized on disk. This usually happens when sync_binlog != 1 on the master. You can investigate it as inspecting binary log contents as below:user yogesh77 on the Percona Forum expanded this
"After sudden reboot mysql rolled back last transactions in binary logs however slave already incremented its binary position so after master is up slave is not able to get correct binary position. To resolve this issue you need to point slave to new binary file created after the server reboot and mysql restart. The same issue happened to be just 2 days back. After setting new binary position I skipped few entries on slave which were updated on slave and rolled back in binary position."How to prevent
SET GLOBAL sync_binlog=1;Add to my.cnf or server.cnf for MariaDB to make permanent.
sync_binlog=1;Also, try shutting down the server gracefully.