Magento 2.4.2-p1
Smartwave Porto theme
Amasty extensions
We migrated from Magento 1.9.4 using the Magento Migration Tool. We had problems with SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name on additional websites that was resolved by me creating the tables for the additional websites as documented here:
Now we are getting the following Error come up in different places including when trying to update orders. This only happens in the 2nd website/store/storefront (it does not happen on the main website and we don’t have the 3rd store up yet):
Unique constraint violation found.
Here are the details from my system.log:
[2021-11-17 15:38:27] main.WARNING: Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size. Please update innodb_buffer_pool_size or decrease batch size value (which decreases memory usages for the temporary table). Current batch size: 100000; Allocated memory size: 1400000000 bytes; InnoDB buffer pool size: 1073741824 bytes. [] []
[2021-11-17 15:39:03] main.INFO: Consumer "async.operations.all" skipped as required connection "amqp" is not configured. Unknown connection name amqp [] []
Here is my exception.log:
[2021-11-17 12:53:43] main.CRITICAL: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: UPDATE `mget_quote_address` SET `collect_shipping_rates` = ?, `weight` = ?, `subtotal` = ?, `base_subtotal` = ?, `subtotal_with_discount` = ?, `base_subtotal_with_discount` = ?, `tax_amount` = ?, `base_tax_amount` = ?, `shipping_amount` = ?, `base_shipping_amount` = ?, `shipping_tax_amount` = ?, `base_shipping_tax_amount` = ?, `discount_amount` = ?, `base_discount_amount` = ?, `grand_total` = ?, `base_grand_total` = ?, `discount_description` = ?, `shipping_discount_amount` = ?, `base_shipping_discount_amount` = ?, `subtotal_incl_tax` = ?, `base_subtotal_total_incl_tax` = ?, `discount_tax_compensation_amount` = ?, `base_discount_tax_compensation_amount` = ?, `shipping_discount_tax_compensation_amount` = ?, `shipping_incl_tax` = ?, `base_shipping_incl_tax` = ?, `free_shipping` = ? WHERE (address_id=36926) {"exception":"[object] (Magento\Framework\DB\Adapter\DeadlockException(code: 1213): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: UPDATE `mget_quote_address` SET `collect_shipping_rates` = ?, `weight` = ?, `subtotal` = ?, `base_subtotal` = ?, `subtotal_with_discount` = ?, `base_subtotal_with_discount` = ?, `tax_amount` = ?, `base_tax_amount` = ?, `shipping_amount` = ?, `base_shipping_amount` = ?, `shipping_tax_amount` = ?, `base_shipping_tax_amount` = ?, `discount_amount` = ?, `base_discount_amount` = ?, `grand_total` = ?, `base_grand_total` = ?, `discount_description` = ?, `shipping_discount_amount` = ?, `base_shipping_discount_amount` = ?, `subtotal_incl_tax` = ?, `base_subtotal_total_incl_tax` = ?, `discount_tax_compensation_amount` = ?, `base_discount_tax_compensation_amount` = ?, `shipping_discount_tax_compensation_amount` = ?, `shipping_incl_tax` = ?, `base_shipping_incl_tax` = ?, `free_shipping` = ? WHERE (address_id=36926) at /home/OURACCOUNT/public_html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:599, Zend_Db_Statement_Exception(code: 40001): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: UPDATE `mget_quote_address` SET `collect_shipping_rates` = ?, `weight` = ?, `subtotal` = ?, `base_subtotal` = ?, `subtotal_with_discount` = ?, `base_subtotal_with_discount` = ?, `tax_amount` = ?, `base_tax_amount` = ?, `shipping_amount` = ?, `base_shipping_amount` = ?, `shipping_tax_amount` = ?, `base_shipping_tax_amount` = ?, `discount_amount` = ?, `base_discount_amount` = ?, `grand_total` = ?, `base_grand_total` = ?, `discount_description` = ?, `shipping_discount_amount` = ?, `base_shipping_discount_amount` = ?, `subtotal_incl_tax` = ?, `base_subtotal_total_incl_tax` = ?, `discount_tax_compensation_amount` = ?, `base_discount_tax_compensation_amount` = ?, `shipping_discount_tax_compensation_amount` = ?, `shipping_incl_tax` = ?, `base_shipping_incl_tax` = ?, `free_shipping` = ? WHERE (address_id=36926) at /home/OURACCOUNT/public_html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:110, PDOException(code: 40001): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction at /home/OURACCOUNT/public_html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:91)"} []
I have googled this and found that the ‘AUTO_INCREMENT could be set wrong for the table’.
I ran the following SQL in my phpMyAdmin in mget_sequence_order_0:
SELECT
`AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'denverga_mage341'
AND TABLE_NAME = 'mget_sequence_order_0';
And I get the following reply:
AUTO_INCREMENT
1
When I run the following SQL for mget_sequence_order_1:
SELECT
`AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'denverga_mage341'
AND TABLE_NAME = 'mget_sequence_order_1';
It comes back with:
AUTO_INCREMENT
100010756
Similarly, for mget_sequence_order_2, it shows:
AUTO_INCREMENT
33
What does this mean and how can I fix this? Simple step-by-step would be much appreciated.
