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

Upgrade 5.5 to 10.0.6 Fails with PARTITIONS

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.6
    • Fix Version/s: 10.0.7
    • Component/s: None
    • Labels:
      None
    • Environment:
      Linux

      Description

      Binary upgrade 5.5 to 10.0 does not work with (some kind of) partitionned tables.

      MariaDB [test]> select version();
      +-----------------------------+
      | version()                   |
      +-----------------------------+
      | 5.5.34-MariaDB-1~wheezy-log |
      +-----------------------------+
      1 row in set (0.01 sec)
      MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS test;
      MariaDB [(none)]> use test
      Enable support for compressed data (not sure if needed to reproduce):
      MariaDB [(none)]> SET global  innodb_file_format = Barracuda;
       CREATE TABLE `stats_data` (
        `rowid` bigint(20) NOT NULL AUTO_INCREMENT,
        `stat_id` bigint(20) NOT NULL DEFAULT '0',
        `starttime` int(11) NOT NULL,
        `endtime` int(11) NOT NULL,
        `customer_id` bigint(20) NOT NULL,
        `key1` varchar(150) NOT NULL DEFAULT '',
        `key2` varchar(150) NOT NULL DEFAULT '',
        `key3` varchar(150) NOT NULL DEFAULT '',
        `key4` varchar(150) NOT NULL DEFAULT '',
        `key5` varchar(150) NOT NULL DEFAULT '',
        `key6` varchar(150) NOT NULL DEFAULT '',
        `value1` decimal(27,0) NOT NULL DEFAULT '0',
        `value2` decimal(27,0) NOT NULL DEFAULT '0',
        `value3` decimal(27,0) NOT NULL DEFAULT '0',
        `value4` decimal(27,0) NOT NULL DEFAULT '0',
        PRIMARY KEY (`rowid`,`stat_id`,`customer_id`,`starttime`),
        UNIQUE KEY `stat_id` (`stat_id`,`customer_id`,`starttime`,`endtime`,`key1`,`key2`,`key3`,`key4`,`key5`,`key6`),
        KEY `stats_data_idx6` (`stat_id`,`key6`),
        KEY `stats_data_idx1` (`stat_id`,`key1`),
        KEY `stats_data_idx2` (`stat_id`,`key2`),
        KEY `stats_data_idx3` (`stat_id`,`key3`),
        KEY `stats_data_idx4` (`stat_id`,`key4`),
        KEY `stats_data_idx5` (`stat_id`,`key5`)
      ) ENGINE=InnoDB AUTO_INCREMENT=27407669 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
      PARTITION BY RANGE (starttime)
      SUBPARTITION BY KEY (stat_id,customer_id)
      SUBPARTITIONS 16
      (PARTITION p201202 VALUES LESS THAN (1330556400) ENGINE = InnoDB,
       PARTITION p201203 VALUES LESS THAN (1333231200) ENGINE = InnoDB,
       PARTITION pnew VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
       
      MariaDB [test]> select * from stats_data;
      Empty set (0.00 sec)

      – Stop MariaDB 5.5
      – Switch binaries to 10.0.6
      – Start MariaDB 10.0
      run mysql_upgrade

      MariaDB [test]> select * from stats_data;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALGORITHM = 2 */ (stat_id,customer_id)
      SUBPARTITIONS 16
      (PARTITION p201202 VALUE' at line 2
       
       
      From InnoDB Table monitor:
      MariaDB [(none)]> use mysql
      MariaDB [mysql]> CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
      Dec 17 16:18:28 sd-25539 mysqld: TABLE: name test/stats_data#P#p201202#SP#p201202sp0, id 61, flags 29, columns 18, indexes 8, appr.rows 0
      Dec 17 16:18:28 sd-25539 mysqld:   COLUMNS: rowid: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; stat_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; starttime: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; endtime: DATA_INT DATA_BIN
      ARY_TYPE DATA_NOT_NULL len 4; customer_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; key1: DATA_VARCHAR prtype 524559 len 150; key2: DATA_VARCHAR prtype 524559 len 150; key3: DATA_VARCHAR prtype 524559 len 150; key4: DATA_VARCHAR prt
      ype 524559 len 150; key5: DATA_VARCHAR prtype 524559 len 150; key6: DATA_VARCHAR prtype 524559 len 150; value1: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; value2: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; value3: DA
      TA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; value4: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name PRIMARY, id 399, fields 4/17, uniq 4, type 3
      Dec 17 16:18:28 sd-25539 mysqld:    root page 3, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  rowid stat_id customer_id starttime DB_TRX_ID DB_ROLL_PTR endtime key1 key2 key3 key4 key5 key6 value1 value2 value3 value4
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stat_id, id 400, fields 10/11, uniq 10, type 2
      Dec 17 16:18:28 sd-25539 mysqld:    root page 4, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id customer_id starttime endtime key1 key2 key3 key4 key5 key6 rowid
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stats_data_idx6, id 401, fields 2/5, uniq 5, type 0
      Dec 17 16:18:28 sd-25539 mysqld:    root page 5, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id key6 rowid customer_id starttime
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stats_data_idx1, id 402, fields 2/5, uniq 5, type 0
      Dec 17 16:18:28 sd-25539 mysqld:    root page 6, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id key1 rowid customer_id starttime
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stats_data_idx2, id 403, fields 2/5, uniq 5, type 0
      Dec 17 16:18:28 sd-25539 mysqld:    root page 7, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id key2 rowid customer_id starttime
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stats_data_idx3, id 404, fields 2/5, uniq 5, type 0
      Dec 17 16:18:28 sd-25539 mysqld:    root page 8, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id key3 rowid customer_id starttime
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stats_data_idx4, id 405, fields 2/5, uniq 5, type 0
      Dec 17 16:18:28 sd-25539 mysqld:    root page 9, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id key4 rowid customer_id starttime
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stats_data_idx5, id 406, fields 2/5, uniq 5, type 0
      Dec 17 16:18:28 sd-25539 mysqld:    root page 10, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id key5 rowid customer_id starttime
      Dec 17 16:18:28 sd-25539 mysqld: --------------------------------------
      Dec 17 16:18:28 sd-25539 mysqld: TABLE: name test/stats_data#P#p201202#SP#p201202sp1, id 62, flags 29, columns 18, indexes 8, appr.rows 0
      Dec 17 16:18:28 sd-25539 mysqld:   COLUMNS: rowid: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; stat_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; starttime: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; endtime: DATA_INT DATA_BIN
      ARY_TYPE DATA_NOT_NULL len 4; customer_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; key1: DATA_VARCHAR prtype 524559 len 150; key2: DATA_VARCHAR prtype 524559 len 150; key3: DATA_VARCHAR prtype 524559 len 150; key4: DATA_VARCHAR prt
      ype 524559 len 150; key5: DATA_VARCHAR prtype 524559 len 150; key6: DATA_VARCHAR prtype 524559 len 150; value1: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; value2: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; value3: DA
      TA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; value4: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 12; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name PRIMARY, id 407, fields 4/17, uniq 4, type 3
      Dec 17 16:18:28 sd-25539 mysqld:    root page 3, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  rowid stat_id customer_id starttime DB_TRX_ID DB_ROLL_PTR endtime key1 key2 key3 key4 key5 key6 value1 value2 value3 value4
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stat_id, id 408, fields 10/11, uniq 10, type 2
      Dec 17 16:18:28 sd-25539 mysqld:    root page 4, appr.key vals 0, leaf pages 1, size pages 1
      Dec 17 16:18:28 sd-25539 mysqld:    FIELDS:  stat_id customer_id starttime endtime key1 key2 key3 key4 key5 key6 rowid
      Dec 17 16:18:28 sd-25539 mysqld:   INDEX: name stats_data_idx6, id 409, fields 2/5, uniq 5, type 0
      Dec 17 16:18:28 sd-25539 mysqld:    root page 5, appr.key vals 0, leaf pages 1, size pages 1

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              joffrey Joffrey MICHAIE (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: