Phinx: Feature Request: Set database session variable after connection

[courtney-miles]

I would like to be able to specify some session variables to always be set after Phinx establishes a connection.

For example, I would like to have storage_engine to ensure that if we neglect to specify the table engine in statement, and the script is ran on a server where default_storage_engine is not in our control.

Another good one would be sql_mode.

Ideally these would be specified in the phinx.yml file.

[courtney-miles]

... actually, it may be best to run the SET for each variable before each individual migrations, in case one of those migrations change the variable which would impact all following migrations.

[rquadling]

You can easily extend Phinx with your own migration base class.

<?php
namespace YourProject\Migration;

abstract class AbstractMigration extends \Phinx\Migration\AbstractMigration
{
    public function init()
    {
        // Initialize the migration.
        parent::init();

        // Now do your sets here.
}
?>

And then, in your phinx config (I use a PHP file, so this is array syntax), set the migration_base_class.

'migration_base_class' => 'YourProject\\Migration\\AbstractMigration',

Admittedly, the set will be done for every migration, rather than connection. Maybe this is OK. Especially if you have to do something different in a single migration.

[courtney-miles]

We have finally been able to upgrade Phinx to be able to take advantage of this.

Unfortunately, it does not work.

The database connection is not made available to the migration until just before the up/down/change methods are executed. So you cannot run any queries from the init() method.

So instead you would need to set the session variable in the up()/down() methods. The abstract class cannot enforce this on the change() method because if it gets defined, it will get used instead of up/down.

It is perhaps a little heavy handed, but to ensure a migration are not accidentally written without calling parent::up()/parent::down(), I have declared these methods as final, and the migrations instead need to implement alternative methods, migrate()/rollback() which parent::up()/parent::down() will call.

Note that this is mostly prompted by the need to ensure SQL_MODE for the connection is correctly set before defining triggers and routines, as their modes are inherited from the connection.

I think there is a good case to incorporate a cleaner solution to ensure consistent schemas between servers.

[rquadling]

Aha. I see.

Looking at the code, the adapter really is made as late as possible.

I think a better solution would be to be able to extend the adapter and allow that to control it's connection setup better.

You could register your own adapter using http://docs.phinx.org/en/latest/configuration.html#custom-adapters, allowing you to setup the adapter as you see fit.

But it wouldn't alter when the adapter would be available to the migration.

OOI, why not just set the SQL_MODE as part of the migration?

$this->query("SET SQL_MODE = 'xxx'");

[courtney-miles]

You could register your own adapter [...] But it wouldn't alter when the adapter would be available to the migration.

You are right, that may have been a nicer option. I probably would have extended the MySQL adapter and set session variables for the connection withing the connect method, like so:

<?php

MyAdapter extends \Phinx\Db\Adapter\MysqlAdapter
{
    public function connect()
    {
        parent::connect();
        $this->execute("SET SQL_MODE = 'xxx'");
    }
}

Although, it's not clear how to best register a custom adapter. I assume we would need to write our own version of bin/phinx.

why not just set the SQL_MODE as part of the migration?

We have a small team writing migrations and people forget. We have around 80 installs of the product and we have found that the same routines have many different SQL modes across the various installs. There have been cases where MySQL has been silently handling bad values for some installs whilst erring on others.

We want to make sure that going forward, all routines and triggers across all servers and database have a consistent SQL mode.

Additionally, we want to be sure that all our migrations are ran in a suitably strict mode.

[rquadling]

Registering a new adapter can in the init() of your own base migration class.

Set 'migration_base_class' in your phinx.yml/phinx.php config file to a class that extends the AbstractMigration class. Add the registration to the init() method. Maybe. Thinking out loud. Untested. YMMV. IANAL.

[rquadling]

Or, even easier.

Use phinx.php rather than phinx.yml, and you can add it to the top of the config script.

Mine looks like ...

<?php

// load our environment files - used to store credentials & configuration
(new \josegonzalez\Dotenv\Loader(\Helpers\Directories::getRootDirectory().'/.env'))->parse()->toEnv(true);

return
    [
        'paths' => [
            'migrations' => '%%PHINX_CONFIG_DIR%%/migrations',
            'seeds' => '%%PHINX_CONFIG_DIR%%/seeds',
        ],
        'environments' =>
            [

As the autoloader will already be running, adding AdapterFactory::instance()->registerAdapter($name, $class); to the top of that file should be enough.

[cottton]

You can use mysql_attr_ commands -- see http://php.net/manual/en/ref.pdo-mysql.php#pdo-mysql.constants

But you have to use the name of the option as a lower case string.
F.e. for PDO::MYSQL_ATTR_INIT_COMMAND you will use mysql_attr_init_command.

see at code: https://github.com/robmorgan/phinx/blob/master/src/Phinx/Db/Adapter/MysqlAdapter.php#L107

Example init command

[
    'phinx' => [
        'paths'        => [...],
        'environments' => [
    'default_migration_table' => '...',
    'default_database'        => '...',
    'default'                 => [
        'adapter'                 => 'mysql',
        'host'                    => ...,
                'name'                    => ...,
                'user'                    => ...,
                'pass'                    => ...,
                'port'                    => ...,
                'charset'                 => ...,
                'collation'               => ...,
                'mysql_attr_init_command' => "SET CHARSET 'utf8', NAMES 'utf8', sql_mode = 'STRICT_ALL_TABLES';",
            ]
        ]
    ]
];

[dereuromark]

Can you please make a PR to improve the docs here?