Laravel Online Migrator

This package minimizes disruptions when applying Laravel's database migrations using tools like Percona Online Schema Change or InnoDB Online DDL. For example, one can write (mostly) standard Laravel migration files then run "php artisan migrate". Database changes will be automatically converted into PTOSC commands or online DDL queries.

Installation

You can install the package via composer:

composer require orisintel/laravel-online-migrator

The pt-online-schema-change command from Percona's toolkit must be in the path where Artisan will be run, unless InnoDB Online DDL is being used exclusively.

Configuration

Publish the configuration file:

php artisan vendor:publish --provider='OrisIntel\OnlineMigrator\OnlineMigratorServiceProvider'

If not using discovery then add the provider to config/app.php:

'providers' => [
    // ...
    OrisIntel\OnlineMigrator\OnlineMigratorServiceProvider::class,

If changing tables with enum columns consider working around "Unknown database type enum requested" by tweaking config/online-migrator.php:

  'doctrine-enum-mapping' => env('DOCTRINE_ENUM_MAPPING', 'string'),

or .env with DOCTRINE_ENUM_MAPPING=string

Usage

Run Artisan's migrate to apply migrations online*:

php artisan migrate

*Limitations are documented below.

Preview what changes it would make:

php artisan migrate --pretend

Add PTOSC options using environment variables:

PTOSC_OPTIONS='--recursion-method=none'  php artisan migrate

Flag migrations known to be incompatible with this tool using the built-in trait:

class MyMigration extends Migration
{
    use \OrisIntel\OnlineMigrator\OnlineIncompatible

Use a different strategy for a single migration:

class MyMigration extends Migration
{
    use \OrisIntel\OnlineMigrator\InnodbOnlineDdl

Do not combine queries for a migration when using PTOSC:

class MyMigration extends Migration
{
    use \OrisIntel\OnlineMigrator\CombineIncompatible

Adding foreign key with index to existing table:

class MyColumnWithFkMigration extends Migration
{
    public function up()
    {
        Schema::table('my_table', function ($table) {
            $table->integer('my_fk_id')->index();
        });

        Schema::table('my_table', function ($table) {
            $table->foreign('my_fk_id')->references('id')->on('my_table2');

Limitations

• Only supports Mysql, specifically those versions supported by PTOSC v3
• With PTOSC
  • Adding unique indexes may cause data loss unless tables are manually checked beforehand because of how PTOSC works
  • Adding not-null columns requires a default
  • Renaming a column or dropping a primary key have additional risks
  • Foreign key creation should be done separately from column creation or duplicate indexes may be created with slightly different naming
    • Close the Schema::create() call and make a separate Schema::table() call for all FKs in the migration
• With InnoDB Online DDL
  • See the MySQL Online DDL documentation
  • May be problematic on AWS Aurora
• Stateful migrations, like those selecting then saving rows, will instead need to do one of the following:
  • Use non-selecting queries like MyModel::where(...)->update(...)
  • Pipe the raw SQL like \DB::statement('UPDATE ... SET ... WHERE ...');
  • Use the OnlineMigratorIncompatible trait to mark the migration as incompatible

Testing

composer test

Output is verbose because passthru is used to help debug production problems. Executing as phpunit --testdox may be more readable until the verbosity can be tamed.

Contributing

Please see CONTRIBUTING for details.

Security

If you discover any security related issues, please email [email protected] instead of using the issue tracker.

Credits

• Paul R. Rogers
• All Contributors
• Percona Team for pt-online-schema-change

License

The MIT License (MIT). Please see License File for more information.