Indigo Domotics

Hi all,

The Indigo log gives spontaneously this error;
(some text from a looooong list)

2015-11-01 15:45:31.382 SQL Logger Error exception trying to create table device_history_118376550 (" Door-sensor-garage")
2015-11-01 15:45:31.384 SQL Logger Error database disk image is malformed
2015-11-01 15:45:31.385 SQL Logger Error exception trying to insert row into table device_history_118376550 (" Door-sensor-garage")
2015-11-01 15:45:31.388 SQL Logger Error database disk image is malformed
2015-11-01 15:45:31.390 SQL Logger Error INSERT INTO device_history_118376550 ("batterylevel", "batterylevel_ui", "onoffstate") VALUES (%s, %s, %s);

So, I replaced a backup from 3 hours before. This works, errors are gone but after some time these errors came back again.

The SQL logger file is created September 18 2014. File size is 5.35Gb. Is there a limit at the file size?
What is happening here?
When Indigo creates a new file, everything is working fine. But all the history is gone.

Thanks for the help and kind regards,

John

Try indigo utilities - download from my section - to fix the db It worked for me


Sent from my iPhone using Tapatalk

Karl: a link to your solution would be most welcome here...

Thanks Karl, you are my friend! I give it a try. The problem is that we are 4 days further in live, and also with the new SQL file.
I miss the import function in your tool
Thanks for the perceptiveness Jay. It was looking a bit but I've found the tool.

For anyone else;
viewtopic.php?f=164&t=14825

Kind regards,

John

Sorry Karl, but your wonder-util doesn't work;

After almost an hour of "fixing" and renaming the file and start the SQL Logger again I got the same problem;
SQL Logger Error database disk image is malformed
SQL Logger Error INSERT INTO variable_history_1313428195 (value) VALUES (%s);

Loooooong red list again.

Below the Util log.

I placed the 4 days old SQL log back.

Kind regards,

John


----------------------------------------------------------------
starting indigo sqlite utility job 2015-11-05 12:16:17.800129 parameters ['fix'] variable_history_308343422

stepCopy cmd: cp '/Library/Application Support/Perceptive Automation/Indigo 6/logs/indigo_history.sqlite' '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.cp'
stepCopy seconds used :444; error= 0

stepDump started :2015-11-05 12:23:42.254754
stepDump cmd: /usr/bin/sqlite3 '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.cp' .dump > '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.dump'
stepDump seconds used :2731; error= 0

stepRecreate started :2015-11-05 13:09:14.035188
stepRecreate cmd: /usr/bin/sqlite3 '/Library/Application Support/Perceptive Automation/Indigo 6/logs/indigo_history-fixed.sqlite' < '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.dump'

John,

that log file output looks perfect .. the error messages will still be there until you replace the sql file

did you do the other steps? i.e.:
stop indigo server& client
rename files
restart indigo?


Karl

did you do the other steps? i.e.: Yes
stop indigo server& client Yes
rename files Yes
restart indigo? Yes

But even when this finally perhaps maybe should work , I lost 4 days of SQL data.
Do you have a solution for that? (import or so)

If yes, I could give the util a retry (and loose an hour of data)

Kind regards,

John

Jay: if we could enable / disable the sql logger with a call we could fully automate the recovery process.. (stop logger, create a dump, recreate db and restart logger)... or better you could add this in the sql logger menu..

John: most of the data would be fine

Karl

Karl, I have tried it again.

Original (broken) file is 5.35 GB.
Where is the new one and how is it called?
(the earlier try was a lot bigger)

I think it couldn't be the one with this name;
indigo_history-fixed.sqlite
because that's only 11 mb!)

Kind regards,

John


----------------------------------------------------------------
starting indigo sqlite utility job 2015-11-05 12:16:17.800129 parameters ['fix'] variable_history_308343422

stepCopy cmd: cp '/Library/Application Support/Perceptive Automation/Indigo 6/logs/indigo_history.sqlite' '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.cp'
stepCopy seconds used :444; error= 0

stepDump started :2015-11-05 12:23:42.254754
stepDump cmd: /usr/bin/sqlite3 '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.cp' .dump > '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.dump'
stepDump seconds used :2731; error= 0

stepRecreate started :2015-11-05 13:09:14.035188
stepRecreate cmd: /usr/bin/sqlite3 '/Library/Application Support/Perceptive Automation/Indigo 6/logs/indigo_history-fixed.sqlite' < '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.dump'
stepRecreate seconds used :1831; error= Error: near line 33939: PRIMARY KEY must be unique
Error: near line 33948: PRIMARY KEY must be unique
Error: near line 33949: PRIMARY KEY must be unique
Error: near line 33950: PRIMARY KEY must be unique
Error: near line 33951: PRIMARY KEY must be unique
Error: near line 33952: PRIMARY KEY must be unique
Error: near line 33953: PRIMARY KEY must be unique
Error: near line 33954: PRIMARY KEY must be unique
finished indigo sqlite job :2015-11-05 13:39:45.815000
total seconds used :5008
----------------------------------------------------------------
----------------------------------------------------------------
starting indigo sqlite utility job 2015-11-05 15:36:04.804070 parameters ['fix'] variable_history_308343422

