Unknown collation REVID trying to import .sql file

[Brendan Duddridge]

I'm trying to help a customer recover their database due to a corrupted SQLite file they have. I've been able to export a dump of the database to a .sql file but when I try to import it I get an error "no such collation sequence: REVID".

According to PRAGMA collation_list, the original sqlite file has the following collations defined:

JSON

REVID

RTRIM

NOCASE

BINARY

I'm using SQLiteManager on macOS to import the sql file into an empty .sqlite file.

The data seems to be intact in the corrupted .sqlite file, but the maps_# tables seem to be corrupted or something because the views can't seem to be indexed without generated a 'database disk image malformed' error.

So is there a way to tell SQLite to ignore the collations when it imports the .sql file?

Or maybe there's a function in CouchbaseLite that I can call to generate a backup of the database that I can then restore with?

Thanks,

Brendan

[Jens Alfke]

On Sep 23, 2016, at 12:38 PM, Brendan Duddridge <bren...@gmail.com> wrote:

I'm trying to help a customer recover their database due to a corrupted SQLite file they have. I've been able to export a dump of the database to a .sql file but when I try to import it I get an error "no such collation sequence: REVID".

Yes, this happens if you try to open a CBL database with another program like the `sqlite3` tool or a SQLite editor app. CBL registers a few custom collators named REVID and JSON. Those are defined by C callbacks, so another program won’t know what they are. Without those collators defined, SQLite can’t operate on those tables or indexes.

Jim Borden built a little extension library that registers those collators. If SQLiteManager supports loading extensions, you can use that. I’ll try to attach it here:

[Brendan Duddridge]

Oh that's cool! Unfortunately it didn't work with SQLiteManager. It does have a function for loading extensions and I did load the .dylib, but I still got the unknown collation REVID.

What SQLite manager tool do you use?

[Jens Alfke]

On Sep 23, 2016, at 1:28 PM, Brendan Duddridge <bren...@gmail.com> wrote:

Oh that's cool! Unfortunately it didn't work with SQLiteManager. It does have a function for loading extensions and I did load the .dylib, but I still got the unknown collation REVID.

What SQLite manager tool do you use?

Usually `sqlite3`, but I do have SQLiteManager and it works, using that extension. I went to Preferences, opened the extensions path as a folder, then copied the dylib into it and restarted.

—Jens

[Brendan Duddridge]

Well that's strange. I did the same thing too and it didn't work. Maybe because I'm on macOS Sierra. Using SQLiteManager version 4.6.6.

When I import the db.sql file it fails with the same unknown REVID. And even if I view the revs table and try sorting the records by doc_id or revid I get the error too. Odd.

[Jens Alfke]

On Sep 23, 2016, at 8:36 PM, Brendan Duddridge <bren...@gmail.com> wrote:

Well that's strange. I did the same thing too and it didn't work. Maybe because I'm on macOS Sierra. Using SQLiteManager version 4.6.6.

I’m also on Sierra, using app version 4.5. Weird!

—Jens