FU MySQL/MariaDB

I just spent an HOUR trying to find out why a stored procedure was returning NULL no matter what i did!

Here is the procedure i was trying to create:

DELIMITER //

CREATE PROCEDURE get_last_student_id(OUT last_id INT) 
BEGIN
	SELECT IF(id,id,0) INTO @last_id FROM students ORDER BY id DESC LIMIT 1;
END;
//

DELIMITER ;

Simple right? Can you spot the mistake?

No? Well don’t worry, i didn´t either.

You see, the problem is with MySQL/MariaDB user defined variables. This is how you create and use variables in MySQL/MariaDB (note: variables don´t need to be defined before use):

-- Create @counter variable and set its value to 0
SET @counter := 0;

-- Increment the @counter value by one
SET @counter := @counter + 1;

-- Show the @counter value
SELECT @counter;

-- Create @aux variable and set its value to 200
SET @aux = 200;

-- Set @counter to 200 (@aux value)
SELECT @aux INTO @counter;

-- Prints: 200
SELECT @counter;

Can you see the mistake now? Look at all those pretty “@” everywhere!

They are infact used everywhere EXCEPT FOR stored procedure variables! So here is the mistake:

	SELECT IF(id,id,0) INTO @last_id FROM students ORDER BY id DESC LIMIT 1;

@last_id should be just last_id because, in reality, last_id is a stored procedure parameter and not a real variable and parameters don’t have the “@” prefix… but wait! What´s this?

Here is how you call a stored procedure:

CALL get_last_student_id(@student_id);
SELECT @student_id;

Wait, what? WTF? I’m passing a VARIABLE that turns into a stored procedure PARAMETER and then turns again into a VARIABLE when selecting it? Wut?

I guess consistency just flew out the window…

Advertisements
This entry was posted in Knowledge and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s