I am in the process of upgrading our Magento 2.2.5 website to version 2.3. I have managed to do this and the website works fine, as far as I can tell.

However, we have a third party extension for a Payment Gateway that is throwing errors at me.

I have been in touch with the developer and they have informed me that recent changes to Magento 2.3 have meant that anyone with a Prefix on their db tables is getting unique constraint violation errors.

They have informed me that the fact Magento limit characters to 32, because of the prefix everything afterwards is getting truncated.

I find it very strange how it worked before upgrading to 2.3 and updating their extension but there you go.

They have told me that I need to remove the DB Table Prefix.

I have done this in PHPMyAdmin using the 'Replace Table Prefix' option and then removed the Prefix from 'app/etc/env.php'.

The frontend and admin seem to be loading, haven't performed extensive testing yet... however I have two issues that I hope some might be able to assist with.

1: There is a table in the database that I have not been able to remove the prefix for. The table is called 'PREFIX_inventory_stock_1' and it just says: "#1347 - .PREFIX_inventory_stock_1' is not a base table." When I try to replace the prefix with the 'Replace Table Prefix' option.

2: When I try to run php bin/magento setup:upgrade I get the following errors:

In Pdo.php line 235: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'multiple tri ggers with the same action time and event for one table', query was: CREATE TRIGGER trg_catalog_product_entity_ after_insert AFTER INSERT ON catalog_product_entity FOR EACH ROW BEGIN INSERT IGNORE INTO catalogrule_product_cl (entity_id) VALUES (NEW.entity_id); INSERT IGNORE INTO catalog_product_price_cl (entity_id) VALUES (NEW.entity_id); INSERT IGNORE INTO catalogsearch_fulltext_cl (entity_id) VALUES (NEW.entity_id); END

In Pdo.php line 228: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'multiple tri ggers with the same action time and event for one table'

I apologise in advance that I am not a developer with a great depth of experience, so if there is something extremely obvious here then I am sorry.

Although I would really appreciate if any kind soul out there could point me in the right direction.

Thanks in advance :)

EDIT ---------------------

I have discovered another issue where all products have now vanished from the frontend of the website. I have checked in admin and all product information appears correct... note they appeared fine before removing the PREFIX.

  • seems you may have a few things going on here. in terms of the setup:upgrade error, what version of MySQL are you running? for the problem renaming prefix_inventory_stock_1 this is a view not a table and will be recreated by Magento when you next re-index. i believe you can drop that view altogether. – BlueC Feb 15 at 11:55
  • Thank you for the response, PHPMyAdmin says 5.6.41 - MySQL Community Server... The extension developers did give me a second option, which was that they would manually make changes to our Magento code to change the 32 character restriction. Although we would then have to have them do this EVERY time we need to update. You wouldn't happen to know if this would be more straight forward and how to do it? – user73594 Feb 15 at 12:27
  • Ok so it seems your MySQL version is fine. You will need to find which specific module is causing that setup:upgrade error and then troubleshoot from there. The frontend showing no products is because you need to successfully run setup:upgrade and then successfully reindex to recreate your index tables/views. – BlueC Feb 15 at 12:46
  • That is good to hear about the products, thank you for clarifying that! Very good to know. In regards to my extensions the only enabled ones are Magento supplied, we actually only have 2 third party extensions. Would the fact that they are installed but disabled still cause issues, as in would I have to completely uninstall them? – user73594 Feb 15 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.