nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Andy Heinzer

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.

All comments

Andy Heinzer

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

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

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?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-software

error

mysql