Incoherence mysql with depotid > 50 chars

Antoine GUEVARA
Beiträge: 54
Registriert: 13 Apr 2011, 22:01
Wohnort: GIP RECIA - France

Incoherence mysql with depotid > 50 chars

Beitragvon Antoine GUEVARA » 02 Dez 2014, 15:02

Hi,

During my setup of opsi Mysql Module I've found this bug :

When I launch opsi-product-updater -i -vv I've got this message ,

Code: Alles auswählen

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`opsiXXXXXXXX`.`PRODUCT_ON_DEPOT`, CONSTRAINT `PRODUCT_ON_DEPOT_ibfk_2` FOREIGN KEY (`depotId`) REFERENCES `HOST` (`hostId`))


This is due to my fqdn of 59 chars for my opsi-depot-server, but in your mysql schema you define a foreign key depotId -> ( HOST -> hostId )

depotId is limited to varchar(50) and hostId varchar(255)

Why ?

I've update the depotId row to support varchar(64) ( DNS RFC ) and it's work fine.

It is possible to release an update of this schema or not ?

This is my update :

ALTER TABLE `PRODUCT_ON_DEPOT` CHANGE `depotId` `depotId` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL


( I have 180 OPSI servers, and I need to find a way to deploy correctly the mysql module ;) )

Thank you,

Antoine

Antoine GUEVARA
Beiträge: 54
Registriert: 13 Apr 2011, 22:01
Wohnort: GIP RECIA - France

Re: Incoherence mysql with depotid > 50 chars

Beitragvon Antoine GUEVARA » 02 Dez 2014, 15:37

This is the same for HARDWARE_* tables, the row hostId is limited to 50 chars

Antoine GUEVARA
Beiträge: 54
Registriert: 13 Apr 2011, 22:01
Wohnort: GIP RECIA - France

Re: Incoherence mysql with depotid > 50 chars

Beitragvon Antoine GUEVARA » 03 Dez 2014, 08:07

this is the shell script I use to path my OPSIs servers

Code: Alles auswählen

#!/bin/bash

TABLES="HARDWARE_CONFIG_1394_CONTROLLER
HARDWARE_CONFIG_AUDIO_CONTROLLER
HARDWARE_CONFIG_BASE_BOARD
HARDWARE_CONFIG_BIOS
HARDWARE_CONFIG_CACHE_MEMORY
HARDWARE_CONFIG_CHASSIS
HARDWARE_CONFIG_COMPUTER_SYSTEM
HARDWARE_CONFIG_DISK_PARTITION
HARDWARE_CONFIG_FLOPPY_CONTROLLER
HARDWARE_CONFIG_FLOPPY_DRIVE
HARDWARE_CONFIG_HARDDISK_DRIVE
HARDWARE_CONFIG_HDAUDIO_DEVICE
HARDWARE_CONFIG_IDE_CONTROLLER
HARDWARE_CONFIG_KEYBOARD
HARDWARE_CONFIG_MEMORY_BANK
HARDWARE_CONFIG_MEMORY_MODULE
HARDWARE_CONFIG_MONITOR
HARDWARE_CONFIG_NETWORK_CONTROLLER
HARDWARE_CONFIG_OPTICAL_DRIVE
HARDWARE_CONFIG_PCI_DEVICE
HARDWARE_CONFIG_PCMCIA_CONTROLLER
HARDWARE_CONFIG_POINTING_DEVICE
HARDWARE_CONFIG_PORT_CONNECTOR
HARDWARE_CONFIG_PRINTER
HARDWARE_CONFIG_PROCESSOR
HARDWARE_CONFIG_SCSI_CONTROLLER
HARDWARE_CONFIG_SYSTEM_SLOT
HARDWARE_CONFIG_TAPE_DRIVE
HARDWARE_CONFIG_USB_CONTROLLER
HARDWARE_CONFIG_USB_DEVICE
HARDWARE_CONFIG_VIDEO_CONTROLLER"

root_password="rootpassword"

