Description:
In product environment, we have a big partitioned table which has millions partitions, each day a new partition will be created, and truncated to verify its emptiness.
However, the error "ERROR 1062 (23000): Duplicate entry '2147483648-0-0' for key 'table_partition_values.PRIMARY'" occurred when trying to truncate a partition, and we can't create any other new partitioned tables either.
In debug mode, we have checked the system table 'table_partition_values' and detected that this PK indeed exists (first partition).
We guess that maybe partition_id exceed INT_MAX, but according to the schema of table_partition_values, the partition_id column is of the bigint data type.
Under the fact that each time add/drop a new partition, all partitions' partition_id will increase from auto_incremented continuously, we provide a method to repeat this problem.
How to repeat:
As in product environment, a large amount of partitions are created and dropped, which is too costly in debug mode, we simulate a case that partition_id has already near to INT_MAX by alter auto_increment:
in debug mode:
```
set debug='+d,skip_dd_table_access_check';
alter table mysql.table_partitions auto_increment=2147483646;
CREATE TABLE `t1` ( `dtstat` bigint NOT NULL,
`id` bigint NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`,`dtstat`) )
ENGINE=InnoDB AUTO_INCREMENT=5808007 DEFAULT CHARSET=utf8 PARTITION BY LIST (`dtstat`)
(PARTITION p_20230601 VALUES IN (20230601) ENGINE = InnoDB,
PARTITION P_20230201 VALUES IN (20230201) ENGINE = InnoDB);
alter table t1 add partition (PARTITION P_20240101 VALUES IN (20240101)ENGINE = InnoDB);
alter table t1 truncate partition P_20240101;
```
Now, 'truncate partition' will report an error.
Suggested fix:
In the constructor of the class Table_partition_values_pk, type Object_id is converted to int, which causes the problem.
We attempt to fix this by replace 'int' with 'ulonglong' and succeed, further test are ongoing.
```
class Table_partition_values_pk : public Object_key {
public:
- Table_partition_values_pk(int partition_id, int list_num, int column_num)
+ Table_partition_values_pk(ulonglong partition_id, int list_num, int column_num)
: m_partition_id(partition_id),
m_list_num(list_num),
m_column_num(column_num) {}
@@ -88,7 +88,7 @@ class Table_partition_values_pk : public Object_key {
String_type str() const override;
private:
- int m_partition_id;
+ ulonglong m_partition_id;
int m_list_num;
int m_column_num;
};
```
Description: In product environment, we have a big partitioned table which has millions partitions, each day a new partition will be created, and truncated to verify its emptiness. However, the error "ERROR 1062 (23000): Duplicate entry '2147483648-0-0' for key 'table_partition_values.PRIMARY'" occurred when trying to truncate a partition, and we can't create any other new partitioned tables either. In debug mode, we have checked the system table 'table_partition_values' and detected that this PK indeed exists (first partition). We guess that maybe partition_id exceed INT_MAX, but according to the schema of table_partition_values, the partition_id column is of the bigint data type. Under the fact that each time add/drop a new partition, all partitions' partition_id will increase from auto_incremented continuously, we provide a method to repeat this problem. How to repeat: As in product environment, a large amount of partitions are created and dropped, which is too costly in debug mode, we simulate a case that partition_id has already near to INT_MAX by alter auto_increment: in debug mode: ``` set debug='+d,skip_dd_table_access_check'; alter table mysql.table_partitions auto_increment=2147483646; CREATE TABLE `t1` ( `dtstat` bigint NOT NULL, `id` bigint NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`,`dtstat`) ) ENGINE=InnoDB AUTO_INCREMENT=5808007 DEFAULT CHARSET=utf8 PARTITION BY LIST (`dtstat`) (PARTITION p_20230601 VALUES IN (20230601) ENGINE = InnoDB, PARTITION P_20230201 VALUES IN (20230201) ENGINE = InnoDB); alter table t1 add partition (PARTITION P_20240101 VALUES IN (20240101)ENGINE = InnoDB); alter table t1 truncate partition P_20240101; ``` Now, 'truncate partition' will report an error. Suggested fix: In the constructor of the class Table_partition_values_pk, type Object_id is converted to int, which causes the problem. We attempt to fix this by replace 'int' with 'ulonglong' and succeed, further test are ongoing. ``` class Table_partition_values_pk : public Object_key { public: - Table_partition_values_pk(int partition_id, int list_num, int column_num) + Table_partition_values_pk(ulonglong partition_id, int list_num, int column_num) : m_partition_id(partition_id), m_list_num(list_num), m_column_num(column_num) {} @@ -88,7 +88,7 @@ class Table_partition_values_pk : public Object_key { String_type str() const override; private: - int m_partition_id; + ulonglong m_partition_id; int m_list_num; int m_column_num; }; ```