I am trying to empty the content of a table using the following command:
TRUNCATE TABLE <table_name>;
The table engine is InnoDB.
Upon executing this command, I receive the following error:
SQL Error [1062] [23000]: Duplicate entry '1' for key 'tablespaces.PRIMARY'
As I keep retrying, the number of the duplicate entry increments and the error becomes, e.g.:
SQL Error [1062] [23000]: Duplicate entry '2' for key 'tablespaces.PRIMARY'
For context, the schema where I execute this query in does not contain a "tablespaces" table. I suspected this table could be in the information_schema schema and it's indeed the case, but the tablespace table is completely empty.
How to explain this and how to work around this problem?
UPDATE
For context, this error started appearing after a migration from MySQL 5.7 to MySQL 8 on AWS Aurora
UPDATE 2
Additional context: Even simply creating a table triggers the error as well.
I'm performing this operation on a restored snapshot (I tried using the "copy-on-write" and "full-copy" methods, with same results).
I was afraid the main DB from which the snapshot is taken was equally affected by the bug, but it's not, as I can create tables there.
The solutions was sent to me by @sam-weston, who received the information through AWS support.
It appears to be a bug introduced with version 3.05.2 of Aurora MySQL.
The bug was fixed in Aurora MySQL 3.06.0, the solution is therefore to upgrade to this version and future snapshots will not have the issue.
AWS also provided the following workaround:
The suggested workaround is to restart the writer instance 2 times for the cache to be cleared and then the auto increment continues to work. If this workaround does not work, we kindly request that you restore the snapshot and change the version to 3.06.0 to resolve the issue. This issue has been fixed in this version.