Fiddling with MySQL

Let us have a look at a simple procedure in MySQL 5.6:

DELIMITER $
CREATE DEFINER=`root`@`%` PROCEDURE `validate_storage`(IN type INT, IN option INT, OUT result varchar(3))
BEGIN
  DECLARE id1 INT;
  SELECT id into id1 FROM storage_type WHERE id=type$
  SELECT id into @id2 FROM cache_option_id WHERE id=option$
  SELECT IF((id1 = type AND @id2 = option),'yes', 'no') into result$
END;
DELIMITER ;

Easy one. A note about variables. id1 is a local/user defined variable, the scope is limited to the BEGIN/END block, @id2 is a global variable, accessible from everywhere. User defined variables need to declared with DECLARE.

Here we have got a procedure which stores a transaction:

DELIMITER //
CREATE PROCEDURE EventDate_Update_test(OUT isvalid INT)
BEGIN
  START TRANSACTION;
  DECLARE testzeit DATE;
  SET testzeit = CURDATE();
  UPDATE `blabli` SET`Status`= 4 WHERE `EventDate` <= testzeit;
  IF NOT EXISTS (SELECT `Status` FROM `blabli` WHERE `EventDate` <= testzeit AND NOT `Status`= 4)
  THEN
    isvalid = 1;
    COMMIT;
  ELSE
    isvalid = 0
    ROLLBACK;
  END IF;
end //
DELIMITER ;

This will throw you an error, you can not use the variable testzeit. Why? because the transaction makes any magic and doesn’t allow local variables, even if the procedure is in a BEGIN/END statement. I did not found this edge case in the MySQL docs, I am not sure if it is not documented or if I missed it. Here is the final working version:

DELIMITER //
CREATE PROCEDURE EventDate_Update_test(OUT isvalid INT)
BEGIN
  START TRANSACTION;
  SET @testzeit = CURDATE();
  UPDATE `blabli` SET`Status`= 4 WHERE `EventDate` <= @testzeit;
  IF NOT EXISTS (SELECT `Status` FROM `blabli` WHERE `EventDate` <= @testzeit AND NOT `Status`= 4)
  THEN
    isvalid = 1;
    COMMIT;
  ELSE
    isvalid = 0
    ROLLBACK;
  END IF;
end //
DELIMITER ;

Thanks MySQL! Took me 30min to fiddle this out!