Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DatabasePool and legacy databases #99

Closed
BassetMan opened this issue Aug 13, 2016 · 10 comments
Closed

DatabasePool and legacy databases #99

BassetMan opened this issue Aug 13, 2016 · 10 comments
Labels

Comments

@BassetMan
Copy link

Version 0.79.2

Hi, I am trying to improve my app by moving to DatabasePool rather than DatabaseQueue. The database I am working with is downloaded from a web service. I am noticing that I am getting a SQLite error 8: attempt to write a readonly database, after which the app crashes. This led me to believe my issue was permissions or readonly attribute. But the strange thing was that having downloaded the database and the app crashing, re-launching the app successfully opened the database.

After many hours for looking into this, I found that it seems to be linked to the File Format Read/Write version (bytes 18/19 in the database header). The database I am using is in legacy mode for both. After the app crashes these are changed to WAL and everything works. I have even managed to get it working by manually manipulating the mutableBytes of the downloaded database changing bytes 18/19 to 0x02.

I guess my questions are:

  • Is this expected behaviour?
  • Can I not use legacy databases with DatabasePool?

Many Thanks

BTW great library. :-)

@groue
Copy link
Owner

groue commented Aug 13, 2016

Hello @BassetMan,

Strange error indeed. I had not yet heard of such an issue!

If your hypothesis is right, then you may find an answer to the why in SQLite release notes.

If the how remains unexplained, I'd suggest dumping the content of your downloaded database into a fresh one, using SQLite backup feature:

let downloaded = try DatabaseQueue(path: "/path/to/downloaded.sqlite")
let regularPool = try DatabasePool(path: "/path/to/regular.sqlite")
try downloaded.backup(to: regularPool)

If you already have an existing instance of DatabasePool when you want to perform the backup, make sure you backup to this pool instance. This is because you'd risk concurrency errors, should you backup to a new pool instance, while some parts of your application access the database. Reusing your existing Pool should just work (I did not test all scenarios yet, but let's trust SQLite on this one).

The backup will totally replace the content of your regular database if it already exists on disk.

After the backup has completed, you can delete the downloaded legacy database.

I hope this can help!

@groue groue added the support label Aug 13, 2016
@groue groue changed the title DatabasePool DatabasePool and legacy databases Aug 13, 2016
@BassetMan
Copy link
Author

Hi, thanks for getting back to me.

I tried the Backup suggestion, and that resolved the issue.

Many Thanks

@groue
Copy link
Owner

groue commented Aug 17, 2016

Hello @BassetMan. I wonder whether your issue with DatabasePool has been fixed in v0.79.4 (see #102 for details). If you have time, will you please try directly opening a pool to your legacy database, without database duplication, and tell us if the error has gone?

@BassetMan
Copy link
Author

Hi @groue, I've only just got round to testing. But I am still getting the same issue using the legacy database directly.

SQLite error 8: attempt to write a readonly database: file /Library/Caches/com.apple.xbs/Sources/swiftlang/swiftlang-703.0.18.1/src/swift/stdlib/public/core/ErrorType.swift, line 54

The error is in DatabasePool line 81 : let serializedDatabase = try! SerializedDatabase(....

So unfortunately the issue is not resolved.

@groue
Copy link
Owner

groue commented Aug 18, 2016

Thanks a lot for the head up, @BassetMan. Something else is at work here, and we still don't quite know what it is. OK, let's wait and see 😄

groue added a commit that referenced this issue Aug 18, 2016
@swiftlyfalling
Copy link
Collaborator

@BassetMan:

Can you run the following queries on the database, freshly-downloaded from the web service, and post the results that SQLite gives?

(Ideally, do this using a DatabaseQueue, so it does not get converted to WAL mode.)

PRAGMA journal_mode
PRAGMA synchronous
PRAGMA application_id
PRAGMA cache_size
PRAGMA compile_options
PRAGMA encoding
PRAGMA page_size

Or is the database public and would it be possible to obtain a link or a reduced sample database that experiences the same issue?

@BassetMan
Copy link
Author

BassetMan commented Aug 18, 2016

Hi @groue

Please see results below:

PRAGMA journal_mode = delete
PRAGMA synchronous = 2
PRAGMA application_id = 0
PRAGMA cache_size = -2000
PRAGMA compile_options = ENABLE_FTS3, ENABLE_FT5, ENABLE_JSON1, ENABLE_RTREE,
SYSTEM_MALLOC, THREADSAFE=1
PRAGMA encoding = UTF-8
PRAGMA page_size = 1024

Thanks

https://www.dropbox.com/s/n34j5jrlix16a4s/SurveyID_1.db?dl=0

@groue
Copy link
Owner

groue commented Aug 18, 2016

@BassetMan I'm about to take a sunny break, and won't be back here until two weeks. Meanwhile @swiftlyfalling may have a look at your database file (but I can't speak for him/her). Many thanks, to both of you, for your cooperation :-)

@BassetMan
Copy link
Author

@groue no problem, I have a work around :-)
Have a great time.

@groue
Copy link
Owner

groue commented Sep 21, 2016

@BassetMan

I've successfully opened your database using the latest GRDB version v0.84.0 on OS X 10.11.6:

import GRDB

let path = "/Users/groue/Downloads/GRDBIssue99.db"
var configuration = Configuration()
configuration.trace = { print($0) }
let dbPool = try! DatabasePool(path: path, configuration: configuration)

dbPool.write { db in
    print("SQLite version: \(String.fetchOne(db, "SELECT sqlite_version()")!)")
    for row in Row.fetch(db, "SELECT * FROM sqlite_master") {
        print(row)
    }
}

The output:

PRAGMA foreign_keys = ON
PRAGMA journal_mode = WAL
PRAGMA synchronous = NORMAL
SELECT sqlite_version()
SQLite version: 3.8.10.2
SELECT * FROM sqlite_master
<Row type:"table" name:"SURVEYINFO" ...>
<Row type:"table" name:"sqlite_sequence" ...>
<Row type:"table" name:"QUESTIONS" ...>
...

Actually I don't know how to reproduce your issue.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

3 participants