lmagyar / homeassistant-addon-mariadb-inmemory Goto Github PK

Getting this error when attempting to update:

Can't install lmagyar/aarch64-addon-mariadb-inmemory:2.5.1.5: 404 Client Error for http+docker://localhost/v1.41/images/create?tag=2.5.1.5&fromImage=lmagyar%2Faarch64-addon-mariadb-inmemory&platform=linux%2Farm64: Not Found ("manifest for lmagyar/aarch64-addon-mariadb-inmemory:2.5.1.5 not found: manifest unknown: manifest unknown")

Hi there,

Installing and first start are okay.

But after reboot, I still have same issue : ERROR 1146 (42S02) at line 1: Table 'homeassistant.schema_changes' doesn't exist

So MardiaDB exit. I'm not sure what I can do.

My install :
Home Assistant 2023.7.2
Supervisor 2023.07.1
Operating System 10.3
Interface utilisateur : 20230705.1 - latest

My installed add-ons :
Duck DNS
File editor
In-memory MariaDB
Mosquitto broker
php-nginx Docker Image
rest980 Docker Image
Terminal & SSH

s6-rc: info: service mariadb-pre: starting
[22:18:39] INFO: Change tmpfs size to 200m
[22:18:39] INFO: Create a new 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:18:40] INFO: Starting MariaDB
2023-07-17 22:18:40 0 [Note] Starting MariaDB 10.6.12-MariaDB source revision 4c79e15cc3716f69c044d4287ad2160da8101cdc as process 209
2023-07-17 22:18:40 0 [Note] InnoDB: innodb_page_size=4096
2023-07-17 22:18:40 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
2023-07-17 22:18:40 0 [Note] InnoDB: Number of pools: 1
2023-07-17 22:18:40 0 [Note] InnoDB: Using generic crc32 instructions
2023-07-17 22:18:40 0 [Note] InnoDB: Using Linux native AIO
2023-07-17 22:18:40 0 [Note] InnoDB: Initializing buffer pool, total size = 5242880, chunk size = 5242880
2023-07-17 22:18:40 0 [Note] InnoDB: Completed initialization of buffer pool
2023-07-17 22:18:40 0 [Note] InnoDB: Setting O_DIRECT on file ./ibdata1 failed
2023-07-17 22:18:40 0 [Note] InnoDB: Opened 2 undo tablespaces
2023-07-17 22:18:40 0 [Note] InnoDB: Opened 2 undo tablespaces
2023-07-17 22:18:40 0 [Note] InnoDB: 128 rollback segments in 2 undo tablespaces are active.
2023-07-17 22:18:40 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-07-17 22:18:40 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-07-17 22:18:40 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-07-17 22:18:40 0 [Note] InnoDB: 10.6.12 started; log sequence number 51921; transaction id 14
2023-07-17 22:18:40 0 [Note] InnoDB: Loading buffer pool(s) from /tmp/databases/ib_buffer_pool
2023-07-17 22:18:40 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-07-17 22:18:40 0 [Note] InnoDB: Buffer pool(s) load completed at 230717 22:18:40
2023-07-17 22:18:40 0 [Note] Server socket created on IP: '0.0.0.0'.
2023-07-17 22:18:40 0 [Note] Server socket created on IP: '::'.
2023-07-17 22:18:40 0 [Warning] 'proxies_priv' entry '@% root@45207088-mariadb' ignored in --skip-name-resolve mode.
2023-07-17 22:18:40 0 [Note] mysqld: ready for connections.
Version: '10.6.12-MariaDB' socket: '/run/mysqld/mysqld.sock' port: 3306 MariaDB Server
[22:18:41] INFO: MariaDB started
[22:18:41] 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
sys.sys_config OK
[22:18:41] INFO: Ensuring internal database upgrades are performed
[22:18:41] INFO: Secure the installation
[22:18:41] INFO: Ensure databases exist
[22:18:41] INFO: Create database homeassistant
[22:18:41] INFO: Last known all databases content timestamp is 2023-07-17 22:17:42.646744676 +0200
[22:18:41] INFO: Importing all databases schema and data... (no connection is allowed until it is finished)
ERROR 1146 (42S02) at line 1: Table 'homeassistant.schema_changes' doesn't exist
s6-rc: warning: unable to start service mariadb-post: command exited 1
/run/s6/basedir/scripts/rc.init: warning: s6-rc failed to properly bring all the services up! Check your logs (in /run/uncaught-logs/current if you have in-container logging) for more information.
/run/s6/basedir/scripts/rc.init: fatal: stopping the container.
s6-rc: info: service mariadb-core: stopping
2023-07-17 22:18:41 0 [Note] mysqld (initiated by: unknown): Normal shutdown
2023-07-17 22:18:41 0 [Note] InnoDB: FTS optimize thread exiting.
2023-07-17 22:18:41 0 [Note] InnoDB: Starting shutdown...
2023-07-17 22:18:41 0 [Note] InnoDB: Dumping buffer pool(s) to /tmp/databases/ib_buffer_pool
2023-07-17 22:18:41 0 [Note] InnoDB: Restricted to 307 pages due to innodb_buf_pool_dump_pct=25
2023-07-17 22:18:41 0 [Note] InnoDB: Buffer pool(s) dump completed at 230717 22:18:41
2023-07-17 22:18:41 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2023-07-17 22:18:41 0 [Note] InnoDB: Shutdown completed; log sequence number 53015; transaction id 15
2023-07-17 22:18:41 0 [Note] mysqld: Shutdown complete
[22:18:41] INFO: Service mariadb exited with code 0 (by signal 0)
s6-rc: info: service mariadb-core successfully stopped
s6-rc: info: service mariadb-pre: stopping
s6-rc: info: service mariadb-pre successfully stopped
s6-rc: info: service cont: stopping
s6-rc: info: service cont successfully stopped
s6-rc: info: service legacy-cont-init: stopping
s6-rc: info: service legacy-cont-init successfully stopped
s6-rc: info: service fix-attrs: stopping
s6-rc: info: service base-addon-log-level: stopping
s6-rc: info: service base-addon-log-level successfully stopped
s6-rc: info: service fix-attrs successfully stopped
s6-rc: info: service base-addon-banner: stopping
s6-rc: info: service base-addon-banner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: stopping
s6-rc: info: service s6rc-oneshot-runner successfully stopped

