When i only modify the column type to allow null, AutoMigrate does not work. #6372
Description
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.