DbLog: Unterschied zwischen den Versionen

Aus FHEMWiki
(Kap. "Fehler in Datenbank korrigieren" erstellt)
Zeile 375: Zeile 375:




== Fehler in Datenbank korrigieren ==
== Datenbank reparieren ==
Es kann immer wieder mal vorkommen, dass Datenbanken Fehler enthalten. Das muss im Alltag garnicht auffallen und auch nicht immer schlimm enden. Wenn man auf der SQL-Konsole aber bspw. eine Meldung <code>Error: database disk image is malformed</code> erhält, sollte man ein Reparatur vornehmen.
Es kann immer wieder mal vorkommen, dass Datenbanken Fehler enthalten. Das muss im Alltag garnicht auffallen und auch nicht immer schlimm enden. Wenn man auf der SQL-Konsole aber bspw. eine Meldung <code>Error: database disk image is malformed</code> erhält, sollte man ein Reparatur vornehmen.


Zeile 458: Zeile 458:
</li>
</li>
</ol>
</ol>


== Links ==
== Links ==
* [[Heizleistung_und_Gasverbrauch|Beispiel das DbLog-Daten für SVG-Plots verwendet]]
* [[Heizleistung_und_Gasverbrauch|Beispiel das DbLog-Daten für SVG-Plots verwendet]]
* [[SVG-Plots von FileLog auf DbLog umstellen]]
* [[SVG-Plots von FileLog auf DbLog umstellen]]

Version vom 16. Mai 2016, 10:29 Uhr

DbLog
Zweck / Funktion
Protokolliert Ereignisse in einer Datenbank
Allgemein
Typ Hilfsmodul
Details
Dokumentation EN / DE
Support (Forum) Automatisierung
Modulname 93_DbLog.pm
Ersteller tobiasfaust (Forum /Wiki)
Wichtig: sofern vorhanden, gilt im Zweifel immer die (englische) Beschreibung in der commandref!


Einleitung

Mit der Zeit entstehen im fhem recht umfangreiche Log-Daten für die verschiedensten konfigurierten Devices. Die übliche Einstiegs-Konfiguration sieht vor, dass die Logs als FileLog gespeichert werden - je nach Einstellung in wenigen sehr großen oder vielen kleineren Dateien. Der Datei-basierte Zugriff ist allerdings nicht wirklick performant und kann schnell zum Flaschenhals werden (z.B. bei der Darstellung von Graphen über einen längeren Zeitraum).

Alternativ kann Fhem die Log-Daten mittels DbLog in einer Datenbank speichern. Diese kann lokal als einfache SQLite- oder als zentrale Server-Datenbank (s.u.) gestaltet sein. Schon eine lokale einfache SQLite-Datenbank ist in der Regel deutlich performanter als File-basierte Logs.

Damit eine Datenbank-Nutzung möglich ist, müssen folgende Anpassungen gemacht werden:

  1. Erstellen einer entsprechenden Datenbank
  2. Konfiguration der Datenbank-Anbindung in FHEM
  3. Anpassen aller (oder einzelner) Konfigurationen von FileLog nach DbLog
  4. Ggf. Anpassen der gplot-Konfigurationen


Konfiguration

Datenbank-Anbindung mittels db.conf

DbLog wird durch 2 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 MySQL-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.

Konfiguration als Device

Das DbLog Device wird dann definiert mit

define <name> DbLog <configfilename> <regexp>

wobei <configfilename> dem Pfad zur zuvor angelegten db.conf entspricht. Ein Beispiel hierfür wäre:

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

Die Angabe von .*:.* bedeutet, dass sämtliche DeviceMessages (Messwerte, Batteriestatus, KeepAlives, etc.) in die Datenbank geschrieben werden. Dies führt u.U. dazu, dass die Datenbank auch mit vielen teils irrelevanten Werten gefüllt wird. Man kann daher die zu loggenden Werte einschränken, indem man genau angibt welche Werte übertragen werden sollen. Dies ist in #Finetuning des Loggings beschrieben.

Finetuning des Loggings

Bei der Konfiguration des Log-Devices werden die zu loggenden Daten definiert - in der einfachsten Form sieht das so aus: define logdb DbLog ./db.conf .*:.* . Die Angabe von .*:.* bedeutet, dass sämtliche DeviceMessages (Messwerte, Batteriestatus, KeepAlives, etc.) in die Datenbank geschrieben werden. Dies führt u.U. dazu, dass die Datenbank auch mit sehr vielen und teils nicht benötigten Werten gefüllt wird und schnell wächst. Die Datenbank ist zwar deutlich leistungsfähiger, was große Datenmengen angeht, Datensparsamkeit kann aber schnell sinnvoll werden...

Um das Log-Aufkommen einzugrenzen gibt es 2 Ansätze:

  • Einschränkung über den define-Eintrag
  • Einschränkung über DbLogExclude-Einträge der jeweiligen Devices

Einschränkung über den zentralen define-Eintrag

Man kann die zu loggenden Werte einschränken, indem man genau angibt welche Werte übertragen werden sollen. Die erste Wildcard, also das erste .*, entspricht dem in FHEM verwendeten Device-Namen. Die zweite Wildcard entspricht dem vom Device ausgegebenen zu loggenden Wert. Separiert werden beiden Angaben durch einen Doppelpunkt.

Ein Beispiel, um zwar alle definierten Devices zu erfassen, aber nur die Werte Temperatur, Ventilposition und Luftfeuchte in die Datenbank zu schreiben wäre:

define myDbLog DbLog ./db.conf .*:(temperature|valveposition|humidity).*

Einschränkung über die jeweiligen Devices

Man kann die zu loggenden Werte für einzelne Devices separat einschränken, ohne dies im zentralen define-Eintrag machen zu müssen. Dies kann interessant sein, wenn beispielsweise ein Device Fehlerwerte meldet, die uninteressant sind, oder es meldet unnötig häufig Werte - beides ist z.B. bei 1-wire-Temperatursensoren gerne der Fall.

Um das einzuschränken gibt es 2 Stellparameter, die als Attribute direkt zum jeweiligen Device konfiguriert werden:

  • DbLogExclude - definiert Werte, die nicht gelogt werden sollen
  • event-min-interval, event-on-change-reading und event-on-update-reading beeinflussen, wie häufig Werte geloggt werden (vgl. commandref)

Eine konkrete Konfiguration für einen sehr gesprächigen 1-wire-Temperatursensor könnte wie folgt aussehen: 

define EG_Balkon GPIO4 BUSMASTER
attr EG_Balkon DbLogExclude failures,T,85     # logge keine "failures", "T"-Werte und "85"-Werte (default-Werte, wenn keine Temperatur gelesen werden kann)
attr EG_Balkon event-on-change-reading state  # logge nur, wenn sich ein Wert ändert (wenn sich die Temperatur nicht ändert, logge das nicht)
attr EG_Balkon event-min-interval state:900   # logge spätestens alle 900sec = 15min
attr EG_Balkon event-on-update-reading .*     # logge alle Werte, die aktualisiert werden

attr <1-Wire-Device vom Typ OWTHERM oder OWSWITCH> DbLogExclude data.*      # verhindert das Logging der state-Eintragungen

Eine in diesem Beitrag vorgestellte Strategie zur Vermeidung unnötigen Loggings ist, dass bei der Definition von Devices durch das nachfolgende notify automatisch ein DbLogExclude für alle Werte (.*) des Devices zugewiesen wird und dies nur bei Interesse an geloggten Werten gelöscht bzw. angepasst wird: define nDbLogExclude notify global:DEFINED.* attr $EVTPART1 DbLogExclude .*


Datenbank

Unterstützte Datenbanksysteme (Auswahl):

  • Sqlite
  • MySQL
  • PostGreSql

Tabellen

  • current
  • history

Tabellenlayout

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 %

Die Vorlagen zur Anlage von Tabellen und Indizes sind für jeden unterstützten Datenbanktyp im Verzeichnis contrib/dblog der FHEM-Installation, oder hier zu finden: Link. Das MySQL-Skript (db_create_mysql.sql) legt eine neue Datenbank, das PostGres-Skript (db_create_postgresql.sql) ein neues Schema mit Namen "fhem" an.


Anpassen der gplot-Konfigurationen

