Problem bei Update DB 3.1->3.2

[Skynetworksde]

Hallo zusammen,

wir setzen momentan noch OTRS 2.4.5 ein und möchten nun mal auf 3.3.8 upgraden.
Klar ist, daß die Datenbank in den Zwischenschritten 2.4 - 3.0 - 3.1 - 3.2 - 3.3 aktualisiert werden muss.
Von 2.4 auf 3.0 und 3.0 auf 3.1 funktioniert das auch problemlos. Nur beim Update von 3.1 auf 3.2 gibt es Probleme beim Ausführen des .pl scripts (wurde wie angewiesen als otrs-user ausgeführt):

$ ./DBUpdate-to-3.2.pl

Migration started...

Step 1 of 10: Refresh configuration cache...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAAuto.pm line 7.
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.

Step 2 of 10: Check framework version... done.

Step 3 of 10: Cleanup UserPreferences... done.

Step 4 of 10: Updating toolbar configuration... done.

Step 5 of 10: Updating AgentTicketZoom window configuration... done.

Step 6 of 10: Dropping obsolete columns from article_search... Check if columns exist.
Columns found, drop them.
ALTER TABLE article_search DROP a_freetext1
ALTER TABLE article_search DROP a_freetext2
ALTER TABLE article_search DROP a_freetext3
ALTER TABLE article_search DROP a_freekey1
ALTER TABLE article_search DROP a_freekey2
ALTER TABLE article_search DROP a_freekey3
done.

Step 7 of 10: Dropping unused indexes on article_flag... SET @FKExists := (SELECT COUNT(*) FROM information_schema.table_constraints WHERE table_schema = DATABASE() AND table_name = 'article_flag' AND constraint_name = 'FK_article_flag_article_id_id')
OK
SET @FKSQLStatement := IF( @FKExists > 0, 'ALTER TABLE article_flag DROP FOREIGN KEY FK_article_flag_article_id_id', 'SELECT ''INFO: Foreign key constraint FK_article_flag_article_id_id does not exist, skipping.''' )
OK
PREPARE FKStatement FROM @FKSQLStatement
OK
EXECUTE FKStatement
OK
SET @FKExists := (SELECT COUNT(*) FROM information_schema.table_constraints WHERE table_schema = DATABASE() AND table_name = 'article_flag' AND constraint_name = 'FK_article_flag_create_by_id')
OK
SET @FKSQLStatement := IF( @FKExists > 0, 'ALTER TABLE article_flag DROP FOREIGN KEY FK_article_flag_create_by_id', 'SELECT ''INFO: Foreign key constraint FK_article_flag_create_by_id does not exist, skipping.''' )
OK
PREPARE FKStatement FROM @FKSQLStatement
OK
EXECUTE FKStatement
OK
DROP INDEX article_flag_create_by ON article_flag
DBD::mysql::db do failed: Can't DROP 'article_flag_create_by'; check that column/key exists at /opt/otrs/Kernel/System/DB.pm line 476.
ERROR: OTRS-DBUpdate-to-3.2-10 Perl: 5.14.2 OS: linux Time: Thu Aug 14 15:24:07 2014

Message: Can't DROP 'article_flag_create_by'; check that column/key exists, SQL: 'DROP INDEX article_flag_create_by ON article_flag'

Traceback (3321):
Module: main::_DropArticleFlagIndexes (unknown version) Line: 486
Module: ./DBUpdate-to-3.2.pl (unknown version) Line: 107

Index was not present
DROP INDEX article_flag_article_id_article_key ON article_flag
OK
ALTER TABLE article_flag ADD CONSTRAINT FK_article_flag_article_id_id FOREIGN KEY (article_id) REFERENCES article (id)
OK
ALTER TABLE article_flag ADD CONSTRAINT FK_article_flag_create_by_id FOREIGN KEY (create_by) REFERENCES users (id)
OK
Step 8 of 10: Migration cache backend configuration... done.

Step 9 of 10: Clean up the cache... done.

Step 10 of 10: Refresh configuration cache another time...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAAuto.pm line 7.
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.

Migration completed!


