pt-online-schema-change getting hanged

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

I’m using mysql 5.5.24 and percona-toolkit-2.1.2 version on linux platform.

Please correct me, if i’m wrong - As far as i know, we can make online schema change while the app is running using pt-online-schema-change tool.
However, i just execute one test case manually from command prompt and it got hanged.

Session-1:

mysql> select * from test;
±–±–+
| i | p |
±–±–+
| 3 | |
| 4 | |
±–±–+
2 rows in set (0.00 sec)

Session-2:

[mysql@obsidian bin]$ ./pt-online-schema-change --user=root --password=passwd --execute --alter=“add column v varchar(100) not null default ‘’” D=gaurav,t=test
Altering gaurav.test…
Creating new table…
Created new table gaurav._test_new OK.
Altering new table…
Altered gaurav._test_new OK.
Creating triggers…

Session-1:

mysql> show processlist;
…
| 10693 | root | localhost | gaurav | Query | 21 | Waiting for table metadata lock | CREATE TRIGGER pt_osc_gaurav_test_del AFTER DELETE ON gaurav.test FOR EACH ROW DELETE IGNORE FRO |
…

So, i need to release lock using COMMIT/ROLLBACK.
Plz guide - what wrong i’m doing here or what is exact use this tool.

Thanks.

2

Hi Gaurav,

A table can have only one trigger for INSERT, UPDATE and DELETE. But If there is one already then pt-online-schema-change can not work. Because pt-osc utility, also needs to create triggers on the original table to update the corresponding rows in the new table.
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch ema-change.html

As per above documentation, “The use of triggers means that the tool will not work if any triggers are already defined on the table”

So AFAIK, If you want to alter the table by pt-osc which contains triggers already. You have to dropped triggers first, alter the table with pt-osc and then again create triggers.

3

Hello,

Thks for guiding,however in my table no trigger been defined.

You can see from example that from one session i’m simply doing SELECT and from other session - I’m trying to ALTER table using pt-online-schema-change.

Please guide.

Regards,
Gaurav