Hi there,
I have transfered the database to mariaDB hosted on a Qnap. I can see that all data will come into the DB on th qnap but my Logbook in HA cannot find any data anymore.
The Database on the Qnap has char-setting utf8mb4-generic and the configuration.yaml shows:
recorder:
purge_keep_days: 7
db_url: mysql://name:[email protected]:3307/DatabaseName?charset=utf8
any ideas?
Spartacus
Hi all,
it seems to be that logging data in an external DB is not really working. It works for 1 or two hours and then no enries are listed in the logbook anymore. The circle is spinning around. Tthe following errors occur.
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:105
Integration: Recorder (documentation, issues)
First occurred: 23:08:40 (2 occurrences)
Last logged: 23:09:06Error executing query: ‘button_8’
Whatever this means. No idea what "button_8 " is !
Spartacus
Have you tried changing the code to:
recorder:
purge_keep_days: 7
db_url: mysql://name:[email protected]:3307/DatabaseName?charset=utf8mb4
Hello Stefan,
yes, I have done that several times. What I also did is to install the addon locally, backup the database and restore it to the external host. Only to make sure that everything is correct. But this causes also the issue.
I will test the local DB again for a day and check, if the error occurs!
I think, the error above is the key to issue and there is something wrong with thw tables. I will come back to you.
Spartacus
P.S: The local environment shows the same issue. So I guess, there is a bug in the recorder or in the sql-Tables.
Hi there,
ok. I have tested a lot and it seems to be that something is wrong with the tabels in the SQL Database. I deleted all tables and started from scretch with HA and MariaDB with only a few sensors. After a few hours, I got an Error in the HA Log.
I mentioned that the errors are different and I guess that this has something to do with the sensors which have been configured in the recorder include section.
You can also see, that HA writes the data, only the reading seems to be a problem.
Is there noone in the HA world who has similar issues?
Spartacus
When resetting, It’s better to drop the database and create it again than delete all the tables to make sure you got everything
Hi Nick,
thanks for reply, but I did it several times. I have created a ticket (#86684) due to the fact, that this is a bug with deconz.
I can NOT confirm this issue, running it for around 6 months successfully on my Qnap and I would not consider my DB as small
My settings are a bit different and I exclude a lot of stuff: 0bin - encrypted pastebin
But I also enabled InnoDB on the NAS, you are running myISAM, which will create already an issue (see: Can't filter logbook after upgrading to 2022.6 - #7 by bdraco) and changed other things in the config, see here: Can't filter logbook after upgrading to 2022.6 - #9 by NODeeJay
IMHO the change to commit every 30 seconds only made the difference in stability.
db_url: mysql://homeassistant:<beep>@nas-003.<beep>.local:3306/homeassistant?charset=utf8mb4
commit_interval: 30
# auto_purge: true
auto_repack: true
purge_keep_days: 720
exclude:
Even the DB migration (Qnap is still running MariaDB 10.5.8) was done on the NUC and the NAS overnight in around 9 1/2 hours and the size query above took 3 seconds.
I replicated this exact setup successfully to several newer QTS 5 models with the included MariaDB and the InnoDB changes and even older QTS 4.2 models with a “manual” upgrade of MariaDB to v 10.6.x so I can recommend giving this setup a try and see if it solves the issue.
I know that Qnap “does silently not recommend” activating InnoDB, but I haven’t seen any issues yet and use it extensively on the Qnaps.
Hi Alexander,
thanks fpr reply! I am not an expert and I cannot really folow what exactly you did, to get this fixed. I am not running InnoDB and startet on scatch with Maria DB10.5.8. Does it mean, that you have started with Maria 10.6.x on a different Server and athen you have trasfereed the DB to the Qnap?
I add the comit_interval to my config and I will see what happens. Do you think, i should remove the tables again?
Thanks,
Spartacus.
Ok, let’s check it step by step.
You have MariaDB already enabled on a QTS 5 and it seems in the default config. You also changed the commit interval now to something higher than > 10 seconds.
You will unavoidibly run in Home Assistant when you query information into:
Error executing query: (MySQLdb._exceptions.OperationalError) (1176, “Key ‘ix_states_entity_id_last_updated’ doesn’t exist in table ‘states’”)
To fix it you need create an index:
CREATE INDEX ix_states_entity_id_last_updated ON states (entity_id, last_updated);
Which you can’t because myISAM will hit the character limit:
Specified key was too long; max key length is 1000 bytes
To avoid all of this you need to allow InnoDB on your Qnap. Follow those 9 steps to enable it, you can do it all from the webinterface, you just need Qnap Text Editor and PHPmyAdmin:
Afterwards you can still change the recorder to suit your needs, you may check my pastebin and decide what not to record, for example changes in time/clock/sun etc.
Let me know when you get stuck at some point, we look at it in detail.
Hi Alexander,
thank you very much für your support. I will do this in a quiet minute , today there is too much bustle 
If I understand this correctly, Inno_DB is such a kind of “option” for MariaDB to fix the key length. I have had a couple of DBs on MatiaDB 5 also running on qnap, which I have transferred to MariaDB10 today. I hope everthing runs smoothly. (an issue with freeradius ; ERROR 1364 (Field ‘id’ doesn’t have a default va lue) which I fixed with autoincrement)) with the new configuration of InnoDB.
I will come back to you soon,
Spartacus
Hi Alexander,
today I made the changes to MariaDB10. I removed the old database and created a new one. At first everything looked good, but after 2 hours the same issue appeared again.
I really need to think about limiting the recordings.
many thanks up to now,
Spartacus.
Make sure you are using InnoDB
Its likely your database has been created with MyIASM which will not work correctly.
Hi all,
I am confused! I followed the guide from Alexander and I am pretty sure, that InnoDB is allowed on the Qnap.
After the modification I restarted MariaDB10 and created a brand new Database homeassistant. Where is my mistake? How can I make sure, that Iam using InnoDB?
Spartacus.
You see it in PHPmyadmin unter Typ zwischen Kollation und Datensaetze, check it there. When InnoDB is activated HA should create the tables in InnoDB.
Can you manually try to create a DB in PHPmyadmin with UTF8
and a table with the StorageEngine InnoDB
Addtionally I suggest you increase logging in HASS. See: Logger - Home Assistant
Hell, if I just would find now how to specifically increase it for the recorder? You may increase it in general.
Then query the DB with a log entry and check what is happening in Home Assistant. If there is an issue accessing your DB/NAS or else it should be there. Did you migrate to 2023.2 already?
And regarding your earlier question. Yes, I actually started on MariaDB on the NUC. Then I decided not to go for any timescale variant - why degrading data quality when the TB is cheap - and keep all for 2 years in MariaDB on the NAS. As I had to migrate from InnoDB to MyISAM to I dumped the “already” 250 MB big MariaDB from the NUC and imported it to the NAS. After a couple of days I ran into issues, namely the index issue and @bdraco and the QNAP forum helped. After the change to InnoDB I started from scratch and since then no issues.
InnoDB and MyISAM are database engines, see: MyISAM vs InnoDB: 7 Critical Differences
Hallo Alexander,
sieht doch gut aus, oder?
Do you think I have to re-create the database? Actually I do not have any issues and I am logging everything.
recorder:
# The number of days of history to keep in the recorder database
purge_keep_days: 720
# Connection to MariaDB on Carla, DB Homeassistant
db_url: mysql://HomeAssistant:<kennwort>@192.168.x.x:3307/homeassistant?charset=utf8mb4
commit_interval: 30
auto_repack: true
#include:
# domains:
# - mqtt
# entity_globs:
# - sensor.ds24*
# - binary_sensor.golf*
But Ihere are two questions left.
I am running the MariaDB10 on my QNAP. I do not really understand the difference between “MariaDB” and “InnoDB” due to the fact, that I only have insert some lines in the mysql.config. MariaDB 10 is still running…
HA is writing the data and the states to the DB. I can see the data in the logbook. I think HA is reading the data for the lookbook from the database. What is meant when HA is talking from History ? Is there a diffrence, or do we speak from the same data?
Thanks a lot,
Spartacus.
Servus, in Bayern sagma da: Laaft nei wia a gleckal
So to unterstand you correctly, it works now, the logbook is showing something, the perfomance is better than before when querying data together in History or Logbook and you do not have any weird entries from the recorder in the Home Assistant log?
When the recorder is setup that way, HA will read it from the remote DB on your NAS. You can make triple sure by
Let’s try an analogy that every German will understand.
SQL is like alcohol, there are many variations like Gin, Vodka, Wiskey, Wine etc. the same in DBs: mySQL, MS-SQL, MariaDB, SQLite etc.
MariaDB is like the beer. But beer still has “sub variations”, like Hefe, Pils, Dunkel, Bock, usw. In our case we wanna compare Hefeweizen with Hellem/Lager, both are made following the Reinheitsgebot from 1300 and all contain the same stuff: Wasser, Hefe, Hopfen und Malz. But Hefeweizen has a different Malz (Weizenmalz) and at least 50% of it compared to Hellem.
Back to brewing our SQL " sub variants": myISAM (mySQL and MariaDB) is a fast sub variant, its the engine that drives storing and retrieving data - mainly how - designed for storing and reading. No separate transaction log. Nice for simple Webapplications etc. As there is no log, a rollback is impossible and power loss/crash can be a complete disaster, but again, it’s fast.
InnoDB is not much slower, but knows transactions. It’s great when you do not just store but also alter data. And in case of power loss or a disaster the DB will “try to recover” into a consistent state by rolling back unfinished transactions with it’s log. Much more resilient, much more resource consumption etc.
Hi Alexander,
I am working with the approach above now for a while and it works perfect.
But, I am observing a phenomenon that I cannot explain:
When HA reboots it seems to be that HA writes a “0” into the database for all my sensors. If you follow the graph in HA you see a small “hole” in the graph. But when you examine the states tabel in mariaDB you see that this hole i will be interpreted as a “0”-Value which resukts in a n error when plotting praphs with grafana.
Do you know, how to fix this issue in the mariaDB? Obviously, HA does not have this problem!!
Thanks,
Spartacus
