Upgrading issue from 3.1.8 to 3.2.6

[eyucks]

Hello all,

I would like to had an advise because im running otrs 3.1.8 with no issues.
I would like to upgrade to the latest version of otrs. Can I upgrade to 3.1.15 -> 3.2.1 -> 3.2.6 ?

I tried in my lab there an issues when 3.1.5 to 3.2.1. i unable to login using same credential anymore.

I'm sorry this is my first time upgrading OTRS. I just follow in here http://doc.otrs.org/3.1/en/html/upgrading.html.

Note : SLES 11 Release 11 x86_64
MYSQL 5.0.96
ITSM 3.1

Thanks

Regards,
Ashraf

[jojo]

go directly from 3.1.8 to 3.2.6

[eyucks]

go directly from 3.1.8 to 3.2.6

I take this note from http://doc.otrs.org/3.1/en/html/upgrading.html

Within a single minor version you can skip patch level releases if you want to upgrade. For instance you can upgrade directly from OTRS 3.1.1 to version 3.1.4. If you need to do such a "patch level upgrade", you should skip steps 9, 11 and 13-19.

Please note that for upgrades from 3.1.beta1 or 3.1.beta2, an additional step 21 is needed!

So i just follow what it said and not to skip steps 9, 11 and 13-19 ?

[jojo]

yes

[eyucks]

yes

Thank you.

how about these issue ?

Trying to connect to database
DSN: DBI:mysql:database=otrs;host=localhost;
DatabaseUser: otrs

Connected.
Your storage engine is MyISAM.
These tables use a different storage engine:

dynamic_field
dynamic_field_value
gi_debugger_entry
gi_debugger_entry_content
gi_object_lock_state
gi_webservice_config
gi_webservice_config_history
scheduler_task_list
smime_signer_cert_relations
survey
survey_answer
survey_question
survey_queue
survey_request
survey_vote

*** Please correct these problems! ***

[crythias]

Wait a sec. 3.1.x to 3.2.x must follow all steps except for 3.2 beta
Only one version upgrade, but all UPGRADING steps.

[inwiwo]

yes

Thank you.

how about these issue ?

Trying to connect to database
DSN: DBI:mysql:database=otrs;host=localhost;
DatabaseUser: otrs

Connected.
Your storage engine is MyISAM.
These tables use a different storage engine:

dynamic_field
dynamic_field_value
gi_debugger_entry
gi_debugger_entry_content
gi_object_lock_state
gi_webservice_config
gi_webservice_config_history
scheduler_task_list
smime_signer_cert_relations
survey
survey_answer
survey_question
survey_queue
survey_request
survey_vote

*** Please correct these problems! ***

Have a look into my thread from yesterday viewtopic.php?f=63&t=20291. The upgrade doc doesn't mention the script "bin/otrs.MySQLInnoDBSwitch.pl" which you need to execute in order to convert the database tables to InnoDB.

HTH

Edit: I just read your posts again. You're using MySQL 5.0 with MyISAM. So your problem is different to mine.

[crythias]

You're using MySQL 5.0 with MyISAM.

Which means he's going to need to switch to INNODB.

[eyucks]

You're using MySQL 5.0 with MyISAM.

Which means he's going to need to switch to INNODB.

I heard that INNODB slower than MyISAM. is there any solution if i dont wnat to switch to INNODB?
Thank you.

[crythias]

You won't notice a difference.
Option is don't upgrade, upgrade with innodb, or change to a different database.

[eyucks]

You're using MySQL 5.0 with MyISAM.

Which means he's going to need to switch to INNODB.

I heard that INNODB slower than MyISAM. is there any solution if i dont wnat to switch to INNODB?
Thank you.

Can I stick with MyISAM? and what should i do to solve this. Sorry noobies here.

[crythias]

Can I stick with MyISAM?

Not if you wish a successful upgrade.

is there any solution if i dont wnat to switch to INNODB?

Don't upgrade.

[GrishmaD1]

I am currently using 3.1.18 Version of OTRS and need to upgrade it to 3.2 version but somehow the database of 3.1.x is NOT compatible with 3.2.x versions kindly help me with this if i need to make any changes to database.

[reneeb]

Read the UPGRADING file (https://github.com/OTRS/otrs/blob/rel-3_2/UPGRADING.md) -- especially item 9 (the database stuff).

[eyucks]

Thanks.. but I dont know how to change to InnoDB. Is there any steps I need to foollow?

[Giulio Soleni]

Thanks.. but I dont know how to change to InnoDB. Is there any steps I need to foollow?

Hallo,
please, consider that I am still testing this procedure, therefore I am not sure if it could have some side effects... I do not take any responsibility ... bla, bla, bla

Saying that, to manually switch the engine from my MyISAM to InnoDB you should:
- enter the mysql console (it will ask for the password of root db user).
mysql -h localhost -u root -p

- First of all check if both InnoDB and MyISAM engines are supported by your current version of MySQL db, to do so run the following:
SHOW ENGINES;

In my system (now) I have the following results, for you the two lines related to MyISAM and InnoDB might be inverted; the important thing is that both engine are supported:
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

- check if there is any FULLTEXT index in otrs schema (there should not be any...) since that indexes cannot be imported and have to be deleted.
SELECT index_name FROM information_Schema.STATISTICS WHERE table_schema = 'otrs' AND index_type = 'FULLTEXT' ORDER BY index_name;

- create the sql script /tmp/change_engine.sql (I use /tmp/ folder 'cos I am sure that any user should be able to read and write there).
SELECT concat('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'otrs' AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE' INTO OUTFILE '/tmp/change_engine.sql';

- once you have performed the query above, please check if the file /tmp/change_engine.sql is actually there and it is not empty.

- switch to otrs db and call the /tmp/change_engine.sql script just defined.
use otrs
source /tmp/change_engine.sql
quit

- edit the /etc/my.cnf file (actually in MySQL 5.6 version the file may be present under some other folders...) adding or modifying the following line:

default-storage-engine=InnoDB

- restart mysqld (or 'mysql' for 5.6 version):
service mysqld restart

And that's it (hopefully)

You might want to check the status of the tables of your db again with the same query of step 1.:
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'otrs';

I think that the same procedure could be used to switch back from InnoDB to MyISAM but I am not sure that all these changes may cause possible issues. In term of disk space, for example, afaik InnoDB is more expensive and it manages indexes in a very different way than MyISAM ... anyhow I am not a dba, so I cannot tell all the pro and cons that this change may have.

HTH
Giulio

[crythias]

There's also bin/otrs.MySQLInnoDBSwitch.pl

[Giulio Soleni]

mmh, I gave a look to bin/otrs.MySQLInnoDBSwitch.pl that I didn't see before and I noticed that before changing the engine it performs a:
SET foreign_key_checks = 0
I think it's ok if it is a temporary setting... but it seems it is not set back to foreign_key_checks = 1 at the end of the process.
Does it mean that referential integrity checks are left disabled after the migration of the engine?

EDIT: That's ok, it is not necessary to set foreign_key_checks back to 1 since it is a session variable. You may check its current value with SELECT @@FOREIGN_KEY_CHECKS;
So... yes, bin/otrs.MySQLInnoDBSwitch.pl can actually do the job

[eyucks]

Hi Giulio and all,

Thanks a lot for your help. I'm able to change from MyISAM to INNOdb as shown below.

Code: Select all

mysql> SHOW ENGINES;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.01 sec)

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'otrs';
+------------------------------+--------+
| TABLE_NAME                   | ENGINE |
+------------------------------+--------+
| article                      | InnoDB |
| article_attachment           | InnoDB |
| article_flag                 | InnoDB |
| article_plain                | InnoDB |
| article_search               | InnoDB |
| article_sender_type          | InnoDB |
| article_type                 | InnoDB |
| auto_response                | InnoDB |
| auto_response_type           | InnoDB |
| change_cab                   | InnoDB |
| change_cip_allocate          | InnoDB |
| change_condition             | InnoDB |
| change_freekey               | InnoDB |
| change_freetext              | InnoDB |
| change_history               | InnoDB |
| change_history_type          | InnoDB |
| change_item                  | InnoDB |
| change_notification          | InnoDB |
| change_notification_grps     | InnoDB |
| change_notification_rec      | InnoDB |
| change_state_machine         | InnoDB |
| change_template              | InnoDB |
| change_template_type         | InnoDB |
| change_wo_freekey            | InnoDB |
| change_wo_freetext           | InnoDB |
| change_workorder             | InnoDB |
| cip_allocate                 | InnoDB |
| condition_action             | InnoDB |
| condition_attribute          | InnoDB |
| condition_expression         | InnoDB |
| condition_object             | InnoDB |
| condition_operator           | InnoDB |
| configitem                   | InnoDB |
| configitem_counter           | InnoDB |
| configitem_definition        | InnoDB |
| configitem_history           | InnoDB |
| configitem_history_type      | InnoDB |
| configitem_version           | InnoDB |
| customer_company             | InnoDB |
| customer_preferences         | InnoDB |
| customer_user                | InnoDB |
| dynamic_field                | InnoDB |
| dynamic_field_value          | InnoDB |
| faq_attachment               | InnoDB |
| faq_category                 | InnoDB |
| faq_category_group           | InnoDB |
| faq_history                  | InnoDB |
| faq_item                     | InnoDB |
| faq_language                 | InnoDB |
| faq_log                      | InnoDB |
| faq_state                    | InnoDB |
| faq_state_type               | InnoDB |
| faq_voting                   | InnoDB |
| follow_up_possible           | InnoDB |
| general_catalog              | InnoDB |
| general_catalog_preferences  | InnoDB |
| generic_agent_jobs           | InnoDB |
| gi_debugger_entry            | InnoDB |
| gi_debugger_entry_content    | InnoDB |
| gi_object_lock_state         | InnoDB |
| gi_webservice_config         | InnoDB |
| gi_webservice_config_history | InnoDB |
| group_customer_user          | InnoDB |
| group_role                   | InnoDB |
| group_user                   | InnoDB |
| groups                       | InnoDB |
| imexport_format              | InnoDB |
| imexport_mapping             | InnoDB |
| imexport_mapping_format      | InnoDB |
| imexport_mapping_object      | InnoDB |
| imexport_object              | InnoDB |
| imexport_search              | InnoDB |
| imexport_template            | InnoDB |
| link_object                  | InnoDB |
| link_relation                | InnoDB |
| link_state                   | InnoDB |
| link_type                    | InnoDB |
| mail_account                 | InnoDB |
| notification_event           | InnoDB |
| notification_event_item      | InnoDB |
| notifications                | InnoDB |
| package_repository           | InnoDB |
| personal_queues              | InnoDB |
| postmaster_filter            | InnoDB |
| process_id                   | InnoDB |
| queue                        | InnoDB |
| queue_auto_response          | InnoDB |
| queue_preferences            | InnoDB |
| queue_standard_response      | InnoDB |
| role_user                    | InnoDB |
| roles                        | InnoDB |
| salutation                   | InnoDB |
| scheduler_task_list          | InnoDB |
| search_profile               | InnoDB |
| service                      | InnoDB |
| service_customer_user        | InnoDB |
| service_preferences          | InnoDB |
| service_sla                  | InnoDB |
| sessions                     | InnoDB |
| signature                    | InnoDB |
| sla                          | InnoDB |
| sla_preferences              | InnoDB |
| smime_signer_cert_relations  | InnoDB |
| standard_attachment          | InnoDB |
| standard_response            | InnoDB |
| standard_response_attachment | InnoDB |
| support_bench_test           | InnoDB |
| survey                       | InnoDB |
| survey_answer                | InnoDB |
| survey_question              | InnoDB |
| survey_queue                 | InnoDB |
| survey_request               | InnoDB |
| survey_vote                  | InnoDB |
| system_address               | InnoDB |
| ticket                       | InnoDB |
| ticket_flag                  | InnoDB |
| ticket_history               | InnoDB |
| ticket_history_type          | InnoDB |
| ticket_index                 | InnoDB |
| ticket_lock_index            | InnoDB |
| ticket_lock_type             | InnoDB |
| ticket_loop_protection       | InnoDB |
| ticket_priority              | InnoDB |
| ticket_state                 | InnoDB |
| ticket_state_type            | InnoDB |
| ticket_type                  | InnoDB |
| ticket_watcher               | InnoDB |
| time_accounting              | InnoDB |
| time_accounting_action       | InnoDB |
| time_accounting_project      | InnoDB |
| time_accounting_table        | InnoDB |
| time_accounting_user         | InnoDB |
| time_accounting_user_period  | InnoDB |
| user_preferences             | InnoDB |
| users                        | InnoDB |
| valid                        | InnoDB |
| virtual_fs                   | InnoDB |
| virtual_fs_db                | InnoDB |
| virtual_fs_preferences       | InnoDB |
| web_upload_cache             | InnoDB |
| xml_storage                  | InnoDB |
+------------------------------+--------+
141 rows in set (0.07 sec)

Now I will try to upgrade to 3.2 then 3.3 . Will update if got any issues.

[eyucks]

Update:

having an issue when login.
error : Session invalid. Please log in again.

upgrade version from 3.1.8 to 3.2.13.

Any idea? saw thread said to add below script into Config.pm.

$Self->{SessionModule} = 'Kernel::System::AuthSession::FS';

is it possible ?

Thanks in advance.

[Giulio Soleni]

Hi,

As far as I know $Self->{SessionModule} = 'Kernel::System::AuthSession::FS'; can move the session management from the database to the filesystem, but I do not know if it can solve your issue... there could probably be something in the db that has not been correctly ported from the previous version of the db to the current one.
Did you read this thread?: viewtopic.php?f=35&t=23222&p=91468&hili ... lid#p91468
It is suggested to drop and recreate the sessionstable.

However, before taking actions directly on the DB I would suggest you to perform the following restart procedure, and see if it is enough to reset the system:

service otrs stop
/opt/otrs/bin/otrs.Scheduler.pl -a stop --force ...this, should not be needed
service crond stop
service httpd stop ... or the corresponding apache deamon in SUSE, I do not remember the name.
cd /opt/otrs/bin
./otrs.RebuildConfig.pl
./otrs.LoaderCache.pl -o delete
./otrs.DeleteCache.pl
# at this point it could also be useful to use ./otrs.SetPermissions.pl to reset the permissions of all files in the /opt/otrs folders, however I am not sure about the correct parameters that should be used with SUSE Linux distro.
service httpd start
service crond start
service otrs start

[SysAdmin12]

Hello,

I'm trying to upgrade from our 3.2.9 to 3.3.9.

I set up a replica of the OTRS in a VM with CentOS 7 and Mysql 5.6.21

I tried the change_engine.sql script mentioned by Giulio and it worked fine except for the following tables

article
article_attachment
article_plain

where I had the following error

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.


and these tables

virtual_fs
virtual_fs_db

both with error

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


I have searched the internet but it seems that nobody had the same problem during the upgrade to InnoDB and I don't know how to solve it since I'm not a SQL user.

Does anybody know how to correct this so I can move on to the next steps of the upgrade?

Thanks in advance.

[Giulio Soleni]

article
article_attachment
article_plain

where I had the following error

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.


and these tables

virtual_fs
virtual_fs_db

both with error

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

I am quite sure that these are the tables in the db where articles and their contents (along with attachments) are stored by default.
I strongly recommend my customers to set the
Ticket -> Core::Ticket Ticket::StorageModule parameter to ArticleStorageFS instead of ArticleStorageDB just to avoid issues of that kind.

Unfortunately I am not sure you may turn your configuration from DB to Filesystem at the present condition... but this is actually the only suggestion that I think I may give you: don't use DB for articles, use Filesystem.

Hope this can solve your problem.

[SysAdmin12]

Hello Giulio,

Thanks for the information and the advice. I'll keep it for future reference.

Actually, I decided not to use MySQL after all and changed to MariaDB: After running your script again, those errors didn't appear and the transition to InnoDB worked like a charm.

Thanks for your help.