Symfony – migrating tables to production

I will describe the installation of local migration to the server. Some nuances make you spend a lot of time. Knowing them, this can be avoided.

We presume that the database connection has already been configured. And the migration tables and everything else is uploaded to production.

Stage 1 – Setup

In the console, we look at the current status of what will be changed on the prod:

bin / console doctrine: schema: update –dump-sql

The information will be displayed in sql syntax, for example:

CREATE TABLE cars (id INT AUTO_INCREMENT NOT NULL, date DATETIME NOT NULL, name LONGTEXT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL, email VARCHAR (255) NOT NULL, username VARCHAR (255) NOT NULL, password VARCHAR (64) NOT NULL, UNIQUE INDEX UNIQ_38329DSDFMEKS (email), UNIQUE INDEX UNIQ_343DYMK, KEY, PRILE (id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

Before continuing, go to the file /config/packages/doctrine.yaml and add the version of MySQL. For example, like this:

doctrine:
dbal:
url: ‘% env (DATABASE_URL)%’
server_version: ‘5.4’
charset: utf8
default_table_options:
charset: utf8
collate: utf8_unicode_ci

If you do not specify the version of MySQL, then further migration may result in errors:

If connecting to the database via .env, then the version of MySQL is indicated in the last parameter.

Updating database schema …

In AbstractMySQLDriver.php line 106:

An exception occurred while executing ‘CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL, email VARCHAR (255) NOT NULL, username VARCHAR (255) NOT NULL, password VARCHAR (64) NOT NULL, UNIQUE INDEX UNIQ_38329DSDFMEKS (email), UNIQUE349DEXKDEXQDQQDEXQ (username), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB ‘:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

In PDOConnection.php line 80:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

In PDOConnection.php line 75:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

This is due to the fact that the old version of MySQL does not have some support for new features. And if you do not explicitly indicate the version of MySQL on production, then similar errors may occur.

Stage 2 – Verification

Run the migration check:

bin / console doctrine: schema: validate

If the migration is not made, then it will be indicated as follows:

[ERROR] The database schema is not in sync with the current mapping file.

Stage 3 – Migration

Everything is ok here, so we start the migration:

bin / console doctrine: schema: update –force

Use attribute force otherwise, on prod, the command does not start the process and a warning appears:

! [CAUTION] This operation should not be executed in a production environment!
!
!
!
! Use the incremental update to detect changes during development and use
!
! the SQL DDL provided to manually update your database in production.
!

There may be other migration options, but this is the easiest. If you first look at what migrates in the presence of backups, then this procedure does not pose serious concern.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *