In this post, We will learn how to handle online schema change if the table has triggers.
In PXC, an alter can be made directly ( TOI ) on tables with less than a 1G ( by default) , but on a 20GB or 200GB table we need some downtime to do ( RSU ).
Pt-osc is a good choice for Percona Cluster/Galera. By default percona toolkit’s pt-online-schema-change will create After “insert / update / delete” triggers for maintaining the sync between the shadow and the original table.
pt-online-schema-change process flow:
Check out the complete slides for effective MySQL administration here
If the tables has triggers already then pt-osc wont work well in those cases. It was an limitation with online schema changes.
Still MySQL 5.6, We cannot create multiple triggers for the same event and type.
From Documentation:
There cannot be multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.
On this case, We will have to drop the triggers before starting online schema change. And re-create the triggers after completion of online schema change. In a production environment it’s a complex operation to perform and requires a downtime.
On MySQL 5.6.32:
[root@mysql-5.6.32 ~]# pt-online-schema-change --version
pt-online-schema-change 3.0.6
[root@mysql-5.6.32 ~]# pt-online-schema-change D=mydbops,t=employees,h=localhost \
--user=root --alter "drop column test,add column test text" \
--no-version-check --execute
The table `mydbops`.`employees` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.
From MySQL 5.7.2, A table can hold multiple triggers.
From Documentation:
It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table.
Complete list of new features of MySQL 5.7 here. This relaxed the complexity of implementation of pt-osc support for tables with triggers.
pt-online-schema-change – v3.0.4, released on 2017-08-02 came with an option –preserve-triggers. Which added a 5.7 only feature, To allow pt-osc to handle OSC operation even the table has triggers.
Even Gh-ost won’t work for PXC without locking the table in MySQL 5.7. Issues
On MySQL 5.7.19:
[root@mysql-5.7.19 ~]# pt-online-schema-change --version
pt-online-schema-change 3.0.6
[root@mysql-5.7.19 ~]# pt-online-schema-change D=mydbops,t=employees,h=localhost \
--user=root --alter "drop column test,add column test text" \
--no-version-check --preserve-triggers --execute
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
Altering `mydbops`.`employees`...
Creating new table...
Created new table mydbops._employees_new OK.
Altering new table...
Altered `mydbops`.`_employees_new` OK.
2018-03-02T07:27:35 Creating triggers...
2018-03-02T07:27:35 Created triggers OK.
2018-03-02T07:27:35 Copying approximately 10777 rows...
2018-03-02T07:27:35 Copied rows OK.
2018-03-02T07:27:35 Adding original triggers to new table.
2018-03-02T07:27:35 Analyzing new table...
2018-03-02T07:27:35 Swapping tables...
2018-03-02T07:27:36 Swapped original and new tables OK.
2018-03-02T07:27:36 Dropping old table...
2018-03-02T07:27:36 Dropped old table `mydbops`.`_employees_old` OK.
2018-03-02T07:27:36 Dropping triggers...
2018-03-02T07:27:36 Dropped triggers OK.
Successfully altered `mydbops`.`employees`.
–preserve-triggers If this option is enabled, pt-online-schema-change will create all the existing triggers to the new table (mydbops._employees_new) after copying rows from the original table (mydbops.employees).
Explained the output with PTDEBUG=1 enabled for better understanding.
1. pt-osc created similar table and applied modifications on it.
# Cxn:3953 2845 DBI::db=HASH(0x2db1260) Connected dbh to mysql-5.7.19 h=localhost
# TableParser:3265 2845 SHOW CREATE TABLE `mydbops`.`employees`
Creating new table...
# pt_online_schema_change:10392 2845 CREATE TABLE `mydbops`.`_employees_new` (
# `employeeNumber` int(11) NOT NULL,
# `lastName` varchar(50) DEFAULT NULL,
# `firstName` varchar(50) DEFAULT NULL,
# `extension` varchar(10) DEFAULT NULL,
# `email` varchar(100) DEFAULT NULL,
# `officeCode` varchar(10) DEFAULT NULL,
# `reportsTo` int(11) DEFAULT NULL,
# `jobTitle` varchar(50) DEFAULT NULL,
# `test` text,
# PRIMARY KEY (`employeeNumber`),
# KEY `reportsTo` (`reportsTo`),
# KEY `officeCode` (`officeCode`)
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Created new table mydbops._employees_new OK.
Altering new table...
# pt_online_schema_change:9192 2845 ALTER TABLE `mydbops`.`_employees_new` drop column test,add column test text
Altered `mydbops`.`_employees_new` OK.
2. pt-osc created After [insert / update / delete] triggers to sync the upcoming data between the source table and new table.
# pt_online_schema_change:11058 2845 CREATE TRIGGER
`pt_osc_mydbops_employees_del` AFTER DELETE ON `mydbops`.`employees`
FOR EACH ROW DELETE IGNORE FROM `mydbops`.`_employees_new`
WHERE `mydbops`.`_employees_new`.`employeenumber` <=>
OLD.`employeenumber`
# pt_online_schema_change:11058 2845 CREATE TRIGGER
`pt_osc_mydbops_employees_upd` AFTER UPDATE ON `mydbops`.`employees`
FOR EACH ROW BEGIN DELETE IGNORE FROM `mydbops`.`_employees_new`
WHERE !(OLD.`employeenumber` <=> NEW.`employeenumber`) AND
`mydbops`.`_employees_new`.`employeenumber` <=>
OLD.`employeenumber`;REPLACE INTO `mydbops`.`_employees_new`
(`employeenumber`, `lastname`, `firstname`, `extension`, `email`,
`officecode`, `reportsto`, `jobtitle`, `test`) VALUES
(NEW.`employeenumber`, NEW.`lastname`, NEW.`firstname`,
NEW.`extension`, NEW.`email`, NEW.`officecode`, NEW.`reportsto`,
NEW.`jobtitle`, NEW.`test`);END
# pt_online_schema_change:11058 2845 CREATE TRIGGER
`pt_osc_mydbops_employees_ins` AFTER INSERT ON `mydbops`.`employees`
FOR EACH ROW REPLACE INTO `mydbops`.`_employees_new`
(`employeenumber`, `lastname`, `firstname`, `extension`, `email`,
`officecode`, `reportsto`, `jobtitle`, `test`) VALUES
(NEW.`employeenumber`, NEW.`lastname`, NEW.`firstname`,
NEW.`extension`, NEW.`email`, NEW.`officecode`, NEW.`reportsto`,
NEW.`jobtitle`, NEW.`test`)
2018-03-02T05:56:46 Created triggers OK.
3. pt-osc copied the existing records.
# pt_online_schema_change:11332 2845 INSERT LOW_PRIORITY IGNORE INTO
`mydbops`.`_employees_new` (`employeenumber`, `lastname`, `firstname`,
`extension`, `email`, `officecode`, `reportsto`, `jobtitle`, `test`)
SELECT `employeenumber`, `lastname`, `firstname`, `extension`,
`email`, `officecode`, `reportsto`, `jobtitle`, `test` FROM
`mydbops`.`employees` FORCE INDEX(`PRIMARY`) WHERE
((`employeenumber` >= ?)) AND ((`employeenumber` <= ?))
LOCK IN SHARE MODE /*pt-online-schema-change 2845 copy nibble*/
lower boundary: 0 upper boundary: 2335
4. After existing row copied, pt-osc created the trigger present on the source table to the newly created table (mydbops._employees_new).
# pt_online_schema_change:9795 2845 CREATE DEFINER=`root`@`%` TRIGGER `mydbops`.`mydbops_employee_update` BEFORE UPDATE ON _employees_new
# FOR EACH ROW
# BEGIN
# INSERT INTO employees_audit
# SET action = 'update',
# employeeNumber = OLD.employeeNumber,
# lastname = OLD.lastname,
# changedat = NOW();
# END
5. Swapping the table and dropping the triggers.
2018-03-02T05:56:47 Analyzing new table...
# pt_online_schema_change:10465 2836 ANALYZE TABLE `mydbops`.`_employees_new` /* pt-online-schema-change */
2018-03-02T05:56:47 Swapping tables...
# pt_online_schema_change:10503 2836 RENAME TABLE `mydbops`.`employees` TO `mydbops`.`_employees_old`, `mydbops`.`_employees_new` TO `mydbops`.`employees`
2018-03-02T05:56:47 Swapped original and new tables OK.
2018-03-02T05:56:47 Dropping old table...
# pt_online_schema_change:9937 2845 DROP TABLE IF EXISTS `mydbops`.`_employees_old`
2018-03-02T05:56:47 Dropped old table `mydbops`.`_employees_old` OK.
2018-03-02T05:56:47 Dropping triggers...
# pt_online_schema_change:11182 2845 DROP TRIGGER IF EXISTS `mydbops`.`pt_osc_mydbops_employees_del`
# pt_online_schema_change:11182 2845 DROP TRIGGER IF EXISTS `mydbops`.`pt_osc_mydbops_employees_upd`
# pt_online_schema_change:11182 2845 DROP TRIGGER IF EXISTS `mydbops`.`pt_osc_mydbops_employees_ins`
2018-03-02T05:56:47 Dropped triggers OK.
Successfully altered `mydbops`.`employees`.
I hope this gives you a better idea about –preserve-triggers.
Key Takeaways:
Upto MySQL 5.6, Only way to alter the tables using pt-osc is to drop the existing triggers and create after the alter done.
From MySQL 5.7, We can use –preserve-triggers option of pt-osc for seamless schema changes though we have triggers present on our table.
It gives us one more reason to recommend MySQL 5.7 upgrade. I also feel pt-osc can be provided with support for the tables with before triggers at least for MySQL versions until 5.6.
.png)
.png)
.svg)
