Tag Archives: relationale Datenbanken

Fiese Falle in MySQL bei Verwendung von Views in (Left)-Joins

Posted on by

Da habe ich doch kurz vor Weihnachten noch eine echte Knobelaufgabe erlebt, deren Ergebnis für den ein oder anderen sicherlich interessant sein dürfte. Ausgangspunkt war folgendes eigentlich simple SQL-Statement:

Select * from tableA left join viewC 
     on tableA.id=viewC.lookup

Soweit so unspektakulär, auch wenn das Original noch mit einigen weiteren Filtern gespickt war und die View nicht gerade ein einfaches Query kappselt.

Was allerdings gar nicht lustig war, waren die Ergebnisse dieser Abfrage, denn sie entsprachen nicht den Erwartungen. Aus der View erhielt ich nämlich keinen Join-Partner obwohl ich diesen als Test extra geprüft hatte mit:

 Select * from viewC where lookup='TestValue'

Folgende Punkte habe ich als “übliche Verdächtige” abgeprüft:

  • Vergleich der Datentypen – auch nicht die Ursache
  • Collations (also Zeichensätze) – auch mit Casting, das Ergebnis bleibt das Gleiche

Spannender Nebeneffekt, der bei der weiteren Untersuchung heraus kam – nutze ich die view nicht sondern packe 1:1 den Code aus der Definition mit in mein Statement

 
Select * from tableA left join 
   (select * from tableB 
     left join tableF 
         on tableB.id=tableF.foo 
      where tableF.bar='baz' and deletedDate is null)
   as testview on testview.id=tableA.id

Dann funktioniert wieder alles wie erwartet. Es staunt der Laie und der Fachmann wundert sich. Höchste Zeit das Problem genauer unter die Lupe zu nehmen und die bekannten Bugs von MySQL bzw. den Deviraten wie MariaDB und Percona zu durchforsten. Ergebnisse sind leider recht dürftig und beschreiben nicht das was ich erlebe. Immerhin stoße ich auf weitere Probleme die erst einmal völlig losgelöst von der Thematik erscheinen, es geht dabei um möglich Einstellungen des Servers und den Standard-Verhalten. Das hat sich in den letzten Jahren immer mal wieder etwas verändert und spiegelt des “Erwachsen werden” von MySQL wieder. Wir erinnern uns: Es hat eine geraume Zeit gedauert bis MySQL endlich als Standard auf INNODB als Engine gewechselt hat und somit referenzielle Integrität in Form von Transaktionen und Foreign Keys unterstützt wurden. Ebenso aus der grauen Urzeit von MySQL stammen Probleme mit NULL-Werten bei Datumsfeldern (DATE,DATETIME). Und genau da liegt der Hase am Ende auch begraben. Die Server-Konfiguration gibt vor, dass der Server sich strikt verhalten soll, unter anderem bei den Datumsfeldern.
Somit ist es nicht mehr zulässig den “uralten NULL-Ersatz 0000-00-00 00:00:00” zu verwenden. Die Routinen für den Ersatz kann man aber per Session einschalten, und genau das ist bei der manuellen Abfrage der Werte jedes mal passiert, sei es per manuellem Join oder bei der Abfrage der einzelnen Tabellen. Dabei verhält sich das isnull je nach eingestelltem Modus. Mal liefert es Zeilen mit “0000-00-00 00:00:00” als Ergebnis, mal nicht, je nachdem ob strict-mode gesetzt ist oder nicht.

Warum läuft es dann beim Verwenden der View aus dem Ruder? Das liegt an der Art und Weise wie MySQL mit Views umgeht: Sind diese übermäßig komplex werden sie als separater Thread gestartet, der eine temporäre Tabelle bereitstellt auf die dann wiederum gejoined werden kann. Dieser Thread wird allerdings vom Serverprozess angestoßen, und dort steht der Modus per default auf strict und dieser wird dann auch angewandt. Somit ist das Ergebnis nicht das was ich bei der singulären Abfrage erhalten habe, sondern leider eben ein leeres da in der Tabelle anstelle NULL eben noch “0000-00-00 00:00:00” verwendet wird.

Die temporäre Lösung ist, den Join manuell auszuführen, mittelfristig werden wir den Server umstellen. Das sind aber eigentlich nur kosmetische Lösungen. Die eigentlich korrekte Maßnahme für dieses Problem ist die Verwendung von sauberen NULL-Werten anstelle obskurer Workarounds. Leider verlassen sich sehr viele Applikationen bei uns noch auf die Tabelle und diese kruden Werte, einfach Umstellen ist also erstmal leider nicht drin. Ich kann jedem Entwickler nur wärmstens empfehlen, sich auf derartige Altlasten nicht zu verlassen und schnellstmöglich dafür zu sorgen diese abzubauen.

