Gorm: sqlite automigrate failed

[egenchen]

Description

I have a sqlite database with a table and a view reference the table column.

When run AutoMigrate, it reported "error in view vW_TheView: no such table: main.TheTable".

According to this answer: https://stackoverflow.com/questions/57253045/sqlite-error-no-such-table-main-table-name-exists and the website https://sqlite.org/lang_altertable.html, I should set "legacy_alter_table=ON" to make migrate works,

but I add db.Exec("PRAGMA legacy_alter_table = ON") before db.AutoMigrate, it still failed.

But I can use db.Raw to run "PRAGMA legacy_alter_table = ON" and migration sql generated by gorm, it will success, I am not sure where the legacy_alter_table value is been reset.

Sample code

if err := db.Exec("PRAGMA legacy_alter_table = ON").Error; err != nil {
    log.Errorf("error while set legacy_alter_table %s", err)
}
if err := db.AutoMigrate(&models.Terminal{}); err != nil {
    log.Errorf("error while migration %s", err)
    return err
}

[jinzhu]

Could you provide the reproducible code?

[egenchen]

package main

import (
    "fmt"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type Table1 struct {
    Id int
    Name string `gorm:"not null"`
}

func main() {
    db, _ := gorm.Open(sqlite.Open("sqlite.db"), &gorm.Config{})
    db.Exec("CREATE TABLE \"table1\" (\"id\" integer,\"name\" varchar);")
    db.Exec("CREATE VIEW \"view1\" AS select id, name from table1;")

    // the legacy_alter_table not work here
    if err := db.Exec("PRAGMA legacy_alter_table = ON").Error; err != nil {
        fmt.Printf("Error while set legacy %s", err)
    }

    if err := db.AutoMigrate(&Table1{}); err != nil {
        fmt.Printf("Error while migrate %+v", err)
    }
}

Here is the reproducible code.

[rmgogogo]

I had another issue related with AutoMigrate in MySQL.

It can create the table at 1st time. Second run, no schema changes. It stops there, like a dead lock.

[jinzhu]

I had another issue related with AutoMigrate in MySQL.

It can create the table at 1st time. Second run, no schema changes. It stops there, like a dead lock.

please create another issue with reproducible playground PR

[jinzhu]

works for me

[jinzhu]

maybe you can upgrade GORM to latest master to recheck?

[rmgogogo]

I had another issue related with AutoMigrate in MySQL.
It can create the table at 1st time. Second run, no schema changes. It stops there, like a dead lock.

please create another issue with reproducible playground PR

https://github.com/go-gorm/gorm/pull/3433
This PR fixed the issue. I sync latest master version and it can work.

[egenchen]

I had another issue related with AutoMigrate in MySQL.
It can create the table at 1st time. Second run, no schema changes. It stops there, like a dead lock.

please create another issue with reproducible playground PR

3433

This PR fixed the issue. I sync latest master version and it can work.

latest master works well, thanks.