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

Online alter of a partitioned MyISAM table with auto_increment

    XMLWordPrintable

Details

    Description

      Even though fix for MDEV-11335 works for partitioned MyISAM tables in simple cases like this:

      MariaDB [test]> show create table tt\G
      *************************** 1. row ***************************
             Table: tt
      Create Table: CREATE TABLE `tt` (
        `a` int(11) DEFAULT NULL,
        `b` int(11) DEFAULT '1',
        KEY `b` (`b`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 DELAY_KEY_WRITE=1
      /*!50100 PARTITION BY HASH (b)
      PARTITIONS 30 */
      1 row in set (0.00 sec)
       
      MariaDB [test]> alter online table tt delay_key_write=0;
      Query OK, 0 rows affected (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> alter online table tt delay_key_write=1;
      Query OK, 0 rows affected (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      rows are still copied for a bit more complex tables (the difference noted so far is the presence of datetime columns with current_timestamp [ON UPDATE CURRENT_TIMESTAMP] default values, like these:

      MariaDB [test]> create table ttt(id_1 int auto_increment, id_2 int, id_3 int, d1 date, dt1 datetime default current_timestamp, dt2 datetime default current_timestamp on update current_timestamp, primary key (id_2, id_3), key(id_1)) engine=myisam partition by hash(id_2) partitions 3 (partition p01 engine=myisam, partition p02 engine=myisam, partition p03 engine=myisam);
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [test]> insert into ttt values(0, 1, 1, NULL, now(), now());
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [test]> select * from ttt;
      +------+------+------+------+---------------------+---------------------+
      | id_1 | id_2 | id_3 | d1   | dt1                 | dt2                 |
      +------+------+------+------+---------------------+---------------------+
      |    1 |    1 |    1 | NULL | 2017-06-15 10:35:10 | 2017-06-15 10:35:10 |
      +------+------+------+------+---------------------+---------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> alter online table tt delay_key_write=1;
      Query OK, 0 rows affected (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> alter online table tt delay_key_write=0;
      Query OK, 0 rows affected (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> alter online table ttt delay_key_write=1;
      ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED.
      MariaDB [test]> alter online table ttt delay_key_write=1, lock=shared;
      Query OK, 1 row affected (0.04 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table ttt\G
      *************************** 1. row ***************************
             Table: ttt
      Create Table: CREATE TABLE `ttt` (
        `id_1` int(11) NOT NULL AUTO_INCREMENT,
        `id_2` int(11) NOT NULL,
        `id_3` int(11) NOT NULL,
        `d1` date DEFAULT NULL,
        `dt1` datetime DEFAULT CURRENT_TIMESTAMP,
        `dt2` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id_2`,`id_3`),
        KEY `id_1` (`id_1`)
      ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1
      /*!50100 PARTITION BY HASH (id_2)
      (PARTITION p01 ENGINE = MyISAM,
       PARTITION p02 ENGINE = MyISAM,
       PARTITION p03 ENGINE = MyISAM) */
      1 row in set (0.00 sec)
      

      I think we should either fix this case also, or document it clearly as a known limitation.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.