Phinx: Commands fail because 'phinxlog' table already exists

[floriangaerber]

When running any command (such as migrate, status) on an empty database, the phinxlog table is created.
Now, when running any command (such as migrate, status, rollback), phinx exits with the below error, without running my migrations or returning status information.

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

When inspecting the database with phpmyadmin, I can see that the first migration or status command creates the phinxlog table (and also, when running migration, it correctly creates the defined tables/columns).
Any subsequent command failes before executing any migrations or queries.

Using MySQL on Windows (WAMP).
(PHP version: 7.0.10, MySQL version: 5.7.14)

Appendix

phinx.php

<?php

require 'app/bootstrap.php';

return [
    'paths' => [
        'migrations' => '%%PHINX_CONFIG_DIR%%/resources/db/migrations',
        'seeds' => '%%PHINX_CONFIG_DIR%%/resources/db/seeds'
    ],
    'version_order' => 'creation',
    'environments' => [
        // 'default_migration_table' => '_phinxlog',
        'default_database' => 'envDb', // DB set in .env
        'envDb' => [
            'name' => 'environment_db',
            'connection' => $container->get('db')->connection()->getPdo()
        ]
    ]
];

(timestamp)_example_migration.php

<?php

use Phinx\Migration\AbstractMigration;

class ExampleMigration extends AbstractMigration
{
    public function up()
    {
        $usersTable = $this->table('users');
        $usersTable
            ->addColumn('email', 'string', ['limit' => 255])
            ->addColumn('password', 'string', ['limit' => 255])
            ->addTimestamps()
            ->addIndex(['email'], ['unique' => true])
            ->create();
    }

    public function down()
    {
        $this->dropTable('users');
    }
}

Full command line output

Example of running status twice on a fresh, empty database:

D:\wamp64\www\project [master = +1 ~3 -0 !]> .\vendor\bin\phinx status
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
warning no environment specified, defaulting to: envDb
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name
----------------------------------------------------------------------------------
   down  20170301100453                                            ExampleMigration
   down  20170301155631                                            UpgradeMigration

D:\wamp64\www\project [master = +1 ~3 -0 !]> .\vendor\bin\phinx status
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
warning no environment specified, defaulting to: envDb
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name
----------------------------------------------------------------------------------


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


status [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-f|--format FORMAT]

D:\wamp64\www\project [master = +1 ~3 -0 !]>

[rquadling]

Can you run with -vvv please?

[rquadling]

Also, temporarily, could you use the credentials that the connection is using rather than the connection itself. Let's rule out any issue there.

[floriangaerber]

Running with -vvv

D:\wamp64\www\project [master = +1 ~3 -1 !]> .\vendor\bin\phinx status -vvv
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
warning no environment specified, defaulting to: envDb
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name
----------------------------------------------------------------------------------
   down  20170301100453                                            SetupMigration

D:\wamp64\www\project [master = +1 ~3 -1 !]> .\vendor\bin\phinx status -vvv
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
warning no environment specified, defaulting to: envDb
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name
----------------------------------------------------------------------------------


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


Exception trace:
 () at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\PdoAdapter.php:544
 Phinx\Db\Adapter\PdoAdapter->createSchemaTable() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\P
doAdapter.php:210
 Phinx\Db\Adapter\PdoAdapter->setConnection() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\PdoAd
apter.php:105
 Phinx\Db\Adapter\PdoAdapter->setOptions() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\PdoAdapt
er.php:84
 Phinx\Db\Adapter\PdoAdapter->__construct() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Db\Adapter\Adapter
Factory.php:130
 Phinx\Db\Adapter\AdapterFactory->getAdapter() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Migration\Manag
er\Environment.php:339
 Phinx\Migration\Manager\Environment->getAdapter() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Migration\M
anager\Environment.php:269
 Phinx\Migration\Manager\Environment->getVersionLog() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Migratio
n\Manager.php:129
 Phinx\Migration\Manager->printStatus() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Console\Command\Status
.php:88
 Phinx\Console\Command\Status->execute() at D:\wamp64\www\project\vendor\symfony\console\Command\Command.php:262
 Symfony\Component\Console\Command\Command->run() at D:\wamp64\www\project\vendor\symfony\console\Application.php:826
 Symfony\Component\Console\Application->doRunCommand() at D:\wamp64\www\project\vendor\symfony\console\Application.php:1
89
 Symfony\Component\Console\Application->doRun() at D:\wamp64\www\project\vendor\robmorgan\phinx\src\Phinx\Console\PhinxA
pplication.php:83
 Phinx\Console\PhinxApplication->doRun() at D:\wamp64\www\project\vendor\symfony\console\Application.php:120
 Symfony\Component\Console\Application->run() at D:\wamp64\www\project\vendor\robmorgan\phinx\bin\phinx:28

status [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-f|--format FORMAT]

D:\wamp64\www\project [master = +1 ~3 -1 !]>

Result: Empty phinxlog table.

Using credentials instead of PDO

Seems to work as intended.

Command line output

D:\wamp64\www\project [master = +1 ~3 -1 !]> .\vendor\bin\phinx status -vvv -e localDb
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
using environment localDb
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name
----------------------------------------------------------------------------------
 -- createTable('phinxlog')
    -> 0.0435s
   down  20170301100453                                            SetupMigration

D:\wamp64\www\project [master = +1 ~3 -1 !]> .\vendor\bin\phinx status -vvv -e localDb
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
using environment localDb
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name
----------------------------------------------------------------------------------
   down  20170301100453                                            SetupMigration

D:\wamp64\www\project [master = +1 ~3 -1 !]> .\vendor\bin\phinx migrate -vvv -e localDb
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
using environment localDb
using adapter mysql
using database mig_test

 == 20170301100453 SetupMigration: migrating
 -- createTable('users')
    -> 0.0377s
 == 20170301100453 SetupMigration: migrated 0.0462s

All Done. Took 0.0576s
D:\wamp64\www\project [master = +1 ~3 -1 !]> .\vendor\bin\phinx rollback -vvv -e localDb
Phinx by Rob Morgan - https://phinx.org. 0.8.0

using config file .\phinx.php
using config parser php
using migration paths
 - D:\wamp64\www\project\resources\db\migrations
using seed paths
using environment localDb
using adapter mysql
using database mig_test
ordering by creation time
No migrations to rollback

All Done. Took 0.0211s
D:\wamp64\www\project [master = +1 ~3 -1 !]>

Updated phinx.php

<?php

require 'app/bootstrap.php';

return [
    'paths' => [
        'migrations' => '%%PHINX_CONFIG_DIR%%/resources/db/migrations',
        'seeds' => '%%PHINX_CONFIG_DIR%%/resources/db/seeds'
    ],
    'version_order' => 'creation',
    'environments' => [
        // 'default_migration_table' => '_phinxlog',
        'default_database' => 'envDb', // DB set in .env
        'envDb' => [
            'name' => 'environment_db',
            'connection' => $container->get('db')->connection()->getPdo()
        ],
        'localDb' => [
            'adapter' => 'mysql',
            'host' => 'localhost',
            'name' => 'mig_test', // new, empty database
            'user' => 'root',
            'pass' => '',
            'charset' => 'utf8',
            'collation' => 'utf8_general_ci'
        ]
    ]
];

I am using Eloquent in my project, the envDb connection is made like this:

$capsule = new \Illuminate\Database\Capsule\Manager();

$dbUrl = parse_url(getenv('DATABASE_URL'));
$capsule->addConnection(
    [
        'driver' => getenv('DB_DRIVER'),
        'host' => $dbUrl['host'],
        'port' => $dbUrl['port'],
        'database' => substr($dbUrl['path'], 1),
        'username' => $dbUrl['user'],
        'password' => $dbUrl['pass'],
        'charset' => 'utf8',
        'collation' => getenv('DB_COLLATION'),
        'prefix' => '',
    ],
    'default'
);
// https://github.com/laracasts/Eloquent-Outside-of-Laravel

$capsule->setAsGlobal();
$capsule->bootEloquent();

$container['db'] = function ($container) use ($capsule) {
    return $capsule;
};

You can get the underlying PDO instance like this:
$pdo = DB::connection()->getPdo();
We know this works, because the first command succeeds.
Calling get_class on this returns "PDO".

I can work with using credentials, but if you still want to investigate, I'll be happy to assist.

[rquadling]

I have a local PDO wrapped connection (Doctrine), so I'll try it out with that and see what's what.

[rquadling]

Just realised we don't use PDO for our doctrine connections.

But using a config (snippet) like this ...

                'development' =>
                    [
                        'connection' => new PDO(
                            sprintf(
                                'mysql:host=%s;dbname=%s',
                                $_ENV['DB_DEVELOPMENT_HOST'],
                                'phinx_testing'
                            ), $_ENV['DB_DEVELOPMENT_USERNAME'],
                            $_ENV['DB_DEVELOPMENT_PASSWORD']
                        ),
//                        'adapter' => 'mysql',
//                        'host' => $_ENV['DB_DEVELOPMENT_HOST'],
//                        'name' => $_ENV['DB_DEVELOPMENT_DATABASE'],
//                        'user' => $_ENV['DB_DEVELOPMENT_USERNAME'],
//                        'pass' => $_ENV['DB_DEVELOPMENT_PASSWORD'],
//                        'port' => 3306,
//                        'charset' => 'utf8',
//                        'collation' => 'utf8_unicode_ci',
                    ],

Is working. When I set the DB name to $_ENV['DB_DEVELOPMENT_DATABASE'], I get to see all my migrations.

Can you try this route?

If it is still working, then I wonder if there is something different with regard to your connection instance that is different from a regular PDO connection.

[floriangaerber]

Using new PDO works perfectly fine.

It seems to be an issue with the PDO instance returned by Eloquent then.

[rquadling]

Did you find a cause within your Eloquent instance?
Can we close this issue?

[floriangaerber]

I simply went and used credentials instead of Eloquent's PDO for the connection.

[rquadling]

OK. Thank you for highlighting the issue. If we have anyone else with the problem, I'll refer to this issue. And thank you for introducing me to connection. Hadn't seen it until you mentioned it!

[volkish]

To avoid this bug you must add a connection name to your Phinx config. Phinx uses this as database name, so when you attach the PDO connection to Phinx it doesn't understand which database it gotta use. The easiest way is to query the database name like this.

Here is my config which works perfectly.

<?php

require_once __DIR__ .  '/boostrap.php';


return [
    'paths' => [
        'migrations' => '%%PHINX_CONFIG_DIR%%/db/migrations',
        'seeds' => '%%PHINX_CONFIG_DIR%%/db/seeds'
    ],
    'environments' => [
        'default_database' => 'production',
        'production' => [
            'adapter' => 'mysql',
            'connection' => $container->db,
            'name' => $container->db->query("SELECT DATABASE()")->fetchColumn(0),
        ]
     ]
];