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.

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

Hi Guarav,

I have installed mysql 5.5.24 and percona-toolkit-2.1.2 versions locally and tried to test. It works perfectly for me. I'm confused that why you are getting above error. As per status "Waiting for table metadata lock" , some process/transaction definitely running on that table from other session and that's why its getting metadata lock. metadata locks are kept for the duration of a transaction. Can you check, if someother process is not running on the same table while you are executing pt-osc?

You can get more information about metadata lock on below link. It might help.
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

Hello Joshi,

Thks for testing and providing us an update.
However, in my first post - i'm mentioning that i'm using 2 sessions.
Session-1: SELECT on test table
Session-2: Doing online schema change.

i got your point that for performing pt-online-schema-change there should not be any active transactions.

But then: What is its use? as if no active transactions - that means, i'm putting my app down. then i can do DDL changes directly.

As far my knowledge goes - online schema change means - doing schema change while app is running.

Please suggest.

Thanks.

Hi Guarav,

So the problem is that you start transaction in the 1st session (begin before select) right?
Until the transaction is not committed MySQL won't let you do any DDL change on this table, and it's not only pt-osc problem, try any alter instead. What if your transaction operates on a column you want to change, but you already inserted or updated values to this column before the commit? If you would be able to alter table in another session before committing the first one, than you would end up with inconsistent one.
Note that DDL operations do immediately implicit commit, and you cannot rollback them:
http://dev.mysql.com/doc/refman/5.5/...it-commit.html

In order to let pt-osc finish it's job you need to commit the transaction, and usually in OLTP operations the transactions are very short, and immediately committed, so not a problem.
pt-online-schema-change purpose is to let you do changes to a table without blocking it for updates for a long time, which is the case for normal alter. But final rename table, where pt-osc has to switch both new and old table needs the table's metadata not locked by any transaction, so it just waits for that lock release, not hangs.

Hi Przemek,
Thks for providing insight information.

Yes, you got it right that i'm holding the transaction from one session and performing DDL on 2nd session.
I got the concept and usage of pt-online-schema-change, but still 1 doubt:

How can we perform DDL over DB with ZERO downtime - Don't want to stop my app/JBOSS?

What we are doing right now is on any env including production:
1> STOP JBOSS services - so as to release app user.
2> kill all sessions at mysql other than root.
3> apply DB patch using root user - DDL operation.

Now, we are looking for this tool so as to make ZERO downtime while applying DB patches.

Please guide.

Thanks.

Hi Guarav,

If the DDL change you perform does not affect the application job, like if you don't remove a column the app uses, pt-osc should not introduce any downtime. If your server is very busy though you may want to tune --max-load setting to minimize additional load impact.

The problem with normal ALTER is that it usually takes a long time when the table is big and during that time the table is blocked for writes.
pt-online-schema-change purpose is to eliminate that time by allowing the table to be editable during the alter process.

If you have long transactions opened before you started pt-online-schema-change, the tool will be able to finish it's job after those transactions will be commited, but it won't block them. Also no new transactions using the table in the mean time will be broken, as final rename done by pt-osc is just yet another transaction, very short one.