Datenbank Update Fehler bei Auswahl vom Stecker Typ
-
Hallo zusammen,
seit neuestem habe ich das Problem, dass wenn ich einen neuen Port erstelle und den Steckertyp auswähle, folgender Fehler kommt. Dabei ist es egal in welchem Objekt ich diesen auswähle.
Database error : Query error: 'UPDATE isys_catg_port_list SET isys_catg_port_list__isys_catg_netp_list__id = 67, isys_catg_port_list__isys_plug_type__id = 1, isys_catg_port_list__isys_port_negotiation__id = 1, isys_catg_port_list__isys_port_standard__id = NULL, isys_catg_port_list__isys_port_duplex__id = 2, isys_catg_port_list__isys_port_type__id = 3, isys_catg_port_list__isys_port_mode__id = 1, isys_catg_port_list__port_speed_value = '1000000000', isys_catg_port_list__isys_port_speed__id = 4, isys_catg_port_list__title = 'eth - 01', isys_catg_port_list__description = '', isys_catg_port_list__mac = '', isys_catg_port_list__state_enabled = 1, isys_catg_port_list__status = 2, isys_catg_port_list__isys_catg_hba_list__id = NULL, isys_catg_port_list__mtu = 0 WHERE isys_catg_port_list__id = '1787';': Cannot add or update a child row: a foreign key constraint fails (`idoit_data`.`isys_catg_port_list`, CONSTRAINT `isys_catg_port_list_ibfk_3` FOREIGN KEY (`isys_catg_port_list__isys_plug_type__id`) REFERENCES `isys_plug_type` (`isys_plug_type__id`) ON DELETE)Ist das ein bekannter Fehler?
Idoit Version 1.13.2 Pro -
Hallo @SAESF,
bitte führe mal diese Querys aus und schicke Sie uns.
Entweder hier oder an help@i-doit.com.
könnten Sie bitte folgende SQL-Querys ausführen und uns die Ergebnisse zusenden?SHOW CREATE TABLE isys_catg_port_list;
SHOW CREATE TABLE isys_plug_type;
SELECT * FROM isys_plug_type LIMIT 4;
SHOW CREATE TABLE isys_connection_type;
SELECT * FROM isys_connection_type ORDER BY isys_connection_type__id DESC LIMIT 4;mfg Micha
-
Hallo @Michael-Overkamp ,
entschuldigen Sie bitte die verspätete Antwort, war bis jetzt im Aussendienst.
So hier sind die Abfragen:MariaDB [idoit_data]> SHOW CREATE TABLE isys_catg_port_list; +---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | isys_catg_port_list | CREATE TABLE `isys_catg_port_list` ( `isys_catg_port_list__id` int(10) unsigned NOT NULL AUTO_INCREMENT, `isys_catg_port_list__isys_catg_connector_list__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_obj__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_port_negotiation__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_port_standard__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_port_duplex__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_plug_type__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__cable_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `isys_catg_port_list__isys_port_type__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_port_mode__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_port_speed__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_catg_netp_list__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__isys_catg_hba_list__id` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__port_speed_value` bigint(32) DEFAULT NULL, `isys_catg_port_list__title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `isys_catg_port_list__description` text COLLATE utf8_unicode_ci DEFAULT NULL, `isys_catg_port_list__mac` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `isys_catg_port_list__state_enabled` int(10) unsigned DEFAULT 1, `isys_catg_port_list__number` int(10) unsigned DEFAULT 0, `isys_catg_port_list__status` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__property` int(10) unsigned DEFAULT NULL, `isys_catg_port_list__mtu` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`isys_catg_port_list__id`), KEY `isys_netp_port_FKIndex1` (`isys_catg_port_list__isys_port_type__id`), KEY `isys_netp_port_FKIndex2` (`isys_catg_port_list__isys_catg_netp_list__id`), KEY `isys_netp_port_FKIndex3` (`isys_catg_port_list__isys_port_speed__id`), KEY `isys_netp_port_FKIndex5` (`isys_catg_port_list__isys_port_duplex__id`), KEY `isys_netp_port_FKIndex6` (`isys_catg_port_list__isys_port_negotiation__id`), KEY `isys_netp_port_FKIndex7` (`isys_catg_port_list__isys_port_standard__id`), KEY `isys_netp_port__isys_plug_type__id` (`isys_catg_port_list__isys_plug_type__id`), KEY `isys_netp_port__isys_obj__id` (`isys_catg_port_list__isys_obj__id`), KEY `isys_catg_port_list__isys_catg_connector_list__id` (`isys_catg_port_list__isys_catg_connector_list__id`), KEY `isys_catg_port_list__isys_catg_hba_list__id` (`isys_catg_port_list__isys_catg_hba_list__id`), KEY `isys_catg_port_list__mac` (`isys_catg_port_list__mac`), KEY `isys_catg_port_list__title` (`isys_catg_port_list__title`), KEY `isys_catg_port_list__mtu` (`isys_catg_port_list__mtu`), CONSTRAINT `isys_catg_port_list_ibfk_1` FOREIGN KEY (`isys_catg_port_list__isys_catg_netp_list__id`) REFERENCES `isys_catg_netp_list` (`isys_catg_netp_list__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_10` FOREIGN KEY (`isys_catg_port_list__isys_port_type__id`) REFERENCES `isys_port_type` (`isys_port_type__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_12` FOREIGN KEY (`isys_catg_port_list__isys_catg_connector_list__id`) REFERENCES `isys_catg_connector_list` (`isys_catg_connector_list__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_13` FOREIGN KEY (`isys_catg_port_list__isys_catg_hba_list__id`) REFERENCES `isys_catg_hba_list` (`isys_catg_hba_list__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_14` FOREIGN KEY (`isys_catg_port_list__isys_plug_type__id`) REFERENCES `isys_connection_type` (`isys_connection_type__id`), CONSTRAINT `isys_catg_port_list_ibfk_2` FOREIGN KEY (`isys_catg_port_list__isys_port_type__id`) REFERENCES `isys_port_type` (`isys_port_type__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_3` FOREIGN KEY (`isys_catg_port_list__isys_plug_type__id`) REFERENCES `isys_plug_type` (`isys_plug_type__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_4` FOREIGN KEY (`isys_catg_port_list__isys_port_speed__id`) REFERENCES `isys_port_speed` (`isys_port_speed__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_6` FOREIGN KEY (`isys_catg_port_list__isys_port_duplex__id`) REFERENCES `isys_port_duplex` (`isys_port_duplex__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_7` FOREIGN KEY (`isys_catg_port_list__isys_port_negotiation__id`) REFERENCES `isys_port_negotiation` (`isys_port_negotiation__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_8` FOREIGN KEY (`isys_catg_port_list__isys_port_standard__id`) REFERENCES `isys_port_standard` (`isys_port_standard__id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `isys_catg_port_list_ibfk_9` FOREIGN KEY (`isys_catg_port_list__isys_obj__id`) REFERENCES `isys_obj` (`isys_obj__id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1790 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+MariaDB [idoit_data]> SHOW CREATE TABLE isys_plug_type; ERROR 1146 (42S02): Table 'idoit_data.isys_plug_type' doesn't existMariaDB [idoit_data]> SELECT * FROM isys_plug_type LIMIT 4; ERROR 1146 (42S02): Table 'idoit_data.isys_plug_type' doesn't existMariaDB [idoit_data]> SHOW CREATE TABLE isys_connection_type; +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | isys_connection_type | CREATE TABLE `isys_connection_type` ( `isys_connection_type__id` int(10) unsigned NOT NULL AUTO_INCREMENT, `isys_connection_type__title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `isys_connection_type__description` text COLLATE utf8_unicode_ci NOT NULL, `isys_connection_type__const` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `isys_connection_type__sort` int(10) NOT NULL, `isys_connection_type__status` int(10) NOT NULL, `isys_connection_type__property` int(10) NOT NULL, PRIMARY KEY (`isys_connection_type__id`), KEY `isys_connection_type__title` (`isys_connection_type__title`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------+-----------------------------+-----------------------------------+-----------------------------+----------------------------+------------------------------+--------------------------------+ | isys_connection_type__id | isys_connection_type__title | isys_connection_type__description | isys_connection_type__const | isys_connection_type__sort | isys_connection_type__status | isys_connection_type__property | +--------------------------+-----------------------------+-----------------------------------+-----------------------------+----------------------------+------------------------------+--------------------------------+ | 13 | SFP | | | 13 | 2 | 0 | | 12 | SFP+ | | | 12 | 2 | 0 | | 11 | LC | | | 11 | 2 | 0 | | 10 | RJ 45 | | | 10 | 2 | 0 | +--------------------------+-----------------------------+-----------------------------------+-----------------------------+----------------------------+------------------------------+--------------------------------+ -
Hallo @SAESF ,
kein Problem der Fehler ist schon bekannt und hier die Lösung.
Nach dem letzten Update wurde die Tabelle für die Steckertypen neu aufgebaut und bei dem neuen Konzept wurde anscheinend ein Wert verdoppelt.Die folgenden Schritte sollten unbedingt von einem DB Administrator durchgeführt werden.
Vorher bitte ein Backup der Datenbank erstellen. Ganz Wichtig.
ALTER TABLE `isys_catg_port_list` DROP FOREIGN KEY `isys_catg_port_list_ibfk_3`; -
Hallo @Michael-Overkamp ,
vielen Dank das hat ohne Probleme geklappt.
MariaDB [idoit_data]> ALTER TABLE `isys_catg_port_list` DROP FOREIGN KEY `isys_catg_port_list_ibfk_3`; Query OK, 0 rows affected (0.121 sec) Records: 0 Duplicates: 0 Warnings: 0Steckertypen lassen sich wieder auswählen.
Ich wünsche ein schönes Wochenende
