SQLite error: "database disk image is malformed"

wimleers · September 14, 2022, 11:38am

While using SBFspot and trying to get my historical data uploaded (I just donated! ), I had sqlite3 open to do SELECT * FROM Config; (as instructed at SBFspot historical data - #7 by Depechie). It worked fine. Then suddenly, after a reboot, I got this:

Error: in prepare, database disk image is malformed (11)

And unfortunately, SBFspot’s log seems to confirm this:

<snip>
sbfspot-sbfspot-1  | Current Inverter Time: 07/09/2022 22:23:49

sbfspot-sbfspot-1  | Inverter Wake-Up Time: 07/09/2022 20:25:06

sbfspot-sbfspot-1  | Inverter Sleep Time  : 07/09/2022 22:22:12

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: sqlite3_exec() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | INSERT OR IGNORE INTO Inverters VALUES(3010933557,'SN: 3010933557','Sunny Boy 5.0','04.00.55.R',0,0,0,0,0,0,'','',0)

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: exec_query() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | INSERT OR IGNORE INTO Inverters VALUES(3010933557,'SN: 3010933557','Sunny Boy 5.0','04.00.55.R',0,0,0,0,0,0,'','',0)

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: sqlite3_exec() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | UPDATE Inverters SET Name='SN: 3010933557',Type='Sunny Boy 5.0',SW_Version='04.00.55.R' WHERE Serial=3010933557

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: exec_query() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | UPDATE Inverters SET Name='SN: 3010933557',Type='Sunny Boy 5.0',SW_Version='04.00.55.R' WHERE Serial=3010933557

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: sqlite3_exec() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | UPDATE Inverters SET TimeStamp=1662582231,TotalPac=0,EToday=16666,ETotal=893400,OperatingTime=596.753,FeedInTime=562.189,Status='OK',GridRelay='N/A',Temperature=0 WHERE Serial=3010933557

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: exec_query() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | UPDATE Inverters SET TimeStamp=1662582231,TotalPac=0,EToday=16666,ETotal=893400,OperatingTime=596.753,FeedInTime=562.189,Status='OK',GridRelay='N/A',Temperature=0 WHERE Serial=3010933557

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: sqlite3_exec() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | INSERT INTO SpotData VALUES(1662582231,3010933557,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,16666,893400,0,596.753,562.189,0,'OK','N/A',0)

sbfspot-sbfspot-1  | [2022-09-07 22:23:51.048] Error: [spot_data]exec_query() returned: 'database disk image is malformed' while executing

sbfspot-sbfspot-1  | INSERT INTO SpotData VALUES(1662582231,3010933557,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,16666,893400,0,596.753,562.189,0,'OK','N/A',0)

sbfspot-sbfspot-1  | ********************

sbfspot-sbfspot-1  | * ArchiveDayData() *

sbfspot-sbfspot-1  | ********************

sbfspot-sbfspot-1  | startTime: 08/06/2022 00:00:00

sbfspot-sbfspot-1  | ********************

sbfspot-sbfspot-1  | * ArchiveDayData() *

sbfspot-sbfspot-1  | ********************

sbfspot-sbfspot-1  | startTime: 07/06/2022 00:00:00

sbfspot-sbfspot-1  | Wed Sep  7 22:23:51 2022: INFO: Done.

sbfspot-sbfspot-1  | Wed Sep  7 22:23:51 CEST 2022

sbfspot-sbfspot-1  | Sleeping 600 seconds.

sbfspot-sbfspot-1 exited with code 137

sbfspot-sbfspot-1 exited with code 0

How do I go about solving this?

sbfspot · September 7, 2022, 10:49pm

I´m afraid your SD-card just passed away.

wimleers · September 8, 2022, 8:52am

Yeah, I feared that it’s largely unrecoverable

I’m running SBFSpot using https://github.com/nakla/sbfspot in Docker on a Mac Mini M1. Docker mounts a directory that contains the SQLite DB. I bet this was caused by my trying to observe what was going on in the SQLite DB while Docker was writing to it.

I re-initialized my database. It’s up and running again. Fortunately, I had CSV exports enabled as well! So I have not lost any of the data. Yay for plain file data storage! But … how do I import the data in the old CSV files into my new DB?

sbfspot · September 8, 2022, 10:48am

To import you can use .mode csv and .import commands

My db file is backed up to dropbox every day

wimleers · November 2, 2022, 9:54pm

I’ve been pulling my hair out over this … I can’t figure out how to use sqlite3's .csv and .import commands on the 70 NAME-Spot-YYYYMMDD.csv files I have. The data in the CSV files needs to be transformed first before it can actually be imported?

The closest information I could find is https://github.com/SBFspot/SBFspot/issues/100 — but that’s just a feature request from a long time ago Did I really lose the ability to upload my full history here?!

SBF · November 3, 2022, 9:56am

What’s the exact problem? Any error messages?
I don’t remember I ever needed this, but I’ll try it asap.

SBF · November 9, 2022, 5:32pm

No, you can’t import a .CSV file as such. To start, you have to import into a temporary table and then insert the converted records in the actual table. The import and conversion process depends highly on the structure of the CSV (depending on the SBFspot config settings)
I tried it and this is what I have so far:

SQLite version 3.27.2 2019-02-25

Field separator in CSV:

.separator ;

Import the CSV into a temporary table (tt) skipping the headerlines (keep the fieldnames only):

drop table if exists tt;
.import '|tail -n +5 NAME-Spot-YYYYMMDD.csv' tt

The biggest challenge is to convert the human readable local datetime to unixepoch - example converts DD/MM/YYYY hh:mm:ss to unixepoch:

strftime('%s', datetime(substr("dd/MM/yyyy HH:mm:ss", 7, 4) || '-' || substr("dd/MM/yyyy HH:mm:ss", 4,2) || '-' || substr("dd/MM/yyyy HH:mm:ss", 1,2) || ' ' || substr("dd/MM/yyyy HH:mm:ss",12,8),'utc'))

If your CSV contains numbers with comma, replace the comma with dot:

replace(Pdc1,',','.') as Pdc1

With that in mind, this is the final insert query:

insert into spotdata
    select strftime('%s', datetime(substr("dd/MM/yyyy HH:mm:ss", 7, 4) || '-' || substr("dd/MM/yyyy HH:mm:ss", 4,2) || '-' || substr("dd/MM/yyyy HH:mm:ss", 1,2) || ' ' || substr("dd/MM/yyyy HH:mm:ss",12,8),'utc')) as TimeStamp,
    Serial,
    replace(Pdc1,',','.') as Pdc1,
    replace(Pdc2,',','.') as Pdc2,
    replace(Idc1,',','.') as Idc1,
    replace(Idc2,',','.') as Idc2,
    replace(Udc1,',','.') as Udc1,
    replace(Udc2,',','.') as Udc2,
    replace(Pac1,',','.') as Pac1,
    replace(Pac2,',','.') as Pac2,
    replace(Pac3,',','.') as Pac3,
    replace(Iac1,',','.') as Iac1,
    replace(Iac2,',','.') as Iac2,
    replace(Iac3,',','.') as Iac3,
    replace(Uac1,',','.') as Uac1,
    replace(Uac2,',','.') as Uac2,
    replace(Uac3,',','.') as Uac3,
    replace(EToday,',','.') as EToday,
    replace(ETotal,',','.') as ETotal,
    replace(Frequency,',','.') as Frequency,
    replace(OperatingTime,',','.') as OperatingTime,
    replace(FeedInTime,',','.') as FeedInTime,
    replace(BT_Signal,',','.') as BT_Signal,
    Condition,
    GridRelay,
    replace(Temperature,',','.') as Temperature
from tt;

That’s all

It’s clear you have to try this with a copy of your production database.

wimleers · November 19, 2022, 6:23pm

THANK YOU SO MUCH! That worked! I’ve now got everything going back to June 24 in there

Now I’m at the final step: how do I get the historical data into G2Cv1 6.000kW? I’ve been fighting this since September 7. I’m a donor, so I should be able to go back to August at least?

The thing is that vwPvoData doesn’t show the data from June, even though it’s in the other views:

sqlite> SELECT * FROM vwSpotData ORDER BY TimeStamp ASC LIMIT 2;
TimeStamp|Nearest5min|Name|Type|Serial|Pdc1|Pdc2|Idc1|Idc2|Udc1|Udc2|Pac1|Pac2|Pac3|Iac1|Iac2|Iac3|Uac1|Uac2|Uac3|PdcTot|PacTot|Efficiency|EToday|ETotal|Frequency|OperatingTime|FeedInTime|BT_Signal|Status|GridRelay|Temperature
2022-06-24 18:27:02|2022-06-24 18:25:00|SN: 3010933557|Sunny Boy 5.0|3010933557|543|557|2.068|2.133|262.76|261.48|977|0|0|4.169|0.0|0.0|242.64|0.0|0.0|1100|977|88.8|15.668|428.303|49.97|247.77|236.521|0.0|Ok|Closed|0.0
2022-06-24 18:28:58|2022-06-24 18:30:00|SN: 3010933557|Sunny Boy 5.0|3010933557|523|539|1.994|2.033|262.76|265.27|940|0|0|4.012|0.0|0.0|242.93|0.0|0.0|1062|940|88.5|15.699|428.333|50.01|247.803|236.554|0.0|Ok|Closed|0.0

and

sqlite> SELECT * FROM vwAvgSpotData ASC LIMIT 2;
Nearest5min|Serial|Pdc1|Pdc2|Idc1|Idc2|Udc1|Udc2|Pac1|Pac2|Pac3|Iac1|Iac2|Iac3|Uac1|Uac2|Uac3|Temperature
2022-06-24 18:25:00|3010933557|543.0|557.0|2.068|2.133|262.76|261.48|977.0|0.0|0.0|4.169|0.0|0.0|242.64|0.0|0.0|0.0
2022-06-24 18:30:00|3010933557|523.0|539.0|1.994|2.033|262.76|265.27|940.0|0.0|0.0|4.012|0.0|0.0|242.93|0.0|0.0|0.0

… yet:

sqlite> SELECT * FROM vwPvoData ORDER BY TimeStamp ASC LIMIT 2;
TimeStamp|Name|Type|Serial|V1|V2|V3|V4|V5|V6|V7|V8|V9|V10|V11|V12|PVoutput
2022-08-13 06:30:00|SN: 3010933557|Sunny Boy 5.0|3010933557|429842|0|||||||||||1
2022-08-14 06:30:00|SN: 3010933557|Sunny Boy 5.0|3010933557|429842|0|||||||||||1

I think it’s because of:

sqlite> SELECT * FROM DayData ORDER BY TIMESTAMP ASC LIMIT 5;
TimeStamp|Serial|TotalYield|Power|PVoutput
1660365000|3010933557|429842|0|1
1660451400|3010933557|429842|0|1
1660451700|3010933557|429842|0|1
1660452000|3010933557|429842|0|1
1660452300|3010933557|429842|0|1

because that’s the first table in the vwPvoData view’s query

So close!

wimleers · November 19, 2022, 9:29pm

Got it!

Applied a similar strategy to what you showed me, but now also for DayData.

To import:

.import '|tail -n +9 NAME-20220624.csv' t2
…
.import '|tail -n +9 NAME-20220813.csv' t2

(to import from June 24 until August 13, which was the missing day data.)

And then delete the unnecessarily imported header rows like so:

sqlite> SELECT COUNT(*) FROM t2 WHERE kW LIKE 'kW';
COUNT(*)
50
sqlite> DELETE FROM t2 WHERE kW LIKE 'kW';
sqlite> SELECT COUNT(*) FROM t2 WHERE kW LIKE 'kW';
COUNT(*)
0

Now we’re good to go for importing into DayData

insert into daydata
    select strftime('%s', datetime(substr("dd/MM/yyyy HH:mm:ss", 7, 4) || '-' || substr("dd/MM/yyyy HH:mm:ss", 4,2) || '-' || substr("dd/MM/yyyy HH:mm:ss", 1,2) || ' ' || substr("dd/MM/yyyy HH:mm:ss",12,8),'utc')) as TimeStamp,
    3010933557 AS Serial,
    replace(kWh,',','') as TotalYield,
    replace(kW,',','') as Power,
    NULL AS PVoutput
from t2;

… where you replace 3010933557 with your serial number of course.

And now I can indeed correctly see all the data that is not yet on pvoutput.org:

sqlite> SELECT MIN(Timestamp) FROM vwPvoData WHERE pvoutput IS NULL;
2022-06-24 00:00:00
sqlite> SELECT MAX(Timestamp) FROM vwPvoData WHERE pvoutput IS NULL;
2022-10-29 18:20:00
sqlite> SELECT MIN(Timestamp) FROM vwPvoData WHERE pvoutput IS NULL AND timestamp > date('2022-09-01');
2022-10-15 16:25:00

(Data since August 13 is on pvoutput.org, and the 90-day limit prevents me from importing older data. The Oct 15-Oct 29 data refuses to upload because of corrupt data — see https://forum.pvoutput.org/t/mysterious-14-day-gap-in-uploads-and-now-again-the-last-8-days/5942/3.)