[Err] 1449 - The user specified as definer ('bforce'@'%') dose not exist

错误描述

执行如下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)