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

Polyglot Projects Percona Toolkit
1

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

2

Hi mikes;

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

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

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.

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

3

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

4

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.

5

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!

6

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

7

I’ve created [URL][PT-1529] Check symlinks are enables in osc + data-dir - Percona JIRA to track this issue