Plugin hook before_swap_tables executed after swap when foreign keys exist

General

Escalation

General

Escalation

Description

When using a plugin which uses the before_swap_tables-event, this event is triggered after swapping the tables when a tables has foreign keys and the parameter --alter-foreign-keys-method="drop swap" is been used.

Workaround: use the after_copy_rows`-event

Environment

None

Activity

Show:

Aaditya Dubey June 7, 2024 at 11:17 AM

Hi @Sander Lootens

Thank you for the report.
Verified as described.

$:~/percona-toolkit-3.5.7/bin$ ./pt-online-schema-change --version
pt-online-schema-change 3.5.7

mysql [localhost:8036] {msandbox} ((none)) > select version();
+-----------+
| version() |
+-----------+
| 8.0.36-28 |
+-----------+
1 row in set (0.00 sec)

Create testing tables:

mysql [localhost:8036] {msandbox} (test) > CREATE TABLE parent (
    ->     id INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

mysql [localhost:8036] {msandbox} (test) > CREATE TABLE child (
    ->     id INT,
    ->     parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     FOREIGN KEY (parent_id)
    ->         REFERENCES parent(id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql [localhost:8036] {msandbox} (test) > INSERT INTO parent (id) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8036] {msandbox} (test) > SELECT * FROM parent;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql [localhost:8036] {msandbox} (test) > INSERT INTO child (id,parent_id) VALUES (1,1);
Query OK, 1 row affected (0.02 sec)

mysql [localhost:8036] {msandbox} (test) > select * from child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
+------+-----------+
1 row in set (0.00 sec)

CREATE TABLE `joinit` (
 `i` int(11) NOT NULL AUTO_INCREMENT,
 `s` varchar(64) DEFAULT NULL,
 `t` time NOT NULL,
 `g` int(11) NOT NULL,
 PRIMARY KEY (`i`)
);

INSERT INTO joinit VALUES (NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )));
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 


Clone the plugin:

$ git clone https://github.com/wasylni/pt_online_schema_change_pause
Cloning into 'pt_online_schema_change_pause'...
remote: Enumerating objects: 33, done.
remote: Total 33 (delta 0), reused 0 (delta 0), pack-reused 33
Unpacking objects: 100% (33/33), 11.36 KiB | 684.00 KiB/s, done.


Run following Command on foreign key table:


$:~/percona-toolkit-3.5.7/bin$ ./pt-online-schema-change --user='msandbox' --password='msandbox' --socket=/tmp/mysql_sandbox8036.sock --execute --plugin $HOME/pt_online_schema_change_pause/pt_online_schema_change_plugin.pl --chunk-time=1 --nodrop-old-table --alter "add column foo int(11) default null, add column foo2 int(11) default null, add column foo3 int(11) default null, add column foo4 int(11) default null" D=test,t=parent --alter-foreign-keys-method "drop_swap"
Created plugin from $HOME/pt_online_schema_change_pause/pt_online_schema_change_plugin.pl.
No slaves found.  See --recursion-method if host localhost has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

# A software update is available:
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `test`.`child` (approx. 1 rows)
Will use the drop_swap method to update foreign keys.
Altering `test`.`parent`...
Creating new table...
Created new table test._parent_new OK.
Altering new table...
Altered `test`.`_parent_new` OK.
2024-06-07T10:53:50 Creating triggers...
2024-06-07T10:53:50 Created triggers OK.
2024-06-07T10:53:50 Copying approximately 1 rows...
2024-06-07T10:53:50 Copied rows OK.
2024-06-07T10:53:50 Drop-swapping tables...
2024-06-07T10:53:50 Analyzing new table...
2024-06-07T10:53:50 Dropped and swapped tables OK.
======================================================================================
SCRIPT WILL ONLY SWAP TABLE IF FILE IS NOT PRESENT ./pt_schema_change_wait_.txt
======================================================================================
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File is ./pt_schema_change_wait_.txt NOT present will swap tables now!
Not dropping old table because --no-drop-old-table was specified.
2024-06-07T11:01:50 Dropping triggers...
2024-06-07T11:01:50 Dropped triggers OK.
Successfully altered `test`.`parent`.


Run following command on without foreign key table:

adi@localhost:~/percona-toolkit-3.5.7/bin$ ./pt-online-schema-change --user='msandbox' --password='msandbox' --socket=/tmp/mysql_sandbox8036.sock --execute --plugin $HOME/pt_online_schema_change_pause/pt_online_schema_change_plugin.pl --chunk-time=1 --nodrop-old-table --alter "add column foo int(11) default null, add column foo2 int(11) default null, add column foo3 int(11) default null, add column foo4 int(11) default null" D=test,t=joinit --alter-foreign-keys-method "drop_swap"
Created plugin from $HOME/pt_online_schema_change_pause/pt_online_schema_change_plugin.pl.
No slaves found.  See --recursion-method if host localhost has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
No foreign keys reference `test`.`joinit`; ignoring --alter-foreign-keys-method.
Altering `test`.`joinit`...
Creating new table...
Created new table test._joinit_new OK.
Altering new table...
Altered `test`.`_joinit_new` OK.
2024-06-07T11:05:46 Creating triggers...
2024-06-07T11:05:46 Created triggers OK.
2024-06-07T11:05:46 Copying approximately 4 rows...
2024-06-07T11:05:46 Copied rows OK.
======================================================================================
SCRIPT WILL ONLY SWAP TABLE IF FILE IS NOT PRESENT ./pt_schema_change_wait_.txt
======================================================================================
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
File ./pt_schema_change_wait_.txt is present will delay table swap by 1 minute to proceed silmply delete this file
^C# Exiting on SIGINT.
Not dropping triggers because the tool was interrupted.  To drop the triggers, execute:
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_joinit_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_joinit_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_joinit_ins`
Not dropping the new table `test`.`_joinit_new` because the tool was interrupted.  To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_joinit_new`;
`test`.`joinit` was not altered.

You will notice that the `before_swap_tables` event is triggered after swapping the tables when a table has foreign keys and the parameter alter-foreign-keys-method="drop_swap" is used.

Details

Assignee

Unassigned

Reporter

Sander Lootens

Priority

Medium

Needs QA

Yes

Smart Checklist

Created May 19, 2024 at 12:43 PM

Updated June 7, 2024 at 11:17 AM

Configure