madAndroid
October 7, 2022, 4:46am
1
I suspect this is some sort of maintenance, but it goes on for a very long time - 90 minutes … the MariaDB addon logs show the following:
[00:00:08] INFO: Lock tables using mariadb client...
[00:00:08] INFO: MariaDB tables locked.
[01:26:03] INFO: MariaDB tables unlocked.
[00:00:02] INFO: Lock tables using mariadb client...
[00:00:02] INFO: MariaDB tables locked.
[01:26:21] INFO: MariaDB tables unlocked.
[00:00:04] INFO: Lock tables using mariadb client...
[00:00:04] INFO: MariaDB tables locked.
[01:26:55] INFO: MariaDB tables unlocked.
[03:00:37] INFO: Lock tables using mariadb client...
[03:00:37] INFO: MariaDB tables locked.
[04:27:19] INFO: MariaDB tables unlocked.
[00:00:04] INFO: Lock tables using mariadb client...
[00:00:04] INFO: MariaDB tables locked.
[01:27:29] INFO: MariaDB tables unlocked.
[00:00:02] INFO: Lock tables using mariadb client...
[00:00:02] INFO: MariaDB tables locked.
[01:27:51] INFO: MariaDB tables unlocked.
[00:00:10] INFO: Lock tables using mariadb client...
[00:00:10] INFO: MariaDB tables locked.
[01:28:15] INFO: MariaDB tables unlocked.
[00:00:33] INFO: Lock tables using mariadb client...
[00:00:33] INFO: MariaDB tables locked.
[01:28:50] INFO: MariaDB tables unlocked.
[00:00:16] INFO: Lock tables using mariadb client...
[00:00:16] INFO: MariaDB tables locked.
[01:29:03] INFO: MariaDB tables unlocked.
[00:00:04] INFO: Lock tables using mariadb client...
[00:00:04] INFO: MariaDB tables locked.
[01:29:03] INFO: MariaDB tables unlocked.
madAndroid
October 7, 2022, 4:48am
2
As a result of this, data isn;t recorded during this period… and I discovered that unless I schedule a restart for just after this period, the data collection stops completely, and doesn’t restart on it’s own
RGN01
December 19, 2022, 6:25am
3
Did you find a reason and fix for this?
I’m having the same issue - and I’m about ready to uninstall MariaDB as a result. The long maintenance (if that is what it is) periods are bad enough but it takes a restart to get it working again - and that is not always possible!
[23:00:00] INFO: Lock tables using mariadb client...
[23:00:00] INFO: MariaDB tables locked.
[01:01:35] INFO: MariaDB tables unlocked.
[23:00:00] INFO: Lock tables using mariadb client...
[23:00:00] INFO: MariaDB tables locked.
[01:04:39] INFO: MariaDB tables unlocked.
[23:00:00] INFO: Lock tables using mariadb client...
[23:00:00] INFO: MariaDB tables locked.
[01:05:58] INFO: MariaDB tables unlocked.
[23:00:00] INFO: Lock tables using mariadb client...
[23:00:00] INFO: MariaDB tables locked.
[01:06:47] INFO: MariaDB tables unlocked.
[23:00:00] INFO: Lock tables using mariadb client...
[23:00:00] INFO: MariaDB tables locked.
[01:08:08] INFO: MariaDB tables unlocked.
[23:00:00] INFO: Lock tables using mariadb client...
[23:00:00] INFO: MariaDB tables locked.
[01:08:19] INFO: MariaDB tables unlocked.
madAndroid
December 23, 2022, 5:23am
4
I’ve not been able to find a solution yet, unfortunately … also, and I’m not sure why (perhaps recent update?) but the maintenance is taking less time recently, looks like approx 45 minutes recently:
[00:00:02] INFO: Lock tables using mariadb client...
[00:00:02] INFO: MariaDB tables locked.
[00:46:30] INFO: MariaDB tables unlocked.
[00:00:01] INFO: Lock tables using mariadb client...
[00:00:01] INFO: MariaDB tables locked.
[00:47:21] INFO: MariaDB tables unlocked.
[00:00:02] INFO: Lock tables using mariadb client...
[00:00:02] INFO: MariaDB tables locked.
[00:48:56] INFO: MariaDB tables unlocked.
Having said that, the time does appear to be growing each iteration.
opened 03:23PM - 09 Aug 22 UTC
### The problem
The ingest of historical data into the database seems to glitch… , or stop recording. This usually happens about 4am ish, every other day or sometimes daily.
Here is a temp graph
Here is an energy graph
This also effects the energy dashboard reporting
Home assistant was rebooted at 6:10 am, and reporting resumed. Last report before the pause was ~4am
This started in the 2022.7 release.
I searched but didnt find a similar issue.
### What version of Home Assistant Core has the issue?
core-2022.8.1
### What was the last working version of Home Assistant Core?
core-2022.6.x
### What type of installation are you running?
Home Assistant Supervised
### Integration causing the issue
effects all things that are recording history
### Link to integration documentation on our website
_No response_
### Diagnostics information
_No response_
### Example YAML snippet
_No response_
### Anything in the logs that might be useful for us?
_No response_
### Additional information
_No response_
opened 10:34AM - 13 Aug 22 UTC
### The problem
August 12, from ~18:04 EEST and August 13, from ~12:00 EEST - n… o new data appears in recorder, MariaDB.
Entities states changing, automation working, but no data in DB (checking by phpMyAdmin, as well see plato on graphs).
MariaDB add-on restart not helping.
August 12 - all become Ok after upgrade 2022.8.2 -> 2022.8.3.
August 13 - working again after upgrade 2022.8.3 -> 2022.8.4.
Probably Core restart can help, will try if happens again.
### What version of Home Assistant Core has the issue?
core-2022.8.2/3
### What was the last working version of Home Assistant Core?
core-2022.8.1
### What type of installation are you running?
Home Assistant OS
### Integration causing the issue
_No response_
### Link to integration documentation on our website
_No response_
### Diagnostics information
## System Information
version | core-2022.8.4
-- | --
installation_type | Home Assistant OS
dev | false
hassio | true
docker | true
user | root
virtualenv | false
python_version | 3.10.5
os_name | Linux
os_version | 5.15.32-v8
arch | aarch64
timezone | Europe/Riga
config_dir | /config
<details><summary>Home Assistant Community Store</summary>
GitHub API | ok
-- | --
GitHub Content | ok
GitHub Web | ok
GitHub API Calls Remaining | 4682
Installed Version | 1.26.2
Stage | running
Available Repositories | 1147
Downloaded Repositories | 49
</details>
<details><summary>Home Assistant Cloud</summary>
logged_in | false
-- | --
can_reach_cert_server | ok
can_reach_cloud_auth | ok
can_reach_cloud | ok
</details>
<details><summary>Home Assistant Supervisor</summary>
host_os | Home Assistant OS 8.4
-- | --
update_channel | stable
supervisor_version | supervisor-2022.08.3
agent_version | 1.2.1
docker_version | 20.10.14
disk_total | 57.8 GB
disk_used | 22.8 GB
healthy | true
supported | true
board | rpi4-64
supervisor_api | ok
version_api | ok
installed_addons | SSH & Web Terminal (12.0.1), File editor (5.3.3), Mosquitto broker (6.1.2), Z-Wave JS to MQTT (0.45.0), Zigbee2MQTT (1.27.0-1), MariaDB (2.5.1), phpMyAdmin (0.8.1), Samba share (10.0.0), DHCP server (1.3.0), Dnsmasq (1.4.4), Samba Backup (5.1.1), RPC Shutdown (2.2)
</details>
<details><summary>Dashboards</summary>
dashboards | 9
-- | --
resources | 31
views | 33
mode | storage
</details>
<details><summary>Recorder</summary>
oldest_recorder_run | 4 August 2022, 08:10
-- | --
current_recorder_run | 13 August 2022, 12:56
estimated_db_size | 3314.75 MiB
database_engine | mysql
database_version | 10.6.8
</details>
<details><summary>Sonoff</summary>
version | 3.1.0 (10c1742)
-- | --
cloud_online | 10 / 15
local_online | 4 / 4
debug | failed to load:
</details>
<details><summary>Xiaomi Gateway 3</summary>
version | 2.1.0 (3d126a5)
-- | --
</details>
### Example YAML snippet
```yaml
recorder:
db_url: mysql://***:***@core-mariadb/hass?charset=utf8
purge_keep_days: 7
commit_interval: 21
```
### Anything in the logs that might be useful for us?
```txt
Unfortunately see nothing helpful while recorder not receiving or not recording new data.
```
### Additional information
_No response_
Do you also see the missing data collection? if so, perhaps add your voice to that issue as well?
Do you have automated backup? This should happen when backing up the home assistant. But 90 minutes are too long, so home assistant instance cannot keep the data on memory until database becomes free again.
Play your recorder settings and start cleaning up some stuff.
Time grows because data grows
Just a thought and I could be way off.
YMMV
A while back I had issues with backups taking WAY too long. Reason for this was the database was growing in size at an alarming rate. I was basically adding too many records to the db and then holding onto them.
Solution was to limit the number of records by using the recorder/purge_keep_days in my configuration.yaml. It is possible to also exclude entities for data you want to retain for longer.
recorder:
purge_keep_days: 30
exclude:
entities:
- sensor.blah.blah
