Updating confluence from 7.10.2 to 7.11.1 have som duplicate key issues

Netset March 19, 2021

I need an Expert opinion here:

What is the impact of deleting the following Tables in the Confluence DATABASE

DENORMALISED_SPACE_VIEW_PERMISSIONS
DENORMALISED_SPACE_EDIT_PERMISSIONS
DENORMALISED_SID

?

5 answers

1 accepted

2 votes
Answer accepted
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 19, 2021

You will render your installation unsupported, and break all the permissions in it.  You will fiind you will need to restore the tables to get your system working properly again.

Question though?  Why do you think you want to do this?  Can you tell us a short-ish story about how you landed here?

0 votes
Normann P. Nielsen (Netic)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 3, 2021
0 votes
Daniel Eads
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 22, 2021

Just noting for the benefit of others reading the question that there are a couple other threads going about this issue:

Normann P. Nielsen (Netic)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 3, 2021

Was this solved, and became something more that links pointing around between similar tickets..

0 votes
Netset March 19, 2021

As you see in logs,  bad SQL grammar [create unique index denormalised_sid_name_type_uniq_idx on DENORMALISED_SID (NAME, TYPE)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate
key name 'denormalised_sid_name_type_uniq_idx' 

 

in Test server I deleted DENORMALISED_SID Table and restarted confluence, it gives med the same bad_sql_command but with DENORMALISED_SPACE_VIEW_PERMISSIONS 

in third time, it gives me the same issue with DENORMALISED_SPACE_EDIT_PERMISSIONS

 

so I deleted the three tables and restarted confluence, so I get them created again and success with the upgrade! 

0 votes
Netset March 19, 2021

Because  I need to upgrade from Confluence 7.10.2 to 7.11.1, I have posted a support case but I get no help, Here is my case!

Server CentOS 7 latest kernel version

-----------------------------------------------------------------

Mysql: Ver 8.0.23 for Linux on x86_64

mysql settings: /etc/my.cnf


innodb_log_file_size=2GB
max_allowed_packet=256M
binlog_format=row
character-set-server=utf8mb4
collation-server=utf8mb4_bin

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

in mysql > 
| max_connections | 151 |
| mysqlx_max_connections | 100 |

----------------------------------------------------------------

Confluence Server configuration:

1- installation_dir/conf/server.xml

using jndi datasource connection

driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/confluence?useUnicode=true&characterEncoding=utf8&useSSL=false"
maxTotal="80"
maxIdle="20"

 

2-  installation_dir/bin/setenv.sh


CATALINA_OPTS="-Xms1024m -Xmx6000m -XX:+UseG1GC ${CATALINA_OPTS}"
JAVA_HOME="/opt/java11" #latest LTS version of java 11

-------------------------------------------------------------------

when upgrading from 7.10.2 to 7.11.1 I see in logs HOME_DIR/confluence-7.11.1/logs/atlassian-confluence.log

that it make recovery for all databsetables and then takes abit longer in:

[confluence.upgrade.recovery.ConfluenceProgressMonitor] begin TABLE_DATA: [scheduler_run_details]

 

then  get 

2021-03-18 11:09:31,512 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] end []
2021-03-18 11:09:31,513 INFO [Catalina-utility-1] [confluence.upgrade.impl.DefaultUpgradeManager] beforeUpgrade Finished generating pre-upgrade recovery file.
2021-03-18 11:09:34,273 INFO [Catalina-utility-1] [confluence.upgrade.ddl.HibernateDdlExecutor] executeDdlStatements Executing DDL: create unique index denormalised_sid_name_type_uniq_idx on DENORMALISED_SID (N
AME, TYPE)
2021-03-18 11:09:34,312 WARN [Catalina-utility-1] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
->[com.atlassian.confluence.upgrade.ddl.HibernateDdlExecutor.executeDdl]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #347931867)
2021-03-18 11:09:34,353 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.up
grade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@3f951504 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [create unique index denormalised_sid_name_type_uniq_idx on D
ENORMALISED_SID (NAME, TYPE)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'denormalised_sid_name_type_uniq_idx'
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@3f951504 failed during the SCHEMA_UPGRADE phase due to: Statem
entCallback; bad SQL grammar [create unique index denormalised_sid_name_type_uniq_idx on DENORMALISED_SID (NAME, TYPE)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate
key name 'denormalised_sid_name_type_uniq_idx'

.

.

.

Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create unique index denormalised_sid_name_type_uniq_idx on DENORMALISED_SID (NAME, TYPE)]; nested exception is com
.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'denormalised_sid_name_type_uniq_idx'

.

.

.

at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
... 34 more
2021-03-18 11:09:34,356 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1 errors were encountered during upgrade:
2021-03-18 11:09:34,357 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1: StatementCallback; bad SQL grammar [create unique index denormalised_sid_name_type_uniq_idx on DENORMALISED_SID (NAME, TYPE)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'denormalised_sid_name_type_uniq_idx'
2021-03-18 11:09:34,366 INFO [Catalina-utility-1] [atlassian.plugin.manager.DefaultPluginManager] lambda$lateStartup$4 Plugin system lateStartup begun
2021-03-18 11:09:37,549 INFO [ThreadPoolAsyncTaskExecutor::Thread 41] [plugins.synchrony.bootstrap.DefaultSynchronyProxyMonitor] <init> synchrony-proxy healthcheck url: http://127.0.0.1:8090/synchrony-proxy/healthcheck
2021-03-18 11:09:38,891 WARN [ThreadPoolAsyncTaskExecutor::Thread 55] [addons.analytics.scheduler.DataRetentionScheduleManagerImpl] afterPropertiesSet Initializing Data Retention Schedule with frequency: 60000 milliseconds, batch size: 10000
2021-03-18 11:09:39,204 WARN [ThreadPoolAsyncTaskExecutor::Thread 55] [addons.analytics.scheduler.EventLimiterScheduleManagerImpl] afterPropertiesSet Initializing Event Limiter Schedule with frequency: 60000 milliseconds

 

then it repeats the database tables recovery and then

 

2021-03-18 11:10:31,086 INFO [AtlassianEvent::CustomizableThreadFactory-2] [atlassian.confluence.event.MonitorableCallerRunsPolicy] rejectedExecution Asynchronous queue is full. The task will be processed synchr
onously in the current thread instead. We'll print this message every 60 seconds until there's enough space in queue to start processing messages asynchronously again. Executor: [java.util.concurrent.ThreadPoolE
xecutor@5aab81d9[Running, pool size = 2, active threads = 2, queued tasks = 256, completed tasks = 564]]
2021-03-18 11:11:12,872 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] end TABLE_DATA: [scheduler_run_details]
2021-03-18 11:11:12,873 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] begin TABLE_DATA: [storageenginetest]
2021-03-18 11:11:12,874 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] end TABLE_DATA: [storageenginetest]
2021-03-18 11:11:12,874 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] begin TABLE_DATA: [user_mapping]
2021-03-18 11:11:12,875 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] end TABLE_DATA: [user_mapping]
2021-03-18 11:11:12,876 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] end TABLES_DATA: []
2021-03-18 11:11:12,876 INFO [Catalina-utility-1] [confluence.upgrade.recovery.ConfluenceProgressMonitor] end []
2021-03-18 11:11:12,877 INFO [Catalina-utility-1] [confluence.upgrade.impl.DefaultUpgradeManager] entireUpgradeFinished Finished generating post-upgrade recovery file.
2021-03-18 11:11:12,877 INFO [Catalina-utility-1] [atlassian.confluence.upgrade.AbstractUpgradeManager] entireUpgradeFinished Upgrade completed successfully
2021-03-18 11:11:14,717 INFO [Catalina-utility-1] [confluence.admin.actions.SystemInfoOnStartup] startup
--------------------------
Build Information
--------------------------
maxUsers = 10
installationDate = Thu Feb 21 13:59:56 CET 2019

=====================================

when I look in the GUI:

Confluence had problems starting up

This page is for Confluence administrators. If you're seeing this page, your Confluence administrator is probably working to restore the service.
Database: Your database connection pool size is not configured. The recommended minimum size is 60.

Learn more
Description Time Level Exception
Upgrade failed. Please consult the system logs for details. You will need to fix these problems, restore your database and confluence home directory to the pre upgrade state. Then retry the upgrade. Upgrade error message: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@3f951504 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [create unique index denormalised_sid_name_type_uniq_idx on DENORMALISED_SID (NAME, TYPE)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'denormalised_sid_name_type_uniq_idx'

 

==========================================

I can send the log via mail if it is needed!

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events