1142 SHOW VIEW command denied to user

Yii 1.1 General Discussion
1

That’s another problem I am facing in using YII.

I have 2-3 views defined in mysql, each taking data from 3-4 tables.

These views are running perfectly fine on my local machine, both using the old php script and the new one using YII.

However on the server, the YII script generates the following error.




CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1142 SHOW VIEW command denied to user 'xxx_xxx'@'localhost' for table 'herb_price_list'

Where herb_price_list is a view

Viewable here

Googling this error suggests that this had occurred in Prado and upgrading resolved this.

But alas, nothing happens in YII, as upgrading to 1.1.5 does no good.

Further googling suggests its a syntax error pertaining to Mysql Version 5.0.xx and mysql 5.2 has resolved this. I checked, my linux host is running 5.0 community, but my local machine is running mysql 5.1.xx and is working great in it.

Further checking revealed that my old script is still working on the same server,

Visible here

This roles out mysql, linux, and windows from the scenerio, leaving only YII as causing factor.

Now how to resolve it?

2

Its a long time, and no YII guru is available here to resolve it???

3

But as it says "command denied to user" could it be that the user you are connecting to the database does not have right permissions?

At least you can try that command from phpMyAdmin for example… just to see if it’s permission problem…

4

Thanks for notifying that, but it isn’t the problem

because the same script runs 3 queries in total and two of them are before this query dealing with view.

Secondly, as we have dealt with each other, I am running my old script parallel to yii script, the old script here again is working correctly.

So there is no permission issue for that mysql user.

5

Your old code maybe does not use that command "SHOW VIEW"… and the other3 queries can be anything else other that the command "SHOW VIEW"…

If you have phpMyAdmin than for the user permissions you will see that there is a option/permission SHOW VIEW under structure… so it’s still possible that your user does not have that permission… at least you should check this…

Or at least… if you know the password for the root user… try to run the application with the root mysql permissions…

6

Well, I have checked the mysql database and user, the user has all the permissions.

But don’t the permission to execute ‘SHOW CREATE VIEW’ command, also the user cannot create a view.

But mysql database User Privileges are all checked.

How to enable the mysql user to create and read views?

7

You use the GRANT mysql command like:


grant show view on * to <username>

Or if you use phpMyAdmin… you can just check that permission

8

Well to be truth full

I have never used the mysql console

and cannot find the options/permissions under structure in phpmyadmin (both local machine and server).

Any help in this regard will be appreciated.

9

Here is the relevant screenshot of my phpMyAdmin (version 3.3.3)

10

In the end, the problem is, I have this option in my local phpmyadmin, but not at the server.

Further, connecting to mysql using mysql gui does not enables the User Administration.

I have contacted the server’s technical staff, if they can help.

Or is there any other way to proceed?

11

Seems that on the remote server you don’t have access to that, so the only way is that the servers administrators enable this option for you…

12

Well, the server guys has resolved the problem, and added the permission for views.

Thanks for your kind help in identifying the problem, and giving me a dive in the mysql permissions system.

13

Glad we solved this…

I saw a issue ticket for this - http://code.google.com/p/yii/issues/detail?id=1930#c0

Can I invalidate this ticket?

14

Yes you can cancel that ticket, but we need a comprehensive guide to using mySql VIEWS in Yii.

Isn’t it?

I mean the basic problem arises in views that yii does not identifies their primary key.

and that function has to be over ridden in model class.

secondly, yii identifies the view and uses the SHOW CREATE VIEW command instead of SHOW CREATE TABLE, which will return the same result anyway.