CODEWITHSUNDEEP
mysql
Guilherme Soares
Guilherme Soares

Reputation: 736

Mysql Error 1356 - Views

When i'll try create some but when i call it appears the error 1356:

Creating the View

CREATE VIEW monitoring_consult AS (
    SELECT 
        m.id,
        account.valor AS 'phone_number',
        IF((c.valor REGEXP '^[0-9]+$' OR c.valor IS NULL) AND cn.short_name IS NOT NULL, cn.short_name, c.valor) AS 'category',
        IF(pn.id IS NOT NULL, pn.id, p.valor) AS 'provider',
        n.valor AS 'nominal',
        m.last_page,
        pn.name AS 'provider_name',
        IF(pay.valor is null, 'Uncompleted', pay.valor) AS 'payment',
        timeEnd,
        DATE_FORMAT(m.timeEnd, '%d/%m/%Y') as 'date'
    FROM
        monitoring AS m
            LEFT JOIN feature   AS account ON m.id = account.id AND account.valor IS NOT NULL AND (account.page = 'PV') AND account.type = 'send'
            LEFT JOIN feature   AS c    ON m.id = c.id_monitoring   AND c.valor IS NOT NULL AND (c.page = 'MA' OR c.page = 'IN') AND c.type = 'select'
            LEFT JOIN feature   AS p    ON m.id = p.id_monitoring   AND p.page = 'PO' AND p.valor IS NOT NULL AND p.type = 'select'
            LEFT JOIN feature   AS n    ON m.id = n.id_monitoring   AND n.valor IS NOT NULL AND n.page = 'OAP' AND n.type = 'select'
            LEFT JOIN feature   AS pay  ON m.id = pay.id_monitoring AND m.last_page = 'OK' AND pay.type = 'userAction'  AND pay.name = 'paymentStatus' AND pay.valor = 'Completed'
            LEFT JOIN terminais AS term ON m.id_terminal = term.id
            LEFT JOIN provider  AS pn   ON (p.valor = pn.id) OR (c.valor REGEXP '^[0-9]+$' AND c.valor = pn.id)
            LEFT JOIN category  AS cn   ON pn.id_category = cn.id
        group by m.id
        having category is not null
)

Calling the view:

select * from monitoring_consult

Return:

Error Code: 1356. View 'qiwi.monitoring_consult' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Mysql Version - 5.5.32-log

Do someone know why this happened?

Upvotes: 5

Views: 36821

Answers (7)

auphill
auphill

Reputation: 11

This message can sometimes be misleading. I was getting the same warning even though i had made sure that the definer user had the correct permissions and all the referenced tables/fields were OK.

After running

show create view viewName;

I noticed that the output contained 1 warning so after that I run

show warnings;

And that showed me the actual problem.

Upvotes: 0

Geraldine Harms
Geraldine Harms

Reputation: 1

I had the same problem. While I still don't know what it caused the problem, because none of the tables the view uses had been changed.

My solution was to drop the old view and create a new one this time I specified at the top of my view the following. 

use databasename;

Upvotes: 0

Massimiliano
Massimiliano

Reputation: 122

this particular problem happen to me every time i imported a mysqldump export, with automatic views creation inside it,

such problem came out because of the creation of the view in the mysqldump (when you reimport again), with "SQL SECURITY DEFINER",

i don't known if it is a mysql bug, but for me deleting and applying new permission to the view, does not solve the problem, recreating the view without the "SQL SECURITY DEFINER", solve my problem, i hope this is your case.

Ciao.

Upvotes: 10

Samuel Vicent
Samuel Vicent

Reputation: 1031

Hi I got this error after changing my database model. Happened that an existing view referenced non existing fields.

So the solution was delete the current view and create a new one, considering the new model instead of focusing on user privileges.

Hope it helps to someone :)

Upvotes: 0

Mike PG
Mike PG

Reputation: 231

I was getting the same error code and was able to pin it down somewhat. It appears to arise when an object two (or more?) levels down is no longer available. Here is some code to recreate the issue.

create table `table1` (`id` int(11), `col1` varchar(16);
insert into `table1` (`id`, `col1`) values (1, 'Foo'), (2, 'Bar');

create view `view_sub` as SELECT `id`, `col1` from `table1`;
# if you drop here you'll get error code 1146: table1 doesn't exist

create view `view_err` as SELECT `id`, `col1` from `view_sub`;
drop table `table1`;

select * from `view_err`
# should get you error code 1356: references invalid table(s), etc

I don't know if anything in your from clause is a view, but the (what appears to be) typo of terminais may be causing it.

Upvotes: 1

Simon
Simon

Reputation: 17

When creating the view, try to specify a definer like this:

    CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `user`@`%` 
    SQL SECURITY DEFINER
VIEW `monitoring_consult` AS ...

where the DEFINER usermust have the appropriate rights to any table that is linked to the view

Upvotes: 0

Shafeeque
Shafeeque

Reputation: 2069

These are the cases

  1. MySQL loggined user has not the privilege view the monitoring_consult view
  2. Any of your joined table not exist
  3. Any of your field that you are trying fetch not exist ( You might be deleted )

Upvotes: 1

Related Questions