Laravel 迁移事务

Laravel migration transaction

提问by olivarra1

When developing i'm having so many issues with migrations in laravel.

在开发时，我在 Laravel 中遇到了很多迁移问题。

I create a migration. When i finish creating it, there's a small error by the middle of the migration (say, a foreign key constraint) that makes "php artisan migrate" fail. He tells me where the error is, indeed, but then migrate gets to an unconsistent state, where all the modifications to the database made before the error are made, and not the next ones.

我创建了一个迁移。当我完成创建它时，在迁移过程中出现了一个小错误（比如外键约束），导致“php artisan migrate”失败。他确实告诉我错误在哪里，但随后 migrate 进入不一致状态，在该状态下，在发生错误之前对数据库所做的所有修改都发生了，而不是接下来的修改。

This makes that when I fix the error and re-run migrate, the first statement fails, as the column/table is already created/modified. Then the only solution I know is to go to my database and "rollback" everything by hand, which is way longer to do.

这使得当我修复错误并重新运行迁移时，第一条语句失败，因为列/表已经创建/修改。然后我知道的唯一解决方案是转到我的数据库并手动“回滚”所有内容，这需要更长的时间。

migrate:rollback tries to rollback the previous migrations, as the current was not applied succesfully.

migrate:rollback 尝试回滚以前的迁移，因为当前没有成功应用。

I also tried to wrap all my code into a DB::transaction(), but it still doesn't work.

我还尝试将我的所有代码包装到 DB::transaction() 中，但它仍然不起作用。

Is there any solution for this? Or i just have to keep rolling things back by hand?



edit, adding an example (not writing Schema builder code, just some kind of pseudo-code):
Migration1:

有什么解决办法吗？或者我只需要手动回滚东西？



编辑，添加一个示例（不是编写架构构建器代码，只是某种伪代码）：
Migration1：

Create Table users (id, name, last_name, email)

Migration1 executed OK. Some days later we make Migration 2:

Migration1 执行正常。几天后，我们进行迁移 2：

Create Table items (id, user_id references users.id)
Alter Table users make_some_error_here

Now what will happen is that migrate will call the first statement and will create the table items with his foreign key to users. Then when he tries to apply the next statement it will fail.

现在将发生的是 migrate 将调用第一个语句，并将使用他的外键创建表项给用户。然后，当他尝试应用下一条语句时，它将失败。

If we fix the make_some_error_here, we can't run migrate because the table "items" it's created. We can't rollback (nor refresh, nor reset), because we can't delete the table users since there's a foreign key constraint from the table items.

如果我们修复了 make_some_error_here，我们将无法运行 migrate，因为它创建了表“items”。我们不能回滚（也不能刷新，也不能重置），因为我们不能删除表用户，因为表项有外键约束。

Then the only way to continue is to go to the database and delete the table items by hand, to get migrate in a consistent state.

那么唯一的办法就是去数据库手动删除表项，让迁移处于一致状态。

回答by Yevgeniy Afanasyev

It is not a Laravel limitation, I bet you use MYSQL, right?

这不是 Laravel 的限制，我敢打赌您使用的是 MYSQL，对吗？

As MYSQL documentation says here

正如 MYSQL 文档在这里所说的那样

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

有些语句不能回滚。通常，这些包括数据定义语言 (DDL) 语句，例如创建或删除数据库的语句，创建、删除或更改表或存储例程的语句。

And we have a recommendation of Taylor Otwell himself heresaying:

我们有泰勒Otwell自己的建议在这里说：

My best advice is to do a single operation per migration so that your migrations stay very granular.

我最好的建议是每次迁移只做一个操作，这样你的迁移就会非常细化。

-- UPDATE --

- 更新 -

Do not worry!

不要担心！

The best practices say:

最佳实践说：

You should never make a breaking change.

你永远不应该做出重大改变。

It means, in one deployment you create new tables and fields and deploy a new release that uses them. In a next deployment, you delete unused tables and fields.

这意味着，在一次部署中，您创建新表和字段并部署使用它们的新版本。在下一次部署中，您将删除未使用的表和字段。

Now, even if you'll get a problem in either of these deployments, don't worry if your migration failed, the working release uses the functional data structure anyway. And with the single operation per migration, you'll find a problem in no time.

现在，即使您在这些部署中的任何一个中遇到问题，也不要担心迁移失败，工作版本无论如何都会使用功能数据结构。通过每次迁移的单一操作，您很快就会发现问题。

回答by Guiman04

I'm using MySql and I'm having this problem.

我正在使用 MySql，但遇到了这个问题。

My solution depends that your down()method does exactly what you do in the up()but backwards.

我的解决方案取决于您的down()方法完全按照您的方式执行，up()但会向后执行。

This is what i go:

这就是我要去的：

try{
    Schema::create('table1', function (Blueprint $table) {
        //...
    });
    Schema::create('tabla2', function (Blueprint $table) {
        //...
    });
}catch(PDOException $ex){
    $this->down();
    throw $ex;
}

