MySQL NOT DETERMINISTIC function error

Adatbázis: SQL, XML DB

Sziasztok!

Küzdök egy problémával, felteszem itt is, hátha tudtok segíteni.
A feladat, hogy egy megrendeléshez az ügyfél könnyen megjegyezhető, de egyedi megrendelésazonosítót kapjon. Ehhez MySQL-t használnék, mert hogy az van. Viszont nem értek hozzá.

Amit elkövettem (és lokálisan, USB WEB Server-el működik is) az a következő SQL script:

CREATE TABLE IF NOT EXISTS `orders` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=1 ;

DELIMITER $$

CREATE FUNCTION `get_next_id`() RETURNS int(11)
BEGIN
DECLARE ID INT(11);

delete from orders;

insert into orders (ID) values (0);

SELECT LAST_INSERT_ID() INTO ID;

RETURN (ID);
END$$

DELIMITER ;

A cél, hogy a get_next_id függvény visszaadjon az előzőnél eggyel nagyobb értéket.
Viszont a függvény scriptjének futtatásakor a következő hibaüzenetet kapom:

#1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

A neten talált "SET GLOBAL log_bin_trust_function_creators = 1;" megoldás nem jó, nincs hozzá meg a jogosultság.

Mit tehetek esetleg, hogy működjön, amit szeretnék?

Gábor

  • 2406 megtekintés

( horse | 2018. 08. 22., sze – 20:12 )

Öööö... miért kell ehhez ez a függvény? Nem arról van esetleg szó, hogy van egy táblád valahol amiben rendelések vannak és abba kell valami egyedi azonosító?

Mert ha igen, akkor elvileg annyi elég lenne hogy felveszed azt az ID oszlopot abba a táblába úgy, ahogy a kódban is van:
`ID` int(11) NOT NULL AUTO_INCREMENT

Oszt ennyi.

Szia!
Az adatbázisban semmit sem szeretnék tárolni. Egyetlen dologra kell: tudjak egyedi azonosítót prezentálni a webes felület számára. A megrendeléseket nem itt tartom nyilván, az más úton jut el hozzánk.
Azért kellett volna a függvény, hogy egy hívás alatt történjen meg a beszúrás és az ID lekérdezése, mert abban a pillanatban, hogy a php-ból két külön hívást kell indítanom - márpedig egyelőre nem látok más megoldást - problémát fognak okozni a párhuzamos folyamatok:
1. lépés: új rekord beszúrása a táblába
2. lépés: utolsó beszúrt ID lekérdezése
Ha párhuzamosan ketten adnak fel rendelést (mivel ez egy webes alkalmazás, nem kizárt!), akkor előfordulhat az az eset, hogy a 2. lépés előtt befigyelt egy másik folyamat 1. lépése, így mindketten ugyanazt az ID-t kapják vissza. Ezt a kockázatot szerettem volna minimálisra csökkenteni.
Gábor

Szerintem teljesen tévúton jársz.

Ha nem tárolod el, akkor meg miért is fontos? A felhasználó nem tud hivatkozni rá nálad, hiszen nincs meg neked, hogy az X azonosítójú számla melyik.

Egyedi sorszámot, vagy egyedi azonosítót? Azonosítóra használj UUID-et, PHP is tud generálni. Sőt, azonosíót képezhetsz is, dátumból, nevekből, mindenféle olyan adatból, amelyeknek az összessége egyedivé teszi az adott számot.

Nem, nem járok tévúton. A teljes folyamat leírása hosszú és a konkrét problémától messzire vezet, ezért nem részleteztem.
Azért nem kell tárolni az így kapott azonosítót, mert a megrendelés - és annak generált azonosítója - más úton jut el a rendszerünkhöz (konkrétan e-mailben és ftp tárhelyre feltöltött, kötött formátumú txt állományban). Hogy miért így, teljesen mellékes, történelmi és praktikussági szempontok játszanak benne szerepet.
Eddig is generáltam megrendelés azonosítót php-ból így:

session_start();
$megrid = session_id()."_".$_SERVER['REQUEST_TIME'];

