change pt-online-schema-change to warn on empty alter table

[Fernando Ipar]

This is a feature request and not a bug report.

I had pt-osc abort due to load on a server earlier this week, so I used --critical-load to work around that.
However, I had a command line invocation like this one:

pt-online-schema-change \
 DSN \
 --execute \
 --no-drop-old-table \
 --alter 'SQL'

And after the change I ran this:

pt-online-schema-change \
 DSN \
 --execute \
 --no-drop-old-table \
 --critical-load=Threads_running=200
 --alter 'SQL'

Note that I forgot to add '\' before the newline in the --critical--load line, so the tool ran an empty alter, rebuilding the table.
I think it would be friendlier if the tool warned on that, perhaps requiring some other arg like --empty-alter if --alter is not present.

[Fernando Ipar (fipar)]

I think the problem is that --alter seems to take the default of a noop alter table, and this is not explicit in the docs. Further, this example:

   Change sakila.actor to InnoDB, effectively performing OPTIMIZE TABLE in a non-blocking fashion because it is already an InnoDB table:

   pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor

Makes me think that there is no implicit value for --alter, if I have to include that for a noop/optimize table one.

The minimal test case I found is by using --execute and the DSN alone, and checking the timestamp on the frm file I can confirm it is altered:

telecaster:tmp fernandoipar$ ls -lah /usr/local/var/mysql/test/pt_osc_test.frm
-rw-rw---- 1 fernandoipar admin 8.4K Mar 9 13:06 /usr/local/var/mysql/test/pt_osc_test.frm

telecaster:tmp fernandoipar$ ./pt-online-schema-change D=test,t=pt_osc_test
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Exiting without altering `test`.`pt_osc_test` because neither --dry-run nor --execute was specified. Please read the tool's documentation carefully before using this tool.
telecaster:tmp fernandoipar$ ./pt-online-schema-change --execute D=test,t=pt_osc_test
No slaves found. See --recursion-method if host telecaster has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`pt_osc_test`...
Creating new table...
Created new table test._pt_osc_test_new OK.
2015-03-09T13:11:14 Creating triggers...
2015-03-09T13:11:14 Created triggers OK.
2015-03-09T13:11:14 Copying approximately 1 rows...
2015-03-09T13:11:14 Copied rows OK.
2015-03-09T13:11:14 Swapping tables...
2015-03-09T13:11:14 Swapped original and new tables OK.
2015-03-09T13:11:14 Dropping old table...
2015-03-09T13:11:14 Dropped old table `test`.`_pt_osc_test_old` OK.
2015-03-09T13:11:14 Dropping triggers...
2015-03-09T13:11:14 Dropped triggers OK.
Successfully altered `test`.`pt_osc_test`.

telecaster:tmp fernandoipar$ ls -lah /usr/local/var/mysql/test/pt_osc_test.frm
-rw-rw---- 1 fernandoipar admin 8.4K Mar 9 13:11 /usr/local/var/mysql/test/pt_osc_test.frm

So I think my feature request is to either:
- make the tool print a warning if no --alter is provided, printing the actual ALTER TABLE command that will be run on the new table, or
- improve the documentation to make it explicit that --alter has a defaut value, and adjust the 'optimize table' example accordingly.

[Nilnandan Joshi (nilnandan-joshi)]

Verified. Agree with feature request.

[root@localhost ~]# ls -al /var/lib/mysql/nil/nil*
-rw-rw----. 1 mysql mysql 8586 Mar 10 11:23 /var/lib/mysql/nil/nil_tab.frm
-rw-rw----. 1 mysql mysql 98304 Mar 10 11:23 /var/lib/mysql/nil/nil_tab.ibd
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# pt-online-schema-change D=nil,t=nil_tab --execute
No slaves found. See --recursion-method if host localhost.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `nil`.`nil_tab`...
Creating new table...
Created new table nil._nil_tab_new OK.
2015-03-10T11:25:12 Creating triggers...
2015-03-10T11:25:12 Created triggers OK.
2015-03-10T11:25:12 Copying approximately 4 rows...
2015-03-10T11:25:12 Copied rows OK.
2015-03-10T11:25:12 Swapping tables...
2015-03-10T11:25:12 Swapped original and new tables OK.
2015-03-10T11:25:12 Dropping old table...
2015-03-10T11:25:12 Dropped old table `nil`.`_nil_tab_old` OK.
2015-03-10T11:25:12 Dropping triggers...
2015-03-10T11:25:12 Dropped triggers OK.
Successfully altered `nil`.`nil_tab`.
[root@localhost ~]#
[root@localhost ~]# ls -al /var/lib/mysql/nil/nil*
-rw-rw----. 1 mysql mysql 8586 Mar 10 11:25 /var/lib/mysql/nil/nil_tab.frm
-rw-rw----. 1 mysql mysql 98304 Mar 10 11:25 /var/lib/mysql/nil/nil_tab.ibd
[root@localhost ~]#
[root@localhost ~]# pt-online-schema-change --version
pt-online-schema-change 2.2.13
[root@localhost ~]#

[Shahriyar Rzayev (rzayev-sehriyar)]

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