Errors while upgrading from 3.0.11 to 3.1.5/6

[yuri0001]

Hi!
Colleagues, help me understand!
When upgrade from 3.0.11 to 3.1.5 posts issued

Code: Select all

Warning !!!!!
warning: /opt/otrs/Kernel/Config.pm created as /opt/otrs/Kernel/Config.pm.rpmnew
warning: /opt/otrs/Kernel/Config/GenericAgent.pm created as /opt/otrs/Kernel/Config/GenericAgent.pm.rpmnew
warning: /opt/otrs/Kernel/Language/ar_SA.pm created as /opt/otrs/Kernel/Language/ar_SA.pm.rpmnew
warning: /opt/otrs/Kernel/Language/ru.pm created as /opt/otrs/Kernel/Language/ru.pm.rpmnew

warning: /opt/otrs/Kernel/Output/HTML/Standard/AAABase.dtl created as /opt/otrs/Kernel/Output/HTML/Standard/AAABase.dtl.rpmnew
.................
.................

backup old (maybe not compatible) templates (of 3.0.11)
`/opt/otrs/Kernel/Output/HTML/Standard/AAABase.dtl' -> `/opt/otrs/Kernel/Output/HTML/Standard/AAABase.dtl.backup_maybe_not_compat_to.3.0.11'
`/opt/otrs/Kernel/Output/HTML/Standard/AAAPreferences.dtl' -> `/opt/otrs/Kernel/Output/HTML/Standard/AAAPreferences.dtl.backup_maybe_not_compat_to.3.0.11'
`/opt/otrs/Kernel/Output/HTML/Standard/AAATicket.dtl' -> `/opt/otrs/Kernel/Output/HTML/Standard/AAATicket.dtl.backup_maybe_not_compat_to.3.0.11'
`/opt/otrs/Kernel/Output/HTML/Standard/AdminAttachment.dtl' -> `/opt/otrs/Kernel/Output/HTML/Standard/AdminAttachment.dtl.backup_maybe_not_compat_to.3.0.11'
`/opt/otrs/Kernel/Output/HTML/Standard/AdminAutoResponse.dtl' -> `/opt/otrs/Kernel/Output/HTML/Standard/AdminAutoResponse.dtl.backup_maybe_not_compat_to.3.0.11'

In what may be the problem?
Or is this standard and we need by hands to rename all the modules? : shock:
It's not all messages are many, but here are typical representatives.
Hands in one of those modules have not changed anything.
And yet, just admins passed:
Run cat scripts/DBUpdate-to-3.1.mysql.sql | mysql -p -f -u root otrs
we get messages

Code: Select all

ERROR 1005 (HY000) at line 225: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 226: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 227: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 229: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 230: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 234: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 235: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 237: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 238: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 239: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)

Once the admin install by hands, but then lost the structure of the FAQ and all its articles.
Missing CMDB data , except those that appear in the system after a clean install..
Help please!

[CSL]

I'd like to help, but haven't yet attempted this upgrade (it's coming though, probably soon...)

The messages in your first quote look normal. I got these when testing RPM upgrades in previous versions. If it finds a conflict with an existing file it creates the new one as "file".rpmnew

This is the main reason I don't use RPM upgrades for OTRS and don't ever plan to. It's like a bull running through the china-shop of my carefully crafted custom dtl files, perl modues, themes, skins, etc. While nothing is removed or lost, you are almost certain to get a broken system at the end of it if you have any kind of complicated customizations, and are left trawling through the RPM output to see where it has stuck your customized files, which it has renamed, which it hasn't, etc.

The database errors look potentially more serious. I would guess (guess!) that sql-157c_5c6 is a temporary table it is trying to create, and for some reason failing. I've not idea why - you would have look into the upgrade script at the lines given in the errors to see what it is trying to do.

[yuri0001]

Thanks!
One question - what method you recommended for upgrade and not drop anything of previous customization?

[yuri0001]

Hi!
I'll find in BugReports description of this error & workarround for it - http://bugs.otrs.org/show_bug.cgi?id=8528, but for Debian. We used Suse 12.
It's right for MySQL 5.5.x

[CSL]

I've found upgrading to be complex and time-consuming because we now have a fair number of customizations. If you had a plan, vanilla install of OTRS that you only changed sysconfig settings on, then it would be fast and stress free, but I've found this isn't possible when a buisiness is using it full time and wants its features customized.

First thing is to manage where your customized files are. Any code changes to perl modules must go inside a copy of the file inside the /opt/otrs/Custom/ directory. I try to keep code changes to the absolute minimum, as these can be a nightmare to migrate across if there are large changes in the new version.

Second is themes and skins. These have to be fully-fledged themes / skins inside their own folders (such as /opt/otrs/Kernel/Output/HTML/yourthemename and /opt/otrs/var/httpd/htdocs/skins/Agent/yourskinname )

Before upgrading, if you want to do things completely right, then you'll have to check all of your customized .dtl files and perl modules against the new versions to see if they've changed anything in the newer version. If they have, you'll have to copy your customizations into the new file. This ensures that nothing will break from having older versions of the files put into the new install.


To perform an upgrade, I first make a list of all of my customized files, and then copy them into a duplicate directory structure. So if I had customized /opt/otrs/Kernel/Config.pm, then I'd create ~/otrs/Kernel/ and copy in Config.pm. This is tedious, but is guaranteed to collect all of your customizations.

I then install the new version alongside the old one (rename old dir to something like otrs30) and go through the upgrade process, updating the DB, apache, and cron if needed. I then drop my duplicate directory over the newly installed directory. This overwrites the new, default files with my customized ones, and should give a working, upgraded system.


Good find for that MySQL 5.5 issue. Looks like you will now need to change the default database engine in 5.5 from innodb to MyISAM to get OTRS to install / upgrade properly.

[yuri0001]

О.К.
Thank you very much for full answer! I do it in same order.

[inwiwo]

Code: Select all

ERROR 1005 (HY000) at line 225: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 226: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 227: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 229: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 230: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 234: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 235: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 237: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 238: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)
ERROR 1005 (HY000) at line 239: Can't create table 'otrs.#sql-157c_5c6' (errno: 150)

I ran into the same problem when upgrading to 3.1.6 while migrating from MySQL 4.x on CentOS 4 to MySQL 5.5. on Ubuntu 12.04. I added "set default_storage_engine = 'MyISAM';" at the beginning of the four files as described in the bugreport, but when I run the script again i still get these error messages.

Code: Select all

/opt/otrs# cat scripts/DBUpdate-to-3.1.mysql.sql | mysql -p -f -u root otrs
Enter password: 
ERROR 1050 (42S01) at line 30: Table 'gi_webservice_config' already exists
ERROR 1050 (42S01) at line 47: Table 'gi_webservice_config_history' already exists
ERROR 1050 (42S01) at line 62: Table 'scheduler_task_list' already exists
ERROR 1050 (42S01) at line 75: Table 'gi_debugger_entry' already exists
ERROR 1050 (42S01) at line 89: Table 'gi_debugger_entry_content' already exists
ERROR 1050 (42S01) at line 103: Table 'gi_object_lock_state' already exists
ERROR 1050 (42S01) at line 171: Table 'smime_signer_cert_relations' already exists
ERROR 1050 (42S01) at line 192: Table 'dynamic_field_value' already exists
ERROR 1050 (42S01) at line 207: Table 'dynamic_field' already exists
ERROR 1005 (HY000) at line 227: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 228: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 229: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 230: Can't create table 'otrs.#sql-1cb2_22b' (errno: 121)
ERROR 1005 (HY000) at line 231: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 232: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 233: Can't create table 'otrs.#sql-1cb2_22b' (errno: 121)
ERROR 1005 (HY000) at line 234: Can't create table 'otrs.#sql-1cb2_22b' (errno: 121)
ERROR 1005 (HY000) at line 235: Can't create table 'otrs.#sql-1cb2_22b' (errno: 121)
ERROR 1005 (HY000) at line 236: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 237: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 238: Can't create table 'otrs.#sql-1cb2_22b' (errno: 121)
ERROR 1005 (HY000) at line 239: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 240: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)
ERROR 1005 (HY000) at line 241: Can't create table 'otrs.#sql-1cb2_22b' (errno: 150)

How can I solve this problem?

For now I have to go back to 3.0.11 ond hope that there will be no other errors. Do I need to add "set default_storage_engine = 'MyISAM';" on the 3.0.11 too when switching to MySQL 5.5?

Thanks

[CSL]

You will need to do more than just upgrade OTRS if you are migrating a database from MySQL version 4.1 to 5.5.

You will first have to upgrade your MySQL install and your database to the version you want. You won't be able to do this in one single step; you will need to upgrade from 4.1 to 5.0, as described here: http://dev.mysql.com/doc/refman/5.0/en/ ... eries.html

And then from 5.0 to 5.5, as described here: http://dev.mysql.com/doc/refman/5.0/en/ ... y-5-0.html

These steps will mostly involve running the MySQL upgrade check on your database: http://dev.mysql.com/doc/refman/5.0/en/ ... grade.html

There are some big changes from version 4.1 to 5.5, and if you just copy / import a 4.1 database into a 5.5 install it almost certainly won't work correctly.

[inwiwo]

Thanks for your reply.

I moved the 3.0.11 installation successfully (as it looks) to the new machine running MySQL 5.5, but I think I'll do a fresh install of 3.1.6 and keep the old system as long as there are open tickets in it.

[hnoor0077]

thanks for everything...

[ChrisRitson]

I realise that this is now an old thread but my solution is a little different. In short, as part of a version upgrade, I imported the existing otrs database to a new mysql (mariadb) instance. The underlying cause of the reported error seems to be that the pre-upgrade script is adding indexes and is attempting to add an index to a (default) InnoDB table which refers to an imported MyISAM table.

Rather than postpone the problem by making all tables MyISAM as suggested, I chose to alter the engine for all existing tables to be InnoDB. I'd not be happy writing an automatic script, but my approach in general was:-

- Use mysqldump to extract existing schemata:

Code: Select all

mysqldump -u root -p -h localhost --no-data otrs >schemas2.sql

- Edit this or use sed to select all table names and engine specifications. Delete all that are not MyISAM. Use global substitution to generate suitable MySQL alter statements that look like this:

Code: Select all

ALTER TABLE `article` ENGINE=InnoDB;

In my case there were 83 of them. Adding an initial

Code: Select all

use otrs;

allowed me to update all the tables.

- Proceed with the remaining upgrade steps.

Chris Ritson.