MySQL stored procedure fails on second call on same connection with "SELECT command denied to user 'user'@'localhost' for table 'view_name'

I have a table called sales_observation_daily_summary which is a materialized view of sales_observation_daily_summary_view. I have defined a stored procedure called sync_daily_summary_view_with_table that will refresh the materialized view. Functionally, it works exactly as I expect. However, I have a strange bug when calling the stored procedure twice on the same connection (a likely scenario when using a connection pool). Originally this came up in my Java integration tests, but I am able to reproduce it on MySQL Workbench easily, so it shouldn't have anything to do with JDBC or Spring or anything in the middle like that.

call sync_daily_summary_view_with_table();
call sync_daily_summary_view_with_table();

On the first call, it does what it's supposed to do and returns normally. On the second call, I get:

Error Code: 1142
SELECT command denied to user 'test'@'localhost' for table 'one_pg_someone_sales_observation_daily_summary_view'

one_pg_someone_sales_observation_daily_summary_view is referenced in sales_observation_daily_summary_view, which is referenced in the stored procedure. The error message makes no sense as first of all, stored procedure did not object the first time it was run and second of all, that user has plenty of rights to select on that view.

I won't show all of the views involved as it is very complex, but the sales_observation_daily_summary_view is defined as a union of several other views thus:

CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`localhost` 
SQL SECURITY DEFINER 
VIEW `sales_observation_daily_summary_view` AS
        /* Specific Stage and Observer */
        SELECT zone,
               session_date,
               session_year,
               session_month,
               session_week,
               phenomenon_group_id,
               phenomenon_group_name,
               stage_id,
               stage_name,
               observer_id,
               series_name,
              开发者_StackOverflow社区 benchmark_id,
               session_count,
               session_value,
               benchmark_value
        FROM   one_pg_someone_sales_observation_daily_summary_view
        UNION ALL /* All Stages */
        SELECT zone,
               session_date,
               session_year,
               session_month,
               session_week,
               phenomenon_group_id,
               phenomenon_group_name,
               stage_id,
               stage_name,
               observer_id,
               series_name,
               benchmark_id,
               session_count,
               session_value,
               benchmark_value
        FROM   all_stages_someone_sales_observation_daily_summary_view
        UNION ALL /* All Activities */
        SELECT zone,
               session_date,
               session_year,
               session_month,
               session_week,
               phenomenon_group_id,
               phenomenon_group_name,
               stage_id,
               stage_name,
               observer_id,
               series_name,
               benchmark_id,
               session_count,
               session_value,
               benchmark_value
        FROM   all_activities_someone_sales_observation_daily_summary_view
        UNION ALL /* All Observers */
        SELECT zone,
               session_date,
               session_year,
               session_month,
               session_week,
               phenomenon_group_id,
               phenomenon_group_name,
               stage_id,
               stage_name,
               observer_id,
               series_name,
               benchmark_id,
               session_count,
               session_value,
               benchmark_value
        FROM   one_pg_everyone_sales_observation_daily_summary_view
        UNION ALL /* Everyone over All Stages */
        SELECT zone,
               session_date,
               session_year,
               session_month,
               session_week,
               phenomenon_group_id,
               phenomenon_group_name,
               stage_id,
               stage_name,
               observer_id,
               series_name,
               benchmark_id,
               session_count,
               session_value,
               benchmark_value
        FROM   all_stages_everyone_sales_observation_daily_summary_view
        UNION ALL /* Everyone over All Activities */
        SELECT zone,
               session_date,
               session_year,
               session_month,
               session_week,
               phenomenon_group_id,
               phenomenon_group_name,
               stage_id,
               stage_name,
               observer_id,
               series_name,
               benchmark_id,
               session_count,
               session_value,
               benchmark_value
        FROM   all_activities_everyone_sales_observation_daily_summary_view
        UNION ALL /* Benchmark */
        SELECT zone,
               session_date,
               session_year,
               session_month,
               session_week,
               phenomenon_group_id,
               phenomenon_group_name,
               stage_id,
               stage_name,
               observer_id,
               series_name,
               benchmark_id,
               session_count,
               session_value,
               benchmark_value
        FROM   benchmark_sales_observation_daily_summary_view

The stored procedure is defined thus:

DELIMITER $$

