1062 Duplicate entry '2147483647' for key 'pim_catalog_completeness.PRIMARY'

Question

As a result of frequent execution of pim:completeness:calculate command, we can face the next exception:

An exception occurred while executing ' INSERT INTO pim_catalog_completeness (locale_id, channel_id, product_id, missing_count, required_count) VALUES (?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?)' with params ["58", "1", 52597, 0, 21, "58", "1", 52599, 0, 21, "58", "1", 52598, 1, 21]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2147483647' for key 'pim_catalog_completeness.PRIMARY'

Answer 1

Having in mind that 2147483647 is MAX_INT, we understand that table reached the primary key boundary (as constantly removing and then adding fresh completeness records increased the primary key to MAX_INT).

To fix an issue in this particular case - we have to truncate the table. It is safe as pim_catalog_completeness table has only generated information that can be regenerated after truncate.

To not have this issue in the future - we can add the next cron command (paths valid for bitnami akeneo default installation):

# Truncate pim_catalog_completeness at 00:05 every 1st day of month to not reach MAX_INT & recalculate completeness
5   0 1 * *  daemon   /opt/bitnami/mysql/bin/mysql -e "truncate bitnami_akeneo.pim_catalog_completeness" && /opt/bitnami/php/bin/php /opt/bitnami/apps/akeneo/htdocs/bin/console pim:completeness:calculate --env=prod

After truncating - IDs starts from 1 again & exception gone:

Answer 2

Just make your ID column to be a BIG INT:

ALTER TABLE pim_catalog_completeness MODIFY COLUMN id BIGINT AUTO_INCREMENT;