Dathan Pattishall opened 1 decade ago
|
|
Thank you for the bug report. I have a few questions though:
|
|
I use mysql 5.5, 5.6 super customized and tweaked out (my specialty) and I also use the JDBC driver provided with the distribution. The specific problem is the following and affects 5.1 my default charset is utf8 and my default storage engine is innodb, thus without any specific directives in the table create statement all columns default to utf8 varchar(255) is the MAX number of characters 255 characters in this case BUT a utf8 character is 2 - 4 bytes instead of 1 byte for latin1 which is the default config for mysql The MAX index size for a PRIMARY key cannot exceed a few hundred bytes, in fact any primary key with 50 bytes or more takes a penalty. http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/ http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/ The MAX secondary index length of all columns in the index is 3072 bytes, (innodb is 3400 bytes but mysql limits the index length) Yet UNIQUE indexes such as PRIMARY and UNIQUE is MUCH less then that.
|
|
Hm, we usually use utf8 encoding by default in our databases, so I wonder why this wasn't discovered sooner. Thank you very much for the bug report and all the details, we will look at it as soon as possible. |
|
|
|
in your my.cnf under the mysql section add character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci utf8mb4 is an extension of utf8 to support emojis
|
|
Dathan, Thank you for additional comments and information. |
Type |
Bug
|
Priority |
Normal
|
Assignee | |
RedmineID |
2544
|
Version |
tigase-server-5.2.3
|
Spent time |
1h 30m
|
tigase.cluster.repo.CliConSQLRepository defines cluster_nodes
the create table statement is
"create table " + TABLE_NAME + " ("
The code assumes that the charset is latin1 but since many mysql installs sets a default character set and a default storage engine errors such as
2014-12-17 21:43:02.714 [main] ClConSQLRepository.initRepository() WARNING: Problem initializing database:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
I suggest adding the following to the create table statement
private static final String CREATE_TABLE_QUERY_MYSQL =
create table cluster_nodes (
hostname varchar(255) not null,
password varchar(255) not null,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
port int,
cpu_usage double precision unsigned not null,
mem_usage double precision unsigned not null,
primary key(hostname)
) ENGINE=INNODB DEFAULT CHARSET=latin1;