Welcome toTencent Cloud + community, get more Tencent mass technology practice dry goods oh ~

This post was posted by horstxu on cloud + communities

1. Background

Db migration in the PHP Laravel framework is one of the more commonly used features. In addition to code changes, general database fields or database tables will also change during each release iteration. So when a new release is released, it is inevitable that changes to the database will be made in addition to the new code. Before the db migration function, our approach is to write the SQL statement to change the database TABLE (CREATE TABLE, ALTER TABLE, etc.) in an SQL file, and then connect to the database when online, the SQL statement is executed again.

A major disadvantage of this is that there is no version management of the database, and if the launch fails, the version needs to be rolled back, and the contents of the SQL file need to be reversed (DROP TABLE, DROP COLUMN, etc.). This approach is also relatively primitive, in Web development, we always want to avoid the development of raw SQL directly to operate on the database, the risk of error is high, and there is a high probability of irreversible errors, every operation must be on the edge of your mind.

As a result, the PHP Laravel framework provides db migration capabilities to manage databases with code. Refer to the link

2. Problem description

In a new version, I log my database changes as follows

php artisan make:migration db_migration_for_new_version
Copy the code

This creates a new PHP file in the project’s Database/Migrations directory that fills in the database contents to be changed itself

public function up {
    Schema::create('a_new_table'.function(Blueprint $table) {
        $table->bigIncrements('id');
    });

    Schema::create('another_new_table'.function(Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('user'.64) - >default(0)->comment('Username');

        // Here is an example of an error
        throw new \Exception("Error occurred");
    });
}
Copy the code

In the example above, MY intention was to create two tables. However, after the first table was created, the second table failed due to an error. According to the normal process, I should execute the following instructions to create the table when I go online

php artisan migrate
Copy the code

Since the second table failed to be created, the command above must have reported an error. But what should you do after reporting an error? The first thing, of course, is to fix the errors in the code, and then what do you do? The first table in the database has been created, but the second table has not been created. If you execute PHP Artisan Migrate again, an error will be reported: your first table has been created, and you cannot create tables again. You might feel like, I need to rollback once, so you might want to do a rollback PHP artisan migrate:rollback –step=1. Do not roll back at this time!!

Because the first migration failed, the database did not generate a new version number. If you roll back, you will be rolling back database operations that were performed during the last release, rather than the one you just performed, which could be disastrous and result in data loss. For details about the latest version of the database, refer to the Batch field of the Migrations table in the database (this table is automatically generated and managed by the Laravel Migration function, not a service table).

To summarize the problem: A db migration error occurs in the middle of the migration process. At this point, the database can only be manually operated to roll back the operation that has already been performed. The artisan command cannot be used to roll back the operation

3. Why is there no solution?

There are plenty of people on GitHub and StackOverflow who have already run into this problem, and the answers are pessimistic.

Everyone’s first reaction is: Can I start a transaction? Is it ok to treat all operations of a migration as a whole and either all succeed or all fail? Unfortunately, transaction operations are not supported. In mysql, transactions can only be performed for regular operations such as update, INSERT, and delete, whereas Data Definition Language (DDL) operations are performed for migration. The CREATE TABLE and ALTER TABLE operations cannot be rolled back, even if transactions are enabled (see link). Placing DDL operations in a Transaction results in the Transaction being committed automatically (see link), which is often not the desired result of our code logic.

4. What should I do then?

If you are already running into this problem, you have no choice but to manually see what has changed in the database one by one and then do the reverse yourself.

Only a few ways can be thought of to prevent the problem. GitHub developers recommend that each CREATE TABLE and ALTER TABLE operation be a separate migration. That is, each migration creates only one table, or changes only one table structure, and performs only one operation (see link)…

Another option is to put all of your table creation and modification operations in a try catch structure, and call the Down function in the migration file to roll back the operation if an error occurs. However, note the compatibility between up and Down. For example, the ADD COLUMN operation is performed in up and the DROP COLUMN operation is performed in Down. If an error occurs before the ADD COLLUMN operation is executed, the DROP COLUMN in the down function may also be reported as an error that the COLUMN does not exist.

In short, there is no perfect solution to this problem, which can be called a deep hole, especially pay attention to the rollback operation do not mess around, do not make up for a hole, dig a bigger hole for yourself.

Question and answer

PHP feature abuse?

reading

Figure out ASCII, GB2312, GBK, GB18030 code

You don’t have to know the CSV file format

Machine learning in action! Quick introduction to online advertising business and CTR knowledge

This article has been authorized by the author to Tencent Cloud + community, more original text pleaseClick on the

Search concern public number “cloud plus community”, the first time to obtain technical dry goods, after concern reply 1024 send you a technical course gift package!

Massive technical practice experience, all in the cloud plus community!