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

**URL:** <https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286>\
**Category:** Percona Toolkit\
**Created:** [April 5, 2018, 4:50am UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286 "2018-04-05T04:50:50Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikes/32/2876_2.png) [@mikes](https://forums.percona.com/u/mikes)\
**Post date:** [April 5, 2018, 4:50am UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286/1 "2018-04-05T04:50:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\
**Post date:** [April 5, 2018, 10:27am UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286/2 "2018-04-05T10:27:26Z")

</div>

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]](https://dev.mysql.com/doc/refman/5.7/en/create-table.html%5B/url%5D)

```auto
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]](https://dev.mysql.com/doc/refman/5.7/en/symbolic-links-to-tables.html%5B/url%5D)

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [April 5, 2018, 12:15pm UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286/3 "2018-04-05T12:15:23Z")

</div>

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:

```auto
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](https://github.com/Percona-Lab/mysql_random_data_load) as follow:

```auto
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:

```auto
.../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:

```auto
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:

```auto
.../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:

```auto
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

---

<div class="post-metadata">

**Author:** ![mikes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikes/32/2876_2.png) [@mikes](https://forums.percona.com/u/mikes)\
**Post date:** [April 6, 2018, 2:51am UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286/4 "2018-04-06T02:51:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\
**Post date:** [April 6, 2018, 9:40am UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286/5 "2018-04-06T09:40:42Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![mikes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikes/32/2876_2.png) [@mikes](https://forums.percona.com/u/mikes)\
**Post date:** [April 9, 2018, 4:11am UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286/6 "2018-04-09T04:11:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [April 9, 2018, 5:56am UTC](https://forums.percona.com/t/pt-online-schema-change-table-fails-to-change-directory/6286/7 "2018-04-09T05:56:55Z")

</div>

I’ve created [URL][[PT-1529] Check symlinks are enables in osc + data-dir - Percona JIRA](https://jira.percona.com/browse/PT-1529%5B/URL%5D) to track this issue