-
Bug
-
Resolution: Fixed
-
Medium
-
None
-
None
Due to an apparent bug in MySQL: http://bugs.mysql.com/bug.php?id=4541, one of Confluence's tables: os_propertyentry, cannot be created when "Confluence+MySQL is configured for foreign character sets or UTF-8": failing with the following error:
create table OS_PROPERTYENTRY (entity_name VARCHAR(125) not null, entity_id BIGINT not null, entity_
key VARCHAR(255) not null, key_type INTEGER, boolean_val BIT, double_val DOUBLE PRECISION, string_va
l VARCHAR(255), long_val BIGINT, int_val INTEGER, date_val DATETIME, primary key (entity_name, entit
y_id, entity_key))
Nov 10, 2004 7:58:30 PM net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: create table OS_PROPERTYENTRY (entity_name VARCHAR(125) not null, entity_id BI
GINT not null, entity_key VARCHAR(255) not null, key_type INTEGER, boolean_val BIT, double_val DOUBL
E PRECISION, string_val VARCHAR(255), long_val BIGINT, int_val INTEGER, date_val DATETIME, primary k
ey (entity_name, entity_id, entity_key))
Nov 10, 2004 7:58:30 PM net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Syntax error or access violation message from server: "Specified key was too long; max key l
ength is 1024 bytes"
or in summary:
"Specified key was too long; max key length is 1024 bytes"
Because a UTF-8 character is stored as three bytes in MySQL the maximum key length can only be 1024/3 characters long. The compound key in the os_propertyentry table seems to be violating this constraint.
- is duplicated by
-
-
- Closed
-
- is related to
-
CONFSERVER-3306 Update os-propertyset key size to ensure that it works with mysql
- Closed
[CONFSERVER-2783] MySQL error in setup: Specified key was too long
Daniel Ostermeier
added a comment - have updated the .hbm.xml file to limit the key length to an allowable size.
Daniel Ostermeier
added a comment - have updated the .hbm.xml file to limit the key length to an allowable size. Daniel Ostermeier
added a comment - This task will fix CONF-2783 Daniel Ostermeier
added a comment - I just installed mysql 4.1.11-nt, using UTF-8 support by default and have encountered the problem.
From charles investigation, the problem is as follows:
1) we are creating a composite key, with varchar(125), varchar(255) and bigint.
2) mysql has a max key size of 1024 bytes.
3) the default database encoding is UTF-8.
4) mysql attempts to allocate 3 bytes for each character since UTF-8 can be up to 3 bytes per char.
5) 125 * (3 bytes) + 255 * (3 bytes) + 1 * (8 bytes) = 1148 bytes.
Options:
1) change character encoding for the varchar columns, supported by mysql 4.1.x
2) reduce the size of the varchar columns to bring them below 1024 bytes.
Daniel Ostermeier
added a comment - I just installed mysql 4.1.11-nt, using UTF-8 support by default and have encountered the problem.
From charles investigation, the problem is as follows:
1) we are creating a composite key, with varchar(125), varchar(255) and bigint.
2) mysql has a max key size of 1024 bytes.
3) the default database encoding is UTF-8.
4) mysql attempts to allocate 3 bytes for each character since UTF-8 can be up to 3 bytes per char.
5) 125 * (3 bytes) + 255 * (3 bytes) + 1 * (8 bytes) = 1148 bytes.
Options:
1) change character encoding for the varchar columns, supported by mysql 4.1.x
2) reduce the size of the varchar columns to bring them below 1024 bytes. There is no proper workaround for this bug.
Until we find a solution your options are:
1) Disable UTF-8 support in the database
2) Use a different database
However this does not seem to consistently happen for all our MySQL customers. In fact, we are able to setup Confluence against a UTF-8 MySQL database (version 4.0.20) with no problems.
If you are unable to import the data dump to your database, switching to latin1 might help.
IE. comment out the default-character-set=utf8 line in your my.ini file (windows) or my.cnf