CREATE DEFINER=`test`@`localhost` PROCEDURE `sync_daily_summary_view_with_table`()
BEGIN

                /* Update any values that may have changed */
                UPDATE sales_observation_daily_summary tb, 
                       sales_observation_daily_summary_view vw 
                SET    tb.session_count = vw.session_count, 
                       tb.session_value = vw.session_count, 
                       tb.benchmark_value = vw.benchmark_value,
                       tb.series_name = vw.series_name 
                WHERE  vw.zone = tb.zone 
                       AND vw.session_date = tb.session_date 
                       AND Coalesce(vw.phenomenon_group_id, 0) = 
                           Coalesce(tb.phenomenon_group_id, 0) 
                       AND Coalesce(vw.stage_id, 0) = Coalesce(tb.stage_id, 0) 
                       AND Coalesce(vw.observer_id, 0) = Coalesce(tb.observer_id, 0) 
                       AND Coalesce(vw.benchmark_id, 0) = Coalesce(tb.benchmark_id, 0) 
                       AND ( Coalesce(tb.session_count, -1) <> Coalesce(vw.session_count, -1) 
                              OR Coalesce(tb.session_value, -1) <> 
                                 Coalesce(vw.session_value, -1) 
                              OR Coalesce(tb.benchmark_value, -1) <> 
                                 Coalesce(vw.benchmark_value, -1) 
                              OR tb.series_name <> vw.series_name ); 
END

I am using version 5.1.56-log on my local development box.

UPDATE 1 I have also reproduced the error on an Amazon RDS server version 5.1.57-log.

UPDATE 2 If I define the stored procedure to be SQL SECURITY INVOKER and execute it as root, it works fine. This is not an acceptable workaround, but it might be some kind of clue. (e.g. It is not a table locking issue.

UPDATE 3 The tables involved are InnoDB tables. I'm not sure if it's a clue, but when I added a Start Transaction at the beginning and a Commit at the end, it took much longer to complete, but then got the same error on the second invocation.

UPDATE 4 I have simplified the stored procedure and still reproduced the problem. It used to have an insert statement followed by an update statement. It turns out that the update statement is sufficient to reproduce the error, so I removed the insert statement from the stored procedure above.

What is the value of autocommit? select @@autocommit;

If the value is 0 try to add a commit between the two calls as you could have an open transaction somehow call sync_daily_summary_view_with_table(); commit; call sync_daily_summary_view_with_table();

Is the materialized table part of any of the views?

this looks like it might be a problem with allowing multiple statements in one query

this(http://dev.mysql.com/doc/refman/5.0/en/mysql-set-server-option.html) might be an option:

mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)

or better during connection:

mysql_real_connect (
    mysql, 
    host_name, 
    user_name, 
    password,
    db_name, 
    port_num, 
    socket_name, 
    CLIENT_MULTI_STATEMENTS)

you should also take a look here for a better explanation: http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

This may be a transaction problem. Try adding a COMMIT after the UPDATE and INSERT statements. You might also want to try using InnoDB if you aren't already.

You should try the function like this to see if you get the same result:

DELIMITER $$

CREATE DEFINER=`test`@`localhost` PROCEDURE `sync_daily_summary_view_with_table`()
BEGIN

            /* Update any values that may have changed */
            UPDATE sales_observation_daily_summary tb, 
                   sales_observation_daily_summary_view vw 
            SET    tb.session_count = vw.session_count, 
                   tb.session_value = vw.session_count, 
                   tb.benchmark_value = vw.benchmark_value,
                   tb.series_name = vw.series_name 
            WHERE  vw.zone = tb.zone 
                   AND vw.session_date = tb.session_date 
                   AND Coalesce(vw.phenomenon_group_id, 0) = 
                       Coalesce(tb.phenomenon_group_id, 0) 
                   AND Coalesce(vw.stage_id, 0) = Coalesce(tb.stage_id, 0) 
                   AND Coalesce(vw.observer_id, 0) = Coalesce(tb.observer_id, 0) 
                   AND Coalesce(vw.benchmark_id, 0) = Coalesce(tb.benchmark_id, 0) 
                   AND ( Coalesce(tb.session_count, -1) <> Coalesce(vw.session_count, -1) 
                          OR Coalesce(tb.session_value, -1) <> 
                             Coalesce(vw.session_value, -1) 
                          OR Coalesce(tb.benchmark_value, -1) <> 
                             Coalesce(vw.benchmark_value, -1) 
                          OR tb.series_name <> vw.series_name ); 
            COMMIT;
END

I don't know if you guys have found a solution for this problem. I ran into same and was able to fix it!

The problem that I got just happen when I tried to make a select in a view through a procedure. Ar first time it just worked fine, but from second time foward it was reponding a command denied message for my user.

The solution was create the view with an "administrator" user, an make the same with the procedure (create it with an "administrator" user), with those processes I got it run!

The problem is that I don't know how exactly, MySQL uses another user to make the select through the view, it doesn't use your definer or logged session user, and at the second time, this "internal" user get comamnd denied from view.

I hope to help you guys solve this problem at all!