In-memory MariaDB (2.6.1.0)

LOG:

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting
curl: (7) Failed to connect to supervisor port 80 after 0 ms: Couldn't connect to server
[16:18:30] ERROR: Something went wrong contacting the API
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
curl: (7) Failed to connect to supervisor port 80 after 0 ms: Couldn't connect to server
[16:18:30] ERROR: Something went wrong contacting the API
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service cont: starting
s6-rc: info: service cont successfully started
s6-rc: info: service mariadb-pre: starting
[16:18:31] INFO: Change tmpfs size to null
mount: mounting tmpfs on /tmp failed: Invalid argument
s6-rc: warning: unable to start service mariadb-pre: command exited 128
/run/s6/basedir/scripts/rc.init: warning: s6-rc failed to properly bring all the services up! Check your logs (in /run/uncaught-logs/current if you have in-container logging) for more information.
/run/s6/basedir/scripts/rc.init: fatal: stopping the container.
s6-rc: info: service cont: stopping
s6-rc: info: service cont successfully stopped
s6-rc: info: service legacy-cont-init: stopping
s6-rc: info: service legacy-cont-init successfully stopped
s6-rc: info: service fix-attrs: stopping
s6-rc: info: service base-addon-log-level: stopping
s6-rc: info: service fix-attrs successfully stopped
s6-rc: info: service base-addon-log-level successfully stopped
s6-rc: info: service base-addon-banner: stopping
s6-rc: info: service base-addon-banner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: stopping
s6-rc: info: service s6rc-oneshot-runner successfully stopped

OS: Debian GNU/Linux 12 (bookworm)
Linux kernel: 5.9.0-arm-64
Home Assistant Supervised: 2023.06.4
Agent: 1.5.1
Docker: 24.0.2

The HA log is full of with recorder errors. Old statistics values are inaccessible, though they are in the database.

