MySQL Procedure check if record exists before insert not working

Question

I have looked at the other questions on here about this. It isn't working.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `environment_admin`(
IN environment_id   TEXT,
IN user_id          TEXT,
IN username         VARCHAR(75),
IN password         VARCHAR(512)
)
BEGIN
DECLARE env_id INT;
DECLARE admin_id INT;

SET env_id = CAST(environment_id AS SIGNED INT);
SET admin_id = CAST(user_id AS SIGNED INT);

IF NOT EXISTS(SELECT 1 FROM `environment`.`environment_accounts` WHERE environment_id = env_id AND user_id = admin_id) THEN
    BEGIN
    INSERT INTO 
        `environment`.`environment_accounts` 
        (
            `environment_id`, 
            `user_id`, 
            `username`, 
            `password`, 
            `is_active`, 
            `is_admin`, 
            `is_mod`
        ) 
        VALUES 
        (
            env_id, 
            admin_id,
            username,
            password,
            1,
            1,
            1
        );
    END;
END IF;
END

So if I run:

CALL `environment`.`environment_admin`('22','1','kacieh','512c9ad228332bbd30d09ce7ffb8896e00a1610e914a5fa180bf15ce702b90423e6a9540579f672315ae3c6cb1b8d06ee2b784b4761e806675aa88c2a915553e');

I get 0 row(s) effected and sure enough, nothing happened. -_- I have been working on this hours I tested the conditional query, it works. I have tested just the insert statement inside a stored proc, it works as well.

Parallelis · Accepted Answer · 2012-08-30 04:42:41Z

5

Stop doing it like that, it's inefficient and it could be worse if two insertare running concurrently! :)

Use INSERT.... ON DUPLICATE KEY UPDATE ... see here

One trick is to do ON DUPLICATE KEY UPDATE environment_id = env_id (not changing the column, so nothing will be updated, ensuring the INSERT will not work without any error condition, you might check number of modified/inserted rows after that)

answered Aug 30, 2012 at 4:42

Parallelis

7293 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KacieHouser Over a year ago

but there can be many of the same environment_id's and many user_id's it is a many to many relationship. There will not be the same environment_id user_id combo though. I didn't make the pair the pk's though because I have other requirements that need those records to have a unique ID

KacieHouser Over a year ago

this should work if I have a unique index on the combination of the two fields. I'll have to see if I have that or get that in there. Give me a few

Parallelis Over a year ago

CREATE UNIQUE INDEX IX_environment_id_user ON environment ( environment_id, user_id);

KacieHouser Over a year ago

yep I got the index created, and that is much, much better, thank you so much

Hobo · Accepted Answer · 2012-09-03 15:28:37Z

I realise you've solved your problem (and +1 for Parallelis's answer, especially for highlighting the concurrency issue), but just in case it helps someone else...

MySQL was probably getting confused between your parameters environment_id and user_id and the environment_accounts columns environment_id and user_id. I suspect the parameters were taking precedence in the WHERE clause, meaning as long as there's at least one row in environment_accounts, the NOT EXISTS clause would always return false, and your insert would never run.

For example, if your environment_id and user_id parameters had values of 1 and 2 respectively, the NOT EXISTS clause would evaluate as

IF NOT EXISTS(SELECT 1 FROM `environment`.`environment_accounts` WHERE 1 = 1 AND 2 = 2) THEN

Might be worth having a naming convention for your parameters (and other variables), such as adding a prefix like p_ for parameter.

Collectives™ on Stack Overflow

MySQL Procedure check if record exists before insert not working

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related