MySQL :: Stored Functions

Покрай питането на OLD FIEND, ми хрумна да си поиграя малко със съхранените функции в MySQL, но се натъкнах на нещо малко неочаквано. Не съм сигурен дали грешката е в мен или в MySQL, защото съм писал съхранени функции само на PostgreSQL досега, но ето какво става:

mysql> create table test (id int unsigned auto_increment primary key, name varchar(10));

Query OK, 0 rows affected (0.10 sec)



mysql> insert into test (name) values ('A'), ('B'), ('C');

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0



mysql> select * from test;

+----+------+

| id | name |

+----+------+

|  1 | A    |

|  2 | B    |

|  3 | C    |

+----+------+

3 rows in set (0.00 sec)



mysql> set @x := 1;

Query OK, 0 rows affected (0.00 sec)



mysql> delimiter //

mysql> create function inc () returns int

    -> begin

    ->   set @x := @x + 1;

    ->   return @x;

    -> end

    -> //

Query OK, 0 rows affected (0.00 sec)



mysql> delimiter ;



mysql> select inc();

+-------+

| inc() |

+-------+

|     2 |

+-------+

1 row in set (0.01 sec)



mysql> select inc();

+-------+

| inc() |

+-------+

|     3 |

+-------+

1 row in set (0.01 sec)



mysql> select inc();

+-------+

| inc() |

+-------+

|     4 |

+-------+

1 row in set (0.00 sec)



mysql> select inc();

+-------+

| inc() |

+-------+

|     5 |

+-------+

1 row in set (0.00 sec)



mysql> select inc();

+-------+

| inc() |

+-------+

|     6 |

+-------+

1 row in set (0.00 sec)



mysql> select id, name, inc() from test;

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> select id, name, inc() from test;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

ERROR:

Can't connect to the server

Everything louder than everything else...

Сървърът очевидно гърми и това трябва да се поправи.

Най смахнатото е, че не ще да гръмне под gdb :(

Много мразя такива бъгове дето не дават да ги дебъгваш

salle.online.bg/sig

Ще го пусна, но исках да съм сигурен, че не е bogus.

Everything louder than everything else...

[линк]

salle.online.bg/sig

[линк]

salle.online.bg/sig

Мерси, че каза за този бъг. Колегите вече умуват какво да го правят.

Въпросната случка е комбинация от "user defined variable" и извикване на такава във функция при това за всеки ред от таблица.

Друг път като ти гръмне mysqld не се чуди дали проблема е в теб :)

Между другото ще те помоля да добавиш коментар на [линк] и д акажеш с каква ОС гърми при теб защото нещо колегите не успяват да го повторят

salle.online.bg/sig

Добре, че refresh-нах клубчетата преди да събмитна в бъгс, че щеше да стане кофти дублиране...

Добавих и коментар, с Windows NT и MySQL 5.0.3 съм, свалено и инсталирано вчера от огледалото на Online.

Everything louder than everything else...

Във връзка с разкритията по бъга се сетих да питам MySQL планира ли да осъществи някакво кеширане на функциите? Това не знам изобщо дали е стандартно, но в PostgreSQL има три опции (IMMUTABLE, STABLE и VOLATILE), които указват по какъв начин извикването на функцията да се оптимизира.

[линк]

Та, в конкретния случай inc() трябва да се дефинира като некешируема, т.е. VOLATILE.

Надявам се да не се приеме като заяждане, но наистина има отношение към решаването на бъга.

А и не съм съвсем наясно с вътрешната имплементация на кеширането в MySQL и съответно - връзката с функциите.

Everything louder than everything else...

Надявам се да не се приеме като заяждане, но MySQL стриктно следва стандарта по отношение на съхранените Процедури и[ Функции като се започне от това, че поддържа и двата вида (И Процедури и Функции)

Никъде в стандарта поне до SQL 92 не се споменават ключови думи IMMUTABLE, STABLE и VOLATILE така, че не виждам защо да трябва да се дефинира функцията по нестандартен начин.

Честно казано ми е омръзнало да чета как понеже в PostgreSQL или в Oracle или в MSSQL това се прави така значи е правилно

Ами аз няма да твърдя, че в MySQL нещата са правилни, но пък ще си позволя удоволствието да се наслаждавам тихичко на опитите на много хора да си завъртят аргументите на 180 градуса от "MySQL грубо нарушава стандарта" на "Е да де то това MySQL го прави по стандарта ама той стандарта е глупав ..."

Колкото до "вътрешната имплементация на кеширането" - кеширането на кое?
Защото те кешовете в MySQL не са един и два. И нито един от тях не е стандартен между другото.

salle.online.bg/sig

според мен mysql си смени политиката към по-стрикно спазване на стандарта, а не хората мнението :-)

