tortoise / tortoise/tortoise-orm
bulk_create not compatible with mariadb
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
Describe the bug
Using bulk_create to insert and update values on mariadb causes incompatible sql code to be generated
To Reproduce
users = [
model.User(user_id="1",name="James",email="james@email.com"),
model.User(user_id="2",name="Claire",email="claire@email.com")
]
await model.User.bulk_create(users, update_fields=["name","email"], on_conflict="user_id", batch_size=3)
Expected behavior
The SQL generated and passed is:
INSERT INTO `user` (`user_id`,`name`,`email`) VALUES ('1','James','james@email.com')
AS `new_User` ON DUPLICATE KEY UPDATE `name`='email',`name`='email'
(as it happens I am not sure why name=email is listed twice when I would expect name=name, email=email - so not even sure this would completely work on mysql)
But, on mariadb, the syntax needed is:
INSERT INTO `user` (`user_id`,`name`,`email`) VALUES ('1','James','james@email.com')
ON DUPLICATE KEY UPDATE `name`=VALUES(name),`email`=VALUES(email)
Additional context
Mysql 8 has deprecated VALUES - it does work, but gives a warning (as I understand it), this might require a dialect configuration. Or accept that VALUES works on both mysql and mariadb, even though it generates a warning on mysql
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start at the bulk_create implementation and the SQL generation path for MySQL-compatible databases, then reproduce the supplied example against MariaDB. Done means bulk_create with update_fields and on_conflict produces valid MariaDB SQL with the correct column mappings, while preserving appropriate MySQL behavior; add or run coverage for this case if the existing test layout is found.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mariadb, python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100