Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: mysql crash (stack trace) - corrupt backup

  1. #1
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default SOLVED: mysql crash (stack trace) - corrupt backup

    Hey

    I was able to recover my Zimbra installation from a dying harddisk (RAID1 - both disks were affected and they were not sync).

    I did a fresh install of Zimbra GA 8.03 on a new machine just to be sure everything is ok - it worked.
    Then I replace /opt/zimbra with my restored backup from the failing RAID system. Of course it didn't work immediately so I ran install.sh again just to be sure.
    Now I hit following error in log/mysql_error.log:


    130526 1:36:00 InnoDB: Waiting for the background threads to start
    130526 1:36:01 InnoDB: 1.1.8 started; log sequence number 2268250335
    130526 1:36:01 [Note] Server hostname (bind-address): 'localhost'; port: 7306
    130526 1:36:01 [Note] - 'localhost' resolves to '127.0.0.1';
    130526 1:36:01 [Note] Server socket created on IP: '127.0.0.1'.
    130526 1:36:01 InnoDB: Error: page 1 log sequence number 2368742154
    InnoDB: is in the future! Current system log sequence number 2268250335.
    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.5 Reference Manual :: 14.3.7.2 Forcing InnoDB Recovery
    InnoDB: for more information.
    130526 1:36:02 InnoDB: Error: page 3 log sequence number 2373218620
    InnoDB: is in the future! Current system log sequence number 2268250335.
    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.5 Reference Manual :: 14.3.7.2 Forcing InnoDB Recovery
    InnoDB: for more information.
    130526 1:36:02 InnoDB: Error: page 16 log sequence number 2378797448
    InnoDB: is in the future! Current system log sequence number 2268250335.
    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.5 Reference Manual :: 14.3.7.2 Forcing InnoDB Recovery
    InnoDB: for more information.
    tcmalloc: large alloc 18446744073709543424 bytes == (nil) @
    23:36:02 UTC - mysqld got signal 11 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    We will try our best to scrape up some info that will hopefully help
    diagnose the problem, but since we have already crashed,
    something is definitely wrong and this may fail.

    key_buffer_size=8388608
    read_buffer_size=1048576
    max_used_connections=0
    max_threads=110
    thread_count=0
    connection_count=0
    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 234725 K bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.

    Thread pointer: 0x0
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong...
    stack_bottom = 0 thread_stack 0x40000
    /opt/zimbra/mysql/bin/mysqld(my_print_stacktrace+0x29)[0x7a3cd9]
    /opt/zimbra/mysql/bin/mysqld(handle_fatal_signal+0x367)[0x681127]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fb6b2173cb0]
    /opt/zimbra/mysql/bin/mysqld[0x89fe0d]
    /opt/zimbra/mysql/bin/mysqld[0x89ff21]
    /opt/zimbra/mysql/bin/mysqld[0x8a02ad]
    /opt/zimbra/mysql/bin/mysqld[0x7fa491]
    /opt/zimbra/mysql/bin/mysqld[0x7fa509]
    /opt/zimbra/mysql/bin/mysqld[0x7fa71b]
    /opt/zimbra/mysql/bin/mysqld[0x7e4763]
    /opt/zimbra/mysql/bin/mysqld[0x8c5d15]
    /opt/zimbra/mysql/bin/mysqld[0x819b71]
    /opt/zimbra/mysql/bin/mysqld[0x7d7403]
    /opt/zimbra/mysql/bin/mysqld[0x8c5e44]
    /opt/zimbra/mysql/bin/mysqld[0x8c63ce]
    /opt/zimbra/mysql/bin/mysqld[0x8c6b78]
    /opt/zimbra/mysql/bin/mysqld[0x8bc6ed]
    /opt/zimbra/mysql/bin/mysqld[0x7f5367]
    /opt/zimbra/mysql/bin/mysqld[0x7e5cd2]
    /opt/zimbra/mysql/bin/mysqld[0x7ea960]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fb6b216be9a]
    /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fb6b07e8ccd]
    The manual page at MySQL :: MySQL 5.6 Reference Manual :: C.5.4.2 What to Do If MySQL Keeps Crashing contains
    information that should help you find out what is causing the crash.
    130526 01:36:02 mysqld_safe mysqld from pid file /opt/zimbra/db/mysql.pid ended


    Any ideas? Thanks in advance.


    EDIT: of course I tried to raise the innodb_force_recovery in my.cnf - it did not help, just more (detailed) errors occured in the log file...
    Last edited by juergenmw; 05-27-2013 at 01:23 PM.

  2. #2
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    Ok, I've raised the innodb_force_recovery from 1 to 6 and tried the recovery from http://wiki.zimbra.com/index.php?tit...Crash_Recovery

    The first part (show databases | grep mbox) delivers me 68 entries in /tmp/mysql.db.list.
    When I try to recover the single mboxgroup*.sql files using this commen:

    for db in `cat /tmp/mysql.db.list`; do ~/mysql/bin/mysqldump $db -S $mysql_socket -u root --password=$mysql_root_password > /tmp/mysql.sql/$db.sql; echo "Dumped $db"; done
    I can only get the first six files - dumping all the others cause following error:

    mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/opt/zimbra/db/mysql.sock' (2) when trying to connect
    Even if i raise the innodb_force_recovery up to 6 ...
    Last edited by juergenmw; 05-25-2013 at 06:24 PM.

  3. #3
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    ok - reason for that was that mysql dies for some mboxgroups and gets restarted again but not fast enough respaning before the next command is getting fired in the loop. So I added a "sleep 10" after the mysqldump:

    for db in `cat /tmp/mysql.db.list`; do ~/mysql/bin/mysqldump $db -S $mysql_socket -u root --password=$mysql_root_password > /tmp/mysql.sql/$db.sql; echo "Dumped $db"; sleep 10 ; done
    That helps but I still can not get all mboxgroups dumped.

    mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `open_conversation`': Lost connection to MySQL server during query (2013)
    So what can I do now with about 50 mboxgroup dumps out of 67 ???

  4. #4
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    I've imported all the sql files without any consideration if the mailboxgroup had an error when exporting - at least some sql was written in every file but I did not check it.
    The system starts now but of course I am getting error messages like

    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityCons traintViolationException: Duplicate entry '1-320' for key 'PRIMARY'
    I am a little bit lost here on what strategy is the best to continue. Just try and error at the moment.
    Any help?

  5. #5
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    In the meantime I am able to logon to admin console on 7071 - but no service is working.
    I've cleaned all mailboxgroups and imported a "plain" mysql structure for every mailboxgroup without any data.

    In Zimbra Admin I can see all user accounts - that means the LDAP is alright I guess, despite I just did a plain cp -av for the data directory.

  6. #6
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    Update: all services show "running" in GUI and zmstatus but it still does not work.
    I managed to get rid of all DB errors saying "... is in the future ..." but atm I am stuck with following message:



    com.zimbra.common.service.ServiceException: system failure: fetching folder data for mailbox 2
    ExceptionId:LmtpServer-1:1369577163101:dffe7a219d8ab0d9
    Code:service.FAILURE
    at com.zimbra.common.service.ServiceException.FAILURE (ServiceException.java:258)
    at com.zimbra.cs.db.DbMailItem.getFoldersAndTags(DbMa ilItem.java:2028)
    at com.zimbra.cs.mailbox.Mailbox.loadFoldersAndTags(M ailbox.java:1886)
    at com.zimbra.cs.mailbox.Mailbox.beginTransaction(Mai lbox.java:1522)
    at com.zimbra.cs.mailbox.Mailbox.beginTransaction(Mai lbox.java:1460)
    at com.zimbra.cs.mailbox.Mailbox.getItemById(Mailbox. java:2445)
    at com.zimbra.cs.mailbox.Mailbox.getItemById(Mailbox. java:2437)
    at com.zimbra.cs.mailbox.Mailbox.getFolderById(Mailbo x.java:3472)
    at com.zimbra.cs.filter.IncomingMessageHandler.getDef aultFolderPath(IncomingMessageHandler.java:85)
    at com.zimbra.cs.filter.RuleManager.applyRulesToIncom ingMessage(RuleManager.java:365)
    at com.zimbra.cs.filter.RuleManager.applyRulesToIncom ingMessage(RuleManager.java:322)
    at com.zimbra.cs.lmtpserver.ZimbraLmtpBackend.deliver MessageToLocalMailboxes(ZimbraLmtpBackend.java:608 )
    at com.zimbra.cs.lmtpserver.ZimbraLmtpBackend.deliver (ZimbraLmtpBackend.java:382)
    at com.zimbra.cs.lmtpserver.LmtpHandler.processMessag eData(LmtpHandler.java:376)
    at com.zimbra.cs.lmtpserver.TcpLmtpHandler.continueDA TA(TcpLmtpHandler.java:73)
    at com.zimbra.cs.lmtpserver.LmtpHandler.doDATA(LmtpHa ndler.java:365)
    at com.zimbra.cs.lmtpserver.LmtpHandler.processComman d(LmtpHandler.java:181)
    at com.zimbra.cs.lmtpserver.TcpLmtpHandler.processCom mand(TcpLmtpHandler.java:66)
    at com.zimbra.cs.server.ProtocolHandler.processConnec tion(ProtocolHandler.java:188)
    at com.zimbra.cs.server.ProtocolHandler.run(ProtocolH andler.java:127)
    at java.util.concurrent.ThreadPoolExecutor.runWorker( ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:722)
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: Table 'mboxgroup2.mail_item' doesn't exist
    mailbox id 2 is the domain admin and it seems he does not have any entry in mail_item


    mysql> select id,comment from mailbox where id=2;
    +----+------------------+
    | id | comment |
    +----+------------------+
    | 2 | admin@xyz.xyz |
    +----+------------------+
    1 row in set (0.00 sec)


    use mailboxgroup2
    mysql> select * from mail_item;
    Empty set (0.00 sec)

    This happend since I was not able to export all DBs - 15 out of 65 mailbox exports "died".

  7. #7
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    ok, my system is up and running again - it seems to me I came across every single problem that can occur after a HD crash.
    I will briefly describe on my solutions for all the problems I had - hopefully others do benefit, but there is no guarantee! My installation was pretty F***ed up so maybe not everything was best practice but at least it works again

    1.) DB problem "InnoDB: is in the future! Current system log sequence number"
    THis problem was described above already but I'll give more info about the details. First of all the provided link Mysql Crash Recovery - Zimbra :: Wiki describes everything needed but I had to do a little bit more after I understood what happened. I did the following steps:

    zmcontrol stop

    # raise innodb_force_recovery level in /opt/zimbra/conf/my.cnf
    mysql.server start

    # increase the innodb_force_recovery level until the DB starts - 6 is max.
    # then start an export as described in the link
    # I came across the issue that the if a query killed the server it was not able to respawn fast enough to process the next run in the for loop.
    # The error messages
    # so I added a "sleep 5"

    for db in `cat /tmp/mysql.db.list`; do ~/mysql/bin/mysqldump $db -S $mysql_socket -u root --password=$mysql_root_password > /tmp/mysql.sql/$db.sql; echo "Dumped $db"; sleep 5 ; done

    # this gave me clean exports for about 50 of my 66 accounts (yep, small installation).
    After I exported them all and dropped the mboxgroup and the zimbra tables the "is in future error" still appeared on startup which told me that the error has to be somewhere inside the "native" mysql tables.
    So I followed the advice in the link above to completly recreate the mysql db:

    Code:
    mysql.server stop
    rm -rf  /opt/zimbra/db/data
    mkdir /opt/zimbra/db/data 
    
    #Remove the innodb_force_recovery line from /opt/zimbra/conf/my.cnf . 
    
    /opt/zimbra/libexec/zmmyinit --sql_root_pw $mysql_root_password  # recreate the standard db 
    
    mysql.server start

    no more error messages on startup - a good base to reimport user accounts.



    2.) Not all user accounts exported from DB
    The mysqldump used in the script above died on some mboxgroup DBs - even if I set innodb_force_recovery = 6 I was not able to export them all cleanly.
    The mboxgroup*.sql file sometimes had the table description for 10 tables, sometimes only for 9, some for 14 which seems to be the default. When I imported them zimbra complained about a missing table in mboxgroup2 (which is my admin user's mailboxgroup)

    com.zimbra.common.service.ServiceException: system failure: fetching folder data for mailbox 2
    ExceptionId:LmtpServer-2:1369571262822:a4f6c4b43f40ee80
    Code:service.FAILURE

    ...
    ..

    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: Table 'mboxgroup2.mail_item' doesn't exist
    mysql -e "check table mboxgroup2.mail_item"
    +----------------------+-------+----------+--------------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +----------------------+-------+----------+--------------------------------------------+
    | mboxgroup2.mail_item | check | Error | Table 'mboxgroup2.mail_item' doesn't exist |
    | mboxgroup2.mail_item | check | status | Operation failed |
    +----------------------+-------+----------+--------------------------------------------+
    This caused a lot of stracktraces in the logs so I thought it might be a good idea to let all mboxgroups have a correct DB scheme - without any data in the beginning of course.
    So I created an sql file which created all tables and did not insert any data - "/tmp/empty_mboxgroup.sql" The file is too big to post it here but you can simply create your own - just copy an exported mboxgroup##.sql file, make sure it has 14 "CREATE" and remove every INSERT INTO statements.

    Then I simply created all mboxgroup DBs

    Code:
    for db in `cat /tmp/mysql.db.list`
    do
        mysql -e "create database $db character set utf8"
        echo "Created $db"
    done
    and import "empty_mboxgroup.sql" into every mboxgroup

    Code:
    for db in `cat /tmp/mysql.db.list`
    do
        mysql `basename $sql .sql` < /tmp/empty_mboxgroup.sql
        echo "Created $db"
    done
    Now I had 66 mboxgroups in the DB - with valid tables (but still no data).
    Last edited by juergenmw; 05-27-2013 at 01:22 PM.

  8. #8
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    3.) com.zimbra.cs.mailbox.MailServiceException$NoSuchI temException: no such folder id: 2
    Of course all my mailboxgroups now where correct but empty ... no data. It seemed that zimbra needed at least some content in mail_item to start (see error in heading).
    So I imported the data I got from the export. I knew that some export files were incomplete (only 8,9,10... of 14 tables) but I did not care - try and error!

    The bad info here was that the export of about 10+ DBs failed and I did not get any mail_item. As supposed above Zimbra needs them though.
    My first mboxgroup with a faulty export was mboxgroup2 - the admin's mboxgroup ... so where do I get it from???

    My next idea was not to use mysqldump for the whole DB - it seemed a good idea to export all tables separately and if I was lucky I could get mail_item for all the users.
    Lets start - I had to backup my new, clean DB and move back the old DB corrupt one.

    Code:
    zmcontrol stop
    mysql.server stop
    cd /opt/zimbra/db
    mv data data_new
    mv data_corrupt data
    
    #  raise innodb_force_recovery level in /opt/zimbra/conf/my.cnf
    mysql.server start
    To do it automated for all (!) of my mboxgroups I put all table names to a temporary file /tmp/mbox_tables

    Code:
    appointment
    appointment_dumpster
    data_source_item
    imap_folder
    imap_message
    mail_item
    mail_item_dumpster
    open_conversation
    pop3_message
    revision
    revision_dumpster
    tag
    tagged_item
    tombstone
    and used a small bash loop to export em all (took about 20 mins):

    Code:
    for box in `cat /tmp/mysql.db.list` ; 
        do mkdir /tmp/table_dump_$box ; 
        for i in `cat /tmp/mbox_tables` ; 
            do ~/mysql/bin/mysqldump -S $mysql_socket -u root --password=$mysql_root_password $box $i >  /tmp/table_dump_$box/$i.sql ; 
            sleep 3 ;
        done ;
    done
    Here I have to state I was really lucky - I was able to get the mail_item for ALL users!
    The faulty table was mostly the appointment which let the complete mysqldump fail - but a single table export worked for all the other data.
    I got the best result with innodb_force_recovery = 6 - it gave me as twice as much data compared to innodb_force_recovery = 3.
    Dunno if it is wise to always use the highest level but I tried them all and for me it was the best result.

    Now bring the "good" DB back in place and start the DB

    Code:
    mysql.server stop
    cd /opt/zimbra/db
    mv data data_corrupt 
    mv data_new data
    
    #  remove raise innodb_force_recovery level in /opt/zimbra/conf/my.cnf
    mysql.server start
    I imported the single table exports into the appropriate mboxgroup DBs and tried to start Zimbra afterwards.


    4.) Duplicate Key entry

    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityCons traintViolationException: Duplicate entry '48-4180' for key 'PRIMARY'

    Query being executed when exception was thrown:
    INSERT INTO mboxgroup48.mail_item
    or

    Code:mail.ALREADY_EXISTS ArgitemId, IID, ...
    This was pretty easy - I mainly followed this thread: https://www.zimbra.com/forums/admini...unctional.html

    Here are two SQL commands that helped me to check if the zimbra table has a lower id than the newest mail_item


    Get all item_id_checkpoints from zimbra table:
    Code:
    zimbra -B -e "SELECT id, item_id_checkpoint FROM mailbox";
    Check all mboxgroups for the highest mail item ID
    Code:
    for i in `cat /tmp/mysql.db.list` ; do mysql $i -e "select mailbox_id,id  from mail_item order by id desc limit 1;" ; done
    If the zimbra.mailbox.item_id_checkpoint is lower than the mboxgroup.mail_item.id (compare for every id) then there MIGHT be a problem causing the message above.
    Now its pretty simple - I followed the instructions from the thread above, stopped zimbra, startet the db and updated the item_id_checkpoint to match the highest mboxgroup.mail_item.id.

    Code:
    update mailbox set item_id_checkpoint = <highest_id_from_mboxgroup.mail_item> where id = <mailboxid>;
    Last edited by juergenmw; 05-27-2013 at 02:23 PM.

  9. #9
    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    7

    Default

    5.) Unexpected blob

    Running the tool

    zmblobchk -m <mbox_id> start
    gave me a lot of unexpected blobs for many accounts. An unexpected blob is a message in the store without a DB entry.
    Most likely the mail_item DB entries couldn't be restored but the store itself still holds the *.msg file. This isn't a big problem but still not "ok" since the users might recognize they miss something.

    So what to do? My solution:

    1.) Search all unexpected blobs for all accounts using zmblobchk
    2.) Move them to a mbox-related directory
    3.) Create a "/backup" folder in the related users mailbox
    4.) If needed: Inject those files (I'll describe this later)
    5.) Inform the user and blame the hardware - it's never the admins fault


    Getting all unexpected blob files for one account:

    for i in `zmblobchk -m 50 start | cut -d " " -f 5 | tr -d ":"` ; do echo $i ; done
    Now lets do this for all accounts:

    # get all mail box ids
    mysql zimbra -B -e "SELECT id from mailbox" | grep -v id > /tmp/mbox_ids

    # get a list for all unexpected blobs in all mboxes and print em
    for id in `cat /tmp/mbox_ids` ; do echo "getting blobs for MBOX $id" ; for i in `zmblobchk -m $id start | grep unexpected | cut -d " " -f 5 | tr -d ":"` ; do echo $i ; done ; done

    # now create a backup dir and move all the blobs to mbox related (id) subdirs
    mkdir /tmp/unexpected_blobs

    # dry run with echo
    for id in `cat /tmp/mbox_ids` ; do echo "getting blobs for MBOX $id" ; mkdir /tmp/unexpected_blobs/$id ; for i in `zmblobchk -m $id start | grep unexpected | cut -d " " -f 5 | tr -d ":"` ; do echo "mv $i /tmp/unexpected_blobs/$id" ; done ; done

    #move them
    for id in `cat /tmp/mbox_ids` ; do echo "getting blobs for MBOX $id" ; mkdir /tmp/unexpected_blobs/$id ; for i in `zmblobchk -m $id start | grep unexpected | cut -d " " -f 5 | tr -d ":"` ; do mv $i /tmp/unexpected_blobs/$id ; done ; done
    Since my Ubuntu installation purges /tmp on startup I moved /tmp/unexpected_blobs/ to some other (backup) directory
    mv /tmp/unexpected_blobs/ /shared/backup/
    Now we achieved the goal: no more unexpected blobs in any mailbox and all msg files stored somewhere else ready to being injected to the users mbox ...

  10. #10
    Join Date
    Jun 2011
    Location
    Caracas Venezuela
    Posts
    476
    Rep Power
    4

    Default

    juergenmw, thanks for share.

    A very good sysadmin.

    ccelis.

Similar Threads

  1. Replies: 13
    Last Post: 03-25-2010, 07:37 AM
  2. Disable hidden stack trace on failed login
    By rhouston in forum Administrators
    Replies: 0
    Last Post: 03-12-2010, 03:15 PM
  3. HTC Magic sync ==> stack trace
    By perbu in forum Zimbra Mobile
    Replies: 12
    Last Post: 08-12-2009, 03:34 AM
  4. [SOLVED] Crash disk and mboxgroup corrupt
    By elibre in forum Administrators
    Replies: 2
    Last Post: 04-12-2009, 02:42 AM
  5. MySQL DB corrupt
    By Urs in forum Installation
    Replies: 3
    Last Post: 11-03-2008, 05:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •