Maria db dies in few hours

[bigboban]

Describe the issue you are experiencing

I have problems with maria db. I think it is since 2.6.1-2.7.0-2.7.1 buggy upgrade. DB dies after few hours.

What type of installation are you running?

Home Assistant OS

Which operating system are you running on?

Home Assistant Operating System

Which add-on are you reporting an issue with?

MariaDB

What is the version of the add-on?

2.7.1

Steps to reproduce the issue

1. Maria db plugin starts ok.
2. It runs few hours and after that it is somehow stuck. Graphs on dashboard don't load.
3. Is see no message in plugin log in this time.
4. When i restart plugin using plugin frontend i see strange messages something about "homeassistant user not found"
5. When i restart whole (virtual) machine or maria-db container it is operation again.. for few hours.

System Health information

System Information

version	core-2024.4.4
installation_type	Home Assistant OS
dev	false
hassio	true
docker	true
user	root
virtualenv	false
python_version	3.12.2
os_name	Linux
os_version	6.6.25-haos
arch	x86_64
timezone	Europe/Prague
config_dir	/config

Home Assistant Community Store

GitHub API	ok
GitHub Content	ok
GitHub Web	ok
GitHub API Calls Remaining	4996
Installed Version	1.34.0
Stage	running
Available Repositories	1399
Downloaded Repositories	8

Home Assistant Supervisor

host_os	Home Assistant OS 12.2
update_channel	stable
supervisor_version	supervisor-2024.04.0
agent_version	1.6.0
docker_version	25.0.5
disk_total	196.2 GB
disk_used	76.1 GB
healthy	true
supported	true
board	ova
supervisor_api	ok
version_api	ok
installed_addons	Home Assistant Google Drive Backup (0.112.1), Samba share (12.3.1), File editor (5.8.0), Let's Encrypt (5.0.18), Mopidy (0.2.7), Mosquitto broker (6.4.0), Terminal & SSH (9.13.0), ESPHome (2024.3.2), Zigbee2MQTT (1.36.1-1), AdGuard Home (5.0.7), DOODS2 (1), Advanced SSH & Web Terminal (17.2.0), TimescaleDB (4.0.1), MariaDB (2.7.1)

Dashboards

dashboards	4
resources	12
views	19
mode	storage

Recorder

oldest_recorder_run	April 22, 2024 at 05:18
current_recorder_run	April 28, 2024 at 19:24
estimated_db_size	877.91 MiB
database_engine	mysql
database_version	10.11.6

Anything in the Supervisor logs that might be useful for us?

No response

Anything in the add-on logs that might be useful for us?

No response

Additional information

No response

[bigboban]

I can log into plugin docker container but i don't know where some useful log files are located.

[bigboban]

This is log after plugin restart (don't fix situation):

s6-rc: info: service legacy-services: stopping
s6-rc: info: service legacy-services successfully stopped
s6-rc: info: service mariadb-post: stopping
[21:59:19] INFO: Deleting service information from Home Assistant

s6-rc: info: service mariadb-post successfully stopped
s6-rc: info: service mariadb-core: stopping
2024-04-29 21:59:19 0 [Note] mysqld (initiated by: unknown): Normal shutdown
2024-04-29 21:59:39 0 [Warning] mysqld: Thread 7 (user : 'homeassistant') did not exit

2024-04-29 21:59:39 0 [Warning] mysqld: Thread 6 (user : 'homeassistant') did not exit

2024-04-29 21:59:39 0 [Warning] mysqld: Thread 5 (user : 'homeassistant') did not exit

2024-04-29 21:59:39 0 [Warning] mysqld: Thread 4 (user : 'homeassistant') did not exit

2024-04-29 21:59:39 0 [Warning] mysqld: Thread 3 (user : 'homeassistant') did not exit

And this after whole machine restart (this fix the situation):

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service mariadb-pre: starting
[22:05:19] INFO: Using existing mariadb
s6-rc: info: service mariadb-pre successfully started
s6-rc: info: service mariadb-core: starting
s6-rc: info: service mariadb-core successfully started
s6-rc: info: service mariadb-post: starting
[22:05:19] INFO: Starting MariaDB
2024-04-29 22:05:19 0 [Note] Starting MariaDB 10.11.6-MariaDB source revision fecd78b83785d5ae96f2c6ff340375be803cd299 as process 67
2024-04-29 22:05:19 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2024-04-29 22:05:19 0 [Note] InnoDB: Number of transaction pools: 1
2024-04-29 22:05:19 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-04-29 22:05:19 0 [Note] mysqld: O_TMPFILE is not supported on /var/tmp (disabling future attempts)
2024-04-29 22:05:19 0 [Note] InnoDB: Using Linux native AIO
2024-04-29 22:05:19 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2024-04-29 22:05:19 0 [Note] InnoDB: Completed initialization of buffer pool
2024-04-29 22:05:19 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
2024-04-29 22:05:19 0 [Note] InnoDB: End of log at LSN=454558003532
2024-04-29 22:05:19 0 [Note] InnoDB: 128 rollback segments are active.
2024-04-29 22:05:19 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2024-04-29 22:05:19 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2024-04-29 22:05:19 0 [Note] InnoDB: log sequence number 454558003532; transaction id 56114279
2024-04-29 22:05:19 0 [Note] InnoDB: Loading buffer pool(s) from /data/databases/ib_buffer_pool
2024-04-29 22:05:19 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-04-29 22:05:19 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-04-29 22:05:19 0 [Note] Server socket created on IP: '::'.
2024-04-29 22:05:19 0 [Note] mysqld: ready for connections.
Version: '10.11.6-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Alpine Linux
2024-04-29 22:05:19 0 [Note] InnoDB: Buffer pool(s) load completed at 240429 22:05:19
[22:05:20] INFO: MariaDB started
[22:05:20] INFO: Check data integrity and fix corruptions
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
homeassistant.event_data                           OK
homeassistant.event_types                          OK
homeassistant.events                               OK
homeassistant.migration_changes                    OK
homeassistant.recorder_runs                        OK
homeassistant.schema_changes                       OK
homeassistant.state_attributes                     OK
homeassistant.states                               OK
homeassistant.states_meta                          OK
homeassistant.statistics                           OK
homeassistant.statistics_meta                      OK
homeassistant.statistics_runs                      OK
homeassistant.statistics_short_term                OK
sys.sys_config                                     OK
[22:05:20] INFO: Ensuring internal database upgrades are performed
[22:05:20] INFO: Ensure databases exists
[22:05:20] INFO: Create database homeassistant
[22:05:20] INFO: Ensure users exists and are updated
[22:05:20] INFO: Update user homeassistant
[22:05:20] INFO: Init/Update rights
[22:05:20] INFO: Granting all privileges to homeassistant on homeassistant
[22:05:21] INFO: Sending service information to Home Assistant

s6-rc: info: service mariadb-post successfully started
s6-rc: info: service legacy-services: starting
s6-rc: info: service legacy-services successfully started

[agners]

In general, we rely on the MariaDB version shipped by Alpine Linux. It could be that this new version uses more resources or introduced a bug your system is triggering 😢

2. It runs few hours and after that it is somehow stuck. Graphs on dashboard don't load.

At this point, is the add-on considered started? Is there high CPU load on the system (the Glances add-on can give you more insight). Is there anything in the Supervisor logs? Could it maybe just be an OOM situation? 🤔