Pomelo.EntityFrameworkCore.MySql: Error applying migration: BLOB, TEXT, GEOMETRY or JSON can't have a default value

Since the strings are not nullable, then a value must always be provided by the user for saving in which case I do not think a default value is necessary.

Theoretically that is true. I checked this against SQL Server and it produces the same migration code including the defaultValue: "" (though it is not an issue for SQL Server, because it does not have the same limitations regarding the text type).

Enabling Nullable mode is just making the System.String property a [Required] property. So the same behavior will also happen, if you make it a required property explicitly.

This behavior generally makes sense because you could mark a previously nullable property as required at a later point in time, in which case you might like the migration to update any null values in the column to an empty string instead of throwing when applying the migration. (Update: This seems to be not the case. See below for further details.)

It might also be helpful so ensure, that in case a null value is send to the column anyway, which is still possible for explicitly marked as required properties and also for those implicitly marked as required by the Nullable mode, a default value is set instead of an exception being thrown. (Though that is probably not the reasoning behind this behavior because then the default value would also be set in conjunction with CreateTable() which it isn’t.)

Maybe someone from the EF Core team (like @ajcvickers) can shed some light on the real reason behind this.

Anyway for our case here, you are right that we should not generate default values for columns that use store types that do not support default values.

---

I just implemented the fix for this in the same way we did before for the CURRENT_TIMESTAMP issue.

Looking at this closer, the default value is not being generated for AlterColumn() calls, when a previously non-required property of type System.String is made required. I would have expected defaultValue: "" to be generated in this case too, because it is being generated for the AddColumn() calls.

But because it is not, the generation of defaultValue: "" for AddColumn() calls now appears to be less of an intended feature and more like a bug in EF Core, that just has no negative impact on SQL Server and thus went unnoticed until now. /cc @ajcvickers

After add a property with REQUIRED in a entity, the same thing happened it will generate Migration like this

 migrationBuilder.AddColumn<string>(
            name: "ColumnName",
            table: "TableName",
            nullable: false,
            defaultValue: "");

But I didn’t set DEFAULT VALUE,.

@foriequal0 Yes, all issues here are fixed:

• The original one, regarding column renaming, had been fixed for Pomelo 3.0.0.
• The second one, regarding AddColumn() and default values, was fixed by #987.
• The last one, regarding inconsistent behavior between AddColumn() and AlterColumn() in conjunction with required properies, was fixed by https://github.com/dotnet/efcore/issues/19882.

