An error generate sql for DateTime and DateTimeOffset.

[a14907]

Steps to reproduce

I have a class like this: public class Version { public int Id { get; set; } public string VersionStr { get; set; } public DateTime CreateTime { get; set; } }

when I call the method : db.Database.EnsureCreated(); I expected everything will ok! but..

The issue

The sql generate is : CREATE TABLEVersion(Idint NOT NULL AUTO_INCREMENT,VersionStrlongtext NULL,CreateTimedatetime(6) NOT NULL, CONSTRAINTPK_Version PRIMARY KEY (Id) );

MySql.Data.MySqlClient.MySqlException (0x80004005): 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 '(6) NOT NULL,
    CONSTRAINT `PK_Version` PRIMARY KEY (`Id`)
)' at line 4 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): 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 '(6) NOT NULL,
    CONSTRAINT `PK_Version` PRIMARY KEY (`Id`)
)' at line 4

The CreateTime should be : CreateTime datetime NOT NULL, what generate is : CreateTime datetime(6) NOT NULL,

Further technical details

MySQL version: mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) Operating system: Pomelo.EntityFrameworkCore.MySql version:2.1.1

Other details about my project setup:

[TheJoelGray]

I'm running into the same issue. Is there a workaround until such a time as this is resolved?

[TheJoelGray]

Turns out my server version wasn't what I though it was. I set the correct version in the startup and all is working now.

[rujianwang]

You should set the mysql ServerVersion like : using System; using Microsoft.EntityFrameworkCore; using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

namespace YourNamespace // replace "YourNamespace" with the namespace of your application { public class Startup { public void ConfigureServices(IServiceCollection services) { // other service configurations go here services.AddDbContextPool<YourDbContext>( // replace "YourDbContext" with the class name of your DbContext options => options.UseMySql("Server=localhost;Database=ef;User=root;Password=123456;", // replace with your Connection String mysqlOptions => { mysqlOptions.ServerVersion(new Version(5, 7, 17), ServerType.MySql); // replace with your Server Version and Type } )); } }

[codeaid]

The last suggestion doesn't work for me. I am using 5.6 (due to AWS Aurora Serverless) and my DateTime fields also get generated as datetime(6), which causes

MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid default value for 'Created'

I obviously get the exact same exception if I run the SQL query manually. It works if I change the field format to datetime though.

My configuration in startup is

services.AddDbContextPool<ApplicationDbContext>(options =>
{
    options.UseMySql(connectionString,
        mysqlOptions =>
        {
            mysqlOptions.MigrationsAssembly(migrationsAssembly);
            mysqlOptions.ServerVersion(new Version(5, 6), ServerType.MySql);
        });
});

[mguinness]

@codeaid According to Fractional Seconds in Time Values you should have support.

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision

From your error it appears that the error is related to the default value.