sqLite Client Disconnected Error

Matt -

I just tried stopping the server, deleting the db file, and restarting the server, then checking to see if the newly created db was good - it was NOT. Both the indigosqlstart command and a plain vanilla sqlite3 command result in "database disk image is malformed".

Hans

[matt (support)]

I haven't a chance to look into this more yet, but right now I haven't a clue. The database file you emailed me also wouldn't open for me (via the command line). I'm also running 10.5.5 and not having any problems getting my system to log to SQLite.

Question: do you have any database elements (Devices, Trigger Action, Variables, etc.) that have high-ASCII characters (accented) in their names or values? I wonder if this is a text encoding problem specific to your database, which would explain why I'm not seeing it.

Matt -

To the best of my knowledge, no high ASCII characters anywhere.

I opened the old db file in sqlite3 - this is the file that contains the last data written. One of the schemas, when I select * from it, ends like this:

19484|2008-09-19 16:53:06|Attic|False|
19485|2008-09-19 16:53:08|Basement|False|
19486|2008-09-19 16:53:08|Blue Room|True|
19487|2008-09-19 16:53:10|Breakfast Nook|False|0
19488|2008-09-19 16:53:11|Breakfast Nook Slave|False|0
19489|2008-09-19 16:53:12|Deer Fence|False|
19490|2008-09-19 16:53:14|Dining Room Painting|False|0
19491|2008-09-19 16:53:17|Dining Room Sideboard|False|0
19492|2008-09-19 16:53:19|Exterior Front Door|False|
19493|2008-09-19 16:53:21|Exterior Garage|False|
SQL error: database disk image is malformed
sqlite>

The others are either fine, or empty, but all end prior to the last entry above.

Hans

Matt -

Tried again just now. Quit the server. Deleted the db file. Restarted the server. The db file was recreated. And it is fine. For the last 10 minutes, anyway. If/when it gets corrupted again, I will email it to you.

Hans

[matt (support)]

Keep me posted. If you can track this down to a specific device state changing (and thus getting logged to SQLite), a variable value changing, or something written to the Event Log, then that would be very helpful.

Matt -

I wrote a crude AppleScript to monitor the database - it basically checks every 5 minutes to see if there has been a db update within the last 15 minutes. So far, so good. Is it possible that a kernel panic would have caused a db corruption? I do experience occasional panics...

Hans

[matt (support)]

Is it possible that a kernel panic would have caused a db corruption?

Yep, that very well might be the source of the problem. Keep us posted.

Matt -

Okay...I just had a kernel panic. I had to hard boot my iMac. After everything started up again (including Indigo/Indigo Server), my automated SQlite check threw an error. I looked at the SQlite database directly, and noted that Indigo was NOT writing to it. I looked at Preferences->Start Server, and confirmed that the checkbox for SQlite was ON. So - I unchecked it, let Indigo/Server restart, then re-checked it, let Indigo/Server restart again, and noted that data is being written into the db again.

Thoughts?

Hans

Matt -

I should point out that the db appeared to be unharmed - I could sqlite3 into it and look at all the data - it seemed as though Indigo/Server had simply stopped writing, even though the check mark was clearly there in prefs.

Hans

[matt (support)]

I'm not too surprised that SQLite is having a hard time starting back up if you have a kernel panic while it is potentially modifying the database. I think maybe the database was locked/corrupt and so it failed to open the first time, but then worked the second time for some reason. Not sure why exactly, but I think you need to figure out what is causing your kernel panics. If you are getting those, then your system isn't stable and you are going to end up with corrupt files that may cause the entire OS to become corrupt.

Matt -

Indigo stopped writing to SQLite again. This time, no kernel panic, no nothing untowards - Indigo just stopped writing about 4 hours ago. My automated check script sent me a fail email. When I got to the machine just now, sure enough - SQLite hadn't been written to in 4 hours. I stopped/restarted the Indigo Server (which runs on this machine) and it started writing to SQLite again. Is there any data I can send you that might illuminate this issue?

Thanks.

Hans

[matt (support)]

Around the time it stopped adding to the database, was there anything of interest in the Event Log file, or the console system log file?

And were you actively doing anything with Indigo when it failed (adding new Device, Trigger Actions, Time/Date Actions, or editing/renaming things)?

Matt -

I think the issue seems to be related to rebooting my machine - the machine that runs the Indigo Server, as well as a client. For example, right now, Indigo is NOT writing to SQLite. I have restarted the server 3 times to no avail. I see this in the log upon restart each time:

SQLite client connected to Indigo Server
SQLite client connected to sqlite_db
SQLite logging all device state changes
SQLite logging all variable value changes

But, SQLite is NOT getting any changes. The database opens fine (in Terminal), and I can see all the old data. Please advise. Thanks.

Hans

[matt (support)]

What happens if you:

1) Stop the Indigo Server.

2) Rename or move your SQLite database file.

3) Launch Indigo Server.

Does the sqlite_db file get recreated and written to correctly then, or does it still fail?

Matt -

It gets recreated and written to correctly.

Hans