MySQL Forums
Forum List  »  Stored Procedures

Function corrupts information_schema.routines table, results in error 1102
Posted by: Jack Barney
Date: July 08, 2015 03:13PM

Hi,

I have a function that compiles without error or warning, but seems to be corrupting the information_schema.routines table, causing SELECT * FROM information_schema.routines; to return with
Error Code: 1102. Incorrect database name 'usr_test '
(Note all of the whitespace after the table name).

I'm using MySQL Server version 5.6.23 on Windows 64-bit, running Windows 7.

Here is the function definition:
DELIMITER $$
CREATE DEFINER='usr_test_admin'@'127.0.0.1' FUNCTION usr_test.FORMAT_EMAIL(
p_email VARCHAR(96)
)
RETURNS VARCHAR(96)
COMMENT 'Returns consistent email address or NULL if invalid.'
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
DECLARE l_at_idx INTEGER DEFAULT 0;
DECLARE l_result VARCHAR(96) DEFAULT NULL;

IF p_email IS NOT NULL
AND TRIM( p_email )<>'' THEN
SET l_at_idx=LOCATE( '@', p_email );
IF 1<l_at_idx
AND l_at_idx<CHAR_LENGTH( p_email )
AND LOCATE( '@', p_email, l_at_idx+1 )<1 THEN
SELECT TRIM( CONCAT( SUBSTRING( p_email, 1, l_at_idx ), LOWER( SUBSTRING( p_email FROM l_at_idx+1 ) ) ) )
INTO l_result
FROM DUAL;
END IF;
END IF;

RETURN l_result;

END$$

It doesn't prevents existing stored procedures and functions from executing from the commandline or workbench client, but fails for all calls through connector/J (I'm assuming this is because it can't retrieve the body of the procedure). Has anyone encountered this before. I've gutted the function, returning a simple VARCHAR value and still am getting the same result after compiling. Once I DROP the function, the information_schema.routines table is accessible again and connector/J works as expected.

Thanks,
Jack

Options: ReplyQuote


Subject
Views
Written By
Posted
Function corrupts information_schema.routines table, results in error 1102
2648
July 08, 2015 03:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.