SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 23.03.2018 01:34:22

Sergio
Участник
Зарегистрирован: 23.03.2018
Сообщений: 7

Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

Здравствуйте!

Имеется:
CREATE TABLE IF NOT EXISTS t1 (
    id INT( 10 ) UNSIGNED NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
...
    PRIMARY KEY (id, time),
...)
INSERT INTO t1 (id, time, ...)
VALUES ("1", "2018-03-22 11:11:11", ...)';

CREATE TABLE IF NOT EXISTS t2 (
    id1 INT( 10 ) UNSIGNED NOT NULL,
    time1 TIMESTAMP NOT NULL,
    time2 TIMESTAMP NOT NULL,
    id2 INT( 10 ) UNSIGNED NOT NULL,
    question VARCHAR( 256 ) CHARACTER SET utf8 NOT NULL,
    answer VARCHAR( 256 ) CHARACTER SET utf8 NOT NULL,
...
    PRIMARY KEY (id1, time1, time2, id2),
    FOREIGN KEY (id1, time1) REFERENCES t1(id, time) ON UPDATE CASCADE)

Добавляю "question", например:
INSERT INTO t2 (id1, time1, time2, id2, question)
VALUES ("1", "2018-03-22 11:11:11", "2018-03-22 22:22:22", "2", "текст")';

При появлении ответа, добавляю "answer":
SELECT question FROM t2 WHERE
    id1="1"
    AND time1="2018-03-22 11:11:11"
    AND time2="2018-03-22 22:22:22"
    AND id2="2" INTO @a;
REPLACE INTO t2 (id1, time1, time2, id2, question, answer)
    VALUES ( "1", "2018-03-22 11:11:11", "2018-03-22 22:22:22", "2", @a, "текст2")

Так работает.

А если добавлять "answer" апдейтом:
UPDATE t2 SET answer="текст2" WHERE
    id1="1"
    AND time1="2018-03-22 11:11:11"
    AND time2="2018-03-22 22:22:22"
    AND id2="2";
или инсертом с ON DUPLICATE KEY UPDATE - выдает error 1452.

Подскажите, пожалуйста, почему вставка работает, а обновление строки ругается на FOREIGN KEY ?

Неактивен

 

#2 23.03.2018 01:58:26

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

Воспроизвести не удалось, UPDATE сработал нормально. Проверил на 5.7.20-18 Percona Server. Какая у Вас версия MySQL?

Неактивен

 

#3 23.03.2018 09:05:49

Sergio
Участник
Зарегистрирован: 23.03.2018
Сообщений: 7

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

mysql 5.5.25 (из состава Denwer годичной давности)

Спасибо за оперативный ответ, если у Вас сработало, это уже сужает круг поиска.
Экспериментирую дальше...

Отредактированно Sergio (23.03.2018 09:11:02)

Неактивен

 

#4 23.03.2018 11:01:02

Sergio
Участник
Зарегистрирован: 23.03.2018
Сообщений: 7

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

Детальнее об ошибке (может натолкнет на мысль):

SHOW ENGINE INNODB STATUS
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
180323  9:33:02 Transaction:
TRANSACTION 6D5A3, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 304, OS thread handle 0x10bc, query id 1886 localhost 127.0.0.1 root Updating
UPDATE t2 SET answer="текст2" WHERE
    id1="1"    AND time1="2018-03-22 11:11:11"
    AND time2="2018-03-22 22:22:22"
    AND id2="2"
Foreign key constraint fails for table `my_db`.`t2`:
,
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id1`, `time1`) REFERENCES `t1` (`id`, `time`) ON UPDATE CASCADE
Trying to add in child table, in index `PRIMARY` tuple:
DATA TUPLE: 8 fields;
0: len 4; hex 00000001; asc     ;;
1: len 4; hex 5ab4adae; asc Z   ;;
2: len 4; hex 5ab4107e; asc Z  ~;;
3: len 4; hex 00000002; asc     ;;
4: len 6; hex 00000006d5a3; asc       ;;
5: len 7; hex 9d0000014c0110; asc     L  ;;
6: len 10; hex d182d0b5d0bad181d182; asc           ;;
7: len 11; hex d182d0b5d0bad181d18232; asc           2;;

But in parent table `my_db`.`t1`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 00000001; asc     ;;
1: len 4; hex 5ab3732f; asc Z s/;;
2: len 6; hex 00000006d59e; asc       ;;
3: len 7; hex 9b0000014a0110; asc     J  ;;

...

ЗЫ: Вот похожая ситуация у людей https://forums.mysql.com/read.php?22,235718 ,
но тоже без решения (насколько я понимаю английский)

Неактивен

 

#5 23.03.2018 13:20:30

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

CREATE TABLE IF NOT EXISTS t1 (
    id INT( 10 ) UNSIGNED NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (id, time));

INSERT INTO t1 (id, time)
VALUES ("1", "2018-03-22 11:11:11");

CREATE TABLE IF NOT EXISTS t2 (
    id1 INT( 10 ) UNSIGNED NOT NULL,
    time1 TIMESTAMP NOT NULL,
    time2 TIMESTAMP NOT NULL,
    id2 INT( 10 ) UNSIGNED NOT NULL,
    question VARCHAR( 256 ) CHARACTER SET utf8 NOT NULL,
    answer VARCHAR( 256 ) CHARACTER SET utf8 NOT NULL,
    PRIMARY KEY (id1, time1, time2, id2),
    FOREIGN KEY (id1, time1) REFERENCES t1(id, time) ON UPDATE CASCADE);

INSERT INTO t2 (id1, time1, time2, id2, question)
VALUES ("1", "2018-03-22 11:11:11", "2018-03-22 22:22:22", "2", "текст");
ERROR 1364 (HY000): Field 'answer' doesnot have a default value

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
 


mysql> set @@sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO t2 (id1, time1, time2, id2, question) VALUES ("1", "2018-03-22 11:11:11", "2018-03-22 22:22:22", "2", "текст");
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> UPDATE t2 SET answer="текст2" WHERE id1="1" AND time1="2018-03-22 11:11:11" AND time2="2018-03-22 22:22:22" AND id2="2";
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`world`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id1`, `time1`) REFERENCES `t1` (`id`, `time`) ON UPDATE CASCADE)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.06 sec)
 


Error 1452 возникает, когда update записи в дочерней таблицы приводит к несогласованности данных. Но в данном случае мы не меняем значения ключевых полей.
Похоже на багу, но странно, что не исправили задолго до 5.7
У кого-нибудь есть идеи?

Неактивен

 

#6 23.03.2018 13:40:03

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

CREATE TABLE IF NOT EXISTS x1 (
    id INT NOT NULL,
    a int NOT NULL,
    PRIMARY KEY (id, a));

INSERT INTO x1 (id, a) VALUES (1, 1);

CREATE TABLE IF NOT EXISTS x2 (
    id INT NOT NULL,
    a int NOT NULL,
    b INT NOT NULL,
    c INT NOT NULL,
    PRIMARY KEY (id, a, b),
    FOREIGN KEY (id, a) REFERENCES x1(id, a) ON UPDATE CASCADE);

INSERT INTO x2 (id, a, b, c) VALUES (1, 1, 1, 1);

update x2 set c=2 where id=1 and a=1 and b=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0


Странно, если упростить пример, заменив TIMESTAMP на INT, то ошибка не возникает.
rgbeast, ты на 5.7.20-18 Percona Server проверял с TIMESTAMP ?

Неактивен

 

#7 23.03.2018 16:40:05

Sergio
Участник
Зарегистрирован: 23.03.2018
Сообщений: 7

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

vasya спасибо!
Поменял тип t1.time и, соответственно t2.time1 с TIMESTAMP на DATATIME - работает.
Уже лучше, чем вариант с REPLACE, но может всетаки с TIMESTAMP как то можно?

Отредактированно Sergio (23.03.2018 16:44:58)

Неактивен

 

#8 23.03.2018 16:43:03

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

Проверял с TIMESTAMP, но добавил DEFAULT CURRENT_TIMESTAMP для time1 и time2. Без дефолтного значения отказывалась создавать таблицу.

Неактивен

 

#9 23.03.2018 17:08:45

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

Sergio написал:

vasya спасибо!
Поменял тип t1.time и, соответственно t2.time1 с TIMESTAMP на DATATIME - работает.
Уже лучше, чем вариант с REPLACE, но может всетаки с TIMESTAMP как то можно?

Похоже это бага. И чтобы работало с TIMESTAMP нужно писать на http://bugs.mysql.com/

Неактивен

 

#10 23.03.2018 19:10:48

Sergio
Участник
Зарегистрирован: 23.03.2018
Сообщений: 7

Re: Подскажите, пожалуйста, почему INSERT работает, а UPDATE ругается на FOREIGN KEY (error 1452)

rgbeast написал:

Проверял с TIMESTAMP, но добавил DEFAULT CURRENT_TIMESTAMP для time1 и time2. Без дефолтного значения отказывалась создавать таблицу.

Спасибо большое. Получилось.
Всего лишь добавил  DEFAULT CURRENT_TIMESTAMP для time1.
Похоже поля, связанные внешним ключом, должны иметь не только тот же тип, но и тот же DEFAULT, для надежности wink

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson