'Unique constraint violation found' - when updating existing orders

Question

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:

SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '', query was: INSERT INTO `` () VALUES ()

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.

---

UPDATED on November 18th, 2021:

I have previously had to create missing tables in my database and by the look of things I have probably messed this up - could the below be causing this problem?

Please note - store 0 is the default and store 1 is the main website store and both work fine with updating the status of the orders. Store_ID 2 is the 2nd website = the one having this problem and in here:

• it has entity_type = creditmemo missing
• it only as 3 items and it looks like they are incorrectly matched?

entity_type - shipment = mget_sequence_invoice_2 entity_type - order = mget_sequence_order_2 entity type - invoice = mget_sequence_invoice_2

Also, store 6 was created by Magento 2 and it has all 4 (shipment, creditmemo, invoice, order) and correctly matched!

---

UPDATED on November 23rd, 2021:

I have done what FROSIT suggested = double clicked the cell that has "mget_sequence_invoice_2" and changed it to "mget_sequence_shipment_2".

This did not, however, resolve the issue.

It now shows what I would think is correct but still missing the mget_sequence_creditmemo_2 as can be seen here:

---

You can see the creditmemo_2 table is there:

---

I ran the following:

INSERT INTO `mget_sales_sequence_meta` (`meta_id`, `entity_type`, `store_id`, `sequence_table`) VALUES
(NULL, 'creditmemo', 2, 'mget_sequence_creditmemo_2');

And this seemed to add the creditmemo_2 association in:

But I still get the same Error after going to update the Ship status.

Answer 1

Based on your last screenshot, yes. Your table structure is incorrect.

1. You are missing a type on store 2 (creditmemo).
2. The row with meta_id 31 probably needs the sequence_table column changed to "mget_sequence_shipment_2" instead of invoice.

You also would need to make sure the tables defined in the sequence_table column exist and possible also be present in the sales_sequence_profile table (not sure).

If this confuses you, there seems to be a known problem with Magento not fully creating these sequence tables. Magento should create them automatically when you create a new website/store. That's why the new store you created has those tables.

If you can't get it fixed using MySQL queries and my tips above, there are different solutions. Easiest would be to have Magento create those tables like it should when you create a new store, but since the store already exist, we need a solution to trigger this process of creating tables manually. I found a module on Github that does just that. Be aware that this module was not build or tested for your Magento version, the code looks okay though. Worth a try.

https://github.com/joeshelton-wagento/fixsalessequence

Looking at the code, It won't work for existing tables. You might want to revert the changes you made. That means checking sales_sequence_meta, write down the meta_id for every row that has store_id = 2. Then go to sales_sequence_profile and remove every row that has a meta_id you just wrote down. After this, you should not see any row with store_id 2 in the sales_sequence_meta table. If you did that, remove any table starting with sequence_* and ending with _2. All tables should be cleared, and when you run the command from the Github repository bin/magento wagento:fixsalessequence 2 the tables should be created and your problem solved.

If you run into trouble, post the output you get and also the contents of the sales_sequence_meta, sales_sequence_profileen what tables are present in your database starting withsales_sequence_*`. so we could try to debug it.

Good luck