Mach Dich unsichtbar: MySQL Generated Invisible Primary Keys
Immer wieder kommt es bei Beratungseinsätzen in Sachen MySQL zu kleineren oder größeren Problemen, über die wir an dieser Stelle gerne berichten möchten. In diesem Fall wollte unser Kunde eine MySQL Single Instance durch ein MySQL InnoDB Cluster mit drei Knoten ablösen. Über den Cluster haben wir an dieser Stelle mehrfach berichtet [1-5] und möchten hier nicht mehr im Detail darauf eingehen.
In diesem konkreten Fall gab es ein vermeintliches einfaches Problem. Eine wesentliche Voraussetzung für die Inbetriebnahme des Clusters war schlicht und einfach nicht erfüllt: Es gab Tabellen ohne Primary Key!
Gute Voraussetzungen haben …
Der MySQL InnoDB Cluster basiert auf der sogenannten „Group Replication“. Zur Verwendung dieses „Features“ müssen alle Tabellen die Storage Engine InnoDB verwenden und über einen Primary Key verfügen. Weitere Anforderungen zur Verwendung der „Group Replication“ bzw. des InnoDB Clusters finden Sie hier.
Der Kunde hatte neben dem eigentlichen, bereits im Betrieb befindlichen, Datenbank-Server drei weitere Knoten bereitgestellt, die zu einem Cluster zusammengefügt wurden.
In unserer Testumgebung haben wir das Problem stark vereinfacht mit vier Docker-Containern nachgestellt. Auf dem initialen Knoten („Altsystem“ mit dem Namen: Berlin) liegt eine Datenbank mit dem Namen „application“, die über eine Tabelle „names“ verfügt. Die anderen drei Container (Hamburg, Cologne, Frankfurt) repräsentieren das Cluster.
Hier ein kleiner Überblick über das Datenmodell:
MySQL berlin:33060+ ssl JS > show tables; +-----------------------+ | Tables_in_application | +-----------------------+ | names | +-----------------------+ 1 row in set (0.0019 sec) MySQL berlin:33060+ ssl JS > desc names; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | firstname | varchar(20) | YES | | NULL | | | lastname | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.0087 sec)
Wie unschwer zu erkennen ist, verfügt die Tabelle „names“ nur über zwei Spalten vom Typ „varchar“, aber besitzt keinen Primary Key.
Unter schlechten Bedingungen leiden …
Bereits vor der eigentlichen Initialisierung des Clusters konnten die Fehler „aufgespürt“ werden. Zu diesem Zeitpunkt befand sich bereit in einem der Knoten der zu bauenden Clusters (Hamburg) eine importierte Version der Datenbank „application“. Mittels der folgenden Prüfroutine sollte im Vorfeld (vor dem Aufbau des Clusters) verifiziert werden, ob das System/die Systeme überhaupt für den Aufbau eines Clusters geeignet wäre. Bereits im ersten Schritt (Aufruf der Prüfroutine auf dem ersten Knoten) stellte sich raus, wir haben ein Problem:
MySQL hamburg:33060+ ssl JS > dba.checkInstanceConfiguration()
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as hamburg:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
ERROR: The following tables do not have a Primary Key or equivalent column:
application.names
Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.
If you can't change the tables structure to include an extra visible key to be used as PRIMARY KEY, you can make use of the INVISIBLE COLUMN feature available since 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html
Checking instance configuration...
{ "status": "error" }
Es fehlt ein Primary Key auf der Tabelle „names“! Bei unserem Kunden trat dieser Fehler leider nicht nur 1x, sondern mehrfach auf.
Man muss sich zu helfen wissen …
Natürlich ist es nicht schwer, einen (!) Primary Key nachzupflegen. Allerdings wollten wir hier nicht von „Hand“ tätig werden. Zusätzlich muss man bedenken, dass das Hinzufügen einer neuen Spalte bei diversen Tabellen ggf. Probleme innerhalb der Applikation verursachen kann. So könnte es sein, dass ein Entwickler an der einen oder anderen Stellen eine „select * from tabX“ nutzt und eine bestimmte Anzahl an Ergebnisspalten erwartet. Taucht hier eine zusätzliche angelegte Spalte, die als Primary Key genutzt wird, plötzlich aus, so könnte dies zu einem Fehler führen. Wir haben uns entschlossen, die neue Funktion „Generated Invisible Primary Keys“ zu nutzen, die ab der Version 8.0.30 zur Verfügung steht. Doch bevor wir mit dem Kundenbeispiel fortfahren, ein kurzer Exkurs, was dieser Parameter eigentlich auf einem Datenbanksystem verändert.
Letztendlich sorgt die Einstellung dafür, dass Tabellen, die ohne expliziten Primary Key erstellt werden, zwangsweise einen ebensolchen bekommen. Dieser Primary Key heißt stets: „my_row_id“.
Hier ein kleines Fallbeispiel:
mysql> SELECT @@sql_generate_invisible_primary_key; +--------------------------------------+ | @@sql_generate_invisible_primary_key | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (0.01 sec)
Wie zu erkennen ist, wird das neue „Feature“ per default nicht verwendet. Erstellen wir zum Test eine kleine Tabelle „tab1“ ohne diese Konfiguration zu aktivieren.
mysql> create table tab1 ( a varchar(10)); Query OK, 0 rows affected (0.03 sec) mysql> show create table tab1; +-------+----------------------------------------------------------------+ | Table | Create Table +-------+----------------------------------------------------------------+ | tab1 | CREATE TABLE `tab1` ( `a` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
Tabellen, die wie oben ohne Primary Key erzeugt werden, bekommen dann folglich auch keinen!
Nach der Aktivierung dieser Funktion ändert sich dieses Verhalten:
mysql> set sql_generate_invisible_primary_key=1; Query OK, 0 rows affected (0.00 sec) mysql> create table tab2 ( a varchar(10)); Query OK, 0 rows affected (0.04 sec) mysql> show create table tab2; +-------+----------------------------------------------------------------+ | Table | Create Table +-------+----------------------------------------------------------------+ | tab2 | CREATE TABLE `tab2` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `a` varchar(10) DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
Die Tabelle hat nun automatisch den Primary Key „my_row_id“ bekommen.
Gut versteckt sein …
Doch wie sieht es aus mit der Sichtbarkeit des Primary Keys. Über die Anweisung „show create table“ kann man die zusätzliche Spalte mit dem Primary Key erkennen. Doch wie sieht es mit der oben erwähnten „select * from“-Variante aus, die ein Entwickler ggf. verwendet hat:
mysql> select * from names; +-----------+----------+ | firstname | lastname | +-----------+----------+ | Matthias | Jung | +-----------+----------+ 1 row in set (0.00 sec)
Ok, die Spalte wird nicht mit ausgegeben. Doch ist dies immer so? Ggf. versucht die Applikation das Datenmodell über andere Mechanismen zu validieren:
- „desc table“
- Systemtabellen
Prüfen wir auch dies:
mysql> desc names; +-----------+-----------------+------+-----+---------+--------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------+------+-----+---------+--------------------------+ | my_row_id | bigint unsigned | NO | PRI | NULL | auto_increment INVISIBLE | | firstname | varchar(20) | YES | | NULL | | | lastname | varchar(20) | YES | | NULL | | +-----------+-----------------+------+-----+---------+--------------------------+ 3 rows in set (0.01 sec) mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_TYPE,EXTRA,COLUMN_COMMENT from information_schema.columns where table_schema='application' and table_name = 'names'; +-------------+-----------+-----------------+--------------------------+----------------+ | COLUMN_NAME | DATA_TYPE | COLUMN_TYPE | EXTRA | COLUMN_COMMENT | +-------------+-----------+-----------------+--------------------------+----------------+ | firstname | varchar | varchar(20) | | | | lastname | varchar | varchar(20) | | | | my_row_id | bigint | bigint unsigned | auto_increment INVISIBLE | | +-------------+-----------+-----------------+--------------------------+----------------+ 3 rows in set (0.00 sec)
Auf diesem Weg bekommen wir quasi einen „Existenzbeweis“ für die Spalte und den Primary Key. Natürlich gibt es auch hier einen kleinen „Tweak“, um das Verhalten der Datenbank „anzupassen“:
mysql> set global show_gipk_in_create_table_and_information_schema=off; Query OK, 0 rows affected (0.00 sec) Welchen Einfluss hat dies auf die Sichtbarkeit bei: „desc table“ Systemtabellen „show create table“ mysql> desc names; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | firstname | varchar(20) | YES | | NULL | | | lastname | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> show create table names; +--------------------------------------------------------------+ | Table | Create Table | +--------------------------------------------------------------+ | names | CREATE TABLE `names` ( `firstname` varchar(20) DEFAULT NULL, `lastname` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +--------------------------------------------------------------+ mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_TYPE,EXTRA,COLUMN_COMMENT from information_schema.columns where table_schema='application' and table_name = 'names'; +-------------+-----------+-------------+-------+----------------+ | COLUMN_NAME | DATA_TYPE | COLUMN_TYPE | EXTRA | COLUMN_COMMENT | +-------------+-----------+-------------+-------+----------------+ | firstname | varchar | varchar(20) | | | | lastname | varchar | varchar(20) | | | +-------------+-----------+-------------+-------+----------------+ 2 rows in set (0.01 sec)
Wie wir sehen, sehen wir nichts!
Es ist immer gut einen Plan zu haben …
Der Plan (nicht alle Schritte werden wir hier erklären) für den Kunden zum Aufbau des Clusters sieht wie folgt aus:
1. Aufbau des neuen Clusters mit den Knoten Hamburg, Cologne, Frankfurt.
2. Aktivierung der Funktion „Generated Invisible Primary Keys“ im Cluster.
3. Aufbau einer Replikation vom Altsystem „Berlin“ in das Cluster hinein (zum primären Knoten „Hamburg“).
4. Backup und Restore der Datenbank „application“ in das Cluster.
5. Starten der Replikation.
6. Umschalten vom Altsystem auf das Cluster.
Das Problem der fehlenden Primary Key sollte sich gemäß dem Plan mit dem dritten Schritt von selbst erledigen. Alle Tabellen, die mit dem Restore des Dumps ohne Primary Keys erstellt werden, bekommen nun per default einen eben solchen.
Nach dem Aufbau des Clusters (Schritt 1), den wir uns an dieser Stelle im Detail „schenken“, wird der entsprechende Parameter aktiviert:
MySQL hamburg:33060+ ssl JS > \sql set persist sql_generate_invisible_primary_key= 1; Fetching global names for auto-completion... Press ^C to stop. Query OK, 0 rows affected (0.0017 sec)
Auch die Replikation zwischen dem primären Cluster (Knoten „Hamburg“) und dem Alt-System („Berlin“) wurde eingerichtet (Schritt 3), ohne dies hier im Detail zu beschreiben. Allerdings ist die Replikation noch nicht aktiv.
Mit dem Einspielen des Backups von Knoten „Berlin“ (Alt-System) in das Cluster (Knoten Hamburg) werden die Tabellen neu angelegt. Aufgrund der obenstehenden Konfiguration werden die fehlenden Primary Keys erzeugt.
bash-4.4# mysqldump --host=berlin --user=root --password=root --opt --source-data=2 --all-databases | mysql --host=hamburg -- user=root --password=root
Schauen wir uns (zur Sicherheit) einmal das Ergebnis an. Wie wurde die Tabelle „names“ in der Datenbank „application“ im Cluster angelegt?
bash-4.4# mysqlsh --database=application --sql Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory MySQL Shell 8.0.32 Copyright (c) 2016, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. MySQL hamburg application SQL > show create table names; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table +-------+-------------------------------------------------------------------------------------------+ | names | CREATE TABLE `names` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `firstname` varchar(20) DEFAULT NULL, `lastname` varchar(20) DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+-------------------------------------------------------------------------------------------+ 1 row in set (0.0022 sec)
Der fehlende Primary Key ist vorhanden.
Schön, wenn ein Plan funktioniert …
Der Plan ließ sich also erfolgreich umsetzen. Der MySQL-Dump vom Alt-System konnte erfolgreich in das Cluster eingespielt werden. Alle fehlenden Primary Keys wurden über die generierten Schlüssel kompensiert. Hätte dies nicht funktioniert, so wäre es bereits an dieser Stelle zu einem Problem gekommen, da, wie wir am Anfang bereits gelernt haben, Tabellen ohne Primary Keys nicht in ein Cluster einspielen werden können. Sicherlich hätte man das Problem des Kunden auch anders lösen können. Das neue „Feature“ hat an dieser Stelle viel manuelle Arbeit eliminiert und uns effizient zum Ziel gebracht.
Sie haben Fragen rund um das Thema MySQL? Dann sprechen Sie mit uns!
Links
[1] MySQL Shell - Cluster Your Application In 40 Minutes
[2] MySQL 8.0.19: Wie ein Ei dem anderen
[3] MySQL InnoDB Cluster: "Schlüsselmomente" beim Import
[4] Doppelt gemoppelt: Wie man Cluster “clustered”. MySQL-InnoDB-ClusterSets
[5] Ein Geheimnis behalten: MySQL Router „Bootstrapping“ ohne „root“-Account
Seminarempfehlung
MYSQL ADMINISTRATION DB-MY-01
Zum Seminar
Principal Consultant bei ORDIX
Bei Updates im Blog, informieren wir per E-Mail.
Kommentare