So here if something fails automatically calls the down()method and throws again the exception.

所以在这里如果出现故障会自动调用该down()方法并再次抛出异常。

Instead of using the migration between transaction()do it between this try

而不是使用之间的迁移transaction()在此尝试之间执行

回答by Martin Bean

Like Yevgeniy Afanasyev highlighted Taylor Otwell as saying (but an approach I already took myself): have your migrations only work on specific tables or do a specific operation such as adding/removing a column or key. That way, when you get failed migrations that cause inconsistent states like this, you can just drop the table and attempt the migration again.

就像 Yevgeniy Afanasyev 强调 Taylor Otwell 所说的（但我自己已经采用了一种方法）：让您的迁移仅适用于特定表或执行特定操作，例如添加/删除列或键。这样，当您遇到导致此类不一致状态的失败迁移时，您只需删除表并再次尝试迁移。

I've experienced exactly the issue you've described, but as of yet haven't found a way around it.

我确实遇到过您所描述的问题，但到目前为止还没有找到解决方法。

回答by DaGardner

Just remove the failed code from the migration file and generate a new migration for the failed statement. Now when it fails again the creation of the database is still intact because it lives in another migration file.

只需从迁移文件中删除失败的代码并为失败的语句生成新的迁移。现在，当它再次失败时，数据库的创建仍然完好无损，因为它位于另一个迁移文件中。

Another advantage of using this approach is, that you have more control and smaller steps while reverting the DB.

使用这种方法的另一个优点是，在恢复数据库时您有更多的控制权和更小的步骤。

Hope that helps :D

希望有帮助:D

回答by user1649498

I know it's an old topic, but there was activity a month ago, so here are my 2 cents.

我知道这是一个老话题，但一个月前有活动，所以这是我的 2 美分。

This answer is for MySql 8 and Laravel 5.8 MySql, since MySql 8, introduced atomic DDL: https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.htmlLaravel at the start of migration checks if the schema grammar supports migrations in a transaction and if it does starts it as such. The problem is that the MySql schema grammar has it set to false. We can extend the Migrator, MySql schema grammar and MigrationServiceProvider, and register the service provider like so:

此答案适用于 MySql 8 和 Laravel 5.8 MySql，因为 MySql 8 引入了原子 DDL：https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html Laravel 在迁移开始时检查是否模式语法支持事务中的迁移，如果它确实如此启动它。问题是 MySql 模式语法将其设置为 false。我们可以扩展 Migrator、MySql 模式语法和 MigrationServiceProvider，并像这样注册服务提供者：

<?php

namespace App\Console;

use Illuminate\Database\Migrations\Migrator as BaseMigrator;

use App\Database\Schema\Grammars\MySqlGrammar;

class Migrator extends BaseMigrator {
    protected function getSchemaGrammar( $connection ) {
        if ( get_class( $connection ) === 'Illuminate\Database\MySqlConnection' ) {
            $connection->setSchemaGrammar( new MySqlGrammar );
        }
        if ( is_null( $grammar = $connection->getSchemaGrammar() ) ) {
            $connection->useDefaultSchemaGrammar();
            $grammar = $connection->getSchemaGrammar();
        }
        return $grammar;
    }
}


<?php

namespace App\Database\Schema\Grammars;

use Illuminate\Database\Schema\Grammars\MySqlGrammar as BaseMySqlGrammar;

class MySqlGrammar extends BaseMySqlGrammar {
    public function __construct() {
        $this->transactions = config( "database.transactions", false );
    }
}


<?php

namespace App\Providers;

use Illuminate\Database\MigrationServiceProvider as BaseMigrationServiceProvider;

use App\Console\Migrator;

class MigrationServiceProvider extends BaseMigrationServiceProvider {   
    /**
     * Register the migrator service.
     * @return void
     */
    protected function registerMigrator() {
        $this->app->singleton( 'migrator', function( $app ) {
            return new Migrator( $app[ 'migration.repository' ], $app[ 'db' ], $app[ 'files' ] );
        } );
        $this->app->singleton(\Illuminate\Database\Migrations\Migrator::class, function ( $app ) {
            return $app[ 'migrator' ];
        } );
    }


<?php

return [
    'providers' => [

        /*
         * Laravel Framework Service Providers...
         */
        App\Providers\MigrationServiceProvider::class,

    ],
];

Of course, we have to add transactions to our database config... DISCLAIMER - Haven't tested yet, but looking only at the code it should work as advertised :) Update to follow when I test...

当然，我们必须将事务添加到我们的数据库配置中... 免责声明 - 尚未测试，但只查看它应该像宣传的那样工作的代码 :) 当我测试时要更新...

回答by Thomas Venturini

I think the best way to do it is like shown in the documentation:

我认为最好的方法是像文档中显示的那样：

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
});

See: https://laravel.com/docs/5.8/database#database-transactions

请参阅：https: //laravel.com/docs/5.8/database#database-transactions