Description:
MySQL Internals manual (https://dev.mysql.com/doc/internals/en/sp-cache-sp.html) says:
"There is a global mechanism to invalidate all the caches of all the THD threads at once, implemented with the variable Cversion in file sp_cache.cc, which is incremented by function sp_cache_invalidate(). This global invalidation is used when the server executes DROP PROCEDURE or UPDATE PROCEDURE statements."
Indeed, invalidation works this way, but when ALTER PROCEDURE is executed:
...
mysql> call sp1;
ERROR 1370 (42000): execute command denied to user 'uv'@'localhost' for routine
'test.sp1'
mysql> show create procedure sp1\G
*************************** 1. row ***************************
Procedure: sp1
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
Create Procedure: CREATE DEFINER=`uv`@`localhost` PROCEDURE `sp1`()
select 'a' as a
character_set_client: cp866
collation_connection: cp866_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> alter procedure sp1 sql security definer;
Query OK, 0 rows affected (0.00 sec)
mysql> show create procedure sp1\G
*************************** 1. row ***************************
Procedure: sp1
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`()
select 'a' as a
character_set_client: cp866
collation_connection: cp866_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> call sp1;
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
UPDATE PROCEDURE gives syntax error, surely:
mysql> update procedure sp1 sql security definer;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'proce
dure sp1 sql security definer' at line 1
How to repeat:
Try to follow the manual and cause stored procedures caches invalidation for existing connections, for example, after changing the definer for the procedure explicitly vith UPDATE to mysql.proc table.
Suggested fix:
Please, replace UPDATE PROCEDURE with ALTER PROCEDURE in the Internal manual.
