DbLog-MySQL

Einleitung

Da bei der Inbetriebnahme und der Migration von MySQL bzw MariaDB-Datenbanken einige kleine Fallen auftreten können, gibt's hier ein paar Hinweise.

Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

db.conf

Anmerkung: MariaDB10 nutzt nicht Port 3306 sondern 3307. Dies ist zum Beispiel bei Synology NAS der Fall.

DbLog wird durch zwei verschiedene Einträge aktiviert/definiert. In einer Datei namens db.conf werden die Parameter für eine Verbindung zur Datenbank (host, username, password, etc.) hinterlegt. Diese Datei kann in einem beliebigen Verzeichnis angelegt werden. Für eine MariaDB-Datenbank sieht die db.conf folgendermaßen aus:

%dbconfig= (
    connection => "mysql:database=fhem;host=db;port=3306",
    user => "fhemuser",
    password => "fhempassword",
);

Im Verzeichnis contrib/dblog der FHEM-Installation befindet sich eine Beispielkonfiguration mit der Syntax für jeden unterstützen Datenbanktyp. Bei der Verwendung von MariaDB muss die MySQL-Datei verwendet werden. Es wird empfohlen, diese Datei zu kopieren und erst dann entsprechend zu bearbeiten. Am Besten kopiert man diese Datei in das FHEM Home Directory /opt/fhem/ und achtet auf die entsprechenden Rechte!

chown fhem:dialout /opt/fhem/db.conf

Tabellen

Die Datenbank ist relativ simpel gestaltet und besteht lediglich aus den folgenden beiden Tabellen:

• current
• history

DbLog ist auf eine feste Tabellenstruktur angewiesen. Man muss daher in seiner Datenbank eine Tabelle mit folgenden Spalten anlegen:

Spalte	Beschreibung (en)	Beschreibung (de)	Beispiel
TIMESTAMP	timestamp of event	Zeitstempel	2007-12-30 21:45:22
DEVICE	device name	Device-Name	Wetterstation
TYPE	device type	Device-Typ	KS300
EVENT	event specification as full string	Eventspezifikation als Text	humidity: 71 (%)
READING	name of reading extracted from event	Bezeichnung des Readings	humidity
VALUE	actual reading extracted from event	Wert des Readings	71
UNIT	unit extracted from event	Einheit des Readings	%

Im Verzeichnis contrib/dblog befindet sich das Script db_create_mysql.sql , welches eine neue Datenbank samt Tabellenstruktur anlegt. Ausserdem wird ein Benutzer "fhemuser" mit einem (zu ändernden) "fhempasswort" angelegt.

Primary Keys

current

Die Tabelle current enthält für jedes zu loggende Device lediglich den letzten Wert. Falls noch kein Wert geloggt wurde, ist diese Tabelle leer. Falls der Inhalt gelöscht wird, bauen sich die Daten automatisch wieder auf. Es gehen durch das löschen der Tabelle current keine Log-Informationen verloren. Der Inhalt wird aber u.a. für die Dropdown-Felder beim Plot-Editor verwendet.

Um doppelte Einträge in der Tabelle zu vermeiden, wurde die Möglichkeit geschaffen, Primary Keys zu definieren. Da in der Spalte READING u.U. bei verschiedenen Geräten gleiche Namen vorkommen können, sollte der Primary Key um den Gerätenamen erweitert werden. Der Primary Key sollte also aus DEVICE und READING bestehen. Um in der Datenbank fhem diesen PK zu setzen, kann folgender SQL Code verwendet werden:

ALTER TABLE `fhem`.`current` 
CHANGE COLUMN `DEVICE` `DEVICE` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
CHANGE COLUMN `READING` `READING` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
ADD PRIMARY KEY (`DEVICE`, `READING`);

history

Die Tabelle history enthält alle bisher geloggten Daten. Löschen in dieser Tabelle bedeutet automatisch Datenverlust (gewollt oder nicht ... ) Der Inhalt dieser Tabelle wird verwendet, um die Plots zu zeichnen oder Auswertungen mit DbRep anzufertigen

Um Probleme beim Import von cacheFiles zu vermeiden, kann in der Datenbank ein PK angelegt werden, welcher Timestamp, Device und Reading umfasst. Dadurch werden doppelte Einträge wirksam verhindert.

