[MDEV-5460] Upgrade 5.5 to 10.0.6 Fails with PARTITIONS - JIRA

Details

Type: Bug
Status: Closed
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

Gliffy Diagrams

Attachments

Issue Links

duplicates

MDEV-5281 Partitioning issue after upgrade from 10.0.3-1 to 10.0.5-1

Closed

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2013-12-17 17:31

At the first glance it looks like the same issue as ~~MDEV-5281~~, I will check it on the current tree to see whether the patch for ~~MDEV-5281~~ fixes this one as well.

Show

Elena Stepanova added a comment - 2013-12-17 17:31 At the first glance it looks like the same issue as MDEV-5281 , I will check it on the current tree to see whether the patch for MDEV-5281 fixes this one as well.

Hide

Permalink

Elena Stepanova added a comment - 2013-12-20 00:28

I checked that the provided test case stopped failing after the fix for ~~MDEV-5281~~ which will be released in 10.0.7.

Show

Elena Stepanova added a comment - 2013-12-20 00:28 I checked that the provided test case stopped failing after the fix for MDEV-5281 which will be released in 10.0.7.

Upgrade 5.5 to 10.0.6 Fails with PARTITIONS

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile