Pomelo.entityframeworkcore.mysql: Invalid default value with CLR type DateTime

[stuartbloom]

Hi there

Sorry if this is another silly question.

I have the following model

```c#
public class TankLevel{
public int id { get; set; }
public DateTime dateLogged { get; set; } = DateTime.Now;
public int level { get; set; }
}

and the following in my `OnModelCreating`

```c#
modelBuilder.Entity<TankLevel>()
     .Property(l => l.dateLogged)
     .HasDefaultValueSql("CURRENT_TIMESTAMP");

Unfortunately, when I try to update the database, I get the error Invalid default for dateLogged.

Am I doing something silly?

Thanks in advance

Stuart

[stuartbloom]

Just dropped the database and created a new migration, but i am now getting the error
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 'CURRENT_TIMESTAMP' at line 1

[yukozh]

If your mysql version is >= 5.6, you should use CURRENT_TIMESTAMP(6) instead of CURRENT_TIMESTAMP.

See MySQL documentation: http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

In Pomelo:

| MySQL | CLR Type | Stored Type |
|--|--|--|
|>=5.6| DateTime | datetime(6) |
|>=5.6| DateTimeOffset | datetime(6) |
|>=5.6| TimeSpan | time(6) |
| < 5.6 | DateTime | datetime |
| < 5.6 | DateTimeOffset | datetime |
| < 5.6 | TimeSpan | time |

BTW, you needn't init the Time value in c#. Just remove = DateTime.Now; in your model.

[stuartbloom]

Perfect, many thanks 👍 :)

One final thing, just wondering how I can turn on cascade deletes, my tank model has a list of tanklevels, but the foreignkey on the TankLevels table has no action

[yukozh]

@stuartbloom In EF Core, cascade on delete is the default behavior. Please provide me your models and dbcontext class.

The following sample will show you how to work with foreignkeys(one to many) in EF Core: https://github.com/Kagamine/YuukoBlog-NETCore-MySql/blob/master/src/YuukoBlog/Models/PostTag.cs#L11-L14

[stuartbloom]

attached, thanks
Models.zip

[yukozh]

There are 2 ways to use 1-n relation in EF Core. You've chose the Flunt API. Maybe you should set tanklevels .HasOne(x => x.Tank).WithMany(x => x.Levels).HasForeignKey(x => x.TankId)

Another way is DataAnnotations: https://github.com/Kagamine/YuukoBlog-NETCore-MySql/blob/master/src/YuukoBlog/Models/PostTag.cs#L11-L14.

See the EF Core documentation: http://githubdocs20161224013358.azurewebsites.net/core/modeling/relational/fk-constraints.md

[stuartbloom]

just tried with WithOne but that doesnt seem to help. I guess I will have to try and change my models to use annotations.

Would you have a good resource that an entity framework newbie can use?

[yukozh]

No, you shouldn't use HasMany WithOne, you should use HasOne WithMany HasForeignKey.

There is a sample project for starting use Pomelo.EntityFrameworkCore.MySql: https://github.com/Kagamine/YuukoBlog-NETCore-MySql

[stuartbloom]

ok, thanks, I will have a play. closing this now.

Thanks again

[rbluethl]

Sorry to recover an old thread, but I'm having issues with MariaDB 10.3.12 not mapping CLR DateTime to datetime(6). Should this work out of the box or is there any configuration necessary? My "special case" is that we support both MySQL and SQL Server. How can I change my configuration so that the migration works for both providers? Thanks in advance.

[mjza]

How can we solve this issue with Maria DB?
Neither this:

entity.Property(e => e.ValidFrom)
                    .HasColumnName("valid_from")
                    .HasColumnType("datetime")
                    .HasDefaultValueSql("'current_timestamp()'");

nor this:

entity.Property(e => e.ValidFrom)
                    .HasColumnName("valid_from")
                    .HasColumnType("datetime")
                    .HasDefaultValueSql("'current_timestamp(6)'");

Does not accepted!

[lauxjpn]

@mjza If you are using the current version of Pomelo (i.e. 3.1.1), then any of the following model definitions should work:

``c# //valid_from` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
entity.Property(e => e.ValidFrom)
.HasColumnName("valid_from")
.ValueGeneratedOnAdd();

// valid_from datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
entity.Property(e => e.ValidFrom)
.HasColumnName("valid_from")
.HasColumnType("datetime(6)")
.ValueGeneratedOnAdd();

// ValidFrom datetime NOT NULL DEFAULT CURRENT_TIMESTAMP()
entity.Property(e => e.ValidFrom)
.HasColumnName("valid_from")
.HasColumnType("datetime")
.ValueGeneratedOnAdd();

// ValidFrom datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
entity.Property(e => e.ValidFrom)
.HasColumnName("valid_from")
.HasColumnType("datetime(6)")
.HasDefaultValueSql("CURRENT_TIMESTAMP(6)");

// ValidFrom datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
entity.Property(e => e.ValidFrom)
.HasColumnName("valid_from")
.HasDefaultValueSql("CURRENT_TIMESTAMP(6)");

// ValidFrom datetime NOT NULL DEFAULT CURRENT_TIMESTAMP()
entity.Property(e => e.ValidFrom)
.HasColumnName("valid_from")
.HasColumnType("datetime")
.HasDefaultValueSql("CURRENT_TIMESTAMP()");

// ValidFrom datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
entity.Property(e => e.ValidFrom)
.HasColumnName("valid_from")
.HasColumnType("datetime")
.HasDefaultValueSql("CURRENT_TIMESTAMP");
```

So you have to remove the single quotes and have to make sure, that argument n in CURRENT_TIMESTAMP(n) has the same precision as the datetime column (in Pomelo 3.1.1, the default precision for datetime columns is 6).

[mjza]

@lauxjpn I removed the single quotations and the problem solved.

I have the latest version of this library.

However I made this migration file with the following command:

dotnet ef dbcontext scaffold "server=localhost;port=3306;userid=root;password=;database=xxx;TreatTinyAsBoolean=true;" Pomelo.EntityFrameworkCore.MySql -o Model

And then I deleted the DB and wanted to test that it can generate the DB again when it does not exist, that I faced with that error!

Therefore, it means there is bug somewhere that it puts the single quotations!

Just for your knowledge my server is 10.4.11-MariaDB - mariadb.org binary distribution. Maybe it is needed that in the ef command I declare the version of the DB somehow?

[mguinness]

@mjza I assume you have correctlly set ServerVersion in your Startup?

[lauxjpn]

@mjza I can confirm that this is a bug that exists only with MariaDB. Remove the single quotes as a workaround. We will release a fix for this shortly.

@mjza I assume you have correctlly set ServerVersion in your Startup?

This is not an issue here, because he is scaffolding and we read the database version before doing so.

[mguinness]

This is not an issue here, because he is scaffolding and we read the database version before doing so.

Ah, I thought this thread was about migrations. Sometimes it's better to start a new issue rather than resurrect a 3 year old one.

[lauxjpn]

Ah, I thought this thread was about migrations. Sometimes it's better to start a new issue rather than resurrect a 3 year old one.

Agreed!

[mjza]

@mguinness Yes I set it already.