Kategorie-Archiv: MySQL

Quersumme berechnen

Mitunter trifft man auf den Fall eine Quersumme, beispielsweise in Form einer Prüfziffer, berechnen zu müssen. In PHP ist das ein einfacher Vorgang, der ungefähr so aussehen kann:

var_dump(array_sum(str_split('123')));

Ein ziemlich übersichtlicher Einzeiler, der hinsichtlich Parameterprüfung sicher noch verfeinert und in eine Funktion gepackt werden kann. In MySQL ist eine Umsetzung schwieriger. Die String-Funktionen bieten keinen Pendant zu PHP str_split() an. Wer kurz  im Netz sucht, erhält in der Regel Lösungsansätze, die auf Stored Procedures und Schleifen aufbauen. Diese möchte ich an dieser Stelle zwar nicht schlecht reden. Dennoch wäre eine  Lösung wünschenswert, die einfach in einen Select eingebaut werden kann. Quersumme berechnen weiterlesen

Tückische Autoincrement-Werte in MySQL

Autoinkrementwerte gehören für Primärschlüsselfelder in Tabellen wie der Deckel zum Topf. Es geht zwar auch ohne – aber i.d.R. ist die Nutzung sinnvoll. Eindeutige Identifier sind das A und O jeglicher Referenzen zwischen Tabellen. Deshalb ist es auch wenig verwunderlich, wenn man eben auf diese Eindeutigkeit großen Wert legt.

Leider scheint MySQL die Wichtigkeit der Autoinkrementwerte nicht allzu ernst zu nehmen, denn jene Werte werden nicht etwa zusammen mit der Tabellendefinition auf der Festplatte abgelegt, sondern im Arbeitsspeicher. Damit lässt sich leicht verständlich nachvollziehen, was geschehen mag, wenn der Arbeitsspeicher entweder durch einen plötzlichen Systemausfall, wie beispielsweise bei einer Kernelpanic, oder aber auch bei einem geplanten Neustart aufgrund einer Software-Aktualisierung, plötzlich geleert wird:

Der letzte Autoinkrement-Wert kann verloren gehen!

Genauer gesagt wird bei einer gefüllten Tabelle (mind. ein Datensatz) ein

SELECT MAX(auto_increment_col) FROM tableXY FOR UPDATE;

ausgeführt und bei einer leeren Tabelle, der Autoinkrement-Wert auf 1 gesetzt (siehe [1]). Unabhängig davon, ob bereits einmal Datensätze und somit eindeutige Schlüssel in der Tabelle existiert haben.
„Was soll’s?“ wird sich mancheiner denken. Nun, wie sieht es aber aus, wenn vorher Daten aus der Tabelle gelöscht wurden? Plötzlich entstehen nach dem Neustart die gleichen IDs noch einmal, die bereits vor dem Neustart generiert wurden. Das kann zu Problemen führen, wie wir im weiteren Textverlauf noch genauer analysieren werden.

Ein Beispiel zum Nachvollziehen:

mysql> CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO A VALUES();
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO A VALUES();
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE TABLE A\G
*************************** 1. row ******************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Es wurde eine Tabelle mit einem Autoinkrementfeld namens „id“ erstellt und zwei Datensätze wurden eingefügt. Die Abfrage des nächsten Autoinkrement-Wertes über SHOW CREATE TABLE liefert demnach den Wert drei. Nun löschen wir den letzten Datensatz:

mysql> DELETE FROM A WHERE id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> show create table A\G
*************************** 1. row ******************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Der Autoinkrement-Wert bleibt nach dieser Aktion stabil und verharrt auf dem Wert drei. Führt man jedoch einen Neustart des Servers durch, indem man beispielsweise das Kommando

sudo service mysql restart

unter Ubuntu nutzt, sieht das Resultant anders aus:

mysql> show create table A\G
*************************** 1. row ******************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Urplötzlich ist von zwei die Rede. Gleiches gilt übrigens, wenn der Truncate-Befehl genutzt wird:

