Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Creating a MySql 8 Table with CurrentUTCDateTime Causes MySQL Syntax Error #1796

Closed
etherfactor opened this issue May 5, 2024 · 4 comments · Fixed by #1797
Closed

Creating a MySql 8 Table with CurrentUTCDateTime Causes MySQL Syntax Error #1796

etherfactor opened this issue May 5, 2024 · 4 comments · Fixed by #1797

Comments

@etherfactor
Copy link
Contributor

Describe the bug
When creating a column with a default value of the current UTC instant, the generated MySQL query will fail with a syntax error.

To Reproduce
Add the FluentMigrator runner, and target it with .AddMySql8().

Add a migration creating table with at least the following column:
.WithColumn("created_at_utc").AsDateTime2().NotNullable().WithDefault(SystemMethods.CurrentUTCDateTime)

Run the migration. The following error will be returned:
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 'UTC_TIMESTAMP

Expected behavior
The migration creates the table as expected. Wrapping the UTC_TIMESTAMP in parentheses causes the table to create as expected.

Information (please complete the following information):

  • OS: Docker mysql:8 & Windows 11
  • Platform: .NET Core 8
  • FluentMigrator version: 5.2.0
  • FluentMigrator runner
  • Database Management System: MySQL
  • Database Management System Version: 8.4.0

Additional context
The following CREATE TABLE statement fails,
create table `test` ( `id` integer not null, `timestamp_utc` datetime not null default utc_timestamp );
while the following CREATE TABLE statement succeeds:
create table `test` ( `id` integer not null, `timestamp_utc` datetime not null default (utc_timestamp) );

This appears to only apply to MySQL 8.0+:
https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-types-defaults-explicit

@jzabroski
Copy link
Collaborator

Well, that stinks. Thanks for the bug report and PR. I will try to merge it this weekend and hasten 6.0 release, which I was hoping would be a bigger release, but would rather service a fix for this and call the next big release 7.0.

@etherfactor
Copy link
Contributor Author

No problem! This issue aside, this library is great. Even then, this is the only issue I've hit in like a year of use.

If your automated tests connect to a MySQL 8.0.13+ database, would you like me to (attempt to) add some integration tests in the meantime?

@jzabroski
Copy link
Collaborator

We don't currently do that. I have bits and pieces of using https://github.com/Deffiss/testenvironment-docker to spin up various test containers. Nothing really working yet.

@etherfactor
Copy link
Contributor Author

Understood, just figured I would check.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants