Addons: MariaDB addon crashes when using repack function

Created on 16 Jul 2020 · 8Comments · Source: home-assistant/addons

I use the following automation to keep my MariaDB (core-addon) database compact.

- alias: Database Purge
  trigger:
    platform: time
    at: '04:30'
  action:
    - service: recorder.purge
      data:
        keep_days: 2
        repack: true

When executed, the following appears in my Logbook (the time is different from the automation, it was triggered manually to gather the necessary information) :

Logger: homeassistant.components.recorder.purge
Source: components/recorder/purge.py:97
Integration: Recorder (documentation, issues)
First occurred: 10:49:43 (1 occurrences)
Last logged: 10:49:43

Error purging history: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: OPTIMIZE TABLE states, events, recorder_runs] (Background on this error at: http://sqlalche.me/e/e3q8).

And this appears in the logbook of the MariaDB addon:

or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
Server version: 10.4.13-MariaDB
key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=3
max_threads=66
thread_count=9
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68678 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f7072fd1bc8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0xb, stack_bottom=0x7f7072730000, thread_stack=196608, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f7072ff6220): OPTIMIZE TABLE states, events, recorder_runs
Connection ID (thread ID): 24
Status: NOT_KILLED
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file...
Working directory at /data/databases
Resource Limits:
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             unlimited            unlimited            processes 
Max open files            1048576              1048576              files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       7918                 7918                 signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c ...
2020-07-16 10:49:43 0 [Note] mysqld: Aria engine: starting recovery
recovered pages: 0% 32% 51% 80% 100% (0.0 seconds); tables to flush: 2 1 0
 (0.0 seconds); 
2020-07-16 10:49:43 0 [Note] mysqld: Aria engine: recovery done
2020-07-16 10:49:43 0 [Note] InnoDB: Using Linux native AIO
2020-07-16 10:49:43 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-16 10:49:43 0 [Note] InnoDB: Uses event mutexes
2020-07-16 10:49:43 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-07-16 10:49:43 0 [Note] InnoDB: Number of pools: 1
2020-07-16 10:49:43 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-07-16 10:49:43 0 [Note] mysqld: O_TMPFILE is not supported on /var/tmp (disabling future attempts)
2020-07-16 10:49:43 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-07-16 10:49:43 0 [Note] InnoDB: Completed initialization of buffer pool
2020-07-16 10:49:43 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-07-16 10:49:43 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=600800130
2020-07-16 10:49:43 0 [Note] InnoDB: Ignoring data file './homeassistant/events.ibd' with space ID 13, since the redo log references ./homeassistant/events.ibd with space ID 10.
2020-07-16 10:49:43 0 [Note] InnoDB: Ignoring data file './homeassistant/states.ibd' with space ID 12, since the redo log references ./homeassistant/states.ibd with space ID 9.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 12. Another data file called ./homeassistant/states.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 12. Another data file called ./homeassistant/states.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 12. Another data file called ./homeassistant/states.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 13. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 13. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 13. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/recorder_runs.ibd' with space ID 14, since the redo log references ./homeassistant/recorder_runs.ibd with space ID 11.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 14. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 14. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-16f_18.ibd' with space ID 14. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-07-16 10:49:44 0 [Note] InnoDB: Starting final batch to recover 750 pages from redo log.
2020-07-16 10:49:44 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-07-16 10:49:44 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-07-16 10:49:44 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-07-16 10:49:44 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-07-16 10:49:44 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-07-16 10:49:44 0 [Note] InnoDB: Waiting for purge to start
2020-07-16 10:49:44 0 [Note] InnoDB: 10.4.13 started; log sequence number 607958257; transaction id 365683
2020-07-16 10:49:44 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-07-16 10:49:44 0 [Note] Server socket created on IP: '::'.
2020-07-16 10:49:44 0 [Note] InnoDB: Loading buffer pool(s) from /data/databases/ib_buffer_pool
2020-07-16 10:49:44 0 [Note] InnoDB: Buffer pool(s) load completed at 200716 10:49:44
2020-07-16 10:49:44 0 [Note] Reading of all Master_info entries succeeded
2020-07-16 10:49:44 0 [Note] Added new Master_info '' to hash table
2020-07-16 10:49:44 0 [Note] /usr/bin/mysqld: ready for connections.
Version: '10.4.13-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 3306  MariaDB Server

This is information about my Home Assistant:

arch    x86_64
chassis vm
dev false
docker  true
docker_version  19.03.8
hassio  true
host_os HassOS 4.11
installation_type   Home Assistant OS
os_name Linux
os_version  5.4.50
python_version  3.7.7
supervisor  228
timezone    Europe/Amsterdam
version 0.112.3
virtualenv  false

And this is how I connect Home Assistant to MariaDB

# mariadb database (add-on)
recorder:
  db_url: mysql://homeassistant:nottherealpassword@core-mariadb/homeassistant?charset=utf8
  auto_purge: false

I assume that it is not normal behavior to crash the database and then rebuild it.

mariadb stale

Source

mvandek

👍3

Most helpful comment

Can this issue be re-opened? This is still an issue and I've pinpointed it to the following query: OPTIMIZE TABLE recorder_runs;.

I already tried removing the recorder_runs table and let Home Assistant recreate it, but to no avail.

pinging @pvizeli @frenck

alex3305 on 6 Oct 2020

👍2

All 8 comments

I am having the exact same issue. I don't recall ever seeing the log entries for the DB connection failing before, and I've had my settings set to nightly purge/repack for about a month now.

My install is method is different and version numbers are slightly different than yours, otherwise things look the same.

I'm running MariaDB addon version 2.2.1

sp0rus on 22 Jul 2020

I changed to auto-purge and no problem.
But when I try (even from dev-tools / services) a manual purge, I have to restart the system, because this error freezes everything.

Loop error...

2020-07-25 18:00:04 ERROR (Recorder) [homeassistant.components.recorder] Error adding event: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/13/e3q8) (Background on this error at: http://sqlalche.me/e/13/7s2a)
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 379, in run
    self.event_session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2523, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2618, in _flush
    flush_context.transaction = transaction = self.begin(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 953, in begin
    self.transaction = self.transaction._begin(nested=nested)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 317, in _begin
    self._assert_active()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 289, in _assert_active
    raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/13/e3q8) (Background on this error at: http://sqlalche.me/e/13/7s2a)

After the reboot, however, the purge was done.

| Precedente | Attuale | Variazione | - Percentuale
-- | -- | -- | -- | --
DB | 151.1 MB | 79.3 MB | -47.52% | (🔽71.8)
Eventi | 285.474 | 144.424 | -49.41% | (🔽141050)
Stati | 173.107 | 86.833 | -49.84% | (🔽86274)

Current version: 2.2.1

MariaDB log after restart Home Assistant

2020-07-24 21:07:59 244 [Warning] Aborted connection 244 to db: 'homeassistant' user: 'homeassistant' host: '172.30.32.1' (Got an error reading communication packets)
2020-07-24 23:43:00 249 [Warning] Aborted connection 249 to db: 'homeassistant' user: 'homeassistant' host: '172.30.32.1' (Got an error reading communication packets)
200725 17:51:52 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

Server version: 10.4.13-MariaDB
key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=9
max_threads=66
thread_count=12
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68678 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f197bc4f588
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0xb, stack_bottom=0x7f1971ef0000, thread_stack=196608, aborting backtrace.

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f197bc9c220): OPTIMIZE TABLE states, events, recorder_runs
Connection ID (thread ID): 258
Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file...
Working directory at /data/databases
Resource Limits:
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             unlimited            unlimited            processes 
Max open files            1048576              1048576              files     
Max locked memory         16777216             16777216             bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       127745               127745               signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: |/usr/share/apport/apport %p %s %c %d %P ...
2020-07-25 17:51:52 0 [Note] mysqld: Aria engine: starting recovery
recovered pages: 0% 32% 51% 80% 100% (0.0 seconds); tables to flush: 2 1 0
 (0.0 seconds); 
2020-07-25 17:51:52 0 [Note] mysqld: Aria engine: recovery done
2020-07-25 17:51:52 0 [Note] InnoDB: Using Linux native AIO
2020-07-25 17:51:52 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-25 17:51:52 0 [Note] InnoDB: Uses event mutexes
2020-07-25 17:51:52 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-07-25 17:51:52 0 [Note] InnoDB: Number of pools: 1
2020-07-25 17:51:52 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-07-25 17:51:52 0 [Note] mysqld: O_TMPFILE is not supported on /var/tmp (disabling future attempts)
2020-07-25 17:51:52 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-07-25 17:51:52 0 [Note] InnoDB: Completed initialization of buffer pool
2020-07-25 17:51:52 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-07-25 17:51:52 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=417154402
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/events.ibd' with space ID 84, since the redo log references ./homeassistant/events.ibd with space ID 81.
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/recorder_runs.ibd' with space ID 85, since the redo log references ./homeassistant/recorder_runs.ibd with space ID 82.
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-171_102.ibd' with space ID 84. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-171_102.ibd' with space ID 84. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-171_102.ibd' with space ID 84. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-171_102.ibd' with space ID 85. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-171_102.ibd' with space ID 85. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-07-25 17:51:52 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-171_102.ibd' with space ID 85. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-07-25 17:51:52 0 [Note] InnoDB: Starting final batch to recover 653 pages from redo log.
2020-07-25 17:51:53 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-07-25 17:51:53 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-07-25 17:51:53 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-07-25 17:51:53 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-07-25 17:51:53 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-07-25 17:51:53 0 [Note] InnoDB: Waiting for purge to start
2020-07-25 17:51:53 0 [Note] InnoDB: 10.4.13 started; log sequence number 417230692; transaction id 40916
2020-07-25 17:51:53 0 [Note] InnoDB: Loading buffer pool(s) from /data/databases/ib_buffer_pool
2020-07-25 17:51:53 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-07-25 17:51:53 0 [Note] InnoDB: Buffer pool(s) load completed at 200725 17:51:53
2020-07-25 17:51:53 0 [Note] Server socket created on IP: '::'.
2020-07-25 17:51:53 0 [Note] Reading of all Master_info entries succeeded
2020-07-25 17:51:53 0 [Note] Added new Master_info '' to hash table
2020-07-25 17:51:53 0 [Note] /usr/bin/mysqld: ready for connections.
Version: '10.4.13-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 3306  MariaDB Server

caiosweet on 25 Jul 2020

Same issue, also removed the addon, reinstalled it, and did a repack, it failed as well with the same error

simon2k13 on 11 Aug 2020

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

stale[bot] on 11 Sep 2020

Can this issue be re-opened? This is still an issue and I've pinpointed it to the following query: OPTIMIZE TABLE recorder_runs;.

I already tried removing the recorder_runs table and let Home Assistant recreate it, but to no avail.

pinging @pvizeli @frenck

alex3305 on 6 Oct 2020

👍2

Please re-open this issue. I have the same problem. I am runing the latest 0.116.2 on Intel Nuc with Hass.io 247.

Core Log:
2020-10-10 11:17:39 WARNING (Recorder) [homeassistant.components.recorder.purge] Error purging history: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: OPTIMIZE TABLE states, events, recorder_runs] (Background on this error at: http://sqlalche.me/e/13/e3q8) 2020-10-10 11:17:39 ERROR (Recorder) [homeassistant.components.recorder] Error executing query: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)") (Background on this error at: http://sqlalche.me/e/13/e3q8) 2020-10-10 11:17:39 ERROR (Recorder) [homeassistant.components.recorder] Error in database connectivity during commit: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)") (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)

MariaDB Log:
2020-10-10 9:57:33 0 [Note] /usr/bin/mysqld: ready for connections. Version: '10.4.13-MariaDB' socket: '/run/mysqld/mysqld.sock' port: 3306 MariaDB Server 2020-10-10 9:57:33 0 [Note] InnoDB: Buffer pool(s) load completed at 201010 9:57:33 2020-10-10 11:01:05 9 [Warning] Aborted connection 9 to db: 'homeassistant' user: 'homeassistant' host: '172.30.32.1' (Got an error reading communication packets) 2020-10-10 11:01:05 11 [Warning] Aborted connection 11 to db: 'homeassistant' user: 'homeassistant' host: '172.30.32.1' (Got an error reading communication packets) 2020-10-10 11:01:05 8 [Warning] Aborted connection 8 to db: 'homeassistant' user: 'homeassistant' host: '172.30.32.1' (Got an error reading communication packets) 201010 11:17:39 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.4.13-MariaDB key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=4 max_threads=66 thread_count=9 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68678 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7fc557cbe1c8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xb, backtrace may not be correct. Bogus stack limit or frame pointer, fp=0xb, stack_bottom=0x7fc558050000, thread_stack=196608, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7fc557decee0): OPTIMIZE TABLE states, events, recorder_runs Connection ID (thread ID): 15 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /data/databases Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size unlimited unlimited bytes Max resident set unlimited unlimited bytes Max processes unlimited unlimited processes Max open files 1048576 1048576 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 13523 13523 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c ... 2020-10-10 11:17:39 0 [Note] InnoDB: Using Linux native AIO 2020-10-10 11:17:39 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-10-10 11:17:39 0 [Note] InnoDB: Uses event mutexes 2020-10-10 11:17:39 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-10-10 11:17:39 0 [Note] InnoDB: Number of pools: 1 2020-10-10 11:17:39 0 [Note] InnoDB: Using SSE2 crc32 instructions 2020-10-10 11:17:39 0 [Note] mysqld: O_TMPFILE is not supported on /var/tmp (disabling future attempts) 2020-10-10 11:17:39 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2020-10-10 11:17:39 0 [Note] InnoDB: Completed initialization of buffer pool 2020-10-10 11:17:39 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-10-10 11:17:39 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=10739317269 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/events.ibd' with space ID 41, since the redo log references ./homeassistant/events.ibd with space ID 38. 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1db_f.ibd' with space ID 41. Another data file called ./homeassistant/events.ibd exists with the same space ID. 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1db_f.ibd' with space ID 41. Another data file called ./homeassistant/events.ibd exists with the same space ID. 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1db_f.ibd' with space ID 41. Another data file called ./homeassistant/events.ibd exists with the same space ID. 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/recorder_runs.ibd' with space ID 42, since the redo log references ./homeassistant/recorder_runs.ibd with space ID 39. 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1db_f.ibd' with space ID 42. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID. 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1db_f.ibd' with space ID 42. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID. 2020-10-10 11:17:39 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1db_f.ibd' with space ID 42. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID. 2020-10-10 11:17:39 0 [Note] InnoDB: Starting final batch to recover 16 pages from redo log. 2020-10-10 11:17:40 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2020-10-10 11:17:40 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2020-10-10 11:17:40 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-10-10 11:17:40 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-10-10 11:17:40 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-10-10 11:17:40 0 [Note] InnoDB: Waiting for purge to start 2020-10-10 11:17:40 0 [Note] InnoDB: 10.4.13 started; log sequence number 10739358352; transaction id 3227981 2020-10-10 11:17:40 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-10-10 11:17:40 0 [Note] InnoDB: Loading buffer pool(s) from /data/databases/ib_buffer_pool 2020-10-10 11:17:40 0 [Note] Server socket created on IP: '::'. 2020-10-10 11:17:40 0 [Note] Reading of all Master_info entries succeeded 2020-10-10 11:17:40 0 [Note] Added new Master_info '' to hash table 2020-10-10 11:17:40 0 [Note] /usr/bin/mysqld: ready for connections. Version: '10.4.13-MariaDB' socket: '/run/mysqld/mysqld.sock' port: 3306 MariaDB Server 2020-10-10 11:17:40 0 [Note] InnoDB: Buffer pool(s) load completed at 201010 11:17:40

