Steps to migrate Shopware 6 database from MariaDB to MySQL?

Recently I needed to import the live database of Shopware 6 for a project. But the live database was Mariadb. I had to make some product-related changes in that database. I encountered several problems importing the database in my local system which had only mysql as the database service. I will discuss this in detail in this blog post.

Step 1– I logged in to the SSH and used the mysqldump command to export the database from a server which was in Mariadb.

mysqldump -u username -p -h hostname databasename > filename.sql

Step 2–  After the export was completed I downloaded the exported SQL file into my local system through ftp.

Step 3– I then created a new database named shopware_live in my local Mysql. Then tried to import the exported database using the following command

mysql -u root shopware_live < pathtofile.sql

On doing this “Variable ‘sql_mode’ can’t be set to the value of ‘NO_AUTO_CREATE_USER‘ ”error was shown. To solve this problem I edited the sql file and removed the  ‘NO_AUTO_CREATE_USER’ statement.

Then again, when trying to import the database I got the “Invalid default value for ‘cms_page_version_id’” error. The issue was the ‘cms_page_version_id’ default value was in an unknown format like ‘���jK¾K��u,4%’

Step 1- To solve this problem I replaced that with ‘???jK¾K??u,4%’

Step 2- Also, replace the default value of version_id with the same value

document_number` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`config`,'$.documentNumber'))) STORED

Then again, when trying to import the database I got “The value specified for generated column

document_number' in table 'document' is not allowed” error.

This issue is mainly due to the generated columns.

So what I did was to replace the GENERATED keyword and the values after that with NULL
`document_number` varchar(255) NULL,

This issue occurred for the following columns. So I needed to do the above for these as well.

In ‘order’ table -> order_date, amount_total, amount_net, position_price, tax_status, shipping_total
In ‘order_delivery_position’ table -> total_price, unit_price and quantity

In ‘order_line_item’ table -> unit_price, total_price

In ‘product_keyword_dictionary’ table -> reversed

In ‘state_machine_history’ table -> referenced_id and referenced_version_id

Conclusion

Now I was able to import the database without any errors and was able to work on the project in my local. By addressing these issues systematically, the database import process can be completed, allowing for a seamless transition from a MariaDB environment to a MySQL setup. This approach ensures that the Shopware 6 database functions correctly in the local MySQL system, enabling developers to work on their projects without encountering unexpected errors. If you face any difficulties with executing any steps with Shopware 6 feel free to contact expert Shopware Services.