Home Assistant Datenbank MySQL vs. SQLite

Home Assistant verwendet als Datenbank SQLite. In SQLite werden alle Daten in eine einfache Datei gespeichert: home-assistant_v2.db. Ich hatte mit der Datenbank bis dato keine Probleme und bin mit der Performance von Home Assistant sehr zufrieden, dennoch wollte ich eine MySQL-Datenbank versuchen, denn zumindest theoretisch könnte MySQL eine bessere Performance erreichen. In der Praxis konnte ich aber keinen Unterschied feststellen. Laut der Herstellerseite wird SQLite sogar empfohlen:  

The default, and recommended, database engine is SQLite which does not require any configuration.

Quelle: www.home-assistant.io/integrations/recorder/

Wer dennoch MySQL verwenden will, kann die Konfiguration wie folgt anpassen:

docker-compose.yml

[+]
version: "3"
services:
  hass:
    image: homeassistant/home-assistant:stable
    container_name: home-assistant
    depends_on:
      - deconz
      - mysql
    environment:
      VIRTUAL_HOST: 'ha.domain.tld'
      VIRTUAL_PORT: '80'
      LETSENCRYPT_HOST: 'ha.domain.tld'
      LETSENCRYPT_EMAIL: 'admin@domain.tld'
    restart: always
    volumes:
      - ./ha:/config
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
    expose:
       - "80"
    ports: 
       - 8123:80
    networks:
            - default
            - backend
  mysql:
    image: 'mysql'
    container_name: ha-mysql
    environment:
        MYSQL_ROOT_PASSWORD: 'MyPassword'
        MYSQL_DATABASE: 'hass_db'
        MYSQL_USER: 'hass_user'
        MYSQL_PASSWORD: 'MyPassword'
    restart: always
    volumes:
        - './db:/var/lib/mysql'
        - './mysql.cnf:/etc/mysql/conf.d/mysql.cnf'
    healthcheck:
      test: ["CMD", "mysqladmin", "ping"]
    networks:
       - backend
  deconz:
    image: marthoc/deconz
    container_name: deconz
    environment:
      DECONZ_DEVICE: '/dev/ttyACM0'
      DECONZ_VNC_MODE: '1'
      DECONZ_VNC_PORT: '5900'
      DECONZ_VNC_PASSWORD: 'MyPassword4VNC'
    restart: always
    volumes:
      - ./deCONZ/:/root/.local/share/dresden-elektronik/deCONZ
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
    devices: 
      - /dev/ttyACM0
    ports:
       - 83:80
       - 5983:5900
    networks:
       - backend
networks:
  default:
    external:
      name: webproxy
  backend:
    external:
      name: smart-home

configuration.yml

...
recorder:
  db_url: mysql://hass_user:myPassword@ha-mysql/hass_db
...

Bestehende Einträge importieren: gescheitert 

Folgender Befehl soll alle Einträge von SQLite nach MYSQL Importieren:

user@server:/var/homeassistant-Folder$ docker run --rm -it -v $(pwd):/db keinos/sqlite3 sqlite3 /db/home-assistant_v2.db .dump | \
    sed -re 's/^PRAGMA .+OFF/SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0/' \
        -e 's/^CREATE INDEX .+//' \
        -e 's/^BEGIN TRANSACTION;\r$/SET autocommit=0;BEGIN;/' \
        -e '/CREATE TABLE/,/\);/ d' \
        -e 's/^INSERT INTO "([^"]+)"/INSERT INTO \1/' \
        -e 's/\\n/\n/g' | \
    perl -pe 'binmode STDOUT, ":utf8";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' | \
    docker exec -i ha-mysql mysql --default-character-set=utf8 -u hass_user -pMyPassword hass_db

Anfangs hat der Befehl relativ schnell abgebrochen, ein Blick in den keinos/sqlite3-Container mit: 

sqlite3 /db/home-assistant_v2.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/**** ERROR: (8) attempt to write a readonly database *****/
ROLLBACK; -- due to errors

Ursache: fehlende Schreibberechtigung im Root-Ordner von Home-Assistant

ERROR 1451 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails (`hass_db`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))
docker stop home-assistant
docker exec -it ha-mysql /bin/bash
mysql -uhass_user -pMypwd 

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('hass_db');
mysql> SET FOREIGN_KEY_CHECKS = 1;

Einen Schritt weiter, bin ich an diesem Fehler gescheitert:

ERROR 1062 (23000) at line 7250: Duplicate entry '1193928' for key 'events.PRIMARY'
                                                                                   write /dev/stdout: broken pipe

siehe auch: www.alexsilcock.net/notes/migrating-home-assistant-from-sqlite3-to-mysql-8-0/ und gist.github.com/seidler2547/93012edf3c7a2414ec1d9a8ebbc9c1a6

positive Bewertung({{pro_count}})
Beitrag bewerten:
{{percentage}} % positiv
negative Bewertung({{con_count}})

DANKE für deine Bewertung!


veröffentlicht am 06.11.2021 von Bernhard


Fragen / Kommentare


Wir verwenden Cookies, um Inhalte und Anzeigen zu personalisieren, Funktionen für soziale Medien anbieten zu können und die Zugriffe auf unsere Website zu analysieren. Außerdem geben wir Informationen zu Ihrer Nutzung unserer Website an unsere Partner für soziale Medien, Werbung und Analysen weiter. Details anzeigen.