* fix issue when setting datetime or timestamp default values

[dalesmckay]

- CURRENT_TIMESTAMP must match the precision of the defined column
- applies to MySQL 5.6+
- ref: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

eg. $this->table('test_table')->addColumn('created_at', 'timestamp', ['limit' => 6, 'default' => 'CURRENT_TIMESTAMP'])

[dereuromark]

Can you add a test case?

[dereuromark]

@dalesmckay ping

[dalesmckay]

Sorry, I don't really have time at the moment. I'm currently using a forked version with my fix via composer-patches.

The official phinx library does not correctly support timestamps with microseconds (at least for MySQL). Errors will occur due to the default value 'CURRENT_TIMESTAMP' not getting the limit applied.

By default, datetime/timestamp fields only save "seconds": 2020-09-30 20:33:13

However, I need to be able to also access the "microseconds": 2020-09-30 20:33:13.931167

eg. If I want to be able to save/fetch datetime fields with 6 digits of microseconds:

use Phinx\Migration\AbstractMigration;

class CreateTestTable extends AbstractMigration
{
    public function change() {
        // Create new table
        $this->table('test_table')
             ->addColumn('reason', 'string')
             ->addColumn('created_at', 'timestamp', ['limit' => 6, 'default' => 'CURRENT_TIMESTAMP'])
             ->addColumn('updated_at', 'timestamp', ['limit' => 6, 'default' => 'CURRENT_TIMESTAMP'])
             ->addColumn('deleted_at', 'timestamp', ['limit' => 6, 'null' => true, 'default' => null])
             ->create()
        ;
    }
}

This will generate SQL something like:

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reason` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

However, to avoid syntax error, the SQL should be like:

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reason` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `deleted_at` timestamp(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ie. CURRENT_TIMESTAMP vs CURRENT_TIMESTAMP(6)

[dereuromark]

Anything that can be done to finalize this for merge at this point?

[dereuromark]

ping @dalesmckay

[dalesmckay]

I don’t have anything more than the MySQL fix from above that I’ve been using.

I’m not using this with any other DB engine.

[dereuromark]

@MasterOdin Shall we close this then?