GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Need clarifications regarding pt-online-schema-change.

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need clarifications regarding pt-online-schema-change.

    Hi,
    1. pt-osc doesn't allows us to create triggers on the table to be altered, since it creates its own triggers. But we have some use cases where we need to have triggers on the table to be altered at the time of alter operation. Is there any option to append our statement list to the triggers that pt-osc creates ?
    2. Any alter table in the system which has FK in the table to be altered or its child tables(if rebuild_constraints used), will finally create a table where the FK name in the altered table and its child tables will be changed to _$OLD_FK_NAME. By naming convention max allowed limit is 64 characters which can get breached. Is there a way to avoid this ?
    3. Because of the above change, the FK name in the db and the FK name in our system will differ and lead to inconsistency. Whether can we handle this using any option in pt-osc ?
    4. Is there any options in the pt-osc tool to avoid the following issues which is due to the naming convention limit of mysql:
      • Failure of new table creation, if the original table name is 60 chars or more. Since new table ​creation appends "_" and "_new" to the original table name.
      • If --nodrop-old-table option used, after a period of time new table name will reach more than 64 chars. Since to create unique new tables tool will keep on appending leading underscore each time when a migration is performed on the table.
    5. rebuild_constraint method makes child tables to be locked one by one, since its direct alter for dropping and adding the FKs. Will tool can perform this operation without locking the tables ?
    6. Modification of primary key needs us to add index in the old pk column, if this old pk column is refered in some child tables. If we miss to add index for the old pk column in the alter primary key query, the rebuild_constraints operation fails and child tables continue to refer old(Wrong) tables. Can pt-osc tool internally handle this kind of use case ? After the alter operation and before rebuild_constraints operation gets completed, the deletes that occur in parent table would not delete its corresponding entries in child table. This is because child tables at that point of time will be refering to old (wrong) table. After that when rebuild_constraints operation tries to add FK to the new (correct) table, it will result in error because of stale entries in the child table. Here the delete and alter query order is getting reversed in mysql binlog and hence replication fails. Using any option whether tool can create triggers to avoid this problem ?
Working...
X