The indentation for addIndex and changeColumn in the generated code does not align with other methods like addColumn / removeColumn.

The script generate something like that

 $table = $this->table("asset_templates", ['id' => false, 'primary_key' => ["id", "unity_object_id"], 'engine' => "InnoDB", 'encoding' => "latin1", 'collation' => "latin1_swedish_ci", 'comment' => ""]);
 $table->addColumn('unity_object_id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_BIG, 'precision' => 19, 'after' => 'id']);

It causes a bug because id is not created.

I'm getting a:

[RuntimeException]                              
Cannot update a table that doesn't exist!

With this generated code:

$table = $this->table("role_sections", ['id' => false, 'primary_key' => ["role_id", "section_name"], 'engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => ""]);
$table->addColumn('role_id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false])->update();;
$table->addColumn('section_name', 'string', ['null' => false, 'limit' => 45, 'collation' => "utf8_general_ci", 'encoding' => "utf8", 'after' => 'role_id'])->update();;
$table->save();

As you can see this table doe not have an "id" column, but 2 PK columns, the resulting generated code should be like this (no ->update(); at the end of each column):

$table = $this->table("role_sections", ['id' => false, 'primary_key' => ["role_id", "section_name"], 'engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => ""]);
$table->addColumn('role_id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false]);
$table->addColumn('section_name', 'string', ['null' => false, 'limit' => 45, 'collation' => "utf8_general_ci", 'encoding' => "utf8", 'after' => 'role_id']);
$table->save();

Could you tell me what the purpose is of the initial migration file? To what is it comparing the current schema?

I ask because it generates a migration file that I cannot run because it contains migration commands that are already in my schema.

I'm probably missing something.

Great tool btw!

Hi,

1. Generated for first time migration file with schema file from development DB (90 tables).
2. Run the migration on testing DB.
3. Removed a column with foreign key on development DB and generated migration file.
4. Run the migration on testing DB, and I got the error.

Could that be because bad conventions are being used or something like that?

I also realized that db transactions are not being used, right? Wouldn't that be a good idea?
phinxlog table does not get updated with the failing migration, but some fields actually get changed

20190926071410 DbChange4138451195d8c654158c26: migrating
PDOException: SQLSTATE[HY000]: General error: 1553 Cannot drop index 'fk_activity_payments1': needed in a foreign key constraint in /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:168
Stack trace:
#0 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(168): PDO->exec('ALTER TABLE `ac...')
#1 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Util/AlterInstructions.php(126): Phinx\Db\Adapter\PdoAdapter->execute('ALTER TABLE `ac...')
#2 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(573): Phinx\Db\Util\AlterInstructions->execute('ALTER TABLE `ac...', Array)
#3 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(932): Phinx\Db\Adapter\PdoAdapter->executeAlterSteps('activity', Object(Phinx\Db\Util\AlterInstructions))
#4 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(497): Phinx\Db\Adapter\PdoAdapter->executeActions(Object(Phinx\Db\Table\Table), Array)
#5 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/TimedOutputAdapter.php(406): Phinx\Db\Adapter\AdapterWrapper->executeActions(Object(Phinx\Db\Table\Table), Array)
#6 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(146): Phinx\Db\Adapter\TimedOutputAdapter->executeActions(Object(Phinx\Db\Table\Table), Array)
#7 /phinx/vendor/cakephp/collection/CollectionTrait.php(64): Phinx\Db\Plan\Plan->Phinx\Db\Plan\{closure}(Object(Phinx\Db\Plan\AlterTable), 0)
#8 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(147): Cake\Collection\Collection->each(Object(Closure))
#9 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(721): Phinx\Db\Plan\Plan->execute(Object(Phinx\Db\Adapter\TimedOutputAdapter))
#10 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(628): Phinx\Db\Table->executeActions(true)
#11 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(687): Phinx\Db\Table->update()
#12 /phinx/migrations/20190926071410_db_change4138451195d8c654158c26.php(53): Phinx\Db\Table->save()
#13 /phinx/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(125): DbChange4138451195d8c654158c26->change()
#14 /phinx/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(391): Phinx\Migration\Manager\Environment->executeMigration(Object(DbChange4138451195d8c654158c26), 'up', false)
#15 /phinx/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(365): Phinx\Migration\Manager->executeMigration('prod', Object(DbChange4138451195d8c654158c26), 'up', false)
#16 /phinx/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(123): Phinx\Migration\Manager->migrate('prod', 20190926071410, false)
#17 /phinx/vendor/symfony/console/Command/Command.php(255): Phinx\Console\Command\Migrate->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /phinx/vendor/symfony/console/Application.php(915): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /phinx/vendor/symfony/console/Application.php(272): Symfony\Component\Console\Application->doRunCommand(Object(Phinx\Console\Command\Migrate), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 /phinx/vendor/robmorgan/phinx/src/Phinx/Console/PhinxApplication.php(96): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#21 /phinx/vendor/symfony/console/Application.php(148): Phinx\Console\PhinxApplication->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#22 /phinx/vendor/odan/phinx-migrations-generator/bin/phinx-migrations(20): Symfony\Component\Console\Application->run()
#23 {main}

== 20190926065526 DbChange7088656665d8c60dcc2c70: migrating
InvalidArgumentException: The specified index name 'fk_users_id_foreign3_idx' does not exist in /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php:654
Stack trace:
#0 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(872): Phinx\Db\Adapter\MysqlAdapter->getDropIndexByNameInstructions('activity', 'fk_users_id_for...')
#1 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(497): Phinx\Db\Adapter\PdoAdapter->executeActions(Object(Phinx\Db\Table\Table), Array)
#2 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/TimedOutputAdapter.php(406): Phinx\Db\Adapter\AdapterWrapper->executeActions(Object(Phinx\Db\Table\Table), Array)
#3 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(146): Phinx\Db\Adapter\TimedOutputAdapter->executeActions(Object(Phinx\Db\Table\Table), Array)
#4 /phinx/vendor/cakephp/collection/CollectionTrait.php(64): Phinx\Db\Plan\Plan->Phinx\Db\Plan\{closure}(Object(Phinx\Db\Plan\AlterTable), 'activity')
#5 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(147): Cake\Collection\Collection->each(Object(Closure))
#6 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(721): Phinx\Db\Plan\Plan->execute(Object(Phinx\Db\Adapter\TimedOutputAdapter))
#7 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(628): Phinx\Db\Table->executeActions(true)
#8 /phinx/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(687): Phinx\Db\Table->update()
#9 /phinx/migrations/20190926065526_db_change7088656665d8c60dcc2c70.php(30): Phinx\Db\Table->save()
#10 /phinx/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(125): DbChange7088656665d8c60dcc2c70->change()
#11 /phinx/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(391): Phinx\Migration\Manager\Environment->executeMigration(Object(DbChange7088656665d8c60dcc2c70), 'up', false)
#12 /phinx/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(365): Phinx\Migration\Manager->executeMigration('prod', Object(DbChange7088656665d8c60dcc2c70), 'up', false)
#13 /phinx/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(123): Phinx\Migration\Manager->migrate('prod', 20190926065526, false)
#14 /phinx/vendor/symfony/console/Command/Command.php(255): Phinx\Console\Command\Migrate->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#15 /phinx/vendor/symfony/console/Application.php(915): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /phinx/vendor/symfony/console/Application.php(272): Symfony\Component\Console\Application->doRunCommand(Object(Phinx\Console\Command\Migrate), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /phinx/vendor/robmorgan/phinx/src/Phinx/Console/PhinxApplication.php(96): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /phinx/vendor/symfony/console/Application.php(148): Phinx\Console\PhinxApplication->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /phinx/vendor/odan/phinx-migrations-generator/bin/phinx-migrations(20): Symfony\Component\Console\Application->run()
#20 {main}

When generating migrations I'm having errors wile deploying changes.
Looks like extra quotes are added to the migration file (not the schema)

->addColumn('email_verified', 'char', [
                'null' => false,
                'default' => '\'N\'',
                'limit' => 1,
                'collation' => 'utf8_unicode_ci',
                'encoding' => 'utf8',
                'after' => 'name',
            ])

and

->addColumn('password', 'char', [
                'null' => true,
                'default' => 'NULL',
                'limit' => 60,
                'collation' => 'utf8_unicode_ci',
                'encoding' => 'utf8',
                'after' => 'new_email',
            ])

Hey @odan,

I know your project specifies that this only works with MySQL 5.7+ but I've tested it on the latest version of MariaDB and everything works as expected, except for the generation of "default null" fields.
i.e: city_id int (10) unsigned default null gets parsed to city_id int (10) unsigned default 'null' (note the quotes around null), thus breaking the migration.

This is what's generated from MySQL 5.7+:

'city_id' => 
        array (
          'TABLE_CATALOG' => 'def',
          'TABLE_NAME' => 'addresses',
          'COLUMN_NAME' => 'city_id',
          'ORDINAL_POSITION' => '2',
          'COLUMN_DEFAULT' => NULL,
          'IS_NULLABLE' => 'YES',
          'DATA_TYPE' => 'int',
          'CHARACTER_MAXIMUM_LENGTH' => NULL,
          'CHARACTER_OCTET_LENGTH' => NULL,
          'NUMERIC_PRECISION' => '10',
          'NUMERIC_SCALE' => '0',
          'DATETIME_PRECISION' => NULL,
          'CHARACTER_SET_NAME' => NULL,
          'COLLATION_NAME' => NULL,
          'COLUMN_TYPE' => 'int(10) unsigned',
          'COLUMN_KEY' => 'MUL',
          'EXTRA' => '',
          'PRIVILEGES' => 'select,insert,update,references',
          'COLUMN_COMMENT' => '',
          'GENERATION_EXPRESSION' => '',
          'SRS_ID' => NULL,
        )

This is generated from MariaDB 10.4+

'city_id' => 
        array (
          'TABLE_CATALOG' => 'def',
          'TABLE_NAME' => 'addresses',
          'COLUMN_NAME' => 'city_id',
          'ORDINAL_POSITION' => '2',
          'COLUMN_DEFAULT' => 'NULL',
          'IS_NULLABLE' => 'YES',
          'DATA_TYPE' => 'int',
          'CHARACTER_MAXIMUM_LENGTH' => NULL,
          'CHARACTER_OCTET_LENGTH' => NULL,
          'NUMERIC_PRECISION' => '10',
          'NUMERIC_SCALE' => '0',
          'DATETIME_PRECISION' => NULL,
          'CHARACTER_SET_NAME' => NULL,
          'COLLATION_NAME' => NULL,
          'COLUMN_TYPE' => 'int(10) unsigned',
          'COLUMN_KEY' => '',
          'EXTRA' => '',
          'PRIVILEGES' => 'select,insert,update,references',
          'COLUMN_COMMENT' => '',
          'IS_GENERATED' => 'NEVER',
          'GENERATION_EXPRESSION' => NULL,
        )

The schema.php is quite different but maybe there could be an upgrade to support both?

Renato

Pretty much every time I make a change to my database, I run a migration, just so that I can be somewhat granular on exactly what has happened to my database by briefly reading the list of migrations in date order.

For the first time (been using PMG for about a year now) I have to use the rollback command as I made a mistake which was recorded in the last migration. For example, today I have the following migrations:

• YYYYMMDD175816_update_folder_to_varchar256
• YYYYMMDD180534_rename_createdby_to_orderid
• YYYYMMDD214148_add_status_to_t1_and_t2

Every time I run the PMG generate command I choose to override my schema.php file. Additionally, I don't use breakpoints.

I wanted to revert back to the 180534 version, and as it was the penultimate migrate, phinx-migrations rollback was all I needed.

1. Can I now safely delete the 214148 file without screwing anything else up, including later migrations? Or do I leave it there and somehow the next time I run a migration, it'll just be ignored? (I note that the phinxlog table shows the latest as being 180534)
2. Can I safely assume schema.php is back to the 180534 version?

Should I be doing anything else??

Hello, how do you create foreign keys? It says its experimental, is there a way to generate them? It only created indexes instead of FK for my tables.

Hi

Is there plans to implement same drivers/adapters that support Phinx: PostgreSQL, SQLite and MSSQL?

Thanks

I know it says in the README that the library supports MySQL 5.7+, but since MariaDB is commonly used to replace MySQL (e.g in linux distros, notably CentOS/Debian...), or at the very least the most common replacement for MySQL, I thought it'd be worth a mention.

MariaDB 10.2 supports expressions on defaults, which MySQL does not, and from MariaDB 10.2.7 onwards, the information schema default column is now evaluated and stored as an expression, and not a string (see: https://mariadb.com/kb/en/library/information-schema-columns-table/)

To be explicit, from MariaDB 10.2.7+:

• When the column default is NULL, the COLUMN_DEFAULT column has a string value of "NULL". This breaks detection of NULL defaults, because they're treated as a "NULL" string.
• When the column default is 'ABC', the COLUMN_DEFAULT column has a string value of "'ABC'", simple quotes included. This also probably breaks things, but I haven't tested it, as I ran into the NULL defaults problem first.

There's of course no way to handle both MySQL and MariaDB at the same time unless the engine is detected. I suppose SELECT @@GLOBAL.version; could be parsed and defaults parsed accordingly, but I'm not sure if there's an interest from your side to support MariaDB moving forward.

Edit: Alternatively, a mariadb_new_default_schema option could be added (or some name like that), which is more reliable than parsing a server variable

In the following code phinxlog table name is hardcoded.

I suggest following improvement: use tablename from phinx.yml config file

I am looking for a database migration generator for Drupal. Do you have any documentation and any experience on how to use this library for drupal?

Hi @odan

first of all, thank you for this great script :-)

I am using the latest version (3.1.0) and unfortunately have the same issue: Multi column primary keys cannot be generated. The problem is, that after every addColumn() in the generated migration file, there is a call to save().

The table:

CREATE TABLE `test` (
  `pk1` int(11) unsigned NOT NULL,
  `pk2` int(11) unsigned NOT NULL,
  PRIMARY KEY (`pk1`,`pk2`)
) ENGINE=InnoDB

Will produce a migration file like this:

...
$table = $this->table("test", ['id' => false, 'primary_key' => ["pk1", "pk2"], 'engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => "", 'row_format' => "Dynamic"]);
$table->addColumn('pk1', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false])->save();
$table->addColumn('pk2', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'after' => 'pk1'])->save();
$table->save();
...

Which will then, when running the migration with phinx, produce the mysql query (output via -vvv option):

 -- createTable('test')
CREATE TABLE `test` (`pk1` INT(11) unsigned NOT NULL, PRIMARY KEY (`pk1`,`pk2`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT=''  ROW_FORMAT=Dynamic;

To fix this, the save() call after the addColumn() calls should be removed, as there is a final $table->save() call anyways.

$table = $this->table("test", ['id' => false, 'primary_key' => ["pk1", "pk2"], 'engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => "", 'row_format' => "Dynamic"]);
$table->addColumn('pk1', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false]);
$table->addColumn('pk2', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'after' => 'pk1']);
$table->save();

Originally posted by @geiru in #43 (comment)

Version: 0.8.0

I just created a new column named 'number' but the addColumn() method is missing ->update() and therefor this statement fails:
$this->table("restaurant_orderLinkCustomers")->changeColumn('zipcode', 'string', ['null' => true, 'default' => '', 'limit' => 8, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'number'])->update();

because the field is 'number' not yet created and cannot be set after the column 'number'

file: 20170526115828_test1.php

class Test1 extends AbstractMigration
{
    public function change()
    {
        $table = $this->table("restaurant_orderLinkCustomers");
        $table->addColumn('number', 'integer', ['null' => true, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'after' => 'company']);
        $this->table("restaurant_orderLinkCustomers")->changeColumn('zipcode', 'string', ['null' => true, 'default' => '', 'limit' => 8, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'number'])->update();
        $this->table("restaurant_orderLinkCustomers")->changeColumn('address', 'string', ['null' => true, 'default' => '', 'limit' => 128, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'zipcode'])->update();
        $this->table("restaurant_orderLinkCustomers")->changeColumn('phoneNumber', 'string', ['null' => true, 'default' => '', 'limit' => 25, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'address'])->update();
        $this->table("restaurant_orderLinkCustomers")->changeColumn('arrival', 'string', ['null' => true, 'default' => '', 'limit' => 124, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'phoneNumber'])->update();
        $this->table("restaurant_orderLinkCustomers")->changeColumn('resellerChainID', 'integer', ['null' => true, 'default' => '0', 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'after' => 'arrival'])->update();
        $this->table("restaurant_orderLinkCustomers")->changeColumn('createTime', 'timestamp', ['null' => true, 'default' => 'CURRENT_TIMESTAMP', 'after' => 'resellerChainID'])->update();
        $table->save();
    }
}

Error: [PDOException] SQLSTATE[42S22]: Column not found: 1054 Unknown column 'number' in 'restaurant_orderlinkcustomers'

I've been using phinx and phinx-migrations for a while now and I've always selected "N" to overwriting the schema file (the last prompt) as I'm anal about deleting anything. Is it possible to get a little more information as to the differences between these two?

I'm guessing that :

• answering "N" will place all prior migrations into the newly created one, so that if I ever lost a prior migration, I could still use the latest and it would've included the prior. This means that all migration files will slowly increase in file size
• Answering "Y" will only write the changes that were seen into the migration file, ignoring all others. This means that a small change (ie field rename) would generate a small migration file, whereas a brand new table would generate a larger migration file.

I have the following migration generated by v2.1.4, from a MySQL database:

$table = $this->table("bulbs_orders", ['id' => false, 'primary_key' => ["line"], 'engine' => "MyISAM", 'encoding' => "latin1", 'collation' => "latin1_swedish_ci", 'comment' => "bulbs order table"]);
$table->addColumn('line', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'identity' => 'enable']);
$table->addColumn('item', 'integer', ['null' => false, 'default' => '0', 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'after' => 'line']);
$table->addColumn('size_code', 'string', ['null' => false, 'limit' => 1, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'item']);
$table->addColumn('quan', 'integer', ['null' => false, 'default' => '0', 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'after' => 'size_code']);
$table->addColumn('orderID', 'string', ['null' => false, 'limit' => 255, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'quan']);
$table->addColumn('date', 'timestamp', ['null' => false, 'default' => 'CURRENT_TIMESTAMP', 'after' => 'orderID']);
$table->addColumn('ordnum', 'integer', ['null' => true, 'default' => '0', 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'after' => 'date']);
$table->addColumn('browser_info', 'text', ['null' => true, 'limit' => 65535, 'collation' => "latin1_swedish_ci", 'encoding' => "latin1", 'after' => 'ordnum']);
$table->save();
if($this->table('bulbs_orders')->hasIndex('item')) {
     $this->table("bulbs_orders")->removeIndexByName('item');
}
$this->table("bulbs_orders")->addIndex(['item','size_code','orderID','ordnum'], ['name' => "item", 'unique' => true])->save();

When I run the migration on an SQLite database, it creates the table only with an id column:

If I remove the 'identity' => 'enable' from the first column definition (for column "line"), the migration will create the table and all columns, but "line" will not be a primary key:

Can I choose a specific table for generating?
For example: phinx-migrations generate --table OnlyOneTable

I'm using generator to generate a migration from a database but when it connects I get the following error :

Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths

• /Users/mikehingley/Code/Website/db/migrations
  using seed paths
• /Users/mikehingley/Code/Website/db/seeds
  warning no environment specified, defaulting to: production
  using database cchits
  using config file /Users/mikehingley/Code/Website/phinx.yml
  using migration path /Users/mikehingley/Code/Website/db/migrations
  using schema file /Users/mikehingley/Code/Website/db/migrations/schema.php

In MysqlAdapter.php line 121:

There was a problem connecting to the database: SQLSTATE[HY000] [1045] Access denied for user 'vagrant'@'_g
ateway' (using password: YES)

generate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [--path PATH] [--name NAME] [--overwrite]

Phinx 0.11 was released today to fix a breaking change in symphony/console - see
cakephp/phinx#1585

Would you kindly bump the migration generator to accept phinx ^0.11 as well?

Thanks lots, P

Hi,
bit value literals are stored as b'0' which causes issues with the generated migrations as most of the places for the PHP strings the generator is using single quotes as well.

I noticed it in default values but worth investigating with the other values as well.

I created a pull request to fix it for defaults.

A new table with one column and an index on said column generates code like this:

   $table = $this->table("tbname");
   $table->removeIndexByName("idxcol");
   $table = $this->table("tbname");
   $table->addIndex(['idxcol'], ['name' => "idxcol", 'unique' => false])->save();

I haven't spent enough time on the internals, but I guess the second ->table call somehow discards the previous removeIndexByName (?), because by the end of the execution, Phinx complains about

Migrations pending after execution

... and bit of debugging showed that the removeIndex actions were those that were pending. If I append to the removeIndexByName() call a save(), it errors out (as it should, because the index doesn't exist).

Thing is, it works as it is, but it doesn't exit cleanly, which will definitely be confusing and make the user think something was wrong (like me). Also, I guess this could hide actual issues with migrations.

My proposed fix is to simply not generate removeIndexByName when the index doesn't exist, but I imagine there might be cases where indexes do exist and are being changed, so maybe those should be revised too, in case they might not be working.

Versions (just in case)

• odan/phinx-migrations-generator - 3.0.1
• robmorgan/phinx - 0.10.6

Hi,

I encountered this issue.
The generated code is (this is part of an initial migration script):

$table = $this->table("artists_spotify", ['id' => false, 'primary_key' => ["artistid", "spotifyid", "country"], 'engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => ""]);
if ($this->table('artists_spotify')->hasColumn('id')) {
    $this->table("artists_spotify")->changeColumn('id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'identity' => 'enable'])->update();
} else {
    $this->table("artists_spotify")->addColumn('id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'identity' => 'enable'])->update();
}
$table->addColumn('last_updated', 'datetime', ['null' => false, 'after' => 'id']);
$table->addColumn('artistid', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'after' => 'last_updated']);
$table->addColumn('spotifyid', 'string', ['null' => false, 'limit' => 50, 'collation' => "utf8_general_ci", 'encoding' => "utf8", 'after' => 'artistid']);
$table->addColumn('preview_url', 'string', ['null' => false, 'limit' => 150, 'collation' => "utf8_general_ci", 'encoding' => "utf8", 'after' => 'spotifyid']);
$table->addColumn('country', 'string', ['null' => false, 'limit' => 4, 'collation' => "utf8_general_ci", 'encoding' => "utf8", 'after' => 'preview_url']);
$table->save();
if($this->table('artists_spotify')->hasIndex('artistid_spotifyid_country')) {
    $this->table("artists_spotify")->removeIndexByName('artistid_spotifyid_country');
}
$this->table("artists_spotify")->addIndex(['artistid','spotifyid','country'], ['name' => "artistid_spotifyid_country", 'unique' => true])->save();
if($this->table('artists_spotify')->hasIndex('id')) {
    $this->table("artists_spotify")->removeIndexByName('id');
}
$this->table("artists_spotify")->addIndex(['id'], ['name' => "id", 'unique' => false])->save();

Throws:
In PdoAdapter.php line 145:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'artists_spotify' doesn't exist

Hi,

I'm currently doing migrations in a bad way (storing whole .sql files in git) and I'm looking to move to a proper migration system.

I'm trying to use this tool (alongside Phinx, obviously :D) to replace this. However, I'm confused how I can manage actual DB data?

It seems that the seeds in Phinx is the way to go, but also it seems that migrations could also handle 'inserts' of new data/rows?

Any advice on how I can auto-generate a migration/seed using quto-extracted data from the DB?

Is it possible to filter tables when generating migrations? For example, I would like to filter all tables that match a particular pattern. Is it possible to filter the tables list using a call back function or regex pattern?

Every developer has it's own database name, so 'TABLE_SCHEMA' generated in file will be differ. For example if I commit my schema.php it will override for others.

Hi, I am getting this kind of error with PHP7.3 :

./bin/phinx-migrations generate
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.php
using config parser php
using migration paths
 - /usr/local/www/test/database/migrations
using seed paths
 - /usr/local/www/test/database/seeds
warning no environment specified, defaulting to: current
using database services
using config file /usr/local/www/test/phinx.php

In GenerateCommand.php line 105:

  No migration paths set in your Phinx configuration file.

Problem that I spoted is in this line:
$migrationsPaths = !is_array($migrationsPaths) ? (array)$migrationsPaths : [];
this just make variable empty.

• float (fix decimals deletion)
• enum
• Multiple composite relations in teble

Currently, the generated migration scripts includes a $this->table(...)->save() call for every table, including the unmodified tables. I have a database with 215 tables.

Is it possible to filter out the entries for unmodified tables, so that it is easier to review the generated migration code?

The generated code for tables tries to save() after every addColumn() call, but that causes an error if the primary key requires more than 1 column.

Hi,

We like to keep our db connection info, and therefore the phinx.yml, outside of our web root folder and the db/migrations inside the web folder. For example:

-private
--phinx.yml
-web
--db
---migrations

I got this to work for Phinx with a wildcard like below. We use the wildcard because the name of the webfolder is unfortunately different in development and production.

paths:
migrations: ../*/db/migrations

However it seems to break the phinx-migrations-generator.

Cheers,

Jonne

Apologies for my ignorance as I have no experience with your solution or phinx until now, but how should i go about using your solution after Ive generated the default schema, and initial '20170308233115_Test.php', etc?

Can I know make changes directly to the mysql db schema and then run your generator to create a new file with the alterations?

How should I go about implementing those alterations using your solution on a new db that needs its schema migrated to the new on that i generated?

Thanks for your time and patience. I truly appreciate it.

I am going to use your package to generate phinx migrations for our existing databases. Is it possible to seed the databases using valid existing table rows?

I know we can seed fake test data, buthopefully there's a way we can generate the same but for existing row seeding?

This check returns always FALSE because isset() is case sensitive when testing assicative arrays (reference).

As SHOW INDEX FROM {table}; don't usually return columns names in uppercase, this isset($indexData['NON_UNIQUE']) == isset($indexData['Non_unique']) is not true.

As a fix I suggest to use array_map and normalize all columns names to uppercase.

Happy to do the PR.

I am using the latest version (3.1.0) and unfortunately have the same issue: Multi column primary keys cannot be generated. The problem is, that after every addColumn() in the generated migration file, there is a call to save().

The table:

CREATE TABLE `test` (
  `pk1` int(11) unsigned NOT NULL,
  `pk2` int(11) unsigned NOT NULL,
  PRIMARY KEY (`pk1`,`pk2`)
) ENGINE=InnoDB

Will produce a migration file like this:

...
$table = $this->table("test", ['id' => false, 'primary_key' => ["pk1", "pk2"], 'engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => "", 'row_format' => "Dynamic"]);
$table->addColumn('pk1', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false])->save();
$table->addColumn('pk2', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'after' => 'pk1'])->save();
$table->save();
...

Which will then, when running the migration with phinx, produce the mysql query (output via -vvv option):

 -- createTable('test')
CREATE TABLE `test` (`pk1` INT(11) unsigned NOT NULL, PRIMARY KEY (`pk1`,`pk2`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT=''  ROW_FORMAT=Dynamic;

To fix this, the save() call after the addColumn() calls should be removed, as there is a final $table->save() call anyways.

$table = $this->table("test", ['id' => false, 'primary_key' => ["pk1", "pk2"], 'engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => "", 'row_format' => "Dynamic"]);
$table->addColumn('pk1', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false]);
$table->addColumn('pk2', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'after' => 'pk1']);
$table->save();

After adding a new table to my database, and generating the migrate script for it, everything seemed well. the actual migration went flawlessly, and worked every time.

The error happens when I try to revert those migrations. The generated script checks whether a column named 'id' already exists, and modifies it if it does. The problem is that the change() method cannot handle changeColumn(), and crashes showing the following error.

Fatal error: Uncaught Phinx\Migration\IrreversibleMigrationException: Cannot reverse a "changeColumn" command in [PATH]\robmorgan\phinx\src\Phinx\Db\Adapter\ProxyAdapter.php:228

This is the generated migrate script:

    <?php

    use Phinx\Migration\AbstractMigration;
    use Phinx\Db\Adapter\MysqlAdapter;

    class AddNewsTable extends AbstractMigration
    {
        public function change()
        {
          $table = $this->table("news", ['engine' => "InnoDB", 'encoding' => "utf8", 'collation' => "utf8_general_ci", 'comment' => ""]);
      $table->save();
      if ($this->table('news')->hasColumn('id')) {
          $this->table("news")->changeColumn('id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'identity' => 'enable'])->update();
      } else {
          $this->table("news")->addColumn('id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'identity' => 'enable'])->update();
      }
      $table->addColumn('title', 'string', ['null' => false, 'limit' => 255, 'collation' => "utf8_general_ci", 'encoding' => "utf8", 'after' => 'id'])->update();
      $table->addColumn('body', 'string', ['null' => false, 'limit' => 255, 'collation' => "utf8_general_ci", 'encoding' => "utf8", 'after' => 'title'])->update();
      $table->addColumn('created_at', 'datetime', ['null' => false, 'default' => 'CURRENT_TIMESTAMP', 'after' => 'body'])->update();
      $table->save();
    }
    }

Is deleting and recreating the column a possible solution?

Great project and thanks!

when execute vendor/bin/phinx migrate
It will be returned

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def
inition; there can be only one auto column and it must be defined as a key

and I confirm my SQL can normal use , please help me to look this 。
thank you

Hello,

are you planning to support PostgreSQL?
If not it seems like I would only need to implement another Adapter class beside the MySqlAdapter.php am I right or is there some other "mysql-only" logic in your script?

Best Regards

Having multiple migration folders seems to crash this tool.

In AbstractCommand.php line 313:

  Migration directory "{app/db/migrations,vendor/curtiscarlson/module/db/migrations}" does not exist

I am on windows so that might be part of the issue

Hello!

I have fully implemented a project using this library and I happen to have many mysql databases running in the same instance and referencing each other.
What happens is that everything works pretty fine except for cross database foreign-key constraints, I will supply a local example (columns are in portuguese).

Consider this table and note the first constraint (which references another table prefixed with another database name):

CREATE TABLE `planos_ambitos` (
 `visitantes_planos_id` bigint(20) unsigned NOT NULL,
 `ambitos_id` int(10) unsigned NOT NULL,
 PRIMARY KEY (`visitantes_planos_id`,`ambitos_id`),
 KEY `ambitos_id` (`ambitos_id`) USING BTREE,
 KEY `visitantes_planos_id` (`visitantes_planos_id`) USING BTREE,
 CONSTRAINT `planos_ambitos_ambitos_id_fk` FOREIGN KEY (`ambitos_id`) REFERENCES `geo`.`ambitos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `planos_ambitos_visitantes_planos_id` FOREIGN KEY (`visitantes_planos_id`) REFERENCES `visitantes_planos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Upon executing phinx-generator generate, this is generated:

$this->table('planos_ambitos', [
    'id' => false,
    'primary_key' => ['visitantes_planos_id', 'ambitos_id'],
    'engine' => 'InnoDB',
    'encoding' => 'utf8',
    'collation' => 'utf8_general_ci',
    'comment' => '',
    'row_format' => 'DYNAMIC',
])
->addColumn('visitantes_planos_id', 'biginteger', [
    'null' => false,
    'limit' => MysqlAdapter::INT_BIG,
    'signed' => false,
])
(... cut for brevity ...)
->addForeignKey('visitantes_planos_id', 'visitantes_planos', 'id', [
    'constraint' => 'planos_ambitos_visitantes_planos_id',
    'update' => 'CASCADE',
    'delete' => 'CASCADE',
])
->create();

This is missing:

->addForeignKey('ambitos_id', 'geo.ambitos', 'id', [
    'constraint' => 'planos_ambitos_ambitos_id_fk',
    'update' => 'CASCADE',
    'delete' => 'CASCADE',
])

Versions (all latest as of this issue):

phinx 0.11.1
phinx-migrations-generator 4.3.0

Would love for an input on this matter, you library rocks!

Renato

From what I can see, and I've been using phinx-migrations for a while now, there's no way of creating any source control options for a MySQL view, just tables and data. Would that be a fair observation?

I've just tried running this tool on a MySQL database with compound indexes, and found that only one of the indexed columns gets exported out of many.

I've dumped the output from the "real" database and the newly migrated one and compared the two dumps.

What is expected:

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_and_category_id` (`id`,`category_id`)
) ENGINE=InnoDB;

What actually happens:

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_and_category_id` (`category_id`)
) ENGINE=InnoDB;

I have a similar index with 3 columns - it appears only the final column in the index is being exported.

It should be a case of adapting the code that generates the addIndex statements to create an array of column names.

e.g.

$this->table("items")->addIndex(['id', 'category_id'], array('name' => "id_and_category_id", 'unique' => false))->save();

Hi there, it worked great, thanks for your work.

I've got an issue, the foreign keys are not getting generated automatically. I checked the example configuration you put on readme but not sure about it.

Any help would be appreciated.

Any plan to make it compatible with new version of Phinx ?

Hi, I am trying to make migration but getting

An invalid column type "double" was specified for column "rate".

mysql5.7
php72
Phinx by CakePHP - https://phinx.org. 0.9.2

How to solve it ?

Is this a typo? $output is being overwritten with the new code, instead of appending. Shouldn't it be $output[]?

A MySQL table with the CREATE script:

CREATE TABLE `accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(10) unsigned NOT NULL,
  `status` int(10) unsigned NOT NULL DEFAULT '1',
  `username` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pwhash` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `staff_level` int(10) unsigned NOT NULL DEFAULT '1',
  `display` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `logo` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_ui` (`username`),
  KEY `type_i` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

is turned into the following phinx php:

        $table = $this->table("accounts", ['id' => false, 'primary_key' => ["id"], 'engine' => "InnoDB", 'encoding' => "utf8mb4", 'collation' => "utf8mb4_unicode_ci", 'comment' => "", 'row_format' => "Dynamic"]);
        $table->addColumn('display', 'string', ['null' => false, 'default' => "", 'limit' => 64, 'collation' => "utf8mb4_unicode_ci", 'encoding' => "utf8mb4"])->save();
        $table->addColumn('id', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'identity' => 'enable', 'after' => 'display'])->save();
        $table->addColumn('logo', 'integer', ['null' => true, 'default' => "0", 'limit' => MysqlAdapter::INT_TINY, 'precision' => 3, 'after' => 'id'])->save();
        $table->addColumn('pwhash', 'string', ['null' => false, 'limit' => 64, 'collation' => "utf8mb4_unicode_ci", 'encoding' => "utf8mb4", 'after' => 'logo'])->save();
        $table->addColumn('staff_level', 'integer', ['null' => false, 'default' => "1", 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'after' => 'pwhash'])->save();
        $table->addColumn('status', 'integer', ['null' => false, 'default' => "1", 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'after' => 'staff_level'])->save();
        $table->addColumn('type', 'integer', ['null' => false, 'limit' => MysqlAdapter::INT_REGULAR, 'precision' => 10, 'signed' => false, 'after' => 'status'])->save();
        $table->addColumn('username', 'string', ['null' => false, 'limit' => 64, 'collation' => "utf8mb4_unicode_ci", 'encoding' => "utf8mb4", 'after' => 'type'])->save();
        $table->save();
        $table = $this->table("accounts");
        $table->removeIndexByName("username_ui");
        $table = $this->table("accounts");
        $table->addIndex(['username'], ['name' => "username_ui", 'unique' => true])->save();
        $table = $this->table("accounts");
        $table->removeIndexByName("type_i");
        $table = $this->table("accounts");
        $table->addIndex(['type'], ['name' => "type_i", 'unique' => false])->save();

When run by phinx migrate it results in the following error: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table

If the generated code is changed (manually) to maintain the order of the CREATE script (or, at least id first) then it migrates correctly.

We are looking to implement a database migration system for an already existing database. Is there anyway to generate initial migrations per table, instead of one giant migration with all the tables?

It's a huge database with about 71 tables, so that initial migration file gets to be pretty huge and unruly

Adding table_prefix: xxx_ to phinx.yml throws exception below.

PHP Fatal error: Call to undefined method Phinx\Db\Adapter\TablePrefixAdapter::getConnection() in /home/ubuntu/workspace/vendor/odan/phinx-migrations-generator/src/Migration/Command/GenerateCommand.php on line 95

cheers