Build 332 - Invalid timestamps with MySQL strict mode

Release Note 4

There is an issue that exists between MySQL versions that use a "strict mode" and the version of Laravel used by OctoberCMS prior to the stable release.

When MySQL in strict mode encounters a timestamp column with an invalid default value, it will produce a fatal error. Prior to the stable release, Laravel would set timestamp columns with an invalid default value.

Existing plugins will need to update their database structure using a patch. This patch will address users who do not have strict mode enabled currently, but later have it enabled by upgrading / reinstalling MySQL 5.7 and above. See action required for authors below.

Issue summary

  • All copies of MySQL version 5.7 and above will have the strict mode applied.
  • The older version of Laravel (5.0) will set timestamps with a default of "0".
  • The October RC and Beta releases use Laravel 5.0 and are affected.
  • This issue has been fixed in the latest version of October using Build 332 and above.
  • All plugins that use timestamps need to be patched (see below).

Workaround solution for users

If you are running MySQL 5.7 or above and encounter errors when updating October. You can fix the problem by setting the strict value to false in your config/database.php file. This will prefix every database query with a directive to disable MySQL's strict mode feature and allow the problematic query to run normally.

'connections' => [

    'mysql' => [
        'driver'    => 'mysql',
        'host'      => 'localhost',
        'database'  => 'ozlance3',
        'username'  => 'root',
        'password'  => '',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false
    ],

    //...
]

Action required for authors

If you are an author of a plugin, you need to create a migration script to remove the invalid default values on existing timestamps. This will retroactively apply the fix to the database tables. For example, if your schema looks like this, it will need to be patched:

    Schema::create('users', function($table)
    {
        // ...
        $table->timestamp('activated_at')->nullable();
        $table->timestamp('last_login');
        $table->timestamps();
    });

Note: If the timestamp has already been declared with nullable() then it does not need to be patched.

See this commit on the RainLab.Users plugin for an example.

The migration file should first call DbDongle::disableStrictMode to prevent any errors during execution. Then the DbDongle::convertTimestamps method should be called for every table that contains a timestamps() schema reference. The first argument passed to convertTimestamps should be the table name, and the second argument should be the names of each timestamp column. If the second argument is not used, it will default to [created_at, updated_at].

Here is an example update_timestamp_nullable.php migration file:

<?php namespace RainLab\User\Updates;

use Schema;
use DbDongle;
use October\Rain\Database\Updates\Migration;

class UpdateTimestampsNullable extends Migration
{

    public function up()
    {
        DbDongle::disableStrictMode();

        DbDongle::convertTimestamps('users', ['created_at', 'updated_at', 'last_login']);
        DbDongle::convertTimestamps('user_groups');
    }

    public function down()
    {
        // ...
    }

}

Once the migration file is finished, you should then updated the version.yaml file. For example:

1.2.5:
    - Database maintenance. Updated all timestamp columns to be nullable.
    - update_timestamp_nullable.php
comments powered by Disqus