Not the answer you need?
Register and ask your own question!

Help resolving pt-table-sync error messages

ocgltdocgltd EntrantCurrent User Role Beginner
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:

# 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.


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.

Comments

  • ocgltdocgltd Entrant Current User Role Beginner
    I tried the same command, running with lock=2 and here are more errors:

    # 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


    Is there a parameter I missing or set incorrectly, which would resolve this?
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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.
  • ocgltdocgltd Entrant Current User Role Beginner
    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?)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.