{"id":1474,"date":"2023-05-10T17:41:48","date_gmt":"2023-05-10T15:41:48","guid":{"rendered":"https:\/\/www.mcgill.org.za\/stuff\/?p=1474"},"modified":"2023-05-10T17:41:48","modified_gmt":"2023-05-10T15:41:48","slug":"last_io_errno-1595-relay-log-write-failure-could-not-queue-event-from-master","status":"publish","type":"post","link":"https:\/\/www.mcgill.org.za\/stuff\/archives\/1474","title":{"rendered":"Last_IO_Errno: 1595 &#8220;Relay log write failure: could not queue event from master&#8221;"},"content":{"rendered":"\n<p>The disk filled up on a mysql master database because of a few 80Gb queries being written to the mysql bin logs (somehow it couldn&#8217;t handle them).  The fix on the master was to have more disk space.  The fix on the slave was not the usual:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave<\/code><\/pre>\n\n\n\n<p>That basically made the slave spend a lot of time on the transaction, and then fail again.  The failure is not in the incoming SQL stream, but in the execution stream, and the execution stream is feeling stupid.  It says it like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>show slave status\\G\n                   Master_Host: 192.168.0.41\n                   Master_User: mastersvr10\n                   Master_Port: 3306\n                 Connect_Retry: 60\n               Master_Log_File: mastersvr4-bin.044956\n           Read_Master_Log_Pos: 2439692635\n                Relay_Log_File: mastersvr4-relay-bin.003988\n                 Relay_Log_Pos: 374519436\n         Relay_Master_Log_File: mastersvr4-bin.044954\n              Slave_IO_Running: <strong>No<\/strong>\n             Slave_SQL_Running: <strong>Yes<\/strong>\n                    Last_Errno: 0\n                    Last_Error:\n                  Skip_Counter: 0\n           Exec_Master_Log_Pos: 374519132\n               Relay_Log_Space: 53133251140\n         Seconds_Behind_Master: <strong>NULL<\/strong>\n                 Last_IO_Errno: <strong>1595<\/strong>\n                 Last_IO_Error: <strong>Relay log write failure: could not queue event from master\n<\/strong>                Last_SQL_Errno: 0\n                Last_SQL_Error:\n       Slave_SQL_Running_State: Update_rows_log_event::ha_update_row(-1)<\/code><\/pre>\n\n\n\n<p>Stopping and starting the slave basically causes the Slave_SQL_Running_State to toggle between Update_rows_log_event::ha_update_row(-1) and Update_rows_log_event::find_row(-1), and then finally stop trying.<\/p>\n\n\n\n<p>The fix is to reconnect to the master at the same place we left off.  The relevant parts of the status are the Master_Log_File, and Exec_Master_Log_Pos, which is how far we got in it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>show slave status ...\n               Master_Log_File: <strong>mastersvr4-bin.044956<\/strong>\n           Read_Master_Log_Pos: 2439692635\n                Relay_Log_File: mastersvr4-relay-bin.003988\n                 Relay_Log_Pos: 374519436\n           Exec_Master_Log_Pos: <strong>374519132<\/strong><\/code><\/pre>\n\n\n\n<p>And the fix is:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>STOP SLAVE;\nCHANGE MASTER TO MASTER_LOG_FILE='<strong>mastersvr4-bin.044956<\/strong>', MASTER_LOG_POS=<strong>374519132<\/strong>;\nSTART SLAVE;<\/code><\/pre>\n\n\n\n<p>And just like that, it&#8217;s happy again.<\/p>\n\n\n\n<p>Acknowledgements to <a href=\"https:\/\/shahalpk.name\/mysql-slave-log-corrupted-how-to-fix\/\">https:\/\/shahalpk.name\/mysql-slave-log-corrupted-how-to-fix\/<\/a> (who reconnected the password and everything, which is unnecessary in the normal case).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The disk filled up on a mysql master database because of a few 80Gb queries being written to the mysql bin logs (somehow it couldn&#8217;t handle them). The fix on the master was to have more disk space. The fix &hellip; <a href=\"https:\/\/www.mcgill.org.za\/stuff\/archives\/1474\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[274,20,291,190],"class_list":["post-1474","post","type-post","status-publish","format-standard","hentry","category-stuff","tag-mariadb","tag-mysql","tag-replication","tag-stuff"],"_links":{"self":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/1474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/comments?post=1474"}],"version-history":[{"count":1,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/1474\/revisions"}],"predecessor-version":[{"id":1475,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/1474\/revisions\/1475"}],"wp:attachment":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/media?parent=1474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/categories?post=1474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/tags?post=1474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}