Pomelo.entityframeworkcore.mysql: String (and NULL) default values get wrapped in additional single quotes when scaffolding from MariaDB

Created on 18 Dec 2019 · 7Comments · Source: PomeloFoundation/Pomelo.EntityFrameworkCore.MySql

Steps to reproduce

Scaffold-DbContext "Server=localhost;User=root;Password=123456;Database=newDb;" Pomelo.EntityFrameworkCore.Mysql -Context MyContext -OutputDir Models -Force
Add-migration initial
update-database

The issue

I noticed that after scaffolding the hasdefault property is always being generated with single quotes in excess. With this I can not run update-database on my migrations.
I made a comparison with scaffoding by entityframeworkcore and there is the difference of the excess quotation marks in the default values.

Exception message:
Stack trace:

Further technical details

MySQL version: MariaDb 10.3 64x
Operating system: win10
Pomelo.EntityFrameworkCore.MySql version: 3.0.0 / 3.0.1
Microsoft.AspNetCore.App version: -

Other details about my project setup:

closed-fixed type-bug

Source

evertonvps

All 7 comments

Please post the CREATE TABLE statement for the table in question of the original database that got scaffolded.

I suspect it to contain the line

`descricao` VARCHAR(150) NULL DEFAULT 'NULL'

which would be wrong and should be instead:

`descricao` VARCHAR(150) NULL DEFAULT NULL

My assumption is backed up by the following database table:

CREATE TABLE `Issue994`.`DefaultTest` (
  `Id` INT NOT NULL AUTO_INCREMENT,

  /* default is NULL */
  `NullableStringWithNull` VARCHAR(45) NULL DEFAULT NULL,

  /* default is a string of 4 characters with the content of 'NULL' */
  `NullableStringWithNullStringValue` VARCHAR(45) NULL DEFAULT 'NULL',

  /* default is a string of 4 characters with the content of 'Test' */
  `NullableStringWithTestStringValue` VARCHAR(45) NULL DEFAULT 'Test',

  PRIMARY KEY (`Id`)
);

When scaffolding, the following model definition gets generated (which is correct):

```c#
modelBuilder.Entity(entity =>
{
entity.Property(e => e.Id).HasColumnType("int(11)");

// No default value at all, because `null` is already the default for a string.
entity.Property(e => e.NullableStringWithNull)
    .HasColumnType("varchar(45)");

// The default value is a string containing "NULL" (4 characters).
entity.Property(e => e.NullableStringWithNullStringValue)
    .HasColumnType("varchar(45)")
    .HasDefaultValueSql("'NULL'");

// The default value is a string containing "Test" (4 characters).
entity.Property(e => e.NullableStringWithTestStringValue)
    .HasColumnType("varchar(45)")
    .HasDefaultValueSql("'Test'");

});
```

lauxjpn on 18 Dec 2019

@lauxjpn , The statement is this.

CREATE TABLE 'centro_custo' (
  'id' int(11) NOT NULL,
  'descricao' varchar(100) NOT NULL,
  'ativo' char(1) NOT NULL DEFAULT 'S' COMMENT 'S-Sim | N-Não',
  'criado_em' timestamp NULL DEFAULT NULL,
  'alterado_em' timestamp NULL DEFAULT NULL,
  'excluido_em' timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

evertonvps on 21 Dec 2019

Your 'descricao' varchar(100) NOT NULL, line does not contain any default value. Therefore there shouldn't be any HasDefaultValueSql() call generated at all.

Also with the NOT NULL clause, it does not allow NULL values at all, so MySQL would not let you define a NULL default value for it in the first place (and would return an error if you would try anyway).

When scaffolding the table you provided, the following code gets generated, which is correct:

```c#
entity.Property(e => e.Descricao)
.IsRequired()
.HasColumnName("descricao")
.HasColumnType("varchar(100)")
.HasCharSet("utf8")
.HasCollation("utf8_general_ci");


Are you sure that this is the actual table you are scaffolding and that this is the actual SQL definition of the table before you scaffolded it?

Its definition diverges significantly from the one you posted the screenshot of (no default value defined, `varchar(100)` instead of `varchar(150)` etc.)?

---

As a side note, the generated `CREATE TABLE` script should contain backticks `` ` `` instead of single quotes `'` for names:

```sql
CREATE TABLE `centro_custo` (
  `id` int(11) NOT NULL,
  `descricao` varchar(100) NOT NULL,
  `ativo` char(1) NOT NULL DEFAULT 'S' COMMENT 'S-Sim | N-Não',
  `criado_em` timestamp NULL DEFAULT NULL,
  `alterado_em` timestamp NULL DEFAULT NULL,
  `excluido_em` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To automatically generate a CREATE TABLE statement for an existing table, use the SHOW CREATE TABLE statement:

SHOW CREATE TABLE `your_table_name`;

(Unrelated to this issue, you might also want to consider to switch from utf8 to utf8mb4. The charset utf8 is a MySQL specific implementation that is not standard compliant.)

lauxjpn on 21 Dec 2019

👍1

Sorry man! I ended up sending the wrong table. I was able to reproduce the problem with another table.

CREATE TABLE `empresa` (
  `id` int(11) NOT NULL,
  `fantasia` varchar(150) DEFAULT NULL,
  `razao_social` varchar(150) NOT NULL,
  `tipo` char(1) NOT NULL DEFAULT 'F' COMMENT 'M-Matriz, F-Filial',
  `cnpj` varchar(14) DEFAULT NULL,
  `inscricao_estadual` varchar(25) DEFAULT NULL,
  `endereco` varchar(255) DEFAULT NULL,
  `bairro` varchar(150) DEFAULT NULL,
  `cidade` varchar(100) DEFAULT NULL,
  `cep` varchar(8) DEFAULT NULL,
  `complemento` varchar(150) DEFAULT NULL,
  `criado_em` timestamp NULL DEFAULT NULL,
  `alterado_em` timestamp NULL DEFAULT NULL,
  `excluido_em` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note the excess of single quotes in the Tipo column. when performing scaffolding.

```c#
entity.Property(e => e.Tipo)
.IsRequired()
.HasColumnName("tipo")
.HasColumnType("char(1)")
.HasDefaultValueSql("'''F'''")
.HasComment("M-Matriz, F-Filial")
.HasCharSet(Pomelo.EntityFrameworkCore.MySql.Storage.CharSet.Utf8);


When I run update-database, throws exception

PM> update-database
Build started...
Build succeeded.
Applying migration '20191222132848_init'.
Failed executing DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE empresa (
id int(11) NOT NULL AUTO_INCREMENT,
fantasia varchar(150) NULL DEFAULT 'NULL',
razao_social varchar(150) NOT NULL,
tipo char(1) NOT NULL DEFAULT '''F''',
cnpj varchar(14) NULL DEFAULT 'NULL',
inscricao_estadual varchar(25) NULL DEFAULT 'NULL',
endereco varchar(255) NULL DEFAULT 'NULL',
bairro varchar(150) NULL DEFAULT 'NULL',
cidade varchar(100) NULL DEFAULT 'NULL',
cep varchar(8) NULL DEFAULT 'NULL',
complemento varchar(150) NULL DEFAULT 'NULL',
criado_em timestamp NULL DEFAULT 'NULL',
alterado_em timestamp NULL DEFAULT 'NULL',
excluido_em timestamp NULL DEFAULT 'NULL',
CONSTRAINT PK_empresa PRIMARY KEY (id)
);
MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid default value for 'tipo'
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid default value for 'tipo'
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 226
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 74
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Pomelo.EntityFrameworkCore.MySql.Migrations.Internal.MySqlMigrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Invalid default value for 'tipo'
```

evertonvps on 22 Dec 2019

@evertonvps Thank you for reporting this issue!

It seems that MariaDB 10.2.7 broke compatibility to MySQL with their INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT implementation.

Lets assume the following table:

CREATE TABLE `MariaDBDefaultValueTest` (
  `Id` int(11) NOT NULL,
  `NonNullableWithoutDefault` varchar(255) NOT NULL,
  `NonNullableWithDefaultValueNull` varchar(255) NOT NULL DEFAULT 'NULL',
  `NullableWithoutDefault` varchar(255) NULL,
  `NullableWithDefaultValueNull` varchar(255) NULL DEFAULT 'NULL',
  `NullableWithDefaultNull` varchar(255) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Until MariaDB 10.2.6, values were saved as they are still in MySQL:

Since MariaDB 10.2.7, they are saved differently:

There are multiple reports of this issue (e.g. MDEV-13341), but it seems to have been a deliberate choice (see MDEV-13132).

So this is a divergence between MySQL and MariaDB we need to address.

lauxjpn on 22 Dec 2019

👍1

This will be fixed in 3.2.0.
Until then, you might need to manually fix default values for strings columns in MariaDB 10.2.7+.

For your particular example, it would need to be done like this:

```c#
// Faulty line
.HasDefaultValueSql("'''F'''")

// Correct line
.HasDefaultValueSql("'F'")
```

lauxjpn on 22 Dec 2019

We will actually release the fix sooner in the 3.1.1 release.

lauxjpn on 16 Jan 2020

🚀1

Was this page helpful?

0 / 5 - 0 ratings

Related issues

XML documentation is not included in the package

hainguyenthanh · 3Comments

nvarchar and nvarchar(max) errors with default ASP.NET Core 3.0 project and Identity option

bobbd · 4Comments

Cannot access a disposed object

a641545621 · 3Comments

Database CPU spikes and cripples database after upgrade from .NET Core 2.2 to .NET Core 3.0 with Pomelo 3.0.0-rc1.final

coultonluke · 3Comments

Fluent API with fixed length varchar

Toemsel · 3Comments