MySQL issues when updating Confluence 4.3 to 5.6

unknown

Dear community,

While trying to upgrade one of our largest Confluence instances from 4.3.7 to 5.6.6, we are running into major issues with MySQL. We have tried almost any approach we could find and know of, but are unable to get the upgrade done. I've detailed out our situation below and I'm hoping that someone would be willing to think along with us. Any help is highly appreciated.

Our main questions are: is there anything in this process that may cause the issues as described below and what can we do to regain a functioning database / successful update to Confluence 5.6

Thanks in advance for any hints or ideas that may help us to get this update done.

--- Jurriaan

 

Here is the approach that we used to perform the update:

OS: Windows server 2008 R2

1. Update confluence 4.3.7 to confluence 5.1.5
2. Update Mysql 5.0.96 to Mysql 5.1.73
3. Copy the data folder from Mysql 5.0.96 to  data folder of Mysql 5.1.73.
   1. Remove log files
4. Start Mysql 5.1.73
5. Run Mysql_upgrade.exe -uUSERNAME - PPASSWORD
6. This is giving some errors, some tables cannot be fix automatically:

The storage engine for the table
doesn't support repair instance11db.content_perm_set

The storage engine for the table
doesn't support repair instance11db.contentproperties

The storage engine for the table
doesn't support repair instance11db.cwd_app_dir_group_mapping

The storage engine for the table
doesn't support repair instance11db.cwd_app_dir_operation

Then:

1. Start Mysql 5.1.73 and confluence 5.1.5
2. The site and MYSQL are working fine.
3. Now we shutdown Confluence
4.  Altering the Database because of the collation error of Confluence with newer versions.
   1. See: https://confluence.atlassian.com/x/UAL_Jw
   2. Alter statements are ones like:

ALTER TABLE `ao_9412a1_aonotification`
MODIFY `ACTION_ICON_URL` longtext CHARACTER SET UTF8 COLLATE utf8_bin;

ALTER TABLE
`ao_9412a1_aonotification` MODIFY `DESCRIPTION` longtext CHARACTER SET UTF8
COLLATE utf8_bin;


ALTER TABLE
`ao_9412a1_aonotification` MODIFY `ICON_URL` longtext CHARACTER SET UTF8
COLLATE utf8_bin;

ALTER TABLE
`ao_9412a1_aonotification` MODIFY `ITEM_ICON_URL` longtext CHARACTER SET UTF8
COLLATE utf8_bin;ALTER TABLE `ao_9412a1_aonotification` MODIFY `ITE

Then:

1. The altering of the collation of the database, tables and collums succeeds, but:
2. After restarting the mysql server, The server wont start anymore, The following errors are found in the error.err file in the data folder:

Version:
'5.1.73-community'  socket: ''  port: 7306  MySQL Community
Server (GPL)

InnoDB: Error: trying to access
page number 3054769112 in space 0,

InnoDB: space name .\ibdata1,

InnoDB: which is outside the
tablespace bounds.

InnoDB: Byte offset 0, len
16384, i/o type 10.

InnoDB: If you get this error at
mysqld startup, please check that

InnoDB: your my.cnf matches the
ibdata files that you have in the

InnoDB: MySQL server.

150320 16:34:40  InnoDB:
Assertion failure in thread 7548 in file .\fil\fil0fil.c line 4135

InnoDB: We intentionally
generate a memory trap.

InnoDB: Submit a detailed bug
report to http://bugs.mysql.com.

InnoDB: If you get repeated
assertion failures or crashes, even

InnoDB: immediately after the
mysqld startup, there may be

InnoDB: corruption in the InnoDB
tablespace. Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

InnoDB: Thread 5884 stopped in
file
G:\mysql-5.1.73-winbuild\mysql-community-nt-5.1.73-build\storage\innobase\include\sync0sync.ic
line 115

InnoDB: Thread 4464 stopped in file
G:\mysql-5.1.73-winbuild\mysql-community-nt-5.1.73-build\storage\innobase\include\sync0sync.ic
line 115

The server starts when we put InnoDB in recoverymode 2, but then we cannot use the database anymore.

Nic Brough -Adaptavist-

Point 6 and the resultant error is where you need to start - you should have fixed them before starting Confluence (and it would have been better to have altered the collation before starting it as well).

I'd be tempted to take a different approach though - take a mysql dump of the old system, set up a new MySQL database on the right version and with the collation set correctly, and import the dump.  That bypasses the need for mysql upgrades which seem to be breaking things for you.