Backend für MySQL konfigurieren

[sailqlej]

Hallo,

ich wollte unseren opsi-Server so einrichten, das er die Daten der Inventarisierung in einer MySQL-Datenbank speichert. Dabei kam es zu einigen Fehlern.

Zuerst wurde der Server auf den aktuellen Stand gebracht mit:

Code: Alles auswählen

apt-get update && apt-get dist-upgrade

zum Einsatz kommt ein Debian Lenny auf dem auch schon die Vorgängerversion 3.4 lief. Portierung ist jetzt ca. 2 Monate her und verlief problemlos nach dem Handbuch.

danach ging es an die Einrichtung des Datenbankservers (wir nutzen einen dezidierten Datenbankserver mit dem DNS-Namen database.fak6.tu-berlin.de):

Code: Alles auswählen

ostrya:~# opsi-setup --configure-mysql
[2] [Feb 27 15:58:32] Traceback: (Logger.py|710)
[2] [Feb 27 15:58:32]      line 2772 in '<module>' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 15:58:32]      line 2738 in 'main' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 15:58:32]      line 2015 in 'configureMySQLBackend' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 15:58:32]      ==>>> (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-berlin.de IDENTIFIED BY '********************'' at line 1") (opsi-setup|2780)

ERROR: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-berlin.de IDENTIFIED BY ''********************'' '' at line 1")

Indem ich nur database als Server angegeben habe nachdem ich einen entsprechenden CNAME-Record im DNS angelegt hatte ließ sich das Problem umgehen.

Danach gings an die Konfiguration der Datei /etc/opsi/backendManager/dispatch.own.conf (die einen Link auf dispatch.conf hat):

Code: Alles auswählen

backend_.*         : file, opsipxeconfd, mysql, dhcpd
host_.*            : file, opsipxeconfd, dhcpd
productOnClient_.* : file, opsipxeconfd
configState_.*     : file, opsipxeconfd
license.*          : mysql
softwareLicense.*  : mysql
audit.*            : mysql
.*                 : file

Ich hoffe die Syntax ist für meine Zwecke korrekt. Standardbackend soll file sein, die Inventarisierungsdateien sollen in die Datenbank und ein lokaler DHCP-Server ist für die IPs verantwortlich.

Nach Handbuch geht es weiter mit

Code: Alles auswählen

ostrya:~# opsi-setup --init-current-config
[5] [Feb 27 16:05:21] Modules file signature verified (customer: opsi community) (MySQL.py|408)
[5] [Feb 27 16:05:21] Creating base path: '/var/lib/opsi/config' (File.py|213)
[5] [Feb 27 16:05:22] Creating opsi base (SQL.py|298)
[2] [Feb 27 16:05:22] Traceback: (Logger.py|710)
[2] [Feb 27 16:05:22]      line 2772 in '<module>' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 2734 in 'main' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 2344 in 'initializeBackends' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in 'backend_createBase' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 393 in '_executeMethod' in file '/var/lib/python-support/python2.5/OPSI/Backend/Backend.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in '<module>' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in 'backend_createBase' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 393 in '_executeMethod' in file '/var/lib/python-support/python2.5/OPSI/Backend/Backend.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in '<module>' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in 'backend_createBase' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 393 in '_executeMethod' in file '/var/lib/python-support/python2.5/OPSI/Backend/Backend.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in '<module>' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in 'backend_createBase' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 309 in '_dispatchMethod' in file '/var/lib/python-support/python2.5/OPSI/Backend/BackendManager.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 1 in '<module>' in file '<string>' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 328 in 'backend_createBase' in file '/var/lib/python-support/python2.5/OPSI/Backend/SQL.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 337 in 'execute' in file '/var/lib/python-support/python2.5/OPSI/Backend/MySQL.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 166 in 'execute' in file '/var/lib/python-support/python2.5/MySQLdb/cursors.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      line 35 in 'defaulterrorhandler' in file '/var/lib/python-support/python2.5/MySQLdb/connections.py' (Logger.py|710)
[2] [Feb 27 16:05:22]      ==>>> (1050, "Table 'host' already exists") (opsi-setup|2780)

ERROR: (1050, "Table 'host' already exists")

Rechte setzen und Neustart der Dienste verliefen dann ohne Probleme.

Dieser letzte Fehler hat aber bisher offensichtlich keine Auswirkungen auf die Funktion. Soweit ich bisher sehe werden die Inventarisierungsdaten in der Datenbank gespeichert und auch abgerufen. Clients erhalten ihre IPs und können neu aufgesetzt werden.

[pk-its]

Ich erhalte bei der Back-End-Konfiguration (ebenfalls dedizierter MySQL-Server) folgende Fehlermeldung:

lnx-opsi:/usr/bin # opsi-setup --configure-mysql
[2] [Oct 03 23:00:49] Traceback: (Logger.py|742)
[2] [Oct 03 23:00:49] line 3068 in '<module>' in file '/usr/bin/opsi-setup' (Logger.py|742)
[2] [Oct 03 23:00:49] line 3031 in 'main' in file '/usr/bin/opsi-setup' (Logger.py|742)
[2] [Oct 03 23:00:49] line 2112 in 'configureMySQLBackend' in file '/usr/bin/opsi-setup' (Logger.py|742)
[2] [Oct 03 23:00:49] line 2100 in 'createUser' in file '/usr/bin/opsi-setup' (Logger.py|742)
[2] [Oct 03 23:00:49] ==>>> (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-opsi.subdomain.domain.ch IDENTIFIED BY 'password'' at line 1") (opsi-setup|3076)

ERROR: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-opsi.subdomain.domain.ch IDENTIFIED BY 'password'' at line 1")

Wenn ich den Skript wie folgt anpasse (Zeile 2099; OPSI 4.0.1), funktioniert es problemlos:
Gepatch
db.query(u'GRANT ALL ON %s .* TO \'%s\'@\'%s\' IDENTIFIED BY \'%s\'' \
% (config['database'], config['username'], host, config['password']));
Original
db.query(u'GRANT ALL ON %s .* TO %s@%s IDENTIFIED BY \'%s\'' \
% (config['database'], config['username'], host, config['password']));

Kann das sonst noch jemand verifizieren?

Thanks
Pasci

[pk-its]

Wohl zu früh gefreut... der anschliessende "opsi-setup --init-current-config" schlägt nun fehl (obwohl die MySQL-Config zuvor geprüft und akzeptiert wurde). Schätze, da wären noch weitere Korrekturen notwendig?

[6] [Oct 03 23:23:10] Creating ConnectionPool instance (MySQL.py|68)
[2] [Oct 03 23:23:10] Traceback: (Logger.py|742)
[2] [Oct 03 23:23:10] line 165 in '_createConnectionPool' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/MySQL.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 78 in '__init__' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/MySQL.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 182 in 'connect' in file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 369 in '__init__' in file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 213 in 'get' in file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 732 in 'do_get' in file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 147 in 'create_connection' in file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 253 in '__init__' in file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 319 in '__connect' in file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 76 in 'creator' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/MySQL.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 71 in 'Connect' in file '/usr/lib64/python2.6/site-packages/MySQLdb/__init__.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 170 in '__init__' in file '/usr/lib64/python2.6/site-packages/MySQLdb/connections.py' (Logger.py|742)
[2] [Oct 03 23:23:10] ==>>> (2003, "Can't connect to MySQL server on 'db-server' (111)") (MySQL.py|168)
[2] [Oct 03 23:23:10] Traceback: (Logger.py|742)
[2] [Oct 03 23:23:10] line 3068 in '<module>' in file '/usr/bin/opsi-setup' (Logger.py|742)
[2] [Oct 03 23:23:10] line 3027 in 'main' in file '/usr/bin/opsi-setup' (Logger.py|742)
[2] [Oct 03 23:23:10] line 2441 in 'initializeBackends' in file '/usr/bin/opsi-setup' (Logger.py|742)
[2] [Oct 03 23:23:10] line 140 in '__init__' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/BackendManager.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 224 in '__init__' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/BackendManager.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 275 in '__loadBackends' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/BackendManager.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 378 in '__init__' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/MySQL.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 142 in '__init__' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/MySQL.py' (Logger.py|742)
[2] [Oct 03 23:23:10] line 169 in '_createConnectionPool' in file '/usr/lib/python2.6/site-packages/OPSI/Backend/MySQL.py' (Logger.py|742)
[2] [Oct 03 23:23:10] ==>>> Backend I/O error: Failed to connect to database 'opsi' address 'db-server': (2003, "Can't connect to MySQL server on 'db-server' (111)") (opsi-setup|3076)

Der Datenbank-Server-Name ist nur als Hostname definiert (nicht vollqualifiziert).

Ideen?

*** hat sich erledigt ***

Konfigurationsfehler meinerseits - vom vorherigen Problem her hatte ich einen hosts-Eintrag für db-server erstellt. Sobald dieser auskommentiert war, hat's funktioniert.

[jakob42]

Code: Alles auswählen

ostrya:~# opsi-setup --configure-mysql
[2] [Feb 27 15:58:32] Traceback: (Logger.py|710)
[2] [Feb 27 15:58:32]      line 2772 in '<module>' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 15:58:32]      line 2738 in 'main' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 15:58:32]      line 2015 in 'configureMySQLBackend' in file '/usr/bin/opsi-setup' (Logger.py|710)
[2] [Feb 27 15:58:32]      ==>>> (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-berlin.de IDENTIFIED BY '********************'' at line 1") (opsi-setup|2780)

ERROR: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-berlin.de IDENTIFIED BY ''********************'' '' at line 1")

Ich habe gerade das erste mal opsi auf einem Ubuntu 10.04 LTS installiert (4.0.1) und bin auf den selben Fehler gestoßen. Wenn man in /usr/bin/opsi-setup die Zeile (rund um 2099)

Code: Alles auswählen

                                 db.query(u'GRANT ALL ON %s .* TO %s@%s IDENTIFIED BY \'%s\'' \
                                         % (config['database'], config['username'], host, config['password']));

hierdurch ersetzt

Code: Alles auswählen

                                 db.query(u'GRANT ALL ON %s .* TO \'%s\'@\'%s\' IDENTIFIED BY \'%s\'' \
                                         % (config['database'], config['username'], host, config['password']));

sollte das funktionieren. Hat es zumindest für uns.

Grüße, HTH