ALTER TABLE ... DELAY_KEY_WRITE=1 creates table copy for MyISAM table with DATA DIRECTORY/INDEX DIRECTORY options

openxs@ao756:~/dbs/maria10.2$ bin/mysql -uroot --host=127.0.0.1 --port=3308 testReading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 10.2.9-MariaDB Source distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> create table tmi(c1 int, c2 int, c3 varchar(100)) engine=MyISAM;

Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> insert into tmi values(1,1,repeat('a', 100));

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into tmi select rand()*1000, rand()*1000, repeat('b', 100) from tmi;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

...

MariaDB [test]> insert into tmi select rand()*1000, rand()*1000, repeat('b', 100) from tmi;

Query OK, 2097152 rows affected (14.50 sec)

Records: 2097152  Duplicates: 0  Warnings: 0

MariaDB [test]> create table tmi_dd(c1 int, c2 int, c3 varchar(100)) engine=MyISAM data directory='/tmp' index directory = '/tmp';

Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> insert into tmi_dd select * from tmi;                           

Query OK, 4194304 rows affected (8.49 sec)

Records: 4194304  Duplicates: 0  Warnings: 0

MariaDB [test]> alter table tmi delay_key_write=1;

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> alter table tmi_dd delay_key_write=1;

Stage: 1 of 2 'copy to tmp table'  0.023% of stage done

...

Query OK, 4194304 rows affected (18.16 sec)

Records: 4194304  Duplicates: 0  Warnings: 0

MariaDB [test]> alter online table tmi_dd delay_key_write=0;

ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED