Description:
Altering a table to use partition feature generates wrong SQL code while 'Synchronize Model...'
In the example I enable partitioning and add a column. While I'm working on our company database, I already drop a column, and the resulting forwarding SQL code looks like the same. 'PARTITION BY' is first ALTER statement, but NOT sepearated by comma and the DROP COLUMN directly behind: "...LESS THAN (MAXVALUE)) DROP COLUMN `..."
(Please execuse my bad english)
How to repeat:
Create a simple, not partitionized table:
CREATE TABLE `table1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`one_value` varchar(64) DEFAULT NULL,
`another_value` varchar(64) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Generate table via 'Database'->'Forward Engineer...'
Add column 'start_datetime' DATETIME NOT NULL and add to the PRIMARY KEY.
Select 'Enable Partitioning', 'Partition By:' RANGE, 'Parameters:' YEAR(start_datetime), 'Partition Count:' 4, checked 'Manual'.
Enter in column 'Values' for part0...3: 2010, 2015, 2020 and MAXVALUE
Result after right click on 'table1'->'Copy SQL to Clipboard':
CREATE TABLE IF NOT EXISTS `test`.`table1` (
`ID` INT NOT NULL AUTO_INCREMENT COMMENT '',
`start_datetime` DATETIME NOT NULL COMMENT '',
`one_value` VARCHAR(64) NULL COMMENT '',
`another_value` VARCHAR(64) NULL COMMENT '',
PRIMARY KEY (`ID`, `start_datetime`) COMMENT '')
ENGINE = InnoDB PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4( PARTITION part0 VALUES LESS THAN (2010), PARTITION part1 VALUES LESS THAN (2015), PARTITION part2 VALUES LESS THAN (2020), PARTITION part3 VALUES LESS THAN (MAXVALUE))
Generated SQL after 'Database'->'Synchronize Model' (no Options selected and click 'Next' six times):
: (SET @... stuff)
ALTER TABLE `test`.`table1`
CHARACTER SET = utf8 , COLLATE = utf8_general_ci PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4( PARTITION part0 VALUES LESS THAN (2010), PARTITION part1 VALUES LESS THAN (2015), PARTITION part2 VALUES LESS THAN (2020), PARTITION part3 VALUES LESS THAN (MAXVALUE)) ,
ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`ID`, `start_datetime`) COMMENT '';
: (SET @... stuff)
Result after click 'Execute >':
Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`,
DROP PRIMA' at line 2
SQL Code:
ALTER TABLE `test`.`table1`
CHARACTER SET = utf8 , COLLATE = utf8_general_ci PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4( PARTITION part0 VALUES LESS THAN (2010), PARTITION part1 VALUES LESS THAN (2015), PARTITION part2 VALUES LESS THAN (2020), PARTITION part3 VALUES LESS THAN (MAXVALUE)) ,
ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`ID`, `start_datetime`) COMMENT ''
SQL script execution finished: statements: 3 succeeded, 1 failed
Fetching back view definitions in final form.
Nothing to fetch
SQL-Editor (Connection->Create new SQL tab for executing queries and paste generated SQL) says at the comma after "...(LESS THAN (MAXVALUE))":
"Syntax error: ',' (comma) is not a valid input at this position"
Suggested fix:
Create the 'PARTITON BY' in a separate ALTER TABLE statement.
ALTER TABLE `test`.`table1`
CHARACTER SET = utf8 , COLLATE = utf8_general_ci ,
ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`ID`, `start_datetime`) COMMENT '';
ALTER TABLE `test`.`table1`
PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4 (
PARTITION part0 VALUES LESS THAN (2010),
PARTITION part1 VALUES LESS THAN (2015),
PARTITION part2 VALUES LESS THAN (2020),
PARTITION part3 VALUES LESS THAN (MAXVALUE)
);
Description: Altering a table to use partition feature generates wrong SQL code while 'Synchronize Model...' In the example I enable partitioning and add a column. While I'm working on our company database, I already drop a column, and the resulting forwarding SQL code looks like the same. 'PARTITION BY' is first ALTER statement, but NOT sepearated by comma and the DROP COLUMN directly behind: "...LESS THAN (MAXVALUE)) DROP COLUMN `..." (Please execuse my bad english) How to repeat: Create a simple, not partitionized table: CREATE TABLE `table1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `one_value` varchar(64) DEFAULT NULL, `another_value` varchar(64) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Generate table via 'Database'->'Forward Engineer...' Add column 'start_datetime' DATETIME NOT NULL and add to the PRIMARY KEY. Select 'Enable Partitioning', 'Partition By:' RANGE, 'Parameters:' YEAR(start_datetime), 'Partition Count:' 4, checked 'Manual'. Enter in column 'Values' for part0...3: 2010, 2015, 2020 and MAXVALUE Result after right click on 'table1'->'Copy SQL to Clipboard': CREATE TABLE IF NOT EXISTS `test`.`table1` ( `ID` INT NOT NULL AUTO_INCREMENT COMMENT '', `start_datetime` DATETIME NOT NULL COMMENT '', `one_value` VARCHAR(64) NULL COMMENT '', `another_value` VARCHAR(64) NULL COMMENT '', PRIMARY KEY (`ID`, `start_datetime`) COMMENT '') ENGINE = InnoDB PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4( PARTITION part0 VALUES LESS THAN (2010), PARTITION part1 VALUES LESS THAN (2015), PARTITION part2 VALUES LESS THAN (2020), PARTITION part3 VALUES LESS THAN (MAXVALUE)) Generated SQL after 'Database'->'Synchronize Model' (no Options selected and click 'Next' six times): : (SET @... stuff) ALTER TABLE `test`.`table1` CHARACTER SET = utf8 , COLLATE = utf8_general_ci PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4( PARTITION part0 VALUES LESS THAN (2010), PARTITION part1 VALUES LESS THAN (2015), PARTITION part2 VALUES LESS THAN (2020), PARTITION part3 VALUES LESS THAN (MAXVALUE)) , ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`, DROP PRIMARY KEY, ADD PRIMARY KEY (`ID`, `start_datetime`) COMMENT ''; : (SET @... stuff) Result after click 'Execute >': Executing SQL script in server ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`, DROP PRIMA' at line 2 SQL Code: ALTER TABLE `test`.`table1` CHARACTER SET = utf8 , COLLATE = utf8_general_ci PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4( PARTITION part0 VALUES LESS THAN (2010), PARTITION part1 VALUES LESS THAN (2015), PARTITION part2 VALUES LESS THAN (2020), PARTITION part3 VALUES LESS THAN (MAXVALUE)) , ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`, DROP PRIMARY KEY, ADD PRIMARY KEY (`ID`, `start_datetime`) COMMENT '' SQL script execution finished: statements: 3 succeeded, 1 failed Fetching back view definitions in final form. Nothing to fetch SQL-Editor (Connection->Create new SQL tab for executing queries and paste generated SQL) says at the comma after "...(LESS THAN (MAXVALUE))": "Syntax error: ',' (comma) is not a valid input at this position" Suggested fix: Create the 'PARTITON BY' in a separate ALTER TABLE statement. ALTER TABLE `test`.`table1` CHARACTER SET = utf8 , COLLATE = utf8_general_ci , ADD COLUMN `start_datetime` DATETIME NOT NULL COMMENT '' AFTER `ID`, DROP PRIMARY KEY, ADD PRIMARY KEY (`ID`, `start_datetime`) COMMENT ''; ALTER TABLE `test`.`table1` PARTITION BY RANGE(YEAR(start_datetime)) PARTITIONS 4 ( PARTITION part0 VALUES LESS THAN (2010), PARTITION part1 VALUES LESS THAN (2015), PARTITION part2 VALUES LESS THAN (2020), PARTITION part3 VALUES LESS THAN (MAXVALUE) );