Help resolving pt-table-sync error messages

I’m trying to synchronize databases on two hosts, and am running into lock related errors. I changed the lock setting from 1 to 2 to 3 to try to resolve the issue, and although there are fewer errors, there are a couple I can’t resolve. My command is:

pt-table-sync --execute --verbose --user=un --password=pw --databases=asterisk --lock=3 --buffer-in-mysql h=otherhost h=localhost

and the errors (underlined in the excerpt) are:

[B][COLOR=#FF0000]# 0 0 0 0 Nibble 20:39:38 20:39:38 0 asterisk.sip

0 0 0 0 Nibble 20:39:38 20:39:38 0 asterisk.sipsettings

Can’t execute the query because you have a conflicting read lock [for Statement “INSERT INTO asterisk.smartscreen(refnum, number_start, number_end, display_name, action, created) VALUES (‘799’, ‘15196672050’, ‘15196672050’, ‘Some FAX’, ‘R’, ‘2006-10-24 00:47:06’) /percona-toolkit src_db:asterisk src_tbl:smartscreen src_dsn:P=3306,h=otherhost,p=…,u=un dst_db:asterisk dst_tbl:smartscreen dst_dsn:P=3306,h=localhost,p=…,u=un lock:3 transaction:0 changing_src:0 replicate:0 bidirectional:0 pid:23085 user:root host:myhost/”] at line 10709 while doing asterisk.smartscreen on localhost

0 0 0 0 0 20:39:38 20:39:38 1 asterisk.smartscreen

0 0 0 0 Chunk 20:39:38 20:39:38 0 asterisk.sng_mcu_details

0 0 0 0 Chunk 20:39:39 20:39:39 0 asterisk.xmpp_users

Failed to unlock server: I need a src argument at /mnt/lserver2/data/development/haast/bin/debug/library/pt-table-sync line 6105.[/B]

Can anyone explain the cause, and how to resolve this? (This is a unidirectional sync, from remote to local host). There is very little, if any, traffic on the databases at this time. So I don’t think an app is writing to/reading from the DB…I even restarted the mysql service but the errors remain.

I tried the same command, running with lock=2 and here are more errors:

[B][COLOR=#FF0000]# 0 0 0 0 Nibble 20:48:12 20:48:12 0 asterisk.sip

0 0 0 0 Nibble 20:48:12 20:48:12 0 asterisk.sipsettings

Table ‘smartscreen’ was locked with a READ lock and can’t be updated [for Statement “INSERT INTO asterisk.smartscreen(refnum, number_start, number_end, display_name, action, created) VALUES (‘799’, ‘15196672050’, ‘15196672050’, ‘FAX’, ‘R’, ‘2006-10-24 00:47:06’) /percona-toolkit src_db:asterisk src_tbl:smartscreen src_dsn:P=3306,h=otherhost,p=…,u=un dst_db:asterisk dst_tbl:smartscreen dst_dsn:P=3306,h=localhost,p=…,u=un lock:2 transaction:0 changing_src:0 replicate:0 bidirectional:0 pid:23870 user:root host:thishost/”] at line 10709 while doing asterisk.smartscreen on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.smartscreen

Table ‘sng_mcu_details’ was not locked with LOCK TABLES [for Statement “SELECT MIN(id), MAX(id) FROM asterisk.sng_mcu_details FORCE INDEX (PRIMARY)”] at line 4206 while doing asterisk.sng_mcu_details on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.sng_mcu_details

Table ‘sng_mcu_entries’ was not locked with LOCK TABLES [for Statement “SELECT MIN(id), MAX(id) FROM asterisk.sng_mcu_entries FORCE INDEX (PRIMARY)”] at line 4206 while doing asterisk.sng_mcu_entries on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.sng_mcu_entries

Table ‘sysadmin_options’ was not locked with LOCK TABLES [for Statement “SELECT MIN(key), MAX(key) FROM asterisk.sysadmin_options FORCE INDEX (key)”] at line 4206 while doing asterisk.sysadmin_options on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.sysadmin_options

Table ‘sysadmin_update_log’ was not locked with LOCK TABLES [for Statement “SELECT MIN(id), MAX(id) FROM asterisk.sysadmin_update_log FORCE INDEX (PRIMARY)”] at line 4206 while doing asterisk.sysadmin_update_log on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.sysadmin_update_log

Table ‘timeconditions’ was not locked with LOCK TABLES [for Statement “SELECT MIN(timeconditions_id), MAX(timeconditions_id) FROM asterisk.timeconditions FORCE INDEX (PRIMARY)”] at line 4206 while doing asterisk.timeconditions on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.timeconditions

Table ‘timegroups_details’ was not locked with LOCK TABLES [for Statement “SELECT MIN(id), MAX(id) FROM asterisk.timegroups_details FORCE INDEX (PRIMARY)”] at line 4206 while doing asterisk.timegroups_details on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.timegroups_details

Table ‘timegroups_groups’ was not locked with LOCK TABLES [for Statement “SELECT MIN(id), MAX(id) FROM asterisk.timegroups_groups FORCE INDEX (PRIMARY)”] at line 4206 while doing asterisk.timegroups_groups on localhost

0 0 0 0 0 20:48:12 20:48:12 1 asterisk.timegroups_groups

0 0 0 0 Nibble 20:48:12 20:48:12 0 asterisk.trunk_dialpatterns

0 0 0 0 Nibble 20:48:12 20:48:12 0 asterisk.trunks[/B]

Is there a parameter I missing or set incorrectly, which would resolve this?

What could make you to use –lock parameter of pt-table-sync ? If it’s replication slave i would suggest to use –sync-to-master parameter.

I have to run pt-table-sync from the slave (cannot be run from master). Unless I misunderstood manual, I the DSN must be of the slave (so wen run on the slave, it has no way of knowing what the master is).

Are you saying it is a mistake to specify any --lock setting? (If all lock settings except for ‘none’ cause failures, does that mean these are bugs or the option makes no sense?)