pt-online-schema-change could support "DATA DIRECTORY"

[Fernando Laudares Camargos]

MySQL native "ALTER TABLE" don't support the "DATA DIRECTORY" option found in "CREATE TABLE" that allows the setting of a different location for the table's tablespace file (when innodb_file_per_table is enabled) but the way pt-online-schema-change is designed (making a copy of the original table and using local triggers to keep changes accross the copied table while the process is ongoing) this could be a good and useful fit.

I've tried it this way, though as you can see, it doesn't work:

$ pt-online-schema-change --execute --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox5547.sock --alter "ENGINE=InnoDB DATA DIRECTORY '/home/fernando.laudares/mysql_tablespaces/'" D=fernando,t=joinit
No slaves found. See --recursion-method if host bm-support01 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 `fernando`.`joinit`...
Creating new table...
Created new table fernando._joinit_new OK.
Altering new table...
Altered `fernando`.`_joinit_new` OK.
2015-11-16T12:20:45 Creating triggers...
2015-11-16T12:20:45 Created triggers OK.
2015-11-16T12:20:45 Copying approximately 33554709 rows...
Copying `fernando`.`joinit`: 8% 05:28 remain
Copying `fernando`.`joinit`: 17% 04:37 remain
Copying `fernando`.`joinit`: 27% 03:53 remain

<glimpse>
$ ls -lh
total 2.7G
-rw-rw---- 1 fernando.laudares percona 65 Nov 16 12:08 db.opt
-rw-rw---- 1 fernando.laudares percona 8.5K Nov 16 12:09 joinit.frm
-rw-rw---- 1 fernando.laudares percona 2.4G Nov 16 12:15 joinit.ibd
-rw-rw---- 1 fernando.laudares percona 8.5K Nov 16 12:20 _joinit_new.frm
-rw-rw---- 1 fernando.laudares percona 372M Nov 16 12:21 _joinit_new.ibd
-rw-rw---- 1 fernando.laudares percona 996 Nov 16 12:20 joinit.TRG
-rw-rw---- 1 fernando.laudares percona 38 Nov 16 12:20 pt_osc_fernando_joinit_del.TRN
-rw-rw---- 1 fernando.laudares percona 38 Nov 16 12:20 pt_osc_fernando_joinit_ins.TRN
-rw-rw---- 1 fernando.laudares percona 38 Nov 16 12:20 pt_osc_fernando_joinit_upd.TRN
</glimpse>

Copying `fernando`.`joinit`: 36% 03:28 remain
Copying `fernando`.`joinit`: 45% 03:00 remain
Copying `fernando`.`joinit`: 54% 02:29 remain
Copying `fernando`.`joinit`: 63% 02:02 remain
Copying `fernando`.`joinit`: 71% 01:34 remain
Copying `fernando`.`joinit`: 79% 01:08 remain
Copying `fernando`.`joinit`: 88% 00:37 remain
Copying `fernando`.`joinit`: 97% 00:07 remain
2015-11-16T12:26:23 Copied rows OK.
2015-11-16T12:26:23 Swapping tables...
2015-11-16T12:26:23 Swapped original and new tables OK.
2015-11-16T12:26:23 Dropping old table...
2015-11-16T12:26:23 Dropped old table `fernando`.`_joinit_old` OK.
2015-11-16T12:26:23 Dropping triggers...
2015-11-16T12:26:23 Dropped triggers OK.
Successfully altered `fernando`.`joinit`.

$ ls -lh
total 2.4G
-rw-rw---- 1 fernando.laudares percona 65 Nov 16 12:08 db.opt
-rw-rw---- 1 fernando.laudares percona 8.5K Nov 16 12:20 joinit.frm
-rw-rw---- 1 fernando.laudares percona 2.4G Nov 16 12:26 joinit.ibd

[Valerii Kravchuk (valerii-kravchuk)]

This is easy to confirm:

[openxs@centos p5.6]$ pt-online-schema-change --execute --host=127.0.0.1 --port=3306 --user=root --alter "ENGINE=InnoDB DATA DIRECTORY '/home/openxs/tmp'" D=test,t=ti
No slaves found. See --recursion-method if host centos 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
Altering `test`.`ti`...
Creating new table...
Created new table test._ti_new OK.
Altering new table...
Altered `test`.`_ti_new` OK.
2015-11-16T19:54:22 Creating triggers...
2015-11-16T19:54:22 Created triggers OK.
2015-11-16T19:54:22 Copying approximately 1 rows...
2015-11-16T19:54:22 Copied rows OK.
2015-11-16T19:54:22 Analyzing new table...
2015-11-16T19:54:22 Swapping tables...
2015-11-16T19:54:22 Swapped original and new tables OK.
2015-11-16T19:54:22 Dropping old table...
2015-11-16T19:54:22 Dropped old table `test`.`_ti_old` OK.
2015-11-16T19:54:22 Dropping triggers...
2015-11-16T19:54:22 Dropped triggers OK.
Successfully altered `test`.`ti`.
[openxs@centos p5.6]$ ls -l /home/openxs/tmp
total 4
drwxrwx---. 2 openxs openxs 4096 Nov 16 19:50 test
[openxs@centos p5.6]$ ls -l /home/openxs/tmp/test
total 96
-rw-rw----. 1 openxs openxs 98304 Nov 16 19:50 ti2.ibd
[openxs@centos p5.6]$

There is a problem here that (unlike explicit ALTER) we do not give any warning or anything about DATA DIRECTORY just ignored:

mysql> alter table ti engine=InnoDB DATA DIRECTORY '/home/openxs/tmp';
Query OK, 0 rows affected, 1 warning (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1618
Message: <DATA DIRECTORY> option ignored
1 row in set (0.00 sec)

And, surely, parsing the ALTER for DATA DIRECTORY and creating a smater CREATE TABLE would be very helpful. Now we have this from PTDEBUG=1 output):

...
Starting a dry run. `test`.`ti` will not be altered. Specify --execute instead of --dry-run to alter the table.
# CleanupTask:6372 2208 Created cleanup task CODE(0x15525a0)
# pt_online_schema_change:9894 2208 Renamed columns (old => new): $VAR1 = {};
#
# Percona::XtraDB::Cluster:7828 2208 centos SHOW VARIABLES LIKE 'wsrep_on'
# Percona::XtraDB::Cluster:7830 2208 $VAR1 = undef;
#
# Daemon:2513 2208 Starting daemon
# Daemon:2583 2208 Daemon running
# TableParser:3192 2208 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
# TableParser:3197 2208 DBI::db=HASH(0x1551820) USE `test`
# TableParser:3201 2208 SHOW CREATE TABLE `test`.`ti`
# TableParser:3211 2208 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
Creating new table...
# pt_online_schema_change:10033 2208 CREATE TABLE `test`.`_ti_new` (
# `id` int(11) NOT NULL DEFAULT '0',
# PRIMARY KEY (`id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Created new table test._ti_new OK.
Altering new table...
# pt_online_schema_change:8963 2208 ALTER TABLE `test`.`_ti_new` ENGINE=InnoD...

[Daniel Nichter (daniel-nichter)]

How about adding --create-table-options to set CREATE TABLE options for the new table? With this you could specify DATA DIRECTORY and whatever else.

Should --create-table-options replace all, or append to, or patch the existing table options? Replace and append are simple. Patching example: original table has COMPRESSION=zlib and --create-table-options="COMPRESSION='lz4'" would do something like s/COMPRESSION=zlib/COMPRESSION=lz4/.

[Carlos Salguero (carlos-salguero)]

Duplicate of https://bugs.launchpad.net/percona-toolkit/+bug/1638293

[Shahriyar Rzayev (rzayev-sehriyar)]

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