{"id":2312,"date":"2015-10-19T19:51:17","date_gmt":"2015-10-19T19:51:17","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2312"},"modified":"2024-01-27T17:29:36","modified_gmt":"2024-01-27T17:29:36","slug":"pt-table-checksum-waiting-to-check-replicas-for-differences-0-0000-remain","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/pt-table-checksum-waiting-to-check-replicas-for-differences-0-0000-remain","title":{"rendered":"pt-table-checksum & Waiting to check replicas for differences: 0% 00:00 remain"},"content":{"rendered":"\n

I recently came across an issue where the task was to checksum databases using Percona’s pt-table-checksum<\/a>. The command continued well for all the databases but when it came to specific DB, it kept on showing following error:

<\/small><\/p>\n\n\n\n

Waiting to check replicas for differences: 0% 00:00 remain<\/h1>\n\n\n\n

As this did not change for hours (was in screen session), I cross-checked that nothing blocks the process and other options!<\/p>\n\n\n\n\n\n\n\n

I killed pt-table-checksum & re-ran the command with PTDEBUG to get detailed debug info:<\/p>\n\n\n\n

[kedar@nitty-witty ~] PTDEBUG=1 \/usr\/bin\/pt-table-checksum --config \/home\/kedar\/pt-table-checksum-config   --recursion-method dsn=h=localhost,D=DB_A,t=dsns --replicate DB_A.checksums --no-check-replication-filters --no-check-binlog-format --chunk-time=3 --max-load Threads_running=18 --check-interval=1 --max-lag=1000 --no-check-plan --ignore-databases mysql,performance_schema,information_schema --databases DB_A,DB_B,DB_C,DB_D --resume > checksum.log 2>&1\n<\/pre>\n\n\n\n
The environment variable PTDEBUG enables verbose debugging output to STDERR.<\/i><\/p>\n\n\n\n
From the logs I found that the checksum command was stuck because following query was returning NULL:<\/p>\n\n\n\n
mysql> SELECT MAX(chunk) FROM `DB_A`.`checksums` WHERE db='DB_D' AND tbl='a_c' AND master_crc IS NOT NULL;\n+------------+\n| MAX(chunk) |\n+------------+\n|       NULL |\n+------------+\n1 row in set (0.00 sec)\n<\/pre>\n\n\n\n
I ran the query with only db=’DB_D’<\/i> condition and found that all the tables of this database returned NULL! The database DB_D was very well present on master and all the slaves.<\/p>\n\n\n\n
Debugging further get me to following status output of the slave:<\/p>\n\n\n\n
mysql> show slave status\\G\n*************************** 1. row ***************************\n               Slave_IO_State: Waiting for master to send event\n                  Master_Host: master.mysite.com\n                  Master_User: slave2\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: mysql-bin.000908\n          Read_Master_Log_Pos: 576546786\n               Relay_Log_File: mysqld-relay-bin.002738\n                Relay_Log_Pos: 576546932\n        Relay_Master_Log_File: mysql-bin.000908\n             Slave_IO_Running: Yes\n            Slave_SQL_Running: Yes\n              Replicate_Do_DB: DB_A,DB_B,DB_C<\/b>\n          Replicate_Ignore_DB: \n           Replicate_Do_Table: \n       Replicate_Ignore_Table: \n      Replicate_Wild_Do_Table: \n  Replicate_Wild_Ignore_Table: \n                   Last_Errno: 0\n                   Last_Error: \n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 576546786\n              Relay_Log_Space: 576547132\n              Until_Condition: None\n               Until_Log_File: \n                Until_Log_Pos: 0\n           Master_SSL_Allowed: No\n           Master_SSL_CA_File: \n           Master_SSL_CA_Path: \n              Master_SSL_Cert: \n            Master_SSL_Cipher: \n               Master_SSL_Key: \n        Seconds_Behind_Master: 0\nMaster_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error: \n               Last_SQL_Errno: 0\n               Last_SQL_Error: \n  Replicate_Ignore_Server_Ids: \n             Master_Server_Id: 1\n1 row in set (0.00 sec)\n<\/pre>\n\n\n\n
Look at “Replicate_Do_DB” closely -> “DB_A,DB_B,DB_C”. That’s right, DB_D is not replicated !!
So, that explains us the NULL results of max-chunk finding query and our error of waiting-to-check-replicas-for-differences.<\/p>\n\n\n\n
Well, so the solution here is, as DB_D does not replicate, we shall remove it from the list of databases to checksum. Add DB_D in to the list of –ignore-databases and rerun the command to get things started!<\/p>\n\n\n\n
[kedar@nitty-witty ~] PTDEBUG=1 \/usr\/bin\/pt-table-checksum --config \/home\/kedar\/pt-table-checksum-config   --recursion-method dsn=h=localhost,D=DB_A,t=dsns --replicate DB_A.checksums --no-check-replication-filters --no-check-binlog-format --chunk-time=3 --max-load Threads_running=18 --check-interval=1 --max-lag=1000 --no-check-plan --ignore-databases DB_D,mysql,performance_schema,information_schema --databases DB_A,DB_B,DB_C --resume > checksum.log 2>&1\n<\/pre>\n\n\n\n
This worked well and killed “Waiting to check replicas for differences: 0% 00:00 remain”.<\/p>\n\n\n\n
Note that we’re using –no-check-replication-filters so that our checksum will continue over the replication filters. Also review –replicate-database option which can also be used to specify databases to be USEd.<\/p>\n\n\n\n
I think fix in pt-table-checksum here could be that: In a condition where max-chunk returns NULL and slave is in sync (seconds_behind_master: 0), pt-table-checksum should exit with appropriate warning.<\/i>
I may be overlooking many other options but that’s something I could quickly think. Comment for corrections \/ if-and-buts.<\/p>\n\n\n\n
Hope this helps.<\/p>\n","protected":false},"excerpt":{"rendered":"This post discuss the pt-table-checksum & error “Waiting to check replicas for differences:   0% 00:00 remain” with a use-case and solution.\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,870,377],"tags":[575,576,572,574,573],"class_list":["post-2312","post","type-post","status-publish","format-standard","category-mysql","category-mysql-tools","category-mysql-articles","tag-data-consistency","tag-mysql-replication","tag-pt-table-checksum","tag-seconds_behind_master","tag-waiting-to-check-replicas"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t