Error Creating Database: Specified Key Was Too Long

[tonyswu]

Hi,

I am trying to initialize OTRS. When I tried to setup the database using the web installer, I got this error:

Code: Select all

Creating database 'otrs':
Done.
Creating tables 'otrs-schema.mysql.sql':
False! :-(
---==> Specified key was too long; max key length is 767 bytes

From Apache log, here is the error:

Code: Select all

[Fri Jul 12 12:15:54 2013] -e: DBD::mysql::db do failed: Specified key was too long; max key length is 767 bytes at /opt/otrs//Kernel/Modules/Installer.pm line 334.
ERR: Specified key was too long; max key length is 767 bytes - CREATE TABLE virtual_fs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    filename TEXT NOT NULL,
    backend VARCHAR (60) NOT NULL,
    backend_key VARCHAR (160) NOT NULL,
    create_time DATETIME NOT NULL,
    PRIMARY KEY(id),
    INDEX virtual_fs_backend (backend(60)),
    INDEX virtual_fs_filename (filename(350))
)

I am using MySQL Community Server 5.6.12, how to I get rid of that error message? Please advise.

Thanks.

[Khole]

It looks like the MySQL server you are trying to use has been set up for something other that utf8 collation. The error you are seeing is a knows limitation I believe:

"Prefixes can be up to 255 bytes long (or 1000 bytes for MyISAM and InnoDB tables as of MySQL
4.1.2). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX
statements is interpreted as number of characters. Take this into account when specifying a
prefix length for a column that uses a multi-byte character set."

I would suggest you follow the normal otrs install procedure for a fresh install of MySQL on a new server. You can setup a sql statements script to backup the otrs database after if that's what you are worried about in the first place.
If you really need to keep the database on a centralised server you should copy the DB check script provided with your otrs install [ /opt/otrs/bin/otrs.CheckDB.pl ] to your MySQL server and run it. It will tell you if there are any indiscrepancies with the current setup.

PS: Read this article http://blog.otrs.org/2013/02/20/about-o ... e-engines/