1

I have the following mysql stored procedure:

DELIMITER //
CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16),
                                     IN cardUID VARCHAR(14)) 
BEGIN
    SET @projectId = (SELECT MIN(ID) FROM PROJECT WHERE DEVICEID =
          (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName)
             ORDER BY ID DESC LIMIT 1);
    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;
    
    SET @id = (SELECT MIN(ID) FROM WORKDAY
                   WHERE WORKERID = @workerId
                     AND PROJECTID = @projectId
                     AND STOPTIME IS NULL
                     AND STARTTIME IS NOT NULL);
    IF (@id >= 0)
        THEN
            UPDATE WORKDAY SET STOPTIME = now() WHERE ID = @id;
    ELSE
        INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME)
            VALUES (@workerId, @projectId, now());
    END IF;
END; //
DELIMITER ;

When I call the procedure once, I get the result:

mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

Query OK, 1 row affected (0.09 sec)

From second run, is not working anymore:

mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

ERROR 1048 (23000): Column 'WORKERID' cannot be null

I did try to isolate and run the procedure with only this two lines of code:

    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;

And it behaves just like above, but if I try to run SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = "045D91B22C5E80"; alone, works without problems. The @projectId variable behaves normal everytime I call the stored procedure.

Can someone help me to find a solution? Thank you!

My setup:

mihai:~$ mysql -V
mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
mysql> describe WORKER;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int         | NO   | PRI | NULL    | auto_increment |
| FIRSTNAME  | varchar(15) | NO   |     | NULL    |                |
| LASTNAME   | varchar(20) | NO   |     | NULL    |                |
| CARDNUMBER | varchar(14) | NO   |     | NULL    |                |
| POSITIONID | int         | NO   | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> SELECT * FROM WORKER;
+----+-------------+-----------+----------------+------------+
| ID | FIRSTNAME   | LASTNAME  | CARDNUMBER     | POSITIONID |
+----+-------------+-----------+----------------+------------+
|  1 | Ionut Mihai | Popescu   | 045D91B22C5E80 |          1 |
|  2 | Ilie        | Zbagan    | 040D7FB22C5E81 |          2 |
|  3 | Ilie        | Ungureanu | 043FA8B22C5E80 |          1 |
|  4 | Adrian      | Tehanciuc | 04D894B22C5E80 |          2 |
|  5 | Ioan        | Bitoanca  | 0405C6B22C5E81 |          2 |
+----+-------------+-----------+----------------+------------+
5 rows in set (0.00 sec)
DROP TABLE IF EXISTS `WORKER`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `WORKER` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `FIRSTNAME` varchar(15) NOT NULL,
  `LASTNAME` varchar(20) NOT NULL,
  `CARDNUMBER` varchar(14) NOT NULL,
  `POSITIONID` int NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `POSITIONID` (`POSITIONID`),
  CONSTRAINT `WORKER_ibfk_1` FOREIGN KEY (`POSITIONID`) REFERENCES `POSITION` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
3
  • I gather there can be multiple rows with the same WORKERID and PROJECTID ? – Rick James Jan 4 at 7:21
  • 1
    Please provide CREATE TABLE and enough rows INSERTed to exhibit the problem with those 2 lines. – Rick James Jan 4 at 7:23
  • Edited the question, providing dump code for WORKER table and all the data inside. – Mihai Popescu Jan 4 at 9:04
0

The problem was my declared local variable id. After I changed her name, everything worked just fine.

My previous answer was:

I don't know what the problem was, but I did found a solution. I wrote:

    SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID INTO @workerId;
    SELECT @workerId;

instead of

    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.