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: 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
<column xsi:type="int" name="operation_key" padding="10" unsigned="true" nullable="false"
<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:
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.