Purpose

This script will massconvert all tables to UTF-8 (needed from Confluence 5.2 and onwards):

REMEMBER TO TAKE BACKUP FIRST :-)

#!/bin/bash

all_tables=$(mysql -uconfluence -pxxxxxxxx confluence -e 'show tables')

for table in ${all_tables}; do

  echo "Altering ${table}"

  echo "ALTER TABLE confluence.${table} convert to character set utf8 collate utf8_bin" > /tmp/sql

  mysql -uconfluence -pxxxxxxxx  confluence < /tmp/sql

done

 

The script originates from https://confluence.atlassian.com/display/CONFKB/Change+MySQL+Database+Character+Encoding+to+UTF8+During+Server+Migration - Thanx to Dejan Golja

A sample run

from a Confluence 5.2 installation:

root@myserver:/opt# ./change.sql 
Altering Tables_in_confluence
ERROR 1146 (42S02) at line 1: Table 'confluence.Tables_in_confluence' doesn't exist
Altering AO_187CCC_SIDEBAR_LINK
Altering AO_9412A1_AONOTIFICATION
Altering AO_9412A1_AOREGISTRATION
Altering AO_9412A1_AOTASK
Altering AO_9412A1_AOUSER
Altering AO_9412A1_USER_APP_LINK
Altering ATTACHMENTDATA
Altering ATTACHMENTS
Altering BANDANA
Altering BODYCONTENT
Altering CLUSTERSAFETY
Altering CONFANCESTORS
Altering CONFVERSION
Altering CONTENT
Altering CONTENTPROPERTIES
Altering CONTENT_LABEL
Altering CONTENT_PERM
Altering CONTENT_PERM_SET
Altering DECORATOR
Altering EXTRNLNKS
Altering FOLLOW_CONNECTIONS
Altering IMAGEDETAILS
Altering INDEXQUEUEENTRIES
Altering KEYSTORE
Altering LABEL
Altering LIKES
Altering LINKS
Altering NOTIFICATIONS
Altering OS_PROPERTYENTRY
Altering PAGETEMPLATES
Altering PLUGINDATA
Altering SPACEGROUPPERMISSIONS
Altering SPACEGROUPS
Altering SPACEPERMISSIONS
Altering SPACES
Altering TRACKBACKLINKS
Altering TRUSTEDAPP
Altering TRUSTEDAPPRESTRICTION
Altering cwd_app_dir_group_mapping
Altering cwd_app_dir_mapping
Altering cwd_app_dir_operation
Altering cwd_application
Altering cwd_application_address
Altering cwd_application_attribute
Altering cwd_directory
Altering cwd_directory_attribute
Altering cwd_directory_operation
Altering cwd_group
Altering cwd_group_attribute
Altering cwd_membership
Altering cwd_user
Altering cwd_user_attribute
Altering cwd_user_credential_record
Altering external_entities
Altering external_members
Altering groups
Altering hibernate_unique_key
Altering local_members
Altering logininfo
Altering os_group
Altering os_user
Altering os_user_group
Altering remembermetoken
Altering users
root@myserver:/opt# 

 

Result:

Scheme before before.sql

Sceme after after.sql

The "interesting" part is that several tables change column types:

`BANDANAVALUE` mediumtext CHARACTER SET latin1,
`BANDANAVALUE` longtext COLLATE utf8_bin,

`VERSIONCOMMENT` mediumtext CHARACTER SET latin1,
`VERSIONCOMMENT` longtext COLLATE utf8_bin,


`BODY` mediumtext CHARACTER SET latin1,
`BODY` longtext COLLATE utf8_bin,


`KEYSPEC` text CHARACTER SET latin1 NOT NULL,
`KEYSPEC` mediumtext COLLATE utf8_bin NOT NULL,


`text_val` mediumtext CHARACTER SET latin1,
`text_val` longtext COLLATE utf8_bin,


`CONTENT` mediumtext CHARACTER SET latin1,
`CONTENT` longtext COLLATE utf8_bin,


`LICENSEKEY` text CHARACTER SET latin1,
`LICENSEKEY` mediumtext COLLATE utf8_bin,

The complete diff: diff.sql

Dont worry

This is a diff from a 5.2.5 instance - confluence5.2.5-dump.sql - And it has the same column types as after the script.