Mysql procedure throw error when cursor fetch 0 rows-CodePudding

I have a Mysql table as follows:

CREATE TABLE `login_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) NOT NULL,
  `device_id` varchar(50) NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login_info_uniq01` (`user_id`, `device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table is empty. The extend_login_timestamp_using_concat procedure is as follows:


DROP PROCEDURE IF EXISTS `extend_login_timestamp_using_concat` ;;
CREATE PROCEDURE `extend_login_timestamp_using_concat`()

BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE login_info_id BIGINT;

  DECLARE cur CURSOR FOR SELECT `id` FROM `login_info` WHERE `device_id` = 'WEB';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET @extended_timestamp = (SELECT UNIX_TIMESTAMP(DATE_FORMAT(NOW()   INTERVAL 365 DAY,'%Y-%m-01 23:59:59')) * 1000);
  SET @id_list = NULL;
  SET @id_count = 0;

  OPEN cur;

  REPEAT FETCH cur INTO login_info_id;

      SET @id_list = CONCAT_WS (',', @id_list, login_info_id);
      SET @id_count = @id_count   1;

      IF (@id_count = 2) THEN

        SET @update_query = CONCAT('UPDATE `login_info` SET `timestamp` = ', @extended_timestamp, ' WHERE `id` in (', @id_list, ')');

        PREPARE extend_expire_statement FROM @update_query;
        EXECUTE extend_expire_statement;
        DEALLOCATE PREPARE extend_expire_statement;

        SET @id_list = NULL;
        SET @id_count = 0;

      END IF;

  UNTIL done END REPEAT;
  CLOSE cur;

  IF (@id_list IS NOT NULL) THEN

    SET @update_query = CONCAT('UPDATE `login_info` SET `timestamp` = ', @extended_timestamp, ' WHERE `id` in (', @id_list, ')');

    PREPARE extend_expire_statement FROM @update_query;
    EXECUTE extend_expire_statement;
    DEALLOCATE PREPARE extend_expire_statement;

  END IF;

END ;;

DELIMITER ;

Since there is no data in the table, so cursor would fetch 0 rows, and when I run the procedure it return error as follows:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

But when I insert one or more than one rows which matches the cursor criteria it works fine.

And When I add any valid mysql query after opening the cursor, it doesn't throw any error irrespective of whether cursor fetch any rows or not.

For e.g I add SELECT CONCAT('Updating login_info device-id timestamp') as log; after opening the cursor will prevent the error when the table has no data.

How can I prevent the error when cursor doesn't fetch any row?

Any help will be highly appreciated. Thanks in advance.

CodePudding user response：

The problem is simple.

When the table is empty then your FETCH causes NOT FOUND event which fires the CONTINUE handler. The handler sets the variable and returns. Then the rest of the cycle code is executed - and your code prepares incorrect SQL code which causes the error in your EXECUTE. See it in @update_query variable immediately after the SP call falling.

Use not REPEAT but DO cycle. And put IF statement which checks the variable value and leaves the cycle if it is set immediately after FETCH statement.

fiddle (pay attention - I add comment marks in your SQL lines, so you can see what statement produces errorneous SQL).

PS. Do not mix local variables (done, login_info_id) and user-defined variables (@extended_timestamp, @id_list, @id_count) usage. In SP the local variables usage is preferred.