Issue:
Updating product attributes in bulk never seems to complete successfully. Regardless of whether it does or not, the messages remain in the queue. There are a number of scenarios under which this could happen. This article only relates to the Integrity constraint violation aspect, where ALL of the following issues are occurring:
- Updating product attributes in bulk results in "Pending, in queue" messages in the Bulk Operations Log and a status of "Not Started"
- The "Dismiss All Completed Tasks" link no longer works.
- The following error can also be seen in var/log/debug.log:
main.CRITICAL: Message has been rejected: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'operation_key' cannot be null, query was: INSERT INTO `magento_operation` (`id`, `bulk_uuid`, `topic_name`, `serialized_data`, `result_serialized_data`, `status`, `error_code`, `result_message`, `operation_key`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) [] []
This seems to be a known issue introduced in Magento 2.4.0 with a fix proposed for 2.4.2.
This can be addressed with a workaround, but before going any further, check that your cron is actually setup and running properly, and that your attribute updates are queued:
Run cron manually:
$ php bin/magento cron:run
Start message queue consumers:
$ php bin/magento queue:consumer:start product_action_attribute.update
Fix / Workaround
-
Update the magento_operation table to make it nullable:
Test by updating the database schema with following SQL:
ALTER TABLE `magento_operation` MODIFY COLUMN `operation_key` int(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Operation Key' AFTER `result_message`;
This will allow for NULL values to be saved in the operation_key column. Now test by updating a couple of products to see if the updates now complete successfully. The status should show "Finished Successfully".
-
Update the database schema
In order to make the above change permanent (well at least until the code fix is provided), we need to update db_schema.xml as a temporary workaround.
- update db_schema.xml using your favourite editor, for example:
vim ./vendor/magento/module-asynchronous-operations/etc/db_schema.xml +37
Change:
<column xsi:type="int" name="operation_key" padding="10" unsigned="true" nullable="false"
To:
<column xsi:type="int" name="operation_key" padding="10" unsigned="true" nullable="true"
- Perform any necessary db updates:
$ php bin/magento setup:upgrade
-
Clear out any pending messages
Since the magento_operations table wasn't updated correctly, you will need to manually update the records in the magento_operation, magento_acknowledged_bulk and magento_bulk tables.
There are a number of different ways to do that, but the following should work:
TRUNCATE magento_operation; TRUNCATE magento_acknowledged_bulk; DELETE FROM magento_bulk; ALTER TABLE magento_bulk AUTO_INCREMENT = 1;
Links / References:
- https://github.com/magento/magento2/issues/23958
- https://github.com/magento/magento2/issues/29797
- https://github.com/magento/magento2/pull/29814/files
If you are a Create Hosting customer utilising our MageVPS Plan, feel free to submit a support ticket and we can investigate and take care of this issue for you.