pt-online-schema-change should imply --no-drop-new-table when --no-swap-tables and --no-drop-triggers used

[Jericho Rivera]

Quoting the customer:

most dependent options in pt-osc resolve well. i.e. --no-drop-triggers implies --no-drop-old-table.
however, there is one situation - when using --no-swap-tables and --no-drop-triggers, one would assume --no-drop-new-table is implied, but it actually isn't, and a system will fail as the triggers will point to nothing.

Workaround per my tests is to explicitly use --no-drop-new-table option together with the other two options.

Not sure if this just needs an update in the documentation to remind the use of --no-drop-new-table for these cases or necessitates adding this feature.

pt-online-schema-change --user=root --password=msandbox --host=127.0.0.1 --port 5633 --no-swap-tables --no-drop-triggers --alter "engine=innodb" --execute D=test,t=sbtest1
No slaves found. See --recursion-method if host percona has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

# A software update is available:
# * The current version for Percona::Toolkit is 2.2.20.

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 `test`.`sbtest1`...
Creating new table...
Created new table test._sbtest1_new OK.
Altering new table...
Altered `test`.`_sbtest1_new` OK.
2017-01-21T13:14:10 Creating triggers...
2017-01-21T13:14:10 Created triggers OK.
2017-01-21T13:14:10 Copying approximately 1000 rows...
2017-01-21T13:14:10 Copied rows OK.
Not dropping old table because --no-drop-triggers was specified.
Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute:
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_sbtest1_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_sbtest1_upd`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_sbtest1_ins`;
2017-01-21T13:14:10 Dropping new table...
2017-01-21T13:14:10 Dropped new table OK.
Successfully altered `test`.`sbtest1`

mysql [localhost] {msandbox} (test) > show triggers;
+-------------------------+--------+---------+-----------------------------------------------------------------------------------------------------------+--------+---------+-------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-------------------------+--------+---------+-----------------------------------------------------------------------------------------------------------+--------+---------+-------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| pt_osc_test_sbtest1_ins | INSERT | sbtest1 | REPLACE INTO `test`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) | AFTER | NULL | NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_AUTO_CREATE_USER | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |
| pt_osc_test_sbtest1_upd | UPDATE | sbtest1 | REPLACE INTO `test`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) | AFTER | NULL | NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_AUTO_CREATE_USER | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |
| pt_osc_test_sbtest1_del | DELETE | sbtest1 | DELETE IGNORE FROM `test`.`_sbtest1_new` WHERE `test`.`_sbtest1_new`.`id` <=> OLD.`id` | AFTER | NULL | NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_AUTO_CREATE_USER | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |
+-------------------------+--------+---------+-----------------------------------------------------------------------------------------------------------+--------+---------+-------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > exit
Bye

sysbench --test=/usr/local/share/sysbench/oltp.lua --mysql-user=root --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=5633 --mysql-db=test --oltp-test-mode=complex --oltp-tables-count=1 --oltp-read-only=off --oltp-reconnect=on --oltp-table-size=1000 --max-requests=1 --num-threads=2 --report-interval=1 run
sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 2
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

ALERT: mysql_drv_query() returned error 1146 (Table 'test._sbtest1_new' doesn't exist) for query 'UPDATE sbtest1 SET k=k+1 WHERE id=497'
FATAL: failed to execute function `event': 3

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-726