Warum der Befehl „explain“ MySQL-Daten ändern kann

Wenn Sie jemand fragen würde, ob die Ausführung von EXPLAIN bei einer Abfrage Ihre Datenbank ändern würde, würden Sie wahrscheinlich „Nein“ sagen. Dies ist normalerweise die übliche Ansicht. EXPLAIN soll uns zeigen, wie eine Abfrage ausgeführt wird, und sie nicht ausführen, sodass es keine Daten ändern kann.

Leider gilt der gesunde Menschenverstand in diesem Fall bei MySQL nicht (zum Zeitpunkt des Schreibens dieses Beitrags MySQL 8.0.21 und früher) - es gibt Fälle, in denen Explain Ihre Datenbank ändern kann, wie dieser Fehler zeigt:

mysql> Version auswählen();
+-------------+
| version() |
+-------------+
| 5.7.31 |
+-------------+
1 Zeile im Satz (0,01 Sek.)

 mysql> TRENNUNGSZEICHEN $$
mysql> ERSTELLEN SIE DIE FUNKTION `cleanup`() GIBT char(50) CHARSET utf8mb4 ZURÜCK
    -> DETERMINISTISCH
    -> BEGIN
    -> aus test.t1 löschen;
    -> RÜCKGABE 'OK';
    -> ENDE $$
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

 MySQL>

mysql> wähle * aus t1$$
+------+------+
| Ich würde | Name |
+------+------+
| 1 | aa |
| 2 | bb |
+------+------+
2 Zeilen im Satz (0,00 Sek.)

 mysql> erklären Sie select * from (select cleanup()) als t1clean$$
+----+-------------+------------+------------+--------+------------+---------+---------+---------+------+---------+------+------+----------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+-------------+------------+------------+--------+------------+---------+---------+---------+------+---------+------+------+----------------+
| 1 | PRIMARY | <derived2> | NULL | System | NULL | NULL | NULL | NULL | 1 | 100,00 | NULL |
| 2 | ABGELEITET | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Keine Tabellen verwendet |
+----+-------------+------------+------------+--------+------------+---------+---------+---------+------+---------+------+------+----------------+
2 Zeilen im Satz, 1 Warnung (0,01 Sek.)

 mysql> wähle * aus t1$$
Leerer Satz (0,00 Sek.)

 MySQL>

Das Problem hierbei besteht darin, dass Explain die gespeicherte Funktion cleanup() ausführt, die Daten ändern kann.

Dies unterscheidet sich vom vernünftigeren Verhalten von PostgreSQL, das beim Ausführen von EXPLAIN keine gespeicherten Funktionen ausführt (es tut dies, wenn Sie EXPLAIN ANALYZE ausführen).

In MySQL beruht diese Entscheidung auf dem Versuch, das Richtige zu tun und die zuverlässigste Erklärung bereitzustellen (der Abfrageausführungsplan kann durchaus davon abhängen, was die gespeicherte Funktion zurückgibt), aber dieser Sicherheitskompromiss scheint nicht berücksichtigt zu werden.

Diese Konsequenz des aktuellen MySQL EXPLAIN-Designs ist eine der schwerwiegendsten. Darüber hinaus besteht jedoch auch das Problem, dass die Ausführung von EXPLAIN (von dem ein rationaler Benutzer erwarten würde, dass es eine schnelle Möglichkeit zur Überprüfung der Abfrageleistung ist) sehr viel Zeit in Anspruch nehmen kann. Beispiel:

mysql> erklären Sie select * from (select sleep(5000) als a) b;

Dies wird über eine Stunde dauern.

Dieses Verhalten ist zwar bedauerlich, tritt jedoch nur auf, wenn Sie über uneingeschränkte Berechtigungen verfügen. Wenn Sie über eine komplexere Konfiguration verfügen, kann das Verhalten anders sein.

Wenn dem Benutzer das EXECUTE-Privileg fehlt, schlägt die EXPLAIN-Anweisung fehl.

mysql> erklären Sie „select * from (select cleanup()) als t1clean“;
FEHLER 1370 (42000): Ausführung des Befehls für Benutzer „abce“@„localhost“ für Routine „test.cleanup“ verweigert

Dies schlägt auch fehl, wenn der Benutzer über EXECUTE-Berechtigungen verfügt, der Benutzer, der die gespeicherte Funktion ausführt, jedoch keine DELETE-Berechtigungen hat:

mysql> erklären Sie „select * from (select cleanup()) als t1clean“;
FEHLER 1142 (42000): DELETE-Befehl für Benutzer „abce“@„localhost“ für Tabelle „t1“ verweigert

Was also, wenn man EXPLAIN sicherer machen möchte, beispielsweise wenn man ein Tool wie Percona Monitoring and Management entwickelt, das es Benutzern unter anderem ermöglicht, EXPLAIN für ihre Abfragen auszuführen?

Es wird empfohlen, dass Benutzer Berechtigungen für eine ordnungsgemäße Überwachung festlegen. Dies sollte die erste Verteidigungslinie für dieses (und viele andere) Probleme sein, allerdings ist es schwierig, sich darauf zu verlassen. Viele Benutzer wählen den einfachen Weg und verwenden den „Root“-Benutzer mit vollen Berechtigungen für die Überwachung.

Umschließen Sie die EXPLAIN-Anweisung mit BEGIN ... ROLLBACK. Dadurch werden alle Schäden rückgängig gemacht, die EXPLAIN möglicherweise verursacht hat. Der Nachteil besteht natürlich in der „Arbeit“, die das Löschen der Daten mit sich bringt, und wenn Sie die Arbeit rückgängig machen, ist sie erledigt. (Hinweis: Dies gilt natürlich nur für Transaktionstabellen. Wenn Sie immer noch MyISAM verwenden, müssen Sie sich in diesem Fall um ernstere Probleme kümmern.)

Verwenden Sie „Transaktion schreibgeschützt festlegen“, um anzugeben, dass Sie keine Schreibvorgänge wünschen. In diesem Fall schlägt ein EXPLAIN-Versuch, die Daten zu schreiben, fehl und führt zu nichts.

Diese Workarounds machen es zwar für Tools sicherer, EXPLAIN auszuführen, sie helfen jedoch Benutzern nicht, die EXPLAIN direkt ausführen, und ich hoffe wirklich, dass dieses Problem durch eine Neugestaltung von EXPLAIN gelöst wird, sodass es nicht versucht, gespeicherte Funktionen auszuführen, wie dies bei PostgreSQL der Fall ist. Für alle, die wissen möchten, wie genau eine Abfrage ausgeführt wird, gibt es jetzt EXPLAIN ANALYZE.

Oben sind die Details, warum der Befehl „explain“ MySQL-Daten ändern kann. Weitere Informationen zum Befehl „explain“ zum Ändern von MySQL-Daten finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!