MigrationDiff with a longblob column fails to apply

[dmromanov]

This is a (multiple allowed):

• bug

• enhancement

• feature-discussion (RFC)

• CakePHP Version: 3.6.1

• Migrations plugin version: 1.8.1

• Bake plugin version (if relevant): 1.7.1

• Database server (MySQL, SQLite, Postgres): 5.7.21 MySQL Community Server

• PHP Version: PHP 7.2.4

• Platform / OS: CentOS release 6.9 (Final) 64

What you did

I've added a "LONGBLOB" column to a table.
Run ./bin/cake bake migration_diff AddContentFieldToFilesTable
Resulting migration is

<?php
use Migrations\AbstractMigration;

class AddContentFieldToFilesTable extends AbstractMigration
{

    public function up()
    {

        $this->table('files')
            ->addColumn('content', 'binary', [
                'after' => 'hash_sha512',
                'comment' => 'Contents',
                'default' => null,
                'length' => 4294967295,
                'null' => true,
            ])
            ->update();
    }

    public function down()
    {

        $this->table('files')
            ->removeColumn('content')
            ->update();
    }
}

Tried to apply the migration on another server (same characteristics)

Expected Behavior

Expected the migration to apply successfuly resulting in a new column in a table.

Actual Behavior

Migration failed with the following message.

================                                                                                                                         
  Exception: SQLSTATE[42000]: Syntax error or access violation: 1074 Column length too big for column 'content' (max = 255); use BLOB or   
  TEXT instead in [/trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php, line 338]       
  2018-04-24 08:50:51 Error: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1074 Column length too big for column 'con  
  tent' (max = 255); use BLOB or TEXT instead in /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/  
  PdoAdapter.php on line 338                                                                                                               
  Stack Trace:                                                                                                                             
  #0 /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(338): PDO->exec('ALTER TABLE   
  `fi...')                                                                                                                                 
  #1 /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php(422): Phinx\Db\Adapter\PdoA  
  dapter->execute('ALTER TABLE `fi...')                                                                                                    
  #2 /trimmedpath/releases/93/vendor/cakephp/migrations/src/CakeAdapter.php(513): Phinx\Db\Adapter\MysqlAdapter->addCo  
  lumn(Object(Migrations\Table), Object(Phinx\Db\Table\Column))                                                                            
  #3 /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(628): Migrations\CakeAdapter->addColumn(Ob  
  ject(Migrations\Table), Object(Phinx\Db\Table\Column))                                                                                   
  #4 /trimmedpath/releases/93/vendor/cakephp/migrations/src/Table.php(106): Phinx\Db\Table->update()                    
  #5 /trimmedpath/releases/93/config/Migrations/20180423152632_AddContentFieldToFilesTable.php(18): Migrations\Table->  
  update()                                                                                                                                 
  #6 /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(125): AddContentField  
  ToFilesTable->up()                                                                                                                       
  #7 /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(366): Phinx\Migration\Manager\Env  
  ironment->executeMigration(Object(AddContentFieldToFilesTable), 'up')                                                                    
  #8 /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(342): Phinx\Migration\Manager->ex  
  ecuteMigration('default', Object(AddContentFieldToFilesTable), 'up')                                                                     
  #9 /trimmedpath/releases/93/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(113): Phinx\Migration\Manag  
  er->migrate('default', 20180423154431)                                                                                                   
  #10 /trimmedpath/releases/93/vendor/cakephp/migrations/src/Command/CommandTrait.php(35): Phinx\Console\Command\Migra  
  te->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))                   
  #11 /trimmedpath/releases/93/vendor/cakephp/migrations/src/Command/Migrate.php(65): Migrations\Command\Migrate->pare  
  ntExecute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))                     
  #12 /trimmedpath/releases/93/vendor/symfony/console/Command/Command.php(252): Migrations\Command\Migrate->execute(Ob  
  ject(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))                                 
  #13 /trimmedpath/releases/93/vendor/symfony/console/Application.php(946): Symfony\Component\Console\Command\Command-  
  >run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))                          
  #14 /trimmedpath/releases/93/vendor/symfony/console/Application.php(248): Symfony\Component\Console\Application->doR  
  unCommand(Object(Migrations\Command\Migrate), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Outpu  
  t\ConsoleOutput))                                                                                                                        
  #15 /trimmedpath/releases/93/vendor/symfony/console/Application.php(148): Symfony\Component\Console\Application->doR  
  un(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))                            
  #16 /trimmedpath/releases/93/vendor/cakephp/migrations/src/Shell/MigrationsShell.php(108): Symfony\Component\Console  
  \Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))             
  #17 /trimmedpath/releases/93/vendor/cakephp/cakephp/src/Console/Shell.php(532): Migrations\Shell\MigrationsShell->ma  
  in('migrations', 'migrate')                                                                                                              
  #18 /trimmedpath/releases/93/vendor/cakephp/migrations/src/Shell/MigrationsShell.php(164): Cake\Console\Shell->runCo  
  mmand(Array, true, Array)                                                                                                                
  #19 /trimmedpath/releases/93/vendor/cakephp/cakephp/src/Console/CommandRunner.php(339): Migrations\Shell\MigrationsS  
  hell->runCommand(Array, true)                                                                                                            
  #20 /trimmedpath/releases/93/vendor/cakephp/cakephp/src/Console/CommandRunner.php(161): Cake\Console\CommandRunner->  
  runShell(Object(Migrations\Shell\MigrationsShell), Array)                                                                                
  #21 /trimmedpath/releases/93/bin/cake.php(12): Cake\Console\CommandRunner->run(Array)                                 
  #22 {main}                                                                                                                               
                                                                                                                                           
                                                                                                                                           
  PHP Warning:  fwrite(): supplied resource is not a valid stream resource in /trimmedpath/releases/93/vendor/cakephp/  
  cakephp/src/Console/ConsoleOutput.php on line 253                                                                                        
  PHP Warning:  fwrite(): supplied resource is not a valid stream resource in /trimmedpath/releases/93/vendor/cakephp/  
  cakephp/src/Console/ConsoleOutput.php on line 253                                                                                        
