MySQL Bugs: #43923: Create Table

Bug #43923	Create Table - Partition
Submitted:	27 Mar 2009 20:51	Modified:	27 Mar 2009 21:21
Reporter:	Leandro Concon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S5 (Performance)
Version:	MySQL 5.1.30-community	OS:	Microsoft Windows (2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
ERROR: VALUES LESS THAN value must be strictly increasing for each partition

CREATE TABLE IF NOT EXISTS BI_MKT_NDDD_DW.FT_SALES
(
	CD_DISTRIBUTION_CHANNEL INT(6) NOT NULL,
	CD_FCC_CUSTOM INT(10) NOT NULL,
	CD_DIVISION INT(2) NOT NULL DEFAULT '999999999',
	CD_STRUCT_ORG INT(10) NOT NULL,
	CD_BRICK INT(10) NOT NULL,
	CD_BUSINESS_UNIT INT(10) NOT NULL,
	CD_THERAPEUTIC_CLASS INT(6) NOT NULL,
	CD_PRODUCT_TYPE INT(2) NOT NULL,
	CD_MARKET_TYPE INT(2) NOT NULL,
	CD_PHARMACEUTICAL_FORM INT(6) NOT NULL,
	CD_TIME DATE NOT NULL,
	CD_MOLECULE INT(10) NOT NULL,
	MONTHLY_REAL NUMERIC(15,2) NOT NULL,
	MONTHLY_DOLAR NUMERIC(15,2) NOT NULL,
	MONTHLY_UNITS BIGINT(15) NOT NULL,
	PRIMARY KEY (CD_DISTRIBUTION_CHANNEL, CD_FCC_CUSTOM, CD_DIVISION, CD_STRUCT_ORG, CD_BRICK, CD_BUSINESS_UNIT, CD_THERAPEUTIC_CLASS, CD_PRODUCT_TYPE, CD_MARKET_TYPE, CD_PHARMACEUTICAL_FORM, CD_TIME, CD_MOLECULE)
)
	ENGINE = MyISAM
	COMMENT = 'Tabela Fato'
	PARTITION BY RANGE ( EXTRACT(YEAR_MONTH FROM CD_TIME) )
	 (

PARTITION FT200912 VALUES LESS THAN (200912),
PARTITION FT200911 VALUES LESS THAN (200911),
PARTITION FT200910 VALUES LESS THAN (200910),
PARTITION FT200909 VALUES LESS THAN (200909),
PARTITION FT200908 VALUES LESS THAN (200908),
PARTITION FT200907 VALUES LESS THAN (200907),
PARTITION FT200906 VALUES LESS THAN (200906),
PARTITION FT200905 VALUES LESS THAN (200905),
PARTITION FT200904 VALUES LESS THAN (200904),
PARTITION FT200903 VALUES LESS THAN (200903),
PARTITION FT200902 VALUES LESS THAN (200902),
PARTITION FT200901 VALUES LESS THAN (200901),
PARTITION FT200812 VALUES LESS THAN (200812),
PARTITION FT200811 VALUES LESS THAN (200811),
PARTITION FT200810 VALUES LESS THAN (200810),
PARTITION FT200809 VALUES LESS THAN (200809),
PARTITION FT200808 VALUES LESS THAN (200808),
PARTITION FT200807 VALUES LESS THAN (200807),
PARTITION FT200806 VALUES LESS THAN (200806),
PARTITION FT200805 VALUES LESS THAN (200805),
PARTITION FT200804 VALUES LESS THAN (200804),
PARTITION FT200803 VALUES LESS THAN (200803),
PARTITION FT200802 VALUES LESS THAN (200802),
PARTITION FT200801 VALUES LESS THAN (200801),
PARTITION FT200712 VALUES LESS THAN (200712),
PARTITION FT200711 VALUES LESS THAN (200711),
PARTITION FT200710 VALUES LESS THAN (200710),
PARTITION FT200709 VALUES LESS THAN (200709),
PARTITION FT200708 VALUES LESS THAN (200708),
PARTITION FT200707 VALUES LESS THAN (200707),
PARTITION FT200706 VALUES LESS THAN (200706),
PARTITION FT200705 VALUES LESS THAN (200705),
PARTITION FT200704 VALUES LESS THAN (200704),
PARTITION FT200703 VALUES LESS THAN (200703),
PARTITION FT200702 VALUES LESS THAN (200702),
PARTITION FT200701 VALUES LESS THAN (200701),
PARTITION FT200612 VALUES LESS THAN (200612),
PARTITION FT200611 VALUES LESS THAN (200611),
PARTITION FT200610 VALUES LESS THAN (200610),
PARTITION FT200609 VALUES LESS THAN (200609),
PARTITION FT200608 VALUES LESS THAN (200608),
PARTITION FT200607 VALUES LESS THAN (200607),
PARTITION FT200606 VALUES LESS THAN (200606),
PARTITION FT200605 VALUES LESS THAN (200605),
PARTITION FT200604 VALUES LESS THAN (200604),
PARTITION FT200603 VALUES LESS THAN (200603),
PARTITION FT200602 VALUES LESS THAN (200602),
PARTITION FT200601 VALUES LESS THAN (200601),
PARTITION FTMAX VALUES LESS THAN MAXVALUE
);

How to repeat:
CREATE TABLE IF NOT EXISTS BI_MKT_NDDD_DW.FT_SALES
(
	CD_DISTRIBUTION_CHANNEL INT(6) NOT NULL,
	CD_FCC_CUSTOM INT(10) NOT NULL,
	CD_DIVISION INT(2) NOT NULL DEFAULT '999999999',
	CD_STRUCT_ORG INT(10) NOT NULL,
	CD_BRICK INT(10) NOT NULL,
	CD_BUSINESS_UNIT INT(10) NOT NULL,
	CD_THERAPEUTIC_CLASS INT(6) NOT NULL,
	CD_PRODUCT_TYPE INT(2) NOT NULL,
	CD_MARKET_TYPE INT(2) NOT NULL,
	CD_PHARMACEUTICAL_FORM INT(6) NOT NULL,
	CD_TIME DATE NOT NULL,
	CD_MOLECULE INT(10) NOT NULL,
	MONTHLY_REAL NUMERIC(15,2) NOT NULL,
	MONTHLY_DOLAR NUMERIC(15,2) NOT NULL,
	MONTHLY_UNITS BIGINT(15) NOT NULL,
	PRIMARY KEY (CD_DISTRIBUTION_CHANNEL, CD_FCC_CUSTOM, CD_DIVISION, CD_STRUCT_ORG, CD_BRICK, CD_BUSINESS_UNIT, CD_THERAPEUTIC_CLASS, CD_PRODUCT_TYPE, CD_MARKET_TYPE, CD_PHARMACEUTICAL_FORM, CD_TIME, CD_MOLECULE)
)
	ENGINE = MyISAM
	COMMENT = 'Tabela Fato'
	PARTITION BY RANGE ( EXTRACT(YEAR_MONTH FROM CD_TIME) )
	 (

PARTITION F1 VALUES LESS THAN (200912),
PARTITION F2 VALUES LESS THAN (200911),
PARTITION F3 VALUES LESS THAN (200910),
PARTITION F4 VALUES LESS THAN (200909),
PARTITION F5 VALUES LESS THAN (200908),
PARTITION F6 VALUES LESS THAN (200907),
PARTITION F7 VALUES LESS THAN (200906),
PARTITION F8 VALUES LESS THAN MAXVALUE
);

Thank you for the bug report. Following instructions according the error message:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.34-Win X64 revno: 2772-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >CREATE DATABASE BI_MKT_NDDD_DW;
Query OK, 1 row affected (0.16 sec)

mysql 5.1 >CREATE TABLE IF NOT EXISTS BI_MKT_NDDD_DW.FT_SALES
    -> (
    ->  CD_DISTRIBUTION_CHANNEL INT(6) NOT NULL,
    ->  CD_FCC_CUSTOM INT(10) NOT NULL,
    ->  CD_DIVISION INT(2) NOT NULL DEFAULT '999999999',
    ->  CD_STRUCT_ORG INT(10) NOT NULL,
    ->  CD_BRICK INT(10) NOT NULL,
    ->  CD_BUSINESS_UNIT INT(10) NOT NULL,
    ->  CD_THERAPEUTIC_CLASS INT(6) NOT NULL,
    ->  CD_PRODUCT_TYPE INT(2) NOT NULL,
    ->  CD_MARKET_TYPE INT(2) NOT NULL,
    ->  CD_PHARMACEUTICAL_FORM INT(6) NOT NULL,
    ->  CD_TIME DATE NOT NULL,
    ->  CD_MOLECULE INT(10) NOT NULL,
    ->  MONTHLY_REAL NUMERIC(15,2) NOT NULL,
    ->  MONTHLY_DOLAR NUMERIC(15,2) NOT NULL,
    ->  MONTHLY_UNITS BIGINT(15) NOT NULL,
    ->  PRIMARY KEY (CD_DISTRIBUTION_CHANNEL, CD_FCC_CUSTOM, CD_DIVISION, CD_STRUCT_ORG,
    -> CD_BRICK, CD_BUSINESS_UNIT, CD_THERAPEUTIC_CLASS, CD_PRODUCT_TYPE, CD_MARKET_TYPE,
    -> CD_PHARMACEUTICAL_FORM, CD_TIME, CD_MOLECULE)
    -> )
    ->  ENGINE = MyISAM
    ->  COMMENT = 'Tabela Fato'
    ->  PARTITION BY RANGE ( EXTRACT(YEAR_MONTH FROM CD_TIME) )
    ->   (
    ->
    -> PARTITION F1 VALUES LESS THAN (200912),
    -> PARTITION F2 VALUES LESS THAN (200911),
    -> PARTITION F3 VALUES LESS THAN (200910),
    -> PARTITION F4 VALUES LESS THAN (200909),
    -> PARTITION F5 VALUES LESS THAN (200908),
    -> PARTITION F6 VALUES LESS THAN (200907),
    -> PARTITION F7 VALUES LESS THAN (200906),
    -> PARTITION F8 VALUES LESS THAN MAXVALUE
    -> );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
mysql 5.1 >CREATE TABLE IF NOT EXISTS BI_MKT_NDDD_DW.FT_SALES
    -> (
    ->  CD_DISTRIBUTION_CHANNEL INT(6) NOT NULL,
    ->  CD_FCC_CUSTOM INT(10) NOT NULL,
    ->  CD_DIVISION INT(2) NOT NULL DEFAULT '999999999',
    ->  CD_STRUCT_ORG INT(10) NOT NULL,
    ->  CD_BRICK INT(10) NOT NULL,
    ->  CD_BUSINESS_UNIT INT(10) NOT NULL,
    ->  CD_THERAPEUTIC_CLASS INT(6) NOT NULL,
    ->  CD_PRODUCT_TYPE INT(2) NOT NULL,
    ->  CD_MARKET_TYPE INT(2) NOT NULL,
    ->  CD_PHARMACEUTICAL_FORM INT(6) NOT NULL,
    ->  CD_TIME DATE NOT NULL,
    ->  CD_MOLECULE INT(10) NOT NULL,
    ->  MONTHLY_REAL NUMERIC(15,2) NOT NULL,
    ->  MONTHLY_DOLAR NUMERIC(15,2) NOT NULL,
    ->  MONTHLY_UNITS BIGINT(15) NOT NULL,
    ->  PRIMARY KEY (CD_DISTRIBUTION_CHANNEL, CD_FCC_CUSTOM, CD_DIVISION, CD_STRUCT_ORG,
    -> CD_BRICK, CD_BUSINESS_UNIT, CD_THERAPEUTIC_CLASS, CD_PRODUCT_TYPE, CD_MARKET_TYPE,
    -> CD_PHARMACEUTICAL_FORM, CD_TIME, CD_MOLECULE)
    -> )
    ->  ENGINE = MyISAM
    ->  COMMENT = 'Tabela Fato'
    ->  PARTITION BY RANGE ( EXTRACT(YEAR_MONTH FROM CD_TIME) )
    ->   (
    -> PARTITION F7 VALUES LESS THAN (200906),
    -> PARTITION F6 VALUES LESS THAN (200907),
    -> PARTITION F5 VALUES LESS THAN (200908),
    -> PARTITION F4 VALUES LESS THAN (200909),
    -> PARTITION F3 VALUES LESS THAN (200910),
    -> PARTITION F2 VALUES LESS THAN (200911),
    -> PARTITION F1 VALUES LESS THAN (200912),
    -> PARTITION F8 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql 5.1 >