MariaDB gives syntax error with empty near-string

Question

Im using MariaDB 10.24.8 on Linux and I'm encoutering this syntax error which has been stumping me for the last few hours:

ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6

with this SQL-Query for creating a stored-procedure:

USE getranke;
DROP PROCEDURE IF EXISTS insert_scan;
-- param scan INT: Barcode Value
-- out-param result INT: 0 = registered; 1 = DB is in inventory mode
CREATE PROCEDURE insert_scan(
 IN scan INT,
 OUT result INT
)
BEGIN
 DECLARE stateName TEXT;
 SELECT Name INTO stateName
 FROM StateIDs
 JOIN SystemState
 ON StateIDs.ID = SystemState.FK_StateIDs_ID
 WHERE SystemState.ID = 1;
 IF stateName = 'Inventory' THEN
 SET result = 1;
 ELSE
 SELECT scan;
 INSERT INTO Scans (FK_Drinks_barcode, when_datetime)
 VALUES (scan, NOW());
 SET result = 0;
 END IF;
END;

What has me especially irritated is that Rust-Rover manages to execute this query without a problem, but if I run it using the mysql/mariadb console it gives me this error. It also occurs with other front-ends like phpmyadmin.

If you need further information I'd be more than happy to provide it. Thanks.

Answer 1

I'm pretty sure this has been answered many times, but I can't find such an answer, so I'm going to write one.

As the manual says,

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

You do that with the delimiter command:

delimiter //
CREATE PROCEDURE insert_scan
...
END
//
delimiter ;

With the "other front-ends like phpmyadmin" you would use their respective settings to redefine the delimiter.

• Akina: Sync specified and used delimiter... Also it is not safe to use escape char as a delimiter or a part of it. I'd use double (or even triple) semicolon, it is safe close to absolutely.
• Fnununkslit: thank you for the answer :) I'm a bit to pampered by things like rust-rover regarding SQL.

Answer 2

Let's simplify the code some:

DELIMITER //
CREATE PROCEDURE insert_scan (
 IN _scan INT,
 OUT _result INT )
BEGIN
 SELECT stateName = 'Inventory' INTO _result
 FROM StateIDs AS si
 JOIN SystemState AS ss
 ON si.ID = ss.FK_StateIDs_ID
 WHERE ss.ID = 1;
 IF _result
 THEN
 INSERT INTO Scans (FK_Drinks_barcode, when_datetime)
 VALUES (_scan, NOW());
 END IF;
END;
//
DELIMITER ;

(I like to declare arguments and local variables with a leading _ to avoid certain puzzling errors. No, I don't think they occurred in this case.