а какво наричаш нестандартен кеш (в спецификациите пише ли, кое може да се кешира и не) не е ли важното резултата да е ОК, а не как е постигнат (т.е. с кеш или без кеш)?

Аз не съм казал, че така е правилно... даже споменах, че не знам дали е стандартно. Просто попитах дали MySQL смята да прави нещо такова.

Колкото до спазването на стандарта, радостно е, че напоследък MySQL полага усилия да се придържа повече към него, дори и с цената на обратна несъвместимост. Макар че има толкова много нестандартни неща, и то на базово ниво, които трудно биха могли да бъдат приведени някога към стандарта.

Но макар и спазването на стандарта да е важно нещо, не това е най-важното за една база. По-важно е да върши работата, която ти трябва.

За кеширането, имах предвид кеша на заявките в контекста на съхранена (и не само) функция - доколкото въпросната буболечка има отношение към това.

Everything louder than everything else...

Вече е поправена

[линк]

Проблемът беше с т.нар. Кеш на Заявките (Query Cache) в който съвсем очевидно подобни заявки не трябва въобще да се записват.

"Очевидно" поради начина по който работи Qcache. Това е един страшно минималистичен кеш и поради това изключително бърз.

Той кешира текста на заявката и резултата от нея. При следващо подаване на същата заявка (буквално същата, символ по символ) то резултатът директно се извиква от кеша без дори да се обработва заявката наново. (Иначе казано дори през parser-а не минава)

От това става ясно, че не бива да се опитва дори кеширане на заявки включващи функция която не дава константен резултат. Т.е.

SELECT PI(), SEC_TO_TIME(ts) FROM x; -- може да се кешира защото резултатът винаги ще е един и същ е константа
SELECT RAND() * a, NOW() FROM x; -- не може да се кешира

Същото е и със съхранена функция или UDF а дори и с prepared statements - резултатът е неизвестен предварително и съответно кешът не върши работа.

Кешът между другото си пази списък на таблиците за които е кеширал заявки и всяка промяна по таблица "изхвърля" съответните заявки от кеша.

Та буболечката #9902 се състоеше в това, че Qcache се опитва да кешира SELECT INC() FROM x; и в един момент една от проверките за неправилно поведение сработва

sql/sql_cache.cc:930::

/* Check that we haven't forgot to reset the query cache variables
*/
DBUG_ASSERT(thd->net.query_cache_query == 0);

корекцията трябва да се появи с 5.0.4 разбира се благодарение и на Dakota

salle.online.bg/sig

Е, и с мен и без мен все някой някога щеше да го открие това.

А кръпката като гледам указва заявката да не се кешира, ако има съхранена процедура. Това осигурява коректни резултати, но точно VOLATILE, IMMUTABLE, STABLE парадигмата, която е разширение на стандарта, позволява изпържване (тунинговане) дори и на съхранените процедури.

Като излезе 5.0.4 ще мога и да отговоря на OLD FIEND как да си направи брояча без дупки - макар че изчистеният вариант би бил малко по-сложен от една променлива и една функцийка.

Everything louder than everything else...

Дакота ще чакам. :)))))
И ще те подсетя.

Защо въобще ти е функция за това което обясняваш?

Каква е разликата между твоята функция и простото
SELECT id, @x := @x + 1 FROM t;

?

Аз този трик го мразя защото нарушава релационния принцип но .... работи. При товаработи още от 4.0 насам

mysql> set @x:=0; select id, @x := @x + 1 from t1 limit 10;
Query OK, 0 rows affected (0.00 sec)

+-------+--------------+
| id | @x := @x + 1 |
+-------+--------------+
| 1848 | 1 |
| 32028 | 2 |
| 7146 | 3 |
| 11003 | 4 |
| 8879 | 5 |
| 32417 | 6 |
| 11936 | 7 |
| 9104 | 8 |
| 24058 | 9 |
| 26383 | 10 |
+-------+--------------+
10 rows in set (0.04 sec)

salle.online.bg/sig

Защото не успях да го докарам до този синтаксис. Все опитвах неща със SET и ми връщаше грешка в синтаксиса...

Everything louder than everything else...