Die meisten gplot-Konfigurationen sind bisher lediglich auf FileLog-Konfigurationen ausgelegt. Deshalb müssen sie für die Verwendung mit DbLog angepasst werden. Glücklicherweise beschränkt sich dies auf die reinen FileLog-Zeilen - es müssen die DbLog-Äquivalente hinzugefügt werden. Die FileLog-Einträge müssen zwar nicht gelöscht werden, wenn man aber FileLog und DbLog parallel betreibt, sollte man getrennte gplot-Dateien für beide Logging-Typen haben um Auswertungsprobleme erkennen zu können.

Für die fht.gplot Konfiguration sähe die Anpassung wie folgt aus (lediglich die vier DbLog-Zeilen wurden hinzugefügt): 

# Created by FHEM/98_SVG.pm, 2014-12-25 21:53:30
set terminal png transparent size <SIZE> crop
set output '<OUT>.png'
set xdata time
set timefmt "%Y-%m-%d_%H:%M:%S"
set xlabel " "
set title '<L1>'
set ytics nomirror
set y2tics 
set grid y2tics
set ylabel "Actuator/Window (%)"
set y2label "Temperature in C"
set yrange 0:100
set y2range 5:25

#FileLog 4:.measured-temp\x3a:0:
#FileLog 4:.actuator\x3a:0:int
#FileLog 4:.desired-temp::
#FileLog 4:.window\x3a::

#DbLog <SPEC1>:.measured-temp:0:
#DbLog <SPEC1>:.actuator:0:int
#DbLog <SPEC1>:.desired-temp::
#DbLog <SPEC1>:.window::

plot "<IN>" using 1:2 axes x1y2 title 'Measured temperature' ls l0 lw 1 with lines,\
     "<IN>" using 1:2 axes x1y1 title 'Actuator (%)' ls l1 lw 1 with lines,\
     "<IN>" using 1:2 axes x1y2 title 'Desired Temperature' ls l2 lw 1 with steps,\
     "<IN>" using 1:2 axes x1y1 title 'Window' ls l3 lw 1 with steps


Beispiel: Anlegen und Nutzung einer SQLite-Datenbank

Im folgenden wird eine lokale SQLite-Datenbank auf einen Ubuntu-System angelegt (nach Quelle: http://www.tatsch-it.de/fhem-dblog/)

  1. Installation von SQLite: 
    sudo aptitude install sqlite3 libdbi-perl libdbd-sqlite3-perl
  2. Anlegen der SQLite-Datenbank fhem.db (öffnet auch direkt eine SQL-Kommandozeile): 
    sudo sqlite3 /opt/fhem/fhem.db

    In der geöffneten SQL-Kommandozeile eingeben: 

    CREATE TABLE 'history' (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));
CREATE TABLE 'current' (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));

    Die Kommandozeile verlässt man mit .exit.

  3. Anpassen des Besitzers und der Rechte der Datenbank-Datei: 
    sudo chown fhem /opt/fhem/fhem.db
sudo chmod 666 /opt/fhem/fhem.db
  4. Datenbank-Anbindung des FHEM konfigurieren: 
    sudo nano /opt/fhem/db.conf

    Inhalt: 

    %dbconfig= (
  connection => "SQLite:dbname=/opt/fhem/fhem.db",
  user => "",
  password => ""
);
  5. Logging des FHEM auf die Datenbank konfigurieren: (hier sind nur die Anpassungen aufgeführt) 
    sudo nano /opt/fhem/fhem.cfg
    ...
