De slechte punten van MySQL op een rijtje

Uit Yapf

Voorwoord.

MySQL is bijzonder populair maar zit helaas vol gaten en problemen. Dit artikel verzamelt de gaten en probeert te vertellen wat er mis is en hoe je het kunt repareren.

MyISAM vs InnoDB vs Falcon vs Maria.

Een aantal van de fouten in MySQL bestaan alleen in de MyISAM tablehandler. Helaas heeft MySQL er voor gekozen om in <5.1(?) MyISAM als standaard tabeltype te gebruiken, dus als je niet weet wat je doet worden deze fouten in je maag gesplitst. Het gebruik van de InnoDB, Falcon of Maria tablehandlers lost sommige problemen van MyISAM gedeeltelijk op, maar ten koste van dingen die InnoDB, Flacon of Maria niet kunnen en MyISAM weer wel. Hieronder valt b.v. de FULLTEXT index.

MySQL < 4.1.7 vs 4.1.7+

Versie 4.x van MySQL is heel erg oud en wordt door MySQL zelf ook niet meer ondersteund. Het zou echt niet meer gebruikt moeten worden maar mocht je nog ergens zo'n oude installatie tegenkomen

MySQL 5.0.15

Met 5.0 is een grote stap gemaakt maar, maar om de backwards compatibility op te houden worden veel zaken alleen opgelost als je STRICT mode aanzet. Deze mode staat standaard uit en is ook handmatig aan en uit te schakelen dus het nut is beperkt tot het IQ van de gebruiker. In de volgende lijst wordt onderscheid gemaakt tussen MySQ 5.0.15 en MySQL 5.0.15 in STRICT mode. Waar STRICT mode vereist is staat 5-STRICT vermeld. Onder 'alle versies' valt dus ook 5 met strict mode!

MySQL 5.1.41

De laatste update van dit document is gedaan tegen 5.1.41 onder Ubunutu.

Fouten tegen de SQL standaard

SQL is een standaard zodat iedereen die SQL kent met zijn kennis op elke database terecht kan. Elke database fabrikant wijkt wel eens van die standaard af door iets uit de standaard niet te implementeren of iets wat niet in de standaard staat juist wel te implementeren, maar MySQL doet dat regelmatig op onhandige en gevaarlijke manieren.

"||" betekent "OR" (alle versies)

In de SQL standaard staat || voor het aan elkaar plakken van twee waardes, SELECT 'A'||'B' geeft 'AB

In MySQL staat het echter voor een binaire or:

geeft 1 Als je in MySQL twee waardes aan elkaar wilt plakken moet je perse de functie CONCAT() gebruiken:

Nadeel: Portabiliteit en debuggen. De syntax van de SQL standaard is ook correcte SQL voor MySQL dus je krijgt geen fout op de query, maar MySQL doet iets anders dan je volgens de standaard zou verwachten. Dit is erg lastig te debuggen omdat er nergens een foutmelding komt, maar de uitslag van je applicatie wel fout is.

Dit gedrag kun je handmatig corrigeren door de SQL-MODE op PIPES_AS_CONCAT te zetten.

Strings zijn niet hoofdlettergevoelig (alle versies)

Strings zijn in MySQL niet hoofdlettergevoelig: SELECT 'A' = 'a' geeft 'true'. Als je wel hoofdlettergevoelig wilt vergelijken dan moet je dat met de hand opgeven via het niet-standaard statement 'BINARY'

In latere versies is dit blijkbaar weer vervangen door een nieuw datatype: VARBINARY.

Volgens de SQL-Standaard moeten strings case-sensitive zijn.

NOT NULL werkt alleen indien NULL wordt ingevoerd in de query zelf (alle versies behalve 5-STRICT)

NOT NULL zorgt er voor dat er geen NULL in een kolom kan staan. Dat betekent dat er altijd een getal of string in de kolom moet staan. INSERT of UPDATE queries die op welke manier dan ook toch een NULL proberen in te voeren die zullen worden geweigerd.

Bij MySQL voorkomt NOT NULL alleen dat er geen NULL als waarde voor die kolom mag worden ingetikt in een query. Als de NULL op een andere manier in de NOT NULL kolom terecht zou komen dan verandert MySQL de NULL naar het dichtstbijzijnde wat wel in de kolom zou mogen, zoals het getal nul of een lege string.

a is nul, b is een lege string en c is NULL, ookal heb ik geen waardes opgegeven voor a en b en staan er geen default waardes in de tabel definitie.

