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

Trouble running toolkit on live table

doctor_regtoolsdoctor_regtools EntrantCurrent User Role Beginner
Hi

I'm trying to use percona toolkit to do a schema change on an active live table, and I'm having trouble at the creating triggers stage.

I'm running (something like)

pt-online-schema-change --max-lag 100000 --execute --alter "column_name int" t=table

the output gets as far as

2017-10-11T09:33:40 Creating triggers...

At this point it hangs (seemingly indefinitely but I gave it about 5 minutes). Meanwhile I can see a load of the following queuing up on the database:

Waiting for table metadata lock │ select count(*) from table where unique_id='[email protected]'
I've no idea what to do, so any help would be appreciated!

Thanks

Comments

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Hi,

    Could you share some environmental information like OS, MySQL version and Toolkit version and, if it is possible, the output for SHOW CREATE TABLE for that table?

    Thanks.
  • doctor_regtoolsdoctor_regtools Entrant Current User Role Beginner
    Thanks for the reply. This is RedHat EL6 running Maria DB 10.0.30, toolkit version 2.20.

    Show create table is like this (some stuff changed / removed due to corporate sensitivity)

    ┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
    ┃ Table ┃ Create Table ┃
    ┡━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
    │ name │ CREATE TABLE `name` ( │
    │ │ `id` int(10) unsigned NOT NULL AUTO_INCREMENT, │
    │ │ `date` datetime DEFAULT NULL, │
    │ │ PRIMARY KEY (`id`), │
    │ │ KEY `column-x` (`column_x`), │ │
    │ │ ) ENGINE=InnoDB AUTO_INCREMENT=77185309 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC │
    └───────┴─────────────────────────────────────────────────────────────────────────────────────┘

    Cheers
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Hi again,
    In your post there is a key on column_x but column_x is not in the fields list and the alter is invalid.
    I don't know how to reproduce the issue.
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.