Project

General

Profile

can't load schema:specified key was too long

wangwang tang
Added over 4 years ago

during I install tigase xmpp server 5.2.0 by GUI,this problem happened.

first i thought maybe the character of myschema not matched,so I tried alter database set tigasedb character set latin 1,and tried checking again,but didn't work.

(I'm using mysql server 5.6 on Windows 8)

QQ图片20141014091214.jpg (112 KB) QQ图片20141014091214.jpg during check the database schema

Replies (5)

Added by Wojciech Kapcia TigaseTeam over 4 years ago

This is related to the changes in MySQL 5.6 and new limits.

With the release of MySQL 5.6 there were a few changes to the defaults used by the database server mostly related to default engine which entails change in defaults regarding length of index key limits. This version switched from MyISAM to InnoDB (v. 5.1: default-storage-engine vs 5.6: default-storage-engine) and then Limits on InnoDB Tables for 5.1 and 5.6 The result in default configuration is the limitation of 767 bytes for index key prefix which is not met by Tigase schema because it defaults to using UTF8 for storage and while we define key length as 765 characters, because of using unicode (in case of MySQL this translates to 3-4bytes per character) we cross the maximum key length limitation.

In order to still be able to use such indexes a following options needs to be included in [mysqld] section of MySQL configuration (most likely, but not limited to, one of the following files: @/etc/my.cnf@, @/etc/mysql/my.cnf@, @/usr/local/mysql/etc/my.cnf@, @~/.my.cnf@):

innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

The problem has been also addressed in Tigase, but this will be included in version 5.3.0 (and new nightlies of such).

Added by wangwang tang over 4 years ago

Wojciech Kapcia wrote:

This is related to the changes in MySQL 5.6 and new limits.

With the release of MySQL 5.6 there were a few changes to the defaults used by the database server mostly related to default engine which entails change in defaults regarding length of index key limits. This version switched from MyISAM to InnoDB (v. 5.1: default-storage-engine vs 5.6: default-storage-engine) and then Limits on InnoDB Tables for 5.1 and 5.6 The result in default configuration is the limitation of 767 bytes for index key prefix which is not met by Tigase schema because it defaults to using UTF8 for storage and while we define key length as 765 characters, because of using unicode (in case of MySQL this translates to 3-4bytes per character) we cross the maximum key length limitation.

In order to still be able to use such indexes a following options needs to be included in [mysqld] section of MySQL configuration (most likely, but not limited to, one of the following files: @/etc/my.cnf@, @/etc/mysql/my.cnf@, @/usr/local/mysql/etc/my.cnf@, @~/.my.cnf@):

[...]

The problem has been also addressed in Tigase, but this will be included in version 5.3.0 (and new nightlies of such).

Thanks for the immediate reply Wojciech.

I tried to changed those options through Mysql Workbranch ,then restarted my computer,and install 5.2.0 again,but it couldn't load schema with the same reason.....

Added by Wojciech Kapcia TigaseTeam over 4 years ago

Can you verify that the settings were applied after the restart by executing SHOW STATUS; and SHOW GLOBAL STATUS;

Can you try installing latest nightly ?

Added by wangwang tang over 4 years ago

Wojciech Kapcia wrote:

Can you verify that the settings were applied after the restart by executing SHOW STATUS; and SHOW GLOBAL STATUS;

Can you try installing latest nightly ?

I have verified that the settings exist in my.ini but not in the results of executing SHOW STATUS or SHOW GLOBAL STATUS;

Just now ,I install latest nightly ,it works that Version can load the schema,but can't load socks 5 schema with the same reason.

I ignore this,and try Run.bat ,problems happened during SimpleCache init ...

Added by Wojciech Kapcia TigaseTeam over 4 years ago

Actually linked output indicates different issue, which was supposed to be fixed already, please see #2320

    (1-5/5)