SQL Logger: "database disk image is malformed"

[jay (support)]

At some point, the database file became corrupt in some way (it can happen when the Mac hangs/crashes or when there's a power loss). If you have Time Machine backups, you might be able to go backwards in time trying various dates to see if you can recover the db before the corruption occurred.

Thanks for the answer Jay. The Mac is connected to an UPS.
I'm not an SQL expert and I sent Karl one of my latest backup files before the crash.
See former posts for the results..

Kind regards,

John

[jay (support)]

That was my point - the corruption could have started before your latest backup, which is why I recommended going back further.

Did you check the entire table's dump? I have no idea if it is the same in SQLite, but in a "normal" relational database, a table-scan (where you have not done an order-by during retrieval of records) will not always be in primary key order. Often it will, but depending upon storage and fragmentation and many other items, keys do not 100% always flow in order. Obviously with an ORDER BY it would.

If some records were already in the target DB, of course, you could have PK collisions. Also could have issues if the import succeeded but the start of the counter was not updated to be greater than the highest imported key value. No idea how SQLite handles this either, but during bulk loads of the database it is not uncommon to have to seed the PK of the destination table to MAX([PKColumn]) + 1.

SQLite does some funny things in my little experience with it, which is why I dropped it for Postgre. That isn't perfect but it is much better.

Adam

Adam, thanks for the answers ... results in more questions ..

this is a simple "sqlite3 database .dump > a.dump" command
when the database is not corrupt the ids are all in sequence.
That output is what I use to test if the db is ok: "sqlite3 newbd < a.dump" if no error it seems to be ok

if the ids are out of sequence, it fails. ... (the date-time stamps are in sequence though)

Is there a simple export command that also creates the tables as produced by .dump or am I better off to just sort the stuff with date-time-stamp and create the ids incremental when creating the fixed dump file?
Karl

Well, if you are not explicitly specifying the output, one would assume that their dump/restore would work as they have coded. Perhaps the database is corrupt in that particular table... maybe investigate if there is a way to ignore errors or tables that fail during the import process. That would get some/most of your data recovered (unless the corruption is more wide-spread).

Try finding that table that you know is giving errors in the dump and examine the SQL statements. Maybe an ID is duplicated in the statements (though not sure how that would ever be IN the database, but if it is corrupt I guess anything could be possible). May have to break the inserts into chunks to identify what is duplicated.

Adam

john,
I can fix the one you send me, but it will have 5+ days missing.. and if you send me another pack of dutch cookies we can talk about adding the data from your current database..

may I suggest to add an action to your system to create a tested backup copy every night (check out the backup option in the utility). This will tell you in the log file if you have a corrupted database.
you then can use the one from the day before. You then only lose a day or so. The time machine backups are done very slowly over several minutes and might not create a working db as indigo continues to write to the db while Time Machine is copying. with the utility is does a normal copy which is MUCH faster and the likely hood that you have a problem is much smaller. and if you don't restart indigo it might not indicate a problem for several days..

Karl

Thanks for all your work Karl!
I'm super happy, the history was saved by you!

Kind regards,

John

question to Jay/Matt,

how would you trigger on a log file event for a bad sql database event.

I get to the field where you have to enter text "Errors to monitor for" (see screenshot) for the event type you like to trigger on. I don't know the exact text we need to enter. ... it is difficult to test for that as I can not easily create an sql error.


Karl

is it:

SQL Logger Error

?

Karl

I had a power failure and had the mac connected to the AC backup but not to the battery outlet only to the surge protector..

yes it is "SQL Logger Error"

one good thing I could test the indigo utilities sql recovery and that seems to have worked. lost ~ 50 minutes of data. .. Naturally that depends on the nature of the damage


Karl