So bitte nicht – Schnittstellen

Posted on by

Es ist an der Zeit die lose Reihe mal wieder etwas weiter zu führen, mit einem Thema das mich die letzten Tage wiederholt beschäftigt hat: Schnittstellen.

In der IT-Welt beschreiben Schnittstellen verschiedenste Dinge – den meisten vertraut sind die Begrifflichkeiten aus der Hardware – hier erkennt man auch noch recht klar die Eigenschaft einer Schnittstelle, sie bildet die Grenze zwischen zwei Geräten, verallgemeinert von Systemen. Welches System an einer Schnittstelle andockt ist im ersten Moment unerheblich – die meisten Schnittstellen sind universell gehalten: Ob man eine Tastatur oder eine Maus in eine USB-Buchse einstöpselt ist im ersten Moment unerheblich. Die Schnittstelle ist so ausgelegt, dass über verschiedene Ebene hinweg eine Kommunikation nach einem bestimmten Muster stattfinden kann. USB beispielsweise gibt verschiedene Operations-Modi für unterschiedliche Anwendungszwecke – von der Maus als Eingabegerät über den GPS-Sensor bis hin zum Speichermedium. Die häufig verwendeten Hadware-Schnittstellen sind durch verschiedene Normen und Vorschriften festgelegt. In der Industrie gibt es zudem verschiedene Eigenentwicklungen für spezielle Zwecke, diese sind nicht zwingend so universell wie USB, aber sie erlauben auch weiterhin den Austausch von Informationen von einem System zu einem anderen hin. Im Fahrzeug z.B. per CAN-Bus: Dort meldet die Klima-Regelung an die Klima-Anlage die gewünschte Temperatur und die aktuelle Temperatur, der Klima-Kompressor wird daraufhin entsprechend in der Leistung geregelt.

Soweit einmal der Sinn und Zweck von Schnittstellen an anschaulichen Beispielen, aber mit der Hardware ist ja noch lange nicht Schluss. Vielmehr ist das ja noch alles nachvollziehbar und man kann sich unter der Definition einer Schnittstelle sehr leicht etwas vorstellen. Aber auf etwas höherer Abstraktionsebene existieren weiter Schnittstellen. Diese sind nicht so leicht erkennbar, aber dennoch vorhanden.

Jeder Programmierer der nicht absolut blutiger Anfänger ist, kennt die Erzeugung von wieder verwendbarem Code – nahezu jede Programmiersprache (von einigen Exoten einmal abgesehen) kennt den Begriff einer Funktion oder Methode. Das Prinzip dieser Konstrukte lehnt sich an der Mathematik an: Meist gibt man eine bestimmte, wohldefinierte Menge von Werten in eine Funktion hinein, die daraus etwas errechnet, etwas auslöst etc. in aller Regel gibt es auch noch einen Rückgabe-Wert (je nach Programmiersprache gibt es auch Möglichkeiten mehrere Werte zurück zu geben, entweder über den Stack oder einen reservierten, gemeinsam genutzten Speicherbereich, oft als Heap bezeichnet – das hier zu erläutern würde allerdings zu weit führen …). Wenn man das Prinzip der Funktionen einmal verstanden hat, sind diese einfach praktisch zu verwenden. Wichtig ist hierbei immer die Parameter-Reihenfolge (wie in der Mathematik auch) und je nach Programmiersprache auch noch der Werte-Typ der Parameter (starke oder schwache Typisierung: In Skriptsprachen wie PHP und Perl ist der Typ egal, in Java oder C müssen auch die Typen übereinstimmen – für beide Standpunkte gibt es gute Argumente, ich tendiere mittlerweile eher zu starker Typisierung und würde sie mir auch in PHP wünschen … aber auch das würde diesen Artikel leider sprengen). In ganz ähnlicher Weise lässt sich das auf objektorientierte Programmierung ausweiten. Auch dort gelten die gleichen Grundsätze, zusätzlich gibt es noch ein paar nette Gimmiks on top.

