Error creating new project in Jira Service Desk Server edition V 3.5.3

[Icon Support]

When I was evaluating Jira service desk (server installation) I was able to create an initial project.   Subsequently we upgraded the product to version 3.5.3 and purchased the 3 agent starter licence but now when I try to create a new project I get the "Hmm... we couldn't create your project due to an unknown error"

The logs show the following :

2018-03-05 10:43:51,702 http-nio-8081-exec-12 ERROR markjames 643x24889x1 sp4hsr xxx.xxx.xxx.xx,xx.xxx.xxx.xxx /rest/project-templates/1.0/templates [c.a.jira.project.ProjectCreateRegistrarImpl] The handler with id com.atlassian.jira.project-templates-plugin:apply-project-template-handler threw an exception while handling a notification about a project being created
com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.RuleSetDaoException: Failed to insert rule component configuration data
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.common.dao.QueryDslDaoHelper.executeWithExceptionMessage(QueryDslDaoHelper.java:54)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.common.dao.QueryDslDaoHelper.executeExpectingNRowUpdates(QueryDslDaoHelper.java:64)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.ConfigDataDao.create(ConfigDataDao.java:35)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.ThenActionConfigDao.create(ThenActionConfigDao.java:53)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.IfThenDao.insertThenActionConfigs(IfThenDao.java:93)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.IfThenDao.create(IfThenDao.java:57)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.RuleDao.insertIfThens(RuleDao.java:89)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.RuleDao.create(RuleDao.java:58)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.RuleSetRevisionDao.insertRules(RuleSetRevisionDao.java:127)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.RuleSetRevisionDao.create(RuleSetRevisionDao.java:77)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.RuleSetDaoImpl.insertRuleSetRevisionAndUpdateActiveRevisionId(RuleSetDaoImpl.java:139)
        at com.atlassian.servicedesk.plugins.automation.internal.configuration.ruleset.dao.querydsl.RuleSetDaoImpl.lambda$create$0(RuleSetDaoImpl.java:84)
        at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:68)
        at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:91)
        at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor$1.execute(DefaultTransactionalExecutor.java:45)
        at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.lambda$borrowConnectionAndExecute$0(JiraHostConnectionAccessor.java:62)
        at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:67) 

The Service desk is running within a Jira Software server edition installation version 7.3.9

I am able top create projects with no problem in Jira Software so the problem is isolated to Jira Service desk.   I have had a look in the community, and on the web generally but didn't see this specific issue.

Any help or advice would be gratefully accepted.

Many thanks, Mark.

[Adrián Plaza [DEISER] · Accepted solution]

Hi @Icon Support,

You can find some information on this JIRAKB.

Regards,

Adrián.

[Icon Support]

Thanks Adrian, 

This was indeed a collation issue where certain columns in some tables were in latin format even though the table was showing the default utf8.  I converted the columns and was then able to create a new SD project as required.    I discovered the columns by running  MySQL query SELECT table_name, character_set_name, collation_name FROM information_schema.COLUMNS WHERE table_schema = '<db_name>' AND collation_name != 'utf8_bin'; 

I then created the conversion sql to an output file 

select CONCAT('ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') INTO OUTFILE '/tmp/collation.sql' from information_schema.columns where table_schema='<db_name>' and collation_name !='utf8_bin' group by table_name;

I then ran the file against the database and all columns were converted.

Hopefully this will help somebody else.