Details
-
Suggestion
-
Resolution: Fixed
-
None
Description
On initial JIRA startup integrated with MySQL the following errors are reported:
[... lots of table creations here ...] 2010-12-16 15:28:10,062 main WARN [core.entity.jdbc.DatabaseUtil] Entity "Worklog" has no table in the database 2010-12-16 15:28:10,218 main ERROR [core.entity.jdbc.DatabaseUtil] Could not create declared indices for entity "GroupAttribute" 2010-12-16 15:28:10,218 main ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following: CREATE INDEX idx_goup_attr_dir_name_lval ON cwd_group_attributes (directory_id, attribute_name, lower_attribute_value) Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes SQL Exception while executing the following: CREATE UNIQUE INDEX uk_group_attr_name_lval ON cwd_group_attributes (group_id, attribute_name, lower_attribute_value) Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes 2010-12-16 15:28:10,288 main ERROR [core.entity.jdbc.DatabaseUtil] Could not create declared indices for entity "Membership" 2010-12-16 15:28:10,289 main ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following: CREATE INDEX idx_mem_dir_parent_child ON cwd_membership (lower_parent_name, lower_child_name, membership_type, directory_id) Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes 2010-12-16 15:28:10,425 main ERROR [core.entity.jdbc.DatabaseUtil] Could not create declared indices for entity "UserAttribute" 2010-12-16 15:28:10,425 main ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following: CREATE INDEX idx_user_attr_dir_name_lval ON cwd_user_attributes (directory_id, attribute_name, lower_attribute_value) Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes 2010-12-16 15:28:13,801 main INFO [atlassian.jira.startup.JiraStartupLogger]
JIRA's performance may suffer if you have a large number of users and/or groups defined (even if they in an external directory).
Cause
There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:
Resolution
The resolution steps are different for new installation and for existing installations with data.
Resolution for a New Installation
In this case we need to configure JIRA to use InnoDB before any of its tables are created.
- Before starting the application installation (i.e. none of JIRA's tables can already be created), ensure that you have followed the instructions provided in Connecting JIRA to MySQL.
- Ensure that the InnoDB Dialect is installed on your MySQL server.
- Change the storage engine used by JIRA so that new tables will always be created under InnoDB. There are two main ways of doing this:
- Option 1: Change MySQL settings*: Set the default-storage-engine to InnoDB and restart MySQL. This makes InnoDB the default engine for all new tables on your MySQL instance. This could affect other applications using your MySQL instance.
- Option2: Change connection URL used for JIRA*: You can configure JIRA to use InnoDB by default by adding sessionVariables=storage_engine=InnoDB to your JDBC URL in the $JIRA_HOME/dbconfig.xml file. This change will be isolated to JIRA. The location of the JDBC URL has changed between JIRA 4.3.x and 4.4.x. For example:
jdbc:mysql://localhost/jiradb?useUnicode=true&characterEncoding=UTF8 will be changed to jdbc:mysql://localhost/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB.
- Install JIRA.
Resolution for existing JIRA installation having MyISAM tables with data
Option 1: Migrate the MyISAM tables in place
- Stop JIRA.
- Take a backup of your database in case things go wrong.
- Make sure that the InnoDB Dialect is installed on your MySQL server.
- Change the storage engine used by JIRA so that new tables will always be created under InnoDB with the following, as from these docs:
ALTER TABLE t1 ENGINE = InnoDB;
- Migrate all JIRA's current tables to InnoDB. For example, you can:
- Run the following command to generate an SQL script that will migrate your tables from MyISAM to InnoDB:
mysql -NBe "select concat('alter table ', TABLE_NAME, ' engine = InnoDB;') from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '<database>' and engine = 'MyISAM'" -u <user> -p <database>
In this command <database> is the name of your database and <user> is a user who can access <database>.
- Check to generated script to ensure that it does not do anything you don't expect.
- Run the generated script. This can take some time.
- Run the following command to generate an SQL script that will migrate your tables from MyISAM to InnoDB:
- Restart JIRA.
Option 2: Perform a JIRA backup and restore
This will require taking an XML backup of JIRA and restoring it to a new database.
- Take an XML backup of JIRA.
- Stop JIRA.
- Make sure that the InnoDB Dialect is installed on your MySQL server.
- Change the storage engine used by JIRA so that new tables will always be created under InnoDB with the following, as from these docs:
ALTER TABLE t1 ENGINE = InnoDB;
- Create a new database for JIRA.
- Configure JIRA to connect to the new database.
- Start JIRA.
- Restore the backup you took earlier.
Attachments
Issue Links
- is duplicated by
-
JRASERVER-24857 Create MySQL tables using InnoDB engine by default - session variable
- Closed
- relates to
-
JRASERVER-23666 Connection problems with MySQL
- Closed