Не работает каскадное удаление в InnoDB

kate · 6 Июн 2006

Не работает каскадное удаление в InnoDB

Никак не получается...ругается на внешний ключ. при попытке удалить раздел
а при попытке обновить по полю hide страницу тоже ругается.

Вот дамп, посмотрите плиз...

---------------------------------------------------------------------------------------------------------------------

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl2
-- http://www.phpmyadmin.net
--
-- Хост: localhost
-- Время создания: Июн 06 2006 г., 15:28
-- Версия сервера: 4.0.22
-- Версия PHP: 5.0.2
--
-- БД: 'cms'
--

-- --------------------------------------------------------

--
-- Структура таблицы 'pages'
--

CREATE TABLE 'pages' (
'idp' int(6) unsigned NOT NULL auto_increment,
'idr' smallint(5) unsigned NOT NULL default '0',
'pos' int(11) NOT NULL default '0',
'name' tinytext NOT NULL,
'addr' tinytext NOT NULL,
'title' tinytext NOT NULL,
'keyw' tinytext NOT NULL,
'desc_p' tinytext NOT NULL,
'h1' tinytext NOT NULL,
'content' text NOT NULL,
'hide' enum('hide','show') NOT NULL default 'hide',
PRIMARY KEY ('idp'),
KEY 'idr' ('idr'),
KEY 'hide' ('hide')
) TYPE=InnoDB AUTO_INCREMENT=7 ;

--
-- Дамп данных таблицы 'pages'
--

INSERT INTO 'pages' VALUES (2, 1, 1, 'Миссия', '/about/mission.php', 'Миссия', 'Миссия', 'Миссия', 'Миссия', 'Миссия', 'show');
INSERT INTO 'pages' VALUES (3, 1, 3, 'Контакты', '/about/contacts.php', 'Контакты', 'Контакты', 'Контакты', 'Контакты', 'Контакты',

'show');
INSERT INTO 'pages' VALUES (4, 1, 5, 'Вакансии', '/about/vacancy.php', 'Вакансии', 'Вакансии', 'Вакансии', 'Вакансии', 'Вакансии',

'show');
INSERT INTO 'pages' VALUES (5, 1, 4, 'Планы', '/about/plans.php', 'Планы', 'Планы', 'Планы', 'Планы', 'Планы', 'show');
INSERT INTO 'pages' VALUES (6, 1, 2, 'Партнёры', '/about/partners.php', 'Партнёры', 'Партнёры', 'Партнёры', 'Партнёры', 'Партнёры',

'show');

-- --------------------------------------------------------

--
-- Структура таблицы 'razdel'
--

CREATE TABLE 'razdel' (
'idr' smallint(5) unsigned NOT NULL auto_increment,
'pos' int(11) NOT NULL default '0',
'name' tinytext NOT NULL,
'addr' tinytext NOT NULL,
'title' tinytext NOT NULL,
'keyw' tinytext NOT NULL,
'desc_r' tinytext NOT NULL,
'h1' tinytext NOT NULL,
'content' text NOT NULL,
'hide' enum('hide','show') NOT NULL default 'hide',
PRIMARY KEY ('idr'),
KEY 'hide' ('hide')
) TYPE=InnoDB AUTO_INCREMENT=7 ;

--
-- Дамп данных таблицы 'razdel'
--

INSERT INTO 'razdel' VALUES (1, 1, 'О КОМПАНИИ', '/about/', 'О компании', 'О компании', 'О компании', 'О компании', 'О компании',

'show');
INSERT INTO 'razdel' VALUES (2, 2, 'УСЛУГИ', '/services/', 'УСЛУГИ', 'УСЛУГИ', 'УСЛУГИ', 'УСЛУГИ', 'УСЛУГИ', 'show');
INSERT INTO 'razdel' VALUES (3, 3, 'ПРОДУКЦИЯ', '//', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ',

'П<STRONG>РОДУКЦИЯ</STRONG>', 'show');
INSERT INTO 'razdel' VALUES (4, 4, 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', '/info/', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'СПРАВОЧНАЯ

ИНФОРМАЦИЯ', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'show');
INSERT INTO 'razdel' VALUES (5, 5, 'ПРОДУКЦИЯ', '/products/', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ',

'ПРОДУКЦИЯ', 'show');
INSERT INTO 'razdel' VALUES (6, 6, 'ПРОЕКТЫ', '/projects/', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'show');

--
-- Constraints for table 'pages'
--
ALTER TABLE 'pages'
ADD CONSTRAINT 'pages_ibfk_1' FOREIGN KEY ('idr') REFERENCES 'razdel' ('idr') ON DELETE CASCADE ON UPDATE NO

ACTION,
ADD CONSTRAINT 'pages_ibfk_2' FOREIGN KEY ('hide') REFERENCES 'razdel' ('hide') ON DELETE NO ACTION ON UPDATE

CASCADE;

---------------------------------------------------------------------------------------------------------------------

Ну никак не получается...помогите!!!
Что не так? версия мускула у меня - MySQL 4.0.22-nt-log

Апельсин · 6 Июн 2006

ну логично что у вас неработает удаление, у вас для одного из внешних ключей стоит ON DELETE NO ACTION. Как вы себе при этом представляете удаление строки?

-~{}~ 06.06.06 19:18:

UPDATE должны работать, обновитесь до последней версии.

kate · 6 Июн 2006

я убрала ON DELETE NO ACTION, удаление всё равно не работает...

Апельсин · 6 Июн 2006

kate, если у вас что-то не работает то имеет смысл:
1) показывать что из себя таблица представляет сейчас
2) говорить что именно вы делали, т.е. приводить конкретные запросы
3) приводить сообщения об ошибках

kate · 6 Июн 2006

ок,

SQL-запрос:
DELETE FROM `razdel` WHERE `idr` = '3' LIMIT 1

Ответ MySQL:
#1217 - Cannot delete or update a parent row: a foreign key constraint fails

дамп :
____________________________________________

CREATE TABLE pages (
idp int(6) unsigned NOT NULL auto_increment,
idr smallint(5) unsigned NOT NULL default '0',
pos int(11) NOT NULL default '0',
name tinytext NOT NULL,
addr tinytext NOT NULL,
title tinytext NOT NULL,
keyw tinytext NOT NULL,
desc_p tinytext NOT NULL,
h1 tinytext NOT NULL,
content text NOT NULL,
hide enum('hide','show') NOT NULL default 'hide',
PRIMARY KEY (idp),
KEY idr (idr),
KEY hide (hide)
) TYPE=InnoDB AUTO_INCREMENT=7 ;

--
-- Дамп данных таблицы `pages`
--

INSERT INTO pages VALUES (2, 1, 1, 'Миссия', '/about/mission.php', 'Миссия', 'Миссия', 'Миссия', 'Миссия', 'Миссия', 'show');
INSERT INTO pages VALUES (3, 1, 3, 'Контакты', '/about/contacts.php', 'Контакты', 'Контакты', 'Контакты', 'Контакты', 'Контакты', 'show');
INSERT INTO pages VALUES (4, 1, 5, 'Вакансии', '/about/vacancy.php', 'Вакансии', 'Вакансии', 'Вакансии', 'Вакансии', 'Вакансии', 'show');
INSERT INTO pages VALUES (5, 1, 4, 'Планы', '/about/plans.php', 'Планы', 'Планы', 'Планы', 'Планы', 'Планы', 'show');
INSERT INTO pages VALUES (6, 1, 2, 'Партнёры', '/about/partners.php', 'Партнёры', 'Партнёры', 'Партнёры', 'Партнёры', 'Партнёры', 'show');

-- --------------------------------------------------------

--
-- Структура таблицы `razdel`
--

CREATE TABLE razdel (
idr smallint(5) unsigned NOT NULL auto_increment,
pos int(11) NOT NULL default '0',
name tinytext NOT NULL,
addr tinytext NOT NULL,
title tinytext NOT NULL,
keyw tinytext NOT NULL,
desc_r tinytext NOT NULL,
h1 tinytext NOT NULL,
content text NOT NULL,
hide enum('hide','show') NOT NULL default 'hide',
PRIMARY KEY (idr),
KEY hide (hide)
) TYPE=InnoDB AUTO_INCREMENT=7 ;

--
-- Дамп данных таблицы `razdel`
--

INSERT INTO razdel VALUES (1, 1, 'О КОМПАНИИ', '/about/', 'О компании', 'О компании', 'О компании', 'О компании', 'О компании', 'show');
INSERT INTO razdel VALUES (2, 2, 'УСЛУГИ', '/services/', 'УСЛУГИ', 'УСЛУГИ', 'УСЛУГИ', 'УСЛУГИ', 'УСЛУГИ', 'show');
INSERT INTO razdel VALUES (3, 3, 'ПРОДУКЦИЯ', '//', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'П<STRONG>РОДУКЦИЯ</STRONG>', 'show');
INSERT INTO razdel VALUES (4, 4, 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', '/info/', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'СПРАВОЧНАЯ \r\n\r\nИНФОРМАЦИЯ', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'СПРАВОЧНАЯ ИНФОРМАЦИЯ', 'show');
INSERT INTO razdel VALUES (5, 5, 'ПРОДУКЦИЯ', '/products/', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'ПРОДУКЦИЯ', 'show');
INSERT INTO razdel VALUES (6, 6, 'ПРОЕКТЫ', '/projects/', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'ПРОЕКТЫ', 'show');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `pages`
--
ALTER TABLE `pages`
ADD CONSTRAINT `pages_ibfk_4` FOREIGN KEY (`hide`) REFERENCES `razdel` (`hide`) ON UPDATE CASCADE,
ADD CONSTRAINT `pages_ibfk_3` FOREIGN KEY (`idr`) REFERENCES `razdel` (`idr`) ON DELETE CASCADE;

____________________________________________

Апельсин · 6 Июн 2006

после DELETE сделайте show innodb status

там должна последняя ошибка более подробно расшифровываться .. хотя я не помню с какой версии они начали писать детальное сообщение об ошибке.

kate · 6 Июн 2006

Ето?

=====================================
060606 20:36:05 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 19, signal count 19
Mutex spin waits 5, rounds 54, OS waits 1
RW-shared spins 26, OS waits 13; RW-excl spins 4, OS waits 4
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060606 20:35:48 Transaction:
TRANSACTION 0 22353, ACTIVE 0 sec, OS thread id 1412 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
5 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 34, query id 805 localhost 127.0.0.1 root updating
DELETE FROM `razdel` WHERE `idr` = '3'
Foreign key constraint fails for table `ccc/pages`:
,
CONSTRAINT `pages_ibfk_4` FOREIGN KEY (`hide`) REFERENCES `razdel` (`hide`) ON UPDATE CASCADE
Trying to delete in parent table, in index `hide` tuple:
DATA TUPLE: 2 fields;
0: len 1; hex 02; asc ;; 1: len 2; hex 0003; asc ;;

But in child table `ccc/pages`, in index `hide`, there is a record:
PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 1; hex 02; asc ;; 1: len 4; hex 00000002; asc ;;

------------
TRANSACTIONS
------------
Trx id counter 0 22355
Purge done for trx's n :eek:

< 0 22355 undo n :eek:

< 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3468
MySQL thread id 38, query id 884 localhost 127.0.0.1 root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
75 OS file reads, 121 OS file writes, 66 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 1513636
Log flushed up to 0 1513636
Last checkpoint at 0 1513636
0 pending log writes, 0 pending chkp writes
48 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13563774; in additional pool allocated 233728
Buffer pool size 512
Free buffers 468
Database pages 43
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 40, created 3, written 67
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 2320, state: sleeping
Number of rows inserted 20, updated 0, deleted 0, read 135
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

baev · 6 Июн 2006

А что за ключ 'hide'?

P.S. Вот и самой MySQL он не понравился...
(Я как-то даже затрудняюсь себе такую связь представить.)

kate · 6 Июн 2006

hide - обновление каскадное. Т.е. когда я обновляю в родительской таблице поле hide, обновляется поле hide и в дочерней. Связь по индексам...не реально?

Апельсин · 6 Июн 2006

гм, ну в общем-то понятно ..
когда вы удаляете строку с idr=3, то эта строка также содержит hide="show". Т.к. в дочерней таблице у вас есть строки со значением 'show' оно вам и не дает удалить.

Если мне не изменяет память, то по стандарту ссылаться можно только на столбец имеющий уникальные значения. В MySQL такого ограничения нет. Результат налицо.

kate · 6 Июн 2006

ну да понятно, и что же делать?

Апельсин · 6 Июн 2006

менять логику приложения?

можете объяснить глубокий смысл внешнего ключа на поле hide? Если у вас в родительской таблице меняется хотя бы одна строка с show на hide, то у вас значение поменяется у _всех_ строк вашей дочерней таблицы (по вашим данным). вы действительно этого хотите?

kate · 6 Июн 2006

Необходимо чтобы при изменении поля hide в razdel менялись поля hide для дочерней, где idr в родительской и дочерней таблицах соответствую друг другу.

Begin;
$query = "UPDATE razdel SET hide = 'show' WHERE idr='$idr'";
$query = "UPDATE pages SET hide = 'show' WHERE idr='$idr'";
Commit;

как-то так...
только хотелось связями

Апельсин · 9 Июн 2006

ну то что вы в самом начале пытались сделать это совсем не то что вы хотите

А зачем это вообще делать? Если у вас раздел невидимый, то просто проверять видимость страниц смысла нет. Если раздел видимый, то тогда проверяется видимость страницы. Все. Зачем эти танцы с внешними ключами?

Не работает каскадное удаление в InnoDB

kate

Новичок

Апельсин

Оранжевое создание

kate

Новичок

Апельсин

Оранжевое создание

kate

Новичок

Апельсин

Оранжевое создание

kate

Новичок

baev

‹°°¬•

kate

Новичок

Апельсин

Оранжевое создание

kate

Новичок

Апельсин

Оранжевое создание

kate

Новичок

Апельсин

Оранжевое создание