Moving a Symfony app using Doctrine ORM from PostgreSQL to MySQL
Doctrine ORM is an Object Relational Mapper for PHP. It handles the heavy lifting of creating database tables, creating queries and so on. This is done using an abstraction based on configuration where you annotate your data objects (Entities).
The pros and cons of using an ORM and if you should use them are almost a religious topic. What is a fact, is that because the Symfony framework has good integration with Doctrine ORM - there are many apps using Doctrine with Symfony successfully.
Potential complexity in database migrations
I am running a handful of online services mostly for fun (including this one). Most run on MySQL (or MariaDB), but I have (had?) one app running on Postgres because I wanted to learn about it. After upgrading my MySQL to 8.0 (using the official APT repository) I figured I might as well move that one app over to MySQL as well.
Switching databases is possible with Postgres (both ORM and the underlying Doctrine DBAL), but documentation is not great (maybe because YMMV like crazy). Worth nothing that there is also the Doctrine migrations project, but that is more for versioning a schema - not migrating data from one database to another.
Moving databases from one platform to another is not always trivial as you might be using features that are unique to a RDBMS. Emerging features JSON fields for unstructured data seem to vary quite a bit (See MySQL 8.0 and PostgreSQL 12.0 JSON documentation). There is no standard or specification so vendors give it their best shot. Hopefully abstractions will edge these out going forward.
Migrating from PostgreSQL to MySQL
So yeah, migrating from one database to another might not be easy and I'm certainly not a DBA. However, I had done a similar exercise so, but the other way around (moving eZ Platform from MySQL to PostgreSQL), so tools like nmig that only focused on moving from pgsql to mysql did not help.
NOTE: Only after the conversion I noticed there is also an interesting project for database migrations using the Doctrine DBAL underneath: Fregata
But don't let me scare you - I'm sure for many cases where you don't use exotic features migrating from PostgreSQL to MySQL is not hard. And the RDBMS itself does help you manage the complexity because they are very good at maintaining data integrity against a schema. (Who would have guessed, right?)
For my humdrum Symfony app using Doctrine ORM the process had these steps:
- Create schema in MySQL using Doctrine
- Export data only from PostgreSQL
- Convert pgsql dump to mysql format
- Import dump to MySQL database
- Validate validate validate
Step 1 was drop-dead simple. Configure your app to use a MySQL database and run
./bin/console doctrine:database:create
This creates a clean schema to import data into. Next step is to dump the data. For this I used the recommended pg_dump options from the pg2mysql script that I used:
PGPASSWORD=yourpass pg_dump -h localhost --quote-all-identifiers \
--no-acl --no-owner --format p --data-only dbname -f pgfile.sql
As a result I had the raw data as SQL queries from PostgreSQL in pgfile.sql. There are some SQL dialect differences between the two databases, so I needed to convert the dump. For this I used a fork of pg2mysql that handles conversion somewhat robustly:
php pg2mysql_cli.php pgfile.sql mysqlfile.sql
For me pg2mysql worked great and without errors. It does require quite a bit of RAM, so you might want to do the conversion on your local development machine instead of a resource constrained server environment. As a baseline, I needed to set the memory limit to 4GB for a 1.7GB DB dump by modifying the script ever so slightly.
With this out of the way you've got a compatible dump file (assuming everything went ok) and a clean database with a valid schema. I usually log in and use the source command to achieve this. I like that it's a bit more interactive over piping in a SQL file.
janit@Reggan ~ % mysql -uroot -p shiny_app Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 530 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source mysqlfile.sql
This is the hard part. You might get errors and whatnot, depending on how well pg2mysql worked its magic on your pgsql dump. But if you keep grinding through the errors, fixing them one by one the end result should be a valid database whose structure is verified by the schema created by Doctrine ORM.
Finally you should to do thorough validation and testing to make sure your app does not break after switching databases. For me this process is still ongoing (and might be for days, weeks or months, depending if I feel like doing it), but so far so good.
The only noticeable issue I have found is the slowdown of certain controller views and REST API calls in my app due to large JSON datablobs (1GB+) and how they seem to degrade count performance in MySQL compared to PostgreSQL.