Interesting Problem with MySQL & SQLAlchemy

While at work a few days ago, I had an interesting albeit weird problem which started with the following cryptic error message:

ERROR 1005: can’t create table `qtn_formdisplay_product` (errno: 150)

It was produced by a local MySQL server running on my development machine when I tried to rebuild test database to accommodate for some model changes happening in the codebase. As you might have noticed, it’s not terribly informative, with the errno number as the only useful tidbit. A cursory glance at top search result for this message said that the most probable cause was a malformed FOREIGN KEY constraint inside the offending CREATE TABLE query.

Upon reading this, I blinked several times; something here was definitely off. The query wasn’t of course written by hand – if it was, we could at least consider an actual mistake to be a problem here. But no, it came from ORM – and not just an ORM, but the best ORM known to mankind. While obviously nothing is perfect, I would think it’s extremely unlikely that I found a serious bug in a widely used library just by doing something as innocent as creating a table with foreign key. I’m not that good, after all ;)

Well, except that it could totally be such a bug. The before mentioned search results also pointed to MySQL issue tracker where it was suggested that the error might happen after trying to create foreign key constraint with duplicate name. Supposedly, this could “corrupt” the parent table and no new FOREIGN KEYs could reference it anymore, yielding the errno 150 if we attempted to create one. While it could not explain the behavior I observed (the parent table was freshly created), it raised some doubts whether MySQL itself may be to blame here.

These were exacerbated when one of my colleagues tried out the same procedure, and it worked for him just fine. He turned out to use newer version of MySQL, though: 5.5 versus 5.1. This appeared to support the hypothesis about a possible bug in MySQL but it didn’t seem to help one bit to get the thing running on the older version.

However, it was an important clue that something relevant changed in between, that had an influence on the whole issue. It was not really any particular bugfix or new feature: it was a change of defaults.

Nawet SQL ma pętle

Stwierdzenie ‘programować w HTML’ jest rzecz jasna nadużyciem, ale istnieją przecież inne języki, dla których określenie ‘programistyczne’ (lub jego brak) nie jest wcale takie oczywiste. Weźmy choćby SQL, teoretycznie pretendujący do miana języków deklaratywnych. Charakteryzują się one tym, że pisząc w nich określamy tylko to, co ma zostać zrobione – nie zaś jak. Na pierwszy rzut oka ma to sens: w końcu pisząc proste lub nawet całkiem skomplikowane zapytanie SELECT w ogóle nas nie interesuje to, przy pomocy jakich struktur danych zostanie ono wykonane i jaka pętla będzie się za nim kryć.
Bo przecież SQL nie ma pętli, prawda? :)

Ano właśnie nieprawda. Ponadto czasami są one jedynym wyjściem, jeśli mamy do czynienia z nieco bardziej skomplikowanymi danymi – jak choćby z jakąś hierarchią drzewiastą. Zwykle zapisuje się ją w relacyjnej bazie danych tak, że każdy element zawiera informacje o identyfikatorze elementu do niego nadrzędnego. Jest to całkowicie wystarczającą informacją do odtworzenia ich hierarchii.
Do takiego drzewka łatwo dodawać nowego elementy, ale ich usuwanie może być już problemem. Wyrzucenie jednej pozycji powinno bowiem oznaczać odcięcie całego poddrzewa – zwłaszcza że na pozostawienie osieroconych potomków często nie pozwoli sam silnik bazy danych, jeśli sprawdza poprawność relacji. Musimy zatem usuwać od dołu, a następnie przesuwać się w górę… Tylko jak niby zapisać to w postaci zapytania SQL?
Okazuje się, że nic prostszego. No, a przynajmniej okazuje się, że da się to zrobić:

Nawet bez specjalnej znajomości składni można się domyślić, co tutaj jest wykonywane. Oto używamy kursora (coś w stylu iteratora), żeby najpierw usunąć elementy podrzędne do tego, który zamierzamy wykasować. W tym celu dla każdego z nich wywołujemy po prostu tę samą procedurę. Na koniec dokonujemy usunięcia pierwotnego elementu, który teraz na pewno jest już liściem (nie ma żadnych potomków), więc może być wyrzucony bez przeszkód.

Całkiem proste, czyż nie? Można powiedzieć, że w każdym języku programowania algorytm ten wyglądałby podobnie… Sęk w tym, że tu właśnie tkwi problem. Bo skoro w rzekomo deklaratywnym języku SQL można (a w tej sytuacji nawet trzeba) używać takich narzędzi jak pętle czy rekurencja, to przecież nie różni się on wtedy niczym od “normalnych” języków programowania. Jeśli całą operację trzeba zakodować krok po kroku, to nie mamy już żadnej korzyści z filozofii polegającej na określaniu ‘co’, a nie ‘jak’.
Może więc znaczy to, że inaczej programować po prostu się nie da? :)