Kopieer de NULL van kolom 'c' naar 'a' en 'b'. Merk op dat 'a' op NOT NULL is ingesteld en dus geen NULL waarde mag accepteren. update test set a=c, b=c; Query OK, 1 row affected, 2 warnings (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 2

"query ok" met twee warnings, maar de query wordt welk uitgevoerd en de data is gesloopt:

Nadeel: NOT NULL garandeert niet dat er geen NULL voor de kolom aangeboden mag worden.

Delen door nul geeft NULL ipv fatal error. (alle versies)

Geeft NULL als resultaat, alle andere databases geven een foutmelding "division by zero".

Geen foutmelding bij ongeldige datums (alle versies behalve 5-STRICT)

Slechtgeformatteerde datums en datums die niet in de normale kalender vallen worden genegeerd en in de tabel komt '0000-00-00' te staan.

Nadeel: De kolom is niet meer betrouwbaar, wat je aanlevert is niet perse wat er wordt opgeslagen en je komt er niet achter wanneer het fout gaat.

STRICT mode niet strict in MyISAM tabellen (MySQL <5.0.37)

STRICT mode hoort datums te controleren voordat ze aan een tabel worden aangeboden. Dit blijkt echter niet te gebeuren:

Volgens STRICT mode zou je nu dus geen 32 februari meer moeten kunnen invoeren, maar de volgende query update eerst een aantal records naar een geldige datum en daarna een aantal andere records naar 32-februari:

Resultaat:

select * from ttt;

+------------+----+ | datum | id | +------------+----+ | 2005-01-01 | 1 | | 0000-00-00 | 2 | | 0000-00-00 | 3 | | 0000-00-00 | 4 | +------------+----+ 4 rows in set (0.00 sec)

Geen error, geen warning, de eerste twee records staan op 1-jan-2005 en de overige staan op 29-dec-1899.

Als de InnoDB table handeler gebruikt wordt krijg je wel een fatal error en worden de updates die wel gedaan zouden kunnen konden worden toch niet gedaan.

31 februari is een geldige datum (alle versies behalve 5-STRICT)

Waarom gebeurt dit? MySQL's controle op geldige datums bestaat in het controleren of de drie onderdelen, dag, maand en jaar, vallen binnen bepaalde grenzen. Zo moet dag vallen tussen 1 en 31, de maand tussen 1 en 12. Maar daarbij wordt niet gecontroleert of de maand wel 31 dagen heeft. Februari is maand 2 en valt dus tussen 1 en 12, en 31 valt tussen 1 en 31, dus 31-februari is een geldige datum.

Nadeel: moet ik dat nog uitleggen? Nou ok dan; je kunt 31 februari invullen als datum. Uhm... Hallooo!

Geen controle op bereik van INT velden (alle versies behalve 5-STRICT)

Als een getal hoger of lager is dan de limiet van het datatype dan wordt het getal afgerond tot iets dat wel in het bereik past:

Nadeel: De kolom is niet meer betrouwbaar, wat je aanlevert is niet perse wat er wordt opgeslagen en de database geeft geen melding wanneer dit gebeurt.

Geen controle op lengte van CHAR/VARCHAR velden (alle versies behalve 5-STRICT)

Als een string langer is dan de definitie van de kolom dan wordt de string afgekapt op de maximale lengte van de kolom:

Nadeel: De kolom is niet meer betrouwbaar, wat je aanlevert is niet perse wat er wordt opgeslagen en de database geeft geen melding wanneer dit gebeurt.

Geen controle op datatype bij invoer (alle versies behalve 5-STRICT)

MySQL voert NULL (<5.1.41) of 0 (5.1.41) in voor numerieke velden waar een string wordt ingevoerd:

Nadeel: De kolom is niet meer betrouwbaar, wat je aanlevert is niet perse wat er wordt opgeslagen en de database geeft geen melding wanneer dit gebeurt.

ENUM Met enum gaat in MYSQL heel wat fout

Meerdere gelijknamige opties mogelijk in een set (alle versies behalve 5-STRICT)

ENUM is een set waardes waar je uit kunt kiezen. Het idee is dat de toegestane opties worden voorgedefinieerd zodat je geen andere kunt invoeren dan wat er reeds is gedefinieerd.

In MySQL kun je in een ENUM set dezelfde naam meerdere keren opgeven:

Nadeel: Het is onmogelijk om aan te geven welke van de dubbele waarden je precies bedoelt; de eerst 'a' of de vierde? Als een van de dubbele waarden wordt weggehaald wet je dus ook niet welke dat zal zijn.

definitie te veranderen terwijl waardes in gebruik zijn (alle versies behalve 5-STRICT)

ENUM definities kunnen worden veranderd terwijl de waardes die je verandert in gebruik zijn. Als in de nieuwe ENUM een key die in gebruik is niet meer bestaat dan wordt die key in de records vervangen door NULL.

Nadeel: Als je een klein foutje maakt bij het aanpassen van je ENUM set dan is je data kapot en je krijgt daar géén foutmelding van!!

Sorteert op key, niet op de naam van de optie (alle versies)

ENUM kolommen worden gesorteerd op de plaats die de key inneemt in de ENUM set, niet op de string van de key:

Het vreemde is dat bij vergelijkingen weer wel de naam wordt gebruikt in plaats van de positie:

Nadeel: Je moet de ENUM set handmatig sorteren bij het aanmaken, anders krijg je onvoorspelbare resultaten.

GROUP BY controleert niets (alle versies behalve 5-STRICT)

MySQL zonder strict mode staat het volgende toe.

Maar wat doet mysql hier met 'b'?

Voorbeeld; een tabel met namen. Sommige mensen hebben dezelfde voornaam.

In MySQL is het nu mogelijk om de totale leeftijd per voornaam op te vragen en daarbij ook de achternaam op te vragen.

Volgens de tabel zijn er meerdere mensen met voornaam 'henk' en 'piet', maar toch zie je hier alleen de achternamen 'biersma' en 'de poeper'. In de query wordt niet gezegd welke achternaam er moet worden opgehaald dus hoe kan MySQL hier bepalen welke achternaam er moet worden getoond? Welke relatie heeft die achternaam tot de totale leeftijd?

MySQL neemt hier voor de achternaam gewoon de eerste die hij tegenkomt. Als 'van rossum' eerder was ingevoerd dan 'biersma' dan zou je nu 'van rossum' zien.

De uitslag van deze query is dus niet alleen onzin, het kan ook nog eens veranderen als de invoer volgorde van de records verandert, bijvoorbeeld na een update query, een OPTIMIZE of een restore van een backup.

Nog een mooie:

Voeg nog een record toe voor henk, met leeftijd 70

insert into test values ('henk', 'plop', 70);

Draai de query opnieuw:

MySQL pakt gewoon het eerste record dat hij regenkomt.

Nadeel: Je krijgt onbetrouwbare data uit je query en MySQL waarschuwt je er niet voor. Stel je voor dat je dit doet bij het maken van rekeningen, dan krijgt 90% van je klanten geen rekening en de rest krijgt een rekening voor het totaal van alle rekeningen voor alle klanten met dezelfde achternaam... brrr!

Je kunt een tijdelijke (temporary) tabel maar één keer gebruiken in een query

zie: http://www.mysql.com/doc/en/Temporary_table_problems.html

Een self-JOIN met een tijdelijke tabel is dus niet mogelijk

Het kan zelfs niet in een UNION:

Nadeel: dit beperkt het nut van tijdelijke tabellen nogal, want die gebruik je juist vaak in constructies waarin je ze meerdere keren binnen dezelfde query nodig hebt.

JOINS in UPDATE en DELETE (alle versies)

Hiermee kun je records in meerdere tabellen tegelijk updaten en deleten.

MySQL ondersteunt dit omdat ze in MyISAM de relaties nog niet voor elkaar hebben. In een gewone relationele database zou je een ON UPDATE CASCADE of ON DELETE CASCADE aan de relaties meegeven zodat updates en deletes automatisch worden doorgevoerd naar alle gerelateerde tabellen.

Nadeel: In deze 'feature' van mysql moet je nog steeds de relaties tussen je tabellen afdwingen in je applicatie terwijl dat een taak van de database hoort te zijn. Het kromme is dan weer dat de InnoDB table handler wel relaties kent en toch ook joins in een delete kan uitvoeren.

Dubbele quotes in SQL (alle versies)

MySQL accepteert naast de enkele quotes ook dubbele quotes om strings te definieren in de query syntax. Dit is tegen de SQL standaard die zegt dat alleen enkele quotes zijn toegestaan. Andere databases gebruiken dubbele quotes dan ook alleen voor speciale database-specifieke doeleinden.

Nadeel: Dit leert je aan om een niet-standaard syntax ge gebruiken voor standaard SQL zaken. Dat vergroot de kans dat je dezelfde foute syntax gaat gebruiken op andere databases die het niet accepteren.

References in CREATE TABLE worden genegeerd

CREATE TABLE a (x int) TYPE=innodb;; CREATE TABLE b (kolom integer not null references a (x)) TYPE=innodb;

Tabal 'b' heeft nu géén relatie met 'a' en er is geen foutmelding geweest.

Bizar gedrag Foreign-keys en Composite Primary Keys (5.0 - 5.1.41)

Het is in MySQL toegestaan om een Foreign-Key relatie te leggen naar een kolom die onderdeel is van een samengestelde Primary-key.

CREATE TABLE `a` (
  `x` INTEGER(11) NOT NULL,
  `y` INTEGER(11) NOT NULL,
  PRIMARY KEY (`x`, `y`)
)ENGINE=InnoDB;
 
 
CREATE TABLE `b` (
  `kolom` INTEGER(11) NOT NULL,
  KEY `kolom` (`kolom`),
  CONSTRAINT `b_fk` FOREIGN KEY (`kolom`) REFERENCES `a` (`x`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
</lang>
 
''kolom'' IS nu een FK naar alleen de kolom ''x'' en dat kan niet want ''x'' IS niet uniek binnen tabel ''a''.
 
Als je vervolgens een ON DELETE CASCADE gebruikt en IN tabel ''a'' een record weghaalt waarvan de waarde van ''x'' IN ''b'' werd gebruikt, dan wordt het record uit Y gewist, ookal zijn er nog andere records IN X waar dezelfde waarde van ''b'' IN staat.
 
<source lang="sql">
INSERT INTO `a` (`x`, `y`) VALUES 
  (1, 1),
  (1, 2),
  (1, 3);
 
INSERT INTO `b` (`kolom`) VALUES 
  (1),
  (1),
  (1);

De tabel a bevat nu drie keer de waarde 1 voor x en die wordt gebruikt in drie records van tabel b. Als nu het eerste record uit tabel a wordt gewist dan verwdijnt er één waarde 1 it x, maar toch verdwijnen alle records uit b.

Views

Met views kun je queries in de database programmeren en aanspreken alsof het een tabel is. Zo kun je de structuur van je database verstoppen voor de buitenwereld (minder tikwerk)

MySQL 5 biedt ondersteuning voor VIEWS en beperkte ondersteuning voor UPDATEABLE VIEWS. Een view kan alleen updatable zijn als er in de definitie van de view slechts één tabel wordt gebruikt en geen GROUP BY, DISTINCT, HAVING, UNION of subquery wordt gebruikt.

Stored functions

Geen parameters voor LIMIT en OFFSET binnen stored function

Binnen een stored function geen functieparameters gebruiken om de LIMIT en OFFSET van een query te voeden.

Geen EXECUTE binnen stored function

Je mag binnen een stored function geen zelfgemaakte query uitvoeren met EXECUTE.

MySQLDump dumpt standaard geen stored functions en routines

Als je via mysqldump een backup maakt moet je zelf aangeven dat je daar de stored functions en stored procedures in wilt opnemen. Standaard worden deze niet in de backup gezet.

Nadeel: De backup slaat delen van de database over, je backup kan ongemerkt incompleet zijn.

Noot: Vrijwel elke database heeft opties om dingen uit de backup over te slaan, maar standaard staan alle opties altijd aan. Alleen MySQL vindt het beter dat de standaardbackup incompleet is.

Vage gedragingen bij database beheer

Je kunt databases droppen terwijl je er mee bent verbonden (alle versies)

Je kunt een verbinden met de server, een database kiezen en vervolgens die database verwijderen terwijl je er mee verbonden bent:

Dit zou op zijn minst een fout moeten geven dat de database in gebruik is.

Nadeel: Als je website werkt met een database user die ook het DROP recht heeft dan kan een hacker via injectie een DROP laten uitvoeren op de database van je website zelf. In elk zinnig systeem kun je de database die je gebruikt niet droppen, je moet eerst de verbinding verbreken en dat is via injectie veel ingewikkelder dan het draaien van een DROP query.

ALTER TABLE kan data corrumperen (alle versies behalve 5-STICT)

Als bij een ALTER TABLE het kolomtype verandert dan worden alle waardes die niet voldoen aan de nieuw specificaties vervangen door het dichtstbijzijnde wat wel voldoet:

Tabelnamen zijn soms case-sensitive, soms niet (alle versies)

MySQL slaat zijn tabellen op als bestanden waarvan de bestandsnaam gelijk is aan de tabelnaam. Helaas zijn bestandsnamen onder Windows niet hoofdlettergevoelig, maar onder linux, unix en BSD wel.

Dit levert veel problemen op wanneer systemen die op windows worden ontwikkeld worden gepubliceerd op een linux/unix omgeving. Hier kun je ook niet echt op testen omdat het een probleem is dat via het OS de database in komt.

Andere databases gebruiken de tabelnaam niet als bestandsnaam en hebben daarom geen enkel probleem hiermee.

ALTER TABLE is feitelijk CREATE/COPY/DROP (alle versies)

Als de structuur van een tabel wordt veranderd dan maakt MySQL een nieuwe tabel volgens de nieuwe structuur en kopieert de data uit de oude tabel naar de nieuwe.

Nadeel: Er wordt effectief een kopie van de tabel gemaakt. Dat kan alleen gebeuren als er voldoende vrije diskruimte is om dat te doen. Een tabel van 6GB kun je dus niet meer veranderen als je minder dan 6GB vrije ruimte hebt.

CREATE INDEX is eigenlijk ALTER TABLE en dus CREATE/COPY/DROP (alle versies)

CREATE INDEX wordt vertaald naar een ALTER TABLE statement en bij ALTER TABLE wordt de tabel gekopieerd naar een nieuwe tabel met de nieuwe structuur.

Nadeel: Er wordt effectief een kopie van de tabel gemaakt. Dat kan alleen gebeuren als er voldoende vrije diskruimte is om dat te doen. Een tabel van 6GB kun je dus niet meer veranderen als je minder dan 6GB vrije ruimte hebt.

DROP INDEX is eigenlijk ALTER TABLE (alle versies)

Hetzelfde verhaal, je kunt een index niet weghalen als er niet genoeg diskruimte is om een kopie van de tabel te bevatten.

Nadeel: Er wordt effectief een kopie van de tabel gemaakt. Dat kan alleen gebeuren als er voldoende vrije diskruimte is om dat te doen. Een tabel van 6GB kun je dus niet meer veranderen als je minder dan 6GB vrije ruimte hebt.

OPTIMIZE vernietigt de tabel als de disk vol raakt (alle versies)

OPTIMIZE gebruikt ook de 'CREATE/COPY/DROP' methode, maar nog erger dan ALTER TABLE. OPTIMIZE controleert niet of er genoeg ruimte vrij is om de tabel opnieuw te maken en het kan dus gebeuren dat OPTIMIZE halverwege stopt met 'disk full'. Wanneer dat gebeurt hoort MYSQL het nieuwe bestand te wissen en door de werken met het oude bestand, maar dat gebeurt niet. Het oude bestand blijft bestaan en de disk zit nu dus vol. Bij het begin van OPTIMIZE heeft MYSQL het .MYI bestand veranderd om aan te geven dat er een nieuw datafile bij gaat horen en dat moet nu dus worden teruggezet maar dat kan niet want de disk is vol. Gevolg: MySQL raakt in paniek en laat jou aan je lot over.

Een .MYI bestand kun je repareren met REPAIR, maar omdat het mislukte datafile niet is weggehaald is je disk vol en werkt REPAIR ook niet. De enige manier om dit te repareren is de tabel te droppen en opnieuw te maken (m.a.w. je database restoren van backup)

Alternatief formaat voor INSERT kan restores breken (alle versies)

MySQLDump maakt SQL files met daarin de inhoud van de database. Standaard gebruikt MySQL daar het "snellere" alternatieve INSERT formaat voor, die meerdere records in één statement kan invoeren.

Helaas is het daarmee mogelijk om een INSERT statement te maken dat langer is dan de maximum statementlengte die de commandline-tool accepteert. Resultaat: je backup werkt perfect, maar je kunt hem niet meer restoren.

Uiteraard is hier wel een oplossing voor; bij mysql_dump zelf opgeven wat de maximale querygrootte mag zijn (en dan wel de juiste waarde opgeven natuurlijk), of de oude (standaard) syntax laten gebruiken.

Maar het is belachelijk dat de standaard instelling een onbruikbare backup kan maken.

ZEROFILL verandert kolomtype (alle versies)

Als je zerofill gebruikt dan wordt je integer automatisch UNSIGNED. UNSIGNED kan geen negatieve getallen bevatten en dus rondt MySQL alle negatieve getallen af naar het dichtstbijzijnde dat wel past: nul. Als je een kolom met negatieve getallen op ZEROFILL zet dan veranderen alle negatieve waardes in nul.

CHAR(4+) en VARCHAR(4+) mogen niet samen in een tabel (<5.0.37)

Als er een of meer VARCHAR kolommen in een tabel is/zijn gedefinieerd dan worden alle CHAR kolommen met een lengte van meer dan 3 tekens omgezet naar VARCHAR.

MySQL's redenering hier achter: Bij een record met alleen CHAR kolommen is de lengte gelijk dus hoeft het niet voor elk record de lengte van de kolommen te worden gelezen. Zodra er een varchar kolom bij komt moet voor die kolom in elk record de lengte worden opgeslagen. Het lezen van die lengtes gaat volgens MySQL blijkbaar net zo snel voor 1 kolom als voor alle kolommen, dus is hun redenering dat je dan net zo goed alle CHAR kolommen om kunt zetten naar VARCHAR om zo wat bytes uit te kunnen sparen. Waarom VARCHAR van minder dan 4 tekens dan wel een CHAR wordt dat leggen ze niet uit, maar blijkbaar is dat dan weer wel sneller?!

Nadeel: CHAR gedraagt zich anders dan VARCHAR en het stilletjes veranderen van het type kan je applicatie breken.

VARCHAR mag niet kleiner dan 3 tekens (<5.0.37)

Logisch gevolg van de bug hierboven; Elke VARCHAR kolom van 3 tekens of minder wordt automatisch een CHAR kolom.

Dusse, CHAR heeft geen zin als er VARCHAR in de kolom staat, maar als de VARCHAR kleiner is dan drie tekens dan heeft het ineens weer wel zin?

VARCHAR van meer dan 65535 tekens wordt zonder waarschuwing een TEXT type (MySQL 5+)

Hier zit een fout in de handleiding van MySQL: " Starting with MySQL 5.0.3, a CHAR or BINARY column with a length specification greater than 255 is not silently converted. Instead, an error occurs." Maar sinds 5.0 is varchar maximal 65535 tekens, dus waarom zou dat een fout moeten opleveren?

Als je in MySQL 5.0.6+ zonder strict mode een VARCHAR kolom maakt van meer dan 65535 tekens dan wordt het kolomtype veranderd van VARCHAR naar een TEXT type dat qua afmeting overeenkomt met de opgegeven lengte.

In STRICT mode geeft het maken van een VARCHAR van meer dan 65535 tekens een error.

BEGIN commit eventueel openstaande transactions (alle versies)

Als je binnen een transaction opnieuw BEGIN geeft om een transaction te starten, dan wordt de transcation waar je al in zat afgebroken met een COMMIT!

Nadeel: er wordt gecommit zonder dat jij een commit opdracht geeft. Transacties bestaan om er voor te zorgen dat de veranderingen ongedaan worden gemaakt wanneer er iets fout gaat, maar als je per ongeluk twee keer BEGIN geeft dan worden de veranderingen juist doorgevoerd.

Correct gedrag is dat de transactie wordt afgebroken met de melding dat je geen transactie mag starten binnen een transactie. Hier zijn savepoints voor uitgevonden.

Stille conversie naar MyISAM

Als een backup een create-table uitvoert met het innodb type, en innodb is niet beschikbaar, dan wordt er zonder melding MyISAM gebruikt.

Gevolg: je FK relaties werken niet.

Workaround: stel je database in op sql_mode NO_ENGINE_SUBSTITUTION

Things that make you go hmmmm...

Naast de dingen die gewoon echt fout zijn zijn er ook een aantal zaken waarbij je je kunt afvragen waarom het is zoals het is, dingen waar je met de beste wil van de wereld geen goede erden voor kunt verzinnen...

EXTRAC( WEEK FROM datumveld) geeft getal voor datum 0000-00-00

Als een DATE veld gevuld is met 0000-00-00 en je selecteert er de EXTRACT(WEEK FROM veld) van, dan krijg je afhankelijk van de MySQL versie een getal ergens achter in de miljoenen, in plaats van de NULL die je zou verwachten als er geen weeknummer uit een datum gehaald kan worden.

Foreign keys vereisen identieke kolomtypes

Een van de meest frustrerende zaken bij het maken van Foreign-keys in MySQL is het feit dat een FK alleen kan bestaan tussen twee volkomen identieke veldtypes, dat wil zeggen dat de twee verbonden kolommen niet alleen beiden hetzelfde datatype moeten zijn, ze moeten ook nog eens exact hetzelfde zijn gedefinieerd. Een INT en een UNSIGNED INT kunnen niet gekoppeld worden, zelfs een INT(7) kan niet aan een INT(8) worden gekoppeld.

VIEWS kunnen een nietbestaande definer hebben

Maak een view, geef hem een als definer een bestaande user, dump de database, restore de dump op een database waar die user niet op bestaat; geen foutmelding van de restore. Bekijk de view: 'Error: There is no ....@.... registered.'

Met andere woorden, MySQL staat toe dat een view (en dus mogelijk ook een procedure) een fictieve definer heeft.

Gevaar: restore is niet betrouwbaar, en wie heeft er nu precies rechten op die view, en welke rechten zijn dat?

REPLACE triggert CASCADING DELETE (alle versies)

REPLACE vervangt een bestaand record door een ander, of maakt het aan als het nog niet bestond. Dat zou zondermeer handig zijn, ware het niet dat het is geimplementeerd als een DELETE om de oude data weg te halen, gevolgd door een INSERT om de nieuwe data in toe voeren. Die DELETE is in elk opzicht een gewoon DELETE statement en triggert dus ook eventuele Foreign key relaties met ecentuele cascading delete.

M.a.w. als je een klantentabel hebt met een ordertabel die via een FK verwijst naar de klanten, dan mag je de klantrecords niet veranderen met een REPLACE, want dan ben je alle orders van die klant direct kwijt.

Loze transactions bij mixen van InnoDB en MyISAM (alle versies)

Transactions zorgen er voor dat je kunt controleren dat een set veranderingen allemaal wel of allemaal niet worden doorgevoerd. Sluit af met COMMIT om de veranderingen door te voeren, of ROLLBACK om ze terug te draaien. De veranderingen die je tijdens een transaction maakt zijn niet zichbaar voor anderen en anderen kunnen de records die jij in een transaction hebt veranderd pas verandeen als je je transaction afsluit.

Dat is echter niet vanzelfsprekend bij MySQL. MySQL heeft meerdere table-handlers, waaronder MyISAM die geen transactions kent en InnoDB die ze wel kent. MySQL gebruikt standaard het MyISAM type omdat ze dat zelf gemaakt hebben terwijl InnoDB door een ander bedrijf is gemaakt.

Het gevaar is dat MySQL toestaat dat je in een database tabellen van verschillende types maakt. Je kunt in een database dus tabellen maken die wel transacties ondersteunen en tabellen die ze niet onderstuenen. Als je nu tijdens een transactie een MyISAM tabel verandert dan is de verandering in die tabel onmiddelijk doorgevoerd (er zijn immers geen transacties voor die tabel) en als je daarna een ROLLBACK geeft dan worden die veranderingen dus ook niet ongedaan gemaakt.

Gevolg? Je data is naar de kl*ten.

FLOAT is bij benadering correct (alle versies)

MySQL converteert FLOAT naar DOUBLE ergens in zijn berekeningen en dat gaat al sinds 2003 mis:

Geen controle op reeds bestaan prepared statement (4.1+)

Als je een prepared statement maakt met dezelfde naam als een eerder gemaakt en nog niet verwijderd statement, dan wordt het bestaande statement zonder melding overschreven met het nieuwe statement.

Ontbrekende functionalteit

Dit zijn functies die elke grote database heeft. Zelfs SQLite, die flatfile-based is (eenvoudiger kan het niet) heeft het merendeel van deze functies.

Geen getal uit auto_increment zonder een record in te voeren (alle versies)

Je kunt geen getal uit een auto_increment serie krijgen zonder een record te inserten. Andere databases hebben sequences waar je te allen tijde zonder INSERT query nieuwe getallen uit kunt ophalen.

Slechts één auto_increment kolom per tabel (alle versies)

Je kunt maar één auto_increment kolom per tabel maken. Dat maakt het b.v. onmogelijk om een dagteller en een maandteller naast elkaar te hebben lopen.

Auto_increment slechts in een tabel tegelijk te gebruiken (alle versies)

Auto_increment is een eigenschap van een kolom. Je kunt de waardes die het maakt dus niet gebruiken in meerdere tabellen tegelijk.

Geen LIMIT in subqueries (alle versies)

MySQL staat in subqueries geen LIMIT toe.

Geen triggers (MySQL <5)

Elementaire triggers zijn pas sinds MySQL 5 beschikbaar. Er zijn een aantal vervelende beperkingen zoals het niet kunnen uitvoeren van dynamische queries via een trigger.

Geen foreign-keys in MyISAM, alleen in InnoDB (alle versies)

Foreign keys werken alleen in InnoDB tabellen, maar je mag ze wel aanmaken in MyISAM, die ze vervolgens gewoon negeert.

Geen transactions in MyISAM, alleen in InnoDB (alle versies)

MySQL vind dat ook niet nodig: " More often than not, critical transactional updates can be rewritten to be atomic. Generally speaking, all integrity problems that transactions solve can be done with LOCK TABLES or atomic updates, ensuring that there are no automatic aborts from the server, which is a common problem with transactional database systems."

Kort samengevat: "je hebt geen transacties nodig want je kunt de tabellen op slot zetten tot je klaar bent, of alle updates gewoon afzonderlijk uitvoeren."

Ik kan geen woorden verzinnen om aan te geven hoe onwetend de persoon die dit schreef moet zijn geweest. Het concept van consistentheid gaat volledig aan hem of haar voorbij...

Geen nested transactions, ook niet in InnoDB (<5.0.15)

MySQL heeft in InnoDB sinds 5.0.15 nested transactions via savepoints.

Geen andere procedurele talen (alle versies < 5.1?)

Andere talen dan de standaard MySQL taal staan gepland voor versie 5.1.

CREATE TABLE kan niet in een transaction worden gebruikt (alle versies)

Omdat MySQL de storage engine van de tabel gebruikt om te beslissen of een tabel wel of niet transactioneel is is het niet mogelijk om het maken van de tabel zelf in een transaction te zetten.

Andere databases, die geen aparte tablehandlers hebben, kunnen dit wel en dat scheelt enorm wanneer een datamodel veranderd moet worden.

Geen stored procedures / functions (<5)

In MySQL 5 is het mogelijk om elementaire stored procedures en stored functions te schrijven.

Geen IP-address type (alle versies)

In MySQL is er geen IP adres datatype. Hier moet je omheen werken met functies als inet_atoi() en inet_itoa(). Dat is onnodig ingewikkeld en traag en je kunt er niets meer mee dan hoger/lager controleren.

In een echt INET type kun je ook subnet maskers toepassen en controleren of een adres in een bereik ligt.

Zeer beperkte locale support

In versies voor 5.0.24 is er geen locale support.

In versies > 5.0.24 kun je de benamingen van tijden en datums vertalen via lc_time_names

-- Stel de local in op Nederlands
SET lc_time_names = 'nl_NL';

-- Vraag de dagnaam van 1-jan-2010 op, dat was een vrijdag
SELECT
  CASE LOWER(DAYNAME('2010-01-01'))
  WHEN 'vrijdag'
    THEN 'ok'
  ELSE
    'FAIL'
  END AS dagnaam_nederlands;

Geen row-level locking in MyISAM (alle versies)

Geen rowlevel locking in MyISAM, alleen in InnoDB MyISAM gebruikt dus nog steeds table-level locking, wat erg traag is als er veel updates/delete tegelijk gebeuren.

Geen subqueries in de FROM in een view (5+)

Binnen een view kun je niet selecteren uit een subquery:

SELECT * FROM (SELECT * FROM tabel) AS subquery;

Gebruikt slechts een index per tabel per query (< 5)

MySQL zal voor een query slechts een index per tabel gebruiken. Je kunt er wel meerdere maken, maar MySQL kan er maar 1 tegelijk gebruiken.

key: idx_a. Er is wel een index op b, en die ziet hij ook, maar hij kan maar 1 index per tabel gebruiken.

Gevolg: om de waarde van 'b' op te halen moeten de records worden gelezen en dat is traag. Als je ook nog sorteert op 'b' dan is het helemaal hommeles want dan moet er een filesort worden gedaan en dat is nog trager.

Geen partial indexes (alle versies)

Geen partial-index support; de mogelijkheid om een index te maken op records met een bepaalde inhoud. Bv een aparte index voor elk jaar van de datum. Als je een query draait die alle gegevens voor een bepaald jaar moet ophalen dan heeft het geen zin om gegevens te lezen over de andere jaren. Met een partial index kun je een index maken die alleen de gewensde jaargang beschrijft zodat de rest niet eens wordt bekeken.

Geen partial UNIQUE constraints (alle versies)

MySQL kan geen unique constraint maken voor die records die aan bepaalde voorwaardes voldoen. Het is altijd alles of niets.

Dus geen mogelijkheid om b.v. af te dwingen dat de factuurnummers van voltooide bestellingen uniek moeten zijn terwijl de nummers van niet-voltooide bestellingen niet uniek hoeven te zijn (omdat er nog geen nummer voor _is_ bijvoorbeeld)

Geen functionele indexes (alle versies)

Geen functionele indexes, de mogelijkheid om indexes te maken op bewerkingen van data, bv op een substring. Bij MySQL kun je geen indexes gebruiken als je zoekt op bijvoorbeeld SUBSTR(filenaam FROM 10 FOR 3) Dat betekent dat dit altijd een sequential scan zal opleveren.

Filesort bij gebrek aan index (<4.1.7)

Als er geen index gebruikt kan worden dan doet MySQL een erg inefficiente filesort methode waarbij alle records twee keer worden gelezen. In MySQL 4.1.7 zou dit opgelost moeten zijn, maar omdat MySQL's explain niets vertelt over wat er wordt gedaan moeten we dat maar van ze aannemen.

Geen BOOL/BIT type (<5)

MYSQL < 4.1.7 kent geen BOOL. 4.1.7 < MySQL < 5 vertaalt BOOL naar TINYINT(1). MySQL >5 heeft wel een BIT type.

Als je 'true' of 't' of 'yes' aanlevert aan een TINYINT kolom die je als BOOL hebt aangemaakt, dan komt er '0' in de kolom te staan, en dat is gelijk aan FALSE. Alleen als je het MySQL keyword TRUE invoert zal het getal 1 in worden ingevoerd.

Dat betekent dus ook dat je 'true' als string in kunt voeren, en die records dan kunt vinden door op FALSE te zoeken:

Geen CHECK constraints (alle versies)

Het is in MySQL niet mogelijk om in de database op te geven aan welke voorwaardes een waarde moet voldoen om in de database geaccepteerd te worden. Bij een email adres is het b.v. niet mogelijk om af te dwingen dat er een @ in moet zitten.

Geen point-in-time recovery in MyISAM, alleen in InnoDB (alle versies)

Het is met MyISAM niet mogelijk om een backup terug te zetten tot een bepaald tijdstip. Dus als je met een query een tabel leegmaakt kun je wel de backup terugzetten, maar alle veranderingen die zijn gemaakt tussen het moment van de backup en het moment van jouw query die zijn verloren.

Geen goede hot backup (alle versies)

Hot backup wordt gedaan door de databestanden zelf te backuppen. In MySQL is het nog altijd nodig om de tabellen read-only te locken voordat dit op een veilige manier gedaan kan worden. Effectief moet de database dus buiten gebruik worden gesteld tijdens deze vorm van backup.

Geen goede crash-recovery (alle versies)

MySQL's crash-recovery bestaat uit het repareren van de datafiles. Als de datafiles door de crash teveel zijn beschadigd is het over en uit, terug naar de backup. Vrijwel alle andere databases werken met write-ahead-logs, bestanden waarin alle te-schrijven transacties worden opgeslagen en bevestigd voordat de hoofd data bestanden worden geopend. Tijdens een crash is dus altijd minstens een van de twee bestanden volledig in tact waardoor de database altijd zeker kan zijn van welke transacties al waren doorgevoerd, welke nog niet, en welke slechts gedeeltelijk. Op basis daarvan kan de database transacties ongedaan maken, opnieuw uitvoeren of afmaken om zo de datafiles en de logfiles te synchroniseren.

Gevaarlijke "handigheidjes"

ZEROFILL (alle versies)

Zerofill voegt voorloop nullen toe aan integers bij het uitlezen. Handig bij het printen van klantnummers en dergelijke, ware het niet dat getallen in programmeertalen geen voorloopnullen kunnen bevatten, dus zodra je b.v. in PHP een waarde als integer in een var zet: $iInt = (int) $value ben je alle voorloppnullen weer kwijt. Daarbij komt dat je de nullen altijd ophaalt, ook wanneer je het getal niet wilt printen. Dat betekent dat de records die je ophaalt een stuk groter zijn dan strict noodzakelijk en dat maakt het ophalen en verwerken van de data onnodig traag.

INSERT DELAYED: data invoeren zonder enige controle (alle versies)

Volgens MySQL is dit handig als je snel moet inserten het niet zo belangrijk is om te weten of het inserten gelukt is.

Wie kan mij een voorbeeld kan geven van een databasetoepassing waarbij het niet van belang is dat je records ook daadwerkelijk in de database komen?

Auto_increment

De auto_increment functie is een verhaal apart, vandaar een apart hoofdstuk.

Verkeerde foutmeldingen bij AUTO_INCREMENT fouten (alle versies)

Als je een SIGNED TINYINT AUTO-INCREMENT gebruikt voor een Primary-key dan kan de waarde daarvoor maximaal 127 zijn. Als je meer dan 127 keer insert dan gaat MySQL klagen over een dubbele waarde voor 127.

Waarom?

MySQL controleert niet of de invoer past in de kolom waar je het invoert. Als de auto_increment 127 bereikt dan zal MYSQL bij de volgende INSERT 128 gebruiken. 128 past niet in een SIGNED TINYTINT, dus MySQL rondt het af op 127. 127 bestond al en de kolom is een primary key, dus een duplicate-key error.

Hier hoort een 'value out of range' error te komen.

In MySQL 5 kunnen hier twee foutmeldingen op komen, de 'duplicate value' en de 'value out of range'.

Raar gedrag van auto_increment (<4.1.7)

De waarde van auto_increment loopt altijd op, maar als je het record waar de hoogste waarde in zat verwijderd dan zal bij de volgende INSERT dat getal opnieuw worden gebruikt.

Als je meerdere records tegelijk weghaalt, waaronder die met de hoogste waarde, dan gebeurt dit weer niet.

Nog meer raar gedrag van auto_increment (alle versies)

Als je de tabel leeg maakt met TRUNCATE dan begint de auto_increment sequence weer bij 1.

Waarom is dat niet goed: MySQL hergebruikt op deze manier getallen die op andere plekken in de database, of zelfs in backups en archieven nog in gebruik kunnen zijn. Dus als je oude records verwijderd om ruimte te sparen en een maand later moet je de records uit de backup terugzetten omdat er b.v. een rapport over moet worden gedraaid, dan kan dat niet meer omdat alle primary-key waardes die in de backup stonden opnieuw zijn gebruikt door nieuwe records.

zie ook: UNIQUE constraints geven niet altijd een error

Auto_increment is niet altijd betrouwbaar, zie "UNIQUE constraints geven niet altijd een error"

uitleg bij InnoDB

InnoDB wordt meegeleverd bij MySQL, maar is geen onderdeel van MySQL. Het is een table-handler die door een ander bedrijf is ontwikkeld. Het wordt tegenwoordig met MySQL meegeleverd omdat gebruikers zoveel moeite hadden met het vinden en installeren van InnoDB. (het zit er ook pas bij sinds 4.0) Mierenneukerig gezegd ondersteunt MySQL dus nog steeds geen transactions. Maar realistischer; MySQL gebruikt InnoDB niet als standaard tablehandler en de standaard tablehandler die ze wel gebruiken ondersteunt met opzet geen transactions.

Het mooie van meerdere table-handlers is dat je kunt kiezen wat je wilt, maar het nadeel is dat geen van de huidige table-handlers echt voldoet. Als je kiest voor InnoDB dan raak je weer andere dingen kwijt, zoals FULLTEXT indexes. Zie de mysql handleiding voor een lijst van dinden die InnoDB mist t.o.v. MyISAM.

MySQL <5.0

Bij INSERT ... SELECT mag er niet worden geselecteerd uit de tabel waarin wordt geinsert

is niet mogelijk, je kunt alleen inserten als je de data ergens anders vandaan haalt dan de tabel waarin je wilt inserten.

Nadeel: Als je records wilt kopieeren of klonen dan moet dat perse in tenminste twee queries.

Display lengte van INT voegt spaties toe aan uitvoer (MySQL <5)

De 'lengte' achter een INTeger heeft niets te maken met de inhoud van de kolom. Het getal zorgt er alleen voor dat de uitvoer van de kolom altijd minimaal X tekens lang is. Als het getal niet genoeg tekens bevat worden er spaties voor geplakt tot het wel de minimale lengte heeft. Als het getal meer tekens bevat dan de display lengte dan wordt gewoon het getal doorgegeven.

Dit gedrag is in MySQL 5.0.15 optioneel en kan dus worden uitgezet.

Volgens de standaard is int(1) een integer van 1 byte, dus wat MySQL een TINYINT noemt.

Nadeel: maakt de resultset onnodig groot; je applicatie doet helemaal niets met de spaties die de database aanmaakt, maar moet ze wel ophalen.

TIMESTAMP kan een displaylengte meekrijgen (<4.1.7)

In MySQL < 4.1.7 Kon je een lengte meegeven aan een TIMESTAMP, waardoor MySQL een datum+tijd anders interpreteerde dan je zou denken. Een timestamp bevat o.a. secondes, maar door de lengte van de tiemstamp twee tekens korter te maken interpreteert MySQL de data anders en worden de jaartallen gezien als maanden.

In MySQL >= 4.1.7 is de TIMESTAMP gewoon altijd een volledige TIMESTAMP en heeft hij hetzelfde formaat als de DATETIME. Je kunt nog wel een lengte opgeven, maar daar doet MySQL vannaf versie 4.1.7 niets meer mee.

Nadeel: maakt timestamp onbetrouwbaar; als je je vergist in de lengte gedraagt de timestamp zich heel anders dan je denkt, zonder dat je ooit ergens een melding krijgt.

TIMESTAMP wordt op eigen initiatief geupdate (<4.1.7, half opgelost in 4.1.7+)

In MySQL < 4.1.7 werd de eerst-gedefinieerde timestamp kolom in een MYSQL tabel automatisch geupdate naar de huidige datum bij elke verandering van een record (INSERT/UPDATE) Vannaf 4.1.7 kun je per TIMESTAMP kolom zelf bepalen of dit gebeurt ja of nee.

Zie: http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html

Nadeel: alleen de eerste timestamp kolom wordt geupdate en de kolom volgorde kan veranderen bij het maken van backups en restores. Na een restore kan het dus gebeuren dat de verkeerde kolom wordt geupdate, niet iets wat je wilt in een webshop waar nu ineens de betaaldatum verandert ipv de besteldatum.

UNIQUE constraints geven niet altijd een error (<4.1.7, helemaal opgelost in 5-STRICT)

Als je met een INSERT query een dubbele waarde invoert voor een kolom waar een UNIQUE constraint op ligt dan krijg je een error 1062: duplicate entry.

Echter, als je een dubbele waarde invoert door hem te selecteren uit een andere tabel via INSERT INTO ... SELECT ... FROM ..., dan krijg je geen error.

Er worden weliswaar geen records ingevoerd, maar de auto_increment waarde wordt wel verhoogd en wel aangeboden aan de client. mysql_insert_id() krijgt dus wel een nieuwe waarde, ookal is er géén record aangemaakt!

Nog erger; omdat het de auto_increment waarde niet in een record is gezet zal de eerstvolgende INSERT query dat getal opnieuw gebruiken. Het getal dat je na de INSERT INTO ... SELECT kreeg verwijst dus naar een record dat je in de toekomst zult gaan maken.

In 4.1.7 is dit "opgelost" door een instelling waarmee je kunt bepalen of de unique wel of niet een fatal error zal gaan geven. Dit is echter nog steeds optioneel en dus onbetrouwbaar. In 5-STRICT geeft het altijd een fatal error en wordt de auto_increment niet meer zinloos verhoogd.

Nadeel: unique is niet meer unique, onacceptabel!

MySQL <5.0 kent geen views

DROP PRIMARY KEY haalt eerste UNIQUE index weg (<5.0)

In MySQL 4.x haalt DROP PRIMARY niet de primary-key weg, maar de eerste UNIQUE die hij tegenkomt. Dus als je in MySQL 4.x geen primary-key in een tabel hebt gemaakt maar wel een UNIQUE index, dan behandelt MySQL die UNIQUE index als Primary-Key. Een DROP PRIMARY KEY opdracht haalt geheel onterecht de UNIQUE index weg, in plaats van een foutmelding dat er geen PK is om te droppen.

Geen subqueries (<4.1.7)

Subqueries zijn beschikbaar vannaf MySQL 4.1.7.