[MDEV-5460] Upgrade 5.5 to 10.0.6 Fails with PARTITIONS Created: 2013-12-17  Updated: 2013-12-20  Resolved: 2013-12-20

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.6
Fix Version/s: 10.0.7

Type: Bug Priority: Major
Reporter: Joffrey MICHAIE (Inactive) Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux

Issue Links:
Duplicate
duplicates MDEV-5281 Partitioning issue after upgrade from... Closed

 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



 Comments   
Comment by Elena Stepanova [ 2013-12-17 ]

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.

Comment by Elena Stepanova [ 2013-12-20 ]

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

Generated at Thu Feb 08 07:04:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.