Failed to parse of DDL containing CURRENT_TIMESTAMP(6)

[tokubass]

My environment:

• MySQL Server version: 5.7.25 Homebrew
• mysqldef : v0.5.15

Case containing CURRENT_TIMESTAMP(6)

Failed to parse of DDL.

$ mysql -u root -e 'create database my_db'
$ cat schema.sql
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
$ ./mysqldef  --file=schema.sql my_db
found syntax error when parsing DDL "CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC": syntax error at position 135

I made sure that schema.sql has the correct syntax.

$ mysql -u root my_db < schema.sql
$ mysql -u root my_db -e 'show create table users;'
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Case not containing CURRENT_TIMESTAMP(6)

The DDL is successfully applied.

$ mysql -u root -e 'create database my_db2'
$ cat schema.sql
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
$ ./mysqldef  --file=schema.sql my_db2
-- Apply --
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

[k0kubun]

I made a fix and started its release of v0.5.16.

[tokubass]

Thank you for the very quick response.

[thanhnguyen2187]

Can the issue be raised again? I am having the same problem. My original script is:

create table staff (
    id integer not null auto_increment primary key,
    date_created timestamp not null default current_timestamp,
    date_modified timestamp not null default current_timestamp on update current_timestamp,

    full_name varchar(50) character set utf8,
    date_of_birth date,
    username varchar(32),
    password varchar(32),
    role_id int
);

My output DDL is:

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `date_modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `full_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `username` varchar(32) DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Thanks in advance...

EDIT: the script works should I add a number to my timestamp like this:

...
    date_created timestamp(6) not null default current_timestamp,
    date_modified timestamp(6) not null default current_timestamp on update current_timestamp,
...

It still feels kinda buggy when I rerun my script:

function assure() {
    FILENAME=$1
    mysqldef \
        --host=127.0.0.1 \
        --port=3306 \
        --user=amber \
        --password=amber \
        --file=./schemas/${FILENAME} \
        amber
}

assure "staff.sql"

The output becomes:

-- Apply --
ALTER TABLE `staff` CHANGE COLUMN `date_created` `date_created` timestamp(6) NOT NULL DEFAULT current_timestamp;
ALTER TABLE `staff` CHANGE COLUMN `date_modified` `date_modified` timestamp(6) NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp;

[k0kubun]

Your report doesn't follow the issue template and thus I cannot even understand what problem you're asking me to fix. Please follow https://github.com/k0kubun/sqldef/blob/master/.github/ISSUE_TEMPLATE.md as is.

I found sqldef fails to parse current_timestamp() correctly while current_timestamp and current_timestamp(6) work. I can work on fixing it, but because of your report, I'm not sure if it's what you want.

[thanhnguyen2187]

Hey @k0kubun... Sorry for the confusion. I am sorry that I did not read things carefully. I will try to explain the problem again.

---

--export output

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `date_modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `full_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `username` varchar(32) DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Input SQL

create table staff (
    id integer not null auto_increment primary key,
    date_created timestamp not null default current_timestamp,
    date_modified timestamp not null default current_timestamp on update current_timestamp,

    full_name varchar(50) character set utf8,
    date_of_birth date,
    username varchar(32),
    password varchar(32),
    role_id int
);

Current output

found syntax error when parsing DDL "CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `date_modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `full_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `username` varchar(32) DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4": syntax error at position 127

Expected output

Nothing is modified

---

I hope things were clarified enough for you... I fixed the error by changing from

    date_created timestamp not null default current_timestamp,
    date_modified timestamp not null default current_timestamp on update current_timestamp,

to

    date_created timestamp(6) not null default current_timestamp,
    date_modified timestamp(6) not null default current_timestamp on update current_timestamp,

timestamp with current_timestamp(6) should work, too... We have a table like this, basically:

	timestamp	timestamp(6)
current_timestamp		x
current_timestamp(6)	x	x

Again, I am sorry that I did not say it at first, but thank you for the project. It is your contribution to the community, and it benefits the users (including me), so please do not worry about what I want. You are doing the work for free, and everybody appreciates that!

Thank you for reading.

[k0kubun]

I'm not sure if I understand the timestamp(6) part yet, but I at least implemented the "Expected output" of the provided issue format and started v0.8.15 release.

You are doing the work for free, and everybody appreciates that!

Well, actually there are two people who became my GitHub Sponsor thanks to this project. But thanks :)

[thanhnguyen2187]

As you see in this script

create table staff (
    ...
    date_created timestamp not null default current_timestamp,
    date_modified timestamp not null default current_timestamp on update current_timestamp,
    ...
);

Basically, in the definition above, I tried to create a date_created column to store when was the row created, and date_modified to store when was the row updated.

• timestamp is the type of the columns
• timestamp(6) means the column reserves 6 digits for milliseconds
• Default values of both are current_timestamp, which is a function in MySQL to generate a new timestamp value
• current_timestamp(6) should be used to generate a new timestamp(6)

I hope that the explanation is clear enough. If not, please do not mind looking at the official documentation.

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_now

Thanks for reading.

[k0kubun]

I think I understand what you're saying, but what you want me to do is often not clear from your comments (if you have one, please use the issue template). You just found how you should write DDLs and it's not about an issue of sqldef. Is that correct?