Connection pool ignores SIGNALs after a successful query

[avoidwork]

Hi,

I noticed this today while a co-worker couldn't trigger an expected error case from our connection pool.

I use a UDF called "udf_uuidInternal()" to verify a record is valid, and it works fantastic in the DB, and from an sproc, but the dynamic SQL in place now returns the data for the last successful query even if a SIGNAL is sent saying the record is invalid.

If I start the API, and make a query with a known invalid UUID, I get the expected error and it's handled properly. If I then make a query with a valid UUID, I get the expected data. If I then re-use the first invalid UUID, get the data for the last successful query, without an error.

This is the UDF, it's pretty basic:

DROP FUNCTION IF EXISTS `udf_uuidInternal`;
delimiter ;;
CREATE FUNCTION `udf_uuidInternal`(xid char(40)) RETURNS bigint(20) unsigned
DETERMINISTIC
BEGIN
  DECLARE my_error CONDITION FOR SQLSTATE '45000';

  SELECT `uuid` FROM registry WHERE `guid` = xid INTO @result;

  IF @result IS NULL THEN
    SIGNAL my_error SET MESSAGE_TEXT = 'Record not found';
    SET @result = 'unknown';
  END IF;

  RETURN @result;
END
 ;;
delimiter ;

[avoidwork]

I got the 'okay' for a $40+ bounty on this bad boy. Willing to discuss further if anyone can get this fixed before next weds.

[kai-koch]

Your function returns a string, if @result is null, but should return a BIGINT or null.
Also I am not sure about the scope of the @result variable, so try the follwing:

DROP FUNCTION IF EXISTS `udf_uuidInternal`;
delimiter ;;
CREATE FUNCTION `udf_uuidInternal`(xid char(40)) RETURNS bigint(20) unsigned
DETERMINISTIC
BEGIN
  DECLARE my_error CONDITION FOR SQLSTATE '45000';
  DECLARE result BIGINT UNSIGNED;

  SELECT `uuid` FROM registry WHERE `guid` = xid INTO result;

  IF result IS NULL THEN
    SIGNAL my_error SET MESSAGE_TEXT = 'Record not found';
  END IF;

  RETURN result;
END
 ;;
delimiter ;

[avoidwork]

That's for logging purposes, and has no impact on the query because it doesn't run due to the SIGNAL from the UDF.

Sample usage:

SELECT * FROM users WHERE `id` = udf_internalUuid($uuid);

The driver bug shows itself by sending the rowpackets for the last query, because the 'invalid' ones never run.