Hey there Joshua,
We see errors like this when the MySQL collation is not set to what Jira expects to use. Luckily this is an easy fix! The process is documented on our knowledgebase, but I'll also paste the info here.
We recommend backing up your MySQL instance before making changes! If Jira is the only thing you've got on this MySQL instance (since Jira isn't working for you yet) go ahead and skip a backup if you wish. But if you're not sure that Jira is the only database there, better safe than sorry.
Changing the Database Collation
Change yourDB to suit your database name:
ALTER DATABASE yourDB CHARACTER SET utf8 COLLATE utf8_bin
Changing Table Collation
The following query will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:
SELECT CONCAT('ALTER TABLE ', table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'yourDB'
AND
(
C.CHARACTER_SET_NAME != 'utf8'
OR
C.COLLATION_NAME != 'utf8_bin'
);
Changing Column Collation
The following queries (one for varchar columns, and one for non-varchar columns) will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'yourDB'
AND DATA_TYPE = 'varchar'
AND
(
CHARACTER_SET_NAME != 'utf8'
OR
COLLATION_NAME != 'utf8_bin'
);
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET UTF8 COLLATE utf8_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'yourDB'
AND DATA_TYPE != 'varchar'
AND
(
CHARACTER_SET_NAME != 'utf8'
OR
COLLATION_NAME != 'utf8_bin'
);
Dealing with Foreign Key Constraints
It may be necessary to ignore foreign key constraints when making changes to a large number of columns. You can use the SET FOREIGN_KEY_CHECKS command to ignore foreign key constraints while you update the database.
SET FOREIGN_KEY_CHECKS=0;
-- Insert your other SQL Queries here...
SET FOREIGN_KEY_CHECKS=1;
Let us know if you still have any issues getting set up!
Cheers,
Daniel | Atlassian Support
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.