attr global userattr DbLogExclude ...  # erlaubt es einzelne Einträge nicht zu loggen
...
define logdb DbLog ./db.conf .*:.*     # logt alle(!) auflaufenden Events aller Konfigurationen
...

    Da durch diese define-Definition alle auflaufenden Events gelogt werden, müssen keine weiteren Anpassungen in der Konfiguration gemacht werden. Die FileLog-Einträge können bedenkenlos bestehen bleiben - dann wird in Datenbank und FileLog gelogt und man verliert keine Daten, falls etwas nicht klappt. Wenn alles wie geplant läuft, können die FileLog-Definitionen gelöscht werden (ebenso die Log-Dateien). Ebenso können die zu loggenden Daten später eingegrenzt werden (s. #Finetuning des Loggings).

  6. FHEM neu starten: 
    sudo service fhem stop
sudo service fhem start
  7. Kontrollieren, ob Logs in die Datenbank geschrieben werden: 
    sudo sqlite3 /opt/fhem/fhem.db

    In der geöffneten SQL-Kommandozeile eingeben: 

    select * from history order by TIMESTAMP;       # dies gibt alle(!) Logs chronologisch aus (kann nach längerem Betrieb recht lange dauern)

    Die Kommandozeile verlässt man mit .exit.

  8. Anpassung der glot-Dateien: siehe #Anpassen der gplot-Konfigurationen


Beispiel: Anlegen und Nutzung einer Mysql-Datenbank

Unter Ubuntu: 

apt-get install mysql-server mysql-client

ob man die Pakete 

apt-get install libdbd-mysql libdbi1 libdbd-mysql-perl

auch installieren muss, weiss ich nicht, bei mir sind sie drin

Bei der Installation sollte man aus Sicherheitsgründen ein Passwort für den mysql-root vergeben, wenn man nicht sogar ganz den Login verbietet.


nun zu mysql verbinden: 

mysql -p -u root
Enter password:

Jetzt die Tabellenstruktur anlegen. Hierfür kann nach Bedarf in der Datei /opt/fhem/contrib/dblog/db_create_mysql.sql das Passwort und der Benutzername geändert werden. Dann wird die Datei eingelesen: 

#mysql -u root -p < db_create_mysql.sql

Jetzt kann man den Zugang testen: 

#mysql -p -u <fhemuser>
Enter password: <fhempassword>
mysql> show databases;

Nun müsste eine Datenbank "fhem" angezeigt werden, 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 nicht standardmäßig localhost eingestellt. 

vim /opt/fhem/contrib/dblog/db.conf

Dann Fhem neustarten/rereadcfg (Wenns jemand sicher weiss, bitte ändern) halt dass die neue Config übernommen wird. Jetzt kann ein DbLog-Device angelegt werden: 

define logdb DbLog ./contrib/dblog/db.conf .*:.* # loggt ALLES!

Als State muss ein "connected" angezeigt werden. 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 current; # Achtung, kann sehr groß werden .... #


Integration von DBLog in eigene Module

Bereitstellung der UNITS

Mit der DbLog_SplitFn kann der Modulautor selbst festlegen, wie die Events des Moduls in die Bestandteile Reading/Value/Unit zerlegt werden um ein korrektes Logging per DbLog zu gewährleisten. Weitere Informationen siehe hier: DevelopmentModuleIntro#X_DbLog_splitFn


Bekannte Probleme

Beim Anlegen der Datenbank per script wird die Value-Spalte nur als Varchar(32) definiert. Dieses kann dazu führen, dass besonders lange Readings (z.b. vom Modul sysmon) abgeschnitten werden und nicht in der Datenbank landen. Dieses lässt sich leicht beheben, indem man die Spalte auf Varchar(64) ändert (siehe auch diesen Forenbeitrag).


Bearbeitung von Datenbank-Einträgen

Info blue.png
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 den fhem ausmacht um Datenfehler zu vermeiden). Eine Datenbank kann bearbeitet werden ohne den fhem abschalten zu müssen.

Datenbanken kann man ohne weiter 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.


SQLite-Datenbanken

Öffnen der DB unter Linux:

(Es werden Schreibrechte benötigt,ohne kann man die DB zwar öffnen, aber nichts machen) 

sudo sqlite3 fhem.db

Dadurch öffnet sich ein SQL-Konsole, auf der alle weiteren Befehle ausgeführt werden.

Schliessen der DB: 

sqlite> .exit


Hilfe anzeigen: 

sqlite> .help


Alle Tabellen anzeigen: 

sqlite> .tables


Das Schema der DB anzeigen:

(vgl. oben DbLog#Datenbanken und DbLog#Beispiel: Anlegen und Nutzung einer SQLite-Datenbank) 

sqlite> .schema


Alle Eintäge anzeigen:

Die Einträge liegen alle in der Tabelle "History".

Ganz wichtig ist immer das ";" am Ende Zeile (bei allen Kommandos, die nicht mit einem "." anfangen). Wenn es vergessen wurde zeigt die Konsole solange neue Zeilen bis ein ";" eingegeben wird. So kann ein Befehl auch bequem über mehrere Zeilen geschrieben werden. 

sqlite> select * from HISTORY;

Dies kann sehr lange dauern und kann ggf. mit STRG-C abgebrochen werden.


Alle Einträge eines Geräts anzeigen:

In where-Statements werden Strings in einfache Anführungsstriche gesetzt, Zahlen nicht. 

sqlite> select * from HISTORY where DEVICE='Pollenflug';


Alle Einträge eines Readings eines Geräts anzeigen: 

sqlite> select * from HISTORY where DEVICE='Pollenflug' and READING='Graeser';


Alle Einträge eines bestimmten Wertes eines Readings eines Geräts anzeigen: 

sqlite> select * from HISTORY where DEVICE='Pollenflug' and READING='Graeser' and VALUE>1;


LÖSCHEN aller Einträge eines bestimmten Wertes eines Readings eines Geräts anzeigen:

Achtung: Löschen kann nicht rückgängig gemacht werden!! Also IMMER erst die entsprechenden SELECT-Statements solange verfeinern bis wirklich nur die gewünschten Einträge angezeigt werden. Dann das select * durch delete ersetzen. 

sqlite> delete from HISTORY where DEVICE='Pollenflug' and READING='Graeser' and VALUE>1;


Datenbank reparieren

Es kann immer wieder mal vorkommen, dass Datenbanken Fehler enthalten. Das muss im Alltag garnicht auffallen und auch nicht immer schlimm enden. Wenn man auf der SQL-Konsole aber bspw. eine Meldung Error: database disk image is malformed erhält, sollte man ein Reparatur vornehmen.

SQLite-Datenbanken

Die folgenden Schritte beschreiben, wie man eine SQLite-DB reparieren kann (Quelle: [1]):

  1. DB öffnen: 
    sudo sqlite3 fhem.db
  2. Integritäts-Check durchführen: 
    sqlite> pragma integrity_check;

    Kommt hier ein "ok" ist die DB gesund. Ansonsten erscheint etwas wie 

    *** in database main ***
On tree page 118786 cell 1: Rowid 75 out of order (previous was 816660)
On tree page 118786 cell 4: Rowid 815704 out of order (previous was 816727)
Corruption detected in cell 0 on page 118786
Multiple uses for byte 132 of page 118786
...
  3. Datenbank-Dump erstellen (Export gesamten DB in die Datei "dump_all_20160516_1043.sql") und DB verlassen: 
    sqlite> .mode insert
sqlite> .output dump_all_20160516_1043.sql
sqlite> .dump
sqlite> .exit
  4. Neue Datenbank erstellen und den Dump einlesen, Integritäts-Check machen und verlassen: 
    sudo sqlite3 fhem-neu.db
    sqlite> .read dump_all_20160516_1043.sql
sqlite> pragma integrity_check;
ok
sqlite> .exit
  5. Spätestens jetzt fhem stoppen: 
    sudo service fhem stop
  6. Alte DB sichern und neue aktivieren: 
    sudo mv fhem.db fhem.db.sv_20160516
sudo mv fhem-neu.db fhem.db
  7. Kontrollieren, dass die neue DB die gleichen Rechte wie die alte DB hat (und ggf. korrigieren): 
    ~/fhem$ ls -lha
insgesamt 6,3G
drwxr-xr-x 12 fhem root    4,0K Mai 16 11:07 .
drwxr-xr-x  4 root root    4,0K Dez 25 17:50 ..
...
-rw-r--r--  1 root root    1,4G Mai 16 11:04 fhem.db
-rw-r--r--  1 fhem root    2,6G Mai 16 10:59 fhem.db.sv_20160516
...

~/fhem$ sudo chown fhem:root fhem.db

~/fhem$ ls -lha
insgesamt 6,3G
drwxr-xr-x 12 fhem root    4,0K Mai 16 11:07 .
drwxr-xr-x  4 root root    4,0K Dez 25 17:50 ..
...
-rw-r--r--  1 fhem root    1,4G Mai 16 11:04 fhem.db
-rw-r--r--  1 fhem root    2,6G Mai 16 10:59 fhem.db.sv_20160516
...
  8. fhem wieder starten (und natürlich kontrollieren): 
    sudo service fhem start

Links

Abgerufen von „http://wiki.fhem.de/w/index.php?title=DbLog&oldid=15345“