I’m using MariDB 10.4.12 on Windows server 2016

 

I have a table with one of the columns defined as

 

`id` bigint(20) NOT NULL AUTO_INCREMENT

 

and

 

PRIMARY KEY (`id`)

 

It is currently a MyISAM table and has been in use for ages. The current auto increment value is AUTO_INCREMENT=106274948879084. Rows have been added and removed over time such that there are gaps in the id value. The table passes all checks, so there are no duplicate entries.

 

I recently tried to change the table to use the Aria engine using the command

 

ALTER TABLE xxx ENGINE=ARIA;

 

and got the error

 

ERROR 1062 (23000) at line 4: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '106274948781020' for key 'PRIMARY'

 

I can fix the problem by resetting the auto increment values using

 

ALTER TABLE xxx AUTO_INCREMENT=1;

 

but is this something I should expect? First, I don’t really see why it is resequencing. No data should be changing. Then why is the resequencing generating a duplicate entry? Fortunately in this instance I don’t care what the id is, but had this been used by another table, changing the id may have caused problems.

 

Thanks for any help on this.

 

Andy Ling

 

DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.