Migration simulator about ideas HOT 13 CLOSED

Always set table engines, encodings, etc and do not rely on default server settings. I extended Blueprint constructor like this:

        $this->engine = config('database.connections.mysql.engine');
        $this->charset = config('database.connections.mysql.charset');
        $this->collation = config('database.connections.mysql.collation');

and always have engine, charset and collation I've set in config.

What are you trying to solve is pritty impossible. For such cases you must have a staging environment which is maximum close to production environment where you also have same data.

I don't think there is need in php artisan migrate:test because it is the same as php artisan migrate on local/test/dev/staging server.

When migration failed and I got broken structure, I'm repairing it fast using MySQL Workbench or just dropping all tables and running all migrations again.

from ideas.

@jamesgrose Nope, at least for MySQL transactions cannot reverse DDL statements (CREATE TABLE etc.)

from ideas.

How is it different from just testing migrations up and down on your local or dev server?

from ideas.

If your up fails, the down won't succeed to revert the changes which will leave you in a broken state, especially if you have foreign key contraints in place.

So you either import a old sql dump of your database, or fight through the database tables making manual changes just to revert back to the original state.

As for a production server, if you ever have a MySQL configuration that is different to the one you use locally, the up can still succeed locally and fail on the remote server. I has this problem once, where my default engine locally was InnoDB, and the prod server MyISAM (default in MySQL prior to 5.5). Then I run migrate command and big red exception was thrown from no where. (My fault I didn't check).

Luckily I had backups, that I used to restore DB to the original state.

Also, I had to set the engine explicitly to InnoDB in all my migration files where a table is created for the first time. (This happened before the introduction the engine config variable in laravel)

This was just one single problem, other issues may rise in different scenarios.

So instead you could test and then if it succeeds, you continue with you migrations with no worries of breaking you database.

And if you use a deployment tool, you could php artisan migrate:test before php artisan migrate, so if the simulation fails, the deployment is cancelled without ever touching your production database.

from ideas.

and ofcourse there must be a backup of your production db server, and yeah staging server is enough to test new migrations.

unless you want to directly migrate without having a test on it?

doing the up and down make sense under your local too.

from ideas.

I agree that Laravel's migration system can be much better. My huge problem is with refrences and it's a horible error for me.
But as for your suggestion @ShinCoder, you must realy test your migration on dev/local mashine and then you'll be sure that if they pass in local so with the same database configuration they will pass on prod server. :)

from ideas.

What about a runtime? Anyone benchmarked it?

from ideas.

@ShinCoder makes some valid points. @Mevrael that's a lot of boilerplate, maybe migrations could be improved in the sense that you would be able to only specify these settings once, or make them default to the mysql conn implicitly, and specifying them per migration would allow overriding. Or something of that nature.

from ideas.

@ShinCoder I totally agree. We do have testing servers with the same data as the live servers. But I still think the migration process could be better.

from ideas.

@ShinCoder Could wrapping each migration in a database transaction help to stop the database being left in a broken state?

from ideas.

@jamesgrose as @franzliedke said, it's not possible for Mysql. This is really where PostgreSQL shines.

from ideas.

Should just use a proper staging server and test your database changes against a mirror copy of the existing database. Also I don't think deployment stuff such as this belongs in core.

from ideas.

Probably will not be included in core.

from ideas.