Negative Values in Energy Dashboard since last update

[Bl4DEx (Sebastian Hollas)]

I updated the script to version 2.1.0:

Add support for MySQL server connections. (I don’t have a MySQL server to test this feature but I am re-using most the of the code provided by @fversteegen. Can someone confirm this feature?)
The script automatically handles the MySQL server connection if following is provided:

DB_SERVER = {
    "DB_HOST": "",
    "DB_USER": "",
    "DB_PASSWORD": "",
    "DB_NAME": ""
}

The script defaults to sqlite3 otherwise

Add support for entities that are no Riemann Sum entities. (I don’t have any other broken entities so I also cannot test this feature. @erik7 can you test the script and provide feedback?)
The option * --list* behaves differently now. Previously, entities with the attribute round were added by default. Now, this option lists all entities which have the “sum” key populated in the table statistics

@fversteegen: About your finding with not corrected entities:
Can you give some examples so I can try to find out what is wrong?

[fversteegen (Frank)]

Running backup now. Will test in the morning. Already thumbs up for your effort!

[fversteegen (Frank)]

Here are my findings:

the --list argument does create a list of Riemann sum entities. That list however is incomplete. I had a look at it and it only generates the list of Riemann sum entities which have been created through the GUI (ie: Settings → Devices & Services → Helpers) and ignores Riemann sum entities which have been created in yaml. Find below an example of an ignored Riemann sum entitiy:

sensor:
  - platform: integration
    source: sensor.droger_power
    unique_id: f45f73d7-7729-4f77-9fd1-59de9cc3009d
    name: integral_dryer
    unit_prefix: k
    round: 2
    method: left

Question: you indicated that Homeassistant should be stopped before running the script. As I run Python from within the Homeassistant container I call the service “Recorder: disable” before running the script. Is that sufficient?

After running the script (output looked like what I would expect from the script!) it appears as if changes are not done. See below screenshot

[Bl4DEx (Sebastian Hollas)]

Hi @fversteegen
thanks for you findings!

1. –list not listing Rieman Sum Entites that are added with configuration.yaml

Starting from version 2.1.0, this option is no longer restricted to Riemann Sum entities, but rather looks into statistics_meta table and writes all entities in entities.list which have the key “sum” populated in statistics. In theory, all of these entities can get fixed if there are unwanted negative values

However, this should also list the Riemann Sum entities which are created through the configuration.yaml. I did find a bug why this happened on your side and fixed it with 2.1.1. You should see all entitites which can be fixed with this script (not only Riemann Sum entities)

2. Changes are not stored with MySQL server connection
I am completely honest, I have no idea why the changes are not stored into the database. We call the commit() and close() which should do the same as your code. Your code is using the context manager which also calls these function at the end.
I added

autocommit=True

into the connector initialization. This way all changes made by execute() are directly committed to the database. Can you retest with version 2.1.1 and provide feedback again?

3. Rounding floating point number
I think this adds too much overhead and I removed this part. Now, all values are handled as they were originally stored in the database. I am using the built-in Decimal module to have a more accurate floatin point calculation. I have not seen any issues on my side using this approach compared to the error prone round method

4. Fixing last valid state in .storage/core.restore_state
Previously, I only tried to fix this file if the entitiy had the round attribute. But we longer have this information available and therefore, I try to fix the last valid state for any entity. The script updates the last valid state attribute if it exists.

[fversteegen (Frank)]

Just tested it and changes are now written into the database Do note: below is written chronologically.

The script does end with the following error (but I guess this is minor)

Traceback (most recent call last):
  File "/config/HA_FixNegativeStatistics.py", line 316, in <module>
    main()
  File "/config/HA_FixNegativeStatistics.py", line 97, in main
    fixDatabase(ENTITIES=ENTITIES)
  File "/config/HA_FixNegativeStatistics.py", line 163, in fixDatabase
    print(f"\n{db.total_changes} changes made to database!")
               ^^^^^^^^^^^^^^^^
AttributeError: 'Connection' object has no attribute 'total_changes'

My statistics for May are now good, but for June I some sensors now show negative values (not for all sensors, just for a few so some have been corrected. Note for self: smarthome energ and licht keuken have been corrected, but sensor.integral_dishwasher and sensor.integral_dryer are now showing negative values). I re-ran the script only with those entities (find output below) and apparently only a few values needed to be corrected.

========================================================================
sensor.licht_schuur_poort_energy | metadata_id_states = 383 | metadata_id_statistics = 249
  Fixing table statistics for key: sum
    Updating idx = 4019851: -6.355599999999453 -> 5.558400000000547
  Fixing table statistics for key: state
    Updating idx = 4019851: 0.6051 -> 12.5191
  Deleting short term statistics
  Fixing table states
    Updating state_id = 109565888: 0.4490 -> 0.6051

========================================================================
sensor.sensor_kamer_laurens_energy_nieuw | metadata_id_states = 295 | metadata_id_statistics = 731
  Fixing table statistics for key: sum
    Updating idx = 4020049: 4.68 -> 25.26
  Fixing table statistics for key: state
    Updating idx = 4020049: 4.68 -> 25.26
  Deleting short term statistics
  Fixing table states
    Updating state_id = 109565488: 1.99 -> 4.68
    Updating state_id = 109566599: 1.99 -> 4.68

========================================================================
sensor.sensor_magnetron_energy_nieuw | metadata_id_states = 294 | metadata_id_statistics = 735
  Fixing table statistics for key: sum
    Updating idx = 4020053: 10.840000000000003 -> 71.64
  Fixing table statistics for key: state
    Updating idx = 4020053: 10.84 -> 71.64
  Deleting short term statistics
  Fixing table states

========================================================================
sensor.integral_cvketel | metadata_id_states = 148 | metadata_id_statistics = 490
  Fixing table statistics for key: sum
    Updating idx = 4019915: 3.9399999999999977 -> 92.48000022888183
  Fixing table statistics for key: state
    Updating idx = 4019915: 3.94 -> 92.48000022888183
  Deleting short term statistics
  Fixing table states

========================================================================
sensor.integral_oven | metadata_id_states = 137 | metadata_id_statistics = 140
  Fixing table statistics for key: sum
    Updating idx = 4019825: 15.539995117187516 -> 341.7599951171875
  Fixing table statistics for key: state
    Updating idx = 4019825: 18.17 -> 344.39
  Deleting short term statistics
  Fixing table states
    Updating state_id = 109565339: 9.78 -> 18.17
    Updating state_id = 109566513: 9.78 -> 18.17

========================================================================
sensor.integral_wasmachine | metadata_id_states = 142 | metadata_id_statistics = 137
  Fixing table statistics for key: sum
    Updating idx = 4019824: -117.75998291016066 -> 488.6500170898394
  Fixing table statistics for key: state
    Updating idx = 4019824: 36.16 -> 642.57
  Deleting short term statistics
  Fixing table states
    Updating state_id = 109565344: 21.02 -> 36.16
    Updating state_id = 109566514: 21.02 -> 36.16

========================================================================
sensor.integral_dryer | metadata_id_states = 141 | metadata_id_statistics = 136
  Fixing table statistics for key: sum
    Updating idx = 4019823: -138.1299792480462 -> 560.9800354003914
  Fixing table statistics for key: state
    Updating idx = 4019823: 30.13 -> 729.2400146484376
  Deleting short term statistics
  Fixing table states
    Updating state_id = 109565343: 17.67 -> 30.13
    Updating state_id = 109566493: 17.67 -> 30.13

========================================================================
sensor.integral_dishwasher | metadata_id_states = 146 | metadata_id_statistics = 134
  Fixing table statistics for key: sum
    Updating idx = 4019822: -244.83000244140635 -> 721.8299975585936
  Fixing table statistics for key: state
    Updating idx = 4019822: 40.86 -> 1007.52
  Deleting short term statistics
  Fixing table states
    Updating state_id = 109565348: 23.69 -> 40.86
    Updating state_id = 109566538: 23.69 -> 40.86
Traceback (most recent call last):
  File "/config/HA_FixNegativeStatistics.py", line 316, in <module>
    main()
  File "/config/HA_FixNegativeStatistics.py", line 97, in main
    fixDatabase(ENTITIES=ENTITIES)
  File "/config/HA_FixNegativeStatistics.py", line 163, in fixDatabase
    print(f"\n{db.total_changes} changes made to database!")

After half an hour of running homeassistant some sensors still go negative today. All sensors that are negative (only slightly negative) are Powercalc sensors. I will dig deeper and will file an issue on the Github of @bramski

[fversteegen (Frank)]

EDIT: I think that restoring the last known value does not work properly. All sensors go very negative after about an hour. See below screenshots. These are also normal integral sensors and not just the powercalc ones…

[bramski (Bram Gerritsen)]

For powercalc you should also have a look at config/.storage/powercalc_groups, that’s a powercalc specific file which is also used to known the previous state values, similar as config/.storage/core.restore_state.

[marcelhoogantink]

Hi @Bl4DEx and @fversteegen ,

I used/tested the version 2.1.1 on a HA-installation with MySQL.

I found some issues (and fixes):

1. I have several records in ‘statistics’-table with column ‘sum’ being ‘NULL’. So I changed the SqlExec line in recalculateStatistics() into (adding a NULL-check for ‘{key}’):

SqlExec(f"SELECT id,{key} FROM statistics WHERE metadata_id=? AND {key} IS NOT NULL ORDER BY created_ts", (metadata_id,))

2. I have several records in the ‘states’-table with column ‘state’ being ‘unknown’ or ‘unavailable’.
   So I changed the SqlExec line in recalculateStates() into: (adding exclusion of ‘unknown’ an ‘unavailable’ for ‘state’)

SqlExec("SELECT state_id,state,old_state_id,attributes_id FROM states WHERE metadata_id=? AND state NOT IN ('unknown','unavailable') ORDER BY state_id",(metadata_id,))

3. MySQL has no db.total_changes so I commented this line out:

print(f"\n{db.total_changes} changes made to database!")

With these changes, I managed to repair my database without errors.
No negative values any more and no unwanted side-effects found (yet…)

Thanks for all your work !!!

[edit]

Now after a little while, only the values of today (that were OK) become negative, so there is something wrong yet …

[edit 2]
And after a rerun, values of today are fixed again, now waiting for a next update …

[edit 3]
Also the hourly-energy values of today on the energy-dashboard are now corrupted, so i will wait until 20:00 hours to see the (change of) values of the individual devices, and than I will restore a backup …

[edit 4]
I can confirm the remark of @fversteegen that the restore of the last known values seems buggy. I also see strange values there …

[edit 5}
Now, just after 20:00 hours:
The graphs of the individual devices is negative again.
The hourly graph of my solar-panels keeps wrong and also added new wrong values for this last hour (19:00-20:00 hours)…
Restoring backup- now.

[marcelhoogantink]

Hi all,

In my previous post, I forgot to mention that there was another error while I tested this fix:

On running the function fixLastValidState(), I found that

state["extra_data"].get("native_value", dict())

not always is a dictionary, so the last .get() in

if state["extra_data"].get("native_value", dict()).get("decimal_str", ""):

won’t work then, so I changed this part into:

            native_value=state["extra_data"].get("native_value", dict())
            if (type(native_value) is dict and native_value.get("decimal_str", "")):

Sorry for forgetting this.

[marcelhoogantink]

@Bl4DEx Just wondering :

Why are the short-term statistics deleted, and only replaced by the last value?
This messes up the history graphs with the 5 minutes settings!

[Bl4DEx (Sebastian Hollas)]

Hi folks,
I updated the script to version 2.1.2

@fversteegen

• I removed the autocommit feature with the latest version. As you and @marcelhoogantink pointed out, there is no total_changes object if PyMySQL module is used. I removed this line completely and improved the overall output of the script. I don’t want to commit changes to the database before all modifications are done to ensure data consistency
• Can you or @bramski provide some kind of example for powercalc.groups file? I could try to implement the same logic as for core.restare_state to support powercalc entities

@marcelhoogantink
Thanks for all the findings!

• NULL for table statistics: I added a check for NULL and instead of ignoring the value, I update NULL to the current value
• The additional check in the table states should not be necessary. The script checks if the value is an integer or float with

  if state is None or not state.replace(".", "", 1).isdigit():
  

  This includes value like unavilable and unknown. Can you confirm that this works on your side?
• I updated the logic in fixLastValidState based on your input
• Why do I delete statistics_short_term and replace with one single entry? Please refer toREADME-md. If you don’t want that to happend, you can just comment out the SqlExc in the function

Please keep in mind that all of my changes in the script cannot be verified on my side because I cannot reproduce any of the mentioned issue. I am doing the changes based on your input.

[fversteegen (Frank)]

Find below the full content of my config/.storage/powercalc_group:

{
  "version": 2,
  "minor_version": 1,
  "key": "powercalc_group",
  "data": {
    "sensor.licht_eettafel_group_energy": {
      "sensor.eettafel_lamp_1_energy": {
        "entity_id": "sensor.eettafel_lamp_1_energy",
        "state": "0.3872",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.eettafel_lamp_1",
          "source_domain": "light",
          "source": "sensor.eettafel_lamp_1_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "Eettafel lamp 1 energy"
        },
        "last_changed": "2023-06-28T08:58:20.023953+00:00",
        "last_updated": "2023-06-28T08:58:20.023953+00:00",
        "context": {
          "id": "01H40KK6ZQETD736DDX28Q2D3P",
          "parent_id": null,
          "user_id": null
        }
      },
      "sensor.eettafel_lamp_3_energy": {
        "entity_id": "sensor.eettafel_lamp_3_energy",
        "state": "0.2932",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.eettafel_lamp_3",
          "source_domain": "light",
          "source": "sensor.eettafel_lamp_3_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "Eettafel lamp 3 energy"
        },
        "last_changed": "2023-06-28T08:58:20.024657+00:00",
        "last_updated": "2023-06-28T08:58:20.024657+00:00",
        "context": {
          "id": "01H40KK6ZRXRZF145WX08G5S73",
          "parent_id": null,
          "user_id": null
        }
      },
      "sensor.eettafel_lamp_2_energy": {
        "entity_id": "sensor.eettafel_lamp_2_energy",
        "state": "0.3377",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.eettafel_lamp_2",
          "source_domain": "light",
          "source": "sensor.eettafel_lamp_2_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "Eettafel lamp 2 energy"
        },
        "last_changed": "2023-06-28T08:58:20.024427+00:00",
        "last_updated": "2023-06-28T08:58:20.024427+00:00",
        "context": {
          "id": "01H40KK6ZRYSGRVHP3EKF6K8HE",
          "parent_id": null,
          "user_id": null
        }
      }
    },
    "sensor.licht_tv_group_energy": {
      "sensor.tv_spot_1_energy": {
        "entity_id": "sensor.tv_spot_1_energy",
        "state": "0.1972",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.tv_spot_1",
          "source_domain": "light",
          "source": "sensor.tv_spot_1_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "TV spot 1 energy"
        },
        "last_changed": "2023-06-28T08:58:20.067406+00:00",
        "last_updated": "2023-06-28T08:58:20.067406+00:00",
        "context": {
          "id": "01H40KK71316DGZ5RX4BXZ09Z3",
          "parent_id": null,
          "user_id": null
        }
      },
      "sensor.tv_spot_2_energy": {
        "entity_id": "sensor.tv_spot_2_energy",
        "state": "0.1972",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.tv_spot_2",
          "source_domain": "light",
          "source": "sensor.tv_spot_2_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "TV spot 2 energy"
        },
        "last_changed": "2023-06-28T08:58:20.067839+00:00",
        "last_updated": "2023-06-28T08:58:20.067839+00:00",
        "context": {
          "id": "01H40KK713A6GV4RBTQN4D0MA2",
          "parent_id": null,
          "user_id": null
        }
      },
      "sensor.tv_spot_3_energy": {
        "entity_id": "sensor.tv_spot_3_energy",
        "state": "0.1972",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.tv_spot_3",
          "source_domain": "light",
          "source": "sensor.tv_spot_3_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "TV spot 3 energy"
        },
        "last_changed": "2023-06-28T08:48:20.079445+00:00",
        "last_updated": "2023-06-28T08:48:20.079445+00:00",
        "context": {
          "id": "01H40K0X3F93F1M1AR26Q5EYEF",
          "parent_id": null,
          "user_id": null
        }
      }
    },
    "sensor.licht_schuur_poort_energy": {
      "sensor.schuur_poort_links_energy": {
        "entity_id": "sensor.schuur_poort_links_energy",
        "state": "0.1671",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.schuur_poort_links",
          "source_domain": "light",
          "source": "sensor.schuur_poort_links_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "Schuur poort links energy"
        },
        "last_changed": "2023-06-28T08:28:20.214667+00:00",
        "last_updated": "2023-06-28T08:28:20.214667+00:00",
        "context": {
          "id": "01H40HW9BPXAAZ8MWARFWJZJAD",
          "parent_id": null,
          "user_id": null
        }
      },
      "sensor.schuur_poort_rechts_energy": {
        "entity_id": "sensor.schuur_poort_rechts_energy",
        "state": "0.3166",
        "attributes": {
          "state_class": "total_increasing",
          "source_entity": "light.schuur_poort_rechts",
          "source_domain": "light",
          "source": "sensor.schuur_poort_rechts_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "icon": "mdi:lightning-bolt",
          "friendly_name": "Schuur poort rechts energy"
        },
        "last_changed": "2023-06-28T08:08:20.219926+00:00",
        "last_updated": "2023-06-28T08:08:20.219926+00:00",
        "context": {
          "id": "01H40GQNFVE159NY1TWQH8A57Q",
          "parent_id": null,
          "user_id": null
        }
      }
    }
  }
}

[marcelhoogantink]

@Bl4DEx

Tested with 2.1.2:

I get the message

 [ERROR]: Cannot fix this entity because first entry in table 'states' is not a number! first entry: 2270
No changes were committed into the database!

The result of the query:

These are the states of my solar-panels, they sometimes are offline for a little period, then unavailable occurs. But within a second this is corrected.

So when the first item is unavailable the script breaks.

[bramski (Bram Gerritsen)]

Thanks for your great work on making a script to fix the HA quirks.

To elaborate on the powercalc groups file. the exact location is config/.storage/powercalc_group.
You can see an example as @fversteegen has shared.

Essentially the data is structures as follows:

{
  "data": {
    "sensor.group_1": {
      "sensor.energy_sensor1": {
         #sensor state
      },
      "sensor.energy_sensor2": {
         #sensor state
      }
    },
    "sensor.group_2": {
      "sensor.energy_sensor1": {
         #sensor state
      },
      "sensor.energy_sensor3": {
         #sensor state
      }
    }
  }
}

Where the first key is the powercalc group entity_id,
And inside that a list of all the individual energy sensors (and the states) making up the group.
So when an energy sensor is corrected it should also be corrected here, because powercalc uses this information to know how much to add to the energy group. By looking at previous and current values of the individual energy sensors.

Hope I explained it correctly.

[Bl4DEx (Sebastian Hollas)]

version 2.2.0 is out

Hi @bramski and @fversteegen

thanks for providing the template files. I added support for powercalc_groups assuming the following:

1. One entity might be part of multiple groups
2. One entity might be listed multiple times in the same group (even if it doesn’t make a lot of sense to me but still, this might be someones setup I guess)

Can you provide feedback if it is working for you?
I made fixing both files core.restore_state and powercalc_group dynamic. This means that the script looks for the entity in the files and fixes the values if the files exist

@marcelhoogantink
The script did not break but rather this was a safety measurement to prevent an invalid starting point for the calculation. Anyway, now the script searches for the first valid value (in both tables states and statistics) and uses this value as a starting point. All entries before the valid value one are ignored.

[marcelhoogantink]

Hi @Bl4DEx ,

Thanks for the new update 2.2.0.

I ran the update om my test-HA, and it went all along without any issues

I found some unexpected results in the repaired data: It looks like that at the moment the correction starts (beginning of may), the corrected value is calculated and saved, but after that this corrected values does not increase.

For this I focused on one table, and I created some graphs, to clarify.
(It looks like the variable pre_value is updated/defined at the wrong moment(??)

Here the graphs before repair:

Here the graphs after repair:

You can see that the long-term graph is corrected from the beginning of may (start of the bug), but is not increasing.
This not-increased-value is then used in both short term as the normal state tables.

[marcelhoogantink]

Here another thing to consider for anyone who following this Topic:

I have Envoy/Enphase solar panels which has some statistics tables with sum:

'sensor.envoy_xxxxx_today_s_energy_production'
'sensor.envoy_xxxxx_last_seven_days_energy_production'
'sensor.envoy_xxxxx_lifetime_energy_production'

The first two, the today_s and the last_seven_days are resetting or can be decreasing.
So these tables will be wrong after correction and should be deleted from the ‘entities_list’.
And you may also consider to remove the third one: lifetime when you do not have issues with this table.

These tables are not Riemann depended!

[fversteegen (Frank)]

Just ran the script. I had negative values in both May and June. After running the script May looks fine (at least from a distance), but for June some values are now gone. What could be the issue there? Do note that, for example, that “Koelvriezer energy” had a positive value in June, but a negative value in May.

June results BEFORE running script 2.2.0

June results after script 2.2.0

[Bl4DEx (Sebastian Hollas)]

Hi @marcelhoogantink

if you created the file entities.list using --list, then this is exactly what the script printed out:

print(f"File '{ENTITIES_FILE}' created with entities that have the key 'sum'"
      f"\nPlease adjust to your needs and rerun the script with no arguments.")

You should only try to fix entities, which are clearly wrong in the UI. This option --list only gives an overview which entities might be fixable by this script. This does not mean that you should leave it like it is

@fversteegen
If I understood you correct, all the values seem so to be correct except from the entity “Koelvriezer energy”?
Looking from the outside without any database before/after it is hard to give any opinion on why this happened.
All I can say is that with my database everything worked like a charm because this is the only input I have when I developed the script…

At least I hope that the script at least is helping a little.

This might be the last update I did to the script unless there is a breaking bug. But I do not plan to investigate further

[marcelhoogantink]

@Bl4DEx
Did you read my reply #76?
That gives some stange results on some Riemann entities.