I have a very simple query that works fine:
SELECT * FROM images i INNER JOIN v_images_stats s ON i.id = s.id
In SELECT * I end up creating duplicate column names, so I edited the query to be more specific and ignored duplicate column names, but MySQL throws error # 1356:
SELECT i.is as id, s.id as imageid FROM images i INNER JOIN v_images_stats s ON i.id = s.id
# 1356 - refer to "events.v_image_stats" invalid tables or columns (columns) or functions or qualifier / invoker of view do not have the right to use them
The v_image_stats view and the base views and the tables on which it is based have the correct privileges, I'm completely confused.
I use MySQL 5.6 and phpmyadmin to create and manage views, I also access DB through PHP PDO as part of Phalcon, but I'm sure this is a MySQL problem, not something else.
Any help gratefully received.
I originally published the full output of SHOW CREATE VIEW, but it was too detailed, here is the simplest example that I can create to recreate the problem:
CREATE ALGORITHM=UNDEFINED DEFINER=`events`@`localhost` SQL SECURITY DEFINER VIEW `v_image_stats` AS ( select it.*, 1 AS `my_alias` from `v_image_totals` `it` order by `my_alias` asc) )
It also fails if I use a table instead of a view in the FROM clause.
source share