Verständnisfrage Datenbankdesign "MariaDB"

CommanderZed

CommanderZed

OpenBSD User
Teammitglied
Hallo,

Hintergrund:
ich versuche mich ein wenig in die Themen "Programmieren" und "Datenbanken" wieder etwas hereinzufuchsen, da ich da seid meiner Schulzeit, Ausbildung und auch meine aktuellen Beruf nur am Rande mit zu tun habe. Leider fehlt mir oft en praktisches Projekt.

Nun ergab sich letztes Jahr ein kleines Projekt auf der Arbeit, es ging darum herauszufinden welche IP-Addressen in einem DHCP-Losen /21er noch belegt sind, da sich in über einen Jahrzehnt der händischen Pflege einer OpenOffice-Liste diverse Leichen angesammelt hatten. Da auch verschiedene Geräte in dem Netz sind die nur Sporadisch eingeschaltet / angeschlossen werden (Notebooks, WLAN-Handhelds e.t.c.) habe ich ein kleines Shell-Script mit einem Perl-Script kombiniert das 3x täglich per Cron aufgerufen wird, mit nmap die aktiven hosts überprüft und anschließend das Ergebniss (Kein Gerät auf der IP-Addresse aktiv -> Wert null oder Gerät auf der IP-Addresse aktiv -> Mac-Addresse) in eine hierfür angelegte MariaDB schreibt, bzw. schreiben soll. Dies hat auch Grundsätzlich funktioniert, nach einem Monat hab ich eine kleine Datenbankabfrage geschrieben und mit unsere Office-Tabelle verglichen. Sozusagen alles Super. Das Script habe ich dennoch weiterlaufen lassen, um u.U. ein ergebnis nochmal zu prüfen oder evtl. einer doppelt vergebenen IP-Addresse auf die Schliche zu kommen.

Das Problem:
Das Schreiben der Daten durch das Perlscript ist irgendwann "vor dem Hammer" gelaufen. ich denke aufgrund eines Fehlers von mir im Datenbank design. Der Fehler:

DBD::mysql::db do failed: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs at $pfad/ipmacauswertung.pl line 25.

