Mysql Error 1356 - Views

Question

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?

Answer 1

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.