```                                                                                   `

[dereuromark]

Is this still an issue with 0.12?

[liqueurdetoile]

Hi @dereuromark

I've landed here because I was facing the same trouble ^^

Anyway, I think you can close the issue as it was indeed a misuse. When using Mysql adapter, the binary type is logically mapped as a BINARY column type limited to 255 bytes and that, obviously, yields an error.

One must use blob type to correctly use BLOBs mysql types and valid subtype is then inferred from length.

<?php
use Migrations\AbstractMigration;

class AddContentFieldToFilesTable extends AbstractMigration
{

    public function up()
    {

        $this->table('files')
            ->addColumn('content', 'blob', [
                'after' => 'hash_sha512',
                'comment' => 'Contents',
                'default' => null,
                'length' => 4294967295, // or any number greater will result in LONGBLOB column type
                'null' => true,
            ])
            ->update();
    }

    public function down()
    {

        $this->table('files')
            ->removeColumn('content')
            ->update();
    }
}

I saw you were very active in having it supported in Phynx 😸

To avoid the same trap for binary/blob migration newbies like me, do you think it worth submitting a PR on Phynx to automatically remap 'binary' as 'blob' when limit is greater than 255 ? It won't take much time. Otherwise, migrations plugin docs should be updated maybe ?

[dereuromark]

I am not sure if we should make those magic switches for devs.
Maybe the documentation PR would be a more straight forward solution here.

Or this is only an issue in MySQL and thus the shimming would be helpful here?

[liqueurdetoile]

I think it only applies to Mysql. At first glance, only mysql_adapter have a blob selector trick around data length. Sqlite also uses blob type but without length and subtypes considerations.

By the way, after some testings, it appears that blob subtypes aliases, like mediumblob are not recognized by migrations plugin and are parsed from command line to string, though defined in Phynx Mysql adapter because not registered in AdapterInterface constants.
Even if updated by hand in migration class, it's still throwing an error cause not registered in MysqlAdapter::$specificColumnTypes.

It can be very useful to handle it in order to avoid looking each time at bytes length for Mysql blob subtypes.

I'm on preparing a PR to Phinx to take all these points in account. Plan to look at the doc also. Feel free to say stop 😄