The most important thing to understand when coming upon an error is to take a moment and relax.
Sometimes people run across an error and become flustered and frustrated. Take a deep breath and relax. Sometimes "Madness is like gravity. All that needs is a little push" and “Sometimes the questions are complicated and the answers are simple.”
What is all this about? Well for example I recently was asked to help solve a problem that was troubling another DBA. Nothing meant in disrespect at all to that DBA but being in a rush people just overlook the answer that is in front of them and instead spiral down into madness.
Here is the general concept of the situation.
After doing a mysqldump of a database the user moved it to another database for others to being testing. The import worked just fine. However, the users began to see errors when they tried to query from a prebuilt view.
mysql> select * from <View_name> limit 1;
ERROR 1356 (HY000): View '<DB_NAME>.<ViewName>' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
The first reaction was to assume that the something went wrong with the import. Did the view or actually the related tables become corrupt?
No.
To solve this error the first thing todo was to: mysql> show create view <View_Name> \G
You likely will see something that begins like this:
*************************** 1. row ***************************
View: Visitors
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`<Some_user>`@`<Some_host_or_IP>` SQL SECURITY DEFINER VIEW
After you see who the definer is : `<Some_user>`@`<Some_host_or_IP>` you can view the grants for that user.
mysql> show grants for `<Some_user>`@`<Some_host_or_IP>`;
It turns out the the solution was just what the error said in the first place: "definer/invoker of view lack rights to use them"
To make it easy you can do a show grants in the database where it already works and copy the grant statement, or you can review what type of permissions you need to implement in the new database to enable the view to gather the required data.
A simple update of permissions for the user and the error was gone.
“Sometimes the answers are simple.”
Sometimes people run across an error and become flustered and frustrated. Take a deep breath and relax. Sometimes "Madness is like gravity. All that needs is a little push" and “Sometimes the questions are complicated and the answers are simple.”
What is all this about? Well for example I recently was asked to help solve a problem that was troubling another DBA. Nothing meant in disrespect at all to that DBA but being in a rush people just overlook the answer that is in front of them and instead spiral down into madness.
Here is the general concept of the situation.
After doing a mysqldump of a database the user moved it to another database for others to being testing. The import worked just fine. However, the users began to see errors when they tried to query from a prebuilt view.
mysql> select * from <View_name> limit 1;
ERROR 1356 (HY000): View '<DB_NAME>.<ViewName>' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
The first reaction was to assume that the something went wrong with the import. Did the view or actually the related tables become corrupt?
No.
To solve this error the first thing todo was to: mysql> show create view <View_Name> \G
You likely will see something that begins like this:
*************************** 1. row ***************************
View: Visitors
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`<Some_user>`@`<Some_host_or_IP>` SQL SECURITY DEFINER VIEW
After you see who the definer is : `<Some_user>`@`<Some_host_or_IP>` you can view the grants for that user.
mysql> show grants for `<Some_user>`@`<Some_host_or_IP>`;
It turns out the the solution was just what the error said in the first place: "definer/invoker of view lack rights to use them"
To make it easy you can do a show grants in the database where it already works and copy the grant statement, or you can review what type of permissions you need to implement in the new database to enable the view to gather the required data.
A simple update of permissions for the user and the error was gone.
“Sometimes the answers are simple.”
I am getting this error as root@localhost! I thought root could do anything
ReplyDeleteHere is what show create view displays
CREATE ALGORITHM=UNDEFINED DEFINER=`abc`@`localhost` SQL SECURITY DEFINER VIEW `open_claim` AS select ...list of columns... where isnull(`claim`.`checkout_timestamp`)
regarding my earlier question as to why root can't select from the view, the solution turned out to be add SECURITY INVOKER VIEW to the create view statement.
ReplyDeleteGlad it worked out for you. Sorry I didn't see your comments sooner.
ReplyDelete