mysql database migration concat syntax error

[bmhenry]

Far from being a database expert, so I'm dropping this error here to hopefully get help with or to look at later:

❯ diesel database setup --migration-dir migrations/mysql/
Creating database: alexandrie
Running migration 2019-10-12-193526_initialise
Executing migration script migrations/mysql/2019-10-12-193526_initialise/up.sql
Failed with: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat(utc_date(), " ", utc_time()),
    updated_at varchar(25) not null default' at line 5

Error generated when trying to set up a new mysql database

Mysql version: 8.0.19

[Hirevo]

Good catch, I was indeed able to reproduce it.
I have done my tests of the mysql feature using mariadb instead of the actual mysql and I didn't encounter that issue.

It seems that MySQL does not support computed expressions as a default for columns:
https://stackoverflow.com/questions/360467/mysql-set-default-value-for-field-as-a-string-concatenation-function
The most immediate fix would be to remove these defaults and use triggers to set the values if not specified upon insert:

create table crates (
    id bigint not null auto_increment unique primary key,
    name varchar(255) not null unique,
    description varchar(4096),
    created_at varchar(25),
    updated_at varchar(25),
    downloads bigint not null default 0,
    documentation varchar(1024),
    repository varchar(1024)
);

delimiter //
create trigger default_crate_fields
before insert on crates
for each row
begin
  if (new.created_at is null) then
    set new.created_at = concat(utc_date(), ' ', utc_time());
  end if;
  if (new.updated_at is null) then
    set new.updated_at = new.created_at;
  end if;
end//
delimiter ;

Another route could be to just not use defaults nor triggers and just always specify these dates when inserting to the database.

I think the second option would be better because specifying the date is technically easy and would simplify the schema for every vendors.
Also, the first option makes the created_at and updated_at columns nullable.

So, I think I'll be pushing a PR for option 2.