Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13636

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

    XMLWordPrintable

    Details

      Description

      As long as DATA DIRECTORY/INDEX DIRECTORY is set for MyISAM table, ALTER TABLE ... DELAY_KEY_WRITE=1 creates table copy. Check the following simple test case:

      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
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: