Tables Project and component where not created.

[David Boisvert]

Tables "project" and "component" where not created.

I noticed this when the web page "http://localhost:8080/secure/SetupApplicationProperties.jspa" hangs

I created the tables manually using the script in the logs.

Did an update of the software but I still get this error...

2018-04-30 15:42:29,072 http-nio-8080-exec-8 ERROR anonymous 942x4x1 1ffh1is 0:0:0:0:0:0:0:1 /secure/SetupApplicationProperties.jspa [c.a.j.w.action.setup.SetupApplicationProperties] Error activating indexing with path 'null': com.atlassian.cache.CacheException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, pname, URL, LEAD, DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FROM project ORDER BY pname (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FR' at line 1)
com.atlassian.cache.CacheException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, pname, URL, LEAD, DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FROM project ORDER BY pname (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FR' at line 1)
 at com.atlassian.cache.memory.DelegatingCachedReference.get(DelegatingCachedReference.java:91)
 at com.atlassian.jira.project.CachingProjectManager.getArchivedProjects(CachingProjectManager.java:118)
 at com.atlassian.jira.project.archiving.ArchivedProjectStatistics.calculateArchivedIssuesCount(ArchivedProjectStatistics.java:48)
 at com.atlassian.jira.project.archiving.ArchivedProjectStatistics.access$100(ArchivedProjectStatistics.java:13)
 ...

Caused by: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, pname, URL, LEAD, DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FROM project ORDER BY pname (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FR' at line 1)
 at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findAll(DefaultOfBizDelegator.java:275)
 at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findAll(WrappingOfBizDelegator.java:161)
 at com.atlassian.jira.project.DefaultProjectManager.getAllProjects(DefaultProjectManager.java:550)

 And the URL "http://localhost:8080/secure/SetupApplicationProperties.jspa" still hangs

In MySQL Workbench I get the same error if I use the same query.
But I get no error if the query is like this...

SELECT ID, pname, URL, `LEAD`, `DESCRIPTION`, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE 
FROM jiradb.project 
ORDER BY pname;

`LEAD`, `DESCRIPTION` : these two needs to be quoted.

Thanks for your help...

David

[Andy Heinzer · Accepted solution]

1. What version of Jira is this?
2. What version of MySQL are you using?
3. Could we also take a look at your $JIRAHOME/dbconfig.xml file? 

This file tells Jira how to connect to your SQL database, so we don't need to see your username/password, but I am interested in all the other settings of that file because depending on the parameters here, Jira can be using incorrect SQL schemes when trying to create the needed structures.

[David Boisvert]

1. Jira Ver.: atlassian-jira-software-7.9.1-x32

2. MySQL ver. : 8.0

3. dbconfig.xml

<jira-database-config><name>defaultDS</name><delegator-name>default</delegator-name><database-type>mysql</database-type><jdbc-datasource><url>jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=3306)/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=default_storage_engine=InnoDB</url><driver-class>com.mysql.jdbc.Driver</driver-class><username>MyUser</username><password>MyPassword</password><pool-min-size>20</pool-min-size><pool-max-size>20</pool-max-size><pool-max-wait>30000</pool-max-wait><validation-query>select 1</validation-query><min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis><time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis><pool-max-idle>20</pool-max-idle><pool-remove-abandoned>true</pool-remove-abandoned><pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout><pool-test-on-borrow>false</pool-test-on-borrow><pool-test-while-idle>true</pool-test-while-idle><validation-query-timeout>3</validation-query-timeout></jdbc-datasource></jira-database-config>

However, to communicate with the DB I had to use "mysql-connector-java-5.1.46-bin.jar" because "mysql-connector-java-8.0.11.jar" didn't work.

With "mysql-connector-java-5.1.46-bin.jar" most tables where created but not all.

Now I fear the ForeignKey were not created also.

Should I uninstall then reinstall ?

Thanks,

David

[Andy Heinzer]

Ah, well unfortunately, Jira does not yet support that version of MySQL.  Please see the supported platforms page for more details.   If you want to use MySQL with Jira, then you will need to use a version from 5.5.x -> 5.7.x.

Once you have one of these versions of MySQL, you can then follow the steps in Connecting Jira Applications to MySQL to complete this setup.

You do not need to uninstall Jira to correct this, but you do need to create a new database on a supported database platform first.   Once you do that, you can then just edit the dbconfig.xml file, save it, and then start Jira to continue.

[David Boisvert]

Hello Andrew,

I am now able to go a step further,

Thanks for your help,

David

[Geoff Davies]

I got caught with this too. Unfortunately MySQL v5.7+ does not include MySQL v8.0. I got caught in the same loop, but the answer given by Andrew explained what was going on.

The SELECT ID, pname, URL, LEAD, DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FROM project ORDER BY pname; was giving a SQL error which could have been fixed with quotes (i.e. SELECT ID, pname, URL, 'LEAD', 'DESCRIPTION', pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FROM project ORDER BY pname;) however reverting to MySQL 5.7.27 allowed me to get past the "stuck" step.

(I also dropped the schema as project hadn't been created correctly). Removing dbconfig.xml and running config.bat got around "most" of the issues.