Ich habe die Datenbank vermutlich falschherum aufgebaut ODER aber in der obersten Zeile die Felder mit einem falschen Typ definiert, oder beides zusammen (Ich habe Screenshots aus phpmyadmin hinzugefügt, zur Darstellung finde ich das recht praktisch.

Ich habe erste eine Spalte hinzugefügt mit varchar(65) und in der ersten Zeile dort den Wert" IP quasi als überschift hinzugefügt. Dann habe ich für jede IP-Addresse des Netzes eine neue Zeile erstellt, mit der jeweiligen IP-Addresse.

Anschließend fügt das o.G. PerlScript ein Spalte hinzu, mit einenm "char(50)" als erste Zeile und einer "Menschenlesbaren" Zeit/Datumsangabe und dann zu jeder IP-Addresse mit Ergebniss die entsprechende Mac-Addresse. Dies hat ca. 2,5 Monate gut funktioniert a ca. 3 Einträge / Tag, bis zur o.G. Fehlermeldung.

Meine Vermutung 1: Ich hätte die IP-Addresse in Spalten anlegen müssen, und die je Ergebniss eine Zeile hinzufügen müssen.
Meine Vermutung 2: Ich hätte kein so großes Feld (Datum in Textform) hinzufügen sollen für die Spalten, sondern z.B. Fortlaufende Zahlen, und das Datum entweder in Zeile 2 oder in eine Seperate Datenbank (Normalform?) Schreiben müssen.?

Jetzt die Frage
Welche der Beide Vermutungen, oder beide, oder auch gar keine Stimmen? Ich habe schon viel gegoogelt, aber keine genauen aussagen gefunden, bzw. habe die mans zu dem Thema nicht genau nachvollziehen können.

Übersicht:
uebersicht.png

Strukturchar:
strukturchar.png
 
Eine Datenbank sollte in der Länge wachsen und nicht in der Breite. Überleg mal, du änderst mit jedem Durchlauf dein Datenbanklayout. Das ist ziemlich unschön. Ein ALTER TABLE sollte eigentlich eher die Seltenheit sein. Standard um Datensätze zu ergänzen ist INSERT INTO.
Entweder arbeitest du in einer Tabelle mit den Überschriften: "Zeitstempel", "IP" und "MAC" (was das einfachste sein dürfte (ich packe irgendwie immer noch gerne eine "ID" Spalte dazu)) oder du Arbeitest mit mehreren Tabellen.

Daraus kannst du später eigentlich alles raus ziehen was du willst. Per SELECT DISTINCT bekommst du dann alle verwendeten IPs und MACs.
 
Hallo,

die Struktur ist nicht wirklich sinnvoll für das was du machen möchtest.
Für den Anwendungsfall ist aus meiner Sicht eine Tabelle mit genau 3 Spalten ausreichend.

IP-Adresse, MAC-Adresse, Zeitpunkt "Zuletzt gesehen".

Die IP und die MAC ergeben zusammengesetzt den Primärschlüssel, da diese Kombination einzigartig ist und für den Anwendungsfall keine ID o.ä. benötigt wird.
Der "Default" auf der Spalte "Zeitpunkt" kann auf "NOW()" gesetzt werden, so dass dein Perl-Skript sowas wie

INSERT INTO ip_finder (ip, mac) VALUES ('192.168.2.1', 'AA-BB-CC-DD-EE-FF')
ON DUPLICATE KEY UPDATE zeitpunkt = NOW();

gegen die DB schreiben kann.

Damit kannst du feststellen, welche IP-Adresse mit welcher MAC zusetzt gesehen wurde und einige Auswertungen fahren.

Gruß
Markus
 
Ich persönlich würde mir nochmal ganz genau darüber Gedanken machen was ich eigentlich sehen will... Das ist kein Maria DB Ding sondern könnte auch andere Datenbanken sprängen was du da gebaut hast.
Ich würde nur drei spalten verwenden: IP Adresse, Mac Adresse und Datum des letzten Changes.
Das könnte man imho wesentlich besser verwalten. Wenn 3x Täglich eine neue Spalte angelegt wird ohne das irgendwann die alten Spalten gelöscht werden... wird die Datenbank immer an irgenwelche Grenzen stossen

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors.

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size.

...

Individual storage engines might impose additional restrictions that limit table column count.

InnoDB permits up to1000 columns.
Zum Vergrößern anklicken....
 
Das würde stimmen, wenn ich nicht sehen wollen würde ob sich die MAC-Addresse ändert, z.B. wenn z.B. ein Notebook und irgend ein anderes Gerät das sporadisch an ist die gleiche IP haben, und sich gegenseitig manchmal blockieren.

Es ist auch eher alsTheoretische Frage gemeint - 4096 columns und row size of 65,535 klingt zumindest so als wäre es andersherum sinnvoller gewesen. GGF.könnte man ja auch jeden Monat eine neue Tabelle anfangen.

Als soooo große Datenmege empfinde ich das aber spontan garnicht, gerade im Kaufmännischen Bereich werden doch ggf. viel größere Datenmengen gesammelt.
 
CommanderZed schrieb:
Als soooo große Datenmege empfinde ich das aber spontan garnicht, gerade im Kaufmännischen Bereich werden doch ggf. viel größere Datenmengen gesammelt.
Zum Vergrößern anklicken....
Ja, aber die sind in mehreren Tabellen organisiert und durch einen Schlüssel verknüpft. Du arbeitest gegen das Prinzip von SQL, so wie du das machst könntest du das gleich in eine Datei schreiben.

Mach es so wie es die anderen gezeigt haben, oder denke über eine ganz andere Lösung nach.
 
Nochmal, ich brauche keine Lösung, das Projekt ist abgeschlossen! Es ist eine Verständnissfrage!

/edit In einem Jahr sind das 1080 "Zeitpunkte" und ingesamt 1785 IP-Addressen. Also ich empfinde das wirklich nicht als viel. Es ist ja auch nicht so das die Performance der Datenbank völlig in die Knie geht, selbst komplexere Abfrage haben dtl. unter 1 Sekunde gebraucht. Insgesamt sinds momentan etwas unter 2MB.

Klar könnte man das in eine Textdatei schreiben, aber eine Datenbank ist doch exakt dazu da große Mengen an Informationen leich durchsuchbar zu Speichern.

Was ist denn z.B. mit einer Bücherei, die die Entnahmehäufigkeit un das Datum von Büchern speichert? Oder legt man da dann für jedes Buch eine eigene Tabelle an? Ich könnte ja, deshalb die Verständnissfrage, z.B. auch jede IP in einer eigenen Tabelle Speichern.

/Doppeledit Ich denke Rakor hat bereits alles beantwortet, sein Post ist irgendwie untergegangen.
 
Das Designproblem ist, dass Du ein aktuelles Datum (Zeitstempel) als Spaltenname nimmst, so ist das in relational wirklich nicht vorgesehen.
Daten gehoeren da in Datenfelder (Zeilen-Inhalte). Die Spaltennamen sind rein beschreibend.
 
double-p schrieb:
Das Designproblem ist, dass Du ein aktuelles Datum (Zeitstempel) als Spaltenname nimmst, so ist das in relational wirklich nicht vorgesehen.
Daten gehoeren da in Datenfelder (Zeilen-Inhalte). Die Spaltennamen sind rein beschreibend.
Zum Vergrößern anklicken....

Also hätte ich als Spaltenname quasi eine fortlaufende Nummer (z.B.) nehemen müssen? Das klingt für mich einleuchtend. Das Datum dann in Zeile zwei, oder ausgelagert in eine 2. Datenbank?

Ich werde das als experiment mal bei Gelegenheit daraufhin versuchen umschreiben.

Würdest du denn auch Rakor zustimmen, und Zeilen und Spalten Quasi "Tauschen"?
 
peterle schrieb:
Wieso nicht?
Wenn er nur sammelt und am Schluß eine Auswertung rauswirft, dann geht das doch auch in einer Tabelle oder übersehe ich etwas?
Zum Vergrößern anklicken....

Gut, ist jetzt eine Normalisierungsfrage... Wenn ich das richtig verstehe wollte der TS den kompletten Zustand des Netzes zu einem bestimmten Zeitpunkt speichern. Wenn du nicht, wie der TS, deine ganze Tabelle mit NULL füllen willst, oder sonstige Limitierungen haben willst, dann wird das mit einer Tabelle recht schwierig. Ansonsten hast du halt ständig Einträge in der Form <ip>, NULL, now(), wenn die IP nicht vergeben wurde.

Die dann auch wieder "ins Excel Format" bringen wird dann auch eklig.

Ich denke da eher so an eine M:N aus IP <-> Datum <-> MAC und nur die Eintragen die vergeben sind. Den Rest kann SQL im Kreuzprodukt selbst mit NULL ausfüllen.

edit: Also dann IPs "UPSERTen", MACs "UPSERTen" und dann die Vergeben unter Datum eintragen.
 
-Nuke- schrieb:
Ansonsten hast du halt ständig Einträge in der Form <ip>, NULL, now(), wenn die IP nicht vergeben wurde.
Zum Vergrößern anklicken....
Das ist nur korrekt unter der Annahme, dass die Historie der IP-Vergaben gespeichert werden soll. Wenn man nur den aktuellen Stand haben möchte, reicht doch das Tripel IP-Adresse|MAC|Zeitstempel_letzte_Änderung, wie es Markus schrieb.

Rob
 
Mir ist (war) in dem Fall tatsächlich die Historie wichtig. Wobei natürlich nie die gesamte Tabelle mit 0 gefüllt ist, da Netzwerkdrucker e.t.c. natürlirch 24/7 erreichbar sind, genauso router e.t.c.
 
KobRheTilla schrieb:
Das ist nur korrekt unter der Annahme, dass die Historie der IP-Vergaben gespeichert werden soll. Wenn man nur den aktuellen Stand haben möchte, reicht doch das Tripel IP-Adresse|MAC|Zeitstempel_letzte_Änderung, wie es Markus schrieb.
Zum Vergrößern anklicken....

Wenn man nur den aktuellen Stand haben wollen würde, dann bräuchte man ihn nicht in einer Datenbank speichern. Es geht da immer irgendwie um Historie und gerade der Vorschlag von Markus zeigt immer nur den aktuellen Stand, was dann im Endeffekt sinnlos ist zu speichern. Alle Altwerte werden ja überschrieben. Es kommt ja immer der komplette Adressbereich rein bei so einem Scan. Und gerade der Fall <IP>, NULL, now() wird dann schnell sehr verwirrend. Und wenn man die NULL Werte nicht speicherst, dann hast du laut Datenbank Doppelbelegungen, welche sich nur von der Uhrzeit unterscheiden. Auch nicht sonderlich übersichtlich.

Sofern MariaDB NULL in einem Primärindex überhaupt erlaubt. Weiß das gerade gar nicht.
 
Ich weiß nicht, was da (selbst bei sukzessivem Speichern) unübersichtlich sein soll. Und statt NULL kannst du ja auch die MAC-Adresse 0:0:0:0:0:0 als Wert nehmen.

Rob
 
Naja, jetzt dann auch noch anfangen mittels Platzhalterwerten und Workarounds zu arbeiten macht das ganze "Design" nicht besser. ;) Auf Krampf sich an eine Tabelle klammern und dann dadurch Historie verlieren und gezwungene Platzhalter einfügen...
 
-Nuke- schrieb:
IP <-> Datum <-> MAC
Zum Vergrößern anklicken....

Wenn Du den über längere Zeit immer wieder mal füllst und zwar nur mit den IPs, die Du zu dem Zeitpunkt auch gefunden hast, hast Du meiner Meinung nach alles, was Du brauchst.

Du kannst dann eine Liste aller vergebenen IPs genieren, eine Zuordnung dazu, welche MACs dazu vergeben sind. Kontrollieren, ob IPs an mehrere MACs vergeben wurden oder nicht, usw, usw.
Ich kann natürlich auch für jedes gewünschte Ergebnis einer Zielstellung eine eigene Tabelle schaffen, was aber nur aus Gründen der Performanz sinnvoll sein dürfte.
 
Mal ganz blöd dahergeredert: Kann man so etwas nicht einfach über Hash Tabellen machen die dann an eine SDBM Datenbank (use SDBM_File) angebunden werden. Als key die MAC Adresse und als value die IP und Uhrzeit?
 
-Nuke- schrieb:
Naja, jetzt dann auch noch anfangen mittels Platzhalterwerten und Workarounds zu arbeiten macht das ganze "Design" nicht besser. ;) Auf Krampf sich an eine Tabelle klammern und dann dadurch Historie verlieren und gezwungene Platzhalter einfügen...
Zum Vergrößern anklicken....

Der MAC-Platzhalter war nur dafür gedacht, falls das RDBMS keine Schlüssel mit NULL-Werten abbilden kann. Und man verliert auch keine Historie, wenn man mit nur einer Tabelle arbeitet. Es kommt eben darauf an, wie man es macht.

Rob
 
Wenn du mit einer Tabelle die Historie nicht verlieren willst, dann geht der Vorschlag von Markus nicht.

1. dürfte IP, MAC kein Primary Key sein
2. es dürfte kein UPSERT stattfinden
3. du haust dir die Tabelle mit IP, NULL, now() voll

Ganz davon ab, dass das dann alles total unnormalisiert ist, wird das Auslesen der Historie schwierig. Man normalisiert sich den ganzen Kram per SQL und dann pickt man sich wieder einzelne Sachen raus. Da kann man sich im Endeffekt das RDBMS auch sparen und es in eine Textdatei schreiben, da dir SQL nicht sonderlich hilft.
 
Also wenn du fürs Einfügen deiner Daten das Schema ändern musst ist dein Schema broken by design. Es sieht so aus als hättest du noch nie mit relationaler Algebra gearbeitet und dementsprechend noch keine Vorstellung davon was hier im Thread mit Normalisierung gemeint ist. Falls dem so ist rate ich dir dazu dir erst die Grundlagen zu erarbeiten, weil du dir sonst das Leben mit SQL sehr schwer machen wirst. SQL ist schon nicht die schönste Sprache *hust*, aber wenn man aus Unwissenheit gegen die Theorie kämpft auf der SQL aufbaut dürfte jede nicht völlig triviale Problemstellung die Hölle werden. Außerdem wirst du dich dafür hassen später den Code warten zu müssen, den du in der Zwischenzeit verbrichst.
 
Crest> Genau deswegen hab ich hier gefragt.

Kannst du da etwas (Webseite oder nicht allzu teures Buch) als Einstieg empfehlen? Wie bereits geschrieben, empfinde ich die vielen Man-Pages e.t.c. auf der mysql Seite oft etwas zu umfangreich für den Einstieg.
 
Du musst dich einloggen oder registrieren, um hier zu antworten.
Zurück
Oben