2022-11-06 09:58:24.118 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb.IntegrityError) (1062, "Duplicate entry '1-2022-10-27 14:00:00.000000' for key 'ix_statistics_statistic_id_start'")
[SQL: INSERT INTO statistics (created, start, mean, min, max, last_reset, state, sum, metadata_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: (datetime.datetime(2022, 11, 6, 8, 58, 24, 114668, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 10, 27, 14, 0, tzinfo=datetime.timezone.utc), 23.599999999999998, 23.6, 23.6, None, None, None, 1)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
2022-11-06 09:58:24.136 WARNING (Recorder) [homeassistant.components.recorder.statistics] Blocked attempt to insert duplicated statistic rows, please report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+recorder%22
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
  self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
  cursor.execute(statement, parameters)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
  res = self._query(query)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
  db.query(q)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
  _mysql.connection.query(self, query)
MySQLdb.IntegrityError: (1062, "Duplicate entry '1-2022-10-27 14:00:00.000000' for key 'ix_statistics_statistic_id_start'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 101, in session_scope
  session.commit()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1451, in commit
  self._transaction.commit(_to_root=self.future)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 829, in commit
  self._prepare_impl()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
  self.session.flush()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3429, in flush
  self._flush(objects)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3568, in _flush
  with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
  compat.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
  raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3529, in _flush
  flush_context.execute()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
  rec.execute(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
  util.preloaded.orm_persistence.save_obj(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
  _emit_insert_statements(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1238, in _emit_insert_statements
  result = connection._execute_20(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
  return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
  return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
  ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
  self._handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
  util.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
  raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
  self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
  cursor.execute(statement, parameters)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
  res = self._query(query)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
  db.query(q)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
  _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (MySQLdb.IntegrityError) (1062, "Duplicate entry '1-2022-10-27 14:00:00.000000' for key 'ix_statistics_statistic_id_start'")
[SQL: INSERT INTO statistics (created, start, mean, min, max, last_reset, state, sum, metadata_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: (datetime.datetime(2022, 11, 6, 8, 58, 24, 114668, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 10, 27, 14, 0, tzinfo=datetime.timezone.utc), 23.599999999999998, 23.6, 23.6, None, None, None, 1)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

This is due to the add-on reports to HA that it is started before it completes the import of the previously exported data.

During HA start the statistics component creates new entries in the statistics_meta table (same values than the old, but with new id-s), and tries to modify the old id-s in the statistics table, and this will result in a unique constraint collision due to the parallel data import.

Previously the add-on waited to the end of the import, but this caused timeout errors in the HA startup process.

Quick workaround:

>ha core stop
>docker kill addon_45207088_mariadb

Note: use docker kill, NOT stop. With this, the database will be reloaded with the uncorrupted values.
Then wait until the add-on starts.

>ha ad log 45207088_mariadb
>ha core start

Supervisor log:

22-06-08 14:45:09 ERROR (MainThread) [supervisor.addons.addon] Pre-/Post backup command returned error code: 126
22-06-08 14:45:09 ERROR (MainThread) [supervisor.backups.backup] Can't create backup for 45207088_mariadb

Hi.

My recorder/log/history is broken and I've started seeing a log full of the below. I don't think I changed anything. Does anyone know what might be wrong?

2022-06-13 12:39:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 12:49:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 12:59:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 13:09:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 13:19:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 13:29:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 13:39:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 13:49:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 13:59:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 14:09:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 14:19:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 14:29:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 14:39:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 14:49:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 14:59:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 15:09:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 15:19:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 15:29:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs
2022-06-13 15:39:12 22 [Warning] mariadbd: Retry in 60 secs. Message reprinted in 600 secs

New tables got hard wired into recorder code in HA, like:

CREATE TABLE event_types (
	event_type_id INTEGER NOT NULL AUTO_INCREMENT, 
	event_type VARCHAR(64), 
	PRIMARY KEY (event_type_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

I have to rewrite queries on the fly, to delete ENGINE=InnoDB...

Until it's done, please install original maridb add-on or restore previous HA version 2023.03.x.

First Thanks for your work!

I encountered a problem after setting up your addon and restarting ha core.
In the logs it says "recorder cannot start".

I managed to trace the problem down to your addon using aria as default engine and recorder expects innodb (see https://www.home-assistant.io/integrations/recorder/ "The database engine must be InnoDB as MyIASM is not supported.")

Is there a specific reason of using aria? Or can i adjust ha such that it can work with aria?

Running:
Home Assistant OS 9.5 | core-2023.3.3
using all default settings as described in documentations

I use HA running in a docker container. Unfortunately, on this version add-on button is not available and I need a manual procedure to install MariaDB in-memory add-on.
Can you please provide it?

First Thanks for your work, had to replace sd card every year before this add-on.

Please consider adding additional warning in section Option: purge_statistics for users that use Energy dashboard.

There is weird behavior in Energy Dashboard when statistic have been purged. Energy usage graph pads values for the first month it has data for.

In attached screenshot:

• earliest available statistics are from march ( purge_statistics, period: monthly, keep_periods: 6)
• yearly energy consumption values are correct
• value for march is padded - should be around 200
• values for later months are correct

21-09-19 03:31:18 WARNING (MainThread) [supervisor.addons.validate] Add-on config 'snapshot_pre' is deprecated, 'backup_pre' should be used instead. Please report this to the maintainer of In-memory MariaDB