Ez mindaddig jó is, amíg az ügyfél csupán simplepayes kártyafizetéssel akar fizetni, mert ott nem számít a megrendelés azonosító hossza és kinézete (azt a rendszerek a háttérben adogatják át egymásnak), valamint garantáltan egyedi lesz. Viszont be szeretnénk vezetni az előreutalásos fizetési módot, ott nem mondhatom a vevőnek, hogy az utalás közleményrovatába írja be a következő szöveget: eom2ejnfdpuskir47irdnt8pk2_1535061590.
Ezért találtam ki, hogy kapjon egy bamba sorszámot, meg egy előtagot, amit az ügyfél sokkal egyszerűbben megjegyez (pl: X01547). Viszont ennek egyedinek kell lennie. Legegyszerűbb módszer erre, hogy adatbázis autoincrement mezője adja ezt az értéket, az adatbázis csupán erre kell.

Miért fontos NEKED, hogy az ügyfél mit jegyez meg egyszerűen? Miért fontos az ügyfélnek? Valaminek a megjegyezhetősége (pláne azonosítóé) azért fontos, hogy utána visszakereshető legyen.
De ha nem tárolod te le, akkor nem lesz nálad visszakereshető ez alapján az eldobós azonosító alapján a megrendelés. Ha meg az ügyfél magának akarja visszakereshetővé tenni, akkor majd úgyis iktatja, és ad saját maga azonosítót neki. És azt meg ő jobban fogja tudni, hogy neki mi a jó azonosító, mint te.

A leírásod alapján az utalásazonosító NEKED mégis fontos, akkor meg miért nem tárolod el?

Azért fontos nekem, hogy az ügyfél mit jegyez meg jól, mert ha az ügyfél előreutalással fizeti ki a megrendelését, azt nekünk be is kell tudnunk azonosítani. Ügyfélkörünk az idősebb korosztály, tőlük nem tudjuk elvárni az olyan technikai tudást, hogy Ctrl-C + Ctrl-V párossal másolja át az utalás közleményébe az azonosítót.
Olyan emberekről van szó, akiknek még bankkártyájuk sincs, vagy nem bíznak a netes kártyás fizetésben!

Mint írtam, letárolom az azonosítót, de nem az adatbázisban.

"Ha párhuzamosan ketten adnak fel rendelést (mivel ez egy webes alkalmazás, nem kizárt!), akkor előfordulhat az az eset, hogy a 2. lépés előtt befigyelt egy másik folyamat 1. lépése, így mindketten ugyanazt az ID-t kapják vissza. "

A LAST_INSERT_ID() az adott kapcsolat alapján adja vissza az utsó id-t. Egy másik kapcsolat insertje nem fog bezavarni.

Ki kell ábrándítsalak, nincs igazad! Az autoincrement mezők (illetve generátorok) minden adatbázismotorban tranzakción kívül álló műveletek, ezért a LAST_INSERT_ID() mindig a ténylegesen utoljára beszúrt rekord azonosítóját adja vissza, akkor is, ha azt egy másik tranzakció kezdeményezte. És ezt nem csak a tanult ismereteimre alapozva állítom, hanem teszteltem a módszert és bizony elő tudtuk állítani az általam leírt szituációt, tehát két külön megrendelés kapta volna ugyanazt az azonosítót.

Én nem írtam tranzakcióról.

LAST_INSERT_ID() ...

"The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions."

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#func…

Ha saját bevallásod alapján sem értesz a mysql-hez, akkor miért akarod megszakérteni?

"The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client"

Vagyis ha php oldalról, te beszúrsz egy új sort a táblába, majd megnézed last_insert_id() -t, akkor az neked bizony unique lesz és nem fogja befolyásolni egy más szálon történt esemény

// Happy debugging, suckers
#define true (rand() > 10)

MySQL-hez konkrétan nem értek, de más adatbáziskezelőkhöz igen (ha firebird, IB, MSSQL, Oracle lenne a szerveren, nem is lett volna kérdésem). Elég sok szakkönyvet is olvastam, általános elvként elég sokszor elhangzott, amit írtam.
De ha ezeknek nem is hinnék, a lefuttatott tesztek eredményei igazolták a problémát: Ugyan csak ketten generáltattunk azonosítókat párhuzamosan, de bizony előfordult, hogy ugyanazt az ID-t kaptuk vissza!

A környezet: MySQL, benne orders tábla egyetlen ID autioncrement mezővel.

A PHP kód három dolgot csinál:
1. először beszúr egy sort a táblába, hogy generálódkon a következő érték: INSERT INTO orders VALUES ();
2. Lekérdezi az utoljára beszúrt értéket: SELECT LAST_INSERT_ID();
3. A visszakapott értéket megjeleníti az oldalon.

A teszt úgy zajlott, hogy a teszt weblap egy szövegre kattintgatva jquery + ajax segítségével kért egy azonosítót és azt belerakta egy listába.
Ezt az oldalt ketten a kollégámmal betöltöttük és elkezdtük vadul kattintgatni a szöveget. Átlag 5-6 értéket tudtunk kérni másodpercenként. Ezek után összehasonlítottuk a két listát és volt benne egyezés!

Ha a pontos kódra is kíváncsi vagy, kedden tudom mutatni, akkor leszek legközelebb.

A PHP kód:

<?php
function Get_Next_ID()
{
$prefix = "X";
$servername = "localhost";
$username = "dbuser";
$password = "dbjelszo";
$dbname = "adatbazisnev";

$ret = "";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if ($conn)
{
$sql = "INSERT INTO orders () VALUES ()";
$stmt = mysqli_prepare($conn, $sql);
mysqli_execute($stmt);

$sql = "SELECT LAST_INSERT_ID() as ID";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0)
{
$row = mysqli_fetch_assoc($result);
$ret = $row["ID"];
}

mysqli_close($conn);
}

$ret = "000000".$ret;
$ret = substr($ret, strlen($ret)-5, 5);

return $prefix.$ret;
}

?>

http://php.net/manual/en/features.persistent-connections.php

Valószínű, hogy igazából NEM nyitottál új kapcsolatot MySQL felé.
A LAST_INSERT_ID pedig kapcsolatonként értelmezendő.

Simán előfordulhat az alábbi:

1. $conn = mysqli_connect($servername, $username, $password, $dbname); nem nyit kapcsolatot, hanem újrahasznál egy connection poolban lévőt.
2. Ezután mindkét script futás az (ugyanazon a MySQL kapcsolaton keresztül) végrehajtja az INSERT-et
3. Ezután mindkét script futás a másodikként lefutott insert LAST_INSERT_ID-jét adja vissza.

Teljesen ésszerű dolog. De hát többszálú programozáshoz érteni kell.

Tehát, ha jól értem:
1. Nincs kontrollotok alatt a MySQL szerver (https://hup.hu/node/160523#comment-2259584), pedig ez kellene ahhoz, hogy olyan függvényt tudják írni, ami neked kell.
2. Nincs kontrollotok alatt a PHP beállítása a connection poolingra (https://hup.hu/node/160523#comment-2260699), pedig ez kell ahhoz, hogy a LAST_INSERT_ID() értéke azt adja, amit szeretnél.

Persze, amit még meg tudsz tenni, az az, hogy tranzakciókat használs, és a LAST_INSERT_ID lekérdezését és az INSERT-et egy darab tranzakcióban hajtod végre.
Ez meg fogja oldani a problémádat. De ahhoz, hogy ez jó legyen, ahhoz az kell, hogy a transaction isolation level jól be legyen állítva, hogy legalább READ COMMITED legyen.
Ezt ugye te csak MySQL sessionönként tudod megtenni, mert ahhoz, hogy GLOBAL szinten is a tranzakcióizoláció jó legyen, SUPER jogosultság kell, az meg nincs neked.
Ekkor viszont minden session-ben meg kell mondanod az elején, hogy SET SESSION TRANSACTION ISOLATION LEVEL = 'READ COMMITED'.
Erre figyelned kell.
Persze tranzakciók csak InnoDB táblák esetén használhatóak, MyISAM táblák esetén nincs értelmük.

Szóval jópár apróságnak kell jól beállítva és jól használva lennie, hogy a problémád helyesen megoldódjon.
És ez után még mindig MySQL műveletekkel akarod megoldani a problémádat. Én ezt gondnak látom.

Én még nem láttam fehér embert, aki persistent kapcsolatot használt volna. Pláne olyat nem, aki ezt úgy tette, h igazából nem is tud róla. Sztem itt sincs erről szó.

2. Na ez az ami sztem nem képzelhető el. (nem azt jelenti h a processek kedvükre választanak maguknak a [z akár] használatban lévő kapcsolatok közül. Mindig csak a poolban lévő szabad kapcsolatot kaphatja meg - ha nincs szabad várnia kell -, ilyen formán kizárt, h két process egyidőben használjon azonos kapcsolatot)

A topiknyitó által leírt jelenség teljesen abszurd.

( dozsi | 2018. 08. 23., cs – 23:42 )

MODIFIES SQL DATA a BEGIN elé (plusz NOT DETERMINISTIC még a MODIFIES SQL DATA elé)?

Bár az nem garantált, hogy egyel nagyobbat fogsz mindig kapni. Ehhez lock kellene, ahogy fentebb is írták.

( Luckye | 2018. 08. 24., p – 10:02 )

Pillanatnyilag megoldottam a problémát, bár nem teljesen olyan módon, ahogy azt eredetileg elképzeltem.
A módszer a következő:

  1. Készítettem egy egy táblát két mezővel:
    1. ID: autoincrement mező
    2. SessioID: varchar mező
  2. A php első lépésben generál egy egyedi azonosítót úgy, mint eddig:
    1. session_start();
    2. $megrid = session_id()."_".$_SERVER['REQUEST_TIME'];
  3. Az így generált - ténylegesen egyedi, de nehezen olvasható - azonosítót beszúrom az adatbázisba
  4. Lekérdezem a generált azonosítóhoz tartozó ID értékét

Ezzel megoldódott a problémám.
Gábor

Sem a PHP-t, sem a MySQL-t nem ismerem, viszont rogton felmerult bennem a kerdes, hogy miert fuggvenyt probalsz deklaralni, miert nem stored procedure-t?

Ha jol ertelmezem, kivulrol hivod, raadasul az adatbazis allapotat modositod (tablaba inzertalsz/torolsz), a fuggveny nem erre valo.

Egy darab ID-ért feltenni egy MySQL-t eléggé ágyúval a verébre. Ettől van egyszerűbb, pont annyira favágó módszer: egy mappában létrehozol egy $megrid nevű fájlt, sorba rendezed a mappában a fájlokat előbb létrehozási dátum majd név szerint, és a mappán belüli sorszám lesz az azonosító. Vagy még ettől is jobb: egy text fájlban tárolod az utolsó kiadott sorszámod, és file locking segítségével garantálod, hogy egyszerre ne tudja két folyamat írni/olvasni.

Nem tudom, de jo parszor ha vasaroltam a netrol, es ilyen egyedi kodot kaptam, akkor azt ugye megkaptam emailben (vagy meg az oldalon letoltheto pdf-ben) hogy amikor majd utolag utalom a penzt legyek kedves ezt az azonositot beirni a kozlemeny mezobe.

Na most nalad egy sima algoritmus is eloallithatna ezt ahol a "vasarlod" valamilyen id-jet, a "termek" valamilyen id-jet es egy timestampet/fate-t hasznalsz es mondjuk nem lesz hosszabb a generalt string 10-12 karakternel.

https://api.random.org/json-rpc/1/basic - aztan generalsz 10 jegyu random azonositokat per tranzakcio

Már írtam. Alapvetően idősebb korosztályról van szó, akiknek még az is kihívás, hogy a 6 jegyű ügyfélazoosítójúkat megjegyezzék, ami minden számlán, emailben szerepel. Ennél hosszabb azonosítót nem generálhatok nekik. Ők annyira antitalentumok az informatikában (és ezt nem bántásnak szánom, nem is várom el tőlük), hogy a Ctrl-V + Ctrl-C nekik már űrtechnika.

Lehet, hogy igy van, de szerintem egy egyzeru mondatot "Kerem a vastagon szedett azonositot irja be a banki atutalas kozlemeny rovataba" eleg nekik, hogy amikor elkuldik a penzt elovegyek a papirt, pdf-et, akarmit es onnan bepotyogjek. Ezt meg a nagyanyam is tudja (86 eves) aki nem okostelefont hasznal, de ir es olvas betuket amit papiron kap, vagy anyam mutatja meg neki egy laptop kepernyojen.

:) Van tapasztalatod ügyfelekkel? Nekem van... Szerinted mit lehet elvárni az olyan ügyfelektől például, akik megrendelik az árut, megkapják postán, vele együtt a számlát rajta a sárga csekkel. Nem fizeti ki, kap utána 3 felszólítást, mindegyiken egy-egy csekkel, aztán az utolsó felszólításnál elbattyog a postára és befizeti mind a 4 csekket...
Szóval nem véletlenül vannak az elvárásaim, nem fogtok tudni meggyőzni az ellenkezőjéről.

"Szerinted mit lehet elvárni az olyan ügyfelektől például, akik megrendelik az árut, megkapják postán, vele együtt a számlát rajta a sárga csekkel. "
Erre való a futár és a futárnál való fizetés. Nem véletlenül csinálják ezt, a csekkes szarakodás helyett. Amennyit elköltesz ügyfélsupportra, annyiból majdnem ingyen is adhatod a futárszolgálatot.

Sokadszor is elmondom: nem az egyediséggel van a bajom, hanem az egyediséggel ÉS A JÓL MEGJEGYEZHETŐSÉGGEL!
Ha csak unique kellene, azt már rég megoldottam.
De ha a DB-t erre gánynak érzed, kérlek, írj egy szebb megoldást. Könnyű a másik ötletét, megoldását leszólni, hogy közben nem írsz semmit.

Például nagyon jó tud lenni egy egyszerű ügyfélazonosító és a dátum, ha éppen egy ügyfél egy napon csak egy megrendelést ad le (hiszen tipikusan idős emberek, bankkártya nélkül).
Ekkor bizyon az ügyfélszám-dátum, például 1234-20180824 egy tökéletesen jól képezhető azonosító, ami nagyon könnyen meg is jegyezhető, még logika is van benne, és ha elfelejtené az idős bácsika, akkor is újra tudja képezni saját maga is. És még adatbázis se kell hozzá.

Ki mondta, hogy az ügyfél magának generál azonosítót? Regisztrációkor nem kap? Lehet az azonosítója e-mail cím is, bármi, amit ő ad meg, amiről te őt be tudod azonosítani. Nem kell, hogy autoinkrementált adat legyen.
Azonosíthatod az ügyfelet akár a számlaszámáról is, és akkor még ügyfélazonosító sem kell, csak egy dátum, meg az, hogy honnan jött az befizetés.

A problémádat sokféle képpen meg lehet oldani, és az, hogy az ügyfeleidnek mi lesz kényelmes, meg könnyen megjegyezhető, azt az ügyfelek fogják tudni, és nem a MySQL.
Nyilván, ha neked csak kalapácsod van (MySQL), akkor mindent szögnek (MySQL autoincrement mezőnek) akarsz látni.

UX szempontból amúgy a kitalálható ID-k nem a legjobbak: valaki félregépeli egy számjeggyel a sorszámot, ami egy valid sorszám lesz, és máris másvalakinek a megrendelését fizette ki.
Egy véletlenszerű, vagy éppen a megrendelőazonosítót (ami nem feltétlenül sorszám ám) is tartalmazó megrendelésazonosító védettebb a vétlen elgépelések ellen, ami tipikusan fontos idős embereknél.

( asch v | 2018. 08. 31., p – 10:34 )

Az alapproblémát egyébként úgy lehetne még jobban megoldani, ha feltöltenél egy szótárat, és számok helyett szavak lennének a kulcsok. (leválogatva a rövidek, amik beférnek a közlemény mező egy sorába)

Amit már kifizettek, azt meg lehet jelölni, és egy idő után újrahasznosítani. Így nem fogynak el soha.

Egy értelmes szót sokkal könnyebb átmásolni, mint egy random számot.

A különféle futárszolgálatok is sok esetben előre definiált tracking numberekkel dolgoznak, amiket újrahasznosítanak. Ez azért jó nekik, mert így az árut feladó emberkének van allokálva X db tracking number, és amikor felad egy terméket, megadja hozzá a tracking numbert, amit a futárszolgálat használ, és kiadhatja a vevőnek is.