Using mysql 5.7, when I execute this procedure:
DELIMITER $$ CREATE PROCEDURE tres() BEGIN DECLARE maxid INT; DECLARE x INT; SET maxid = 655; SET x=1; WHILE x<= maxid DO INSERT INTO `forum_topicresponder` (topic_id, username, reply_id) SELECT p.topic_id, p.creator_name, p.id FROM forum_post AS p WHERE p.topic_id=x; SET x = x + 1; END WHILE; END$$
I get
mysql> call tres();
Query OK, 0 rows affected (3.49 sec)
However when I execute the INSERT for a certain values inside the loop range:
INSERT INTO `forum_topicresponder` (topic_id, username, reply_id) SELECT p.topic_id, p.creator_name, p.id FROM forum_post AS p WHERE p.topic_id=42;
I see that the results are inserted without problem.
The table schemas:
CREATE TABLE `forum_topicresponder` ( `id` int(11) NOT NULL AUTO_INCREMENT, `topic_id` int(10) NOT NULL DEFAULT '0', `username` varchar(300) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'na', `reply_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=latin1;
And
CREATE TABLE `forum_post` ( `id` int(11) NOT NULL AUTO_INCREMENT, `body` varchar(16000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `topic_id` int(11) NOT NULL DEFAULT '0', `creator_id` int(11) NOT NULL DEFAULT '0', `creator_name` varchar(30) NOT NULL DEFAULT 'NA', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6264595 DEFAULT CHARSET=utf8;
So what could be wrong here and how can I fix it?
Advertisement
Answer
You need to check the value of x in the procedure, It might be not matched with your topic_id of the forum_post table.
If topic_id not matched then no data will copy to the forum_topicresponder table.
You need to set the proper value of x, (from min to max) depends on topic_id of forum_post table