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

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

2 answers

1 accepted

0 vote

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.

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.

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.

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?

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

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.

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published May 21, 2018 in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

1,203 views 10 18
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you