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

pt-online-schema-change concepts

giomandagiomanda ContributorInactive User Role Beginner
Hi

I am reading the doc for the relevant tool in order to use it, well, actually I have performed a test with great success but when i came across to tables with foreign keys i got troubled . So i have a few questions on the subject which i don’t understand:

1)
"When the tool renames the original table to let the new one take its place, the foreign keys “follow” the renamed table, and must be changed to reference the new table instead."

I dont understand this. I mean, why does the child table "follows" the old table? The way I think of it , the child table will look for the foreign keys according to the table name (right?), and since the new table (the altered) was renamed and has the proper name, how come the child table tries to reference the foreign keys to the old one. Also, as it is stated on the manual, the old table is dropped which means the child table tries to reference a table that doesn’t exist?

What change on the foreign keys will be made on the child table? I mean, the table name and the rows are the same ...

I am not an expert of mysql so excuse me in advance if i ask something obvious.

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    Basically pt-osc does the following steps:
    # Step 1: Create the new table.
    # Step 2: Alter the new, empty table. This should be very quick,
    # Step 3: Create the triggers to capture changes on the original table and
    # Step 4: Copy rows.
    # Step 5: Rename tables: orig -> old, new -> orig
    # Step 6: Update foreign key constraints if there are child tables.
    # Step 7: Drop the old table.

    So what #7 does is it drops the foreign keys referencing the old table and creates new ones for the new table.

    https://dev.mysql.com/doc/refman/5.0/en/rename-table.html
    Note that if the table being renamed is a target for any InnoDB foreign keys, the keys will continue to point to the swapped-out table, not its replacement, and referential integrity for newly added rows may be different than what you might be expecting.. A fully formed table swap needs to drop all referencing foreign keys from all other tables and add new ones to the swapped-in table, which means index/table rebuilds over the entire database schema. In practice, this means the atomicity only works for databases where foreign keys aren't used.
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.