MySQL configuration

Configuration
1

Hello,

I had my db running on external MariaDB for months. As it was version 10.4. I had a message to upgrade this. During that I messed up my db and decided to start over.
I started my mariadb with:

* CREATE DATABASE homeassistant;
* create user 'hass' identified by 'supergeheim';
* GRANT ALL ON `homeassistant`.* TO 'hass'@'%';
* FLUSH PRIVILEGES;

I can access this db from my Windows with HeidiSQL. Starting HA I can see it seems to start initialising that db, but results in an error. Can someone please help?

Database error during schema migration
21:54:28 – (FEHLER) Recorder
Error executing query: (MySQLdb.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: CREATE INDEX ix_states_event_id ON states (event_id)] (Background on this error at: https://sqlalche.me/e/14/e3q8)
21:54:28 – (FEHLER) Recorder
Adding index `ix_states_event_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
21:54:28 – (WARNUNG) Recorder
Database is about to upgrade from schema version: 4 to: 33
21:54:28 – (WARNUNG) Recorder

It is consistent the same SQL call CREATE INDEX ‘ix_states_event_id’ across multiple restarts, so I don’t think it is a real connection issue.

Regards,
Anduril

2

Lost connection during query can mean a number of different things, but given adding indexes can take a while, chances are the connection is timing out before its complete.

you can check the current timeout value by running
SHOW VARIABLES LIKE "%timeout";

IIRC, connect timeout default is only like 10 seconds, you probably want it more like 300 or 600.
SET GLOBAL connect_timeout = 600;

If all else fails, try running the query manually.

3

The timeout was set to 10s as you expected. I changed it and tried again, with exact same result. I also run the query directly on my MariaDB host machine. But it can’t run it either:

root@localhost [ha]> CREATE INDEX ix_states_event_id ON states (event_id);
ERROR 2006 (HY000): Server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: ha

ERROR 2013 (HY000): Lost connection to server during query

Any ideas on how to go on?

edit: during my research I found that it might be due to a large packet transmitted. The standard size in my db is 16mb, but I’m not able to increase it. Maybe I can disable some home assistant entities to make the db smaller and include them in batches later? But how…?

edit2: well I managed to increase it to 512M, but still I get the disconnect… I think that is way beyond any database on a RPi could handle…

4

I recall two possible reasons (from times of mysql 5):

  • too long query
  • not enough space on storage (or wrong privileges making impossible ro store data)
5
  • well the querry is defined by HA - is there a way to reduce the amout of data that my existing HA is writing to the db?
  • not enough space can ruled out (df -h shows 3.6T available) and permissions I think are also correct. At least MariaDB could create that database and write the table-files each containing a few K of data
6

make sure you also change those settings in config files, otherwise they wont persist.

There are a number of other timeout settings, and max packet size type settings, try those.

The best and worst thing about mysql/mariadb is how configurable it is.

7

well I did revisit this problem. During that time I used the mariadb addon which works perfect. I entered interactive docker mode to look for the settings made there:

MariaDB [(none)]> SHOW VARIABLES LIKE "%timeout";
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| connect_timeout                       | 10    |
| delayed_insert_timeout                | 300   |
| idle_readonly_transaction_timeout     | 0     |
| idle_transaction_timeout              | 0     |
| idle_write_transaction_timeout        | 0     |
| innodb_flush_log_at_timeout           | 1     |
| innodb_lock_wait_timeout              | 50    |
| innodb_rollback_on_timeout            | OFF   |
| interactive_timeout                   | 28800 |
| lock_wait_timeout                     | 86400 |
| net_read_timeout                      | 30    |
| net_write_timeout                     | 60    |
| rpl_semi_sync_master_timeout          | 10000 |
| rpl_semi_sync_slave_kill_conn_timeout | 5     |
| slave_net_timeout                     | 60    |
| thread_pool_idle_timeout              | 60    |
| wait_timeout                          | 28800 |
+---------------------------------------+-------+
17 rows in set (0.003 sec)

MariaDB [(none)]> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.002 sec)

Those are exactly the same as on my NAS hosting mariadb, except for the now changed max_allowed_packet to 536870912

I really want to have my db on a seperate system with storage with reasonable write cycles…