SQLite file format error?

[Waher]

A very strange bug, but a critical once since it could in theory corrupt my entire database.

Basically, at some point when running the server, no matter what I did (and even the same thing with aps_demo), it could not talk to the database at all. The database log showed:

o Got request: SELECT player FROM pwdata WHERE player='Chris_Waher' AND tag='Monk' AND name='demoName'
! SQL Error: unsupported file format
o Sent response (0 bytes):

When I deleted the database file, everything worked again. However everything only worked when I completely deleted the database, which is so very bad that it's hard to put into words. Using sqlitecc.exe everything was still available and looked good.

Any idea how to make sure this does not repeat in the future? How to recover from such a bug?
_________________
Kristo Vaher
Developer of Teramyre PW

[Gryphyn]

[Waher]

That's also the only thing I found, however I have only accessed the database either through nwnx_odbc or sqlitecc.exe. Neither caused problems the dozens of times I used them before, until this error appeared.

One of the solutions is, of course, to export everything from the file format that can't be accessed and then import it again to a clean database, but once databases become really large, that's quite a hassle to do.

The thing that worries me most is that I don't know what exactly causes it, so I don't exactly know how to prevent it next time.
_________________
Kristo Vaher
Developer of Teramyre PW

[Waher]

It seems I found the thing that causes it. Using sqlitecc and executing vacuum command on database table will make it unreadable, entirely, by NWN.

Anything that can be done against that?

EDIT: Seems not to be the case, whatever SQL files I create with sqlitecc end up corrupted at some point, this is really bad..
_________________
Kristo Vaher
Developer of Teramyre PW

[Waher]

I am desperate here, now both the SQLite tool links in NWNX documentation are broken and I've tried downloading various tools, most of which corrupt the database and have NWNX say errors like 'encrypted or not a database' or 'unsupported file format'.

Could someone please link to me a working tool that they use with NWN and NWNX for SQLite databases that has no problems?
_________________
Kristo Vaher
Developer of Teramyre PW

[Zebranky]

Can they at least open the DB and view the data? At this point I'd recommend migrating to MySQL if that's at all an option.
_________________
Win32 SVN builds: http://www.mercuric.net/nwn/nwnx/

<Fluffy-Kooshy> NWNx plugin is to this as nuclear warheads are to getting rid of fire ants.

<ThriWork> whenever I hear nwn extender, I think what does NWN need a penis extender for?

[Waher]

[Mithreas]

Chris, I'd strongly recommend switching to MySQL. For one thing, we're using it for pretty much everything on Arelith so it's been stress tested, and we find it really easy to manage. The other day, for example, I had to do a selective restore of one table from a backup a few hours old, without affecting any other data, and the whole process took me a handful of minutes.

My experience of SQLite has been less positive: I found it a lot more of a hassle to work with.
_________________
Admin team, Arelith PW
Hakless world that relies heavily on the great work done by the NWNX team.

[Waher]

[Mithreas]

Gotcha.

I'm not a database expert, but from a server admin perspective I'd strongly urge you to go with whatever is easiest for you to maintain. Server admining is a lot of work and the last thing you want to do is to impose a bunch of additional routine manual work on yourself when you don't have to. In that light, it's not simply a technical choice between the two - now that you're running your own place you will want to start thinking of technical solutions in terms of 'how does this reduce work for me and my DMs'. This mindset is one of the secrets of Arelith's longevity - there's nothing that will suffer if all the admin team are unavailable for a week or two.

-Mith
_________________
Admin team, Arelith PW
Hakless world that relies heavily on the great work done by the NWNX team.

[Waher]