Hledáme nového kolegu do redakce - 100% home office, 100% flexibilní pracovní doba. Více informací.
Využij akce až 80 % zdarma při nákupu e-learningu - více informací. Zároveň pouze tento týden sleva až 80 % na e-learning týkající se Swift
Avatar
Peter Schoeller:17.4.2018 12:07

Zdravim komunitu.
Potrebujem poradit s zjednodusenim spustaca.
Scenar: mam stranku, na ktoru je pristum len pre clenov, tzn, kazdy sa musi zaregistrovat, verifikovat email. Na uvodnej stranke mam taktiez moznost obnovy hesla. Tzn, mam v DB tri tabulky - members, confirm_email, recovery_key
Po uspesnej registraci dostane novy clen verifikacny email a po verifikacii sa zaznam v DB vymaze. Rovnako tak aj pri uspesnom zmeneni hesla.
V poslednej dobe som si vsimol, ze sa snazia zaregistrovat aj nezelani navstevnici s neexistujucim (ale realne vyplnenym) emailom. Niekedy sa tito "nezelani navstevnici" pokusaju dostat na stranku aj cez obnovu hesla.
Vytvoril som si preto tento trigger, aby po uplynuti 1 dna sa vymazal zaznam zo vsetkych uvedenych tabuliek:

DELETE confirm_email, members, recovery_keys
FROM confirm_email
INNER JOIN members ON confirm_email.email = members.email
INNER JOIN recovery_keys ON members.email = recovery_keys.email
WHERE date_confirm < DATE_SUB(NOW(), INTERVAL 1 DAY)

Funguje perfektne, pokial "nezelany navstevnik" okrem registracie prevedie aj obnovu hesla. Tzn ze zaznamy su vo vsetkych troch tabulkach. Ale ked sa len zaregistruje trigger nefunguje. Musel som vytvorit este jeden:

DELETE confirm_email, members
FROM confirm_email
INNER JOIN members ON confirm_email.email = members.email
WHERE date_confirm < DATE_SUB(NOW(), INTERVAL 1 DAY)

Takze ak mam obidva triggre, vsetko funguje k mojej spokojnosti, ale rad by som to zjednodusil.
Otazka znie:
Je mozne (a ak ano, ako) vytvorit len jeden trigger, aby fungoval pre obidva pripady?
Dakujem za vase napady.

 
Odpovědět
17.4.2018 12:07
Avatar
Peter Schoeller:17.4.2018 12:16

oops, v popise situacie mam chybu, spravne ma byt recovery_keys
Zdrojaky su napisane spravne.

 
Nahoru Odpovědět
17.4.2018 12:16
Avatar
Odpovídá na Peter Schoeller
Michal Štěpánek:17.4.2018 12:49

Můžu se zeptat, k čemu u tohoto dotazu slouží ten "INNER JOIN"?
Podle mě se JOIN používá pro výpis, ale pro mazání je tam zbytečný...

Editováno 17.4.2018 12:50
Nahoru Odpovědět
17.4.2018 12:49
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
Odpovídá na Michal Štěpánek
Peter Schoeller:17.4.2018 13:29

Nie je zbytocny. Bez neho by som musel mat dva prikazy "DELETE". Pre kazdu tabulku zvlast.
Takto jednym prikazom DELETE vymazem z obidvoch tabuliek zaznamy s rovnakou podmienkou - v mojom pripade je to email ako jedinecna hodnota v kazdej tabulke. Takze ak najde vo vsetkych tabulkach zaznam s rovnakym emailom, tak ich vymaze prislusne riadky.

 
Nahoru Odpovědět
17.4.2018 13:29
Avatar
Odpovídá na Peter Schoeller
Michal Štěpánek:17.4.2018 13:31

Tak zkus místo INNER JOIN použít OUTER JOIN...

Nahoru Odpovědět
17.4.2018 13:31
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
Odpovídá na Michal Štěpánek
Peter Schoeller:17.4.2018 13:38

S JOIN-mi problem nie je.
Problem je, ze ak v tabulke recovery_keys nie je zaznam, tak DELETE z tabuliek members a confirm_email sa podla mojho prveho Triggeru nevykona.

 
Nahoru Odpovědět
17.4.2018 13:38
Avatar
Odpovídá na Peter Schoeller
Michal Štěpánek:17.4.2018 13:41

A o to právě jde. INNER JOIN funguje, pokud jsou záznamy ve všech dotčených tabulkách, OUTER JOIN vybere i ty záznamy i které nejsou ve všech tabulkách...

Nahoru Odpovědět
17.4.2018 13:41
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
Odpovídá na Michal Štěpánek
Peter Schoeller:17.4.2018 13:51

Nie, OUTER nefunguje. :-|

 
Nahoru Odpovědět
17.4.2018 13:51
Avatar
Mouser
Člen
Avatar
Odpovídá na Peter Schoeller
Mouser:19.4.2018 1:28

Nastav si kaskádu. To znamená, že kdykoliv se smaže záznam v "rodičovské" (primární) tabulce, smažou se automaticky i odpovídající záznamy v podřízené tabulce. Používá se to, když tabulky popisují věci, které jedna bez druhé (podřízená bez nadřízené) nemají smysl. V tvém případě bude rodič members a dítě recovery_keys.

Jak na to: buď to jde naklikat nebo spusť následující skript:

ALTER TABLE recovery_keys ADD FOREIGN KEY (email) REFERENCES members (email) ON DELETE CASCADE

Správně by se nemělo svazovat podle emailu, ale podle rodičova Id, ale to teď nechme stranou.

Pak ti bude stačit jen ten druhý trigger. Ten smaže membera, a spolu s ním se díky kaskádě smažou i jeho záznamy z recovery_keys, pokud existují. Pokud ne, smaže se prostě jenom member (spolu s tím confirm_emailem, samozřejmě). Důležité je říct, že kaskáda funguje vždycky, takže memberovy recovery_keys se ti smažou i když vymažeš membera kdykoliv jindy, nejenom tím triggerem.

 
Nahoru Odpovědět
19.4.2018 1:28
Avatar
Odpovídá na Mouser
Peter Schoeller:19.4.2018 16:07

Diky moc za odpoved, ale neviem si s tym poradit. :-S

 
Nahoru Odpovědět
19.4.2018 16:07
Avatar
Mouser
Člen
Avatar
Mouser:19.4.2018 18:26

Co přesně ti nejde? Prostě spusť ten skript a smaž ten první trigger a mělo by to fungovat.

 
Nahoru Odpovědět
19.4.2018 18:26
Avatar
Odpovídá na Mouser
Peter Schoeller:19.4.2018 19:52

Snad z obrazku to bude vidiet.

 
Nahoru Odpovědět
19.4.2018 19:52
Avatar
Mouser
Člen
Avatar
Odpovídá na Peter Schoeller
Mouser:19.4.2018 21:08

Ten řádek s tím ALTER TABLE nedávej do toho triggeru, spust ho jen jednorázově.

 
Nahoru Odpovědět
19.4.2018 21:08
Tento výukový obsah pomáhají rozvíjet následující firmy, které dost možná hledají právě tebe!
Avatar
Odpovídá na Mouser
Peter Schoeller:19.4.2018 22:44

No prave tomu sa chcem vyhnut. Ja chcem, aby to bezalo automaticky na serveri.
Tie moje dva triggery vykonavaju presne co chcem.
Myslienka bola, ci by nebolo mozne spojit to do jedneho. Jedna uloha = jeden trigger.
Keby to bolo na php, je to lahko osetrene cez if a else, takze som hladal ci nie je mozne aj kaskadu napisat nejak podobne.

 
Nahoru Odpovědět
19.4.2018 22:44
Avatar
Mouser
Člen
Avatar
Odpovídá na Peter Schoeller
Mouser:19.4.2018 23:00

Teď ti moc nerozumím, to poběží automaticky. Tu kaskádu stačí nastavit jen teď jednou, od té doby bude fungovat pořád. Aniž bys musel cokoliv dalšího dělat, zavolá se automaticky pokaždé, když se spustí ten trigger.

 
Nahoru Odpovědět
19.4.2018 23:00
Avatar
Peter Schoeller:19.4.2018 23:06

Ked to skusam spustit samostatne, dostanem tuto chybu:

Chyba v dotaze (1005): Can't create table `xxxxxx`.`#sql-241c_3852` (errno: 150 "Foreign key constraint is incorrectly formed")
 
Nahoru Odpovědět
19.4.2018 23:06
Avatar
plelovsky
Člen
Avatar
plelovsky:20.4.2018 14:43

Psotě si udělej 2 FK - v tabulkách recovery_keys a members:

ALTER TABLE members ADD FOREIGN KEY (email) REFERENCES confirm_email (email) ON DELETE CASCADE
ALTER TABLE recovery_keys ADD FOREIGN KEY (email) REFERENCES members (email) ON DELETE CASCADE

Toto provedeš pouze jednou, ne při každém mazání.
Tím se zajistí, že

  • při smazání záznamu z confirm_email se automaticky smažou související záznamy z members.
  • při smazání záznamu z members se automaticky smažou související záznamy z recovery_keys.
 
Nahoru Odpovědět
20.4.2018 14:43
Avatar
Mouser
Člen
Avatar
Odpovídá na plelovsky
Mouser:22.4.2018 15:01

Tu druhou kaskádu ať určitě nedělá. :-) Psal, že po úspěšné verifikaci záznam v DB smaže (předpokládám, že myslí záznam v tabulce confirm_email), tím by si smazal i člena. :-) Jak jsem psal, kaskáda by měla být jen tam, kde záznamy v tabulce-dítěti bez záznamu v rodičovské tabulce nemají smysl.

Peter Schoeller: Ještě asi bude potřeba nastavit sloupec email v members jako unikátní. Takže nejdřív zkus spustit tohle:

ALTER TABLE members ADD UNIQUE (email)

A pak až tohle:

ALTER TABLE recovery_keys ADD FOREIGN KEY (email) REFERENCES members (email) ON DELETE CASCADE

Kdyby ani to nefungovalo, tak holt gůgli, ta chyba může být cokoliv. Dobré odpovědi jsou třeba tady.

Jinak podle tohohle by v delete měl fungovat i LEFT JOIN, takže pokud máš problémy s kaskádou, můžeš zkusit přepsat ten trigger takhle:

DELETE members, confirm_email, recovery_keys
FROM confirm_email
INNER JOIN members ON confirm_email.email = members.email
LEFT JOIN recovery_keys ON recovery_keys.email = members.email
WHERE date_confirm < DATE_SUB(NOW(), INTERVAL 1 DAY)
Akceptované řešení
+20 Zkušeností
+1 bodů
Řešení problému
 
Nahoru Odpovědět
22.4.2018 15:01
Avatar
Odpovídá na Mouser
Peter Schoeller:22.4.2018 15:59

Diky moc, idem testovat dalej :-D
Tu prvu podmienku mam splnenu uz pri registracii, kde pred INSERT dotaz skontrouje ci sa uz email v DB nachadza.
Ano, mas pravdu, confirm_email sa po uspesnej verifikacii zmaze.
Len v pripade, ak novy clen, ktory si neverifikuje ucet (prepokladany utocnik) bude po 24 hodinach vymazany. Nielen z confirm_email, ale aj z tabulky members.
A ako som pisal, niekedy (nie vzdy) to skusaju aj cez reset hesla. Tym sa dostane zaznam aj do tabulky recovery_keys. A kedze email zadany pri registracii neexistuje (lebo nie je ani verifikovany - priklad: abcd@example.com - ma platnu strukturu, ale realne neexistuje), tak mi potom zbytocne v tych tabulkach (recovery a confirm) ostavaju zaznamy.
Doteraz mam tam tie dva triggre. Oba funguju spolahlivo len otazka bola, ci by to slo jednym. :-)

 
Nahoru Odpovědět
22.4.2018 15:59
Avatar
Mouser
Člen
Avatar
Odpovídá na Peter Schoeller
Mouser:22.4.2018 16:30

Tu prvu podmienku mam splnenu uz pri registracii, kde pred INSERT dotaz skontrouje ci sa uz email v DB nachadza.

Jj, to děláš dobře, ale to je něco jiného. Ta unikátnost musí být nastavená vyloženě jako vlastnost toho sloupce, bez toho nepůjde nastavit ten foreign key. (Aspoň tak praví google.)

Případně pokud nechceš řešit kaskádu, tak prostě zkus ten trigger s tím left joinem. Pak bys druhý trigger neměl potřebovat. Vyzkoušej a dej vědět. :-)

 
Nahoru Odpovědět
22.4.2018 16:30
Avatar
Odpovídá na Mouser
Peter Schoeller:22.4.2018 17:00

Uz pri tom prvom pokuse (ALTER TABLE) som dostal tutu chybu:

Chyba v dotaze (1062): Duplicate entry '' for key 'email'

Co robim zle? :-D
DB je MYSQL, INNODB

 
Nahoru Odpovědět
22.4.2018 17:00
Avatar
Mouser
Člen
Avatar
Mouser:22.4.2018 17:53

Pravděpodobně máš v members několik záznamů, které mají ve sloupci email jako hodnotu prázdný textový řetězec. Můžeš si to prověřit:

SELECT COUNT(1) FROM members WHERE email = ''

Když bude výsledek větší než 1, tak je opravdu chyba v tomhle. Pak tedy prověř, že sloupec email může obsahovat hodnotu NULL (pokud ne, tak ho tak nastav). A pak můžeš ty prázdné řetězce změnit na NULL:

UPDATE members SET email = NULL WHERE email = ''
 
Nahoru Odpovědět
22.4.2018 17:53
Avatar
Odpovídá na Mouser
Peter Schoeller:22.4.2018 18:51

Diky... Jedna chyba odstranena.... jeden email nemal hodnotu - ten moj :-D :-D
Teraz je dalsia chyba:

Chyba v dotaze (1452): Cannot add or update a child row: a foreign key constraint fails (`my-db_admin`.`#sql-241c_5770`, CONSTRAINT `#sql-241c_5770_ibfk_1` FOREIGN KEY (`email`) REFERENCES `members` (`email`) ON DELETE CASCADE)

A este raz diky za pomoc....

 
Nahoru Odpovědět
22.4.2018 18:51
Avatar
Mouser
Člen
Avatar
Odpovídá na Peter Schoeller
Mouser:22.4.2018 19:31

Není zač, ikdyž bys mohl zkoušet trochu googlit. :-) Hned první odkaz by tě navedl na pravděpodobnou odpověď: Nejspíš máš v recovery_keys záznamy, které nepatří žádnému memberovi. Zbav se jich:

DELETE FROM recovery_keys
WHERE email NOT IN (SELECT email FROM members)
 
Nahoru Odpovědět
22.4.2018 19:31
Avatar
Odpovídá na Mouser
Peter Schoeller:22.4.2018 20:43

Jeeeeee.... Konecne to funguje :-D
Mas u mna pivo "Kölsch" :-D
Ten odkaz na googli som nasiel aj ja, ale ak vidim v odkaze "DROP" (tusim tretia rada v tom odkaze) tak sa tomu oblukom vyhybam :-D No po troch dnoch skusania uz mi to nemysli :-)
Hned som aj pochopil (snad) ako to funguje :-D

 
Nahoru Odpovědět
22.4.2018 20:43
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 25 zpráv z 25.