lukasmd1989 on 10 Oct 2020

Log HA:
2020-10-26 14:41:10 WARNING (Recorder) [homeassistant.components.recorder.purge] Error purging history: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: OPTIMIZE TABLE states, events, recorder_runs]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
2020-10-26 14:41:15 ERROR (Recorder) [homeassistant.components.recorder] Error in database connectivity during keepalive: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/13/e3q8)
2020-10-26 14:41:17 ERROR (Recorder) [homeassistant.components.recorder] Error executing query: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/13/e3q8)
2020-10-26 14:41:17 ERROR (Recorder) [homeassistant.components.recorder] Error in database connectivity during commit: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)

Log MariaDB:
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 10.4.13-MariaDB
key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=3
max_threads=66
thread_count=9
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68678 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7fc607690e48
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0xb, stack_bottom=0x7fc606ac0000, thread_stack=196608, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7fc6076b25a0): OPTIMIZE TABLE states, events, recorder_runs
Connection ID (thread ID): 37
Status: NOT_KILLED
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file...
Working directory at /data/databases
Resource Limits:
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size unlimited unlimited bytes
Max resident set unlimited unlimited bytes
Max processes unlimited unlimited processes
Max open files 1048576 1048576 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 13074 13074 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
Core pattern: co...
2020-10-26 14:41:18 0 [Note] mysqld: Aria engine: starting recovery
recovered pages: 0% 12% 22% 33% 43% 53% 65% 75% 85% 95% 100% (0.0 seconds); tables to flush: 3 2 1 0
(0.3 seconds);
2020-10-26 14:41:18 0 [Note] mysqld: Aria engine: recovery done
2020-10-26 14:41:18 0 [Note] InnoDB: Using Linux native AIO
2020-10-26 14:41:18 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-26 14:41:18 0 [Note] InnoDB: Uses event mutexes
2020-10-26 14:41:18 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-26 14:41:18 0 [Note] InnoDB: Number of pools: 1
2020-10-26 14:41:18 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-10-26 14:41:18 0 [Note] mysqld: O_TMPFILE is not supported on /var/tmp (disabling future attempts)
2020-10-26 14:41:18 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-10-26 14:41:18 0 [Note] InnoDB: Completed initialization of buffer pool
2020-10-26 14:41:18 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-10-26 14:41:18 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=539060
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/events.ibd' with space ID 10, since the redo log references ./homeassistant/events.ibd with space ID 5.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/states.ibd' with space ID 9, since the redo log references ./homeassistant/states.ibd with space ID 8.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 9. Another data file called ./homeassistant/states.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 9. Another data file called ./homeassistant/states.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 9. Another data file called ./homeassistant/states.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 10. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 10. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 10. Another data file called ./homeassistant/events.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/recorder_runs.ibd' with space ID 11, since the redo log references ./homeassistant/recorder_runs.ibd with space ID 6.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 11. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 11. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Ignoring data file './homeassistant/#sql-1b3_25.ibd' with space ID 11. Another data file called ./homeassistant/recorder_runs.ibd exists with the same space ID.
2020-10-26 14:41:18 0 [Note] InnoDB: Starting final batch to recover 48 pages from redo log.
2020-10-26 14:41:19 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-10-26 14:41:19 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-10-26 14:41:19 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-10-26 14:41:19 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-10-26 14:41:19 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-10-26 14:41:19 0 [Note] InnoDB: 10.4.13 started; log sequence number 621853; transaction id 273
2020-10-26 14:41:19 0 [Note] InnoDB: Loading buffer pool(s) from /data/databases/ib_buffer_pool
2020-10-26 14:41:19 0 [Note] InnoDB: Buffer pool(s) load completed at 201026 14:41:19
2020-10-26 14:41:19 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-10-26 14:41:19 0 [Note] Server socket created on IP: '::'.
2020-10-26 14:41:19 0 [Note] Reading of all Master_info entries succeeded
2020-10-26 14:41:19 0 [Note] Added new Master_info '' to hash table
2020-10-26 14:41:19 0 [Note] /usr/bin/mysqld: ready for connections.
Version: '10.4.13-MariaDB' socket: '/run/mysqld/mysqld.sock' port: 3306 MariaDB Server

Here I have a clean base, since I just reinstalled mariadb, it did not help, before this base there was ~ 150MB. The error occurs when doing a 'purge' ..