nicht angemeldet
MySQL: Buchungen autom. löschen, wenn Person oder Termin gelöscht wird
Linuchs
MySQL: Buchungen autom. löschen, wenn Person oder Termin gelöscht wird
Linuchs
Rolf B
TS
TabellenkalkMoin,
In der Terminplanung habe ich Personen, Termine und die Schnittmenge in drei DB-Tabellen.
Hier ist visualisiert, dass Hermann drei Termine gebucht hat, Carlo hat einen storniet und einen gebucht:
Wenn Hermann gelöscht wird, soll MySQL autom. seine drei Buchungssätze löschen.
Wenn der Termin 06.04.23 gelöscht wird, sollen auch die zugehörigen Buchungssätze bei Hermann und Carlo gelöscht werden.
Wie funktioniert das?
fragt Linuchs
-
Wie funktioniert das?
Trigger.
Du stellst praktisch ein kleines Männchen hin, dass argwönisch äugt, ob in einer Tabelle was gelöscht wird. Und wenn das geschieht, führt es die vorbestimmten Aufgaben aus.
Stellt sich eine Frage:
Kater Carlo wird vielleicht einfach fressen was man ihm hinstellt und wann man das tut. Aber anderen hätten vielleicht gerne einen Hinweis per Email, damit die sich nicht auf die Socken machen...
Das macht die Sache mit dem Triggern in der Datenbank schwieriger.
-
Hallo Raketenwilli,
das kann man zwar mit einem Trigger auch lösen, aber ein Fremdschlüssel ist deutlich leichter zu handhaben.
Rolf
--
sumpsi - posui - obstruxi-
Ja. Richtig.
Macht die Sache mit der Information der betroffenen Benutzer aber auch nicht wirklich einfacher.
-
Hallo Raketenwilli,
das stimmt, das muss man vorher erledigen. Betroffene Termine ermitteln, Mindestteilnehmerzahl überwachen etc.
Aber ich nehme an, das hat Linuchs auf dem Schirm.
Rolf
--
sumpsi - posui - obstruxi
-
-
-
-
Moin,
Wie funktioniert das?
Mit Fremdschlüsseln – wenn du die mit
[…] ON DELETE CASCADE […]definierst, werden die Datensätze, die auf den gelöschten verweisen, mit gelöscht.Gruß
Tobias -
Hallo Linuchs,
Personen, Termine und die Schnittmenge in drei DB-Tabellen.
D.h. eine M:N Beziehung (eine Person kann M Termine haben und an einem Termin können N Personen teilnehmen)? Und die "Schnittmenge" ist das, was man in relationalen Datenbanken braucht, um eine solche Relation abzubilden.
Was Du suchst, heißt "Foreign Key"
Mal angenommen, deine DB sieht so aus:
Tabelle person, primary key ist die Spalte pers_ID pers_id INT Primary Key, ... weitere spalten PRIMARY KEY (pers_id) Tabelle termin term_id INT, ... weitere Spalten PRIMARY KEY (term_id) Tabelle pers_term pers_id INT, term_id INT, ... weitere Spalten PRIMARY KEY (pers_id, term_id)Dann definierst Du in pers_term zwei sogenannte FOREIGN KEYS.
FK 1: von pers_id nach person.pers_id, ON UPDATE RESTRICT ON DELETE CASCADE FK 2: von term_id nach termin.term_id, ON UPDATE RESTRICT ON DELETE CASCADE
Wenn Du die Foreign Keys anlegst, solltst Du Auswahlmöglichkeiten für das ON UPDATE und ON DELETE verhalten haben.
ON UPDATE RESTRICT bedeutet: Du kannst keine ID in der Person- oder Termin-Tabelle ändern, wenn die in einer pers_term Row verwendet wird
ON DELETE CASCADE tut, wonach Du fragst: Wird eine Row in Person gelöscht, kaskadiert die Löschung über den Foreign Key, und die abhängigen Sätze in pers_term werden ebenfalls gelöscht. Das gleiche gilt, wenn Du einen Termin löschst.
Dabei könnn natürlich Personen ohne Termine oder Termine ohne Personen zurückbleiben. Dass ein Termin gelöscht wird, wenn es keine pers_term Row mehr für ihn gibt, ist mit SQL Bordmitteln nicht möglich. Ggf. sollte jeder Termin einen Besitzer haben (direkte Beziehung zu einer Person). Auch dort kannst Du einen Foreign Key definieren, so dass das Löschen einer Person alle Termine löscht, die dieser Person gehören und damit auch alle Termin_Person Beziehungen, die an dem Termin hängen.
Rolf
--
sumpsi - posui - obstruxi-
Hello,
Dann definierst Du in pers_term zwei sogenannte FOREIGN KEYS.
FK 1: von pers_id nach person.pers_id, ON UPDATE RESTRICT ON DELETE CASCADE FK 2: von term_id nach termin.term_id, ON UPDATE RESTRICT ON DELETE CASCADE
Wenn Du die Foreign Keys anlegst, solltst Du Auswahlmöglichkeiten für das ON UPDATE und ON DELETE verhalten haben.
ON UPDATE RESTRICT bedeutet: Du kannst keine ID in der Person- oder Termin-Tabelle ändern, wenn die in einer pers_term Row verwendet wird
ON DELETE CASCADE tut, wonach Du fragst: Wird eine Row in Person gelöscht, kaskadiert die Löschung über den Foreign Key, und die abhängigen Sätze in pers_term werden ebenfalls gelöscht. Das gleiche gilt, wenn Du einen Termin löschst.
Das hängt doch aber von der verwendeten Datenbankmaschine ab, ob das funktionirt, oder ist das nicht mehr so?
MyISAM vs. InnoDB, oder was gibt's sonst noch?
Glück Auf
Tom vom Berg-
Hallo TS,
ups, das habe ich nicht überprüft. Unterstützt MyISAM keine Foreign Keys?
überprüfen geh
Zumindest in MariaDB 10.5 geht's mit MyISAM.
Rolf
--
sumpsi - posui - obstruxi-
Hello Rolf,
ups, das habe ich nicht überprüft. Unterstützt MyISAM keine Foreign Keys?
überprüfen geh
Zumindest in MariaDB 10.5 geht's mit MyISAM.
Das wäre gut, wenn das mit MyISAM (inzwischen) auch funktioniert. Meine Museumsversionen konnten das noch nicht. Da war es nur mit InnoDB möglich, automatisch zu kaskadieren.
Und InnoDB mochte ich immer nicht, weil das alle Tabellen in einem monolithischen Klotz abspeichert(e). Da kam nan also an einzelne Tabellen nicht ran, um sie z. B. separat zu sichern.
Glück Auf
Tom vom Berg-
Hallo TS,
Da war es nur mit InnoDB möglich, automatisch zu kaskadieren.
Studium Fuga! [1]
Ich habe nur probiert, ob man den Fremdschlüssel anlegen kann. Ja, du hast recht, die Kaskadierung wurde zwar als Option angenommen, wird aber von MyISAM nicht ausgeführt.
Aber gibt es heute noch Gründe, MyISAM zu verwenden?
Rolf
--
sumpsi - posui - obstruxi
Eifer Flucht ↩︎
-
Hello,
Da war es nur mit InnoDB möglich, automatisch zu kaskadieren.
Studium Fuga! [1]
Ich habe nur probiert, ob man den Fremdschlüssel anlegen kann. Ja, du hast recht, die Kaskadierung wurde zwar als Option angenommen, wird aber von MyISAM nicht ausgeführt.
Aber gibt es heute noch Gründe, MyISAM zu verwenden?
Ja selbstverständlich. Habe ich doch genannt.
Monolithische Datenbanken machen mir immer etwas Angst, wenn die wachsen.
Die kann man auf Dateiebene nur komplett sichern, wiederherstellen usw.
Glück Auf
Tom vom Berg
Eifer Flucht ↩︎
-
Hallo TS,
Ja selbstverständlich. Habe ich doch genannt.
Monolithische Datenbanken machen mir immer etwas Angst, wenn die wachsen.Wie sind deine Erfahrungen hiermit:
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-space.html
- innodb_file_per_table
- Eigene Tablespaces
Rolf
--
sumpsi - posui - obstruxi-
Hello Rolf,
Ja selbstverständlich. Habe ich doch genannt.
Monolithische Datenbanken machen mir immer etwas Angst, wenn die wachsen.Wie sind deine Erfahrungen hiermit:
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-space.html
- innodb_file_per_table
- Eigene Tablespaces
Damit muss ich mich auch erst wieder beschäftigen.
Dank meiner frühen Allergie gegen InnoDB habe ich für die Praxis immer einen Bogen darum gemacht, nur gelegentlich für die Theorie geschaut, wie es sich weiter entwickelt.Das Sichern von MyISAM-Tabellen war aber immer der schnellste und sauberste Weg. In einer Schwachlastzeit das Login verbieten, die Tabellen flushen, den DB-Server stoppen und einen Tarball von den Tabellen anlegen. Das geht hostintern relativ schnell. Dann kann man den Server schon wieder starten und das Login erlauben.
Das muss die API abfangen.
Dann kann man den Tarball in Ruhe runterladen/übertragen auf einen Backup-Host.
Das Wiederherstellen geht dann ähnlich schnell.
Dasselbe mit einem offiziellen Dump dauert ungefähr 10 bis 20mal solange.
Glück Auf
Tom vom Berg
-
Moin,
Monolithische Datenbanken machen mir immer etwas Angst, wenn die wachsen.
Die kann man auf Dateiebene nur komplett sichern, wiederherstellen usw.
Nein, kann man nicht – zumindest nicht wirklich sinnvoll. Das Kopieren von den Dateien ist – egal welche Engine verwendet wird – keine gute Idee: auch wenn im Handbuch steht dass MyISAM-Tabellen durch das Kopieren der Dateien gesichert werden könnten, ist die Methode anfällig und ein Restore geht schnell mal schief. Von der Methode ist dringend abzuraten, zum Sichern sollte immer mysqldump verwendet werden, das funktioniert immer, egal welche Engine verwendet wird.
Gruß
Tobias-
zum Sichern sollte immer mysqldump verwendet werden, das funktioniert immer, egal welche Engine verwendet wird.
Dann sichere mal mehrere gigabyte-große Datenbanken auf diese Weise und stelle sie wieder her.
Mein (mein) Königsweg war früher ein Slave-Server als Datensicherung fürs Desaster (von dem dann in Hinsicht auf die Sicherung auch früherer Zustände aus Dumps gezogen werden) - das hat man einfach am schnellsten (nämlich durch Anpassung von IPs, Firewallregeln an den Servern) wieder online, jetzt wird zwecks Desaster-„Recovery“ geclustert.
https://wiki.ubuntuusers.de/Archiv/MariaDB/Galera_Cluster/
https://www.ionos.de/digitalguide/hosting/hosting-technik/mariadb-galera-cluster-vorgestellt/
Was jetzt myisam und das Kopieren von Daten betrifft. Hm. Das ist dann eher was für das Übertragen der Datenbanken z.B. vom alten auf einen neuen Server. Da ist die Engine eigentlich egal, es wird halt alles aus /var/lib/mysql kopiert (nicht vergessen, den neuen Server vorher zu stoppen und danach neu zu starten - muss mysql/mariadb hierbei Anpassungen an den Dateien an neue Versionen der Engines vornehmen macht es das hierbei selbst, zu große Versionssprünge würde ich aber meiden.)
Will man dabei Datenbanken aus $Gründen neu auf verschiedene Server verteilen, dann einfach alles kopieren und nachfolgend die überzähligen Schemas (Datenbanken) löschen. Fertig.
-
Hallo Raketenwilli,
Dann sichere mal mehrere gigabyte-große Datenbanken auf diese Weise und stelle sie wieder her
Wer solche DBs betreibt, braucht vermutlich eh eine Enterprise-Edition von mysql und dann gibt's auch mysqlbackup als Tool.
Sag ich jetzt mal so, frei von Ahnung und nach kurzem handbuchschmökern.
Rolf
--
sumpsi - posui - obstruxi
-
-
Hello,
Monolithische Datenbanken machen mir immer etwas Angst, wenn die wachsen.
Die kann man auf Dateiebene nur komplett sichern, wiederherstellen usw.
Nein, kann man nicht – zumindest nicht wirklich sinnvoll. Das Kopieren von den Dateien ist – egal welche Engine verwendet wird – keine gute Idee: auch wenn im Handbuch steht dass MyISAM-Tabellen durch das Kopieren der Dateien gesichert werden könnten, ist die Methode anfällig und ein Restore geht schnell mal schief. Von der Methode ist dringend abzuraten, zum Sichern sollte immer mysqldump verwendet werden, das funktioniert immer, egal welche Engine verwendet wird.
Wie oft hast Du das schon für eine Datenbank mit mehr als 1GB gemacht? Und wie lange hat es gedauert?
Glück Auf
Tom vom Berg-
Genau. Zum Test:
https://downloads.mysql.com/docs/airport-db.tar.gz
Das sind sind gezippt ~625 MB.
Alternative:
Und das ist eine Datenbank für Test und Lehrzwecke.
Hinweis: Nicht wundern, wenn ich ab Sonntag bis in den Juli hinein relativ schweigsam bin. Mir geht es gut, aber erst muss ich den Urlaub vorarbeiten, dann 5 Wochen Urlaub machen und sodann den Urlaub nacharbeiten. Ich bin ausgebucht.
-
Hello,
Genau. Zum Test:
https://downloads.mysql.com/docs/airport-db.tar.gz
Das sind sind gezippt ~625 MB.
Alternative:
Und das ist eine Datenbank für Test und Lehrzwecke.
Hinweis: Nicht wundern, wenn ich ab Sonntag bis in den Juli hinein relativ schweigsam bin. Mir geht es gut, aber erst muss ich den Urlaub vorarbeiten, dann 5 Wochen Urlaub machen und sodann den Urlaub nacharbeiten. Ich bin ausgebucht.
Habe ich das jetzt richtig verstanden?
Die Erzeugung der gezippten Datei mit Dump ist kürzer, ale die von mir benutzte Methode?
Glück Auf
Tom vom Berg-
Habe ich das jetzt richtig verstanden?
Die Erzeugung der gezippten Datei mit Dump ist kürzer, ale die von mir benutzte Methode?
Nein, sowas habe ich weder behauptet noch vormachen wollen. Ich wollte nur mal eine „ausreichend große“ Datenbank in die Runde werfen, damit jede(r) ein Bild machen kann.
-
Nein, sowas habe ich weder behauptet noch vormachen wollen. Ich wollte nur mal eine „ausreichend große“ Datenbank in die Runde werfen, damit jede(r) ein Bild machen kann.
Etwa so:
- Einlesen eines Dumps:
ll -h flughafendb_large_de.sql -rw-rw-r--+ 1 fastix fastix 2,2G Feb 28 00:45 flughafendb_large_de.sql time mysql test < flughafendb_large_de.sql real 374m19,278s user 1m9,086s sys 0m1,596sDas mag ein langsamer Rechner mit einem „Intel(R) Celeron(R) J4125 CPU @ 2.00GHz“ (mein NAS) sein, man kann es aber drehen und wenden wie man will: 6 Stunden für das Recovery wären inakzeptabel. Allerdings vermute ich, dass da noch Optimierungspotential drin steckt: Ich habe nämlich einen Dump, der mit mysql 5.7.6 gemacht wurde, mit mariadb eingelesen.
- Herstellen eines Dumps
mysqldump --opt --lock-tables test | gzip -c > /tmp/test.sql.gz real 4m4,907s user 4m1,635s sys 0m1,943sDas geht also freilich deutlich schneller. Nur sorgt das --lock-tables ebenfalls für eine faktische Downtime wenn man nicht eine Datenbank betreibt, in welche selten (und nur auf besondere Veranlassung des Betreibers) geschrieben wird.
Wie schon geschrieben: Mein Favorit fürs Desaster-Recovery ist „kein Desaster-Recovery“ sondern ein Cluster. Der Dump oder jede andere Art von Backup (z.B. via binlog) ist toll, wenn es darum geht bestimmte Zustände periodisch zu sichern um bei Unfällen oder absichtlicher Zerstörung von Daten in der Datenbank einen früheren Zustand wieder herzustellen.
-
Hello,
Habe ich das jetzt richtig verstanden?
Die Erzeugung der gezippten Datei mit Dump ist kürzer, ale die von mir benutzte Methode?
Ja, danke für das Praxisbeispiel.
Das bestätigt mich aber in meiner Methode und ich muss da noch nichts ändern.
Besser wäre selbstvertändlich eine Life-Spiegelung der SQL-Aktionen, die automatisch erkennen kann, wann korrumpierte Operationen ausgeführt werden und die Spiegelung dann sofort stoppt - mit Alarmmeldung.
Aber sowas habe ich bisher noch nicht gefunden.
Alternativ könnte man das BIN-LOG von MySQL einschalten, wenn man genügend Plattenplatz hat.Aber auch das muss man von Zeit zu Zeit schneiden (also stoppen und sichern) und die Aufsetzpunkte genau feststellen, um im Schadensfalle eine Rückabwicklung (besser: Neuaufbau ab dem Startpunkt des Bin-Logs) vornehmen zu können. Das dürfte dann auch Stunden bis Tage dauern, wäre aber die genaueste Lösung.
Glück Auf
Tom vom Berg
-
-
-
-
-
-
-
-
-
-
Dieser Beitrag wurde gelöscht: genug andre Antworten
-
Hello K.H.,
mir fällt da noch eine Spielart ein:
Wenn Du die Kaskade-oder-Trigger-Frage geklärt hast (tiefer im Thread), dann solltest Du vielleicht noch überlegen, ob Du das Löschen nicht besser ins Datenmodell einarbeiten solltest:
Daten nicht sofort löschen, sondern nur per Update zum Löschen markieren. Diese Datensätze dürfen dann aber beim normalen Select nicht mehr angezeigt werden.
Vorteile:
-
Du behältst eine "Papierkorbfunktion" übrig, die es Dir als Supervisor gestattet, die gelöschten (Deleted) Records ggf. wiederherzustellen
-
Löschen in Datenbanken ist bezüglich Indexen relativ teuer. Du könntest das tatsächliche Löschen (Purge) dann nach Sichtung zu einer Schwachlastzeit vornehmen lassen, also eventuell den gesamten Vorgang dreistufig aufbauen:
-- Delete
-- View and Commit
-- Purge by CronGlück Auf
Tom vom Berg -