stepCopy cmd: cp '/Library/Application Support/Perceptive Automation/Indigo 6/logs/indigo_history.sqlite' '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.cp'
stepCopy seconds used :80; error= 0

stepDump started :2015-11-05 15:37:25.814440
stepDump cmd: /usr/bin/sqlite3 '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.cp' .dump > '/Library/Application Support/Perceptive Automation/Indigo 6/logs/a.dump'
stepDump seconds used :2712; error= 0

stepRecreate started :2015-11-05 16:22:38.152682

John,

I guess the fix did not work. The dump created was not able to recreate a proper data base..

"Error: near line 33948: PRIMARY KEY must be unique"

Would be interested in looking at the file (a.dump), but 10 Gbyte is pretty big, do you have a way to post it on dropbox or something like that?

thx

Karl

Just send you the original Zipped SQL file by WeTransfer.
Thanks for looking und ein schönen Abend!
John

PS: Suddenly the SQL fixed file was 4Gb. I changed the name, closed the Indigo Server and start it again.
No SQL errors at this moment. But I don't know if all the data is in the file.
Kind regards,
John

the sqlite dump creates this output:

Code: Select all

...
INSERT INTO "device_history_1391975966" VALUES(735,'2014-12-02 18:16:43','False');
INSERT INTO "device_history_1391975966" VALUES(736,'2014-12-02 18:29:10','True');
INSERT INTO "device_history_1391975966" VALUES(737,'2014-12-02 18:35:43','False');
INSERT INTO "device_history_1391975966" VALUES(10449,'2014-12-03 00:37:00','False');
INSERT INTO "device_history_1391975966" VALUES(10471,'2014-12-03 00:38:51','False');
INSERT INTO "device_history_1391975966" VALUES(94066,'2014-12-05 12:50:05','False');
INSERT INTO "device_history_1391975966" VALUES(94307,'2014-12-05 12:50:10','False');
INSERT INTO "device_history_1391975966" VALUES(94308,'2014-12-05 12:50:10','False');
INSERT INTO "device_history_1391975966" VALUES(94309,'2014-12-05 12:50:10','False');
INSERT INTO "device_history_1391975966" VALUES(94310,'2014-12-05 12:50:10','False');
INSERT INTO "device_history_1391975966" VALUES(94311,'2014-12-05 12:50:10','False');
INSERT INTO "device_history_1391975966" VALUES(1441,'2014-12-05 12:52:11',0);
INSERT INTO "device_history_1391975966" VALUES(1450,'2014-12-05 19:58:05',1);
INSERT INTO "device_history_1391975966" VALUES(1451,'2014-12-05 19:58:05',0);
INSERT INTO "device_history_1391975966" VALUES(1452,'2014-12-05 19:58:07',1);
INSERT INTO "device_history_1391975966" VALUES(1453,'2014-12-05 19:58:07',0);
INSERT INTO "device_history_1391975966" VALUES(1454,'2014-12-05 19:58:09',1);
INSERT INTO "device_history_1391975966" VALUES(1455,'2014-12-05 19:58:09',0);
INSERT INTO "device_history_1391975966" VALUES(1456,'2014-12-05 19:58:21',1);
INSERT INTO "device_history_1391975966" VALUES(781,'2014-12-02 22:05:08','False');
INSERT INTO "device_history_1391975966" VALUES(782,'2014-12-03 06:19:59','True');
INSERT INTO "device_history_1391975966" VALUES(783,'2014-12-03 06:34:26','False');

...

and that will create a problem when importing it as the id (737 .. 10449 .. 1441 .. 781 ..) is not increasing as set in the definition CREATE TABLE device_history_1391975966 ( id INTEGER PRIMARY KEY....

I should be able to fix that dump file (but too late for you John, just for the next one) , but I don't understand why .dump creates it ... I guess the data base is corrupt

any sql expert input?


Karl

and that will create a problem when importing it as the id (737 .. 10449 .. 1441 .. 781 ..) is not increasing as set in the definition CREATE TABLE device_history_1391975966 ( id INTEGER PRIMARY KEY....

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

Thanks for all the input.
It's a very strange problem. The new fixed SQL file was 4Gb when I placed it back in the Logs folder.
This morning it's 14.3 Mb....
So, I'm afraid I lost a lot of data.
And the Indigo Util can't fix this (Thanks anyway for your help Karl).

So, you can have a perfect every hour backup but when an SQL database is crashed, inside, then it's done....
And we have to start over with a new year

Kind regards,

John