MySQL/MariaDB-Datenbankdienst: Änderung in Konfiguration

Das Problem

Bei der im Sommer 2019 erfolgten Umstellung des URZ-Datenbankdienstes auf die Open-Source-Software MariaDB wurde vereinzelt beobachtet, dass sich bestimmte Datenbank-Transaktionen (INSERT- oder UPDATE-Statements) plötzlich anders verhielten oder mit einer Fehlermeldung abgebrochen wurden. Die Ursache dafür ist der in MariaDB ab Version 10.2.4 standardmäßig gesetzte STRICT_TRANS_TABLES-Modus. Dadurch erfolgt eine strenge Syntax-Prüfung der verwendeten SQL-Statements. Nachlässig programmierte Anwendungen funktionieren so möglicherweise nicht mehr wie vorher.

Das URZ musste aufgrund nicht absehbarer weiterer Auswirkungen zeitnah reagieren: Der STRICT_TRANS_TABLES Mode wurde deaktiviert. Der Server wird dadurch aber seitdem nicht mit der empfohlenen Standard-Konfiguration betrieben, sodass bei Anwendungen mit unzulässigen SQL-Statements Datenverlust auftreten kann.

Das URZ wird daher den STRICT_TRANS_TABLES Mode am 08.01.2020 wieder aktivieren.

Wer ist betroffen?

Alle Nutzer des MySQL/MariaDB-Datenbankdienstes des URZ sind potentiell betroffen. Nicht betroffen ist, wer in seinen Datenbank- oder Web-Applikationen (die den Datenbankdienst nutzen) stets SQL-Standard-konforme Anweisungen verwendet. Sind Sie unsicher? Dann folgen Sie bitte diesem Link mit wichtigen Hinweisen zur Programmierung.

Was ist zu tun?

Bis zum genannten Termin müssen alle (Web-)Applikationen, die den MySQL/MariaDB-Datenbankdienst nutzen, überprüft und – soweit erforderlich – korrigiert werden.

Sie können vorab überprüfen, ob Ihre Applikation auch mit aktiviertem STRICT_TRANS_TABLES funktioniert. Setzen Sie dafür innerhalb der Verbindung zur Datenbank die nachfolgend genannte Option (Beispiel für PHP-Anwendung):

$db = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $user, $password);

// STRICT_TRANS_TABLES Mode für aktuelle Session setzen
$db->query('SET SESSION sql_mode="STRICT_TRANS_TABLES"');

Der Test sollte nur temporär erfolgen. Bitte vergessen Sie nicht, die Anweisung danach wieder zu entfernen!

Stellen Sie ein geändertes Verhalten der Datenbank-Operationen fest, müssen diese angepasst werden. Dabei hilft folgende Checkliste:

1. Kontrollieren Sie alle INSERT- und UPDATE-Operationen.

   Verwenden Sie die korrekten Datentypen und beachten Sie bei Zuweisungen an Felder die Feldlänge.

2. Achten Sie bei INSERT-Operationen darauf, allen Spalten der Tabelle einen Wert zuzuordnen.

   Alternativ können Sie beim beim Erzeugen einer Tabelle mit CREATE TABLE … allen Spalten der Tabelle einen entsprechenden Standardwert zuordnen.

3. Aufgepasst beim „Datumsformat mit Zeitzone“.

   Dieses Datumsformat war schon immer ungültig (Beispiel: '2019-11-13T07:09:10+0200'), jedoch hat der alte nicht standardkonforme SQL-Modus einfach die Zeitzone abgeschnitten und ignoriert. Nach der Umstellung werden solche Datumsformate nicht mehr vom Server akzeptiert. Bitte setzen Sie das Datumsformat immer in der Zeitzone, die Sie für die aktuelle SQL-Sitzung eingestellt haben (SESSION.time_zone).

4. Abfangen von Fehlern

   Um bei aktivem STRICT_TRANS_TABLES-Modus Fehlermeldungen von Datenbankoperationen auswerten zu können, muss der Datenbankverbindung ein weiteres Attribut mitgesendet werden – wieder gezeigt am Beispiel PHP-Anwendung:

   $db = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $user, $password);
   
   // PDO Error-Handling für aktuelle Session setzen
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   Anschließend lassen sich fehlerhafte SQL-Statments abfangen, da zusätzlich zum Fehler-Code eine PDOException geworfen wird, was Ihnen beim Debuggen des Quellcodes hilft.

   try {
       // Spalte `foo` existiert nicht
       $query = 'SELECT foo FROM foo_bar WHERE bar = 1';
       $result = $db->query($query);
   } catch (PDOException $e) {
       echo 'Fehler: ' . $e->getMessage();
   }

Erweist sich das Überarbeiten Ihrer Anwendung(en) als zu aufwendig oder nicht durchführbar, bleibt noch als letzte Variante, den STRICT_TRANS_TABLES Mode im Code für die jeweilige Datenbank-Session zu deaktivieren. Nehmen wir wieder oben genanntes PHP-Beispiel, dann stellen wir das nach erfolgtem Verbindungsaufbau so ein:

$db->query('SET SESSION sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"'); 

Sind Sie unsicher oder wissen Sie trotz der Erklärungen nicht, was Sie jetzt tun müssen? Dann schicken Sie bitte eine E-Mail mit dem Betreff STRICT_TRANS_TABLES an support@hrz.tu-chemnitz.de. Bitte zögern Sie nicht, uns zu kontaktieren – wir helfen gern.