mysql - 安装 OTRS5 时出现 SQL 错误

Question

我需要你的帮助，我正在我公司的 centos7 上将 OTRS4 升级到 OTRS5，我在数据库迁移步骤 6.2 中遇到问题

https://otrs.github.io/doc/manual/admin/5.0/en/html/upgrading.html

这是一个更新旧 otrs 数据库的 perl 脚本

这是我运行脚本得到的结果:

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
============================
...

Answer 1

这不是您第一次运行 DBUpdate 脚本。我认为这可能是因为您创建的数据库使用的是 MyISAM，而现在由于数据库升级后的新默认值而使用 InnoDB；看这里:http://blog.otrs.org/2013/02/20/about-otrs-mysql-myisam-and-innodb-storage-engines/

最好的办法是再次导入数据库；将其升级到 InnoDB，然后进行迁移。