[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=InnoDB DATA DIRECTORY '/home/openxs/tmp'
Altered `test`.`_ti_new` OK.
...
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 foreign_ keys, 10, 1 tmp/test
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_
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/
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):
... schema_ change: 9894 2208 Renamed columns (old => new): $VAR1 = {}; :XtraDB: :Cluster: 7828 2208 centos SHOW VARIABLES LIKE 'wsrep_on' :XtraDB: :Cluster: 7830 2208 $VAR1 = undef; SHOW_CREATE, @@SQL_QUOTE_ SHOW_CREATE := 1 */ HASH(0x1551820) USE `test` SHOW_CREATE := @OLD_QUOTE */ schema_ change: 10033 2208 CREATE TABLE `test`.`_ti_new` ( schema_ change: 8963 2208 ALTER TABLE `test`.`_ti_new` ENGINE=InnoDB DATA DIRECTORY '/home/openxs/tmp'
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_
#
# Percona:
# Percona:
#
# 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_
# TableParser:3197 2208 DBI::db=
# TableParser:3201 2208 SHOW CREATE TABLE `test`.`ti`
# TableParser:3211 2208 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_
Creating new table...
# pt_online_
# `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_
Altered `test`.`_ti_new` OK.
...