Move default_migration_table to a separate database

[laurinkeithdavis]

We have the issue that for test and some development, we sync the data between production (a MySQL slave) and the local database. Obviously, this is a problem because the default_migration_table will be overwritten. What I would like to see is the option to specify a default_migration_database, with a schema of the table name being the schema being synced. For example:

environments:
default_migration_database: phinx
default_database: development
development:
adapter: mysql
host: mysql.pridedallas.com
name: db1
user: xxxxxxxxxxxxx
pass: xxxxxxxxxxx
port: 3306
charset: utf8

So that if default_migration_database is used, instead of default_migration_table, then that the phinx schema would have a table for each schema that migrations are performed on (in this case a table named db1).

[laurinkeithdavis]

Structure would be something like this:

• schema: db1
• schema: db2
• schema: phinxlog
  ** table: db1 (phinxlog table for db1)
  ** table: db2 (phinxlog table for db2)

Maybe this helps with #180 as well?

[davidjdixon]

Have you just tried specifying the table name for phinxlog with the dot syntax to set a different database?

eg. default_migration_table = db2.phinxlog

?

[laurinkeithdavis]

Yes, that does work - though I was hoping to separate migrations, but I think we can work with this, thanks!

[davidjdixon]

If you follow the process I described on ticket #180 then this can be done using the same config split.

[laurinkeithdavis]

Actually, that does not work. It worked the first time, but not on subsequent runs:

[InvalidArgumentException]
There was a problem creating the schema table: SQLSTATE[42S01]: Base table
or view already exists: 1050 Table 'phinxlog' already exists

[laurinkeithdavis]

Seems like the migration table creation works, but then at some point when it checks to see if the migration table exists, that comes back negative and then attempts to create it anyway.

[laurinkeithdavis]

The issue is the hasTable() method does not work with database.tablename:

    public function hasTable($tableName)
    {
        $options = $this->getOptions();

        $exists = $this->fetchRow(sprintf(
            "SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'",
            $options['name'], $tableName
        ));

        return !empty($exists);
    }

[laurinkeithdavis]

I'm looking at ways to modify the project to allow for this, but the problem is the database name is set as a single option: "name". Everywhere that that is used, there is no way to say "If this is the schema, use this database, otherwise use this database", without heavy modifications.

[laurinkeithdavis]

I mean, I could do something hacky like create a new option called "schemadb" and then do this:

    public function hasSchemaTable()
    {
        $options = $this->getOptions();
        $name = $options["name"];

        $this->setOptions(["name" => $options["schemadb"]]);

        $return = $this->hasTable($this->getSchemaTableName());

        $this->setOptions(["name" => $name]);

        return $return;
    }

[laurinkeithdavis]

Or going with previous recommended solution of phinxlog.phinxlog (db.tablename), this is a little less hacky of a solution:

    public function hasTable($tableName)
    {
        $options = $this->getOptions();

        if(strstr($tableName, ".")){
            $dbtable = explode(".", $tableName);
            $options['name'] = $dbtable[0];
            $tableName = $dbtable[1];
        }

        $exists = $this->fetchRow(sprintf(
            "SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'",
            $options['name'], $tableName
        ));

        return !empty($exists);
    }

[laurinkeithdavis]

I would like to see that universal change to the project.

[laurinkeithdavis]

So why does Postgres use an option called schema?

[rquadling]

If you clone a DB that is migrated to a different point than the phinxlog says, how would you get them in sync? Having phinxlog in the db makes sense.

[laurinkeithdavis]

If you clone a DB that is migrated to a different point than the phinxlog says, how would you get them > in sync?

I'm not even sure what that means.

Can you give an example?

We rely on the migrations themselves to ensure the database is correct. We write our migrations so that if the change is already done, for example, they are skipped. For example: If a column has already been added, we don't add the column again.

So if I clone a DB today and my phinxlog table does not match the cloned DB, then all migrations will be run and bring the DB up to date.

[rquadling]

Phinx will only run migrations that don't exist in the phinxlog. So they are run-once. If you now copy a DB that hasn't had all the migrations, you cannot re-run them without dropping phinxlog and then relying on your migrations to NOT repeat themselves in some way. Handling views/stored procedures/triggers/events/etc. all get a bit messy. As do migrations that change data, not just schema.

[laurinkeithdavis]

Yes, I know and that is exactly how we write these. Because we also sync data and schema manually at times between the various copies of this DB (1 production, 4 dev, 1 test), we have to write all of the migrations so that they can only be run once, even if phinx attempts to run the more than once.

[laurinkeithdavis]

It's not even hard to right the migrations with these kind of "checks" in place:

    public function up(){

        if($this->hasTable("premier_creditadj"))
            $this->dropTable("premier_creditadj");

        if($this->hasTable("premier_debitadj"))
            $this->dropTable("premier_debitadj");

    }

[othercorey]

Closing due to lack of activity or out of scope. If there is new interest, please open a PR.