What can we do when during tests, wipeDB (SQLite) throws: "uncaught exception: SqliteException (SQLite3 returned ErrorConstraint while attempting to perform step.)"?

[Wizek]

Some more info on StackOverflow:

https://stackoverflow.com/questions/43169704/what-may-cause-an-errorconstraint-sqliteexception-error-with-yesod-sqlite3

wipeDB implementation that I'm using as generated by stack's yesod-sqlite template:

wipeDB :: App -> IO ()
wipeDB app = do
    -- In order to wipe the database, we need to temporarily disable foreign key checks.
    -- Unfortunately, disabling FK checks in a transaction is a noop in SQLite.
    -- Normal Persistent functions will wrap your SQL in a transaction,
    -- so we create a raw SQLite connection to disable foreign keys.
    -- Foreign key checks are per-connection, so this won't effect queries outside this function.

    -- Aside: SQLite by default *does not enable foreign key checks*
    -- (disabling foreign keys is only necessary for those who specifically enable them).
    let settings = appSettings app
    sqliteConn <- rawConnection (sqlDatabase $ appDBSqliteConf settings)
    disableForeignKeys sqliteConn

    let logFunc = messageLoggerSource app (appLogger app)
    pool <- runLoggingT (createSqlPool (wrapConnection sqliteConn) 1) logFunc

    flip runSqlPersistMPool pool $ do
        tables <- getTables
        sqlBackend <- ask
        let queries = map (\t -> "DELETE FROM " ++ (connEscapeName sqlBackend $ DBName t)) tables
        forM_ queries (\q -> rawExecute q [])

Is this a bug in SQLite? Or perhaps, but I suspect less likely, a bug in persistent-sqlite? Or just a change in SQLite that would need to be adapted to by persistent-sqlite?

In the meantime -- since the above StackOverflow link mentions that SQLite 3.9 may be unaffected -- I am trying to downgrade to persistent-sqlite-2.2, which seems to be the last version to use 3.9 or below, namely 3.8.4.3.

[Wizek]

As I've commented on the SO answer too:

I've tried earlier versions of persistent-sqlite on a project of mine and these were my findings:

Good, working versions: 2.2, 2.5, 2.6, 2.6.0.1.
Bad version (throws above mentioned exception): 2.6.2.

The strange thing about this is that both 2.6.2 and 2.6.0.1 seem to use SQLite 3.12.1 internally, so after all, may this be a bug in persistent-sqlite?

[MaxGabriel]

It won't solve this issue directly, but this is the relevant wiki/cookbook page on SQLite foreign keys: https://github.com/yesodweb/yesod-cookbook/blob/master/cookbook/Activate-foreign-key-checking-in-Sqlite.md

Maybe try running the fksEnabled function from that page right before the DELETE FROM statement, to figure out if foreign keys are actually disabled before the delete?

import Database.Persist.Sql (SqlBackend, rawSql, unSingle)

fksEnabled :: MonadIO m => ReaderT SqlBackend m Bool
fksEnabled = do
    fkStatus <- rawSql "PRAGMA foreign_keys" []
    return $ (map unSingle fkStatus) == ["1" :: Text]

fkStatus <- runDB $ fksEnabled
traceM $ "Foreign keys enabled = " ++ show fkStatus
-- Run delete here

[MaxGabriel]

The SO answer that says:

Seems like after sqlite-3.9 (using 3.11 here) the default is PRAGMA foreign_key=ON and PRAGMA foreign_key=OFF have no effect.

Doesn't seem right to me:

Max@maximilians-mbp ~> sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA foreign_keys;
0