Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3, 10.4, 10.5, 10.6
-
None
Description
Currently MariaDB don't support ALTER TABLE PARTITION for RANGE UNIX_TIMESTAMP(COLUMN_NAME)
Reference :- https://mariadb.com/kb/en/unix_timestamp/
For Example :-
|
|
CREATE TABLE `test_partitioned` (
|
`id` bigint NOT NULL AUTO_INCREMENT,
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`local_time` timestamp NULL DEFAULT NULL,
|
`resident_id` int NOT NULL,
|
KEY `Index 1` (`id`),
|
KEY `Index 2` (`resident_id`,`local_time`),
|
KEY `Index 3` (`created_at`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
/*!50100 PARTITION BY RANGE (unix_timestamp(`created_at`))
|
(PARTITION p20220806 VALUES LESS THAN (1659744000) ENGINE = InnoDB,
|
PARTITION p20220807 VALUES LESS THAN (1659830400) ENGINE = InnoDB,
|
PARTITION p20220808 VALUES LESS THAN (1659916800) ENGINE = InnoDB,
|
PARTITION p20220809 VALUES LESS THAN (1660003200) ENGINE = InnoDB,
|
PARTITION p20220810 VALUES LESS THAN (1660089600) ENGINE = InnoDB,
|
PARTITION p20220811 VALUES LESS THAN (1660176000) ENGINE = InnoDB,
|
PARTITION p20220812 VALUES LESS THAN (1660262400) ENGINE = InnoDB,
|
PARTITION p20220813 VALUES LESS THAN (1660348800) ENGINE = InnoDB,
|
PARTITION p20220814 VALUES LESS THAN (1660435200) ENGINE = InnoDB,
|
PARTITION p20220815 VALUES LESS THAN (1660521600) ENGINE = InnoDB,
|
PARTITION p20220816 VALUES LESS THAN (1660608000) ENGINE = InnoDB,
|
PARTITION p20220817 VALUES LESS THAN (1660694400) ENGINE = InnoDB,
|
PARTITION p20220818 VALUES LESS THAN (1660780800) ENGINE = InnoDB,
|
PARTITION p20220819 VALUES LESS THAN (1660867200) ENGINE = InnoDB,
|
PARTITION p20220820 VALUES LESS THAN (1660953600) ENGINE = InnoDB,
|
PARTITION p20220821 VALUES LESS THAN (1661040000) ENGINE = InnoDB,
|
PARTITION p20220822 VALUES LESS THAN (1661126400) ENGINE = InnoDB,
|
PARTITION p20220823 VALUES LESS THAN (1661212800) ENGINE = InnoDB,
|
PARTITION p20220824 VALUES LESS THAN (1661299200) ENGINE = InnoDB,
|
PARTITION p20220825 VALUES LESS THAN (1661385600) ENGINE = InnoDB,
|
PARTITION p20220826 VALUES LESS THAN (1661472000) ENGINE = InnoDB,
|
PARTITION p20220827 VALUES LESS THAN (1661558400) ENGINE = InnoDB,
|
PARTITION p20220828 VALUES LESS THAN (1661644800) ENGINE = InnoDB,
|
PARTITION p20220829 VALUES LESS THAN (1661731200) ENGINE = InnoDB,
|
PARTITION p20220830 VALUES LESS THAN (1661817600) ENGINE = InnoDB,
|
PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
|
|
|
|
ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO ( PARTITION p20220901 VALUES
|
LESS THAN (UNIX_TIMESTAMP('2022–09–02 00:00:00')), PARTITION future VALUES LESS THAN MAXVALUE );
|
|
|
|
ERROR 1697 (HY000): VALUES value for partition 'p20220901' must have type INT
|
|
During create table UNIX_TIMESTAMP('2022-09-02 00:00:00') will be converted into int, before the int check will be done. Maybe in the alter statement it is different.... so using the int in alter is a workaround.
SELECT UNIX_TIMESTAMP('2022-09-02 00:00:00');
|
|
|
+---------------------------------------+
|
| UNIX_TIMESTAMP('2022-09-02 00:00:00') |
|
+---------------------------------------+
|
| 1662091200 |
|
+---------------------------------------+
|
1 row in set (0.000 sec)
|
|
Using the above value in below alter command ...
|
|
ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO ( PARTITION p20220901 VALUES
|
LESS THAN (1662091200), PARTITION future VALUES LESS THAN MAXVALUE );
|
Query OK, 0 rows affected (0.036 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|