Gehen wir noch eine Abstraktionsstufe höher, dann sind wir bei Abläufen und Prozessen und eigentlich nicht mehr zwingend in der Softwarewelt verhaftet. Jeder Austausch von Daten über Systemgrenzen hinweg benötigt eine Schnittstelle. Egal ob die beiden Systeme nun Rechner sind, Menschen, Unternehmen oder sogar jede beliebige Kombination aus den vorgenannten. Überlegen Sie einmal inwiefern ein Rechner ihnen bestimmte Schnittstellen eröffnet und wann sie diese nutzen. Etwas Hirnakrobatik, aber wenn man es mal gemacht hat, sind viele Dinge am Rechner mit einmal logisch verständlich.

Was leider immer wieder schief geht ist die Spezifikation entsprechender Schnittstellen in dieser Abstraktionsebene. Oftmals sind sich die beiden Kommunikationspartner nicht bewusst, dass eine klar definierte Sprache/Syntax/Form der Kommunikation notwendig ist um Missverständnisse und Fehler zu vermeiden. Das beginnt meist mit solchen Sprüchen “wir bekommen dann eine Datei, da steht alles drin was uns unser Partner mitteilen möchte und was wir wissen wollen…”. Wie diese Datei am Ende aussieht ist bei solchen Sachen meisten leider erst einmal egal – dank der Intelligenz vor dem Monitor ist es dem Betrachter auch egal ob ein Textdokument als reiner Text, Word-Datei, Vektorgrafik oder als pixelbasierte Bilddatei vorliegt. Lesen und verstehen kann der Mensch das alles. Problematisch wird es wenn es dann an die Automatisierung geht. Da sind nicht alle Formate wirklich gut geeignet – jeder der einmal versucht hat aus einem abfotografierten Text wieder einen editierbaren Text zu machen weiß wovon ich spreche: Ohne Optial Character Recognition (OCR – Texterkennung) geht da gar nichts und selbst die ist meist nicht sonderlich fehlerfrei, von der Semantik der einzelnen Texteile und dem Layout mal ganz zu schweigen. Besser sind dann schon Excel oder Word-Dateien wobei die auch nicht immer kompatibel untereinander sind. Es gilt auch hier das alte Mantra: Je einfacher, je simpler um so besser. In der IT hat man für diverse Datentypen gut erprobte Ablage-Möglichkeiten entwickelt. Tabellen kann man recht gut in SQL oder CSV ausdrücken, wenn es etwas weniger stark strukturiert ist, kommen XML-Varianten in Frage.

Was lernen wir aus diesen Sachen?

Erstens: Es geht nicht ohne Schnittstellen, selbst innerhalb eines Programms gibt es in der Regel verschiedene Module oder Funktionen die Daten untereinander austauschen und so zum Gesamtsystem beitragen. Diese Schnittstellen sind in der Regel durch die verwendete Programmiersprache und die Programmierweise vorgeben (funktionale, prozedural oder objektorientiert).

Zweitens: Schnittstellen nach außen hin (egal ob Mensch oder Maschine) müssen klar erkennbar und definiert sein. Man denke hierbei immer an die Zukunft und überlege ob es vielleicht sinnvoll ist, an einigen Stellen bereits die Option für eine externe Schnittstelle zu schaffen, später nachrüsten ist immer schwierig und bestehende Schnittstellen verändern ist wie beim Auto plötzlich Kupplungs- und Gas-Pedal zu vertauschen: Die Konsequenzen sind bestenfalls amüsant, schlimmstenfalls gefährlich und es kostet sehr viel Nerven…

Drittens: Bereits bei der Planung von Systemen berücksichtigen welche Daten mit externen Systemen automatisiert ausgetauscht werden sollen und wie. Einerseits muss klar definiert sein, welche Daten an externe Systeme bereit gestellt werden sollen. Evtl. ändert sich durch solche Anforderungen der Erfassungsbedarf in der eigenen Anwendung/dem eigenen System oder lässt einigen Sachverhalten eine neue Priorität zukommen. Auch wichtig ist das Format – dieses muss für den Anwendungsfall geeignet sein, Bilder in Word einzufügen um sie daraus wieder zu extrahieren ist ungeschickt – einfacher geht es wenn man Bilddateien direkt verschickt. Ist die Information tabellenförmig, so kommen Excel oder die simple Variante CSV (comma separated values) in Betracht. Für komplexere und ggf. nicht immer einheitliche Datenaustausche kann man sich mit XML behelfen. Wobei immer zu beachten ist: In aller Regel steht hinter jeder XML-Datei und jedem System auch wieder ein relationales Backend (aus Performance-Gründen) – es lohnt also, ggf. einmal auch die Betreuer und Entwickler des Partner-Systems über deren Struktur zu fragen.