I'm getting a MySQLSyntaxErrorException when trying to delete an issue

[Thom Orahim]

So I've just installed JIRA on my machine and was setting up a new project when two errors hit me. I did a lot of Googling, but still didn't manage to find a solution.

Problem 1:

I can't browse a project if I have any of the HipChat plugins enabled. I get a "Oops, something went wrong" message

Problem 2:

I created a couple of test issues, which went fine. But then I get an exception stack when I try do delete the issues using More > Delete.

Error stack:

Technical details

Log's referral number: 56ce7cbe-a39f-4fa0-878b-045c10638b17

Cause

Referer URL: http://my.domain.url:8080/projects/VR/issues/VR-1?filter=allopenissues

com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.pyxis.greenhopper.jira]

com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.pyxis.greenhopper.jira]
	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:95) [?:?]
	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:86) [?:?]
	at com.atlassian.sal.core.executor.ThreadLocalDelegateCallable.call(ThreadLocalDelegateCallable.java:38) [?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_102]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_102]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_102]
	at java.lang.Thread.run(Thread.java:745) [?:1.8.0_102]
Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
    Database:
    	- name:MySQL
    	- version:5.7.19-log
    	- minor version:7
    	- major version:5
    Driver:
    	- name:MySQL Connector Java
    	- version:mysql-connector-java-5.1.43 ( Revision: 1d14b699eff3e6112aaedb1cbe5a151ab81f98f1 )
    
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1142
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:53) [?:?]
	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:77) [?:?]
	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:72) [?:?]
	at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21) [?:?]
	at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:34) [classes/:?]
	at sun.reflect.GeneratedMethodAccessor162.invoke(Unknown Source) [?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [?:1.8.0_102]
	at java.lang.reflect.Method.invoke(Method.java:498) [?:1.8.0_102]
	at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26) [atlassian-plugins-core-4.5.0.jar:?]
	at com.sun.proxy.$Proxy406.doInTransaction(Unknown Source) [?:?]
	at sun.reflect.GeneratedMethodAccessor162.invoke(Unknown Source) [?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [?:1.8.0_102]
	at java.lang.reflect.Method.invoke(Method.java:498) [?:1.8.0_102]
	at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:136) [?:?]
	at com.sun.proxy.$Proxy406.doInTransaction(Unknown Source) [?:?]
	at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:18) [?:?]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [?:1.8.0_102]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [?:1.8.0_102]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [?:1.8.0_102]
	at java.lang.reflect.Method.invoke(Method.java:498) [?:1.8.0_102]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56) [?:?]
	at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60) [?:?]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70) [?:?]
	at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53) [?:?]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57) [?:?]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at com.sun.proxy.$Proxy915.execute(Unknown Source) [?:?]
	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:72) [?:?]
	at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:32) [?:?]
	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:91) [?:?]
	... 6 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1142
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [?:1.8.0_102]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [?:1.8.0_102]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [?:1.8.0_102]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [?:1.8.0_102]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.Util.getInstance(Util.java:408) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480) [mysql-connector-java-5.1.43-bin.jar:5.1.43]
	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) [commons-dbcp2-2.1.jar:2.1]
	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) [commons-dbcp2-2.1.jar:2.1]
	at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2238) [?:?]
	at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2294) [?:?]
	at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2266) [?:?]
	at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91) [?:?]
	at net.java.ao.EntityManager.migrate(EntityManager.java:128) [?:?]
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:51) [?:?]
	... 45 more

[Andy Heinzer · Accepted solution]

I think both problems are related to the same source cause. It appears that a MySQL 1142 error means that the SQL user trying to run a SELECT query does not have permissions to do so:  https://stackoverflow.com/questions/10956195/mysql-error-1142-select-command-denied-to-user

Since you see these errors, it most likely means that the user account JIRA is trying to use has not be granted these required permissions yet.

We have a helpful document that explains all the permissions and settings that need to be configured for JIRA to use a MySQL database, please see
https://confluence.atlassian.com/adminjiraserver073/connecting-jira-applications-to-mysql-861253043.html


For a MySQL database running 5.7.19 like yours that would be:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;

But you would need to input your own values for username, database name, password, etc.

Once these settings are correct, you can then try to restart JIRA to see if this resolves the problem.

[Thom Orahim]

That was one of the issues I expected as well so I ran the GRANT query as specified in the "connect JIRA applications to MySQL".

It didn't work.

The next thing I tried was the "GRANT FULL" query on the user as defined in the "Connect CONFLUENCE to MySQL" article. Still no luck.

I can verify that I ran "SHOW GRANTS" query on the user and MySQL report FULL access.

So there shouldn't be anything hindering JIRA doing whatever it wants to the database.

Sorry for double-post. I hit the wrong reply button first time.

Edit: I have Confluence running on the same machine, using the same user but on another database. And I have no problems in Confluence, only in JIRA.

[Andy Heinzer]

Did you restart JIRA after making this database change?   That would be required for JIRA to be able to try to load this plugin again during startup.  If you did that and it is still not working, I would be interested to see the updated JIRAHOME/log/atlassian-jira.log to see what the error is now.   If the database permissions are correct then we should not be seeing the same error.

It is possible that if the database permissions were not correct during the setup, that the database might now exist in this broken state that JIRA cannot repair.   If this is a new install, it might be best just to drop the existing database, and start over with new database instance that has been properly configured before it gets connected to JIRA.

[Thom Orahim]

Can I simply just drop the database and create a new one before starting JIRA, or are there other steps I need to take as well?

[Andy Heinzer]

I would follow these steps:

1. Stop JIRA
2. Drop this existing database
3. closely follow the database setup steps in Connecting JIRA to MySQL database  (this includes database setup, but also configuration of the dbconfig.xml for JIRA)
4. If the database name is the same, and the dbconfig was correct, then you shouldn't have to change anything else in JIRA.  If the new database has a new name/credentials, then you need to update that dbconfig.xml to reflect these changes and save that file
5. Then start JIRA and complete setup once more

[Thom Orahim]

Thanks for the help, both problems were resolved successfully!

There was most likely an issue or corruption in the database which was fixed by simply recreating the database.

[Thom Orahim]

Edit: Moved reply as I hit the wrong reply button the first time.