我需要你的帮助，我正在公司的centos7上将OTRS4升级到OTRS5，我对数据库迁移步骤6.2有问题
https://otrs.github.io/doc/manual/admin/5.0/en/html/upgrading.html
它是一个perl脚本，用于更新旧的otrs数据库
以下是我得到的我运行脚本：
root@otrs:cat /opt/otrs/scripts/DBUpdate-to-5.pl | 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)
root@otrs: 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
我正在升级“otrs5”数据库这是我导入的旧otrs4数据库
MariaDB [otrs5]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| otrs5 |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
我好像有外键错误，
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
============================
...
最佳答案
这不是您第一次运行DBUpdate脚本。我认为这可能是因为您创建的数据库使用的是MyISAM，而现在您使用的是InnoDB，因为数据库升级后出现了新的默认值；请参见此处：http://blog.otrs.org/2013/02/20/about-otrs-mysql-myisam-and-innodb-storage-engines/
最好的办法是再次导入数据库；将其升级到InnoDB，然后进行迁移。
关于mysql - 安装OTRS5时出现SQL错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34085027/