cakephp / cakephp/phinx

Can not changeColumn with TIMESTAMP and limit

Open
#1,445 3 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
PHP
Stars
4.5k
Forks
884
PR merge metrics
No merged PRs in 30d

Description

  • CakePHP Version: 3.6.11.
  • Migrations plugin version: 2.0.0.
  • Database server: MySQL
  • PHP Version: 7.1.13
What you did

Created migration

use Cake\ORM\TableRegistry;
use Migrations\AbstractMigration;

class UpdateTimestamps extends AbstractMigration
{
    public function change() {
        $table = $this->table(TableRegistry::get('Messages')->getTable());
        $table->changeColumn(
            'created_at',
            'timestamp',
            [
                'default' => 'CURRENT_TIMESTAMP(4)',
                'null'    => false,
                'limit'   => 4,
            ]
        );
        $table->update();
    }
}

With Xdebug found that in class PdoAdapter method execute():
vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:167

SQL was:
ALTER TABLE messagesCHANGEcreated_at created_at TIMESTAMP(4) NOT NULL DEFAULT 'CURRENT_TIMESTAMP(4)'

Expected Behavior

Must work ))

Actual Behavior

As result I've got exception:

Exception: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at' in [/var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php, line 167]
2018-09-28 16:05:30 Error: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at' in /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php on line 167
Stack Trace:
#0 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(167): PDO->exec('ALTER TABLE `se...')
#1 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Util/AlterInstructions.php(126): Phinx\Db\Adapter\PdoAdapter->execute('ALTER TABLE `se...')
#2 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(518): Phinx\Db\Util\AlterInstructions->execute('ALTER TABLE `se...', Array)
#3 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(877): Phinx\Db\Adapter\PdoAdapter->executeAlterSteps('sepa_messages', Object(Phinx\Db\Util\AlterInstructions))
#4 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(477): Phinx\Db\Adapter\PdoAdapter->executeActions(Object(Phinx\Db\Table\Table), Array)
#5 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/TimedOutputAdapter.php(406): Phinx\Db\Adapter\AdapterWrapper->executeActions(Object(Phinx\Db\Table\Table), Array)
#6 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(477): Phinx\Db\Adapter\TimedOutputAdapter->executeActions(Object(Phinx\Db\Table\Table), Array)
#7 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(144): Phinx\Db\Adapter\AdapterWrapper->executeActions(Object(Phinx\Db\Table\Table), Array)
#8 /var/www/sepagateway/vendor/cakephp/cakephp/src/Collection/CollectionTrait.php(51): Phinx\Db\Plan\Plan->Phinx\Db\Plan\{closure}(Object(Phinx\Db\Plan\AlterTable), 'sepa_messages')
#9 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(145): Cake\Collection\Collection->each(Object(Closure))
#10 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(717): Phinx\Db\Plan\Plan->execute(Object(Migrations\CakeAdapter))
#11 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(624): Phinx\Db\Table->executeActions(true)
#12 /var/www/sepagateway/vendor/cakephp/migrations/src/Table.php(103): Phinx\Db\Table->update()
#13 /var/www/sepagateway/plugins/Sepa/config/Migrations/20180928152624_UpdateTimestamps.php(19): Migrations\Table->update()
#14 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(125): UpdateTimestamps->change()
#15 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(387): Phinx\Migration\Manager\Environment->executeMigration(Object(UpdateTimestamps), 'up', false)
#16 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(361): Phinx\Migration\Manager->executeMigration('default', Object(UpdateTimestamps), 'up', false)
#17 /var/www/sepagateway/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(121): Phinx\Migration\Manager->migrate('default', 20180928152624, false)
#18 /var/www/sepagateway/vendor/cakephp/migrations/src/Command/CommandTrait.php(35): Phinx\Console\Command\Migrate->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /var/www/sepagateway/vendor/cakephp/migrations/src/Command/Migrate.php(67): Migrations\Command\Migrate->parentExecute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 /var/www/sepagateway/vendor/symfony/console/Command/Command.php(251): Migrations\Command\Migrate->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#21 /var/www/sepagateway/vendor/symfony/console/Application.php(886): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#22 /var/www/sepagateway/vendor/symfony/console/Application.php(262): Symfony\Component\Console\Application->doRunCommand(Object(Migrations\Command\Migrate), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#23 /var/www/sepagateway/vendor/symfony/console/Application.php(145): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#24 /var/www/sepagateway/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))
#25 /var/www/sepagateway/vendor/cakephp/cakephp/src/Console/Shell.php(532): Migrations\Shell\MigrationsShell->main('migrations', 'migrate')
#26 /var/www/sepagateway/vendor/cakephp/migrations/src/Shell/MigrationsShell.php(164): Cake\Console\Shell->runCommand(Array, true, Array)
#27 /var/www/sepagateway/vendor/cakephp/cakephp/src/Console/CommandRunner.php(342): Migrations\Shell\MigrationsShell->runCommand(Array, true)
#28 /var/www/sepagateway/vendor/cakephp/cakephp/src/Console/CommandRunner.php(164): Cake\Console\CommandRunner->runShell(Object(Migrations\Shell\MigrationsShell), Array)
#29 /var/www/sepagateway/bin/cake.php(12): Cake\Console\CommandRunner->run(Array)
#30 {main}

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start at vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:167 and trace how changeColumn options produce the shown ALTER TABLE SQL. Reproduce the migration with MySQL and verify that CURRENT_TIMESTAMP(4) is emitted as a valid default rather than an invalid quoted value, while preserving the TIMESTAMP limit and NOT NULL behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, php
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.