Shulou(Shulou.com)06/01 Report--

Before we explain the internal processing flow of pt-osc, let's take a look at the information of the child table after the rename exchange table through the following example.

-- create a parent table CREATE TABLE parent (id int (11) NOT NULL auto_increment,parent_id int,PRIMARY KEY (id), KEY IX_parent_id (parent_id)) ENGINE=InnoDB Create a child table with the foreign key child_id, and associate it with the parent table parent_id (id int (11) NOT NULL auto_increment,child_id int (11) default NULL,PRIMARY KEY (id), KEY IX_child_id (child_id), FOREIGN KEY (child_id) REFERENCES parent (parent_id)) ENGINE=InnoDB;-- to rename the parent table rename table parent to parent_1

At this point, the child table automatically sticks to the new parent table name, as shown below:

Show create table child\ GCREATE TABLE child (id int (11) NOT NULL AUTO_INCREMENT, child_id int (11) DEFAULT NULL, PRIMARY KEY (id), KEYI IX_child_id (child_id), CONSTRAINT child_ibfk_1 FOREIGN KEY (child_id) REFERENCES parent_1 (parent_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Now we are going to add a field name varchar to the parent table

Execute with the pt-osc tool, usually the internal execution process is:

1) create a temporary table _ parent_new

2) add the name field to the temporary table _ parent_new

3) define triggers on the original table parent so that changes made to the data on the original table will also be applied to the temporary table _ parent_new

4) copy the data from the original table parent to _ parent_new.

5) Exchange names rename table parent to _ parent_old, _ parent_new to parent

6) delete the original table drop table _ parent_old

7) delete, add, delete and modify three triggers

Then the most dangerous thing is that after rename exchanges names, the foreign key of the child table will be directed to _ parent_old and will not become parent, which will lead to data inconsistency.

Solid, pt-osc added-- alter-foreign-keys-method parameter, the default is drop_swap, its execution process is a little different from just now.

The first four steps are the same, and the fifth step begins to become

5) set FOREIGN_KEY_CHECKS=OFF; # turn off foreign key checking

6) Exchange names rename table parent to _ parent_old

7) drop table _ parent_old

8) Exchange names rename table _ parent_new to _ parent_old, _ parent_old to parent

9) delete, add, delete and modify three triggers

10) set FOREIGN_KEY_CHECKS=ON

Step 7 if the table is large and the deletion is slow (step 8 will not be performed), the business will be affected, which is also dangerous.

If changed to rebuild_constraints, the execution process is as follows:

Exchange names

Rename table parent to _ parent_old, _ parent_new to parent

(this step remains the same as before.)

1) delete the foreign key of the child table and re-associate the parent table parent

ALTER TABLE child DROP FOREIGN KEY child_id, ADD CONSTRAINT child_ibfk_1 FOREIGN KEY (child_id) REFERENCES parent (parent_id)

Note: this step will use the ALGORITHM=INPLACE algorithm, will not lock the table, and support concurrent DML.

2) delete the original table drop table _ parent_old

3) delete, add, delete and modify three triggers

Set to auto, and use rebuild_constraints; if the number of rows in the child table is small, otherwise convert to drop_swap.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.