THE BOOK--Playing The Percentages In Baseball

Wednesday, September 08, 2010

MySQL VIEW access problem

By Tangotiger, 02:32 PM

I tried to re-create a view (which was successful), but for some reason when I do a SELECT, I got this crazy error:
#1356 - View ‘XX.YY’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

In order to get around this, I changed:
create or replace view YY as
into
create table YY as

This error seems old and related to bug from previous versions of PHP. I can tell you that my host, last week, upgraded our server:

Custom PHP
If you built your own custom PHP (or php.ini), it may be broken now. This is because the old servers used a 32bit architecture and the new servers are 64bit. Unfortunately, this isn’t something we can help you troubleshoot, but you’ll just need to rebuild your custom php (or php.ini) again using the new servers libraries.

While PHP has nothing to do with my creating VIEWS with SQL, I am using phpMyAdmin.

Thoughts?

• Web Admin

#1 (see all posts) 2010/09/08 (Wed) @ 15:09

Do you get the same problem if you do the query/select in command line?

#2 Tangotiger (see all posts) 2010/09/08 (Wed) @ 15:19

I don’t have access to command line from the office.

#3 ryan jl (see all posts) 2010/09/08 (Wed) @ 15:28

Try using mysql woekbench or GUI tools instead of phpmyadmin. I prefer it personally.

#4 weskelton (see all posts) 2010/09/08 (Wed) @ 15:56

Is the id that you’re using to create the view, also the owner of the underlying table(s)? If not, you’ll need to grant the view-creating id “select” permissions on the tables.

#5 wcw (see all posts) 2010/09/08 (Wed) @ 16:03

I’d bet it’s a CREATE VIEW issue. Maybe INVOKER versus DEFINER or vice-versa.

#6 Tangotiger (see all posts) 2010/09/08 (Wed) @ 16:23

wes: yes, I am using the same ID. In any case, I was able to do the create table with the same ID, so I don’t see why I’d have a problem. I also created brand new tables, and views on those tables, to the same effect. So, definitely am in with the same ID.

***

ryan: I’ll try that at home. I have mysql workbench there.

***

wcw: what is it you want me to try?

#7 weskelton (see all posts) 2010/09/08 (Wed) @ 16:42

Does the ID that is trying to query the view have “select” permissions on the underlying tables in the view? Not sure if this should matter though.

#8 Tangotiger (see all posts) 2010/09/08 (Wed) @ 16:54

I don’t see how it could not, since that user created the table, not to mention, like I said, I was able to do CREATE TABLE xxx as SELECT ...

#9 David Pinto (see all posts) 2010/09/08 (Wed) @ 17:17

MySQL views are simply buggy. They don’t let you get at parameters for creating and altering them. MYsql needs to fix this.

#10 wcw (see all posts) 2010/09/09 (Thu) @ 09:56

Assuming you already tried an explicit ‘grant select on XX.YY,’ drop your view and try your create with ‘sql security invoker’ or ‘..definer’. My bet is that the default changed to whichever of those doesn’t work for you.

#11 Tangotiger (see all posts) 2010/09/09 (Thu) @ 10:31

Ok, I’ll try that. I’m very new to MySQL (most of my work was in Oracle), so, I’m not up on all the little things specific to MySQL.

***

Also, perhaps unrelated, but I am now unable to EXPORT the entire DB through phpMyAdmin, as one or more tables is causing me issues. This is what I’ve been getting:

#1345 - EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/dh/web/phpmyadmin/export.php:190) in /usr/local/dh/web/phpmyadmin/libraries/header_http.inc.php on line 28

I should note that I’ve never had a problem and this just happened. The only change I made was to try to recreate the VIEW.

So, I agree something with privileges occurred over the weekend, which may or may not be related to the 64-bit upgrade.

#12 Tangotiger (see all posts) 2010/09/09 (Thu) @ 11:28

Awesome guys. I need to explicitly note:

SQL SECURITY INVOKER

#13 Tangotiger (see all posts) 2010/09/10 (Fri) @ 10:49

Curioser and curioser…

I ran my test cases (see below) under the following database / schemas:
1. Problem database, problem schema
2. Problem database, other schema
3. Other databases, other schemas

Only in situation #1 do I have a problem.

What are the commands in MySQL that lets me look at the TABLE owners, and whoever has been GRANTed access?

***

CREATE TABLE TEMP_TABLE1 (
    TEMP_FIELD1 int
);

CREATE VIEW TEMP_VIEW as
    SELECT * from TEMP_TABLE1
;

CREATE SQL SECURITY DEFINER VIEW TEMP_VIEW_DEFIN as
    SELECT * from TEMP_TABLE1
;

CREATE SQL SECURITY INVOKER VIEW TEMP_VIEW_INVOK as
    SELECT * from TEMP_TABLE1
;


SELECT * FROM TEMP_VIEW;
SELECT * FROM TEMP_VIEW_DEFIN;
SELECT * FROM TEMP_VIEW_INVOK;

-- The first two SELECTS return an error message, and the third SELECT returns successfully.

DROP VIEW TEMP_VIEW;
DROP VIEW TEMP_VIEW_DEFIN;
DROP VIEW TEMP_VIEW_INVOK;
DROP TABLE TEMP_TABLE1;

Page 1 of 1 pages

<< Back to main

THE BOOK--Playing The Percentages In Baseball

Wednesday, September 08, 2010

MySQL VIEW access problem

Latest...