Problém s Foreign Key – self-referencing

VojtaSim

Zdravím,
vím, že tohle se Nette moc netýká ale už selhal i StackOverflow. Mám tabulku, která zrcadlí složky v souborovém systému, a jak je známo souborový systém je hierarchický, kdežto MySQL tabulka ne. Proto je v tabulce sloupec parent_id, kterým se odkazuji na rodiče. Odkazování pomocí FK mezi tabulkami funguje bez problému, odkaz na stejnou tabulku taky funguje, ale když se pokusím v tabulce folders změnit id rodičovského adresáře vyhodí se chyba Cannot delete or update a parent row: a foreign key constraint fails...

CREATE TABLE IF NOT EXISTS `folders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `account_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(30) NOT NULL,
  `path` varchar(600) DEFAULT NULL,
  `size` bigint(22) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  KEY `frontend_id` (`frontend_id`),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

ALTER TABLE `folders`
  ADD CONSTRAINT `folders_ibfk_5` FOREIGN KEY (`parent_id`) REFERENCES `folders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `folders_ibfk_3` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

Zatím jsem nacházel řešení typu: Obě pole ve FK musí být stejného typu, parent_id by mělo být DEFAULT NULL, atd.

Editoval VojtaSim (27. 12. 2013 20:47)

David Matějka

ale když se pokusím v tabulce folders změnit id rodičovského adresáře vyhodí se…

chapu, ze menis primary key id? proc je to potreba?

VojtaSim

chapu, ze menis primary key id? proc je to potreba?

přečíslování

SET @a=0;
UPDATE folders SET id=(@a:=@a+1);

David Matějka

vykaslal bych se na precislovani.

jinak zkus:

SET FOREIGN_KEY_CHECKS=0;
SET @a=0;
UPDATE folders SET id=(@a:=@a+1);
SET FOREIGN_KEY_CHECKS=1;

ale bojim se, ze se to nekde rozsipe.. dalsi, co me napada, je pred precislovanim navysit id o maximalni hodnotu id a precislovat to pak – melo by se tim predejit nejakym kolizim atd.

ale nejlepsi bude se na precislovani vykaslat, nebo proc ho potrebujes?

VojtaSim

ale nejlepsi bude se na precislovani vykaslat, nebo proc ho potrebujes?

no třeba u tabulky folders to není až tak akutní ale mám tabulku sessions, kde je seznam přihlášených uživatelů, a ta se za měsíc může docela rozdrobit. Napadalo mě, že by každý uživatel měl svůj řádek, ale pak mi došlo, že se může přihlásit z více zařízení najednou.

VojtaSim

Asi to vyřeším tak, že použiju:

SET FOREIGN_KEY_CHECKS=0;

a napíšu si proceduru, kde postaru aktualizuji všechny reference.

Milo

@VojtaSim: Seš si jistý, že všechny cizí klíče, které odkazují na tabulku folders jsou cascade? Tvoje situace je ukázkový příklad na základní referenční integritu a musí fungovat.

EDIT: A postni se případně celou chybovou hlášku, ne jen výsek.

Editoval Milo (27. 12. 2013 22:57)

VojtaSim

@Milo
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (d24218_cmsa.folders, CONSTRAINT `folders_ibfk_4 FOREIGN KEY (parent_id) REFERENCES folders (id) ON DELETE CASCADE ON UPDATE CASCADE)`

Už jsem asi objevil, kde je zakopaný pes. Z MySQL dokumentace:
If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates.

Pro potřeby Nette vše funguje jak má, ale pro případ přečíslování je to už horší…

EDIT: Sekce Referential Actions

Editoval VojtaSim (27. 12. 2013 23:26)

David Matějka

stale si myslim, ze nejlepsi je se na nejake precislovani vykaslat… nebo ti nestaci bigint? :)

VojtaSim

nebo ti nestaci bigint? :)

bigint mi samozřejmě stačí, ale jen pro pořádek jsem vymyslel tohle:

CREATE PROCEDURE renumber_folders()
BEGIN
	DECLARE done INT DEFAULT FALSE;
  	DECLARE old_id BIGINT(20);
  	DECLARE folders_reader CURSOR FOR SELECT id FROM folders;
  	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	SET FOREIGN_KEY_CHECKS=0;
        SET @number = 0;

  	OPEN folders_reader;

  	read_loop: LOOP
    		FETCH folders_reader INTO old_id;
    		IF done THEN
      			LEAVE read_loop;
                END IF;

                SET @number := @number + 1;
                UPDATE folders SET id = @number WHERE id = old_id;
                UPDATE folders SET parent_id = @number WHERE parent_id = old_id;
                UPDATE files SET folder_id = @number WHERE folder_id = old_id;
  	END LOOP;

  	CLOSE folders_reader;
	SET FOREIGN_KEY_CHECKS=1;
END

Milo

@VojtaSim: Teď jsem si to zjednodušeně zkusil a fakt to nejde. MySQL (5.5.31) nedovolí update primárního klíče pokud na něj odkazuje cizí klíč ze stejné tabulky. Jestli můžeš, použij PostgreSQL.

nAS

Přečíslování primárních klíčů je hodně špatný nápad a pokud OPRAVDU nemusíš, tak je dobré se tomu vyhnout. Až si budeš chtít něco cachovat, tak je nejjednodušší způsob podle primárních klíčů, což ti tady selže. Nette\Database také používá předpoklad, že jsou primární klíče stále stejné a pokud je budeš měnit, tak může vracet špatná data. Těch příkladů je víc a budeš na to postupně narážet.

A jak píše Milo, pokud můžeš, zkus PostgreSQL. Obzvlášť pokud chceš v DB používat funkce, triggery atd, tak se to vyplatí.

hrach

Jak padlo, na primarni klice vubec nesahej. A propo, veci kolem session a filesystemu zni totalne jako spatne vyuziti *sql, pouzij pro session redis, nebo memcached.

VojtaSim

hrach napsal(a):

A propo, veci kolem session a filesystemu zni totalne jako spatne vyuziti *sql, pouzij pro session redis, nebo memcached.

Když jsem řekl že do DB ukládám sessions myslel jsem user_sessions, aneb seznam aktivně přihlašených zařízení
http://i.imgur.com/84fvqnF.png