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
./do_sql.sh
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, classificationFROMdb.t1LOCK 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 INTOdb._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!