Announcement

Announcement Module
Collapse
No announcement yet.

Help resolving pt-table-sync error messages

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:

    # 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.
    Last edited by ocgltd; 08-24-2014, 08:51 PM.

  • #2
    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?
    Last edited by ocgltd; 08-24-2014, 08:52 PM.

    Comment


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

      Comment


      • #4
        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?)

        Comment

        Working...
        X