Database scaffolding produces wrong code when tables have datetime/timestamp columns with default value CURRENT_TIMESTAMP

[pjc89]

Steps to reproduce

Create a table like this one:

create table TimestampTable(
    Id int not null,
    Ts timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    Dt datetime default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    primary key (Id)
);

run:

dotnet ef dbcontext scaffold \
    --data-annotations \
    --context TestDb \
    --force \
    --use-database-names \
    "my_connection_string" \
    "Pomelo.EntityFrameworkCore.MySql"

The issue

Pomelo sets the default value 'CURRENT_TIMESTAMP' (string) instead of CURRENT_TIMESTAMP

The generated code is:

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TimestampTable>(entity =>
            {
                entity.Property(e => e.Dt)
                    .HasDefaultValueSql("'CURRENT_TIMESTAMP'") // <-- problem
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.Ts)
                    .HasDefaultValueSql("'CURRENT_TIMESTAMP'") // <-- problem
                    .ValueGeneratedOnAddOrUpdate();
            });
        }

Trying to recreate the database with myDbContext.Database.EnsureCreated() will throw an exception
because the default value for the timestamp column is 'CURRENT_TIMESTAMP'.

EFCore Log:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.1.2-rtm-30932 initialized 'TestDb' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `TestDb_EFCore`;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `TimestampTable` (
          `Id` int(11) NOT NULL AUTO_INCREMENT,
          `Ts` timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP',
          `Dt` datetime NULL DEFAULT 'CURRENT_TIMESTAMP',
          CONSTRAINT `PK_TimestampTable` PRIMARY KEY (`Id`)
      );

Exception message:

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

Stack trace:

MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid default value for 'Ts' ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid default value for 'Ts'
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 42
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 74
   at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 299
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 284
   at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 37
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 261
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 62
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Invalid default value for 'Ts' ---> MySql.Data.MySqlClient.MySqlException: Invalid default value for 'Ts'
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 42
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 74
   at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 299
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 284
   at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 37
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 261
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 62
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
   at CreateDb.Program.Main(String[] args) in /git/netcore-planet-garavot8-data/CreateDb/Program.cs:line 30

Further technical details

MySQL version: 5.7.20
Operating system: CentOS 7.4
Pomelo.EntityFrameworkCore.MySql version: 2.1.2

[mguinness]

The logic for defaults in MySqlDatabaseModelFactory class is currently rudimentary as it assumes default is a string.

Type checking will be required, i.e. varchar, int, enum etc. to determine if quotes are required (and to use escaping if needed) and also following rules in Data Type Default Values.

[lauxjpn]

Fixed in #896.

[LeaFrock]

I find that the codes below can work fine and avoid the bug before new version releases.

 protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TimestampTable>(entity =>
            {
                entity.Property(e => e.Dt)
                    .HasDefaultValueSql("CURRENT_TIMESTAMP()")
                    .ValueGeneratedOnAddOrUpdate();

                entity.Property(e => e.Ts)
                    .HasDefaultValueSql("CURRENT_TIMESTAMP()")
                    .ValueGeneratedOnAddOrUpdate();
            });
        }