# How to verify data consistency for replication(master:window OS, slave:Ubuntu OS)? **URL:** \ **Category:** Percona Toolkit\ **Created:** [May 28, 2015, 6:06am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243 "2015-05-28T06:06:04Z")\ **Posts on this page:** 16\ **Page:** 2
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 4, 2015, 11:21pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/21 "2015-06-04T23:21:26Z")
Hi Scott, I tried to run pt-table-checksum on a single database at a time using the --databases option and --no-check-replication-filters command: replication@replication:/$ pt-table-checksum --databases=SUBS\_270315\_9NKD3 --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… it is correct? It wont show the process like below output? TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 06-04T15:46:35 0 0 0 1 0 0.062 mysql.columns\_priv 06-04T15:46:35 0 0 47 1 0 0.058 mysql.db 06-04T15:46:35 0 0 0 1 0 0.051 mysql.event 06-04T15:46:35 0 0 0 1 0 0.050 mysql ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 5, 2015, 10:31am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/22 "2015-06-05T10:31:44Z")
Hi gpuser; It should show you the output with a list of tables it checked after it is finished. When you wrote the above was the tool still running? You could add the --progress=time,300 to see if it’s still working on something. It can take quite a while for large tables. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 6, 2015, 10:30am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/23 "2015-06-06T10:30:11Z")
Hi Scott, Yes, when I type command: pt-table-checksum --progress=time,300 --databases=subs\_270315\_9nkd3 --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns The output is : Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… Waiting for the --replicate table to replicate to replication… I ran it for whole day still the same output. any advice? ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 6, 2015, 4:57pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/24 "2015-06-06T16:57:31Z")
Hi gpuser; Depending on your table sizes and number of tables, it could take a long time to run. I have not seen your specific output message before, so not sure if it is stuck waiting on that indefinitely, or if it is just outputting that off and on as it is doing other work. To get a better idea of what it is doing, you could try it in debug mode, but that will output a lot of data, so be careful: PTDEBUG=1 pt-table-checksum --progress=time,300 --databases=subs\_270315\_9nkd3 --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns \> /tmp/pt.log 2\>&1 Other than that, I would check the processlist on the master and slave to see if it is actually doing work still (if you decide not to try debug). -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 7, 2015, 10:16am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/25 "2015-06-07T10:16:53Z")
Hi Scott, I run the debug command and store in google drive. [https://drive.google.com/file/d/0B\_1idIUKpJq\_bVI0U0dqNlZvRDQ/view?usp=sharing](https://drive.google.com/file/d/0B_1idIUKpJq_bVI0U0dqNlZvRDQ/view?usp=sharing) Please assist me have a look into it. What I have seen is it keeps looping below error # TableParser:4441 18067 Checking `percona`.`checksums` # TableParser:4445 18067 SHOW TABLES FROM `percona` LIKE ‘checksums’ # TableParser:4451 18067 DBD::mysql::db selectrow\_arrayref failed: Unknown database ‘percona’ [for Statement “SHOW TABLES FROM `percona` LIKE ‘checksums’”] at /usr/bin/pt-table-checksum line 4448. Thank you very much. ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 8, 2015, 10:51am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/26 "2015-06-08T10:51:08Z")
Hi gpuser; It looks like it is complaining about a missing percona database. The issue is probably that your percona database is not getting replicated to the slave due to your replication filters. You could try to manually create the percona database on your slave, and then run it again and see if that works. If that does not work, then what you’ll likely need to do is add “percona” to the list of databases to replicate with the replicate-do-db command in your my.cnf configuration file. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 9, 2015, 5:48am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/27 "2015-06-09T05:48:06Z")
Hi Scott, From Slave dsns table IP, I should put master IP? Because from master dsns I put slave IP ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 9, 2015, 6:05am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/28 "2015-06-09T06:05:11Z")
Hi Scott, I able to run it with manually create the percona database on your slave (dsns put master IP) and add “percona” to the list of databases to replicate with the replicate-do-db command in my.cnf configuration file But here return me some errors: Below link is the txt file that I had save. [https://drive.google.com/file/d/0B\_1idIUKpJq\_cHByeFd2VUU2Y3M/view?usp=sharing](https://drive.google.com/file/d/0B_1idIUKpJq_cHByeFd2VUU2Y3M/view?usp=sharing) ## Below is 2 of the error return: 06-09T19:24:50 Skipping table subs\_270315\_9nkd3.gl\_gstreturntrack because it has problems on these replicas: Table subs\_270315\_9nkd3.gl\_gstreturntrack does not exist on replica replication This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check. 06-09T19:24:50 Error checksumming table subs\_270315\_9nkd3.gl\_gstreturntrack: DBD::mysql::db selectrow\_hashref failed: Table ‘subs\_270315\_9nkd3.gl\_gstreturntrack’ doesn’t exist [for Statement “EXPLAIN SELECT \* FROM `subs_270315_9nkd3`.`gl_gstreturntrack` WHERE 1=1”] at /usr/bin/pt-table-checksum line 6521. ## 06-09T19:24:59 Skipping table subs\_270315\_9nkd3.imp\_gl\_doubleentry because it has problems on these replicas: Table subs\_270315\_9nkd3.imp\_gl\_doubleentry on replica replication is missing these columns: projectcode, docdate This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check. 06-09T19:24:59 Skipping table subs\_270315\_9nkd3.imp\_gl\_doubleentrykoff because it has problems on these replicas: Table subs\_270315\_9nkd3.imp\_gl\_doubleentrykoff does not exist on replica replication This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check. 06-09T19:24:59 Error checksumming table subs\_270315\_9nkd3.imp\_gl\_doubleentrykoff: DBD::mysql::db selectrow\_hashref failed: Table ‘subs\_270315\_9nkd3.imp\_gl\_doubleentrykoff’ doesn’t exist [for Statement “EXPLAIN SELECT \* FROM `subs_270315_9nkd3`.`imp_gl_doubleentrykoff` WHERE 1=1”] at /usr/bin/pt-table-checksum line 6521. ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 9, 2015, 11:53am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/29 "2015-06-09T11:53:35Z")
Hi gpuser; Do those tables actually exist on the slave? Looks like it is saying one table has a different structure and the other just does not exist at all. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 10, 2015, 1:57am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/30 "2015-06-10T01:57:59Z")
Hi Scott, I checked the table is in Slave. From Slave dsns table IP, I should put master IP? Because from master dsns I put slave IP Any advise or how should I troubleshoot it? ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 10, 2015, 2:06am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/31 "2015-06-10T02:06:08Z")
Hi Scott, from the slave percona DB, checksum table do not record the table. But in the actual DB, the table is existed. ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 10, 2015, 9:48am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/32 "2015-06-10T09:48:25Z")
Hi gpuser; The dsns table on the master should have the connection information for the slave. The dsns table on the slave can be the same as the master since normally it would be replicated. The tool is saying that those tables do no exist / have a different structure. So if you think that is wrong, you can try the --no-check-slave-tables option it mentions. But as it tells you, that will break replication if the tables are actually no there / have the wrong structure. So use that at your own risk. I would verify your connection information in the master dsns table and make sure that the IP and username/password actually work to connect to the slave from the host you are running pt-table-checksum from as well. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 24, 2015, 4:16am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/33 "2015-06-24T04:16:05Z")
Hi Scott, I tried to run on one DB only it everything look fine without error. ## Command: :~$pt-table-checksum --databases=SUBS\_270315\_9NKD3 --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns ## TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 06-24T17:43:39 0 0 0 1 0 0.093 subs\_270315\_9nkd3.ao\_ccpayment 06-24T17:43:39 0 0 0 1 0 0.082 subs\_270315\_9nkd3.ao\_ccpaymentchangeset 06-24T17:43:39 0 0 1 1 0 0.083 subs\_270315\_9nkd3.ao\_ccterminal When i tried to check all database and it return below output: :~$pt-table-checksum --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns * * * ## Waiting to check replicas for differences: 0% 00:00 remain Waiting to check replicas for differences: 0% 00:00 remain Waiting to check replicas for differences: 0% 00:00 remain I tried to debug it with below command: :~$PTDEBUG=1 pt-table-checksum --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns \> /tmp/pt.log 2\>&1 * * * Waiting to check replicas for differences: 0% 00:00 remain # pt\_table\_checksum:11144 5583 Sleep 1.25 waiting for chunks # pt\_table\_checksum:11111 5583 replication max chunk: undef # pt\_table\_checksum:11144 5583 Sleep 1.25 waiting for chunks # pt\_table\_checksum:11111 5583 replication max chunk: undef * * * Should I wait longer time to let it finish load the waiting to check replicas for differences? ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 24, 2015, 7:28pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/34 "2015-06-24T19:28:58Z")
Hi gpuser; Are your replication filters preventing some of the databases from being replicated still? If it works on one of them, then it likely is due to your replication configuration. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 25, 2015, 5:07am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/35 "2015-06-25T05:07:16Z")
Hi Scott, When I tried to checksum 2 DB, it run as normal but return me some error output in regards chunk size limit replication@replication:~$ pt-table-checksum --databases=subs\_300115\_0yh4k,subs\_270315\_dbpxq --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password=–recursion-method dsn=D=percona,t=dsns \> /home/replication/2506.txt 06-25T18:19:31 Skipping table subs\_270315\_dbpxq.mc\_postcode because on the master it would be checksummed in one chunk but on these replicas it has too many rows: 2819 rows on replication The current chunk size limit is 2794 rows (chunk size=1397 \* chunk size limit=2.0). how to resolve this? ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 25, 2015, 10:25pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/36 "2015-06-25T22:25:44Z")
Hi gpuser; Try upping your --chunk-size-limit to 3 and give it another go. Take a look at the below link prior so you know what is happening and the warning about it potentially causing additional load on your system: [url][pt-table-checksum — Percona Toolkit Documentation](https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum--chunk-size-limit%5B/url%5D) -Scott [Previous page](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243.md?page=1)