mysql> truncate A;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table A\G
*************************** 1. row ******
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Für MyISAM-Tabellen hingegen scheint das Problem nicht zu existieren.

Die Gefahren

Wann kann das besagte Verhalten zum Problem werden? I.d.R. kommt dies beim Archivieren von Daten vor. Man kopiert die Datensätze inkl. der Schlüssel in eine andere Tabelle. Wird eine ID nach einem Neustart erneut vergeben, schlägt die nächste Archivierung z.B. mit einem „Duplicate Key Entry“ fehl.

Die Lösung

Man kann hier sicherlich nicht von der einen Lösung sprechen. Es gibt hierzu verschiedene Ansätze und jeder Betroffene mag es auf seine eigene Weise lösen. Eine Variante wäre sicher, statt die Daten in eine andere Tabelle zu verschieben, sie einfach über ein zusätzliches Feld als „deleted“ zu markieren. Damit ist die Archivierung aber im Prinzip dahin und der Vorteil kleiner, schneller Tabellen nicht mehr gegeben.
Eine andere Variante ist, den Primärschlüssel beim Archivieren einfach nicht mit zu kopieren, sondern in der Archivtabelle neue zu vergeben. Dies ist jedoch keine besonders elegante Lösung, insbesondere dann, wenn irgendwo in der Datenbank doch noch „weiche“ Verweise (also ohne DB-Foreign-Key) auf die Schlüssel existieren.

Vielversprechender scheint es zu sein, den Autoinkrement-Wert automatisch durch eine Stored-Procedure neu setzen zu lassen:

DELIMITER $$
DROP PROCEDURE IF EXISTS resetAutoIncrement;
CREATE PROCEDURE resetAutoIncrement()
BEGIN
  SELECT @autoIncrementValue:=MAX(id) FROM B;
  SET @query = CONCAT("ALTER TABLE A AUTO_INCREMENT = ", 
                      @autoIncrementValue);
  PREPARE stmt from @query;
  EXECUTE stmt;
  DEALLOCATE prepare stmt; 
END $$
DELIMITER ;

Die Prozedur könnte beim Systemstart beispielsweise über den „init-file“-Parameter in einer SQL-Datei ausgeführt werden (siehe [3]). Das Query auf Tabelle B (beispielsweise die Archivtabelle) kann bei Bedarf natürlich beliebig getauscht oder angepasst werden.

Quellen

[1] http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

[2] http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html

[3] http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_init-file

[4] http://stackoverflow.com/questions/2361982/create-table-new-table-name-like-old-table-name-with-old-table-names-auto-incre/2362599#2362599

Große Tabellen

Heutzutage werden die zu verwaltenden Datenmenge unbestritten immer größer. Viele datenverarbeitende Systeme sammeln und sammeln und sammeln… Außerdem müssen mittlerweile sehr oft auch Veränderungen von Daten aufgezeichnet werden. Dadurch wird die Datenmenge noch einmal deutlich größer. D.h. soll beispielsweise die alte Adresse eines Kunden nach einer Adressänderung weiterhin zur Verfügung stehen, muss sie zwangsläufig separat gespeichert werden. Die nachfolgenden Beispiele beziehen sich ausschließlich auf MySQL.
Große Tabellen weiterlesen

Probleme mit Umlauten und Sonderzeichen

Ein Problem, auf das zahlreiche Entwickler immer wieder stoßen, sind Umlaute. Wer kennt sie nicht – Zeichen wie ö, ä oder ü? Viele von uns haben schon einmal PHP-Code gesehen, in dem Entwickler mittels utf8_encode() oder utf8_decode() versucht haben, irgendwie den Zeichensatz zurechtzubiegen, ohne genau zu wissen was sie da eigentlich machen, oder weshalb die seltsam anmutenden Zeichen entstanden sind. Oft treten die Probleme auch nach einem Umzug der Datenbank auf. Beispielsweise wenn ein Content Management System auf einen neuen Server portiert werden soll. Erläutern wir also kurz wo die Probleme mit Umlauten/Sonderzeichen liegen und wie man sie vermeiden kann.

Erst einmal sollte man sich verinnerlichen, welche Teile der Anwendung vom Encoding betroffen sind:

  • Dateisystem (Dateinamen & Pfade, nicht die Datei selbst)
  • Dateien (PHP-Dateien genauso wie Datendateien)
  • Datenbank
    • Die Verbindung zur Datenbank
    • Standard-Encoding für die Datenbank
    • Standard-Encoding für die Tabelle
    • Encoding für die Zelle
  • Webserver
  • Encoding der Ausgabe des PHP-Interpreters
  • Standard-Encoding des Browsers
  • Encoding im HTTP-Header (z.B. „charset=utf-8“)
  • Angegebenes Encoding in der entstandenen HTML-Ausgabe (per meta-Tag)
  • Benutzer-Eingaben (z.B. aus Forms)

Diese Angaben beruhen natürlich auf der Annahme, dass man eine HTML-Datei erzeugen und diese per Webserver in einem Browser wieder ausgeben möchte. Versendet man beispielsweise E-Mails per PHP-Skript, so kann man in obiger Liste den Browser durch das Mail-Programm ersetzen und die HTML-Ausgabe fällt – zumindest bei Text-Mails – weg.

Ist das Encoding kaputt, muss man im schlimmsten Fall alle möglicherweise betroffenen Stellen prüfen. Bricht das Encoding in einem der vielen Schritte, ist es danach i.d.R. überall kaputt. I.d.R. deshalb, weil man natürlich mit Funktionen wie utf_encode() ggfs. die betroffenen Zeichen wieder geraderücken kann. Das ist aber nur die halbe Wahrheit. Sobald man sich nämlich aus dem westeuropäischen Zeichensatz herausbewegt, sind die Umlaute dann auch wieder kaputt. Das liegt an der intern fest kodierten Verwendung von ISO-8859-1, dem westeuropäischen Zeichensatz, innerhalb von utf8_encode/utf_decode.

In den letzten Jahren hat sich UTF-8 als neuer Standard im Web festgesetzt. Noch vor einiger Zeit bestimmten die ISO Codes 8859-xy maßgeblich die Zeichenkodierungen. Leider haben diese aber den Nachteil, dass sie immer nur einen ziemlich beschränkten Teil aller zurzeit verfügbaren Zeichen darstellen können und man deshalb in mehrsprachigen Systemen viele unterschiedliche Zeichensätze einsetzen musste. Mit der Einführung von UTF-8 benötigt man nur noch eine Kodierung.

Wie kann man per PHP auf das Encoding Einfluss nehmen?

Letztendlich kann ich nicht auf alle Webserver-Konfigurationen & Co. eingehen. Dahingehend konzentriere ich meine nachfolgenden Beispiele hauptsächlich auf PHP und MySQL.

Bereits in der php.ini gibt es die per default deaktivierte Option:

default_charset = "UTF-8"

Diese Option bewirkt in eingeschaltetem Zustand eine Erweiterung des Header-Eintrages „Content-Type: text/html“ um „;charset=UTF-8“. Für Apache-Nutzer: Gleiches kann man erreichen, indem man in der Datei „/etc/apache2/conf.d/charset“ die nachfolgende Zeile durch das Entfernen von „‚#“ aktiviert:

#AddDefaultCharset UTF-8

In beiden Fällen ist ein erneutes Einlesen der Konfigurationen (z.B. durch einen Neustart des Webservers) erforderlich. Sind übrigens beide Optionen aktiviert – also die in der php.ini und die in der Apache-Config, so gewinnt die von PHP.

Wer nach dem Aktivieren von einer der obigen Optionen noch immer Probleme mit dem Zeichensatz hat und der Text aus einer einfachen Datei kommt (dabei ist es irrelevant, ob der Text innerhalb der PHP-Datei oder einer separaten Datei steht) prüfen,

…ob der Header nun auch richtig gesendet wird. In Firefox erhält man diese Information beispielsweise über die rechte Maustaste und den Menüpunkt „Seiteninformationen anzeigen“ im Reiter „Header“. Dort kann der Punkt „Content-Type: text/html; charset=utf-8“ geprüft werden.

…ob die Datei auch im richtigen Encoding geschrieben wurde. Dabei ist es irrelevant, ob der auszugebende String innerhalb der PHP-Datei selbst, oder einer separaten Datei steht (von Datenbanken mal abgesehen – darauf gehe ich später ein).  In VIM kann UTF-8 beispielsweise mit folgendem Eintrag in der .vimrc erzwungen werden:

set fileencoding=utf-8

In praktisch allen aktuellen IDE’s und Texteditoren gibt es eine entsprechende Option, das Fileencoding einzustellen. Eine Ausnahme stellt hier wohl Netbeans dar, das es nur erlaubt, das Encoding für ein gesamtes Projekt zu bestimmen :-(. Wichtig zu wissen ist wahrscheinlich auch noch: Nur weil man durch Aktivieren einer der weiter oben beschriebenen Optionen im Header dem Content-Type ein charset-Attribut hinzufügt, bedeutet das nicht das automatische Konvertieren der Ausgabe und in unserem Fall somit der Datei. Die Datei muss bereits im richtigen Format vorliegen.

Bei der Verbindung zu MySQL sieht es anders aus. Hier wird bei der Angabe des Zeichensatzes auch automatisch konvertiert. Der entsprechende SQL-Befehl dafür lautet:

SET NAMES utf8

Natürlich muss man aber auch hier erst einmal sicherstellen, dass die Daten in der Datenbank im richtigen Format gespeichert wurden (genau wie bei Dateien auch) und dass zusätzlich auch das Feld das korrekte Encoding erhalten hat. Beispiel:

In einer Zelle steht ein gemischt-kodierter latin1- und utf8-String „üöäüöä“. Der erste Teil „üöä“ steht mit latin1 kodiert, der zweite „üöä“ mit utf-8 in der Datenbank innerhalb einer Zelle. Nun tätigen wir folgende Abfrage:

SET NAMES latin1; SELECT myfield FROM mytable;

Ergebnis: üöä��

Nun das Ganze in utf-8:

SET NAMES utf8; SELECT myfield FROM mytable;

Ergebnis: üöäüöä

Wenn also bereits der Inhalt einer Zelle nicht korrekt geschrieben wurde, kann auch kein „SET NAMES“ mehr helfen.

Fazit: Zeichensätze sollten über mehrere Systeme hinweg nicht gemischt werden. Angefangen beim Einlesen der Eingabedaten bis hin zur Ausgabe im Browser sollte der gleiche Zeichensatz beibehalten werden. Dann sind auch Funktionen wie utf8_encode() und utf8_decode vollkommen überflüssig. Wer diese benötigt, sollte sich fragen, ob es nicht sinnvoller ist die Quell-/bzw. Zieldaten gleich in UTF-8 zu halten. Wer kein UTF-8 benutzt und gleichzeitig mehrere Sprachen mit ihren verschiedenen Sonderzeichen unterstützten muss, sollte außerdem vorsichtig Kodierungen umgehen. UTF-8 nimmt einem hier eine Menge Arbeit ab.

Doctrine & Timestamps in Updates

Wenn man so am entwickeln ist, erfährt man manchmal seltsame Dinge. So habe ich heute versucht, ein datetime-Feld mit Doctrine Version 1 zu befüllen. Allerdings nicht mit NOW(), sondern mit einem noch zu berechnenden Wert. Das sah so aus:

Doctrine_Query::create()->update('Model_X')
      ->set(
        'datefield',
        'DATE_ADD(datefield, INTERVAL 1 MINUTE)'
      )
      ->where('ID = ?', $id)
      ->execute();

Es soll also einfach eine Minute auf das entsprechende Feld draufgerechnet werden. Leider funktioniert der obige Code nicht. Ersetzt man das execute() durch ein getSqlQuery(), so steht im erzeugten SQL-Statement nur noch DATE_ADD(datefield). Letzteres wiederum, ist keine gültige Syntax für MySQL.

Die Lösung ist so einfach wie trivial: Man nutzt einfach nicht DATE_ADD, sondern impliziete Typkonversion und lässt MySQL die Arbeit verrichten:

Doctrine_Query::create()->update('Model_X')
      ->set('datefield', 'datefield + INTERVAL 1 MINUTE')
      ->where('ID = ?', $id)
      ->execute();

Manchmal ist weniger eben mehr 😉

MySQL: Temporäre Tabellen

Manchmal kommt man um temporäre Tabellen einfach nicht herum. So kann es geschehen, dass man ein und die selbe Tabelle in einem Update und einer Unterabfrage  benötigt. Beispiel:

UPDATE `user`
SET `firstname` = 'Vorname', `lastname` = 'Nachname'
WHERE `id` IN (
  SELECT `id` FROM `user` WHERE `id` > 5 AND `id` < 10
)

Zugegebenermaßen hinkt die Sinnhaftikeit dieser Abfrage, was jedoch nicht unser Thema sein soll. Natürlich fällt eine Unterabfrage i.d.R. etwas komplexer aus. Beim Ausführen des obigen Queries erhält man folgende Fehlermeldung:

Error Code : 1093
You can’t specify target table ‚user‘ for update in FROM clause

Mit einer temporären Tabelle lässt sich das Problem schnell lösen. Man schreibt zunächst alle relevanten Daten in eine temporäre Tabelle, führt das Update aus und löscht die Temp-Tabelle wieder. Das Ganze könnte dann etwasso aussehen:

CREATE TEMPORARY TABLE tmp ENGINE=INNODB
SELECT `id` FROM `user` WHERE `id` > 5 AND `id` < 10;

UPDATE `user`
SET `firstname` = 'Vorname', `lastname` = 'Nachname'
WHERE `id` IN (
  SELECT `id` FROM `tmp`
);
DROP TABLE `tmp`;

MySQL bietet hierfür eine besondere Syntax „CREATE TEMPORARY“. Die Tabelle ist dadurch nur für die aktuelle Verbindung sichtbar und wird automatisch wieder entfernt, wenn man die Verbindung schließt.

Vorsicht beim DROP TABLE!

Nicht schlecht soweit. Eine Besonderheit ist mir allerdings im Beisein von Transaktionen noch aufgefallen. Lässt man alle Statements innerhalb einer Transaktion ausführen und macht am Ende ein Rollback, so hat letzterer bei obigem Code keine Wirkung mehr.

START TRANSACTION;
...
ROLLBACK;

Warum? Werfen wir einen Blick in MySQL-Dokumentation zu dem Stickwort „DROP TABLE“: http://dev.mysql.com/doc/refman/5.1/de/drop-table.html

Denn auch bei DROP TABLE gibt es das zusätzliche Schlüsselwort „TEMPORARY“. Dieses bewirkt unter anderem, dass keine laufende Transaktion wie etwa beim normalen „DROP TABLE“ beendet wird. Also updaten wir nochmal unser Skript:

START TRANSACTION;
CREATE TEMPORARY TABLE tmp ENGINE=INNODB
SELECT `id` FROM `user` WHERE `id` > 5 AND `id` < 10;

UPDATE `user`
SET `firstname` = 'Vorname', `lastname` = 'Nachname'
WHERE `id` IN (
  SELECT `id` FROM `tmp`
);
DROP TEMPORARY TABLE `tmp`;
ROLLBACK;

Die Datensätze in der Tabelle „user“ dürfen durch die oberen Statements nun nicht mehr beeinflusst worden sein.

Fazit:

Wer temporäre Tabellen nutzt und diese im gleichen Atemzug wieder löscht, sollte innerhalb von Transaktionen peinlichst genau auf das Schlüsselwort „TEMPORARY“ achten – sonst kann das ganz schön in die Hose gehen 😉

MySQL-Optimierungen die Zweite

Heute geht es um die Beschleunigung von Abfragen. Natürlich gibt es auch in diesem Themenbereich viele unterschiedliche Möglichkeiten, weshalb ich nur eine sehr kleine Auswahl darstelle:

MySQL-LIMIT

SELECT firstname, lastname
FROM user
LIMIT 1000, 1000;

Stellen wir uns vor, es sollen alle Vor- und Nachnamen in Tausender-Schritten aus der Datenbank gezogen werden.
Man kann dazu – wie im obigen Beispiel – zwei Parameter für LIMIT nutzen. Offset und RowCount. Das funktioniert soweit ganz gut. Solange jedenfalls, bis die Tabellen größer werden. Dann kann der Offset-Parameter zur echten Bremse werden.
Besser ist es dann, wenn man sich softwareseitig einfach den letzten maximalen Primärschlüssel zwischenspeichert und die Abfrage, dann wiefolgt ändert:

SELECT firstname, lastname
FROM user
WHERE id > 1000
LIMIT 1000;

Nun kann es natürlich geschehen, dass id’s gelöscht werden. Daher muss man sich auch die letzte ID merken und nicht einfach immer den Wert 1000 „draufrechnen“. Durch die Umstellung kann man deutlich schnellere Abfragen erreichen. Da auf der Primärschlüsselspalte ein Index liegt, fällt MySQL die Suche nach der nächstgrößeren ID leicht.

Subqueries statt Joins

Joins resultieren oft in temporären Tabellen, was ein EXPLAIN EXTEND schnell verraten kann:

EXPLAIN EXTENDED SELECT ... JOIN ...

Das Resultat lautet oftmals: Using temporary

Using temporary bedeutet leider einen ziemlichen Performanceverlust bei einer großen Datenmenge. Besser ist es dann, man nutzt ein Subquery:

 

EXPLAIN EXTENDED SELECT (SELECT b.myfield
                         FROM b
                         WHERE b.id = a.foreignKey) ...
                 FROM a

Für die Subqueries steht dann wieder ein Index bereit, der die Sache beschleunigt. Die lästige temporäre Tabelle fällt dann einfach weg und die Abfragen gehen zügig voran 😉

MySQL-Optimierungen

Immer wieder gelangt man als Entwickler an den Punkt, an dem die Datenbankabfragen einfach einen Tick schneller sein könnten. Mögliche Performanceauswirkungen sollte man deshalb bereits beim Entwerfen einer Tabelle berücksichtigen.
Dazu gibt es einige Möglichkeiten, von denen ich nachfolgend einige aufführen möchte.

NULL, wo auch NULL gebraucht wird.

NULL-Werte können eine sinnvolle Sache sein. Nicht immer werden diese aber benötigt. Mit der Zeit sieht man hin und wieder beispielsweise Spalten, die NULL erlauben und gleichzeitig einen DEFAULT-Wert besitzen. Das kann unter Umständen sinnvoll sein. Oder aber, die möglichen NULL-Werte werden überhaupt nicht genutzt. In diesem Fall sind sie überflüssig und kosten nur Performance. MySQL muss somit immer auch den NULL-Fall prüfen und aufwendigere Funktionen durchlaufen.

MyISAM und ROW_FORMAT=FIXED

Für MyISAM-Tabellen bietet es sich für Geschwindigkeitsoptimierungen hinsichtlich Lesezugriffen an, das Tabellenformat auf FIXED zu stellen. Damit muss die Datensatzlänge nicht aufwendig berechnet werden. Das spart Zeit. Dafür müssen allerdings feste Datentypen (also beispielsweise CHAR, statt VARCHAR) verwendet werden. Dadurch benötigt man aber mehr Speicher.