Schlagwort-Archive: MySQL

BitNotice #134 – Volkszähler-Daten mit Grafana visualisieren

BitNotice #134 - Volkszähler-Daten mit Grafana visualisieren

(77 MB) 00:17:25

2018-09-12 17:30 🛈

Das Projekt Volkszähler hatte ich ja schon öfter gezeigt: Mit diesem lassen sich über längere Zeiträume Messwerte sammeln und darstellen. Auch wenn es prinzipiell funktioniert: Inzwischen gibt es Konkurrenzprojekte, welche interessante Funktionen haben. Eines davor ist Grafana – eine reine Messwertvisualisierung, welche durch einfache Bedienung und großen Funktionsumfang heraussticht. In der aktuellen Version können auch Daten auf MySQL-Tabellen angezeigt werden – also jene Datenbank, die auch der „VZ“ nutzt. Verheiraten wir das Ganze doch einfach und nutzen Grafana zum Anzeigen der VZ-Daten.

Code

Using Channel-IDs (that’s not UUID)

SELECT
  timestamp/1000 as time_sec, 
  data.value as value, 
  properties.value as metric
FROM data
  LEFT JOIN properties ON (properties.entity_id = data.channel_id)
  LEFT JOIN entities ON (entities.id = data.channel_id)
WHERE 
  (channel_id = 1 || channel_id = 2 || channel_id = 3)  AND 
  (timestamp/1000) >= $__unixEpochFrom() AND 
  (timestamp/1000) <= $__unixEpochTo() AND
  properties.pkey = 'title'
ORDER BY timestamp ASC

Using channel names

SELECT
  timestamp/1000 as time_sec, 
  data.value as value, 
  properties.value as metric
FROM data
  LEFT JOIN properties ON (properties.entity_id = data.channel_id)
  LEFT JOIN entities ON (entities.id = data.channel_id)
WHERE 
  (properties.value = "Flur" || properties.value = "Werkstatt" ) AND
  (timestamp/1000) >= $__unixEpochFrom() AND 
  (timestamp/1000) <= $__unixEpochTo() AND
  properties.pkey = 'title'
ORDER BY timestamp ASC

Using UUID

SELECT
  timestamp/1000 as time_sec, 
  data.value as value, 
  properties.value as metric
FROM data
  LEFT JOIN properties ON (properties.entity_id = data.channel_id)
  LEFT JOIN entities ON (entities.id = data.channel_id)
WHERE 
  (uuid = "12345678-1234-1234-1234-1234567890ab" || uuid = "12345678-1234-1234-1234-1234567890ac" ) AND
  (timestamp/1000) >= $__unixEpochFrom() AND 
  (timestamp/1000) <= $__unixEpochTo() AND
  properties.pkey = 'title'
ORDER BY timestamp ASC

MySQL: Wuchernde Log-Dateien (mysqld-bin.xxx) zähmen

Je nach Konfiguration können MySQL-Server bei vielen Anfragen die Festplatte mit Binärdateien des Namens mysqld-bin.(zahl) füllen. Hintergrund: In diesen Dateien zeichnet der Server alle Anfragen auf, welche den Datenbestand verändern (also INSERT, UPDATE, etc). Das kann z.B. zur Datenrettung bei Abstürzen oder zur Replikation bei der Verwendung mehrerer Server hilfreich sein. Hat man jedoch nur beschränkt Speicherplatz zur Verfügung können diese Dateien zum Problem werden.

Log einmalig löschen

Benötigt man nur einmalig den Speicherplatz kann man sich mit einem passenden MySQL-Befehl behelfen. Hierzu schaut man sich die durchnummerierten Log-Dateien an und überlegt sich einen Zeitpunkt, der als neuen Startpunkt dienen soll. Transaktionen vor diesem Zeitpunkt können bei Ausfällen ggf. Beschädigt werden, daher empfielt es sich einen Zeitpunkt zu wählen, der kurz vor dem letzten Backup liegt. Der Befehl lautet dann z.B.:

PURGE BINARY LOGS BEFORE '2015-12-08 12:34:56';