echo "Patch des tables HARDWARE_CONFIG .... "
for table in $TABLES;
do
    echo $table
    mysql -u root -p$root_password -e 'ALTER TABLE opsi.'$table' CHANGE `hostId` `hostId` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;'
done
echo "Patch de la table PRODUCT_ON_DEPOT ... "

mysql -u root -p$root_password -e 'ALTER TABLE opsi.`PRODUCT_ON_DEPOT` CHANGE `depotId` `depotId` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;'


Now I have my hwaudit fully functional and everything works fine ( for now :) )

Benutzeravatar
n.wenselowski
uib-Team
Beiträge: 3195
Registriert: 04 Apr 2013, 12:15

Re: Incoherence mysql with depotid > 50 chars

Beitragvon n.wenselowski » 09 Dez 2014, 09:58

Hello Antoine,

thank you for the report.

I created a ticket for this and this will be fixed for the next release.
Probably there will also be a simple way to update the columns with opsi-setup --update-mysql.


With kind regards

Niko

Code: Alles auswählen

import OPSI

Antoine GUEVARA
Beiträge: 54
Registriert: 13 Apr 2011, 22:01
Wohnort: GIP RECIA - France

Re: Incoherence mysql with depotid > 50 chars

Beitragvon Antoine GUEVARA » 10 Dez 2014, 08:44

Yes,

This is the patch for opsi-setup :

Code: Alles auswählen

--- opsi-setup-old   2014-12-04 09:50:10.158781374 +0100
+++ opsi-setup   2014-12-03 15:48:37.029043590 +0100
@@ -1214,7 +1214,7 @@
       logger.notice(u"Updating database table HOST from opsi 3.3 to 3.4")
       # SOFTWARE_CONFIG
       logger.notice(u"Updating table SOFTWARE_CONFIG")
-      mysql.execute(u"alter table SOFTWARE_CONFIG add `hostId` varchar(50) NOT NULL;")
+      mysql.execute(u"alter table SOFTWARE_CONFIG add `hostId` varchar(64) NOT NULL;")
       mysql.execute(u"alter table SOFTWARE_CONFIG add `softwareId` varchar(100) NOT NULL;")
       for res in mysql.getSet(u"SELECT hostId,host_id FROM `HOST` WHERE `hostId` != ''"):
          mysql.execute(u"update SOFTWARE_CONFIG set `hostId`='%s' where `host_id`=%s;" % (res['hostId'].replace("'", "\\'"), res['host_id']))
@@ -1229,7 +1229,7 @@
       # HARDWARE_CONFIG
       if key.startswith(u'HARDWARE_CONFIG') and 'host_id' in tables[key]:
          logger.notice(u"Updating database table %s from opsi 3.3 to 3.4" % key)
-         mysql.execute(u"alter table %s add `hostId` varchar(50) NOT NULL;" % key)
+         mysql.execute(u"alter table %s add `hostId` varchar(64) NOT NULL;" % key)
          for res in mysql.getSet(u"SELECT hostId,host_id FROM `HOST` WHERE `hostId` != ''"):
             mysql.execute(u"update %s set `hostId` = '%s' where `host_id` = %s;" % (key, res['hostId'].replace("'", "\\'"), res['host_id']))
          mysql.execute(u"alter table %s drop `host_id`;" % key)
@@ -1240,7 +1240,7 @@
       logger.notice(u"Updating database table HARDWARE_INFO from opsi 3.3 to 3.4")
       # HARDWARE_INFO
       logger.notice(u"Updating table HARDWARE_INFO")
-      mysql.execute(u"alter table HARDWARE_INFO add `hostId` varchar(50) NOT NULL;")
+      mysql.execute(u"alter table HARDWARE_INFO add `hostId` varchar(64) NOT NULL;")
       for res in mysql.getSet(u"SELECT hostId,host_id FROM `HOST` WHERE `hostId` != ''"):
          mysql.execute(u"update HARDWARE_INFO set `hostId` = '%s' where `host_id` = %s;" % (res['hostId'].replace("'", "\\'"), res['host_id']))
       mysql.execute(u"alter table HARDWARE_INFO drop `host_id`;")


And for /usr/share/pyshared/OPSI/Backend/SQL.py

Code: Alles auswählen

--- SQL-old.py   2014-12-04 09:47:58.374777845 +0100
+++ SQL.py   2014-12-04 08:41:38.286671262 +0100
@@ -475,7 +475,7 @@
                `productId` varchar(255) NOT NULL,
                `productVersion` varchar(32) NOT NULL,
                `packageVersion` varchar(16) NOT NULL,
-               `depotId` varchar(50) NOT NULL,
+               `depotId` varchar(64) NOT NULL,
                `productType` varchar(16) NOT NULL,
                `locked` bool,
                PRIMARY KEY (`productId`, `depotId`),
@@ -802,7 +802,7 @@
                   u'`hardware_id` INTEGER NOT NULL ' + self._sql.AUTOINCREMENT + ',\n'
          hardwareConfigTable = u'CREATE TABLE `' + hardwareConfigTableName + '` (\n' + \
                   u'`config_id` INTEGER NOT NULL ' + self._sql.AUTOINCREMENT + ',\n' + \
-                  u'`hostId` varchar(50) NOT NULL,\n' + \
+                  u'`hostId` varchar(64) NOT NULL,\n' + \
                   u'`hardware_id` INTEGER NOT NULL,\n' + \
                   u'`firstseen` TIMESTAMP NOT NULL DEFAULT \'0000-00-00 00:00:00\',\n' + \
                   u'`lastseen` TIMESTAMP NOT NULL DEFAULT \'0000-00-00 00:00:00\',\n' + \


Thank you,

Antoine

Benutzeravatar
n.wenselowski
uib-Team
Beiträge: 3195
Registriert: 04 Apr 2013, 12:15

Re: Incoherence mysql with depotid > 50 chars

Beitragvon n.wenselowski » 19 Dez 2014, 09:59

Hello Antoine,

to keep you updated on this topic: I made the required changes and so this will be part of the next update.
Because the holiday season is around the corner it probably will take some time until first experimental builds can be expected ;)

Happy holidays!

-Niko

Code: Alles auswählen

import OPSI

Antoine GUEVARA
Beiträge: 54
Registriert: 13 Apr 2011, 22:01
Wohnort: GIP RECIA - France

Re: Incoherence mysql with depotid > 50 chars

Beitragvon Antoine GUEVARA » 06 Jan 2015, 09:22

Hi,

Happy new year and thank you for your reactivity.

What kind of patch did you do ? what are the files impacted by this one ?

Regards,

Antoine

Benutzeravatar
n.wenselowski
uib-Team
Beiträge: 3195
Registriert: 04 Apr 2013, 12:15

Re: Incoherence mysql with depotid > 50 chars

Beitragvon n.wenselowski » 06 Jan 2015, 16:36

Hello Antoine,

I changed OPSI/Backend/SQL.py to use the new size when setting up a new backend and then I modified the updateMySQLBackend-method used in opsi-setup to apply a fix similar to yours to already existing backends.

I took this as a chance to shave the yak a little further and moved the method from opsi-setup (tool) to python-opsi(aka. OPSI - our Python library) to avoid duplicate work in the future. We are maintaining a version of opsi-setup for UCS and one for all other versions and I'm trying to get rid of the duplicate code and put as much as I can into python-opsi so that all kinds of tools can use it.


With kind regards

Niko

Code: Alles auswählen

import OPSI

Benutzeravatar
n.wenselowski
uib-Team
Beiträge: 3195
Registriert: 04 Apr 2013, 12:15

Re: Incoherence mysql with depotid > 50 chars

Beitragvon n.wenselowski » 12 Jun 2015, 14:54

Hello Antoine,

There are experimental packages in https://build.opensuse.org/project/show ... perimental that should fix the problem.
Please be aware that these are experimental packages and we do not yet recommend in using them in your productive environment!

After updating python-opsi and opsi-depotserver from this repository you should run:

Code: Alles auswählen

opsi-setup --init-current-config
opsi-setup --update-mysql
service opsiconfd restart


After this everything should be working as expected.


With kind regards

Niko

Code: Alles auswählen

import OPSI