Význam datového typu set v MySQL Už jste možná v MySQL někdy narazili na datový typ set, který umožňuje omezit hodnoty sloupce na seznam prvků. Přemýšleli jste někdy nad případy jeho použití? Jste si jisti, že chápete všechna jeho úskalí? V Mergadu nás jeho nepochopení, špatné použití a lidská nepozornost stály porušení integrity databáze, což nakonec vedlo k nutnosti obnovit produkční data ze zálohy.

Z imperativních programovacích jazyků už nejspíš znáte datovou strukturu set (česky množina), která se typicky používá pro práci s neuspořádaným seznamem prvků. Další důležitou vlastností množin je, že každý prvek se v nich může vyskytovat nejvýše jednou. Množiny ale nejsou výhradně doménou programovacích jazyků, nejčastěji se s nimi setkáváme v matematice, můžeme si jich však s trochou představivosti všimnout i v reálném světě – všichni máme nějakou množinu sourozenců (leč někdy prázdnou), přátel nebo třeba klíčů od bytu. Z matematiky si možná ještě vzpomeneme na množinu přirozených čísel, která obsahuje tak ohromné množství prvků, že je z lenivosti nikdy všechny nevypisujeme a raději je značíme velkým písmenem N.

V MySQL se množiny používají ke specifikaci sloupců v tabulkách. Například sloupec, který může nabývat pouze číselné hodnoty označujeme datovým typem integer, dále například sloupec označený datovým typem varchar může obsahovat libovolnou posloupnost znaků. Podobnou funkci má i datový typ set, jehož význam je však složitější a abychom jej pochopili, bude lepší se nejprve podívat na zápis definice následující tabulky:

CREATE table table1(
    id integer,
    elements set('ITEM_ID','URL','EAN','NAME')
);

Tabulka table1 výše obsahuje dva sloupce, první představuje číselný identifikátor záznamu, u druhého si prozatím vystačíme s informací, že je to množina elementů, které typicky najdeme v XML feedu, obsahující produktovou databázi nějakého e-shopu. Přidání nového záznamu do této tabulky pak provedeme následovně:

INSERT INTO table1 (id, elements) VALUES
(1, 'URL,EAN,NAME'),
(2, 'ITEM_ID,NAME');

Nyní si vypíšeme všechna data, která tabulka table1 obsahuje:

SELECT id, elements FROM table1;

Výstup:

id elements
1 URL,EAN,NAME
2 ITEM_ID,NAME

Výstup dotazu SELECT tedy odpovídá přesně tomu, co bychom očekávali. Pojďme teď zkusit přidat do tabulky záznam obsahující prvek, jenž není součástí specifikace sloupce elements:

INSERT INTO table1 (id, elements) VALUES (3, 'PRODUCTNO,EAN,NAME');
SELECT id, elements FROM table1;

Výstup:

id elements
1 URL,EAN,NAME
2 ITEM_ID,NAME
3 EAN,NAME

MySQL nám evidentně nepovolí přidat prvek, který chybí v množině prvků definovaných tabulkou. Zajímavé však je, že MySQL v tomto případě nevyhodí chybu (porušení integrity), ale pouze nepovolené prvky ignoruje, toto chování lze naštěstí změnit nastavením konfigurační konstanty sql-mode na STRICT_TRANS_TABLES, viz oficiální dokumentace. Co se však stane, pokud prvky přidáme do tabulky v odlišném pořadí, než ve kterém se nachází v definici tabulky?

INSERT INTO table1 (id, elements) VALUES (4, 'EAN,NAME,ITEM_ID');
SELECT id, elements FROM table1;

Výstup:

id elements
1 URL,EAN,NAME
2 ITEM_ID,NAME
3 EAN,NAME
4 ITEM_ID,EAN,NAME

Můžeme si všimnout, že u posledního záznamu se změnilo uspořádání prvků v množině tak, aby odpovídalo specifikaci sloupce elements. Jak se tedy vlastně set v MySQL chová? Záleží na pořadí prvků nebo nezáleží? Co se stane, pokud upravíme tabulku table1 a změníme pořadí prvků? Pojďme to zkusit:

ALTER TABLE table1 MODIFY elements SET('EAN','NAME','ITEM_ID','URL');
-- Query executed OK, 4 rows affected.

Výpis tabulky po úpravě sloupce elements:

id elements
1 EAN,NAME,URL
2 NAME,ITEM_ID
3 EAN,NAME
4 EAN,NAME,ITEM_ID

Jak vidíme, změnilo se i pořadí prvků u všech záznamů tak, aby odpovídalo specifikaci. Z toho plynou dva rozumné případy použití pro datový typ set:

  1. Nezáleží nám na pořadí, v jakém jsou prvky uloženy a chceme mít na úrovni databáze ohlídané, že všechny prvky jsou na seznamu specifikovaném schématem tabulky. Nevýhodou je, že neexistující prvek MySQL potichu ignoruje, což může způsobit nepředvídatelné chování.
  2. Záleží nám na pořadí, v jakém jsou prvky uloženy, chceme mít toto pořadí ohlídané na úrovni databáze a u všech záznamů stejné. Problém nastane, pokud budeme chtít pořadí u nových záznamů v tabulce změnit a u starých ponechat.

Jak jsme na to přišli v Mergadu#

Skutečnost, že MySQL změní pořadí prvků u starých záznamů, změní-li se pořadí ve specifikaci tabulky, jsme v Mergadu zjistili bohužel až shodou nešťastných náhod. MySQL totiž používáme k ukládání produktových dat, které nám zasílají e-shopy v XML feedech. Pokud netušíte, jak Mergado funguje a jak zpracovává produktová data e-shopů, doporučuji si přečíst tento článek.

První nešťastnou náhodou bylo, že jsme se někdy v dávných dobách vývoje naší aplikace rozhodli použít u velmi důležité tabulky datový typ set, a to na místě, kde by bylo vhodnější použít varchar nebo text. Dnes už MySQL v novějších verzích podporuje typ JSON, což by byla ještě lepší volba. Nejlepší by však pravděpodobně bylo tabulku normalizovat do první normální formy. Druhou nešťastnou náhodou bylo, že jsme později část aplikace refaktorovali, během čehož jsme posílili vazbu mezi dvěma tabulkami v naší aplikaci, tato vazba je znázorněna na obrázku níže:

Ilustrace problému.

V obrázku je naznačeno, že seznam elementů v tabulce table1 zastupuje jména sloupců v tabulce table2. Role druhé tabulky spočívá v tom, že pomáhá jednoznačně identifikovat produkty v e-shopu. Pokud se tedy změní pořadí, změní se i identifikátor každého produktu. Zatímco první tabulka je tedy relativně malá, druhá obsahuje desítky miliónů záznamů.

Osudovým rozhodnutím bylo aktualizovat schéma tabulky table1, přičemž jsme omylem promíchali pořadí prvků v definici sloupce elements. Následně Mergado začalo v průběhu zpracování dat vytvářet nové identifikátory produktů a tím se rozpustila vazba s dalšími tabulkami v databázi – problém se projevil například u ručních úprav produktů, které uživatelé v Mergadu provádějí, ty jsou totiž provázány cizím klíčem s identifikátorem produktu v tabulce table2.

Výsledkem byl asi 24 hodinový výpadek manuálních úprav produktů v Mergadu a nakonec obnova některých tabulek ze zálohy.

Závěr#

Vzhledem k použitelnosti datového typu set je pravděpodobně v mnohých případech nejlepší se množinám v MySQL zcela vyhnout a ukládat spíš text s využitím typů varchar nebo text, jde totiž o datové typy zavedené a dobře známe z jiných relačních databází. Na aplikační úrovni by se potom pracovalo se seznamem, který pořadí prvků zachovává. Ještě lepší by však bylo tabulku normalizovat, tím by klesla redundance a taky by se snížila pravděpodobnost vzniku anomálií v datech.

Autor: Pavel Dedík