Gorm When i only modify the column type to allow null, AutoMigrate does not work.

Your Question

according to the document. i should be able to use AutoMigrate to modify the column type to allow null on MySQL5.7, but in practice, AutoMigrate is not working.

at first, i used this structure to create the table.

type User struct {
    ID       int    `gorm:"primary_key"`
    Account  string `gorm:"size:128;not null"`
    GoogleID string `gorm:"size:128;not null" json:"google_id"`
}

func main() {
    db, err := connectDB()
    if err != nil {
        log.Fatal(err)
    }

    if err := db.Debug().Migrator().AutoMigrate(&User{}); err != nil {
        log.Fatal(err)
    }
}

when i drop not null of field GoogleID, and execute AutoMigrate. field GoogleID is still of type not null.
the following is log and table describe.

2023/06/02 22:07:04 /Users/vito/Project/gorm-practice/main.go:40
[1.756ms] [rows:-] SELECT DATABASE()

2023/06/02 22:07:04 /Users/vito/Project/gorm-practice/main.go:40
[8.397ms] [rows:1] SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE 'testdb%' ORDER BY SCHEMA_NAME='testdb' DESC,SCHEMA_NAME limit 1

2023/06/02 22:07:04 /Users/vito/Project/gorm-practice/main.go:40
[5.804ms] [rows:-] SELECT count(*) FROM information_schema.tables WHERE table_schema = 'testdb' AND table_name = 'users' AND table_type = 'BASE TABLE'

2023/06/02 22:07:04 /Users/vito/Project/gorm-practice/main.go:40
[1.521ms] [rows:-] SELECT DATABASE()

2023/06/02 22:07:04 /Users/vito/Project/gorm-practice/main.go:40
[3.230ms] [rows:1] SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE 'testdb%' ORDER BY SCHEMA_NAME='testdb' DESC,SCHEMA_NAME limit 1

2023/06/02 22:07:04 /Users/vito/Project/gorm-practice/main.go:40
[4.028ms] [rows:-] SELECT * FROM `users` LIMIT 1

2023/06/02 22:07:04 /Users/vito/Project/gorm-practice/main.go:40
[6.795ms] [rows:-] SELECT column_name, column_default, is_nullable = 'YES', data_type, character_maximum_length, column_type, column_key, extra, column_comment, numeric_precision, numeric_scale , datetime_precision FROM information_schema.columns WHERE table_schema = 'testdb' AND table_name = 'users' ORDER BY ORDINAL_POSITION

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| account   | varchar(128) | NO   |     | NULL    |                |
| google_id | varchar(128) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

i tried more tests and found that if you want to change the column type to null, you have to modify other column types together, such as size, length...etc

this case is when i drop not null constraint and modify size of the GoogleID field, AutoMigrate is working.

type User struct {
    ID       int    `gorm:"primary_key"`
    Account  string `gorm:"size:128;not null"`
    GoogleID string `gorm:"size:256" json:"google_id"`
}

the following is log and table describe.

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[2.043ms] [rows:-] SELECT DATABASE()

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[8.842ms] [rows:1] SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE 'testdb%' ORDER BY SCHEMA_NAME='testdb' DESC,SCHEMA_NAME limit 1

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[7.345ms] [rows:-] SELECT count(*) FROM information_schema.tables WHERE table_schema = 'testdb' AND table_name = 'users' AND table_type = 'BASE TABLE'

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[1.634ms] [rows:-] SELECT DATABASE()

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[4.387ms] [rows:1] SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE 'testdb%' ORDER BY SCHEMA_NAME='testdb' DESC,SCHEMA_NAME limit 1

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[3.068ms] [rows:-] SELECT * FROM `users` LIMIT 1

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[7.463ms] [rows:-] SELECT column_name, column_default, is_nullable = 'YES', data_type, character_maximum_length, column_type, column_key, extra, column_comment, numeric_precision, numeric_scale , datetime_precision FROM information_schema.columns WHERE table_schema = 'testdb' AND table_name = 'users' ORDER BY ORDINAL_POSITION

2023/06/02 22:07:39 /Users/vito/Project/gorm-practice/main.go:40
[31.866ms] [rows:0] ALTER TABLE `users` MODIFY COLUMN `google_id` varchar(256)

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| account   | varchar(128) | NO   |     | NULL    |                |
| google_id | varchar(256) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

I found the same question on issue, but it has not been solved.

The document you expected this should be explained

https://gorm.io/docs/migration.html

Expected answer

when i only change column type to can be a null, AutiMigrate should be working.

Comment From: a631807682

https://github.com/go-gorm/gorm/blob/master/migrator/migrator.go#L485

It seems that we should execute alter column when nullable is different, but from the comments, we ignore the change from not null to null. @jinzhu Is there any reason for this?

Comment From: darkoment

Try passing the table struct, not the address if err := db.Debug().Migrator().AutoMigrate(User{})

Comment From: xxxVitoxxx

@darkoment I tried passing the table struct. Even when I only drop the 'not null' constraint, it still doesn't work.

Comment From: hungtcs

+1, still not effective so far.

Comment From: Joaolfc0

+1, facing same error

Comment From: xxxVitoxxx

@a631807682 It seems that to avoid encountering an error(when you change an existing column from non-nullable to nullable and the column has no value), I have submitted a pull request to adjust the document, as the current description can be misleading.

This issue can be closed. Thank you.

Comment From: wookie0

@xxxVitoxxx (cc. @jinzhu @a631807682 ) I think it's bug.

https://github.com/go-gorm/gorm/blob/deceebfab8c460cfee229233aded2821ac6b08eb/migrator/migrator.go#L525-L531

In upper code, nullable variable means current database column type constraints. (field means to-be database model 's column) In general, (as-is)non-nullable column can be changed (to-be)nullable. Because that current column is non-nullable, every record has non-null value in this column. So it's not problem.

However, in the opposite case, there are restrictions. Because record may have null value in column. So migration will be failed.

Do I understand correctly??

I create PR about this. Please review. thx.