2
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

enter image description here

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

enter image description here

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

enter image description here

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:

enter image description here


You can see the creditmemo_2 table is there:

enter image description here


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:

enter image description here

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

4
  • oh boy, I was in the same situation, with every fix a new issue appears. Inconsistency's in ID's, types, relationships, you fix one thing, later on another thing breaks. It can be fixed but it's a real challenge with the risk of future unknown instabilities. My best advice is to ensure the migration doesn't break M2 table logic. Trying to fix it like you do only caused different problems for me later on. It was going to be a headache to maintain so together with the client we decided to start fresh, only migrate what's absolutely necessary. Quality was more important. good luck
    – FROSIT
    Commented Nov 19, 2021 at 7:36
  • @FROSIT Thank you for your comment. I think you are right, however, going back and doing it all again is not really an option for us. I'm thinking in the absolute worse case scenario, I would just create a new website, store and store front and set up the design, connect to the existing categories and add the products to the website en masse. That should work, right? I mean the test website, store, store front I created on the new Magento 2 seems to work I think (even the tables are created correctly by Magento 2). I would prefer some kind of a fix though as it is only the SHIP not working.
    – Allysin
    Commented Nov 19, 2021 at 10:03
  • @FROSIT Would you look at the last screenshot in my post and would that be part of the problem you think? I created these tables by myself and I think I've done it incorrectly that's why looking at the screenshot it appears to not be matching up?
    – Allysin
    Commented Nov 19, 2021 at 10:05
  • Worst case scenario, yes, this could make things workable. We took this approach during development to get to a working product. Most things worked, but we had really strange behaviour and didn't feel like releasing something we rather not maintain. When you decide this approach, make sure you test extensively after setting up. It worked for us at first but we had many scope related issues, did a lot of fixes on the database as well. That could end up bad so backup backup backup if you are uncertain of your questions.
    – FROSIT
    Commented Nov 21, 2021 at 2:40

1 Answer 1

0

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

3
  • Would you know what SQL commands I need to put in to try to correct the table structure (for points 1 and 2 in your post)? I believe the tables defined in the sequence_table column exist and are present in the sales_sequence_profile table. I am really in a lot of trouble here as customers are complaining etc.
    – Allysin
    Commented Nov 22, 2021 at 15:20
  • I think you can just point and double click the cell that has "mget_sequence_invoice_2" and change it to "mget_sequence_shipment_2" (at least for point 2). The first one i can't tell you.
    – FROSIT
    Commented Nov 23, 2021 at 22:59
  • If you can't get this right i think you're better off using the plugin. revert what you did or remove the things like i mentioned and use the plugin to fix everything. It's difficult to help you since I don't have your database here to try things.
    – FROSIT
    Commented Nov 23, 2021 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.