Categories
Database Development

Why are my rows deleted on update?

I have 2 tables, quizzes and quizzes_answers. The former contains questions and the latter contains available answers to this question.

Adding some rows in the table quizzes_answers with a quiz_id higher than 3 makes these newly created rows disappear instantly. Updating an existing row to set its quiz_id to a value higher than 3 also results in this row disappearing.

I have absolutely no idea what’s happening. There is no warning, no error, nothing I find to explain what’s happening.

I am using mysql workbench.

These data are added into the table from dump files.

/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

/*!40103 SET TIME_ZONE='+00:00' */;
CREATE TABLE `quizzes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `competition_id` bigint(20) DEFAULT NULL,
  `status` int(4) NOT NULL DEFAULT 0,
  `private` tinyint(1) DEFAULT NULL,
  `question` varchar(256) NOT NULL,
  `end_date` datetime NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_idx` (`user_id`),
  KEY `competition_idx` (`competition_id`) /* ,
  CONSTRAINT `quizzes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `quizzes_ibfk_2` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `quizzes` VALUES

-- Upcoming quizzes

(1,1,3,1,1,"Pour le quiz et ses questions il est important d'établir un nombre de caractères maximum. L'idéal est de prendre celui de twitter, qui peut être assez long pour les plus aventureux,  et reste acceptable. Oui, cela permet d'écrire plusieurs lignes pour une seule et même question ?","2120-02-25 12:30:00","2020-02-20 12:04:51"),
(2,1,3,1,1,"Question 1","2120-02-25 12:30:00","2020-02-20 12:04:51"),
(3,1,3,1,1,"Question 2","2120-02-25 12:30:00","2020-02-20 12:04:51"),

-- Passed quizzes

(4,1,3,1,1,"Question passée avec points","2020-02-26 20:00:00","2020-02-20 12:04:51"),
(5,1,3,1,1,"Question passée sans points","2020-02-25 17:45:00","2020-02-20 12:04:51"),
(6,1,3,1,1,"Question passée non répondue","2020-02-25 17:45:00","2020-02-20 12:04:51"),
(7,1,3,1,1,"Question passée dont les points des réponses n'ont pas encore été définis.","2020-02-25 17:45:00","2020-02-20 12:04:51"),
(8,1,3,1,1,"Question passée non répondue ET dont les points des réponses n'ont pas encore été définis. D'ailleurs le texte est très long comme ça on peut bien juger de la flexibilité du conteneur. J'écris encore une phrase et ensuite je m'arrête, c'est vraiment pour avoir un gros texte.","2020-02-25 17:45:00","2020-02-20 12:04:51");
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

/*!40103 SET TIME_ZONE='+00:00' */;
CREATE TABLE `quizzes_answers` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `quiz_id` bigint(20) NOT NULL,
  `text` varchar(256) NOT NULL,
  `points` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `quiz_idx` (`quiz_id`),
  CONSTRAINT `quizzes_propositions_ibfk_1` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `quizzes_answers` VALUES

-- Answers for upcoming quizzes

(1,1,"Le petit amis anglais à l'allure irréprochable qui trompe sa compagne assidument dans un roman psychologique néo-zélandais",NULL),
(2,1,"le méchant d’un thriller sud-africain qui tue ses victimes avec des balles gravées d'une tête de cobra",NULL),

(3,2,"Réponse 1",NULL),
(4,2,"Réponse 2",NULL),

(5,3,"Réponse 1",NULL),
(6,3,"Réponse 2",NULL),

-- Answers for passed quizzes

(7,4,"Réponse 10pts",10),
(8,4,"Réponse 0pt",0),

(9,5,"Réponse 10pt",10),
(10,5,"Réponse 0pts",0),

(11,6,"Réponse 10pts",10),
(12,6,"Réponse 0pt",0),

(13,7,"Réponse dont les points n'ont pas encore été définis 1",NULL),
(14,7,"Réponse dont les points n'ont pas encore été définis 2",NULL),

(15,8,"Réponse dont les points n'ont pas encore été définis 1",NULL),
(16,8,"Réponse dont les points n'ont pas encore été définis 2",NULL);

You can find a dump here: https://we.tl/t-x1ic5fbw5G

Note that quizzes_answers rows from id 7 to 16 are not added into the table (or they are instantly deleted after creation, idk). Only rows from id 1 to id 6 are created.

The only possible explanation I found for these deletion would have something to do with a delete cascade and the end_date that is passed. The answers would be deleted because mysql would somehow delete answers because of their corresponding quiz’s passed end_date. However I tried to disable the cascade, and I tried to set all end_dates in the future, and the bug is still there.

Can someone help me understand these deletions?

Leave a Reply

Your email address will not be published. Required fields are marked *