I am facing a very strange problem with while loop insert on MySQL

Here's the background information of my test:

CREATE TABLE test_table (
  `token` varchar(16) NOT NULL,
  `val1` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`token`)
);

DELIMITER $$
CREATE PROCEDURE sp_test(
    IN token VARCHAR(16),
    IN token1 VARCHAR(16),
    IN token2 VARCHAR(16),
    IN token3 VARCHAR(16),
    IN token4 VARCHAR(16),
    IN val1 VARCHAR(256)
)
BEGIN
    DECLARE tokenUsed VARCHAR(16);
    DECLARE trial INT DEFAULT 0;
    DECLARE tmpTok VARCHAR(16);
    
    WHILE (tokenUsed IS NULL AND trial < 5)
    DO
        SET tmpTok = CASE trial WHEN 0 THEN token WHEN 1 THEN token1 WHEN 2 THEN token2 
            WHEN 3 THEN token3 WHEN 4 THEN token4 END;
        
        IF NOT EXISTS(SELECT 1 FROM test_table WHERE token = tmpTok)
        THEN
            SET tokenUsed = tmpTok;
            INSERT INTO test_table (token) VALUES(tmpTok);
        END IF;
        
        SET trial = trial   1;
    END WHILE;
    
    IF tokenUsed IS NOT NULL
    THEN
        UPDATE test_table SET val1 = val1 WHERE token = tokenUsed;
    END IF;
    
    SELECT * FROM test_table WHERE token = tokenUsed;
END$$
DELIMITER ;

Then I would call the stored proc servral times like this

CALL sp_test ('test1', 'test2', 'test3', 'test4', 'test5', 'happy');

What I am trying to achieve

1. feed 5 random token to the sp
2. insert the first token available (i.e. not exists in test_table)
3. update the rest of the record with the inserted token and return that record

Here are the symptom

1. 1st call to sp_test, no problem, sp returned | test1 | happy |
2. 2nd call to sp_test, no error but sp returned nothing; test2 is inserted but val1 is not updated i.e. | test2 | NULL |
3. 3rd call to sp_test crashed with

Error Code: 1062. Duplicate entry 'test2' for key 'PRIMARY'

The while loop did move to token test2 and inserted it to test_table but went no further, update also failed after test2. The stored proc is quite straight forward and I have no idea why this is happening. Any thoughts?

CodePudding user response：

1. In your SP there is a row

UPDATE test_table SET val1 = val1 WHERE token = tokenUsed;

Both val1 in it are local variable defined in SP parameters. The same about token. Must be

UPDATE test_table SET test_table.val1 = val1 WHERE test_table.token = tokenUsed;

Also check your code for the same issue in another statements.

2. Investigate your SP flow, like: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d31de2054cc4077ceb26d2cf2da588ab