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'
CodePudding user response:
Just make your ID column to be a BIG INT:
ALTER TABLE pim_catalog_completeness MODIFY COLUMN id BIGINT AUTO_INCREMENT;
CodePudding user response:
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: