opower stopped initializing w/ Seattle City Light

[llarian0]

The problem

This had been working for months, but out of nowhere I stated to get the error "Failed setup, will retry. List index out of range" with the Seattle City Light opower integration.

Running the opower module manually seems to work as expected.

Debug logging shows the following error:

Unexpected error fetching Opower data Traceback (most recent call last): File "/usr/src/homeassistant/homeassistant/helpers/update_coordinator.py", line 312, in _async_refresh self.data = await self._async_update_data() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/src/homeassistant/homeassistant/components/opower/coordinator.py", line 89, in _async_update_data await self._insert_statistics() File "/usr/src/homeassistant/homeassistant/components/opower/coordinator.py", line 143, in _insert_statistics consumption_sum = cast(float, stats[consumption_statistic_id][0]["sum"])

IndexError: list index out of range

### What version of Home Assistant Core has the issue?

core-2024.7.4

### What was the last working version of Home Assistant Core?

_No response_

### What type of installation are you running?

Home Assistant OS

### Integration causing the issue

opower

### Link to integration documentation on our website

https://www.home-assistant.io/integrations/opower

### Diagnostics information

[home-assistant_opower_2024-08-05T19-28-07.971Z.log](https://github.com/user-attachments/files/16501942/home-assistant_opower_2024-08-05T19-28-07.971Z.log)

### Example YAML snippet

_No response_

### Anything in the logs that might be useful for us?

_No response_

### Additional information

_No response_

[home-assistant[bot]]

Hey there @tronikos, mind taking a look at this issue as it has been labeled with an integration (opower) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of `opower` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign opower` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

opower documentation opower source _{^{(message by IssueLinks)}}

[DAE51D]

I see SOME stats, but I've never seen the $ ones other than 0... The stats that are showing, SEEM legit, at least the graphs are moving lines that are plausible

[tronikos]

@DAE51D this is unrelated to this issue. Keep in mind that for many utilities opower doesn't provide granular cost so it's 0. Also in the energy dashboard you need to setup the statistics and not these sensors. See the documentation.

@llarian0 this type of issue is hard to debug and fix without access to your account. In the past users temporarily changed their password and privately shared their login information with me. If you want to do the same you can reach me at gmail, community forum, or discord. My username is tronikos in all of them.

[tronikos]

@llarian0 can you install the SQLite Web and copy the results of the following queries here:

SELECT datetime(s.start_ts, 'unixepoch'), s.start_ts, s.state, s.sum
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.source = "opower" and sm.statistic_id LIKE "%_consumption"
ORDER BY s.start_ts DESC
LIMIT 10;

and

SELECT datetime(s.start_ts, 'unixepoch'), s.start_ts, s.state, s.sum
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.source = "opower" and sm.statistic_id LIKE "%_cost"
ORDER BY s.start_ts DESC
LIMIT 10;

[llarian0]

My statistics backend is MariaDB, but I think this is the output you want:

MariaDB [hass]> SELECT from_unixtime(s.start_ts), s.start_ts, s.state, s.sum FROM statistics s JOIN statistics_meta sm ON s.metadata_id = sm.id WHERE sm.source = "opower" and sm.statistic_id LIKE "%_consumption" ORDER BY s.start_ts DESC LIMIT 10;
+----------------------------+------------+-------+--------------------+
| from_unixtime(s.start_ts)  | start_ts   | state | sum                |
+----------------------------+------------+-------+--------------------+
| 2024-08-20 00:00:00.000000 | 1724137200 |  0.02 | 2044.0029999998471 |
| 2024-08-19 23:00:00.000000 | 1724133600 |     0 | 2043.9829999998472 |
| 2024-08-19 22:00:00.000000 | 1724130000 |     0 | 2043.9829999998472 |
| 2024-08-19 21:00:00.000000 | 1724126400 |     0 | 2043.9829999998472 |
| 2024-08-19 20:00:00.000000 | 1724122800 |  0.04 | 2043.9829999998472 |
| 2024-08-19 19:00:00.000000 | 1724119200 |     0 | 2043.9429999998472 |
| 2024-08-19 18:00:00.000000 | 1724115600 |     0 | 2043.9429999998472 |
| 2024-08-19 17:00:00.000000 | 1724112000 |     0 | 2043.9429999998472 |
| 2024-08-19 16:00:00.000000 | 1724108400 |     0 | 2043.9429999998472 |
| 2024-08-19 15:00:00.000000 | 1724104800 |     0 | 2043.9429999998472 |
+----------------------------+------------+-------+--------------------+
10 rows in set (0.038 sec)

MariaDB [hass]> SELECT from_unixtime(s.start_ts), s.start_ts, s.state, s.sum
    -> FROM statistics s
    -> JOIN statistics_meta sm ON s.metadata_id = sm.id
    -> WHERE sm.source = "opower" and sm.statistic_id LIKE "%_cost"
    -> ORDER BY s.start_ts DESC
    -> LIMIT 10;
+----------------------------+------------+-------+--------+
| from_unixtime(s.start_ts)  | start_ts   | state | sum    |
+----------------------------+------------+-------+--------+
| 2024-08-20 00:00:00.000000 | 1724137200 |     0 | 141.16 |
| 2024-08-19 23:00:00.000000 | 1724133600 |     0 | 141.16 |
| 2024-08-19 22:00:00.000000 | 1724130000 |     0 | 141.16 |
| 2024-08-19 21:00:00.000000 | 1724126400 |     0 | 141.16 |
| 2024-08-19 20:00:00.000000 | 1724122800 |     0 | 141.16 |
| 2024-08-19 19:00:00.000000 | 1724119200 |     0 | 141.16 |
| 2024-08-19 18:00:00.000000 | 1724115600 |     0 | 141.16 |
| 2024-08-19 17:00:00.000000 | 1724112000 |     0 | 141.16 |
| 2024-08-19 16:00:00.000000 | 1724108400 |     0 | 141.16 |
| 2024-08-19 15:00:00.000000 | 1724104800 |     0 | 141.16 |
+----------------------------+------------+-------+--------+
10 rows in set (0.039 sec)

[tronikos]

I tried with the login information you privately shared with me and it's working fine on my end with the default SQLite. From your output your database seems to be in a good state. Do you still get the error in your first post?

[llarian0]

I do yes.

Identical error.

Oddly, this was defintitely working until recently w/ the MariaDB backend. I'm not sure what would have changed, but clearly it has something to do with my install.

[tronikos]

Can you run the following:

SELECT s.start_ts
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.source = "opower" and sm.statistic_id LIKE "%_consumption"
EXCEPT
SELECT s.start_ts
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.source = "opower" and sm.statistic_id LIKE "%_cost"

and

SELECT s.start_ts
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.source = "opower" and sm.statistic_id LIKE "%_cost"
EXCEPT
SELECT s.start_ts
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.source = "opower" and sm.statistic_id LIKE "%_consumption"

[llarian0]

MariaDB [hass]> SELECT s.start_ts
    -> FROM statistics s
    -> JOIN statistics_meta sm ON s.metadata_id = sm.id
    -> WHERE sm.source = "opower" and sm.statistic_id LIKE "%_consumption"
    -> EXCEPT
    -> SELECT s.start_ts
    -> FROM statistics s
    -> JOIN statistics_meta sm ON s.metadata_id = sm.id
    -> WHERE sm.source = "opower" and sm.statistic_id LIKE "%_cost"
    -> ;
Empty set (0.007 sec)

MariaDB [hass]> SELECT s.start_ts
    -> FROM statistics s
    -> JOIN statistics_meta sm ON s.metadata_id = sm.id
    -> WHERE sm.source = "opower" and sm.statistic_id LIKE "%_cost"
    -> EXCEPT
    -> SELECT s.start_ts
    -> FROM statistics s
    -> JOIN statistics_meta sm ON s.metadata_id = sm.id
    -> WHERE sm.source = "opower" and sm.statistic_id LIKE "%_consumption";
+------------+
| start_ts   |
+------------+
| 1699430400 |
| 1699434000 |
| 1699437600 |
| 1699441200 |
| 1699444800 |
| 1699448400 |
| 1699452000 |
| 1699455600 |
| 1699459200 |
| 1699462800 |
| 1699466400 |
| 1699470000 |
| 1699473600 |
| 1699477200 |
| 1699480800 |
| 1699484400 |
| 1699488000 |
| 1699491600 |
| 1699495200 |
| 1699498800 |
| 1699502400 |
| 1699506000 |
| 1699509600 |
| 1699513200 |
+------------+
24 rows in set (0.021 sec)