setting journal_mode throws and error

[rgigger]

I'm trying to execute the following statements:

        try! db.execute("PRAGMA journal_mode=WAL")
        try! db.execute("PRAGMA synchronous=OFF")

if I comment out the first one then the second one works fine. But the first one gives the following error:

fatal error: 'try!' expression unexpectedly raised an error: SQLite error 100 with statement PRAGMA journal_mode=WAL;: unknown error: file /Library/Caches/com.apple.xbs/Sources/swiftlang/swiftlang-700.1.101.15/src/swift/stdlib/public/core/ErrorType.swift, line 50

if I copy and page the journal mode command and run it on the command line it runs fine and properly sets the journal mode.

Any idea what's going on here?

[groue]

Hello @rgigger

No, I don't know why you get this SQLite error. Did you google it a little bit, since it's not a GRDB problem? Or do you think GRDB should behave differently?

[rgigger]

yes I googled around for a bit but couldn't find anything so I wondered if it was normal for this to happen and thought I'd ask here. I'll keep looking online. I'll try a different sqlite API, maybe another swift one or maybe objective-c or c and see if I get the same problems everywhere.

[groue]

OK. If you get another result with a different SQLite API, will you please tell me?

[rgigger]

Yes, I will. Thanks!

[groue]

For information, GRDB opens database in the "Multi-thread" mode (see https://www.sqlite.org/threadsafe.html). Maybe it can help your googling a bit.

[rgigger]

Thanks, I'll try to figure out if that is related.

[rgigger]

http://stackoverflow.com/questions/19111064/cannot-use-sqlite-wal-mode

It seems that the journal mode needs to be set when the connection is first created. (I haven't found any documentation stating that for definite but it's the only way I can get it to work)

I now specify the journal mode when creating the connection string

I'm guessing this is it. Is here a way to set the journal mode when connecting with GRDB?

[rgigger]

Actually I'm wondering now if that is accurate. It doesn't seem to be one of the startup flags, and when I do it on the command line it doesn't matter when I do it, it's even fine if I do it in a transaction. I'll keep looking...

[groue]

Not sure, but it may be the first statement ever issued to the database after it has been opened.

There is no way to do that in GRDB right now, and I guess this should be a new property of the Configuration struct: let WALModeEnabled: Bool.

Do you feel like doing it yourself and submitting a PR, or will you wait for me to try and implement it?

[rgigger]

Well, I can't do it right this moment, but I'd be happy to give it a shot tomorrow.

[groue]

OK :-) Let me have my day of work, and I'll see it this evening :-)

[groue]

@rgigger That's weird...

Even when I add the following line at https://github.com/groue/GRDB.swift/blob/v0.35.0/GRDB/Core/Database.swift#L632, just after the connection has been opened, the same error 100 is thrown...

try execute("PRAGMA journal_mode = WAL") // or DELETE, or whatever...

I don't quite know what the problem is, actually. My own googling is dry so far.

[groue]

Hmmm... Even the most minimal code fails:

var sqliteConnection: SQLiteConnection = nil
let connectionCode = sqlite3_open_v2(path, &sqliteConnection, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, nil)
assert(connectionCode == SQLITE_OK)    // OK
var sqliteStatement: SQLiteStatement = nil
let prepareCode = sqlite3_prepare_v2(sqliteConnection, "PRAGMA journal_mode = DELETE", -1, &sqliteStatement, nil)
assert(prepareCode == SQLITE_OK)    // OK
let executeCode = sqlite3_step(sqliteStatement)
assert(executeCode == SQLITE_DONE)    // Oops, we got 100 instead

[groue]

😄 I got it!! This pragma is not an update statement, because it returns a row (quoting https://www.sqlite.org/pragma.html#pragma_journal_mode):

The new journal mode is returned. If the journal mode could not be changed, the original journal mode is returned.

And indeed, your code should read instead:

let journalMode = String.fetchOne(db, "PRAGMA journal_mode=WAL")!
assert(journalMode.uppercaseString == "WAL", "Could not apply WAL journal_mode")
try! db.execute("PRAGMA synchronous=OFF")

OK I'll look at how to produce a better diagnostic for such an unwitting mistake!

[groue]

@rgigger You can update GRDB to v0.36.0. It lets you write try! db.execute("PRAGMA journal_mode=WAL") if you decide to ignore the result of this pragma.

[rgigger]

Oh awesome! The error message makes more sense now.

Thanks for helping me figure this out! GRDB is great btw.

[groue]

The last version of the lib decides to ignore such case, and silently executes the fetch as an update. We were both so dazzled, and this should not happen again :-) Thank you for your bug report, it has helped improving the lib!

[rgigger]

Just a little background on this, I wanted to use WAL mode to speed up the creation of a database with about 300,000 rows that will later be read only. It dropped the run time of the script from about 220 seconds to 20 seconds.

So the other journaling modes are reset for each connection. The only one that sticks is WAL. So it might be good to have a config option to set the journal mode in the configuration to isolate the user from having to deal with the details. If I sent a pull request for this would you accept it?

[groue]

For my performance tests I need to create huge tables, and those huge tables are slow to build... So yes, I'm quite interested!