Gorm The logic of automatic migration is not rigorous

GORM Playground Link

sorry, the project is urgent so I don't have time, I'll try to make it clear And I found the code for it : gorm/migrator/migrator.go:MigrateColumn and driver/mysql/migrator.go:AlterColumn

Description

The rules for Gorm automatic migration update column constraints are as follows: AutoMigrate changes the column type if the size, precision, and nullness can be changed

After testing, it was found that these are the conditions that trigger column constraint updates for automatic migration: field size changes, default value changes (changing to 0 does not take effect), precision changes, and changing from not 'not NULL' to 'not NULL'

==There is an anomaly, however==, that once the auto-migrated column constraint updates are triggered for these conditions, other unsupported constraints will be synchronized as well

Such as:

If 'not null' is added:

Automatic migration is triggered:

ALTER TABLE 'test' MODIFY COLUMN 'gold' decimal NOT NULL DEFAULT 10 COMMENT 'gold'

(Remember to remove the 'not NULL' constraint after this step, so as not to affect subsequent steps)

If you simply change the field type or change the default value to 0, it will not fire

But if 'not null' is added

Automatic migration is triggered, along with the types and default values

ALTER TABLE 'test' MODIFY COLUMN 'gold' int NOT NULL DEFAULT 0 COMMENT '

It's a very uncomfortable experience. A rule that is not supported is exposed because of another supported rule. But this is because the automatic migration trigger condition (gorm migrator/migrator. Go: MigrateColumn) and eventually joining together SQL (driver/mysql/migrator. Go: AlterColumn) is not rigorous

Comment From: jhartman86

+1 on this. When wiring up columns with GENERATED values, something like this below had worked just fine previously:

type thing struct {
    Depth int gorm:"->;type:int GENERATED ALWAYS AS (NLEVEL(node_path)) STORED;default:(-)"`
}

Now when auto migrations are run, GORM detects these as changes and tries to issue an ALTER statement, which fails.

Relevant versions: this worked fine on gorm: v1.21.14 w/ postgres driver: v1.1.0. After upgrading to gorm: v1.22.4 w/ postgres driver: v1.2.3, this is broken.

Related issue and feedback on making this work previously referenced here @jinzhu: #3887

Comment From: wushu037

+1 这个。当用GENERATED值连接列时，像下面这样的东西以前工作得很好：

type thing struct { Depth int gorm:"->;type:int GENERATED ALWAYS AS (NLEVEL(node_path)) STORED;default:(-)"` }

现在，当运行自动迁移时，GORM 将这些检测为更改并尝试ALTER失败的语句。

相关版本：这在 gorm：v1.21.14 w/ postgres 驱动程序：v1.1.0 上运行良好。升级到 gorm: v1.22.4 w/ postgres 驱动程序: v1.2.3后，坏了。

相关问题和关于使该工作以前在此处引用的反馈@jinzhu： #3887

In fact, there's a lot of code that feels sloppy. I think it's because there are so many people maintaining the project, each submitting only code that fixes their own problems, which leads to the current situation such as #4908 .I can't figure out what the point is

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days

Comment From: jinzhu

We have improved this in the new release v1.23.0, please try if it works for your situations.

The auto alert column can only works well for some basic cases, the alerting logic is like below code described, if possible you can write a playground for those cases doesn't work, let me check if it is possible to fix it.

Thank you.

func (m Migrator) MigrateColumn(value interface{}, field *schema.Field, columnType gorm.ColumnType) error {
    // found, smart migrate
    fullDataType := strings.ToLower(m.DB.Migrator().FullDataTypeOf(field).SQL)
    realDataType := strings.ToLower(columnType.DatabaseTypeName())

    alterColumn := false

    // check size
    if length, ok := columnType.Length(); length != int64(field.Size) {
        if length > 0 && field.Size > 0 {
            alterColumn = true
        } else {
            // has size in data type and not equal
            // Since the following code is frequently called in the for loop, reg optimization is needed here
            matches := regRealDataType.FindAllStringSubmatch(realDataType, -1)
            matches2 := regFullDataType.FindAllStringSubmatch(fullDataType, -1)
            if (len(matches) == 1 && matches[0][1] != fmt.Sprint(field.Size) || !field.PrimaryKey) &&
                (len(matches2) == 1 && matches2[0][1] != fmt.Sprint(length) && ok) {
                alterColumn = true
            }
        }
    }

    // check precision
    if precision, _, ok := columnType.DecimalSize(); ok && int64(field.Precision) != precision {
        if regexp.MustCompile(fmt.Sprintf("[^0-9]%d[^0-9]", field.Precision)).MatchString(m.DataTypeOf(field)) {
            alterColumn = true
        }
    }

    // check nullable
    if nullable, ok := columnType.Nullable(); ok && nullable == field.NotNull {
        // not primary key & database is nullable
        if !field.PrimaryKey && nullable {
            alterColumn = true
        }
    }

    if alterColumn && !field.IgnoreMigration {
        return m.DB.Migrator().AlterColumn(value, field.Name)
    }

    return nil
}