Breaking changes regarding setting a default datetime in v0.15.1

[maximedupre]

Environment

Knex version: v0.15.0
Database + version: mysql v5.7.18
OS: macOS 10.13.5

Bug

This worked in v0.15.0

exports.up = knex => {
    let createTable = (table) => {
        table.increments('id');
        table.integer('user_id').notNullable().unsigned();
        table.dateTime('creation_date').notNullable().defaultTo(knex.fn.now());
        table.dateTime('modification_date');
        table.string('share_token', 200);
        table.boolean('deleted').notNullable().defaultTo(false)
    }

    return knex.schema.createTable('projects', createTable)
};

But in v0.15.1

 create table `projects` (`id` int unsigned not null auto_increment primary key, `user_id` int unsigned not null, `creation_date` datetime(6) notnull default CURRENT_TIMESTAMP, `modification_date` datetime(6), `share_token` varchar(200), `deleted` boolean not null default '0') - Invalid default value for 'creation_date'
Error: Invalid default value for 'creation_date'

[tgriesser]

This is due to #2542 which wasn't running in 0.15.0 and was fixed with a patch for 0.15.1, but looking closer this might not be what we want here.

@ricardograca I didn't look closely at the implementation of #2542, but since it's a configurable parameter and potentially breaking for things like this, I feel like it might be simpler to just make it an optional param:

.datetime('column', 6)

similar to other columns with precision such as string, float, decimal, etc.

[thetutlage]

This also happens with the timestamp field.

Config

{
  client: 'mysql',
  version: '5.7',
  connection: {
    ...
  }
}

With the following code

table.timestamp('some_date').defaultsTo(knex.fn.now())

Returns the following error

Invalid default value for 'some_date'

[elhigu]

the original issue about the reasons for the change is here #2524 basically the default behavior was pretty bad and inconsistent compared to other dialects.

[thetutlage]

@elhigu Thanks for replying quickly. I am trying to get my head around what should be action item here.

So I am correct using knex.fn.now() is not the way to go with MYSQL>5.6?

[tgriesser]

@thetutlage @maximedupre if you specify the precision in the default to match the timestamp that'll be a quick fix. knex.raw("CURRENT_TIMESTAMP(6)") or knex.raw("NOW(6)") but ultimately this should be fixed - it's just a little tricky because the additional arguments in the builder are already used as a boolean to specify whether timestamptz should be used.

Working on a fix that accounts for both without breaking anything.

[ricardograca]

Right, the idea was to make MySQL behave like other dialects, that's why the precision wasn't optional. So in that same vein the knex.fn.now() method should probably be adapted as well to match the precision specification in case it's there.

[elhigu]

@ricardograca was it right that currenty also setting version attr in config to 5.5 restores the old behaviour? Im on mobile right now so I dont want to browse too much, but IIRC plan was to add version attribute for now and keep old functionality as a default and change default behaviour to better precision to be default in 0.16?

[thetutlage]

Yes, setting 5.5 works as per the old behaviour. But it feels weird to use MySQL>5.6 and set version in the config as 5.5.

Again, I agree with the change, but just trying to understand is knex.fn.now() is way to go with the precision timestamp or I should simply stay away from using knex.fn.now()

@ricardograca

Yeah I agree that knex.fn.now() should be updated. However, I am not sure whether it's currently left out?

[thetutlage]

I was looking into the source code and found that knex.fn.now() actually uses CURRENT_TIMESTAMP. https://github.com/tgriesser/knex/blob/232fe9f1517dba927f6a3a1fb1b8842d7c0a4007/src/functionhelper.js#L8

However, MYSQL also supports CURRENT_TIMESTAMP as a function, which takes the fraction. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_current-timestamp

Doing you think, we can add support for same?

FunctionHelper.prototype.now = function(precision) {
  return this.client.raw(`CURRENT_TIMESTAMP(${precision})`);
};