Lots of NULLs in MariaDB tables

Configuration
1

I was looking for ways to convert string states to numbers in grafana so I can graph them along with numbers to see when my A/C turns on and such. I found some good queries to use in my MariaDB database as it appears it can’t be done with the InfluxDB. I have MariaDB set up under my recorder config:

recorder:
  db_url: mysql+pymysql://user:[email protected]/home_assistant?charset=utf8

and influxdb:

influxdb:
  host: 192.168.x.x
  port: 8086
  username: !secret influxdb_user
  password: !secret influxdb_password
  ssl: true
  verify_ssl: false

I get NULLs in a lot of fields.

+----------+--------+------------------+-------------+------------+----------+----------------------------+----------------------------+---------+----------------------------+-----------------+--------------+---------------+------------+-------------------+
| state_id | domain | entity_id        | state       | attributes | event_id | last_changed               | last_updated               | created | context_id                 | context_user_id | old_state_id | attributes_id | origin_idx | context_parent_id |
+----------+--------+------------------+-------------+------------+----------+----------------------------+----------------------------+---------+----------------------------+-----------------+--------------+---------------+------------+-------------------+
| 51662335 | NULL   | climate.upstairs | heat_cool   | NULL       |     NULL | 2022-08-31 19:03:01.662043 | 2022-08-31 19:06:03.335277 | NULL    | 01GBTMQMP773RN9GSBPMBK60NE | NULL            |         NULL |        901639 |          0 | NULL              |
| 51662943 | NULL   | climate.upstairs | heat_cool   | NULL       |     NULL | NULL                       | 2022-08-31 21:03:27.962456 | NULL    | 01GBTVEM6TB47THKG261R08214 | NULL            |         NULL |       1249597 |          0 | NULL              |
| 51665619 | NULL   | climate.upstairs | heat_cool   | NULL       |     NULL | 2022-08-31 21:03:27.962456 | 2022-08-31 21:06:28.618606 | NULL    | 01GBTVM4MAZBARCQ6CF77Z1Q0D | NULL            |     51662943 |        765172 |          0 | NULL              |

I can’t pull the attribute information or even get the ‘created’ time. Is this a failure in a schema update or something? Is it because InfluxDB is configured? Can I fix this?

2

States are always strings in Home Assistant.

If you want the state to be treated as a number it must be given a unit_of_measurement.

The NULLs are normal. There is no need to mess with them.

3

I know they’re strings, that’s not the problem. The problem is the only thing I see in my table is the current state and none of the attributes. If the nulls are normal, why do people have JSON in their attributes column and use the created column for the X (time) axis? I’m trying to pull in the hvac_action attribute from my ecobee as well as some others and can’t find them anywhere in the MariaDB.

One of the examples I found:

4

You need to create template sensors for each attribute you want to monitor. Influx/Grafana only imports entity states.

5

huh? I can absolutely get attribute data from InfluxDB:

image
image955×297 7.95 KB

image
Though creating a template sensor may be a solution if I can replace the text with numerical values. I’ll take a look at that option. I guess I could always just write a Node-Red flow to write a new sensor to MQTT if that doesn’t work. Ugly, but it would get the job done.

6

Well colour me surprised. I did not know that. Sorry.

7

That post is you linked is quite old. The states table underwent a significant re-organization to improve performance this year. In particular attributes were bumped out into their own table in this release:

That way HA does not write duplicate data again and again in each row. Which is great for performance but does mean many posts with custom SQL that haven’t been updated this year don’t work anymore.

I would recommend taking a look at this: