OTRS 4 to 5 migration

[mathieu2]

I am upgrading otrs4 to otrs5 and i have an issue with the database migration step 6.2: https://otrs.github.io/doc/manual/admin ... ading.html


root@otrs[15:38]:su -c "/opt/otrs/scripts/DBUpdate-to-5.pl" -s /bin/bash otrs

Migration started...

Step 1 of 17: Refresh configuration cache...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
done.

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

Step 3 of 17: Migrate Database Column Types...done.

Step 4 of 17: Migrate charset to UTF-8 on auto_response table...done.

Step 5 of 17: Migrate charset to UTF-8 on notification_event table...done.

Step 6 of 17: Migrate event based notifications to support multiple languages...done.

Step 7 of 17: Migrate notifications to event based notifications...ERROR: OTRS-DBUpdate-to-5.pl-42 Perl: 5.16.3 OS: linux Time: Wed Dec 2 15:38:33 2015

Message: Can't add notification 'Old Ticket new note notification ( Duplicate Name )', notification already exists!

Traceback (17187):
Module: Kernel::System::NotificationEvent::NotificationAdd Line: 271
Module: main::_MigrateNotifications Line: 1109
Module: /opt/otrs//scripts/DBUpdate-to-5.pl Line: 162


Thanks for your help.

[mathieu2]

root@otrs[14:20]:cat /opt/otrs/scripts/DBUpdate-to-5.mysql.sql | mysql -p -f -u root otrs5
Enter password:
ERROR 1061 (42000) at line 4: Duplicate key name 'link_relation_list_source'
ERROR 1061 (42000) at line 5: Duplicate key name 'link_relation_list_target'
ERROR 1050 (42S01) at line 9: Table 'scheduler_task' already exists
ERROR 1050 (42S01) at line 28: Table 'scheduler_future_task' already exists
ERROR 1050 (42S01) at line 47: Table 'scheduler_recurrent_task' already exists
ERROR 1091 (42000) at line 68: Can't DROP 'text2'; check that column/key exists
ERROR 1050 (42S01) at line 72: Table 'notification_event_message' already exists
ERROR 1050 (42S01) at line 87: Table 'cloud_service_config' already exists
ERROR 1005 (HY000) at line 101: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 102: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 103: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 104: Can't create table 'otrs5.#sql-918_f727' (errno: 121)

[jojo]

you're applying the DB Update script on a OTRS5 database.

[mathieu2]

you're applying the DB Update script on a OTRS5 database.

yes my otrs database name is "otrs5" not "otrs"

MariaDB [otrs5]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| otrs5 |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

[mathieu2]

you're applying the DB Update script on a OTRS5 database.

it seems that i have an error with a foreign key constraint ?


MariaDB [(none)]> SHOW ENGINE INNODB STATUS;
...

| InnoDB | |
=====================================
151203 15:05:28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6410 1_second, 6409 sleeps, 634 10_second, 67 background, 67 flush
srv_master_thread log flush and writes: 6667
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 72, signal count 72
Mutex spin waits 3, rounds 90, OS waits 3
RW-shared spins 86, rounds 2580, OS waits 66
RW-excl spins 0, rounds 90, OS waits 3
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 90.00 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
151203 15:01:12 Error in foreign key constraint creation for table `otrs5`.`#sql-918_f752`.
A foreign key constraint of name `otrs5`.`FK_cloud_service_config_valid_id_id`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1879 OS file reads, 2600 OS file writes, 767 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.48 writes/s, 0.59 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 3 buffer(s)
0.11 hash searches/s, 5.15 non-hash searches/s
---
LOG
---
Log sequence number 111668678
Log flushed up to 111668678
Last checkpoint at 111668678
Max checkpoint age 869019772
Checkpoint age target 841862905
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
599 log i/o's done, 0.52 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137756672; in additional pool allocated 0
Total memory allocated by read views 384
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2266736 (2213368 + 53368)
Page hash 139112 (buffer pool 0 only)
Dictionary cache 1725743 (554768 + 1170975)
File system 83536 (82672 + 864)
Lock system 334376 (332872 + 1504)
Recovery system 0 (0 + 0)
Dictionary memory allocated 1170975
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 6261
Database pages 1927
Old database pages 727
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1866, created 61, written 1909
0.00 reads/s, 0.00 creates/s, 0.93 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1927, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
0 transactions active inside InnoDB
0 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 1CBAB
Read view up limit trx id 1CBAB
Read view low limit trx id 1CBAB
Read view individually stored trx ids:
-----------------
Main thread process no. 2328, id 140526940509952, state: sleeping
Number of rows inserted 131, updated 151, deleted 473, read 1837
0.07 inserts/s, 0.22 updates/s, 0.07 deletes/s, 1.07 reads/s
------------
TRANSACTIONS
------------
Trx id counter 1CBAB
Purge done for trx's n:o < 1CB9B undo n:o < 0
History list length 1037
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1CBA5, not started
MySQL thread id 63319, OS thread handle 0x7fcefa9b1700, query id 645787 localhost otrs
---TRANSACTION 0, not started
MySQL thread id 63317, OS thread handle 0x7fcefa968700, query id 645794 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1CB86, not started
MySQL thread id 63296, OS thread handle 0x7fcefa8d6700, query id 645743 localhost otrs
---TRANSACTION 1CBAA, not started
MySQL thread id 63295, OS thread handle 0x7fcefaa43700, query id 645793 localhost otrs
----------------------------
END OF INNODB MONITOR OUTPUT
============================
...

[mathieu2]

anyone ?

[crythias]

Jojo was telling you that you unnecessarily ran the upgrade script on an existing v5 database. not that it was called otrs5, but that it was already v5.

root@otrs[14:20]:cat /opt/otrs/scripts/DBUpdate-to-5.mysql.sql | mysql -p -f -u root otrs5
Enter password:
ERROR 1061 (42000) at line 4: Duplicate key name 'link_relation_list_source'
ERROR 1061 (42000) at line 5: Duplicate key name 'link_relation_list_target'
ERROR 1050 (42S01) at line 9: Table 'scheduler_task' already exists
ERROR 1050 (42S01) at line 28: Table 'scheduler_future_task' already exists
ERROR 1050 (42S01) at line 47: Table 'scheduler_recurrent_task' already exists
ERROR 1091 (42000) at line 68: Can't DROP 'text2'; check that column/key exists
ERROR 1050 (42S01) at line 72: Table 'notification_event_message' already exists
ERROR 1050 (42S01) at line 87: Table 'cloud_service_config' already exists
ERROR 1005 (HY000) at line 101: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 102: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 103: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 104: Can't create table 'otrs5.#sql-918_f727' (errno: 121)

duplicate, already exists = you already did this.


What is the question that you need answered on this thread?

[mathieu2]

Jojo was telling you that you unnecessarily ran the upgrade script on an existing v5 database. not that it was called otrs5, but that it was already v5.

root@otrs[14:20]:cat /opt/otrs/scripts/DBUpdate-to-5.mysql.sql | mysql -p -f -u root otrs5
Enter password:
ERROR 1061 (42000) at line 4: Duplicate key name 'link_relation_list_source'
ERROR 1061 (42000) at line 5: Duplicate key name 'link_relation_list_target'
ERROR 1050 (42S01) at line 9: Table 'scheduler_task' already exists
ERROR 1050 (42S01) at line 28: Table 'scheduler_future_task' already exists
ERROR 1050 (42S01) at line 47: Table 'scheduler_recurrent_task' already exists
ERROR 1091 (42000) at line 68: Can't DROP 'text2'; check that column/key exists
ERROR 1050 (42S01) at line 72: Table 'notification_event_message' already exists
ERROR 1050 (42S01) at line 87: Table 'cloud_service_config' already exists
ERROR 1005 (HY000) at line 101: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 102: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 103: Can't create table 'otrs5.#sql-918_f727' (errno: 121)
ERROR 1005 (HY000) at line 104: Can't create table 'otrs5.#sql-918_f727' (errno: 121)

duplicate, already exists = you already did this.


What is the question that you need answered on this thread?

Ok now i can't connect with root@localhost at 'https://ipserver/otrs/index.pl'
I tried to reset the password with /opt/otrs/bin/otrs.SetPassword.pl root@localhost newpassword
it doesn't show any error


The otrs database is on a remote mysqlserver
my config file /opt/otrs/Kernel/Config.pm:
...
sub Load {
my $Self = shift;
$Self->{'SecureMode'} = '1';

# ---------------------------------------------------- #
# database settings #
# ---------------------------------------------------- #

# The database host
$Self->{'DatabaseHost'} = 'mysql-prod.mydomain.com';

# The database name
$Self->{'Database'} = "otrs5";

# The database user
$Self->{'DatabaseUser'} = "otrs";

# The password of database user. You also can use bin/otrs.CryptPassword.pl
# for crypted passwords
$Self->{'DatabasePw'} = 'otrspassword';

# The database DSN for MySQL ==> more: "perldoc DBD::mysql"
$Self->{'DatabaseDSN'} = "DBI:mysql:database=$Self->{Database};host=$Self->{DatabaseHost}";
...

maybe this script does'nt work if the otrs database is not on the same server ? how can i do this please ?

[crythias]

maybe this script does'nt work if the otrs database is not on the same server ? how can i do this please

cat /opt/otrs/scripts/DBUpdate-to-5.mysql.sql | mysql -p -f -u root otrs5

?

http://dev.mysql.com/doc/refman/5.7/en/connecting.html
-h mysql-prod.mydomain.com

But make sure you have a backup of your data before applying updates.

[mathieu2]

maybe this script does'nt work if the otrs database is not on the same server ? how can i do this please

cat /opt/otrs/scripts/DBUpdate-to-5.mysql.sql | mysql -p -f -u root otrs5

?

http://dev.mysql.com/doc/refman/5.7/en/connecting.html
-h mysql-prod.mydomain.com

But make sure you have a backup of your data before applying updates.

i have to explain:

i have a 'centos6' server with otrs 4 installed but no mysql on it. centos6 is connected to mysql-prod.mydomain.com.

I need to upgrade that server to otrs 5 so i have installed a 'centos7' server with otrs5 and a mysqlserver on it.

Then on the mysql-prod.mydomain.com i dumped the otrs4 database and added it to my mysqlserver on the 'centos7', i wanted to update the database the way it's done here: https://otrs.github.io/doc/manual/admin ... ading.html

i got the errors above, so i didn't do anything else.
Today i have dumped the otrs database on the centos7 and added it back to the mysqlserver-prod.mydomain.com

So now i have a centos7 with otrs5 installed and mysql-prod.mydomain.com with the otrs database.

So now i should do:
root@centos7: /opt/otrs/bin/DBUpdate-to-5.mysql.sql | mysql -p -f -u root otrs5 -h mysql-prod.mydomain.com

?

[crythias]

If I understand you:
Old Server (source) is OTRS4, using database hosted on DBserverA
for migration, you've created a staging server (Staging) for both OTRS5 and Database
You say you've dumped OTRS4 data from DBserverA and installed it on Staging.
Apparently, you've also installed OTRS5 on Staging in a different database (please verify?) on Staging
Your Config.pm on Staging points to data on DBserverA, but not to the data on Staging.
You attempted to apply otrs5 update on the already otrs5 data, which failed.
You have not attempted to apply otrs5 update on Staging otrs4 data.
Somehow you think you want to update DBserverA with otrs5 update, even though you made an otrs4 data on staging.

What you probably want to do is run the otrs5 update on the Staging's otrs4 data. Also make sure Staging's otrs5 Config.pm points to Staging's otrs4 data, not production's otrs4 data.

[mathieu2]

I deleted all my staging vms and just tried the migration only on the otrs4 prod, i succesfully done all the steps here https://otrs.github.io/doc/manual/admin ... ading.html
I have the index page otrs5 but when i try to login (ldap credential) nothing happen (when i try a wrong username i can see the logs in /var/log/httpd/error.log 'user id not found' )
What happened here ? is there an addon that i need to upgrade or something ?
Thank you

[crythias]

I don't really know how to assist with "nothing happens" and since you basically claim to successfully update production, you might want to start a new topic because nothing above your last post is likely relevant.

[mathieu2]

what i'm saying is that we cannot log in via the otrs interface with our ldap credentials