MySQL Table Creation Fails With Error "specified key was too long"

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

The following error messages are reported in the Crowd log file:

ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table cwd_group_attribute (id bigint not null, group_id bigint not null, directory_id bigint not null, attribute_name varchar(255) not null, attribute_value varchar(255), attribute_lower_value varchar(255), primary key (id), unique (group_id, attribute_name, attribute_lower_value)) ENGINE=InnoDB
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Specified key was too long; max key length is 1000 bytes
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table cwd_property (property_key varchar(255) not null, property_name varchar(255) not null, property_value varchar(4000), primary key (property_key, property_name)) ENGINE=InnoDB
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Specified key was too long; max key length is 1000 bytes
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table cwd_user_attribute (id bigint not null, user_id bigint not null, directory_id bigint not null, attribute_name varchar(255) not null, attribute_value varchar(255), attribute_lower_value varchar(255), primary key (id), unique (user_id, attribute_name, attribute_lower_value)) ENGINE=InnoDB
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Specified key was too long; max key length is 1000 bytes
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create index idx_group_attr_group_id on cwd_group_attribute (group_id)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_group_attribute' doesn't exist
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create index idx_group_attr_dir_name_lval on cwd_group_attribute (directory_id, attribute_name, attribute_lower_value)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_group_attribute' doesn't exist
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: alter table cwd_group_attribute add index fk_group_attr_dir_id (directory_id), add constraint fk_group_attr_dir_id foreign key (directory_id) references cwd_directory (id)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_group_attribute' doesn't exist
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: alter table cwd_group_attribute add index fk_group_attr_id_group_id (group_id), add constraint fk_group_attr_id_group_id foreign key (group_id) references cwd_group (id)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_group_attribute' doesn't exist
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create index idx_mem_dir_parent_child on cwd_membership (membership_type, lower_parent_name, lower_child_name, directory_id)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Specified key was too long; max key length is 1000 bytes
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create index idx_user_attr_dir_name_lval on cwd_user_attribute (directory_id, attribute_name, attribute_lower_value)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_user_attribute' doesn't exist
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create index idx_user_attr_user_id on cwd_user_attribute (user_id)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_user_attribute' doesn't exist
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: alter table cwd_user_attribute add index fk_user_attribute_id_user_id (user_id), add constraint fk_user_attribute_id_user_id foreign key (user_id) references cwd_user (id)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_user_attribute' doesn't exist
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: alter table cwd_user_attribute add index fk_user_attr_dir_id (directory_id), add constraint fk_user_attr_dir_id foreign key (directory_id) references cwd_directory (id)
ERROR [hibernate.tool.hbm2ddl.SchemaExport] Table 'crowd.cwd_user_attribute' doesn't exist

Cause

There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:

Resolution

  1. Before starting the application installation, make sure that you've followed the instructions for configuring MySQL
  2. Make sure that the InnoDB Dialect is installed in your MySQL server
  3. Change the storage engine used by default so that new tables will always be created with the appropriate foreign keys. You can use a query like:

    set GLOBAL storage_engine='InnoDb';
    
  4. Make sure that you've specified the InnoDB dialect as the MySQL dialect to be used when creating the database during the Crowd Installation Wizard

 

Last modified on Feb 27, 2015

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.