volatiletech
Insert can NOT work well when composite primary keys on MySQL
My test MySQL schema is simplified like
CREATE TABLE `demo` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL DEFAULT '0000-00-00',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The table is taken as un-CanLastInsertID in the following function because of composite primary keys.
https://github.com/volatiletech/sqlboiler/blob/df8550538fefdf62f2520e74dbfad73f1bcb84a2/drivers/table.go#L44-L66
However, when populating default values for the model, it uses both 2 primary keys id = 0 (last_insert_id isn't used here) and date='20190123'. Nothing found and populate fails.
@aarondl
What do you think about relaxing the condition of CanLastInsertID() to take composite primary keys as true?
In this case, MySQL still returns last_insert_id for the AUTO_INCRMENT primary key.
The problem is the guarantee of uniqueness is gone. My data -could- look like:
id | date
2 | 2019-01-01
2 | 0000-00-00 <- just inserted
At which point internal data structuring is the only thing giving us a chance of getting the most recently inserted piece of data because we used the default value for The only way the uniqueness guarantee stays is if we did something complicated.
For a composite primary key we must have non-default values for all fields that are part of the primary key except the auto-increment one. Otherwise we can't guarantee that we'll pull the right record.
I think the AUTO_INCREMENT id can guarantee the uniqueness in my case. I can make the second primary key date without default value.
e.g.
CREATE TABLE `demo` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Actually, I can pull the right record just through AUTO_INCREMENT id. I add date as secondary primary key for some other reason.
An auto incremented id can -always- uniquely identify a table row. I can't understand why you'd have date in there and it sounds like you don't either.
Maybe this is not a useful use case? Doesn't sound useful to me :)
@ceshihao Sorry for not responding for a bit.
I read this documentation: https://www.postgresql.org/docs/current/ddl-partitioning.html
And I thought about your use case (two people can order something on the same timestamp but the same person cannot) and it makes some sense to do (id, date). If you simply had a unique date then two customers could not place an order at the exact same time (despite that being a near impossibility, it's still a weird case).
So I think I understand better now.
But that doesn't change my response from above, if we did want to relax the constraint it would still have to have the constraints I mentioned in that:
For a composite primary key we must have non-default values for all fields that are part of the primary key except the auto-increment one. Otherwise we can't guarantee that we'll pull the right record.
If we were to do that it'd probably be okay?
@aarondl Hello. I am Japanese, so please forgive my poor English.
It has been a long time since this problem was reported, have you guys encountered the same problem? In fact, I just encountered the same problem with my product and tried to debug it again.
For a composite primary key we must have non-default values for all fields that are part of the primary key except the auto-increment one. Otherwise we can't guarantee that we'll pull the right record.
As for the reference here ☝️ , this is correct, and the possibility of default values for the keys that make up the composite primary key should be eliminated in the first place. However, since my test case does not deal with default values, it is clearly due to another cause.
The premise of the case I encountered is probably the same as @ceshihao who reported this issue. What it is is that you have a composite primary key consisting of an auto-generated ID and other columns (in this case of type DATETIME). It is especially important that the "auto-generated ID" is included in the composite primary key. The schema definition is introduced again as follows. *** The composite key is used because the date column makes up the range partition. ***
CREATE TABLE `demo`
(
`id` bigint unsigned auto_increment,
`date` datetime(6) not null,
`created_at` datetime not null,
`updated_at` datetime not null,
PRIMARY KEY (`id`, `date`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8
First, we noticed that the test case returns errors such as "db: unable to populate default values for demo". However, since the date column has no default values and there are no duplicate records, we investigate and find that a record was created in the table and the INSERT actually succeeded.
Now, what is the cause of this?
As @ceshihao said, the CanLastInsertID() condition determines that if the table has a composite primary key, you cannot get LastInsertID even if part of the composite primary key is an auto-increment ID. However, this condition is not strict and the mysql driver can return LastInsertID if the column has the AUTO_INCREMENT attribute.
https://github.com/volatiletech/sqlboiler/blob/43dcd896e18b1ca77570d2366901a444ce6b12f5/drivers/table.go#L55-L77
This means that the following expected operations will not be performed, and the retrieval of a successful INSERT record will fail.
https://github.com/volatiletech/sqlboiler/blob/22497a87c693908913b6f1d6572fb6541cb555dc/templates/main/15_insert.go.tpl#L147-L156
https://github.com/volatiletech/sqlboiler/blob/22497a87c693908913b6f1d6572fb6541cb555dc/templates/main/15_insert.go.tpl#L181-L189
In other words, o.ID, which should be assigned an automatically generated ID, remains 0.
Do you think it is possible to fix this problem? If anything is unclear, please ask.
