You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When I perform a batch insertion and want to update all fields if the data to be inserted has a unique index conflict.
I used the OnConflict{UpdateAll: true} method to implement the above function, but after execution, if there is an update, the returned ID is incorrect.
The test code is as follows:
funcTestGORM(t*testing.T) {
addedUsers:= []UniqueUser{
{
Name: "Alice",
Age: 18,
},
{
Name: "Bob",
Age: 22,
},
{
Name: "Cindy",
Age: 25,
},
}
// Drop TABLE IF EXISTSDB.Migrator().DropTable(&UniqueUser{})
DB.AutoMigrate(&UniqueUser{})
// create usersDB.Create(&addedUsers)
// In this case, I use a new slice, to simulate another business scenario// where new data is obtained and needs to be updatednewUsers:= []UniqueUser{}
// one year laterfor_, user:=rangeaddedUsers {
newUsers=append(newUsers, UniqueUser{
Name: user.Name,
Age: user.Age+1,
})
}
// update users, use on conflict to update all fieldsDB.Clauses(clause.OnConflict{
// add Columns to compatible with sqliteColumns: []clause.Column{{Name: "name"}},
UpdateAll: true,
}).Create(&newUsers)
// The bug here is that when using OnConflict{UpdateAll: true} for batch insertion,// if there is an update, the returned ID is incorrect// In this test case,// the expected result is that the second time the insertion is executed,// it should actually be updated,// so the ID returned by the corresponding object should be the same as the first time.fori:=rangenewUsers {
firseCreateUser:=addedUsers[i]
secondCreateUser:=newUsers[i]
iffirseCreateUser.ID!=secondCreateUser.ID {
t.Errorf("Expected ID %d, got %d", firseCreateUser.ID, secondCreateUser.ID)
}
}
}
In this test case, the expected result is that the second time the insertion is executed, it should actually be updated, so the ID returned by the corresponding object should be the same as the first time.
But the result is as follows:
GORM_DIALECT=mysql go test
2024/05/18 14:31:04 testing mysql...
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:200
[0.582ms] [rows:0] SET FOREIGN_KEY_CHECKS = 0;
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:203
[9.195ms] [rows:0] DROP TABLE IF EXISTS `unique_users` CASCADE
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:208
[0.651ms] [rows:0] SET FOREIGN_KEY_CHECKS = 1;
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:326
[0.865ms] [rows:-] SELECT DATABASE()
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:329
[2.028ms] [rows:1] SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE 'gorm%' ORDER BY SCHEMA_NAME='gorm' DESC,SCHEMA_NAME limit 1
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/src/github.com/jpbirdy/playground/main_test.go:33
[3.375ms] [rows:-] SELECT count(*) FROM information_schema.tables WHERE table_schema = 'gorm' AND table_name = 'unique_users' AND table_type = 'BASE TABLE'
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/src/github.com/jpbirdy/playground/main_test.go:33
[24.974ms] [rows:0] CREATE TABLE `unique_users` (`id` bigint unsigned AUTO_INCREMENT,`created_at` datetime(3) NULL,`updated_at` datetime(3) NULL,`deleted_at` datetime(3) NULL,`name` varchar(32),`age` bigint unsigned,PRIMARY KEY (`id`),INDEX `idx_unique_users_deleted_at` (`deleted_at`),CONSTRAINT `uni_unique_users_name` UNIQUE (`name`))
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/src/github.com/jpbirdy/playground/main_test.go:36
[7.194ms] [rows:3] INSERT INTO `unique_users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2024-05-18 14:31:04.428','2024-05-18 14:31:04.428',NULL,'Alice',18),('2024-05-18 14:31:04.428','2024-05-18 14:31:04.428',NULL,'Bob',22),('2024-05-18 14:31:04.428','2024-05-18 14:31:04.428',NULL,'Cindy',25)
2024/05/18 14:31:04 /Users/jpbirdy/Workspaces/go/src/github.com/jpbirdy/playground/main_test.go:54
[5.269ms] [rows:6] INSERT INTO `unique_users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`) VALUES ('2024-05-18 14:31:04.436','2024-05-18 14:31:04.436',NULL,'Alice',19),('2024-05-18 14:31:04.436','2024-05-18 14:31:04.436',NULL,'Bob',23),('2024-05-18 14:31:04.436','2024-05-18 14:31:04.436',NULL,'Cindy',26) ON DUPLICATE KEY UPDATE `updated_at`='2024-05-18 14:31:04.436',`deleted_at`=VALUES(`deleted_at`),`name`=VALUES(`name`),`age`=VALUES(`age`)
--- FAIL: TestGORM (0.06s)
main_test.go:67: Expected ID 1, got 3
main_test.go:67: Expected ID 2, got 4
main_test.go:67: Expected ID 3, got 5
FAIL
exit status 1
FAIL gorm.io/playground 1.004s
But I tested it and it works fine on sqlite and postgres.
However, I tried to debug it. The problem may be that Mysql does not support the RETURNING syntax. When GORM handles this situation when inserting data in Mysql, it will use LastInsertID to get the ID, and handle the auto-increment situation through the code. This situation is fine when all data is newly inserted, but when batch insertion, and there may be conflicting updates, the processing of LastInsertID will be problematic. Causing the returned ID to be incorrect.
The text was updated successfully, but these errors were encountered:
GORM Playground Link
go-gorm/playground#737
Description
UniqueUser is a model which exists unique constraint on name. And its ID is auto-increment. The Code can be as follows:
When I perform a batch insertion and want to update all fields if the data to be inserted has a unique index conflict.
I used the OnConflict{UpdateAll: true} method to implement the above function, but after execution, if there is an update, the returned ID is incorrect.
The test code is as follows:
In this test case, the expected result is that the second time the insertion is executed, it should actually be updated, so the ID returned by the corresponding object should be the same as the first time.
But the result is as follows:
But I tested it and it works fine on sqlite and postgres.
However, I tried to debug it. The problem may be that Mysql does not support the RETURNING syntax. When GORM handles this situation when inserting data in Mysql, it will use LastInsertID to get the ID, and handle the auto-increment situation through the code. This situation is fine when all data is newly inserted, but when batch insertion, and there may be conflicting updates, the processing of LastInsertID will be problematic. Causing the returned ID to be incorrect.
The text was updated successfully, but these errors were encountered: