Indigo Domotics

I run SQLite as database with SQL Logger. Every time there is a power cut, the database gets corrupted. Is there a difference between SQLite and PostgreSQL regarding how well they handle power cuts/unexpected shoutdowns?

Thanks!

Peter

Yes. I'm pretty sure you won't have any problems with PostgreSQL in that regards. It is significantly more robust.

I'll change to PostgreSQL then.

Thanks!

Or get a UPS for your Indigo Server Mac and have it do an orderly shutdown when necessary. That would also protect your Mac hardware.

jay (support) wrote:

Or get a UPS for your Indigo Server Mac and have it do an orderly shutdown when necessary. That would also protect your Mac hardware.

I have one which is broken and will be replaced, however it is not rare with longer power cuts than a "home" ups can handle in the area.

Skickat från min SM-G960F via Tapatalk

But UPSs with USB ports can be connected to the Mac and the Mac can then perform an orderly shutdown (which will avoid the corruption issue) when the battery on the UPS gets low.

Yes, the new ups will have a USB port for sure.

Skickat från min SM-G960F via Tapatalk

Depending on how most history you have in the corrupt sqlite db and how much you want to keep it, you may have some luck with Karl's utilities and fixing the corruption, then migrating to postgres app. It worked for me several times. Take a long time to run though

Either way there almost needs to be a indigo newbie guide that explains that the 30 mins to set up postgres app right at the start is really worth it to avoid this pain later. I can't recommend it enough.


Sent from my iPhone using Tapatalk

I'm in the weeds here after a similar power loss and database corruption (i think):

Code: Select all


Sep 8, 2018, 10:27:29 AM
   Enabling plugin "SQL Logger 2.0.7"
   Starting plugin "SQL Logger 2.0.7" (pid 1792)
   Started plugin "SQL Logger 2.0.7"
   SQL Logger                      connected to /Library/Application Support/Perceptive Automation/Indigo 7/Logs/indigo_history.sqlite
   SQL Logger                      updating all device tables
   SQL Logger Error                exception trying to create table device_history_28446439 ("AIRFOIL_okimac2013")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_28446439 ("AIRFOIL_okimac2013")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_28446439 ("canconnect", "canremotecontrol", "instancename", "protocolversion", "sourcealbum", "sourceartist", "sourcegroup", "sourceidentifier", "sourcemachinemodel", "sourcemachinename", "sourcemetadataavailable", "sourcename", "sourceplaystatus", "sourceplaystatus_error", "sourceplaystatus_paused", "sourceplaystatus_playing", "sourceplaystatus_stopped", "sourceplaystatus_unavailable", "sourceremotecontrolavailable", "sourcetrackname", "status", "status_connected", "status_disconnected", "status_unavailable") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
   SQL Logger Error                exception trying to create table device_history_1421122410 ("Alexa Den")
   SQL Logger Error                database disk image is malformed


Is there a QUICK fix to this? Or do I need to go the PostGreSQL route?

Edit/ Update: Restoring from a Time Machine backup did not work. Karl's utility did not work.

I was going to suggest Karl's utility to fix it. When you ran that, what did it report/show in the Event Log window? You might try posting those results in Karl's sub-forum for the utility. He probably knows the most about trying to fix those corrupt databases.

mundmc wrote:

I'm in the weeds here after a similar power loss and database corruption
Is there a QUICK fix to this? Or do I need to go the PostGreSQL route?

Edit/ Update: Restoring from a Time Machine backup did not work. Karl's utility did not work.

Might be worth another try with Karl's plugin, or try on another time machine version. The utility can take a very very long time to run. Depends how much you want the history.

Switching to postgres will stop you getting these problems in the future but itself won't recover the corrupted data. Once you've fixed it you can convert it.
It took me a few goes to get it fixed and converted, over a few evenings but I didn't want to lose the temperature and power history.



Sent from my iPhone using Tapatalk

If you use SQLite I strongly suggest to use the utilities dB backup option. Time machine does not create a valid copy of the SQLite file.

The utility creates a sqldump into the ~/indigo/utilities directory. Then time machine can produce a valid copy.

Time machine against a life Database file does not work. It takes much too long and then the file is not consistent as it gets heavily modified during copy.


Sent from my iPhone using Tapatalk

Thanks guys, I really appreciate your responses.

My PSA, for other dumbasses like myself, is to (if you don't REALLY need the data in the sql database) just delete the file /Library/Application Support/Perceptive Automation/Indigo 7/Logs/indigo_history.sqlite, then let Indigo rebuild a new one.

I know this is obvious to everybody else in this sub-forum except for some lost souls like myself!

If you are going to keep the data history in future, once you've deleted the SQL file, install postgresapp and run that. You won't get this problem again.



Sent from my iPhone using Tapatalk