JIRA complaining of non empty database and then not creating all tables in DB

[Rob Benovic]

Hi all,

I'm dipping into the world of Atlassian and am experiencing some issues.

My setup is as follows: JIRA 8.12.1 Server, MySQL 8, Confluence 7.7.3 (will also be installing Bitbucket and Bamboo at a later stage).

My issue as follows:

I've setup MySQL 8 and got that up and running and managed to also get Confluence installed and running - so I know that the DB is working okay.

I then installed JIRA, and was running through the setup for connecting with my own database, but, when I enter all the details and test my connection, it's telling me that I am trying to connect to a non empty database which is not correct as it was just created moments before.

Error message reads "You have specified a database that is not empty, please specify an empty database."

Then, I try and go the route of using a dbconfig.xml file and we get past the database setup stage when I restart the JIRA service. When I try to add my license, it errors out again as the cwd_application table was not created (according to the logs) - it seems as though quite a few tables were not created (error snippet below).

Caused by: java.sql.SQLSyntaxErrorException: Table 'jiradb.cwd_application' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) [mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) [mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) [mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) [mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) [mysql-connector-java-8.0.21.jar:8.0.21]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) [commons-dbcp2-2.1.jar:2.1]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) [commons-dbcp2-2.1.jar:2.1]
at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:42) [classes/:?]
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeQuery$5(DiagnosticPreparedStatement.java:59) [classes/:?]
at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:69) [atlassian-diagnostics-platform-1.1.10.jar:?]
at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55) [classes/:?]
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeQuery(DiagnosticPreparedStatement.java:59) [classes/:?]
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:527) [entityengine-1.5.0.jar:?]
... 315 more
, referer=http://localhost:8080/secure/SetupLicense!default.jspa, servletErrorMessage=}

After having an easy time setting Confluence up, I'm a little confused as to why JIRA is causing me such grief.

I've looked at the compatibility charts and according to them, JIRA 8.12 should work with MySQL 8.

Any tips or pointers in getting this up and running would be very much appreciated.

Thanks

Rob

[Nic Brough -Adaptavist- · Accepted solution]

If you're going via dbconfig to connect it to a database that Jira has already said is not empty, then it's not going to work - there's a reason it's complaining about a non-empty database in the installer and just bypassing the check is not solving the problem.

I would be interested to see what is actually in the current database that triggered the "database has data already message, but my instinct is to start again. 

Create a completely new database (although you could reuse the old name by simply using "drop database", I wouldn't want to do that until I knew what had been putting data into it that stopped Jira installing)

Then run the installer again, pointing to the new should-be-empty database.  Bear in mind that the second you get past the "configure database" step, Jira will be adding tables, so if you stop the installation at any point after that, you'll need to clean out the target database and start yet again.

[Rob Benovic]

Hi Nic,

After too-ing and fro-ing for a bit (dropping the database and recreating a few times) I decided to bite the bullet and uninstall and re-install everything. This time JIRA was the first Atlassian application I installed and lo and behold it worked!

I don't understand why it didn't work the first time as the database was definitely empty as I created it just before trying to attach JIRA to it, exactly the same way I did it when it worked just now.

Either way, a re-install did the trick.

Now to see if the other Atlassian products will play nice.

Thanks

Rob

[Pieter Wouter Hartog]

Had the same issue with a mysql database, and it was because my jira db user had global privileges besides the Jira schema privileges. As soon as I removed the global privileges the error about database not being empty disappeared. 

[Foo Guan Sim - ServiceRocket]

Somehow with MySQL 8 if you use the same user for both Jira and Confluence DB, you may run into this problem.

To fix this, follow the steps below:

These Instructions are for Fresh Installations Only!

1. Shutdown Jira
2. Remove your current jira home directory (of course, you need to make sure that you do not have any existing Jira data in there)
3. Drop the Jira database (make sure there are no existing data)
4. Create a new MySQL Database user for Jira (somehow jira doesn't play along nicely if you share the same database user for both Confluence and Jira)
5. Create a new Jira DB as per the instructions here:  https://confluence.atlassian.com/adminjiraserver/connecting-jira-applications-to-mysql-8-0-1018775461.html 
6. Grant all the privileges on the new Jira DB to the new user created in step 4 (follow the steps here exactly: https://confluence.atlassian.com/adminjiraserver/connecting-jira-applications-to-mysql-8-0-1018775461.html )
7. Run config.sh in JiraInstallation/bin and connect to the database. 
8. Startup Jira

All the best!

[Nicholas Boughton]

I am also experiencing this issue. I have a mysql 8 db host on which I've setup two databases. One for servicedesk and one for software. 

The servicedesk install and db config went fine but for software it insists that the database is not empty. Here is the output from the mysql shell:

mysql> use jira_software 

Database changed 

mysql> show tables;

 Empty set (0.00 sec)

I'm using the same db user account for both databases.

Is JIRA incapable of recognising that a db host can have more than one database running in an instance?

[guntherfitzgibbons]

same issue on Ubuntu 20.04

[Ben Green]

I am experiencing a similar issue. I am following the Here Be Dragons instructions on a fresh Windows install. The database did not exist before I created it exactly as described in the instructions. I get this error: "You have specified a database that is not empty, please specify an empty database. "

[Nic Brough -Adaptavist-]

It would be interesting to see what tables are in the database that are triggering this problem.

[Ben Green]

Yes it would! Can you tell me how to figure that out?

[Nic Brough -Adaptavist-]

I'd log into the database with the command-line tool for it, and start with "show tables" or equivalent.

I don't know what database you might be using or what connection options you have for it, so I have to be a bit vague here.

[Charles Dombek]

Same issue here. Debian Buster running mysql 8. Confluence installed without issue and is running. When I try to setup Jira, I create a database jiradb. A show tables command in mysql says Empty set (0.00 sec). No tables, no data, absolutely nothing. I too have tried numerous versions of the JDBC connector, still no luck. Any thoughts appreciated.

[Devendar Gangapuram]

Hi @Nicholas Boughton ,

Same issue , I am also facing. I would need to try switch with dieffent the DB table, so when trying it says the same error: You have specified a database that is not empty, please specify an empty database. "

It stopping our JIRA upgradation and PROD work, some ne please assist me on this asap, how to fix and proceed smotth. 

Thank you,

Dev G

[Kumsal ajans]

Same Problem