pt-online-schema-change - table fails to change directory

Hi,

The following details are from Jira PT-1512, just wondered if anyone else had seen this issue, or might spot if I’m doing this wrong.

Thanks,

Mike

Details

We are testing the use of pt-online-schema-change to move a 15 GB table in the near future.

I created a copy of a small table on one of our dev boxes, as follows

[root@localhost mysql]# ls -ltrh camspatial1/curation_automated_group_routes*
rw-rr-. 1 mysql mysql 8.5K Mar 8 15:08 camspatial1/curation_automated_group_routes.frm
rw-rr-. 1 mysql mysql 6.3M Mar 8 15:17 camspatial1/curation_automated_group_routes.MYI
rw-rr-. 1 mysql mysql 5.1M Mar 8 15:17 camspatial1/curation_automated_group_routes.MYD

CREATE TABLE ms_curation_automated_group_routes LIKE curation_automated_group_routes;
ALTER TABLE ms_curation_automated_group_routes DISABLE KEYS;
INSERT INTO ms_curation_automated_group_routes SELECT * FROM curation_automated_group_routes;
ALTER TABLE ms_curation_automated_group_routes ENABLE KEYS;

I then ran the following command, this failed, I them updated the toolkit to 3.0.8-1, running on 5.7.21-20, and it failed again.

[root@localhost mysql]# pt-online-schema-change --data-dir="/viewranger/data/mysql_old" --execute D=camspatial1,t=ms_curation_automated_group_routes -u root -p;
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:
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 camspatial1.ms_curation_automated_group_routes
Creating new table…
Created new table camspatial1._ms_curation_automated_group_routes_new OK.
2018-03-23T16:57:15 Creating triggers…
2018-03-23T16:57:16 Created triggers OK.
2018-03-23T16:57:16 Copying approximately 133481 rows…
2018-03-23T16:57:17 Copied rows OK.
2018-03-23T16:57:17 Analyzing new table…
2018-03-23T16:57:17 Swapping tables…
2018-03-23T16:57:17 Swapped original and new tables OK.
2018-03-23T16:57:17 Dropping old table…
2018-03-23T16:57:17 Dropped old table camspatial1._ms_curation_automated_group_routes_old OK.
2018-03-23T16:57:17 Dropping triggers…
2018-03-23T16:57:17 Dropped triggers OK.
Successfully altered camspatial1.ms_curation_automated_group_routes.
[root@localhost mysql]# pwd
/viewranger/data/mysql
[root@localhost mysql]# ls -ltrh camspatial1/ms*
rw-r----. 1 mysql mysql 8.5K Mar 23 16:57 camspatial1/ms_curation_automated_group_routes.frm
rw-r----. 1 mysql mysql 2.6M Mar 23 16:57 camspatial1/ms_curation_automated_group_routes.MYD
rw-r----. 1 mysql mysql 2.8M Mar 23 16:57 camspatial1/ms_curation_automated_group_routes.MYI

Tables are still in the old directory.

Am I, as they say, doing it wrong ?

Mike

Hi mikes;

Looks like you’re working with a MyISAM table, so the problem is most likely with symlinks.

https://dev.mysql.com/doc/refman/5.7/en/create-table.html

These options work only when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See Section 8.12.3.2, “Using Symbolic Links for MyISAM Tables on Unix”, for more complete information.

https://dev.mysql.com/doc/refman/5.7/en/symbolic-links-to-tables.html

Hi,

Those files remain in the original directory.
.frm file holds the table definition
.MYD should be converted to a link pointing to the new data directory
.MYI indexes.

Example:
I’ve created this table:

mysql> create database test;
Query OK, 1 row affected (0,00 sec)

mysql> create table test.t1 (id int auto_increment primary key, name varchar(30)) engine=MyISAM;
Query OK, 0 rows affected (0,00 sec)

Then I put 1000000 rows using MySQL random data loader as follow:

mysql_random_data_load --host 127.0.0.1 --port 12345 --user msandbox --password msandbox test t1 1000000
INFO[2018-04-05T14:30:22-03:00] Starting
17s [====================================================================] 100%
INFO[2018-04-05T14:30:40-03:00] 1000000 rows inserted

The original data dir has these files:

.../data/test $ ls
total 47M
-rw-r----- 1 karl karl 65 abr 5 14:30 db.opt
-rw-r----- 1 karl karl 8,4K abr 5 14:30 t1.frm
-rw-r----- 1 karl karl 37M abr 5 14:30 t1.MYD
-rw-r----- 1 karl karl 9,9M abr 5 14:30 t1.MYI

Then I ran:

bin/pt-online-schema-change h=127.0.0.1,P=12345,u=msandbox,p=msandbox,D=test,t=t1 --data-dir=/tmp/newdatadir --execute

The original directory now has this:

.../data/test $ ls
total 9,9M
-rw-r----- 1 karl karl 65 abr 5 14:30 db.opt
-rw-r----- 1 karl karl 8,4K abr 5 14:33 t1.frm
lrwxrwxrwx 1 karl karl 22 abr 5 14:33 t1.MYD -> /tmp/newdatadir/t1.MYD
-rw-r----- 1 karl karl 9,9M abr 5 14:33 t1.MYI

As you can see, the .MYD is a symbolic link to the new location and a ls into the new dir shows the 37 Mb data file:

pwd
/tmp/newdatadir
/tmp/newdatadir $ ls
total 37M
-rw-r----- 1 karl karl 37M abr 5 14:33 t1.MYD


Remember that in order to change the directory you need:

  1. The destination directory must exists
  2. FILE permissions granted.

I hope it helps.

Regards

Thanks for the advice, I’ll take another look on Monday and see if its something totally trivial like permissions. One of my unasked questions has also been answered, in that a sym link will be created, which will suit us just fine.

Hi mikes;

Yeah, just make sure symlinks are enabled (SHOW VARIABLES LIKE ‘have_symlink’). What Carlos described is how it should work, but that requires symlinks to be enabled, which I think is the issue you’re having based on your output.

Have a good weekend!

Thanks Everyone,

It would appear that having symlinks disabled would appear to be the issue for MyISAM files. Perhaps a warning when executed would be nice, as the output is identical to trying the same operation on an InnoDB file, except that this is successful, replacing the .ibd file with a .isl file containing the new location.

Cheers,

Mike

I’ve created https://jira.percona.com/browse/PT-1529 to track this issue