Views in MySQL

Posted: Fri Feb 19, 2010 1:11 pm Post subject: Views in MySQL

Could you clarify for me how views are handled @Nethosted implementation of MySQL?

I am running version 5.1.36 locally and have no problems with views which were introduced in 5.0. I just uploaded a set of tables which include views, all the tables went up except the first one which happens to be a view, a combination of real tables. The process tells me I don't have the necessary superuser privileges.

In addition while the basic tables are fine, the other views don't show any data. It looks like the system has a problem with "CREATE ALGORITHM".

NetHosted Staff Joined: 24 Jun 2005 Posts: 1021

Hi,

Can you tell me what user that you are attempting to import this SQL with, I will check they have the appropriate privileges.

Regards,

Darryl

Posted: Fri Feb 19, 2010 1:28 pm Post subject:

Will contact you by PM to provide personal details

Here is a link which describes this error, may be of help.

http://www.hostingrails.com/MySQL-Error-1227-Access-Denied-you-need-SUPER-privilege-for-this-operation

NetHosted Staff Joined: 24 Jun 2005 Posts: 1021

Hi,

This seems to suggest that trying to run this via PHPMyAdmin may work, have you already attempted this?

Regards,

Darryl

Posted: Fri Feb 19, 2010 1:42 pm Post subject:

Solved:

When mysql creates the SQL for the CREATE ALGORITHM statement it includes a DEFINER='xxxxx'@'xxxxx' section in the statement. It does no good to change this to xxxx@your_real_database_at_nethosted, you have to drop that part entirely.

With this removed the import goes fine and even though on rollover it appears to show 0 records, on double click you see what you expect to see.

Edit: I was and am using PHPMyAdmin

NetHosted Staff Joined: 24 Jun 2005 Posts: 1021

Hi,

Ah fair enough, glad that you got this solved and thank you for posting the solution, I'm sure this will be of help to others!

Regards,

Darryl

Client Support	Community	Server Status	Contact Us	Client Login
Email Hosting	Website Hosting	Reseller Hosting	VPS Hosting	Dedicated Servers