[SOLVED] mysql errors upgrading Zimbra-4.5.10 to 5.00 or 5.04

[shinacalypse]

Hi,
Ive been having mysql errors when i try to upgrade Zimbra4.5.10 to anything higher. From what i can understand, its a bug in upgrading zimbra's mysql server to a newer version, but please tell me if i am wrong. Please find below the install log during the upgrade process:

Setting defaults from saved config in /opt/zimbra/.saveconfig/config.save
HOSTNAME=mx.example.com
LDAPHOST=mx.example.com
LDAPPORT=389
SNMPTRAPHOST=mx.example.com
SMTPSOURCE=admin@example.com
SMTPDEST=admin@example.com
SNMPNOTIFY=yes
SMTPNOTIFY=yes
LDAPROOTPW=Ntbvnb2y
LDAPZIMBRAPW=Ntbvnb2y
LDAPPOSTPW=zmpostfix
LDAPREPPW=zmreplica
LDAPAMAVISPW=zmamavis
Restoring existing configuration file from /opt/zimbra/.saveconfig/config.save...done
Operations logged to /tmp/zmsetup.05232008-203527.log
Starting ldap...done.
Setting defaults...done.
Upgrading from 4.5.10_GA_1575 to 5.0.4_GA_2101
Stopping zimbra services
Verifying /opt/zimbra/conf/my.cnf
Starting mysql
Starting logger mysql
Fri May 23 20:43:00 2008: SELECT value FROM config WHERE name = 'db.version'
This appears to be 4.5.10_GA
Redolog Version: 1.12 New Redolog Version: 1.22
Fri May 23 20:43:40 2008: Verified redolog version 1.12.
Fri May 23 20:43:40 2008: Updating Redolog schema version from 1.12 to 1.22.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
Fri May 23 20:44:35 2008: Error while running '/opt/zimbra/bin/mysql --user=zimbra --password=password_removed --database=zimbra --batch --skip-column-names'.

Please help, this is a production server and i only have the weekend to sort this out!

[phoenix]

Before you do the upgrade, can you check the /opt/zimbra/db/data/your_hostname.err and see if there's any errors in the log? I've also removed the mysql password you posted above.

[shinacalypse]

Oops.. sorry about the exposed password.. i should know better.

The error that appears most in the .err file (very big file) is

080523 11:01:35 InnoDB: Error: page 1 log sequence number 20 4035350367
InnoDB: is in the future! Current system log sequence number 20 4022856577.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: MySQL :: MySQL 5.0 Reference Manual :: 13.2.8.1 Forcing InnoDB Recovery
InnoDB: for more information.

I've edited the /opt/zimbra/conf/my.cnf to reference 127.0.0.1 instead of "localhost".. im trying an upgrade to 5.0.0 to see if it works..

[shinacalypse]

Didnt work i still have the same error as before:
Error while running '/opt/zimbra/bin/mysql --user=zimbra --password=****** --database=zimbra --batch --skip-column-names'.

[phoenix]

So, there's nothing else in there to indicate any corrupt tables? Would that be correct? Have you taken a full backup of your /opt/zimbra directories? If not do that and if you have no currput tables then try adding the force recover option in the innodb error message link. Please make sure you have a good backup. Is this a NE or OSS install?

[shinacalypse]

Uhh there wasnt anything to indicate corrupt tables, and i do have a full backup of zimbra. I actually have been attempting to upgrade without verifying message store databases integrity. Ive downloaded 5.0.6 and i chose this time to verify the message store databases.. and i got this output...

Verifying integrity of message store databases. This may take a while.
mysqld is alive
/opt/zimbra/mysql/bin/mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... CHANGED'
Generating report
Database errors found.
/opt/zimbra/mysql/bin/mysqlcheck --defaults-file=/opt/zimbra/conf/my.cnf -S /opt/zimbra/db/mysql.sock -A -C -s -u root --password=******* --auto-repair
imserver.jiveID
warning : 1 client is using or hasn't closed the table properly
imserver.jiveOffline
warning : 1 client is using or hasn't closed the table properly
imserver.jivePresence
warning : 1 client is using or hasn't closed the table properly
imserver.jivePrivate
warning : 1 client is using or hasn't closed the table properly
imserver.jiveProperty
warning : 1 client is using or hasn't closed the table properly
imserver.jiveRoster
warning : 1 client is using or hasn't closed the table properly
imserver.jiveRosterGroups
warning : 1 client is using or hasn't closed the table properly
imserver.jiveVCard
warning : 1 client is using or hasn't closed the table properly
command failed

.. what does this mean? the upgrade is continuing.. but will it work this time around?

Sina

[shinacalypse]

Hi.. i've sorted it out.. it was a corrupt database table in zimbra-4.5.10..

I tried running /opt/zimbra/mysql/bin/mysqlcheck --defaults-file=/opt/zimbra/conf/my.cnf -S /opt/zimbra/db/mysql.sock -A -C -s -u root --password=**** --auto-repair
manually but it still failed.
Googling thru mysql docs online led me to adding "innodb_force_recovery = 4" to the [mysqld] section of the /opt/zimbra/conf/my.cnf file,
starting and stopping zimbra, then i "#"-ed out the "innodb_force_recovery = 4" line again, and restarted zimbra normally.

I now ran the upgrade and it ran smoothly,.. i upgraded from 4.5.10 to the current 5.0.6 latest edition.

Its up and running now, but i shall be watching it closely in the days to come. somehow i dont think i'll have any problems

I hope this helps someone else..

cheers