MySQL stored procedure does not work in the second call with the same connection with the "SELECT" command, which is denied to the user "@" localhost for the table "exit_name"

Question

MySQL stored procedure does not work in the second call with the same connection with the "SELECT" command, which is denied to the user "@" localhost for the table "exit_name"

I have a sales_observation_daily_summary table, which is a materialized view of sales_observation_daily_summary_view. I have defined the sync_daily_summary_view_with_table stored procedure that will update the materialized view. Functionally, it works exactly as I expect. However, I have a strange error when calling a stored procedure twice in the same connection (likely scenario when using the connection pool). This initially happened in my Java integration tests, but I can easily reproduce it on MySQL Workbench, so it should not have anything to do with JDBC or Spring or anything in between.

call sync_daily_summary_view_with_table();
call sync_daily_summary_view_with_table();

On the first call, it does what it should do, and returns normally. In 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 sales_observation_daily_summary_view, . , , , -, .

, , sales_observation_daily_summary_view , :

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,
               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

:

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

5.1.56-log .
1 RDS- Amazon 5.1.57.

2 SQL SECURITY INVOKER root, . , - . (, .

3 InnoDB. , , , , .

4 . insert, update. , , , insert .

+5

mysql stored-procedures mysql-error-1142

jhericks 02 . '11 5:23

4

Andreas Wederbrand · Answer 1 · 2011-09-07T19:32:41+0000

? @@autocommit;

0, , - sync_daily_summary_view_with_table(); ; sync_daily_summary_view_with_table();

?

Hunter · Answer 2 · 2011-09-06T22:19:26+0000

. COMMIT UPDATE INSERT. InnoDB, .

, , :

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

red-X · Answer 3 · 2011-09-09T10:11:24+0000

,

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

mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)

:

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

, : http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

vinibarr · Answer 4 · 2014-12-16T20:36:00+0000

, , , . !

, , . , , .

"" , ( "" ), , !

The problem is that I don’t know exactly how MySQL uses another user to make a choice through the view, it does not use your identifier or session user, and for the second time this “internal” user get comamnd is denied viewing.

I hope to help you guys solve this problem in general!

MySQL stored procedure does not work in the second call with the same connection with the "SELECT" command, which is denied to the user "@" localhost for the table "exit_name"

More articles: