Sensor cards not showing graph anymore

Configuration
1

Hi,

I’m on 0.91.2 and I only recently noticed that none of my sensor cards display correct graphs anymore.
They all show flat lines while the numerical value that is displayed seems to be correct.
Here’s an example:

Screenshot

The corresponding definition looks like this:

cards:
  - detail: 2
    entity: sensor.fibaro_fgms_livingroom_temperature
    graph: line
    hours_to_show: 24
    id: be5b2df01bf84fe398f1231b0aac2d73
    name: Temperatur
    type: sensor
  - detail: 1
    entity: sensor.fibaro_fgms_livingroom_luminance
    graph: line
    hours_to_show: 24
    icon: 'mdi:brightness-7'
    id: 00ae98c094ac49dc8b9ba932f4fc0789
    name: Helligkeit
    type: sensor
type: horizontal-stack

I’ve seen that detail: now somewhat replaces the former accurancy: property and I just updated the definition to reflect that.
Still, all the graphs are drawn as flat lines.

Sebastian

2

It’s highly probable that your database is corrupt. have a read of this thread:

3

Thanks for your reply!
I’m logging to a mysql/mariadb instance. Haven’t seen any errors, but then again, I’m usually running HA on ‘fatal’ log level, so most issues won’t show up (there’s also nothing in the db log).

Anyway, I just restarted my HA docker container and that seems to have revived the graphing.
I’m starting to see the current values, great :slight_smile:
The older ones seem to be lost, though, but that’s fine as I do my “serious graphing” with Grafana/InfluxDB and that still worked fine.

So it seems there was some kind of issue with the mysql db connection.
Sorry for the false alarm… :wink:

Sebastian

4

Something still seems strange here.
Graphs start to flat out after a while:

Screenshot%202019-04-12
Screenshot%202019-04-12720×808 33.7 KB

Looking at mysql, I still see active connections from HA:

MariaDB [(none)]> show processlist;
+------+---------+--------------------+---------------+---------+------+-------+------------------+----------+
| Id   | User    | Host               | db            | Command | Time | State | Info             | Progress |
+------+---------+--------------------+---------------+---------+------+-------+------------------+----------+
| 7759 | hass    | 192.168.10.3:60180 | homeassistant | Sleep   |    2 |       | NULL             |    0.000 |
| 7760 | hass    | 192.168.10.3:60182 | homeassistant | Sleep   |    2 |       | NULL             |    0.000 |
| 7761 | hass    | 192.168.10.3:60186 | homeassistant | Sleep   |    2 |       | NULL             |    0.000 |
| 7763 | hass    | 192.168.10.3:37162 | homeassistant | Sleep   |    2 |       | NULL             |    0.000 |
[...]
+------+---------+--------------------+---------------+---------+------+-------+------------------+----------+

Clicking on a sensor graph or opening another tab in the UI resets the connections’ timers as one would expect.

Sebastian

5

I turned on logging for the recorder component and get this in home-assistant.log:

2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')
2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

(I left out the SQL statements that were appended to those log lines.)
So apparently HA for some reason lost the connection to MySQL (the server is running the whole time) and then is unable to reconnect because of some transaction that needs to be rolled back first?
How would it roll back a transaction without reconnecting to the server first - or is the message suggesting that this has to happen internally to HA?

I’m running HA with MySQL in an unchanged environment since the dawn of time and only now notice those issues.
So it looks to me like this has been introduced only recently.

Sebastian

6

For the records:
Apparently the problem is connected to another error I was receiving:
ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: 'latin-1' codec can't encode character '\u2605' in position 14: ordinal not in range(256)

Appending “?charset=utf8” to my db_url string seems to have fixed both issues for me.

Sebastian

7

Thanks this seems to have fixed the issue for me also.