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

Deadlock Encountered when using pt-online-schema-change

eiosiseiosis EntrantCurrent User Role Beginner
I have a shell script looping 1000 times, doing inserts into a database table.
I am running pt-online-schema-change, creating a unique index on a column in that table.
pt-online-schema-change --alter-foreign-keys-method=auto --alter="ADD UNIQUE INDEX UQ_E_ID (E_ID)" --execute h=localhost,u=xx,p=**,D=db,t=t1

My shell script reports
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction

The deadlock reported is
pt-deadlock-logger h=localhost,u=xx,p=**,D=db,t=t1
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2016-02-02T10:40:06 13616 0 0 root localhost db t1 PRIMARY RECORD S w 0 INSERT LOW_PRIORITY IGNORE INTO `db.`_t1_new` (`uid`, `name`, `accountid`, `deleted`, `e_id`, `parent_id`, `classification`) SELECT `uid`, `name`, `accountid`, `deleted`, `e_id`, `parent_id`, `classification` FROM `db`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 23347 copy table*/
localhost 2016-02-02T10:40:06 13847 0 0 root localhost db _t1_new TABLE AUTO-INC w 1 REPLACE INTO `db`.`_t1_new` (`uid`, `name`, `accountid`, `deleted`, `e_id`, `parent_id`, `classification`) VALUES (NEW.`uid`, NEW.`name`, NEW.`accountid`, NEW.`deleted`, NEW.`e_id`, NEW.`parent_id`, NEW.`classification`)

We are seriously considering using this tool to prevent downtime in our application when we need to modify our database schema, but am quite concerned by this finding. Can anyone suggest work-arounds or problems with my commands.
Thanks in advance!


  • eiosiseiosis Entrant Current User Role Beginner
    mysql> show variables like '%version%';
    | Variable_name | Value |
    | innodb_version | 5.5.46 |
    | protocol_version | 10 |
    | slave_type_conversions | |
    | version | 5.5.46-0ubuntu0.14.04.2 |
    | version_comment | (Ubuntu) |
    | version_compile_machine | x86_64 |
    | version_compile_os | debian-linux-gnu |
    7 rows in set (0.00 sec)

    pt-online-schema-change --version
    pt-online-schema-change 2.2.14
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.