mysql - 更新失败 - 选择被拒绝

Question

我有一个无法解决的 mysql 授权问题。

mysql>  UPDATE frontier_remote.trident_update SET completed=NOW() WHERE mac_address="00:1b:24:a0:da:e9" AND completed IS NULL;
ERROR 1143 (42000): SELECT command denied to user 'trident_client'@'host-78-147-8-82.as13285.net' for column 'mac_address' in table 'trident_update'
mysql> SELECT mac_address from trident_update WHERE mac_address="00:1b:24:a0:da:e9" and completed is NULL;
+-------------------+
| mac_address       |
+-------------------+
| 00:0:de:ad:be:ef |
+-------------------+
1 row in set (0.04 sec)

因此更新声称在选择中失败，但命令的选择部分似乎可以自行运行。

grants 表中的相关条目如下所示:

GRANT USAGE ON *.* TO 'trident_client'@'%' IDENTIFIED BY PASSWORD 'shadow_password'
GRANT INSERT, UPDATE ON `frontier_remote`.* TO 'trident_client'@'%'
GRANT SELECT ON `frontier_test`.`trident_update` TO 'trident_client'@'%'

有什么想法吗？

Answer 1

执行以下命令:

FLUSH PRIVILEGES

Reloads the privileges from the grant tables in the mysql database. On Unix, this also occurs if the server receives a SIGHUP signal.

The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

文档:FLUSH