2

I was trying to compress few tables, but one of them caused this error

alter table MY_table row_format=compressed, algorithm=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec)
ERROR 1062 (23000): Duplicate entry '740003820' for key 'PRIMARY

Then tried same with

alter table MY_table row_format=compressed;

Got same error 1062 Duplicate entry.

According to DOC https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

What are my options?

zeridon
  • 760
  • 3
  • 6
DBAd
  • 21
  • 2
  • I'd advise to either isolate this in transaction so you compressing the table are the only one modifying it or enabling the `LOCK` – zeridon Apr 14 '16 at 13:37
  • FYI. We chose to stop replication and run the alter, we did it one server at time... – DBAd Apr 22 '16 at 21:41

0 Answers0