Repository navigation
Runtime error with putMany on Sqlite #847
Description
Bug Reports
Reporting a bug? If relevant, we recommend including:
-
Your OS name and version: Ubuntu 18.04
-
The versions of tools you're using:
λ ~/ stack --version Version 1.9.3, Git revision 40cf7b37526b86d1676da82167ea8758a854953b (6211 commits) x86_64 hpack-0.31.1 -
The versions of dependencies you're using:
resolver: lts-13.3 -
Finally, if possible, please reproduce the error in a small script; we have created a script for each backend with a minimal setup to use for this purpose. These are made with Stack's scripting support and can be run as
./file-name.hs. Even better, writing a failing test case is tremendously helpful.
Repro script available as a gist here
Output is:
$ ./repro.hs
Migrating: CREATE TABLE "person"("id" INTEGER PRIMARY KEY,"name" VARCHAR NOT NULL)
repro.hs: SQLite3 returned ErrorError while attempting to perform prepare "INSERT INTO \"person\"(\"name\") VALUES (?) ON CONFLICT () DO UPDATE SET \"name\"=EXCLUDED.\"name\"": near ")": syntax error
Activity
psibi commentedon Jan 16, 2019
@naushadh I assume this would have arisen because of https://github.com/yesodweb/persistent/pull/833/files which seemed to modify
putManysql query. Can you look into this ?parsonsmatt commentedon Jan 16, 2019
For what it's worth,
putManyseems to work fine on a table that has a Unique key.parsonsmatt commentedon Jan 16, 2019
The grammar for Sqlite is surprising. It seems to suggest that you should be able to write
ON CONFLICT DO UPDATE .... However, this consistently fails with a syntax error. If I doON CONFLICT DO NOTHINGit's fine. But it seems to consistently fail without a key providedON CONFLICT (id) DO UPDATE ...parsonsmatt commentedon Jan 16, 2019
I've made a mailing list post for this issue: https://www.mail-archive.com/[email protected]/msg113513.html
parsonsmatt commentedon Jan 16, 2019
The grammar doesn't tell the whole story:
(source)
Fix yesodweb#847: Use simple insert when there are no unique constrai…
Merge pull request #848 from naushadh/fix-847
psibi commentedon Jan 17, 2019
@parsonsmatt A new persistent version has been released with a fix. @naushadh Thanks for the quick fix!
parsonsmatt commentedon Jan 17, 2019
Thanks 😄