My Confluence not startup after try change database Collation in MySQL

[Matti Kiviharju]

My Confluence not startup after try change database Collation in MySQL.

I ran this bash code in command line for to try to fix collation:

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8"
[ -n "$COLL" ] || COLL="utf8_bin"

echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql -uroot -ppassword

echo "USE $DB; SHOW TABLES;" | mysql -s -uroot -ppassword| (
 while read TABLE; do
 echo $DB.$TABLE
 echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql -uroot -ppassword $DB 
 done
)

How to fix this bash script to get Conflunence to startup again?

[Shannon S]

Hi Matti,

In order to properly fix the collation, you'll want to make sure that you've completed exactly the same steps as mentioned on our article:

• How to Fix the Collation and Character Set of a MySQL Database

The Table of Contents at the bottom discusses the set of changes you will need to perform.

Can you be sure that you edited the collation on both a table, column, and database level? 

Thank you!

Regards,

Shannon

[Matti Kiviharju]

Hi,

I do not success with this.

How to set Foreing Key Checks correctly?

[Shannon S]

Matti,

This is the way we recommend to set the collation of MySQL in Confluence. What issue did you have following the procedure?

When you ask about foreign key checks, are you having some sort of error message? Can you show us what you're seeing in the logs?

Shannon

[Matti Kiviharju]

Hi,

I just try to change collation with my phpMyAdmin and it not change all tables/columns collation to utf8_bin from utf8_general_ci. I am not sure am I placed foreing key checks to right place on SQL-scripts.

[Shannon S]

Matti,

Our suggestion is only to run the changes exactly the way they are outlined in these articles:

• MySQL Collation Repair: Database Level Changes
• MySQL Collation Repair: Table Level Changes
• MySQL Collation Repair: Column Level Changes
• MySQL Collation Repair: Column Level Encoding Issues
• MySQL Collation Repair: Case Study - Repairing a Production Database

Please review each individually and if you're having trouble you may want to reach out to your MySQL server administrator.

Regards,

Shannon

[Matti Kiviharju]

Thanks for this I ask from you if have a questions in future. Now my Confluence works fine because I restored old collation.

[Shannon S]

Happy to hear your Confluence is up and running!

Take care,

Shannon