-
Suggestion
-
Resolution: Fixed
Currently Confluence with MySQL 5.7 is affected by this: https://confluence.atlassian.com/display/CONFKB/%27Page+Not+Found%27+when+navigating+to+Confluence+Questions+due+to+com.mysema.query.QueryException
Which is caused by this:
Caused by: com.mysema.query.QueryException: Caught SQLException for delete from `CONTENTPROPERTIES` where `CONTENTPROPERTIES`.`PROPERTYID` in (select `PROPERTY_ID` from (select `answerProperties`.`PROPERTYID` as `PROPERTY_ID` from `CONTENT` `answers` join `CONTENTPROPERTIES` `answerProperties` on `answerProperties`.`CONTENTID` = `answers`.`CONTENTID` left join (select `questionProperties`.`LONGVAL` as `ANSWER_ID` from `CONTENT` `questions` join `CONTENTPROPERTIES` `questionProperties` on `questionProperties`.`CONTENTID` = `questions`.`CONTENTID` where `questions`.`PLUGINKEY` = 'com.atlassian.confluence.plugins.confluence-questions:question' and `questions`.`PREVVER` is null and `questionProperties`.`PROPERTYNAME` = 'accepted') as `ACCEPTED_ANSWER_IDS` on `answers`.`CONTENTID` = `ANSWER_ID` where `answers`.`PLUGINKEY` = 'com.atlassian.confluence.plugins.confluence-questions:answer' and `answers`.`PREVVER` is null and `answerProperties`.`PROPERTYNAME` = 'accepted' and `ANSWER_ID` is null) as `TMP`) at com.mysema.query.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:38) at com.mysema.query.sql.Configuration.translate(Configuration.java:451) at com.mysema.query.sql.dml.SQLDeleteClause.execute(SQLDeleteClause.java:169) at com.atlassian.confluence.plugins.questions.internal.upgrade.DuplicatedAcceptedAnswersRemover.deleteDuplicatedAcceptedAnswers(DuplicatedAcceptedAnswersRemover.java:83) ... at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:67) 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: java.sql.SQLException: You can't specify target table 'CONTENTPROPERTIES' for update in FROM clause
Different syntax in MySQL 5.7. This seems to only affect Confluence Questions add-on.
UPDATE: The SQLException above was fixed in Questions for Confluence 1.0.618. If you install the latest version, it won't be a problem.
![[Atlassian Documentation] Page](https://confluence.atlassian.com/images/icons/favicon.png "[Atlassian Documentation] Page"){kind=icon}
![[Confluence] Page](https://bulldogwiki.internal.atlassian.com/wiki/images/icons/favicon.png "[Confluence] Page"){kind=icon}
[CONFSERVER-40385] Support MySQL 5.7
A fix for this issue is now available for Confluence Server customers.
Upgrade now or check out the Release Notes to see what other issues are resolved.
Hi werner.mueller,
We have already identified the root cause for the following error:
2017-03-23 22:22:19,667 ERROR [localhost-startStop-1] [confluence.setup.dbcheck.MySQLChecker] checkStorageEngineType Your database Storage Engine could not be determined: Incorrect result size: expected 1, actual 0
A fix will be available in an upcoming release.
Regarding the 2nd error, we will check if it's related to MySQL 5.7.
I think the issue here has a wider scope than just fixing the exception above. it's about having 5.7 on the list of supported databases.
using mysql 5.7 and confluence up to 6.1.0 I - for example - get some more exceptions:
2017-03-23 22:22:08,545 INFO [localhost-startStop-1] [com.atlassian.confluence.lifecycle] info Starting Confluence 6.1.0 [build 7109 based on commit hash 9af699845080e5027a80c674ed9e6d30a44ff7e8] 2017-03-23 22:22:10,183 INFO [localhost-startStop-1] [atlassian.confluence.cluster.DefaultClusterConfigurationHelper] lambda$populateExistingClusterSetupConfig$1 Populating setup configuration if running with Cluster mode... 2017-03-23 22:22:10,279 INFO [localhost-startStop-1] [springframework.web.context.ContextLoader] initWebApplicationContext Root WebApplicationContext: initialization started 2017-03-23 22:22:11,948 INFO [localhost-startStop-1] [com.atlassian.confluence.lifecycle] <init> Loading EhCache cache manager 2017-03-23 22:22:19,209 INFO [localhost-startStop-1] [springframework.web.context.ContextLoader] initWebApplicationContext Root WebApplicationContext: initialization completed in 8930 ms 2017-03-23 22:22:19,667 ERROR [localhost-startStop-1] [confluence.setup.dbcheck.MySQLChecker] checkStorageEngineType Your database Storage Engine could not be determined: Incorrect result size: expected 1, actual 0 org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 at org.springframework.dao.support.DataAccessUtils.requiredSingleResult(DataAccessUtils.java:71) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:797) at com.atlassian.confluence.setup.dbcheck.MySQLChecker.getMySQLVariable(MySQLChecker.java:100) at com.atlassian.confluence.setup.dbcheck.MySQLChecker.checkStorageEngineType(MySQLChecker.java:77) at com.atlassian.confluence.setup.dbcheck.MySQLChecker.verifyDatabaseSetup(MySQLChecker.java:37) at com.atlassian.confluence.setup.DefaultBootstrapManager.checkConfigurationOnStartup(DefaultBootstrapManager.java:359) at com.atlassian.confluence.setup.BootstrapApplicationStartupListener.checkConfigurationOnStartup(BootstrapApplicationStartupListener.java:31) at com.atlassian.confluence.setup.BootstrapApplicationStartupListener.onApplicationEvent(BootstrapApplicationStartupListener.java:25) at com.atlassian.confluence.setup.BootstrapApplicationStartupListener.onApplicationEvent(BootstrapApplicationStartupListener.java:20) at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:163) at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:136) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:381) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:387) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:335) at com.atlassian.spring.container.SpringContainerContext.contextReloaded(SpringContainerContext.java:164) at com.atlassian.spring.container.ContainerContextLoaderListener.contextInitialized(ContainerContextLoaderListener.java:40) at com.atlassian.confluence.setup.ConfluenceBootstrappedContextLoaderListener.contextInitialized(ConfluenceBootstrappedContextLoaderListener.java:41) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4853) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5314) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1408) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1398) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) 2017-03-23 22:22:19,729 INFO [localhost-startStop-1] [atlassian.plugin.manager.DefaultPluginManager] earlyStartup Plugin system earlyStartup begun 2017-03-23 22:22:48,700 WARN [ThreadPoolAsyncTaskExecutor::Thread 1] [spring.scanner.util.ProductFilterUtil] detectProduct Couldn't detect product, will use ProductFilter.ALL 2017-03-23 22:22:48,885 WARN [ThreadPoolAsyncTaskExecutor::Thread 2] [spring.scanner.util.ProductFilterUtil] detectProduct Couldn't detect product, will use ProductFilter.ALL 2017-03-23 22:22:57,498 INFO [localhost-startStop-1] [atlassian.plugin.manager.DefaultPluginManager] logTime Plugin system earlyStartup ended 2017-03-23 22:22:57,546 INFO [localhost-startStop-1] [confluence.upgrade.impl.DefaultUpgradeManager] beforeUpgrade Generating pre-upgrade recovery file... 2017-03-23 22:22:57,578 INFO [localhost-startStop-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] begin [] 2017-03-23 22:22:57,583 INFO [localhost-startStop-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] begin DATABASE_INFORMATION: []
Same error still shown when upgrading to 6.2.0
Since that database is used by other tools as well I'm not entirely happy with changing global settings.
There are other exceptions in the log as well:
jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:866) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:937) at com.atlassian.confluence.impl.schedule.caesium.SchedulerRunDetailsDao.purgeJobHistory(SchedulerRunDetailsDao.java:90) at com.atlassian.confluence.impl.schedule.caesium.SchedulerRunDetailsDao.addRunDetails(SchedulerRunDetailsDao.java:58) at sun.reflect.GeneratedMethodAccessor363.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) at com.sun.proxy.$Proxy85.addRunDetails(Unknown Source) at com.atlassian.scheduler.core.AbstractSchedulerService.addRunDetails(AbstractSchedulerService.java:151) at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:98) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:443) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeLocalJob(CaesiumSchedulerService.java:410) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:388) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:285) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:282) at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:65) at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:59) at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:34) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:873) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:866) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) ... 28 more 2017-06-05 04:45:18,557 ERROR [http-nio-8090-exec-4] [confluence.servlet.download.ExportDownload] getStreamForDownload Unauthorized attempt to access resource by null. For more detail turn on INFO level logging for package : com.atlassian.confluence.servlet.d ownload
Hard to say if database version related, I have no way to compare that.
So: please...? 
Axel Pardemann
added a comment - In that I agree Sander. Official support would be nice! Yes @Apardemann it will probably work in most cases, but if you are using a not frequently used function/plugin it might break your installation and Atlassian will probably not support you just as @Mark Boulton stated already.
So in short; Yes it will work if you set up MySQL 5.7 according to installation instructions but in case of problems you will be on your own. It's a risk you need to decide for yourself to take.
(Added comment with correct account)
Still the need exists for official support, Atlassian!
Axel Pardemann
added a comment - Yes I've been following this instructions: https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html
I'm using
CREATE SCHEMA `confluence` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
to create the database.
I have copied mysql-connector-java-5.1.42-bin.jar (which works fine with JIRA) to the /opt/atlassian/confluence/lib
I've also tried configuring the jdbc with and without the &useUnicode=true&characterEncoding=utf8 querystring without luck:
jdbc:mysql://server.domain.com/confluence[?useUnicode=true&characterEncoding=utf8]
My setup two Ubuntu 16.04.2 servers, one is the app server where I have already installed JIRA and it works fine, and now I'm installing Confluence. The other servers is the database server and remote connection works ok (again: JIRA is working without a problem).
After the third try I succesfully get Confluence to work. I was configuring it behind a proxy (so that I can have a nice URL without a port). Seems that the proxy timeout was collapsing the install. So for anyone trying this: configure Confluence first with the ugly portful URL.
Thanks for all who shared they thoughts. I can confirm that although not officially supported, it can be installed with MySql 5.7.x
Axel Pardemann
added a comment - Yes I've been following this instructions: https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html
I'm using
CREATE SCHEMA `confluence` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
to create the database.
I have copied mysql-connector-java-5.1.42-bin.jar (which works fine with JIRA) to the /opt/atlassian/confluence/lib
I've also tried configuring the jdbc with and without the &useUnicode=true&characterEncoding=utf8 querystring without luck:
jdbc:mysql: //server.domain.com/confluence[?useUnicode= true &characterEncoding=utf8]
My setup two Ubuntu 16.04.2 servers, one is the app server where I have already installed JIRA and it works fine, and now I'm installing Confluence. The other servers is the database server and remote connection works ok (again: JIRA is working without a problem).
After the third try I succesfully get Confluence to work. I was configuring it behind a proxy (so that I can have a nice URL without a port). Seems that the proxy timeout was collapsing the install. So for anyone trying this: configure Confluence first with the ugly portful URL.
Thanks for all who shared they thoughts. I can confirm that although not officially supported, it can be installed with MySql 5.7.x While we're also waiting for an official support for MySQL 5.7, I know that several very large Confluence server instances run on MySQL 5.7 (i.e. German Bundeswehr). An I18N error might be related to a wrong charset / collation setting. I assume everything is set as Atlassian defined it for 5.6...?
Axel Pardemann
added a comment - I don't think it works @robert_kerkhoven
I tried the same on an Ubuntu 16.04 with MySql 5.7.18 and it does not work. The tables are created/populated but the confluence app cannot be started due to an i18NBean error. Tried twice without luck...
Hopefully official MySql 5.7.x support is coming soon as I cannot have another MySql server just for confluence...
If the issue I got is not database related then pleas point me in the right direction... but the problem arises just when the DB is configured.
Axel Pardemann
added a comment - I don't think it works @robert_kerkhoven
I tried the same on an Ubuntu 16.04 with MySql 5.7.18 and it does not work. The tables are created/populated but the confluence app cannot be started due to an i18NBean error. Tried twice without luck...
Hopefully official MySql 5.7.x support is coming soon as I cannot have another MySql server just for confluence...
If the issue I got is not database related then pleas point me in the right direction... but the problem arises just when the DB is configured.
Thanks for the hint. Just upgraded to 6.3.1 - all errors gone