Commerce and split databases

[Spaggel]

Hi,
we are using the Magento Commerce with split databases. In a split DB setup the sales (orders), quotes and catalog are split up.

We noticed a non working foreign key on the mp_quote_conversion table. The conversion_quote FK references the quote table which is in another database (quote DB) and does not work with split databases.

A problem might also occur with the extension attributes from quote and sales to the quote_conversion table.

Could you tell us if this is a known issue and if it's going to be fixed?

[PaulBoss]

Hi,

Thanks for the information. We have only tested our extension on the community edition of Magento 2. So a split database has not been tested. The foreign keys can simply be removed. The code accessing mp_quote_conversion probable should be updated as well. At this time we do not have access to the Enterprise edition so a fix on a short term will not be released. If you have any changes done yourselves we are happy to accept a merge request or a patch. If you would be willing to test a development version of the plugin we could provide a version without the foreign key contraints and the code changes for accessing the quotes table but this is not on our roadmap at this time since this is the first time we've encountered this issue.

[BasBlanken]

With "code accessing the mp_quote_conversion table" do you mean the ExtendedOrderRepository and related files/logic?
As the core OrderRepository calls extensionAttributesJoinProcessor->process on the searchResult already.

In my opinion using automated joins through extension_attributes would be the proper way to do it, it's just not an option if the mp_quote_conversion table is situated in the "checkout" database (imo the logical place if using split DB).

By "The foreign keys can simply be removed.", do you mean after the logic for getting the mailplus_id on the orders is in place, or that the calls made to "/V1/mailplus/orders" don't even make use of this field?

---

If we are to make the required changes (possibly next sprint/week), is it possible to confirm we are on the right track with the suggestions emailed last week?

In short:

• mp_quote_conversion table creation using "checkout" connection, resulting in it being created in the checkout db when using split db
• saving the mailplus_id in a column of the sales_order table, it being "archive" data (mailplus_id at time of order placement)

If there is anything else we should know before we start we of-course would be glad to hear!

[Bbbrinks]

As the core OrderRepository calls extensionAttributesJoinProcessor->process on the searchResult already.

This is true but was only recently fixed. magento/magento2#8035.
And not all our clients are fast with updating their Magento so we'd like to keep that 'fix' in there.

mp_quote_conversion table creation using "checkout" connection, resulting in it being created in the checkout db when using split db

If this means there are no other changes required for this part of the extension, and it will work the same on non-split db setups this would be the way to go.

saving the mailplus_id in a column of the sales_order table, it being "archive" data (mailplus_id at time of order placement)

I think that should work just fine but I think Magento recommends not to change their 'core tables'. Would it not be possible to create another table similar (equal) to mp_quote_conversion on the using the same connection as the sales_order table?
Either way is ok with me but I think a separate table would be more clean.

[BasBlanken]

Either way is ok with me but I think a separate table would be more clean.

Agreed, and definitely possible, database would have to be situated in the sales one as you suggested.
An observer or plugin could save the mailplus_id at order placement.

I have tested with adding the connection argument to the getConnection function without problems on OpenSource and there are more ways available to be able to make it work on both OpenSource and Commerce in the same module.

[Bbbrinks]

Great. Will you guys be making a pull request with these changes?