Log abschalten

Duch Abschaltung der Log-Funktion ist das Problem schnell und dauerhaft behoben, allerdings verliert man die Möglichkeit mehrere Server zu verwenden und kann unter Umständen bei Ausfällen (Absturz, Stromausfall, etc) Daten verlieren. Zum Abschalten öffnet man die Konfigurationsdatei im Editor seiner Wahl – diese findet sich üblicherweise unter /etc/mysql/my.cnf oder /etc/my.cnf. Im Abschnitt [mysqld] sollte ein Eintrag „log-bin“ zu finden sein, eventuell noch mit einer Zahl oder einem Dateinamen dahinter. Diesen inaktiviert man durch Vorstellen eines Raute-Zeichens:

[mysqld]
...
#log-bin

Nachdem der MySQL-Server neu gestartet wurde legt er keine weiteren Dateien an. Alte Einträge müssen ggf. wie weiter oben beschrieben entfernt werden.

Loggröße einschränken

Ein guter Mittelweg ist es meist Logs anzulegen, um alle damit verbundenen Optionen nutzen zu können, das Alter jedoch zu beschränken. Ich mache täglich eine Sicherung, damit ist sichergestellt, dass ich Daten mit >24h nicht aus dem Log widerherstellen muss. Um bei Störungen des Backup jedoch etwas Puffer zu erhalten lasse ich Logs 3 Tage stehen. Hierzu nutzt man die oben beschriebene Konfigurationsdatei und sucht im Abschnitt [mysqld] nach dem Eintrag „expire_logs_days“. Ist er vorhanden kann er passend modifiziert werden, andernfalls fügt man die Zeile in der Nähe von log-bin ein. Um Logs nach 3 Tagen zu löschen lautet die Konfiguration:

[mysqld]
...
log-bin
expire_logs_days = 3

Nach einem Neustart des MySQL-Servers werden ältere Logs automatisch gelöscht, zukünftig werden sie nur noch 3 Tage aufbewahrt.

Durch die Änderung konnte ich „mal schnell“ mehr als 10GB an alten Logs vom betroffenen Server kratzen – bei einem embedded-System mit 16GB Speicher nicht grade unerheblich.

MySQL: Anlegen neuer Benutzer schlägt fehl

Dann nur noch schnell einen neuen User für die Datenbank anlegen…oder auch nicht.

Unknown column 'plugin' in 'mysql.user'

Ursache ist, dass man zuvor ein MySQL-Update verpennt hat und lediglich die Binarys, nicht jedoch die Systemdatenbanken aktualisiert hat. Nachholen lässt sich dies auf der Konsole mit einem schnellen

mysql_upgrade -u root -p

Hierdurch werden die Tabellen auf den aktuellen Stand gebracht und das anlegen neuer Nutzer sollte wieder problemlos möglich sein.

MySQL/MariaDB unter Linux mit ZFS: Operating system error number 22 in a file operation

Nachdem LVM eine Qual ist wenn es um die Konfiguration von RAID geht (anm: Natives LVM-Raid, nicht md) und btrfs auf mehreren meiner Systeme gerne mal Dateien vergisst habt ich mich nach langer Zeit nochmal an ZFS gewagt. Die Funktionspalette ist beeindruckend: Integriertes RAID, integriertes Volumemanagement, Subvolumes, Kompression, Quota, etc. Alles natürlich verzahnt, sodass ein RAID-Rebuild wirklich nur belegte Dateisystembereiche rekonstruiert. Einzig die statische RAID-Konfiguration ist wenn man von LVM kommt etwas bedauerlich. Ansonsten scheint die Linux-Portierung des eigentlich von Solaris stammenden Systems inzwischen durchaus stabil.

Genug geredet, eigentliches Thema: MySQL unter ZFS. Als guter Einstieg sollte man hier einen Blick auf die Arch-Wiki werfen, welche einen Blick auf die Blockgrößen und Cache-Eigenheiten wirft. Doch egal wie: MySQL bzw. MariaDB haben noch eine Gemeinheit im Paket: Direct IO.

Da MySQL ein eigenes Caching implementiert versucht es über O_DIRECT die Dateicaches des Betriebssystems zu umgehen. Im Falle von ZFS ist dies durch den Aufbau nicht sonderlich hilfreich und wird durch den Linux-Treiber nicht unterstützt. Während die meisten Programme automatisch auf klassisches IO zurückfallen verabschiedet sich MySQL mit folgenden Log-Einträgen:

141019 18:07:42 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‚Invalid argument‘.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File name ./ib_logfile0
InnoDB: File operation call: ‚aio write‘.
InnoDB: Cannot continue operation.

Um das Problem zu beheben kann man die Datenbank über /etc/mysql/my.cnf in den klassischen IO-Modus zwingen, hierzu unter [mysqld] folgenden Eintrag ändern bzw. hinzufügen:

innodb_use_native_aio=0

im Anschluss sollte die Datenbank wieder wie gewohnt starten. Hinweis: Fehlender Direct-IO bringt auch Probleme mit KVM/LibVirt, auch hier sind ggf. Anpassungen notwendig.

MySQL-Fehler: XMBC/OpenELEC lässt keine Änderungen an der Filmdatenbank zu

Narf? Muss das sein? Beim automatischen Import meines neu sortierten TV-Aufnahmen-Speichers ist in XMBC bzw. OpenELEC so einiges nicht ganz da gelandet, wo es sollte. A Clockwork Orange von 2013? Guess not.

Keine große Sache, oder? Einfach manuell aktualisieren und den richtigen Eintrag auswählen, schon wird alles aktualisiert, so weit die Theorie. In der Praxis landete ich wieder auf der selben, falschen Detailansicht.

Schuld war meine MySQL-Datenbank, diese liegt – da ich mehrere XMBCs betreibe – auf einem zentralen Server. In der Log-Datei (/storage/.xbmc/temp/xbmc.log) fand sich folgende Zeile:

ERROR: SQL: Undefined MySQL error: Code (1548)
Query: delete from movie where idMovie=146
ERROR: DeleteMovie failed

Hm – strange, also auf in phpMyAdmin und nachgeschaut. Ein Film mit idMovie=146 existiert, so weit so gut, aber auch hier funktioniert der Delete nicht und vermeldet:

Cannot load from mysql.proc. The table is probably corrupted

Schuld sind die integrierten Trigger/Funktionen/… der XMBC-Datenbank in Kombination mit einem verpennten Update. Der MySQL-Server selbst wurde eine Version hochgezogen, die Datenbanken aber nicht aktualisiert. Ein einfaches

mysql_upgrade

(ggf. mit -uroot -p) bringt alles auf den neuesten Stand und die MediaDB kann wieder bearbeitet werden.

Schei? encoding – MySQL vs. Charsets

Encoding ist ja schon was feines – und vorallem etwas, dass offenbar überall Probleme macht. Alleine um einen Überblick über den heutigen Tag zu geben:

Eventim begrüßt mich mit

Lieber eventim.de-Kunde,

Sie haben sich für den[….]

ein Telekom-Mitarbeiter verabschiedet sich als

J?rgen L[…]

(Ja, die Formatierung ist original aus der Outlook-Mail)

und im gerade eingetroffenen Chatlog sehe ich auch nur &uuml;’s

Aber auch mich hats Heute erwischt: MySQL und Umlaute waren schon immer eine schlechte Idee. Beim Umzug auf den neuen Server wieder der alte K(r)ampf: Sonderzeichen die sich bemühen wirklich sonderbar zu sein. Egal welcher Exportcharset, egal welcher Importcharset – egal ob Konsole oder phpMyAdmin, sinnvolle Sonderzeichen waren der Datenbank nicht zu entlocken. Abhilfe schaffte die Software MySQLDumper, welche mir im SysCP-Chat empfohlen wurde. Dort die UTF8-Datenbank als latin1 exportieren und auf dem neuen Server das selbe File in phpMyAdmin als UTF8 importiert – logisch, oder? Da passt daer MySQL-Ikea-Vergleich der aktuellen Datenschleuder irgendwie…