mysql调用存储过程1305错误_【MySQL】调用储存过程显示 ERROR 1305 (42000): PROCEDURE test.sp1 does not exist...

问题描述：

1.在MySQL中创建了一个简单的查询存储过程：

mysql> CREATE PROCEDURE spl() SELECT VERSION();

Query OK, 0 rows affected (0.01 sec)

2.然后用CALL调用此存储过程，报错 ERROR 1305 (42000): PROCEDURE test.sp1 does not exist：

mysql> CALL sp1;

ERROR 1305 (42000): PROCEDURE test.sp1 does not exist

问题排查：

1.首先考虑是否是此储存过程当真不在，查看当前存储过程，发现存储过程存在：

mysql> SHOW PROCEDURE STATUS;

+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |

+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| test | spl | PROCEDURE | root@localhost | 2019-02-28 13:00:02 | 2019-02-28 13:00:02 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |

+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

1 row in set (0.01 sec)

2.此时想到是用户没有当前存储过程的调用权限，赋予存储过程权限给当前用户，此时又出现了一个错误：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

ERROR 1133 (42000): Can't find any matching row in the user table

通过查询资料发现，需要先将当前的存储过程刷新，再执行授权语句：

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@localhost;

Query OK, 0 rows affected (0.00 sec)

3.再执行调用此存储过程，显示成功调用：

mysql> SHOW PROCEDURE STATUS;

+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |

+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| test | spl | PROCEDURE | root@localhost | 2019-02-28 13:11:23 | 2019-02-28 13:11:23 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |

+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

1 row in set (0.01 sec)

mysql> CALL spl;

+-----------+

| VERSION() |

+-----------+

| 5.5.40 |

+-----------+

1 row in set (0.00 sec)