Recorder stops working after a few days

Configuration
1

Hi,

the recorder stops working after a few days. I’m using MariaDB 10 on my synology diskstation. It works nearly a year without problem.
If I manually wipe the DB on my NAS it starts working again for a few days (maybe the purge_keep_days: 7 I configured, but I’m not sure)

This is my config

recorder:
  purge_keep_days: 7
  db_url: mysql://hass:[email protected]:3307/hass

I see this in the log. Please help.

2018-08-25 10:55:29 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.OperationalError) (1206, 'The total number of locks exceeds the lock table size') [SQL: 'DELETE FROM events WHERE events.time_fired < %s AND events.event_id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2018, 8, 18, 8, 47, 43, 724233, tzinfo=<UTC>), 641587, 641588, 641589, 641590, 641591, 641594, 641595, 641596, 641597, 641598, 641604, 641607, 641608, 641609, 641610, 641611, 641612, 641613, 641614, 912568, 1258579, 1261530, 1375252, 1375253, 1375254, 1375255, 1375300, 1375302, 1375303, 1375304, 1375314, 1375341, 1375348, 1375350, 1375351, 1375358, 1375359, 1375373, 1375377, 1375382, 1375385, 1375389, 1375390, 1375400, 1375404, 1375411, 1375415, 1375416, 1375417, 1375418, 1375420, 1375422, 1375423, 1375424, 1375425, 1375426, 1375427, 1375428, 1375442, 1375480, 1375481, 1375495, 1375496, 1375498, 1375502, 1375514, 1375515, 1375516, 1375517, 1375518, 1375519, 1375520, 1375521, 1375522, 1375523, 1375524, 1375525, 1375526, 1375528, 1375529, 1375531, 1375532, 1375533, 1375535, 1375551, 1375552, 1375575, 1375576, 1375577, 1375578, 1375579, 1375583, 1375584, 1375590, 1375591, 1375595, 1375596, 1375597, 1375601, 1375604, 1375606, 1375607, 1375620, 1375625, 1375635, 1375639, 1375640, 1375641, 1375642, 1375643, 1375649, 1375658, 1375663, 1375664, 1375665, 1375666, 1375667, 1375669, 1375670, 1375671, 1375672, 1375673, 1375675, 1375676, 1375679, 1375680, 1375681, 1375683, 1375684, 1375685, 1375686, 1375687, 1375688, 1375689, 1375690, 1375691, 1375692, 1375693, 1375694, 1375695, 1375698, 1375699, 1375701, 1375708, 1375710, 1375713, 1375723, 1375727, 1375743, 1375746, 1375748, 1375763, 1375764, 1375765, 1375767, 1375789, 1375813, 1375815, 1375817, 1375828, 1375835, 1375836, 1375849, 1375852, 1375864, 1375876, 1375895, 1375908, 1375909, 1375910, 1375911, 1375922, 1375925, 1375930, 1375955, 1375983, 1376027, 1376035, 1376059, 1376114, 1376123, 1376147, 1376219, 1376786, 1376918, 1376948, 1377173, 1377174, 1377185, 1377289, 1377290, 1377291, 1377292, 1377296, 1377297, 1377320, 1377334, 1377345, 1377350, 1377351, 1377352, 1377353, 1377354, 1377358, 1377364, 1377366, 1377367, 1377405, 1377406, 1377407, 1377459, 1377486, 1377508, 1377509, 1377510, 1377511, 1377512, 1377513, 1377533, 1377535, 1377537, 1377565, 1377582, 1377584, 1377585, 1377597, 1377602, 1377607, 1377608, 1377609, 1377610, 1377611, 1377895, 1377896, 1377897, 1377916, 1377918, 1377919, 1377920, 1377921, 1377952, 1377963, 1377965, 1377966, 1377967, 1377968, 1377978, 1378188, 1378191, 1378192, 1378193, 1378441, 1378442, 1378443, 1378538, 1378768, 1379412, 1379816, 1380754, 1380755, 1380756, 1380837, 1380859, 1380950, 1381207, 1381351, 1381352, 1381353, 1381356, 1381358, 1381359, 1381360, 1381362, 1381366, 1381369, 1381520, 1382756, 1382796, 1382876, 1382877, 1382878, 1382879, 1382880, 1382907, 1382943, 1382946, 1382947, 1382948, 1382949, 1382957, 1382958, 1382959, 1382960, 1382962, 1382963, 1382970, 1382971, 1382972, 1382973, 1382974, 1382975, 1382976, 1383187, 1383256, 1383257, 1383260, 1383262, 1383270, 1383271, 1383323, 1383324, 1383452, 1383453, 1383454, 1383462, 1383463, 1383464, 1383465, 1383466, 1383474, 1383475, 1383476, 1383477, 1383478, 1383486, 1383487, 1383488, 1383489, 1383490, 1383491, 1383492, 1383493, 1383501, 1383502, 1383503, 1383589, 1383596, 1383686, 1383840, 1383889, 1383890, 1383891, 1383906, 1383909, 1383999, 1384000, 1384001, 1384002, 1384003, 1384011, 1384012, 1384013, 1384033, 1384034, 1384035, 1384036, 1384037, 1384038, 1384039, 1384040, 1384049, 1384050, 1384051, 1384052, 1384053, 1384054, 1384055, 1384057, 1384058, 1384059, 1384065, 1384068, 1384069, 1384070, 1384071, 1384072, 1384073, 1384074, 1384081, 1384082, 1384083, 1384084, 1384086, 1384087, 1384088, 1384089, 1384090, 1384091, 1384092, 1384093, 1384094, 1384095, 1384096, 1384097, 1384098, 1384099, 1384100, 1384101, 1384102, 1384103, 1384104, 1384105, 1384115, 1384117, 1384118, 1384119, 1384120, 1384121, 1384122, 1384123, 1384124, 1384125, 1384126, 1384127, 1384128, 1384129, 1384130, 1384131, 1384132, 1384133, 1384134)] (Background on this error at: http://sqlalche.me/e/e3q8)

2

I had a similar issue with MySQL and auto-purge a while ago and fixed it by increasing the buffer pool size. The default value is 128MB, and I increased it to 256MB.
On Ubuntu 16.04 with MySQL 5.7 add a new line to /etc/mysql/mysql.conf.d/mysqld.cnf, and then restart the database service:

innodb_buffer_pool_size=256MB

(Your configuration file structure may be different for MariaDB.)

3

hey thanks for reply,

a this time I deleted the hole Database and tried again. I this doesn’t fix my problem I will try to increase the buffer. Thanks a lot

4

Ok,

after 7 Days ( 7 days is „keep purge days“) the recorder stops working.

So why is purging the database not working anymore?? I upgraded MariaDB to Version 10 a few weeks ago. I‘m not sure if this can cause the problem. Maybe its a privileges problem, but cant find a problem.

Please help.

@exxamalte
I cant find this option on my NAS.

5

Did you ever get that sorted? I’ve just seen that error for the first time under 0.91.3 with MariaDB 10 on Synology RS814+. Been working great for the last 2 months until a restart today.

6

No I dont.
I tried several thinks, but nothing works. I went back to the buildin Database. Its now also pretty fast, because I switched from a Raspberry Pi 3+ to a VM on my MacMini server.

7

@kolossboss I found the problem. If MariaDB is upgraded on the NAS, you get a phpmyadmin error like the one below.

If you simply click the links inside phpMyAdmin and go through the steps, it will create a database call phpmyadmin and assign everything there which cures the HA connection issue. I had to do it twice, around 2 weeks apart. The first time it created a database called phpMyAdmin and the second time, phpmyadmin. The blog post that assisted me is here.

image

8

@kolossboss Sadly, my recorder stops too after only 1 day now. If I drop the database at the Synology end, it’s all happy for 12hrs but now that the database is over 150Mb, it’s not happy. No logbook entries and history does not load.

Seems we have to add a my.cnf file into */var/packages/MariaDB/etc with an increased ‘buffer pool size’?

More info here. I suck at VI so haven’t managed to do this in PuTTy yet.

9

@kolossboss Sadly, Synology MariaDB keeps playing up on HassOS 0.92.1. Once database gets over about 150Mb, it seems to get unstable. I did manage to mod the my.cnf file (above) but this has made no difference. Database is 267Mb now and HA will not load/find any logbook entries and history won’t load at all.

I’ve now installed the MariaDB add-on to core and have just started using that to test. On first run, logbook and history load in about 1~2 seconds so will be interesting to see what happens in next 24hrs.

FYI, it seems to only have increased my CPU usage on my old NUC 1% to 11% so not bad.

10

Did this solve your problem ? Recording stopped after a few days and deleting all records from tbale 'states’did not help.
What do you mean with “I’ve now installed the MariaDB add-on to core”
Suffering from bad knowledge of the English language I am afraid and of Mariadb too.

11

Believe my issue was actually related to the conflict between the stream component and shell_commands. I’ve disabled shell_commands until the UV Loop issue that is causing this is fixed and everything is stable.

In regards MariaDB add-on, that is here. The benefit of using that instead of the default database (at least for me) is that you don’t have a large database building in config which makes backing up that location to the cloud a lot smaller.