Сбой хранимой процедуры MySQL при втором вызове в том же соединении с командой "SELECT, запрещенной для пользователя 'user'@'localhost" для таблицы "view_name"

Question

Сбой хранимой процедуры MySQL при втором вызове в том же соединении с командой "SELECT, запрещенной для пользователя 'user'@'localhost" для таблицы "view_name"

У меня есть таблица с именем sales_observation_daily_summary, которая представляет собой материализованное представление sales_observation_daily_summary_view. Я определил хранимую процедуру с именем sync_daily_summary_view_with_table, которая обновит материализованное представление. Функционально он работает именно так, как я ожидаю. Тем не менее, у меня есть странная ошибка при вызове хранимой процедуры дважды на одном и том же соединении (вероятный сценарий при использовании пула соединений). Первоначально это встречалось в моих тестах интеграции Java, но я могу легко воспроизвести его на MySQL Workbench, поэтому он не должен иметь ничего общего с JDBC или Spring или чем-то посередине, как это.

call sync_daily_summary_view_with_table();
call sync_daily_summary_view_with_table();

При первом вызове он делает то, что должен, и возвращает нормально. На второй звонок я получаю:

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 Я также воспроизвел ошибку на сервере Amazon RDS версии 5.1.57-log.

ОБНОВЛЕНИЕ 2 Если я определю хранимую процедуру как SQL SECURITY INVOKER и выполнить его как root, он работает нормально. Это не приемлемый обходной путь, но это может быть какая-то подсказка. (Например, это не проблема блокировки таблицы.

ОБНОВЛЕНИЕ 3 Используемые таблицы являются таблицами InnoDB. Я не уверен, что это подсказка, но когда я добавил Start Transaction в начале и Commit в конце, это заняло гораздо больше времени, но затем я получил ту же ошибку при втором вызове.

ОБНОВЛЕНИЕ 4 Я упростил хранимую процедуру и все еще воспроизвел проблему. Раньше имелся оператор вставки, за которым следовал оператор обновления. Оказывается, оператора update достаточно для воспроизведения ошибки, поэтому я удалил оператор вставки из хранимой процедуры выше.

5

mysql stored-procedures mysql-error-1142

Источник

user316745 02 сен '11 в 05:23

4 ответа

Другие вопросы по тегам mysql stored-procedures mysql-error-1142

user296452 07 сен '11 в 19:32 2011-09-07 19:32 · Answer 1 · 2011-09-07 19:32

Какова ценность автокоммитов? выберите @@autocommit;

Если значение равно 0, попробуйте добавить фиксацию между двумя вызовами, так как у вас может быть открытая транзакция, так или иначе, вызовите sync_daily_summary_view_with_table(); совершить; вызовите sync_daily_summary_view_with_table();

Является ли материализованная таблица частью какого-либо из представлений?

user931096 06 сен '11 в 22:19 2011-09-06 22:19 · Answer 2 · 2011-09-06 22:19

Это может быть проблемой транзакции. Попробуйте добавить 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

user3337405 16 дек '14 в 20:36 2014-12-16 20:36 · Answer 3 · 2014-12-16 20:36

Я не знаю, нашли ли вы, ребята, решение этой проблемы. Я столкнулся с тем же и смог это исправить!

Проблема, с которой я столкнулся, возникла, когда я попытался сделать выбор в представлении через процедуру. В первый раз он просто работал нормально, но со второго раза он отвечал на сообщение об отказе команды для моего пользователя.

Решение состояло в том, чтобы создать представление с пользователем "администратор", сделать то же самое с процедурой (создать его с пользователем "администратор") с теми процессами, которые я запустил!

Проблема в том, что я не знаю, как именно, MySQL использует другого пользователя для выбора через представление, он не использует вашего определенного или зарегистрированного пользователя сеанса, и во второй раз этот "внутренний" пользователь получает командир, запрещенный с точки зрения.

Я надеюсь помочь вам, ребята, решить эту проблему на всех!

user305971 09 сен '11 в 10:11 2011-09-09 10:11 · Answer 4 · 2011-09-09 10:11

Похоже, это может быть проблемой с разрешением нескольких операторов в одном запросе

это ( 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