ALTER TABLE `fhem`.`history` 
CHANGE COLUMN `DEVICE` `DEVICE` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
CHANGE COLUMN `READING` `READING` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
ADD PRIMARY KEY (`TIMESTAMP`,`DEVICE`, `READING`);

Beispiel: Anlegen und Nutzung einer MariaDB-Datenbank

Anstatt nano kann jeder andere kompatible Editor verwendet werden. Weiterhin bitte beachten, dass die hier genannten Befehle teilweise root-Rechte voraussetzen. Entweder komplett als root arbeiten oder mittels sudo. In den nachfolgenden Beispielen wird daher stets sudo in Klammern geschrieben, dies bitte aber nicht so eingeben, sondern eben sudo su grundsätzlich voranstellen oder vor jedem Befehl sudo einsetzen.

Hinweis: im Folgenden ist "#" der Shell-Prompt und "mysql>" der MariaDB-Prompt innerhalb mysql, dieser kann mit exit verlassen werden.

Unter Ubuntu/debian:

# (sudo) apt-get update && apt-get install mariadb-server

Nach der Installation muss das Script

# (sudo) mysql_secure_installation

ausgeführt werden. Dieses legt ein root-Passwort fest, entfernt die anonymen User und entfernt die test-Datenbank. Empfohlen wird, alle Fragen mit Y zu beantworten.

Zum Test mit MariaDB verbinden:

# (sudo) mysql -u root -p
Enter password:
MariaDB [(none)]> exit

Sollte hier eine Fehlermeldung "Access denied for user 'root'@'localhost'" trotz richtigem Passwort erscheinen, muss man sich als root mit der Datenbank verbinden; also sudo mysql...

Jetzt die Tabellenstruktur anlegen. Hierfür kann die Datei /opt/fhem/contrib/dblog/db_create_mysql.sql als Vorlage verwendet und das Passwort und der Benutzername geändert werden.

cd /opt/fhem/contrib/dblog/
(sudo) nano db_create_mysql.sql

Dann wird die Datei eingelesen:

# (sudo) mysql -u root -p < db_create_mysql.sql
Enter password:
MariaDB [(none)]>  exit

Jetzt kann man den Zugang als fhemuser testen (eventuell muss der Befehl sudo mysql lauten):

# (sudo) mysql -p -u <fhemuser>
Enter password: <fhempassword>
MariaDB [(none)]>  show databases;

Das Ergebnis lautet

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| fhem               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Nun müsste eine Datenbank "fhem" angezeigt werden. Um die Tabellen zu sehen, kann man den folgenden Befehl eingeben:

MariaDB [(none)]> use fhem; show tables;

und das Ergebnis lautet

MariaDB [fhem]> show tables;
+----------------+
| Tables_in_fhem |
+----------------+
| current        |
| history        |
+----------------+
2 rows in set (0.001 sec), 

die die Tabellen current und history enthält.

Nun in der Datei db.conf den mysql-Block auskommentieren und ebenfalls Benutzername, Passwort UND HOST anpassen. Leider ist hier nichts standardmäßig eingestellt.

(sudo) nano /opt/fhem/contrib/dblog/db.conf

Jetzt kann unter FHEM ein DbLog-Device angelegt werden (mit dem beispiel wird alles geloggt:

define logdb DbLog ./contrib/dblog/db.conf .*:.*

Als State muss ein "connected" angezeigt werden.

Ein rereadcfg in FHEM stellt sicher, dass die neue Konfiguration übernommen wird - ein Neustart ist nicht erforderlich.

Nun kann die Funktion noch einmal überprüft werden:

 # mysql -u <fhemuser> -p
 Enter password: <fhempassword>
 mysql> use fhem;
 Database changed
 mysql> show tables;
 +----------------+
 | Tables_in_fhem |
 +----------------+
 | current        |
 | history        |
 +----------------+
 2 rows in set (0,00 sec)
 mysql> select * from history; # Achtung, kann sehr groß werden .... #

Beispiel: Abfragescript PHP/MySQL

Um eine schnelle Übersicht zu bekommen habe ich mir dieses Script geschrieben:

<?php $pdo = new PDO('mysql:host=localhost;dbname=fhem', 'fhemuser', 'fhempasswort');
echo '<h2>Tabelle Current</h1><br><table border="1">';
  echo "<tr><th>Anzahl</th><th>Name</th><th>Readings</th></tr>";
$sql = "SELECT COUNT(*), DEVICE, GROUP_CONCAT(DISTINCT READING ORDER BY READING DESC SEPARATOR '</li><li>') FROM current GROUP BY DEVICE;"; foreach ($pdo->query($sql) as
$row) {
  echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td><td><ol><li>" . $row[2] . "</li></ol></td></tr>";
}
echo "</table>";


echo '<h2>Tabelle History</h1><br><table border="1">';
  echo "<tr><th>Anzahl</th><th>Name</th></tr>";
$sql = "SELECT COUNT(*), DEVICE FROM history GROUP BY DEVICE;"; foreach ($pdo->query($sql) as
$row) {
  echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td></tr>";
}
echo "</table>";
?>

Bitte passt fhemuser und fhempasswort an. Das Ganze kommt dann nach /var/www/html/fhemdb.php und ist mit <IP>/fhemdb.php aufrufbar. Wenn ihr den 2. Block für die history Tabelle ausklammert oder entfernt läuft das Script viel schneller ab - klar die history Tabelle ist meist randvoll.

Bearbeitung von Datenbank-Einträgen

Dieser Abschnitt soll lediglich eine kleine Einführung in die Datenbank-Bearbeitung liefern. Für vertiefende Informationen sollte man sich grundsätzlich mit SQL beschäftigen. Eine umfassende und gut verständliche Anleitung zu SQL bietet bspw. w3schools.

Irgendwann wird der Fall eintreten, dass in der Datenbank Einträge drinstehen, die geändert oder gelöscht werden sollen (zB. fehlerhafte Sensor-Rückmeldungen, umbenannte Readings). In klassischen Log-Dateien würde man diese einfach bearbeiten und löschen/anpassen (wobei man aber tunlichst zuvor FHEM stoppt, um Datenfehler zu vermeiden). Eine Datenbank kann bearbeitet werden, ohne FHEM stoppen zu müssen.

Datenbanken kann man ohne weitere Hilfsmittel direkt von der Kommandozeile/Shell aus bearbeiten. Alternativ gibt es auch verschiedenste Tools (webbasiert oder als Applikation), die einen dabei unterstützen (Bsp. findet man u.a. hier). Für einfache Arbeiten reicht allerdings idR. Shell.

phpMyAdmin

Ein sehr beliebtes Tool ist phpMyAdmin.

# apt-get install phpmyadmin

Bei der Installation wird man nach einem Passwort für den User "phpMyAdmin" gefragt, der im weiteren Verlauf erstmal nutzlos ist. Nun kann man sich unter <meine-fhem-Domain>/phpMyAdmin mit der Seite verbinden. Zum einloggen kann nun der in der config-datei angegebene fhemuser mit dem fhempasswort verwendet werden, oder, sofern ein Passwort vergeben ist, auch den User root. Der Unterschied liegt darin, dass root *alle* Datenbanken sehen und ändern darf (was zum ändern von Datenbankeigenschaften sowie Benutzerkonten notwendig ist), während dem fhemuser nur die fhemdatenbank angezeigt wird(was für suchabfragen, deletes und inserts sowie Tabellenänderungen völlig ausreicht.

Datenbank migrieren

Eine schöne Anleitung zur Migration von SQLite zu MySQL/MariaDB mit Hilfe von DbRep findet sich hier: [1].

Hinweis: Wenn die SQLite-DB sehr groß wird, kann es sein, dass der oben beschriebene Weg nicht funktioniert (konkret war dies bei meiner 15 GB großen DB nicht möglich, der Prozess hat sich immer nach mehreren Stunden aufgehängt).

Nützliche Codeschnipsel

Anbei ein paar nützliche Codeschnipsel rund um DbLog

Dateigrösse mitloggen

Da die Datenbank ins Unermessliche wachsen kann, empfiehlt es sich - je nach Speicherplatz - ab einer bestimmten Grösse tätig zu werden. Dazu muss diese Grösse allerdings ermittelt werden. Zur Ermittlung der MariaDB-Größe kann wie hier beschrieben vorgegangen werden.

Links

• Beispiel das DbLog-Daten für SVG-Plots verwendet
• SVG-Plots von FileLog auf DbLog umstellen