Database corruption

Posted on
Fri Aug 10, 2018 6:37 am
lochnesz offline
Posts: 366
Joined: Oct 01, 2014
Location: Stockholm, Sweden

Database corruption

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

Posted on
Fri Aug 10, 2018 6:46 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: Database corruption

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

Image

Posted on
Fri Aug 10, 2018 6:48 am
lochnesz offline
Posts: 366
Joined: Oct 01, 2014
Location: Stockholm, Sweden

Re: Database corruption

I'll change to PostgreSQL then.

Thanks!

Posted on
Fri Aug 10, 2018 9:00 am
jay (support) offline
Site Admin
User avatar
Posts: 18199
Joined: Mar 19, 2008
Location: Austin, Texas

Re: Database corruption

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 (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Fri Aug 10, 2018 9:12 am
lochnesz offline
Posts: 366
Joined: Oct 01, 2014
Location: Stockholm, Sweden

Re: Database corruption

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

Posted on
Fri Aug 10, 2018 9:25 am
jay (support) offline
Site Admin
User avatar
Posts: 18199
Joined: Mar 19, 2008
Location: Austin, Texas

Re: Database corruption

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.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Fri Aug 10, 2018 9:36 am
lochnesz offline
Posts: 366
Joined: Oct 01, 2014
Location: Stockholm, Sweden

Re: Database corruption

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

Skickat från min SM-G960F via Tapatalk

Posted on
Fri Aug 10, 2018 4:48 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Database corruption

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

Posted on
Sat Sep 08, 2018 8:31 am
mundmc offline
User avatar
Posts: 1060
Joined: Sep 14, 2012

Re: Database corruption

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.

Posted on
Sat Sep 08, 2018 10:10 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: Database corruption

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.

Image

Posted on
Sat Sep 08, 2018 1:36 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Database corruption

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

Posted on
Sat Sep 08, 2018 2:12 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Database corruption

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

Posted on
Sat Sep 08, 2018 8:30 pm
mundmc offline
User avatar
Posts: 1060
Joined: Sep 14, 2012

Re: Database corruption

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!

Posted on
Sun Sep 09, 2018 1:43 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Database corruption

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

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 3 guests