Magento is one of the leading eCommerce platforms with rapidly growing popularity in the international market. The key reason behind it is the continuous support and regular updates. We currently have two versions of Magento namely Magento 1 and Magento 2. The latter is the latest version with the most number of users. Magento 2 has a huge database with a number of tables containing data stored related to several key areas of the website. Hence, merging two Magento 2 websites/stores is quite tricky. There are a few problem statements in which data merging is required. We will look at these problems, their solutions, and the complete process of merging two separate Magento 2 databases.
Problem Statements
Data Merging refers to transferring data from one store/website to another through the database. There are a few cases in which you would want to perform merging two databases in Magento 2. Let us look at an example:-
You have different instances of Magento running various stores for eg:- one of your domains have stores for UK and US and another domain has stores for AU and CA. There are two servers where these sites are hosted containing separate files.
www.domain1.com – Installed on database 1- with stores UK and US
www.domain2.com– Installed on database 2- with stores AU and CA
Having two domains create the following problems:-
- Need to maintain two separate databases one for each store.
- Both domains are hosted on different hosting services which costs way more than having a single domain
- Maintenance costs would be higher in the case of maintaining two sites instead of one
- Any change required would need to be done twice once for each store
You want to merge both stores to be hosted on a single domain which requires transferring and merging all the data from one store to the other. The data transfer would include transferring all plugin data, catalog data, order data, and more. This would be a complicated process because both databases will have pretty much the same tables with same primary keys eg:- Entity IDs and joining them simply won’t be enough.
Challenges
There are several challenges to be faced in the entire process of merging two Magento 2 databases. First of all, Magento has a huge database depending on number of stores the size can be even bigger. The data is stored in a number of tables that need to migrate during the process. Some of the tables can have some same or conflicting primary/foreign keys. Resolving these conflicts with so many tables seems quite difficult and time-consuming. We as store owners first decide which data we want to migrate. The examples of some of the data stored in the tables that are important during merging are as follows:-
- Admin Users
- Category
- Stock
- Products
- Customer
- Sales Orders
- Sales Invoices
- Sales Shipment
- Sales Credit Memo
- Quote
- Quote Item
- Wishlist
- Wishlist Item
- Sales Rule
- Newsletter
- Reviews
Note:- Some data can be imported and exported using out of the box Magento import/export feature which can be exempted from data merging in Magento to increase the efficiency of the entire process. For eg:- Catalog product data, customer data, etc. You can go to System>Import/Export to retrieve or upload this data.
There are many more tables that you want to assess before starting the merging process.
Now let us take a look at an example of challenges faced during transferring data from one table to another.
Example:- Let us say you have two websites one running on Magento 2. You want to shift the first Magento 2 site to the second Magento 2 site so that both sites are hosted on the same domain with all the data from the previous store. The first store has two stores US and UK whereas the second Magento 2 store has 3 stores of DE, CA, and AU. One would simply assume that restoring the tables from 1st store to the second store. However, there are issues that will create conflicts if we try to restore the database directly. Let us look at an example of orders.
Magento 2 First Instance
ORDERS
US
Entity ID 1 / Increment ID 100000009
Entity ID 4/ Increment ID 100000007
UK
Entity ID 3/ Increment ID 200000005
Entity ID 6/ Increment ID 200000001
Entity ID 5/ Increment ID 200000006
CUSTOMERS
US
Entity ID 1
Entity ID 4
UK
Entity ID 2
Entity ID 3
Magento 2 Second Instance
ORDERS
DE
Entity ID 1/ Increment ID 300000004
CA
Entity ID 3/ Increment ID 400000007
AU
Entity ID 5/ Increment ID 500000002
CUSTOMERS
DE
Entity ID 1
Entity ID 4
Entity ID 5
CA
Entity ID 2
AU
Entity ID 3
In the above scenario, all the stores have their orders stored in the table sales_order. If we simply want to merge the two databases then we can see that the primary keys will conflict since both the tables have similar primary keys i.e 1, 3, and 5. Hence a simple merge won’t be possible in this case. The same problem applies to customer data as well because we can see same primary keys are used in both DBs i.e. 1,2,3,4. This problem will be there for most of the tables especially tables which have primary and foreign key relationship.
Lets analyse sales_order table which contains several foreign keys that would be same as in the sales_order table in the second Magento database. Foreign keys in this table are as follows:-
- customer_id
- quote_id
- billing_address_id
- shipping_address_id
These ids will be the same in both databases hence merging them would cause conflicts. Again, with the customer_entity table, the entity ids will be the same in both databases which are quite challenging to overcome.
All the other tables will have similar issues during the merging process. So what’s the solution how do we efficiently merge two tables to have all the data?
Solution
The solution is to create a mapping of the tables and remap the keys so that they can be efficiently migrated. As in the above scenario, we will remap the order data as follows:-
Magento 2 First Instance
ORDERS
US
Entity ID 1/ Increment ID 100000009
Entity ID 4/ Increment ID 100000007
UK
Entity ID 3/ Increment ID 200000005
Entity ID 2/ Increment ID 200000001
Entity ID 5/ Increment ID 200000006
Magento 2 Second Instance
ORDERS
DE
Entity ID 1/ Increment ID 300000004 –6
CA
Entity ID 5/ Increment ID 400000007 –8
AU
Entity ID 3/ Increment ID 500000002 –7
So the orders in the table are mapped as 6,7,8 so that they will not create conflicts during merging So we can merge these two tables easily. The mapping data is stored separately in a file for each table.
We will be creating a temporary mapping table that will contain entries as old_key and new_key for each of the foreign keys present in the table. As in the above scenario, the old key is the original entity ID i.e 1 and the new_key is the new entity ID will be 6. For eg:- In the sales_order table mapping is required for four foreign keys customer_id, quote_id, billing_address_id, shipping_address_id. Once mapping is done we will merge the database by removing the check for foreign keys. Then using the mapping file we will run update queries for each of the foreign keys. In the sales_order table, four update queries will run that will be replacing the old keys with the new keys storing all entries uniquely without any duplicates or conflicts.
Data Merging Process in Magento 2
Now we have looked at a few major challenges faced during data merging. We will now work through the entire process of merging two Magento 2 databases:-
STEP 1:- The first step is to do a deep analysis of the database tables which is done by the store owners in general. This is the most time-consuming process as it requires understanding each of the tables and their relations. Deep analysis is the most crucial step of data merging because the entire process is based on this analysis. For eg:- once the database relations are well understood then only we create the appropriate remappings of the tables required. An example of a deep analysis of tables is as shown below:-
| sales_order | ||
| column | description | relation |
| entity_id | replace value in child tables before insert into them (possible columns – order_id, parent_id, …) | |
| store_id | stays the same | |
| customer_id | update customer_id after customer migration (map it using email field) | customer_entity.entity_id |
| billing_address_id | update after migration of sales_order_address.entity_id | sales_order_address.entity_id |
| shipping_address_id | update after migration of sales_order_address.entity_id | sales_order_address.entity_id |
| quote_id | update as a part of quote migration (complex query to select old_order_id for initial quote_idand using sales_order_temp update quote_id for new_order_id) | |
| customer_group_id | update if customer_group has changed | |
| gift_message_id | update if gift messages are used, if not no actions required | |
| worldpay_subscription_id | update from table worldpay_subscriptions if used | worldpay_subscriptions.subscription_id |
| pos_device_order_id | update from ebizmarts tables if used and if those tables migrated | |
| pos_order_override_id | ||
| sales_order_payment | ||
| column | description | relation |
| parent_id | part of order migration, values are set from sales_order_temp | |
| quote_payment_id | NULL / Zero value (no update) | |
| last_trans_id | value from sales_payment_transaction.txn_id, provided by payment method (no update) | |
| cc_trans_id | value from sales_payment_transaction.txn_id, provided by payment method (no update) | |
| tokenbase_id | NULL value and highly possible provided by payment method |
In the above example, we can see the deep analysis performed for the two tables namely the sales_order and sales_order_payment tables. Here we have defined the columns existing in the table, the description ( also includes whether we need to update it during the process of merging two Magento 2 databases or not), and also the relation with the columns in other tables.
STEP 2:- Create the required remapping of the data in the tables. We create a PHP script to remap the data in the tables. Look at the example below which contains the remapping of the above two tables:-
<?php return [ '.sales_order' => [ 'primary_key' => 'entity_id', 'use_store' => true, 'remap' => [ 'customer_id' => '.customer_entity', 'quote_id' => '.quote', 'billing_address_id' => '.sales_order_address', 'shipping_address_id' => '.sales_order_address', ] ],
.sales_order_payment' => [ 'primary_key' => 'entity_id', 'use_store' => false, 'update_required' => false, 'parent_table' => '.sales_order', 'parent_key' => 'parent_id', 'temp_required' => true, 'remap' => [ 'parent_id' => '.sales_order' ] ],
];Similarly, create the remapping script for all the tables that need to be migrated then create the data merging scripts as usual to restore the data.
STEP 3:- Once you have performed the first two steps and all the scripts are ready, we would suggest you to create a staging site for the merging process. Restore the main site data on the staging site then perform the data merging on that site. After the merging, if everything is working then you can switch to the live site.
If the entire merging process seems overwhelming then our team can assist you in the process. We at Scommerce Mage have a seasoned team of developers expert in data merging two Magento 2 databases. If you want to merge two Magento 2 databases we can help you achieve that with ease. If you have any queries or want to consult us about Data Merging please Contact Us.
