错误描述
执行如下sql报错
SELECT
area_id,
getAreaCodeName_func(area_id)AS area_val
FROM
agent_area_info
WHERE
agent_id =(
SELECT
agent_id
FROM
agent_info
WHERE
user_id = 'UID081508893860976'
LIMIT 1
)
AND is_deleted = 0
AND area_state = 0
AND end_date > now();
报错内容为:
[Err] 1449 - The user specified as definer ('bforce'@'%') dose not exist
分析
在执行getAreaCodeName_func这个函数时,会报错。
提示没有bforce这个用户不存在,就是bforce不具备操作getAreaCodeName_func函数的权限。
因为年前数据库备份,使用了root用户创建的函数。所以认为当前用户没用权限。
解决办法
连接mysql,授权给bforce用户所有sql的权限。
login as: root
root@192.168.18.242's password:
Last login: Wed Mar 7 13:52:56 2018 from 192.168.18.187
[root@test ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3170204
Server version: 5.7.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all privileges on *.* to bforce@"%" identified by ".";
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)