It should be mentioned, that in Pomelo 6.0, we will only add default values when they are expected by the user. Making an existing string column required in a later migration, will not lead to a default value being added to the table definition. Instead, an UPDATE statement will be generated, that updates existing NULL values in the column to an empty string (see #1498).

@lauxjpn Sorry, late reply: I think this is a bug. I recently observed the same behavior and was confused too. Filed https://github.com/dotnet/efcore/issues/19882

One more thing to mention is that doing it in MigrationsSqlGenerator would also help users who manually add AddColumn assuming that they need to specify some default for new NOT NULL columns.

I agree that this could be helpful for some users. But it also opens the window for subtle and unexpected bugs, where users now expect a default value to be applied to columns of store types that don’t support them.

With the current implementation, we don’t encourage them to try this, by not generating a default value migration parameter in the first place and at least openly throw an exception with a concise description about the fact that this is not supported, in case anybody tries it on their own.

---

@bricelam Do you have any idea, why the following is implemented the way it is:

Why is an empty default value generated for a required string property for AddColumn, while it is not generated for AlterColumn when an existing non-required string property is made required by the user?

(This is internal EF Core behavior that should be reproducible across all database providers. I tested it with Pomelo and SQL Server.)

Its this inconsistent behavior, that makes me suspicious about the AddColumn generation in the first place (or it could just mean, that is has a purpose that was just missed to be implemented for AlterColumn).

It would make some sense the other way around, which would also support @ajcvickers statement (and what I assumed in my previous comment).

The last_name column for the existing row has an empty string; in other words, MySQL seems to have an implicit default ‘’ behavior even if the explicit instruction in the ALTER TABLE statement isn’t allowed. Because of this, I think it may be OK to modify your MigrationsSqlGenerator to simply omit the DEFAULT clause on certain types of columns which don’t support it.

@roji Good observation! It it backed up by 11.7 Data Type Default Values:

Data type specifications can have explicit or implicit default values. […] Handling of Implicit Defaults: If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. […] For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

• If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

• If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

I already implemented a fix at the migration level by changing a line in the model differ (that we already had) to just not generate the default values in general for those store types that don’t support them.

See #987:

public class MySqlMigrationsModelDiffer : MigrationsModelDiffer
{
    public MySqlMigrationsModelDiffer(
        [NotNull] IRelationalTypeMappingSource typeMappingSource,
        [NotNull] IMigrationsAnnotationProvider migrationsAnnotations,
        [NotNull] IChangeDetector changeDetector,
        [NotNull] IUpdateAdapterFactory updateAdapterFactory,
        [NotNull] CommandBatchPreparerDependencies commandBatchPreparerDependencies)
        : base(
            typeMappingSource,
            migrationsAnnotations,
            changeDetector,
            updateAdapterFactory,
            commandBatchPreparerDependencies)
    {
    }


    protected override IEnumerable<MigrationOperation> Add(IProperty target, DiffContext diffContext, bool inline = false)
    {
        if (target.FindTypeMapping() is RelationalTypeMapping storeType)
        {
            var valueGenerationStrategy = MySqlValueGenerationStrategyCompatibility.GetValueGenerationStrategy(MigrationsAnnotations.For(target).ToArray());


            // Ensure that null will be set for the columns default value, if CURRENT_TIMESTAMP has been required,
            // or when the store type of the column does not support default values at all.
            inline = inline ||
                        (storeType.StoreTypeNameBase == "datetime" ||
                        storeType.StoreTypeNameBase == "timestamp") &&
                        (valueGenerationStrategy == MySqlValueGenerationStrategy.IdentityColumn ||
                        valueGenerationStrategy == MySqlValueGenerationStrategy.ComputedColumn) ||
                        storeType.StoreTypeNameBase.Contains("text") ||
                        storeType.StoreTypeNameBase.Contains("blob") ||
                        storeType.StoreTypeNameBase == "geometry" ||
                        storeType.StoreTypeNameBase == "json";
        }


        return base.Add(target, diffContext, inline);
    }
}

Implementing it in a way for empty strings as you proposed is however a good backward compatible addition to what we have planed in an upcoming release, where we will natively support default value expressions, that are also allowed for the store types in question and would look like DEFAULT ('') instead of DEFAULT '' for empty strings, but are only supported by MySQL 18.0.13+.

As to why we have a defaultValue when renaming a column, I have no idea… In a simple Npgsql test-case (changing a property’s mapped column name only) no defaultValue gets generated. @bricelam probably knows.

The RenameColumn issue has already been solved. This #615 issue was recently reopend only because of the similarities with AddColumn.

So the current question here is not about RenameColumn, but is the following:

Why is an empty default value generated for a required string property for AddColumn, while it is not generated for AlterColumn when an existing non-required string property is made required by the user?

(This is internal EF Core behavior that should be reproducible across all database providers. I tested it with Pomelo and SQL Server.)

Its this inconsistent behavior, that makes me suspicious about the AddColumn generation in the first place (or it could just mean, that is has a purpose that was just missed to be implemented for AlterColumn).

It would make some sense the other way around, which would also support @ajcvickers statement (and what I assumed in my previous comment). Though I am not completely convinced that this should be handled by EF Core, because it is user database specific implementation behavior and should probably be implemented by the user, because he might not want an empty string but a different value as the replacement for NULL (though I agree that this makes this potential issue more discoverable for users).

For the column addition case, I did a quick test to see how MySQL behaves when a non-nullable text column is added to a table with existing rows:

CREATE TABLE test (id INT, first_name TEXT); 
INSERT INTO test (id, first_name) VALUES (1, 'foo');
ALTER TABLE test ADD COLUMN last_name TEXT NOT NULL;
SELECT * FROM test where last_name = '';

The last_name column for the existing row has an empty string; in other words, MySQL seems to have an implicit default '' behavior even if the explicit instruction in the ALTER TABLE statement isn’t allowed. Because of this, I think it may be OK to modify your MigrationsSqlGenerator to simply omit the DEFAULT clause on certain types of columns which don’t support it. This means it would continue to get generated in migrations, but would never actually make it to the database. @bricelam/@ajcvickers can confirm if that sounds like a good idea.

As to why we have a defaultValue when renaming a column, I have no idea… In a simple Npgsql test-case (changing a property’s mapped column name only) no defaultValue gets generated. @bricelam probably knows.

It might also be helpful so ensure, that in case a null value is send to the column anyway, which is still possible for explicitly marked as required properties and also for those implicitly marked as required by the Nullable mode, a default value is set instead of an exception being thrown. (Though that is probably not the reasoning behind this behavior because then the default value would also be set in conjunction with CreateTable() which it isn’t.)

I’m not aware of any database which does this. AFAIK if you send a null value for a non-nullable column, you should get a constraint violation error even if that column defines a default value. In other words, the default is used only if a value is not provided when inserting, and not when NULL is provided.

FWIW here’s an issue opened with MySQL 12 years to allow default…!

Since the strings are not nullable, then a value must always be provided by the user for saving in which case I do not think a default value is necessary.

The issue in your code is, that you do not specify the length of the Name property, so you force Pomelo to use the MySQL text type. The text type does not support direct default values though and therefore an exception is thrown.

That, IMO, is not an issue with my code. Users of EF providers should not be expected to know database nuances such as this one. At the very least an exception should be thrown instead of creating a migration that will not be possible to apply.

It looks to me as if you altered the model, because the default AspNetUserRoles table would only contain the UserId and RoleId columns and would generate code like the following:

CREATE TABLE `AspNetUserRoles` (
    `UserId` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    `RoleId` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    CONSTRAINT `PK_AspNetUserRoles` PRIMARY KEY (`UserId`, `RoleId`),
    CONSTRAINT `FK_AspNetUserRoles_AspNetRoles_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `AspNetRoles` (`Id`) ON DELETE CASCADE,
    CONSTRAINT `FK_AspNetUserRoles_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
);

So it appears to me, that you added the Id property to the model, am I correct?

Your model definition might be wrong in multiple ways (can’t say for sure, because you didn’t post it). The fact that you did specify newsequentialid() as the default, hints at that you want the Id property to be of type System.Guid. The newsequentialid() is a SQL Server specific function and not available in MySQL. Also, as the error message already tells you, the MySQL type text cannot have a direct default value (though depending on the used MySQL version, it would be theoretically possible to use an expression). Types like char and varchar can however. Pomelo supports all GuidFormats of MySqlConnector and uses char(36) as the default Guid representation.

If this does not fix your problem, you really need to post your model alterations, your model definition alterations and your migration Up() method.

Same problem using Pomelo.EntityFrameworkCore.MySql 2.2.0:

migrationBuilder.RenameColumn(
   name: "Value",
   table: "event_values",
   newName: "Text");

Fails with:

Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE `event_values` CHANGE `Value` `Text` tinyblob NOT NULL DEFAULT X'';

My current workaround is to just hack the migration by hand:

migrationBuilder.Sql("ALTER TABLE event_values RENAME COLUMN Value TO Text;");

Please provide your model, but a TEXT field cannot have a default value in MySQL.