Not the answer you need?
Register and ask your own question!

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

mikesmikes EntrantCurrent User Role Beginner

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.




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

[[email protected] 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.

[[email protected] 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`.
[[email protected] mysql]# pwd
[[email protected] 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 ?



  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi mikes;

    Looks like you're working with a MyISAM table, so the problem is most likely with symlinks.
    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, “Using Symbolic Links for MyISAM Tables on Unix”, for more complete information.

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role

    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.

    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 --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=,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:
    /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.

  • mikesmikes Entrant Current User Role Beginner
    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.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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!
  • mikesmikes Entrant Current User Role Beginner
    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.


  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    I've created to track this issue
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.