MySQL Stored Procedure schlägt beim zweiten Aufruf auf derselben Verbindung fehl mit "SELECT command denied to user 'user'@'localhost' for table 'view_name'

Question

Ich habe eine Tabelle namens sales_observation_daily_summary, die eine materialisierte Ansicht von sales_observation_daily_summary_view ist. Ich habe eine gespeicherte Prozedur namens sync_daily_summary_view_with_table definiert, die die materialisierte Ansicht aktualisiert. Funktionell funktioniert sie genau so, wie ich es erwarte. Allerdings habe ich einen seltsamen Fehler, wenn ich die gespeicherte Prozedur zweimal über dieselbe Verbindung aufrufe (ein wahrscheinliches Szenario bei Verwendung eines Verbindungspools). Ursprünglich trat dieser Fehler in meinen Java-Integrationstests auf, aber ich kann ihn mit MySQL Workbench leicht reproduzieren, also sollte es nichts mit JDBC oder Spring oder irgendetwas in der Art zu tun haben.

call sync_daily_summary_view_with_table();
call sync_daily_summary_view_with_table();

Beim ersten Aufruf tut es, was es tun soll, und kehrt normal zurück. Beim zweiten Aufruf erhalte ich:

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 wird in sales_observation_daily_summary_view referenziert, die in der gespeicherten Prozedur referenziert wird. Die Fehlermeldung ergibt keinen Sinn, da erstens die gespeicherte Prozedur bei der ersten Ausführung kein Objekt enthielt und zweitens dieser Benutzer über zahlreiche Rechte zur Auswahl dieser Ansicht verfügt.

Ich werde nicht alle beteiligten Ansichten zeigen, da es sehr komplex ist, aber die Ansicht sales_observation_daily_summary_view ist als Vereinigung mehrerer anderer Ansichten definiert:

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

Die gespeicherte Prozedur ist folgendermaßen definiert:

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

Ich verwende Version 5.1.56-log auf meinem lokalen Entwicklungssystem.
UPDATE 1 Ich habe den Fehler auch auf einem Amazon RDS Server Version 5.1.57-log reproduziert.

UPDATE 2 Wenn ich die gespeicherte Prozedur so definiere, dass sie SQL SECURITY INVOKER und führen Sie es als Root aus, es funktioniert einwandfrei. Dies ist kein akzeptabler Workaround, aber es könnte eine Art Hinweis sein. (Es ist z.B. kein Problem mit der Tabellensperre.

UPDATE 3 Die betroffenen Tabellen sind InnoDB-Tabellen. Ich bin mir nicht sicher, ob das ein Hinweis ist, aber als ich am Anfang eine Start-Transaktion und am Ende eine Commit-Transaktion hinzufügte, dauerte es viel länger, bis der Vorgang abgeschlossen war, aber dann trat beim zweiten Aufruf derselbe Fehler auf.

UPDATE 4 Ich habe die gespeicherte Prozedur vereinfacht und das Problem immer noch reproduziert. Früher gab es eine Einfügeanweisung, gefolgt von einer Aktualisierungsanweisung. Es hat sich herausgestellt, dass die Aktualisierungsanweisung ausreicht, um den Fehler zu reproduzieren, also habe ich die Einfügeanweisung aus der obigen gespeicherten Prozedur entfernt.

Answer 1

Was ist der Wert von autocommit? Wählen Sie @@autocommit;

Wenn der Wert 0 ist, versuchen Sie, zwischen den beiden Aufrufen ein Commit hinzuzufügen, da Sie möglicherweise eine offene Transaktion haben sync_daily_summary_view_with_table() aufrufen; Übertragen; Aufruf von sync_daily_summary_view_with_table();

Ist die materialisierte Tabelle Teil einer der Ansichten?

Answer 2

Dies könnte ein Transaktionsproblem sein. Versuchen Sie, nach den UPDATE- und INSERT-Anweisungen ein COMMIT hinzuzufügen. Versuchen Sie auch, InnoDB zu verwenden, wenn Sie dies noch nicht tun.

Sie sollten die Funktion so ausprobieren, um zu sehen, ob Sie das gleiche Ergebnis erhalten:

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

Answer 3

Es sieht so aus, als ob es ein Problem mit dem Zulassen von mehreren Anweisungen in einer Abfrage sein könnte

this( http://dev.mysql.com/doc/refman/5.0/en/mysql-set-server-option.html ) könnte eine Option sein:

mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)

oder besser während der Verbindung:

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

Für eine bessere Erklärung sollten Sie auch hier nachsehen: http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

Answer 4

Ich weiß nicht, ob ihr eine Lösung für dieses Problem gefunden habt. Ich bin auf das gleiche Problem gestoßen und konnte es beheben!

Das Problem, das ich bekam nur passieren, wenn ich versuchte, eine Auswahl in einer Ansicht durch eine Prozedur zu machen. Beim ersten Mal hat es noch gut funktioniert, aber beim zweiten Mal wurde meinem Benutzer die Meldung "Befehl verweigert" angezeigt.

Die Lösung war, die Ansicht mit einem "Administrator"-Benutzer zu erstellen und das Gleiche mit der Prozedur zu machen (mit einem "Administrator"-Benutzer erstellen), mit diesen Prozessen bekam ich es zum Laufen!

Das Problem ist, dass ich nicht weiß, wie genau, MySQL verwendet einen anderen Benutzer, um die Auswahl durch die Ansicht zu machen, es verwendet nicht Ihre Definer oder angemeldete Sitzung Benutzer, und beim zweiten Mal, diese "interne" Benutzer erhalten comamnd von Ansicht verweigert.

Ich hoffe, ich kann euch helfen, dieses Problem zu lösen!