Database repair (utility meter totals)

[SumDum]

Version: 2024.7 (SjoerdNLD build)
Platform: Pi B+
OS: Buster Lite
Hardware: MQTT LAN interface, Enphase Envoy, 1wire (owfs)

Something in my system suffered a brain fart yesterday afternoon. Below is an extract from my nightly Domoticz database backup, ID 50 is my solar production, at 15:20 it almost doubled in 5 minutes.

50 7099466 14460 2025-04-16 15:10:01 0.0
50 7099591 13730 2025-04-16 15:15:01 0.0
50 7099699 15490 2025-04-16 15:20:00 0.0
50 14199380 14400 2025-04-16 15:25:01 0.0
50 14199496 13850 2025-04-16 15:30:01 0.0
50 14199622 13330 2025-04-16 15:35:01 0.0

I'm not blaming Domoticz here, it could equally have been my Envoy or a network gremlin. Right now my Enphase Envoy is reporting on api/v1/production:

Code: Select all

{
  "wattHoursToday": 7334,
  "wattHoursSevenDays": 83388,
  "wattHoursLifetime": 7109139,
  "wattsNow": 1083
}

Domoticz is still reporting ~7 megawatt hours ahead. It's the first time it's happened in 5 years of having solar PV so I'm not fussed about what happened, I don't have evidence to point fingers in any case. I just need to fix my data.

In Influx I can just overwrite the bad values with corrected ones at the matching timestamp. But I need to correct the Domoticz database too. If I stop domoticz, correct the bad values and restart it will I break anything? Are there checksums on the data I'd also have to correct for example?

Thanks
Sumdum

[SumDum]

How the hell do i fix the data in Domoticz?

I've tried downloading the database from backup/restore, fixing the Meter and Meter_Calendar tables and uploading using restore. Result: Meter_Calendar fixed. Meter table uploaded values totally ignored apart from the last one entry provided but Domoticz has logged more bad data since.

So I stopped Domoticz, downloaded the database, fixed Meter again then renamed the .db-wal and .db-shm files and uploaded my corrected database. Bad data sent to MQTT again.

What else do i have to fix? This is becoming infuriating!

[SumDum]

Arghhhh!!!

Fixed the value for device 50 in DeviceStatus and as well, still on restart the values pushed to MQTT are roughly double what my Envoy is reporting as is the new value put into DeviceStatus. What on earth is going on?

[waltervl]

You probably also have to update the Meter_Calendar table too.
But yes, fixing these counter values in the database manually is a PITA. I stay away from it if I can.
Better delete the incorrect high value the next day from the month graph by shift - click on it.

[SumDum]

Having walked away and taken time to think it through I'm still stuck. I have fixed:

Meter_Calendar
Meter
DeviceStatus

In all of these the highest number stored for my production total is 7114821.

looking at the code id seems the api in use is {ip}/production.json?details=1, which right now is returning this:

Code: Select all

{"production":[{"type":"inverters","activeCount":13,"readingTime":1745051430,"wNow":221,"whLifetime":7255452},{"type":"eim","activeCount":1,"measurementType":"production","readingTime":1745051513,"wNow":310.7,"whLifetime":7117187.875,"varhLeadLifetime":2111.731,"varhLagLifetime":5993765.899,"vahLifetime":12621399.547,"rmsCurrent":1.663,"rmsVoltage":242.356,"reactPwr":245.63,"apprntPwr":402.707,"pwrFactor":0.78,"whToday":281.875,"whLastSevenDays":64625.875,"vahToday":2152.547,"varhLeadToday":0.731,"varhLagToday":2055.899,"lines":[{"wNow":310.7,"whLifetime":7117187.875,"varhLeadLifetime":2111.731,"varhLagLifetime":5993765.899,"vahLifetime":12621399.547,"rmsCurrent":1.663,"rmsVoltage":242.356,"reactPwr":245.63,"apprntPwr":402.707,"pwrFactor":0.78,"whToday":281.875,"whLastSevenDays":64625.875,"vahToday":2152.547,"varhLeadToday":0.731,"varhLagToday":2055.899}]}],"consumption":[{"type":"eim","activeCount":1,"measurementType":"total-consumption","readingTime":1745051513,"wNow":435.646,"whLifetime":25763107.459,"varhLeadLifetime":18538224.186,"varhLagLifetime":-5987294.444,"vahLifetime":34957874.297,"rmsCurrent":4.798,"rmsVoltage":242.472,"reactPwr":-435.589,"apprntPwr":1163.311,"pwrFactor":0.37,"whToday":2486.459,"whLastSevenDays":14.459,"vahToday":6306.297,"varhLeadToday":5522.186,"varhLagToday":0.0,"lines":[{"wNow":435.646,"whLifetime":25763107.459,"varhLeadLifetime":18538224.186,"varhLagLifetime":-5987294.444,"vahLifetime":34957874.297,"rmsCurrent":4.798,"rmsVoltage":242.472,"reactPwr":-435.589,"apprntPwr":1163.311,"pwrFactor":0.37,"whToday":2486.459,"whLastSevenDays":14.459,"vahToday":6306.297,"varhLeadToday":5522.186,"varhLagToday":0.0}]},{"type":"eim","activeCount":1,"measurementType":"net-consumption","readingTime":1745051513,"wNow":124.946,"whLifetime":18646406.88,"varhLeadLifetime":18540335.917,"varhLagLifetime":6471.455,"vahLifetime":34957874.297,"rmsCurrent":3.135,"rmsVoltage":242.472,"reactPwr":-681.219,"apprntPwr":760.183,"pwrFactor":0.16,"whToday":0,"whLastSevenDays":0,"vahToday":0,"varhLeadToday":0,"varhLagToday":0,"lines":[{"wNow":124.946,"whLifetime":18646406.88,"varhLeadLifetime":18540335.917,"varhLagLifetime":6471.455,"vahLifetime":34957874.297,"rmsCurrent":3.135,"rmsVoltage":242.472,"reactPwr":-681.219,"apprntPwr":760.183,"pwrFactor":0.16,"whToday":0,"whLastSevenDays":0,"vahToday":0,"varhLeadToday":0,"varhLagToday":0}]}],"storage":[{"type":"acb","activeCount":0,"readingTime":0,"wNow":0,"whNow":0,"state":"idle"}]}

The value passed to the meter is whLifetime

Code: Select all

uint64_t mtotal = reading["whLifetime"].asUInt64();

In the json above is
"whLifetime":7117187.875

And yet as soon as I start Domoticz it reports to MQTT:
{
"Battery": 255,
"EnergyMeterMode": "0",
"LastUpdate": "2025-04-19 09:09:43",
"RSSI": 12,
"description": "",
"dtype": "General",
"hwid": "6",
"id": "00000601",
"idx": 50,
"name": "Enphase kWh Production",
"nvalue": 0,
"org_hwid": "6",
"stype": "kWh",
"svalue1": "179.000",
"svalue2": "14216798.000",
"unit": 1
}

What on earth is going on? Where else could bad data be stored?