Question 40036 in PHP Question posted in
Official manual can be found here.

CakePHP 4 datetime migrations error 'Incorrect datetime value'

I am developing an app in PHP 7.4 (cakephp 4.4).

I am trying to use migrations to properly update my tables using cakephp bake migrations feature, but I have an issue with datetime columns.

Here is my code :

bin/cake bake migration AddInvitationTokenToUsers invitation_token:string:unique invitation_token_expires_at:datetime

The output :

Creating file /Applications/MAMP/htdocs/app/config/Migrations/20230416152709_AddInvitationTokenToUsers.php
Wrote `/Applications/MAMP/htdocs/app/config/Migrations/20230416152709_AddInvitationTokenToUsers.php`

And then I launch the migration :

bin/cake migrations migrate

And I have this error :

PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'invitation_token_expires_at' at row 1 in /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:192
Stack trace:
#0 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(192): PDO->exec('ALTER TABLE `us...')
#1 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Util/AlterInstructions.php(108): PhinxDbAdapterPdoAdapter->execute('ALTER TABLE `us...')
#2 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(626): PhinxDbUtilAlterInstructions->execute('ALTER TABLE `us...', Array)
#3 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(1001): PhinxDbAdapterPdoAdapter->executeAlterSteps('users', Object(PhinxDbUtilAlterInstructions))
#4 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(477): PhinxDbAdapterPdoAdapter->executeActions(Object(PhinxDbTableTable), Array)
#5 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/TimedOutputAdapter.php(420): PhinxDbAdapterAdapterWrapper->executeActions(Object(PhinxDbTableTable), Array)
#6 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(477): PhinxDbAdapterTimedOutputAdapter->executeActions(Object(PhinxDbTableTable), Array)
#7 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(151): PhinxDbAdapterAdapterWrapper->executeActions(Object(PhinxDbTableTable), Array)
#8 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(719): PhinxDbPlanPlan->execute(Object(MigrationsCakeAdapter))
#9 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(627): PhinxDbTable->executeActions(true)
#10 /Applications/MAMP/htdocs/app/vendor/cakephp/migrations/src/Table.php(152): PhinxDbTable->update()
#11 /Applications/MAMP/htdocs/app/config/Migrations/20230416144739_AddInvitationTokenToUsers.php(27): MigrationsTable->update()
#12 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(108): AddInvitationTokenToUsers->change()
#13 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(388): PhinxMigrationManagerEnvironment->executeMigration(Object(AddInvitationTokenToUsers), 'up', false)
#14 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(359): PhinxMigrationManager->executeMigration('default', Object(AddInvitationTokenToUsers), 'up', false)
#15 /Applications/MAMP/htdocs/app/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(122): PhinxMigrationManager->migrate('default', 20230416144739, false)
#16 /Applications/MAMP/htdocs/app/vendor/cakephp/migrations/src/Command/Phinx/CommandTrait.php(37): PhinxConsoleCommandMigrate->execute(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#17 /Applications/MAMP/htdocs/app/vendor/cakephp/migrations/src/Command/Phinx/Migrate.php(85): MigrationsCommandPhinxMigrate->parentExecute(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#18 /Applications/MAMP/htdocs/app/vendor/symfony/console/Command/Command.php(298): MigrationsCommandPhinxMigrate->execute(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#19 /Applications/MAMP/htdocs/app/vendor/symfony/console/Application.php(1040): SymfonyComponentConsoleCommandCommand->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#20 /Applications/MAMP/htdocs/app/vendor/symfony/console/Application.php(301): SymfonyComponentConsoleApplication->doRunCommand(Object(MigrationsCommandPhinxMigrate), Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#21 /Applications/MAMP/htdocs/app/vendor/symfony/console/Application.php(171): SymfonyComponentConsoleApplication->doRun(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#22 /Applications/MAMP/htdocs/app/vendor/cakephp/migrations/src/Command/MigrationsCommand.php(126): SymfonyComponentConsoleApplication->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#23 /Applications/MAMP/htdocs/app/vendor/cakephp/cakephp/src/Console/BaseCommand.php(189): MigrationsCommandMigrationsCommand->execute(Object(CakeConsoleArguments), Object(CakeConsoleConsoleIo))
#24 /Applications/MAMP/htdocs/app/vendor/cakephp/migrations/src/Command/MigrationsCommand.php(198): CakeConsoleBaseCommand->run(Array, Object(CakeConsoleConsoleIo))
#25 /Applications/MAMP/htdocs/app/vendor/cakephp/cakephp/src/Console/CommandRunner.php(334): MigrationsCommandMigrationsCommand->run(Array, Object(CakeConsoleConsoleIo))
#26 /Applications/MAMP/htdocs/app/vendor/cakephp/cakephp/src/Console/CommandRunner.php(172): CakeConsoleCommandRunner->runCommand(Object(MigrationsCommandMigrationsMigrateCommand), Array, Object(CakeConsoleConsoleIo))
#27 /Applications/MAMP/htdocs/app/bin/cake.php(12): CakeConsoleCommandRunner->run(Array)
#28 {main}

My migration file is like this :

<?php
declare(strict_types=1);

use MigrationsAbstractMigration;

class AddInvitationTokenToUsers extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     * @return void
     */
    public function change(): void
    {
        $table = $this->table('users');
        $table->addColumn('invitation_token', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('invitation_token_expires_at', 'datetime', [
            'default' => null,
            'null' => false,
        ]);
        $table->addIndex([
            'invitation_token',
        
            ], [
            'name' => 'UNIQUE_INVITATION_TOKEN',
            'unique' => true,
        ]);
        $table->update();
    }
}

Do you have any idea on the reason of this issue ?

Thanks

Tags:

2

Answers


  1. 0

    Your sql_mode config probably doesn’t allow zero dates, check how NO_ZERO_DATE works.

    Since you have existing rows in your tables, you should either provide a valid datetime default value for the new column according to your DBMS configuration (for example 'CURRENT_TIMESTAMP'), or make the column nullable.

    Since the column is an expiration time for tokens, you most likely don’t want to set a datetime value as the default, so you’d go for the latter, ie change the value for the null option to true to make the column nullable and thus use null as the default.

    Login or Signup to reply.
  2. 0

    From https://dev.mysql.com/doc/refman/8.0/en/datetime.html:

    The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD hh:mm:ss’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

    Your ‘0000-00-00 00:00:00’ is less than the supported values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search