error on database view on hosted server - localhostworks fine

[JNArcen]

Hello,

In my fields.ini of the lob table i have the following start line:
__sql__ = "select a.*, b.lev_naam, b.lev_relatiecode from lob a left join leveranciers b on a.lob_lev_id=b.lev_id"

When i open the lob table in the interface on the localhost erverything works fine. Also when i click the view in phpmyadmin everything works fine.

But when I uploaded the code and the tables to the hosted server i get an error message:

Uncaught exception was thrown while processing this request. Troubleshooting steps:

1. Refresh the APC Cache. - This may help in cases where you have changed a table column definition and your server has the APC opcode cache installed.
2. Clear Cache Views - This may also help in cases where you have changed a table column definition and some tables include __sql__ definitions.
3. Check your PHP error log for a description of the error and go from there. See this page for troubleshooting tips.

Fatal error: Uncaught exception 'Exception' with message 'SELECT command denied to user 'usernameofthedatabase'@'%' for column 'lob_lev_id' in table 'lob'SELECT COUNT(*) as num FROM `dataface__view_859c0efea6466122116c4da0dff97f5b` as `lob`' in /var/www ....................

In the phpmyadmin i get the following error when i click on this view:

#1356 - View 'databasenaam__view_9a80ef8a19d6aae41d76c747d1f0127a' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I searched for a solution and came across this:

A view created in a hosted MySQL database cannot be used afterwards by the same database user. (http://kb.sp.parallels.com/en/113106)

What's the problem here? And is there a workaround ?
I'm using the __sql__ statement to get the right field information like lev_naam (is the name) and lev_relatiecode (is the relationcode) instead of the id value.

Hope someone can help me with this.

Kind regards.
Johan

[Steve Hannah]

Sometimes this happens when you dump databases from one server and import into another.  You should just delete all of the views in the database of the form "dataface__view_xxx".  They are automatically generated anyways, so this is safe to do.

Steve

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
Visit this group at http://groups.google.com/group/xataface.
To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/0098197b-7acd-49e6-9a0c-0e03bc14adc3%40googlegroups.com.

--

Steve Hannah

Web Lite Solutions Corp.

[JNArcen]

Hello Steve,

Thanks for your response. I already deleted the views on the hosted server and in the dataface__mtimes table and just did it again, but I still get the same error message.
Could something else be wrong? On my localhost everything works just fine.

Kind regards.
Johan

Op woensdag 3 december 2014 18:26:48 UTC+1 schreef Steve Hannah:

[Steve Hannah]

It's a user permissions issue.  Check that your user has select access to that view.  

To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/4c1918c5-8c1d-4945-a603-49fd8f7119d6%40googlegroups.com.

[JNArcen]

It's a user I created with the admin panel of the shared host. I am not able to change any permission for this database user. Normally it should have all the permissions granted, but now I'm not so sure of this is true.

I'm going to try another shared host one of these days and hope the problem has resolved.

Thanks for your help.

Op donderdag 4 december 2014 20:51:13 UTC+1 schreef Steve Hannah:

[JNArcen]

Hi Steve,

I moved the web application to another hosted server and now I don't get an error message anymore.

You already told me that it's an permission issue, so it is.

Has anyone experienced problems before with different hosted servers and permission of the database user?

Thanks Steve for your help!

Op donderdag 4 december 2014 20:51:13 UTC+1 schreef Steve Hannah:

It's a user permissions issue.  Check that your user has select access to that view.