Auf dem OTRS Server ist Debian 7.6 im Einsatz, auf dem DB Server Debian 5.0.7 mit mysql-server 5.0.51a-24. Datenbank-Kollation ist utf8_general_ci, und Typ MyISAM, Datenbankgröße liegt momentan bei ca. 5.8 GB.
Wo könnte denn der Fehler liegen?

Danke & Grüße
Thomas

[wurzel]

Hi,

wo siehst Du einen Fehler?

Weil was gelöscht werden soll was nicht da ist?

Ach und Du solltest nach InnoDB wechseln.

Flo

[Skynetworksde]

Hi,

ok, das mag so noch ok sein. Fand ich erstmal ungewöhnlich, da die Skripte im Normalfall eigentlich ohne weitere Ausgabe durchlaufen (außer "Subroutine Load redefined" - aber das wird ja explizit auch als unkritisch genannt).
Aber es geht weiter, das Update von 3.2 auf 3.3 läuft dann richtig schief:


root@otrs-lcr:/opt/otrs# cat scripts/DBUpdate-to-3.3.mysql.sql | mysql -p -f -h 192.168.99.1 -u otrstest otrs-test2
Enter password:
INFO: Foreign key constraint FK_standard_response_valid_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_valid_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_queue_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_queue_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_attachment_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_attachment_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_change_by_id does not exist, skipping.

Und beim Migrationsskript:
$ ./DBUpdate-to-3.3.pl

Migration started...

Step 1 of 13: Refresh configuration cache...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
[Fri Aug 15 13:08:19 2014] DBUpdate-to-3.3.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAAuto.pm line 7.
[Fri Aug 15 13:08:19 2014] DBUpdate-to-3.3.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.

Step 2 of 13: Check framework version... done.

Step 3 of 13: Generate MessageID md5sums... [Fri Aug 15 13:08:19 2014] DBUpdate-to-3.3.pl: DBD::mysql::db do failed: Unknown column 'a_message_id_md5' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 499.
ERROR: OTRS-DBUpdate-to-3.3-10 Perl: 5.14.2 OS: linux Time: Fri Aug 15 13:08:19 2014

Message: Unknown column 'a_message_id_md5' in 'field list', SQL: '
UPDATE article
SET a_message_id_md5 = MD5(a_message_id)
WHERE a_message_id IS NOT NULL
'

Traceback (3849):
Module: main::_GenerateMessageIDMD5 (unknown version) Line: 582
Module: ./DBUpdate-to-3.3.pl (unknown version) Line: 99

done.

Step 4 of 13: Migrate old settings... done.

Step 5 of 13: Migrate OTRSExternalTicketNumberRecognition... done.

Step 6 of 13: Checking Standard Template table columns...
Check if 'template_type' columns exists.
'template_type' column not found, create it.
ALTER TABLE standard_template ADD template_type VARCHAR (100) NULL
[Fri Aug 15 13:08:20 2014] DBUpdate-to-3.3.pl: DBD::mysql::db do failed: Table 'otrs.standard_template' doesn't exist at /opt/otrs/Kernel/System/DB.pm line 499.
ERROR: OTRS-DBUpdate-to-3.3-10 Perl: 5.14.2 OS: linux Time: Fri Aug 15 13:08:20 2014

Message: Table 'otrs.standard_template' doesn't exist, SQL: 'ALTER TABLE standard_template ADD template_type VARCHAR (100) NULL'

Traceback (3849):
Module: main::_AddTemplateTypeColumn (unknown version) Line: 478
Module: ./DBUpdate-to-3.3.pl (unknown version) Line: 126

ERROR: OTRS-DBUpdate-to-3.3-10 Perl: 5.14.2 OS: linux Time: Fri Aug 15 13:08:20 2014

Message: Error during execution of 'ALTER TABLE standard_template ADD template_type VARCHAR (100) NULL'!

Traceback (3849):
Module: main::_AddTemplateTypeColumn (unknown version) Line: 480
Module: ./DBUpdate-to-3.3.pl (unknown version) Line: 126

[Fri Aug 15 13:08:20 2014] DBUpdate-to-3.3.pl: Died at ./DBUpdate-to-3.3.pl line 126.
$

Irgendwas läuft da total schief

Viele Grüße
Thomas

[wurzel]

Hi,

hast Du auf InnoDB gewechselt? http://otrs.github.io/doc/manual/admin/ ... ading.html
Punkt 9

Flo