MariaDB/MySQL + TokuDB Partitioning Error – ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

MariaDB/MySQL + TokuDB Partitioning Error – ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

The above error occurs while trying to add a new partition in a MariaDB/MySQL partitioned table.

Existing table definition

MariaDB [partitiontest]> show create table salaries;
| salaries | CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`='tokudb_zlib'
/*!50500 PARTITION BY RANGE COLUMNS(from_date)
(PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = TokuDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = TokuDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = TokuDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = TokuDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = TokuDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = TokuDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = TokuDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = TokuDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = TokuDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = TokuDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = TokuDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = TokuDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = TokuDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = TokuDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = TokuDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = TokuDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = TokuDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = TokuDB,
 PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB) */

Error occur while trying to add a new partition

alter table salaries
add partition
(partition p20 values less than ('2003-12-31')
engine=TokuDB);
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

The error is due to the following reasons:

  • You are trying to add a “lesser” value that the current largest value, which captures all
  • In this case, I was trying to add <2003-12-13 date, which is lesser than the current last partition, MAXVALUE

I will now “REORGANIZE” the table so that I can add a partition between “<2002-12-13” and “MAXVALUE”.

alter table salaries
reorganize partition p19 into
(partition p19 values less than ('2003-12-31'),
partition p20 values less than (MAXVALUE)
)

Let’s take at a look at the happier¬†table now!

MariaDB [partitiontest]> show create table salaries;
 | salaries | CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`)
 ) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`='tokudb_zlib'
 /*!50500 PARTITION BY RANGE COLUMNS(from_date)
 (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = TokuDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = TokuDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = TokuDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = TokuDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = TokuDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = TokuDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = TokuDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = TokuDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = TokuDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = TokuDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = TokuDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = TokuDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = TokuDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = TokuDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = TokuDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = TokuDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = TokuDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = TokuDB,
 PARTITION p19 VALUES LESS THAN ('2003-12-31') ENGINE = TokuDB,
 PARTITION p20 VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB) */|

Regards,
Wei Shan

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: