[CONFSERVER-30380] Upgrade to 5.2.x fails due to MySQL collation mismatch on columns 'FOLLOW_CONNECTIONS.FOLLOWER' and 'user_mapping.user_key'

Type: Bug
Resolution: Fixed
Priority: High
Fix Version/s: 5.2.4, 5.2.5
Affects Version/s: 5.2.3
Component/s: None
Labels:

Bug Fix Policy:
View Atlassian Server bug fix policy

The problem

Confluence 5.2.x introduces a new table 'user_mappings' as well as a new foreign key:

alter table FOLLOW_CONNECTIONS add constraint FK_FOLLOW_CONNECTIONS_FOLLOWER foreign key (FOLLOWER) references user_mapping (user_key)

When using MySQL, the upgrade can fail at the stage where this foreign key is being added if the storage engine is incorrect, or the column definitions between the following do not match:

FOLLOW_CONNECTIONS.FOLLOWER
user_mapping.user_key

The logs will show:

2013-08-13 22:50:16,459 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table FOLLOW_CONNECTIONS add constraint FK_FOLLOW_CONNECTIONS_FOLLOWER foreign key (FOLLOWER) references user_mapping (user_key)

Diagnosis

There are two issues that can cause this problem - either having the wrong collation, or using the wrong storage engine (MyISAM as opposed to InnoDB). To determine which you are experiencing, follow these steps. Be aware that your database may have both problems, and ensure you run through all steps in the diagnosis section.

Run the following against the Confluence database in MySQL:
```
show table status;
```
1. If the storage engine for any table is not InnoDB, follow the instructions in Workaround 1.
2. See whether collations for FOLLOW_CONNECTIONS and user_mapping tables are different (and check other tables while you are there).
3. If they are different, run a show variables like '%collation%'; and see what your DB collation is set to.
4. If the collation is not set to utf8_bin, follow the steps in Workaround 2.
Otherwise if the collations are the same, examine the two tables explicitly:
```
show create table FOLLOW_CONNECTIONS;
show create table user_mapping;
```
1. Look at the column definitions for FOLLOWER and user_key for differences.
2. If the collation for these tables is not both set to utf8_bin, follow the steps in Workaround 2.
Check the default engine by running this command:
```
show engines;
```
- If InnoDB is not the default engine, follow the steps in this document to set the default engine.

Workaround

Workaround 1 - Storage Engine

NB: the database should be backed up before making any changes.

Follow the instructions in this document. This is necessary to create all the foreign keys that you should have in your database, to ensure referential integrity.
Then attempt the upgrade to 5.2 again. NB: Do not attempt to perform the upgrade at the same time. Follow the instructions above to fix the storage engine, and then upgrade as normal once that is complete.

You may also want to check the Known Issues for MySQL to see if there are any other settings you may be missing that could prevent issues like this in future.

Workaround 2 - Collation

NB: the database should be backed up before making any changes.

Shut down Confluence

If any of the following SQL statements returns any rows, migration to utf8 should be performed before proceeding:

use confluence;
SHOW variables WHERE Variable_name = "character_set_database" AND Value != "utf8";

SELECT table_name, column_name, character_set_name FROM information_schema.`COLUMNS` C
WHERE table_schema = "confluence"
  AND character_set_name != "utf8";

SELECT T.table_name, CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "confluence"
  AND CCSA.character_set_name != "utf8";

Check database collation:

use confluence;
show variables like "collation_database";

If database collation is not "utf8_bin", fix it with:

alter database confluence character set utf8 collate utf8_bin;

Find tables using non utf8_bin collation:

SELECT DISTINCT T.table_name FROM information_schema.`TABLES` T, information_schema.`COLUMNS` C 
WHERE T.table_schema = "confluence"
  AND T.table_name = C.table_name
  AND (T.table_collation != "utf8_bin" OR C.collation_name != "utf8_bin");

Fix each table with a command like the following:

ALTER TABLE FOLLOW_CONNECTIONS CONVERT TO CHARACTER SET DEFAULT;

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET DEFAULT') as stmt 
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'confluence'
ORDER BY 1;

Ensure to change the t.table_schema to reflect your Confluence database name.

Restart Confluence. The remaining upgrade tasks should run at this point.

You may also want to check the Known Issues for MySQL to see if there are any other settings you may be missing that could prevent issues like this in future.

was split into

CONFSERVER-31994 Improve default storage engine error message

Closed

mentioned in: Page Failed to load; Wiki Page Failed to load; Wiki Page Failed to load; Page Failed to load; Wiki Page Loading...; Wiki Page Loading...; Wiki Page Loading...

(3 mentioned in)

Form Name

Jonas Andersson added a comment - 26/Jan/2015 3:14 PM

I am attempting to move our current production confluence instance to a new box, and ran into the error: "Detected tables with non-default collation. See https://confluence.atlassian.com/x/TABrFw" which doesnt make any sense since this very same sqldump that is currently running on our current production machine. As an Atlassian admin i am used to having to fiddle with things to get them work, even if they are a carbon-copy of the production-env. However, i followed this article to rectify it, and until step 5, no rows showed up that needed changing. Step 5 however, showed me a list of 126 tables, making me VERY curious to how this could ever worked on our production server. Never the less i created a script containing these 126 lines:

 ALTER TABLE `flownet_prod`.`AO_098293_ANNOUNCEMENT_ENTITY` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_187CCC_SIDEBAR_LINK` CONVERT TO CHARACTER SET DEFAULT         
 ALTER TABLE `flownet_prod`.`AO_21D670_WHITELIST_RULES` CONVERT TO CHARACTER SET DEFAULT      
 ALTER TABLE `flownet_prod`.`AO_38321B_CUSTOM_CONTENT_LINK` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_429490_RESPONSE` CONVERT TO CHARACTER SET DEFAULT             
 ALTER TABLE `flownet_prod`.`AO_429490_SETTINGS` CONVERT TO CHARACTER SET DEFAULT             
 ALTER TABLE `flownet_prod`.`AO_42E351_HEALTH_CHECK_ENTITY` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_4E5044_FORUM_ACTIVITY_SCORE` CONVERT TO CHARACTER SET DEFAULT 
 ALTER TABLE `flownet_prod`.`AO_4E5044_FORUM_PERMISSION` CONVERT TO CHARACTER SET DEFAULT     
 ALTER TABLE `flownet_prod`.`AO_54C900_CONTENT_BLUEPRINT_AO` CONVERT TO CHARACTER SET DEFAULT 
 ALTER TABLE `flownet_prod`.`AO_54C900_C_TEMPLATE_REF` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_54C900_SPACE_BLUEPRINT_AO` CONVERT TO CHARACTER SET DEFAULT   
 ALTER TABLE `flownet_prod`.`AO_5F3884_FEATURE_DISCOVERY` CONVERT TO CHARACTER SET DEFAULT    
 ALTER TABLE `flownet_prod`.`AO_6384AB_DISCOVERED` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`AO_6384AB_FEATURE_METADATA_AO` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_65B466_INFINIDEX_CONFIG` CONVERT TO CHARACTER SET DEFAULT     
 ALTER TABLE `flownet_prod`.`AO_65B466_PATH_INFO_ENTITY` CONVERT TO CHARACTER SET DEFAULT     
 ALTER TABLE `flownet_prod`.`AO_6D345F_CUSTOM_LIBRARY` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_6D345F_CUSTOM_SHAPE` CONVERT TO CHARACTER SET DEFAULT         
 ALTER TABLE `flownet_prod`.`AO_6D345F_USER_ENTITY` CONVERT TO CHARACTER SET DEFAULT          
 ALTER TABLE `flownet_prod`.`AO_6D345F_USER_TO_LIBRARY` CONVERT TO CHARACTER SET DEFAULT      
 ALTER TABLE `flownet_prod`.`AO_7CDE43_EVENT` CONVERT TO CHARACTER SET DEFAULT                
 ALTER TABLE `flownet_prod`.`AO_7CDE43_FILTER_PARAM` CONVERT TO CHARACTER SET DEFAULT         
 ALTER TABLE `flownet_prod`.`AO_7CDE43_NOTIFICATION_SCHEME` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_7CDE43_NOTIFICATION` CONVERT TO CHARACTER SET DEFAULT         
 ALTER TABLE `flownet_prod`.`AO_7CDE43_RECIPIENT` CONVERT TO CHARACTER SET DEFAULT            
 ALTER TABLE `flownet_prod`.`AO_7CDE43_SERVER_CONFIG` CONVERT TO CHARACTER SET DEFAULT        
 ALTER TABLE `flownet_prod`.`AO_7CDE43_SERVER_PARAM` CONVERT TO CHARACTER SET DEFAULT         
 ALTER TABLE `flownet_prod`.`AO_92296B_AORECENTLY_VIEWED` CONVERT TO CHARACTER SET DEFAULT    
 ALTER TABLE `flownet_prod`.`AO_9412A1_AONOTIFICATION` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_9412A1_AOREGISTRATION` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_9412A1_AOTASK` CONVERT TO CHARACTER SET DEFAULT               
 ALTER TABLE `flownet_prod`.`AO_9412A1_AOUSER` CONVERT TO CHARACTER SET DEFAULT               
 ALTER TABLE `flownet_prod`.`AO_9412A1_USER_APP_LINK` CONVERT TO CHARACTER SET DEFAULT        
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_CUSTOM_EV_TYPES` CONVERT TO CHARACTER SET DEFAULT   
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_DISABLE_EV_TYPES` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_EVENTS_EXCL` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_EVENTS_INVITEES` CONVERT TO CHARACTER SET DEFAULT   
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_EVENTS` CONVERT TO CHARACTER SET DEFAULT            
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS_PRIV_GRP` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS_PRIV_USR` CONVERT TO CHARACTER SET DEFAULT  
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS_PROPS` CONVERT TO CHARACTER SET DEFAULT     
 ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`AO_A0B856_WEB_HOOK_LISTENER_AO` CONVERT TO CHARACTER SET DEFAULT 
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_ADMIN_MESSAGES` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_BOARD` CONVERT TO CHARACTER SET DEFAULT         
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_ITEM` CONVERT TO CHARACTER SET DEFAULT          
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_KEY` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_SECTION` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_VERSION` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_SPACE_SETTINGS` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_A3F2EF_SPACE_TYPES` CONVERT TO CHARACTER SET DEFAULT          
 ALTER TABLE `flownet_prod`.`AO_B1DBB9_VOTE` CONVERT TO CHARACTER SET DEFAULT                 
 ALTER TABLE `flownet_prod`.`AO_B68BCB_FORUM_ACTIVITY_SCORE` CONVERT TO CHARACTER SET DEFAULT 
 ALTER TABLE `flownet_prod`.`AO_B68BCB_FORUM_PERMISSION` CONVERT TO CHARACTER SET DEFAULT     
 ALTER TABLE `flownet_prod`.`AO_BAF3AA_AOINLINE_TASK` CONVERT TO CHARACTER SET DEFAULT        
 ALTER TABLE `flownet_prod`.`AO_BCCAB2_FIELD_CATEGORY` CONVERT TO CHARACTER SET DEFAULT       
 ALTER TABLE `flownet_prod`.`AO_BCCAB2_FIELD_OPTION` CONVERT TO CHARACTER SET DEFAULT         
 ALTER TABLE `flownet_prod`.`AO_BCCAB2_USER_FIELD` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`AO_DC98AE_AOHELP_TIP` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`AO_E52910_ACCESS` CONVERT TO CHARACTER SET DEFAULT               
 ALTER TABLE `flownet_prod`.`AO_E52910_CSUCONFIG` CONVERT TO CHARACTER SET DEFAULT            
 ALTER TABLE `flownet_prod`.`AO_E52910_CSUSESSION` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`AO_E52910_SUAUDITLOG` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`AO_E52910_SUPARAMETER` CONVERT TO CHARACTER SET DEFAULT          
 ALTER TABLE `flownet_prod`.`AO_EF9604_FEATURE_DISCOVERY` CONVERT TO CHARACTER SET DEFAULT    
 ALTER TABLE `flownet_prod`.`ATTACHMENTDATA` CONVERT TO CHARACTER SET DEFAULT                 
 ALTER TABLE `flownet_prod`.`ATTACHMENTS` CONVERT TO CHARACTER SET DEFAULT                    
 ALTER TABLE `flownet_prod`.`BANDANA` CONVERT TO CHARACTER SET DEFAULT                        
 ALTER TABLE `flownet_prod`.`BODYCONTENT` CONVERT TO CHARACTER SET DEFAULT                    
 ALTER TABLE `flownet_prod`.`CLUSTERSAFETY` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`CONFANCESTORS` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`CONFVERSION` CONVERT TO CHARACTER SET DEFAULT                    
 ALTER TABLE `flownet_prod`.`CONTENTPROPERTIES` CONVERT TO CHARACTER SET DEFAULT              
 ALTER TABLE `flownet_prod`.`CONTENT_LABEL` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`CONTENT_PERM_SET` CONVERT TO CHARACTER SET DEFAULT               
 ALTER TABLE `flownet_prod`.`CONTENT_PERM` CONVERT TO CHARACTER SET DEFAULT                   
 ALTER TABLE `flownet_prod`.`CONTENT` CONVERT TO CHARACTER SET DEFAULT                        
 ALTER TABLE `flownet_prod`.`cwd_application_address` CONVERT TO CHARACTER SET DEFAULT        
 ALTER TABLE `flownet_prod`.`cwd_application_attribute` CONVERT TO CHARACTER SET DEFAULT      
 ALTER TABLE `flownet_prod`.`cwd_application` CONVERT TO CHARACTER SET DEFAULT                
 ALTER TABLE `flownet_prod`.`cwd_app_dir_group_mapping` CONVERT TO CHARACTER SET DEFAULT      
 ALTER TABLE `flownet_prod`.`cwd_app_dir_mapping` CONVERT TO CHARACTER SET DEFAULT            
 ALTER TABLE `flownet_prod`.`cwd_app_dir_operation` CONVERT TO CHARACTER SET DEFAULT          
 ALTER TABLE `flownet_prod`.`cwd_directory_attribute` CONVERT TO CHARACTER SET DEFAULT        
 ALTER TABLE `flownet_prod`.`cwd_directory_operation` CONVERT TO CHARACTER SET DEFAULT        
 ALTER TABLE `flownet_prod`.`cwd_directory` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`cwd_group_attribute` CONVERT TO CHARACTER SET DEFAULT            
 ALTER TABLE `flownet_prod`.`cwd_group` CONVERT TO CHARACTER SET DEFAULT                      
 ALTER TABLE `flownet_prod`.`cwd_membership` CONVERT TO CHARACTER SET DEFAULT                 
 ALTER TABLE `flownet_prod`.`cwd_user_attribute` CONVERT TO CHARACTER SET DEFAULT             
 ALTER TABLE `flownet_prod`.`cwd_user_credential_record` CONVERT TO CHARACTER SET DEFAULT     
 ALTER TABLE `flownet_prod`.`cwd_user` CONVERT TO CHARACTER SET DEFAULT                       
 ALTER TABLE `flownet_prod`.`DECORATOR` CONVERT TO CHARACTER SET DEFAULT                      
 ALTER TABLE `flownet_prod`.`external_entities` CONVERT TO CHARACTER SET DEFAULT              
 ALTER TABLE `flownet_prod`.`external_members` CONVERT TO CHARACTER SET DEFAULT               
 ALTER TABLE `flownet_prod`.`EXTRNLNKS` CONVERT TO CHARACTER SET DEFAULT                      
 ALTER TABLE `flownet_prod`.`FOLLOW_CONNECTIONS` CONVERT TO CHARACTER SET DEFAULT             
 ALTER TABLE `flownet_prod`.`groups` CONVERT TO CHARACTER SET DEFAULT                         
 ALTER TABLE `flownet_prod`.`hibernate_unique_key` CONVERT TO CHARACTER SET DEFAULT           
 ALTER TABLE `flownet_prod`.`IMAGEDETAILS` CONVERT TO CHARACTER SET DEFAULT                   
 ALTER TABLE `flownet_prod`.`INDEXQUEUEENTRIES` CONVERT TO CHARACTER SET DEFAULT              
 ALTER TABLE `flownet_prod`.`KEYSTORE` CONVERT TO CHARACTER SET DEFAULT                       
 ALTER TABLE `flownet_prod`.`LABEL` CONVERT TO CHARACTER SET DEFAULT                          
 ALTER TABLE `flownet_prod`.`LIKES` CONVERT TO CHARACTER SET DEFAULT                          
 ALTER TABLE `flownet_prod`.`LINKS` CONVERT TO CHARACTER SET DEFAULT                          
 ALTER TABLE `flownet_prod`.`local_members` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`logininfo` CONVERT TO CHARACTER SET DEFAULT                      
 ALTER TABLE `flownet_prod`.`NOTIFICATIONS` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`os_group` CONVERT TO CHARACTER SET DEFAULT                       
 ALTER TABLE `flownet_prod`.`OS_PROPERTYENTRY` CONVERT TO CHARACTER SET DEFAULT               
 ALTER TABLE `flownet_prod`.`os_user_group` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`os_user` CONVERT TO CHARACTER SET DEFAULT                        
 ALTER TABLE `flownet_prod`.`PAGETEMPLATES` CONVERT TO CHARACTER SET DEFAULT                  
 ALTER TABLE `flownet_prod`.`PLUGINDATA` CONVERT TO CHARACTER SET DEFAULT                     
 ALTER TABLE `flownet_prod`.`remembermetoken` CONVERT TO CHARACTER SET DEFAULT                
 ALTER TABLE `flownet_prod`.`SPACEGROUPPERMISSIONS` CONVERT TO CHARACTER SET DEFAULT          
 ALTER TABLE `flownet_prod`.`SPACEGROUPS` CONVERT TO CHARACTER SET DEFAULT                    
 ALTER TABLE `flownet_prod`.`SPACEPERMISSIONS` CONVERT TO CHARACTER SET DEFAULT               
 ALTER TABLE `flownet_prod`.`SPACES` CONVERT TO CHARACTER SET DEFAULT                         
 ALTER TABLE `flownet_prod`.`TRACKBACKLINKS` CONVERT TO CHARACTER SET DEFAULT                 
 ALTER TABLE `flownet_prod`.`TRUSTEDAPPRESTRICTION` CONVERT TO CHARACTER SET DEFAULT          
 ALTER TABLE `flownet_prod`.`TRUSTEDAPP` CONVERT TO CHARACTER SET DEFAULT                     
 ALTER TABLE `flownet_prod`.`users` CONVERT TO CHARACTER SET DEFAULT                          
 ALTER TABLE `flownet_prod`.`user_mapping` CONVERT TO CHARACTER SET DEFAULT

But... It fails on line 35:
ERROR 1832 (HY000) at line 35: Cannot change column 'BELONG_SUB_CALENDAR_ID': used in a foreign key constraint 'fk_ao_950dc3_tc_custom_ev_types_belong_sub_calendar_id'

I dont if i should laugh or cry at this moment. In my world all tables are dumped if existing in the sql dump, so how can this much be wrong?!

Jonas Andersson added a comment - 26/Jan/2015 3:14 PM I am attempting to move our current production confluence instance to a new box, and ran into the error: "Detected tables with non-default collation. See https://confluence.atlassian.com/x/TABrFw " which doesnt make any sense since this very same sqldump that is currently running on our current production machine. As an Atlassian admin i am used to having to fiddle with things to get them work, even if they are a carbon-copy of the production-env. However, i followed this article to rectify it, and until step 5, no rows showed up that needed changing. Step 5 however, showed me a list of 126 tables, making me VERY curious to how this could ever worked on our production server. Never the less i created a script containing these 126 lines: ALTER TABLE `flownet_prod`.`AO_098293_ANNOUNCEMENT_ENTITY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_187CCC_SIDEBAR_LINK` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_21D670_WHITELIST_RULES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_38321B_CUSTOM_CONTENT_LINK` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_429490_RESPONSE` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_429490_SETTINGS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_42E351_HEALTH_CHECK_ENTITY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_4E5044_FORUM_ACTIVITY_SCORE` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_4E5044_FORUM_PERMISSION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_54C900_CONTENT_BLUEPRINT_AO` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_54C900_C_TEMPLATE_REF` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_54C900_SPACE_BLUEPRINT_AO` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_5F3884_FEATURE_DISCOVERY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_6384AB_DISCOVERED` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_6384AB_FEATURE_METADATA_AO` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_65B466_INFINIDEX_CONFIG` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_65B466_PATH_INFO_ENTITY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_6D345F_CUSTOM_LIBRARY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_6D345F_CUSTOM_SHAPE` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_6D345F_USER_ENTITY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_6D345F_USER_TO_LIBRARY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_7CDE43_EVENT` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_7CDE43_FILTER_PARAM` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_7CDE43_NOTIFICATION_SCHEME` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_7CDE43_NOTIFICATION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_7CDE43_RECIPIENT` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_7CDE43_SERVER_CONFIG` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_7CDE43_SERVER_PARAM` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_92296B_AORECENTLY_VIEWED` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_9412A1_AONOTIFICATION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_9412A1_AOREGISTRATION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_9412A1_AOTASK` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_9412A1_AOUSER` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_9412A1_USER_APP_LINK` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_CUSTOM_EV_TYPES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_DISABLE_EV_TYPES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_EVENTS_EXCL` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_EVENTS_INVITEES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_EVENTS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS_PRIV_GRP` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS_PRIV_USR` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS_PROPS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_950DC3_TC_SUBCALS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A0B856_WEB_HOOK_LISTENER_AO` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_ADMIN_MESSAGES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_BOARD` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_ITEM` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_KEY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_SECTION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_LAYOUT_VERSION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_SPACE_SETTINGS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_A3F2EF_SPACE_TYPES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_B1DBB9_VOTE` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_B68BCB_FORUM_ACTIVITY_SCORE` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_B68BCB_FORUM_PERMISSION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_BAF3AA_AOINLINE_TASK` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_BCCAB2_FIELD_CATEGORY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_BCCAB2_FIELD_OPTION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_BCCAB2_USER_FIELD` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_DC98AE_AOHELP_TIP` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_E52910_ACCESS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_E52910_CSUCONFIG` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_E52910_CSUSESSION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_E52910_SUAUDITLOG` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_E52910_SUPARAMETER` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`AO_EF9604_FEATURE_DISCOVERY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`ATTACHMENTDATA` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`ATTACHMENTS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`BANDANA` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`BODYCONTENT` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CLUSTERSAFETY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CONFANCESTORS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CONFVERSION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CONTENTPROPERTIES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CONTENT_LABEL` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CONTENT_PERM_SET` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CONTENT_PERM` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`CONTENT` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_application_address` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_application_attribute` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_application` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_app_dir_group_mapping` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_app_dir_mapping` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_app_dir_operation` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_directory_attribute` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_directory_operation` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_directory` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_group_attribute` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_group` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_membership` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_user_attribute` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_user_credential_record` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`cwd_user` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`DECORATOR` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`external_entities` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`external_members` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`EXTRNLNKS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`FOLLOW_CONNECTIONS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`groups` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`hibernate_unique_key` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`IMAGEDETAILS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`INDEXQUEUEENTRIES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`KEYSTORE` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`LABEL` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`LIKES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`LINKS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`local_members` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`logininfo` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`NOTIFICATIONS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`os_group` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`OS_PROPERTYENTRY` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`os_user_group` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`os_user` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`PAGETEMPLATES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`PLUGINDATA` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`remembermetoken` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`SPACEGROUPPERMISSIONS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`SPACEGROUPS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`SPACEPERMISSIONS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`SPACES` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`TRACKBACKLINKS` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`TRUSTEDAPPRESTRICTION` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`TRUSTEDAPP` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`users` CONVERT TO CHARACTER SET DEFAULT ALTER TABLE `flownet_prod`.`user_mapping` CONVERT TO CHARACTER SET DEFAULT But... It fails on line 35: ERROR 1832 (HY000) at line 35: Cannot change column 'BELONG_SUB_CALENDAR_ID': used in a foreign key constraint 'fk_ao_950dc3_tc_custom_ev_types_belong_sub_calendar_id' I dont if i should laugh or cry at this moment. In my world all tables are dumped if existing in the sql dump, so how can this much be wrong?!

Olli Nevalainen added a comment - 16/Dec/2013 9:43 PM

That's a good reason for it. But the error message could be more accurate, e.g. "your default engine is MyISAM"

Thank you for the feedback Matt. I changed to wording to "MyISAM is configured as the default storage engine in your database" in ~~CONF-31994~~.

Olli Nevalainen added a comment - 16/Dec/2013 9:43 PM That's a good reason for it. But the error message could be more accurate, e.g. "your default engine is MyISAM" Thank you for the feedback Matt. I changed to wording to "MyISAM is configured as the default storage engine in your database" in CONF-31994 .

MattS added a comment - 16/Dec/2013 4:30 PM

That's a good reason for it. But the error message could be more accurate, e.g. "your default engine is MyISAM"

MattS added a comment - 16/Dec/2013 4:30 PM That's a good reason for it. But the error message could be more accurate, e.g. "your default engine is MyISAM"

Olli Nevalainen added a comment - 15/Dec/2013 9:28 PM

Hi matt.doar,

The motivation for that behaviour is to ensure that any new tables that are added during or after the upgrade will use InnoDB too.

Olli Nevalainen added a comment - 15/Dec/2013 9:28 PM Hi matt.doar , The motivation for that behaviour is to ensure that any new tables that are added during or after the upgrade will use InnoDB too.

MattS added a comment - 13/Dec/2013 11:27 PM

Interesting to note that even if all your tables are InnoDB you may still get the error "Your database is using the MyISAM Storage Engine" if the default engine is MyISAM.

MattS added a comment - 13/Dec/2013 11:27 PM Interesting to note that even if all your tables are InnoDB you may still get the error "Your database is using the MyISAM Storage Engine" if the default engine is MyISAM.

James Green added a comment - 11/Sep/2013 2:38 PM

@_geo_ is almost there.

select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET DEFAULT;") as MySQLCMD from TABLES where TABLE_SCHEMA = "confluence" INTO OUTFILE '/tmp/confluence-fix.sql';

Then, when invoking mysql to read back the confluence-fix, include the confluence database name:
mysql -u root -p confluence < /tmp/confluence-fix.sql

James Green added a comment - 11/Sep/2013 2:38 PM @_ geo _ is almost there. select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET DEFAULT;") as MySQLCMD from TABLES where TABLE_SCHEMA = "confluence" INTO OUTFILE '/tmp/confluence-fix.sql'; Then, when invoking mysql to read back the confluence-fix, include the confluence database name: mysql -u root -p confluence < /tmp/confluence-fix.sql

Wiley Koepp added a comment - 28/Aug/2013 9:48 PM

Experiencing the same problem as Andrei Arkhipov. Advice?

Wiley Koepp added a comment - 28/Aug/2013 9:48 PM Experiencing the same problem as Andrei Arkhipov. Advice?

Paul Curren added a comment - 26/Aug/2013 1:52 AM - edited

QA Notes

In all cases we would expected Confluence start up to be blocked.

1) Configure the default storage-engine/table type to myisam.
2) Configure the default storage-engine to innoDB but configure one Confluence table to be using myisam.
Note: I think the message for this case should be altered to state more specifically that at least one table is configured to MyISAM. Currently it says "Your database is using the MyISAM Storage Engine..." which I think is slightly misleading.
3) Configure a single table with a different charset from the default character set.
4) Configure a single column within a table to use a differing character set.
5) Configure a single table with a different collation from the default.
6) Configure a single column with a different collation from the default.

Paul Curren added a comment - 26/Aug/2013 1:52 AM - edited QA Notes In all cases we would expected Confluence start up to be blocked. 1) Configure the default storage-engine/table type to myisam. 2) Configure the default storage-engine to innoDB but configure one Confluence table to be using myisam. Note : I think the message for this case should be altered to state more specifically that at least one table is configured to MyISAM. Currently it says "Your database is using the MyISAM Storage Engine..." which I think is slightly misleading. 3) Configure a single table with a different charset from the default character set. 4) Configure a single column within a table to use a differing character set. 5) Configure a single table with a different collation from the default. 6) Configure a single column with a different collation from the default.

Andrei Arkhipov added a comment - 17/Aug/2013 1:22 PM

I have tried the workaround but stuck at the end of the process. When I do step 5 of Workaround 2, which seems to be my case, I get list of 55 tables vulnerable.
I try to ALTER one of the tables and nothing happens - the same 55 tables appear on the list.
I look deeper into these tables and I see they have in each column CHARSET = utf8, while COLLATE_NAME = utf8_general_ci. Any attempt to change these collate_name to utf8_bin is unsuccessful, so far.

Any help?

Thanks!

Andrei Arkhipov added a comment - 17/Aug/2013 1:22 PM I have tried the workaround but stuck at the end of the process. When I do step 5 of Workaround 2, which seems to be my case, I get list of 55 tables vulnerable. I try to ALTER one of the tables and nothing happens - the same 55 tables appear on the list. I look deeper into these tables and I see they have in each column CHARSET = utf8, while COLLATE_NAME = utf8_general_ci. Any attempt to change these collate_name to utf8_bin is unsuccessful, so far. Any help? Thanks!

Olli Nevalainen added a comment - 16/Aug/2013 1:07 AM

Hi craig.castlemead,

This problem happens when the database contains tables with storage engine, character set or collation that are different from the default values for the database. While those inconsistencies don't generally prevent Confluence from running, they cause some Confluence upgrades to fail.

We are working on adding checks to Confluence to prevent it from starting up when it detects these inconsistencies, thus allowing to abort the upgrade and keep using the pre-upgrade version of Confluence temporarily. Unfortunately fixing instances with this problem will still require following the workaround instructions.

Note that the workaround can be performed on existing instances separately before Confluence 5.2.3 upgrade.

I hope this helps.

Olli Nevalainen added a comment - 16/Aug/2013 1:07 AM Hi craig.castlemead , This problem happens when the database contains tables with storage engine, character set or collation that are different from the default values for the database. While those inconsistencies don't generally prevent Confluence from running, they cause some Confluence upgrades to fail. We are working on adding checks to Confluence to prevent it from starting up when it detects these inconsistencies, thus allowing to abort the upgrade and keep using the pre-upgrade version of Confluence temporarily. Unfortunately fixing instances with this problem will still require following the workaround instructions. Note that the workaround can be performed on existing instances separately before Confluence 5.2.3 upgrade. I hope this helps.

Assignee:: Olli Nevalainen

Reporter:: Robert Chang

Affected customers:: 11 This affects my team

Watchers:: 24 Start watching this issue

Created:: 13/Aug/2013 11:51 PM

Updated:: 11/Oct/2018 8:40 AM

Resolved:: 27/Aug/2013 6:56 AM

Details

Description

The problem

Diagnosis

Workaround

Attachments

Issue Links

Forms

Activity

Collapse comment: Jonas Andersson added a comment - 26/Jan/2015 3:14 PM

Expand comment: Jonas Andersson added a comment - 26/Jan/2015 3:14 PM

Collapse comment: Olli Nevalainen added a comment - 16/Dec/2013 9:43 PM

Expand comment: Olli Nevalainen added a comment - 16/Dec/2013 9:43 PM

Collapse comment: MattS added a comment - 16/Dec/2013 4:30 PM

Expand comment: MattS added a comment - 16/Dec/2013 4:30 PM

Collapse comment: Olli Nevalainen added a comment - 15/Dec/2013 9:28 PM

Expand comment: Olli Nevalainen added a comment - 15/Dec/2013 9:28 PM

Collapse comment: MattS added a comment - 13/Dec/2013 11:27 PM

Expand comment: MattS added a comment - 13/Dec/2013 11:27 PM

Collapse comment: James Green added a comment - 11/Sep/2013 2:38 PM

Expand comment: James Green added a comment - 11/Sep/2013 2:38 PM

Collapse comment: Wiley Koepp added a comment - 28/Aug/2013 9:48 PM

Expand comment: Wiley Koepp added a comment - 28/Aug/2013 9:48 PM

Collapse comment: Paul Curren added a comment - 26/Aug/2013 1:52 AM, Edited by Paul Curren - 27/Aug/2013 6:39 AM

Expand comment: Paul Curren added a comment - 26/Aug/2013 1:52 AM, Edited by Paul Curren - 27/Aug/2013 6:39 AM

Collapse comment: Andrei Arkhipov added a comment - 17/Aug/2013 1:22 PM

Expand comment: Andrei Arkhipov added a comment - 17/Aug/2013 1:22 PM

Collapse comment: Olli Nevalainen added a comment - 16/Aug/2013 1:07 AM

Expand comment: Olli Nevalainen added a comment - 16/Aug/2013 1:07 AM

People

Dates