Results 1 to 9 of 9

Thread: How to delete accounts of corrupted mysql database

  1. #1
    Aleks is offline Active Member
    Join Date
    May 2007
    Location
    Europe
    Posts
    48
    Rep Power
    8

    Default How to delete accounts of corrupted mysql database

    Last night server crashed and some of mysql DB mailboxgroup got corrupted (missing few tables and one complete db)

    I've already dumped & import all databases in mysql because of zmdbintegrityreport (it fixed almost all problems except that mysql bug that can be found on forums)
    but the problem remains, incomplete databases - libexec/scripts/migrate20100913-Mysql51.pl returns some missing tables!

    I've decided to do a workaround since we want to migrate zimbra from 32bit to 64 environment (clean inst.) but until then...

    I can get accounts of corrupted mailboxgroup from zimbra.mailbox table but the problem is I can't remove them from Admin console nor zmprov so I could restore them from zimbra backup.
    What else can I do? What would be the proper way?
    Removing records from zimbra.mailbox, ldap etc?

  2. #2
    Krishopper is offline Dedicated Member
    Join Date
    Dec 2006
    Location
    Minneapolis MN
    Posts
    777
    Rep Power
    9

    Default

    What error are you getting when you attempt to remove them from the admin console or zmprov?
    01 Networks, LLC / Cybernetik.net
    Zimbra NE and OSS Cloud Hosting
    Shared Web Hosting
    Consulting Services

  3. #3
    Aleks is offline Active Member
    Join Date
    May 2007
    Location
    Europe
    Posts
    48
    Rep Power
    8

    Default

    From Admin console I get
    Message: system failure: fetching folder data for mailbox 56 ERROR: service.FAILURE Details:soap:Receiver
    and from CLI
    zimbra@mail:~> zmprov ra account@test.lan old_account@test.lan
    ERROR: service.FAILURE (system failure: fetching folder data for mailbox 56)
    This points out, that admin console and CLI both uses SOAP API in background to servlet engine which has broken mysql database for that individual mailbox group/s.
    It doesn't matter what operation I do to such accounts I aways get the same error message.

    That's why I'm asking, how can I manually remove such accounts from zimbra in proper way as would soap service (if worked correctly)?
    What should I be aware of when removing accounts and where should I start? Would removing records from mysql table be enough?

    PS: I need this as a workaround so I can restore from zimbra backup into clean mailbox with same account name

    Thanks

  4. #4
    Krishopper is offline Dedicated Member
    Join Date
    Dec 2006
    Location
    Minneapolis MN
    Posts
    777
    Rep Power
    9

    Default

    Your zmprov command is trying to rename the account, but your original message said you are trying to delete. Which is it?

    And when you get the error, check /opt/zimbra/log/mailbox.log and see what the details about the error are, there may be more hints.
    01 Networks, LLC / Cybernetik.net
    Zimbra NE and OSS Cloud Hosting
    Shared Web Hosting
    Consulting Services

  5. #5
    Aleks is offline Active Member
    Join Date
    May 2007
    Location
    Europe
    Posts
    48
    Rep Power
    8

    Default

    Quote Originally Posted by Krishopper View Post
    Your zmprov command is trying to rename the account, but your original message said you are trying to delete. Which is it?

    And when you get the error, check /opt/zimbra/log/mailbox.log and see what the details about the error are, there may be more hints.
    Sorry for misleading, didn't realize because of copy&paste
    It doesn't matter what "operation" is used on such account, I always get same error.


    Here are relevant logs after executing "zmprov da account@zimbra.lan"
    Code:
    2012-12-07 21:27:37,186 INFO  [btpool0-178://localhost:7071/service/admin/soap/AuthRequest] [ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] soap - AuthRequest
    2012-12-07 21:27:37,282 INFO  [btpool0-178://localhost:7071/service/admin/soap/GetAccountRequest] [name=zimbra;ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] soap - GetAccountRequest
    2012-12-07 21:27:37,282 INFO  [btpool0-178://localhost:7071/service/admin/soap/GetAccountRequest] [name=zimbra;ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] misc - delegated access: doc=GetAccount, authenticated account=zimbra, target account=account@zimbra.lan
    2012-12-07 21:27:37,600 INFO  [btpool0-178://localhost:7071/service/admin/soap/DeleteAccountRequest] [name=zimbra;ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] soap - DeleteAccountRequest
    2012-12-07 21:27:37,600 INFO  [btpool0-178://localhost:7071/service/admin/soap/DeleteAccountRequest] [name=zimbra;ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] misc - delegated access: doc=DeleteAccount, authenticated account=zimbra, target account=account@zimbra.lan
    2012-12-07 21:27:37,600 INFO  [btpool0-178://localhost:7071/service/admin/soap/DeleteAccountRequest] [name=zimbra;ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] index - Initialized Index for mailbox 56 directory: LuceneIndex{mbox=56, dir=org.apache.lucene.store.NIOFSDirectory@/opt/zimbra/index/0/56/index/0 lockFactory=org.apache.lucene.store.SingleInstanceLockFactory@833304} Analyzer=com.zimbra.cs.index.ZimbraAnalyzer@1af2bc
    2012-12-07 21:27:37,600 INFO  [btpool0-178://localhost:7071/service/admin/soap/DeleteAccountRequest] [name=zimbra;ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] cache - initializing folder and tag caches for mailbox 56
    2012-12-07 21:27:37,602 INFO  [btpool0-178://localhost:7071/service/admin/soap/DeleteAccountRequest] [name=zimbra;ip=127.0.0.1;ua=zmprov/7.1.4_GA_2568;] SoapEngine - handler exception
    com.zimbra.common.service.ServiceException: system failure: fetching folder data for mailbox 56
    ExceptionId:btpool0-178://localhost:7071/service/admin/soap/DeleteAccountRequest:1354912057602:cd91d64920f5e183
    Code:service.FAILURE
            at com.zimbra.common.service.ServiceException.FAILURE(ServiceException.java:253)
            at com.zimbra.cs.db.DbMailItem.getFoldersAndTags(DbMailItem.java:2102)
            at com.zimbra.cs.mailbox.Mailbox.loadFoldersAndTags(Mailbox.java:1535)
            at com.zimbra.cs.mailbox.Mailbox.beginTransaction(Mailbox.java:1239)
            at com.zimbra.cs.mailbox.Mailbox.beginTransaction(Mailbox.java:1190)
            at com.zimbra.cs.mailbox.Mailbox.getConfig(Mailbox.java:1263)
            at com.zimbra.cs.mailbox.Mailbox.open(Mailbox.java:458)
            at com.zimbra.cs.mailbox.MailboxManager.getMailboxById(MailboxManager.java:521)
            at com.zimbra.cs.mailbox.MailboxManager.getMailboxByAccountId(MailboxManager.java:369)
            at com.zimbra.cs.mailbox.MailboxManager.getMailboxByAccountId(MailboxManager.java:337)
            at com.zimbra.cs.mailbox.MailboxManager.getMailboxByAccount(MailboxManager.java:283)
            at com.zimbra.cs.mailbox.MailboxManager.getMailboxByAccount(MailboxManager.java:260)
            at com.zimbra.cs.service.admin.DeleteAccount.handle(DeleteAccount.java:73)
            at com.zimbra.soap.SoapEngine.dispatchRequest(SoapEngine.java:412)
            at com.zimbra.soap.SoapEngine.dispatch(SoapEngine.java:287)
            at com.zimbra.soap.SoapEngine.dispatch(SoapEngine.java:158)
            at com.zimbra.soap.SoapServlet.doWork(SoapServlet.java:303)
            at com.zimbra.soap.SoapServlet.doPost(SoapServlet.java:217)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
            at com.zimbra.cs.servlet.ZimbraServlet.service(ZimbraServlet.java:206)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:814)
            at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
            at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
            at com.zimbra.cs.servlet.SetHeaderFilter.doFilter(SetHeaderFilter.java:79)
            at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
            at org.mortbay.servlet.UserAgentFilter.doFilter(UserAgentFilter.java:81)
            at org.mortbay.servlet.GzipFilter.doFilter(GzipFilter.java:155)
            at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
            at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
            at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:218)
            at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
            at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
            at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:422)
            at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
            at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
            at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
            at org.mortbay.jetty.handler.rewrite.RewriteHandler.handle(RewriteHandler.java:230)
            at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
            at org.mortbay.jetty.handler.DebugHandler.handle(DebugHandler.java:77)
            at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
            at org.mortbay.jetty.Server.handle(Server.java:326)
            at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:585)
            at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:988)
            at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
            at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
            at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:415)
            at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:429)
            at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:451)
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mboxgroup56.mail_item' doesn't exist
    
    Query being executed when exception was thrown:
    SELECT mi.id, mi.type, mi.parent_id, mi.folder_id, mi.index_id, mi.imap_id, mi.date, mi.size, mi.volume_id, mi.blob_digest, mi.unread, mi.flags, mi.tags, mi.subject, mi.name, mi.metadata, mi.mod_metadata, mi.change_date, mi.mod_content FROM mboxgroup56.mail_item AS mi WHERE mailbox_id = 56 AND type IN (1,2,13,3)
    
    
            at sun.reflect.GeneratedConstructorAccessor169.newInstance(Unknown Source)
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
            at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
            at com.mysql.jdbc.Util.getInstance(Util.java:384)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
            at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
            at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
            at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
            at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
            at com.zimbra.cs.db.DebugPreparedStatement.executeQuery(DebugPreparedStatement.java:156)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
            at com.zimbra.cs.db.DbMailItem.getFoldersAndTags(DbMailItem.java:1972)
            ... 46 more
    Exception is thrown because "mboxgroup56" database doesn't exist anymore in /opt/zimbra/db/data after crash
    Second account is similar but have only partial tables/db files in "mboxgroup55"

    So question still remains - how can I remove such corrupted accounts manually from rest of the system so I can restore from backup?

    PS: it's temporarily solved with split domain, LDAP entries seems fine...

    Thanks

  6. #6
    Krishopper is offline Dedicated Member
    Join Date
    Dec 2006
    Location
    Minneapolis MN
    Posts
    777
    Rep Power
    9

    Default

    Try "zmprov -l deleteAccount user@domain.com"

    That will go straight to LDAP to get it out of the system.

    Make sure you can get it out of LDAP before you do anything inside of the mailbox SQL.
    01 Networks, LLC / Cybernetik.net
    Zimbra NE and OSS Cloud Hosting
    Shared Web Hosting
    Consulting Services

  7. #7
    Aleks is offline Active Member
    Join Date
    May 2007
    Location
    Europe
    Posts
    48
    Rep Power
    8

    Default

    Interesting, seems reasonable to clean LDAP and then mySql entries to get rid of account from system.
    It doesn't matter if some messages get left behind in message store as long I will be able recreate account from backup.

    Will try over the weekend after a good backup and report back.

    Thanks for now Krishopper!

  8. #8
    Krishopper is offline Dedicated Member
    Join Date
    Dec 2006
    Location
    Minneapolis MN
    Posts
    777
    Rep Power
    9

    Default

    As long as you delete from LDAP, you should be able to recreate from backup. There is a chance that something might be left in the zimbra.mailbox table within MySQL referencing the account, which may (or may not) throw off the backup. If that happens, feel free to post again and let me know what error you receive from the restore (mailbox.log) and I can guide you in the right direction.
    01 Networks, LLC / Cybernetik.net
    Zimbra NE and OSS Cloud Hosting
    Shared Web Hosting
    Consulting Services

  9. #9
    Aleks is offline Active Member
    Join Date
    May 2007
    Location
    Europe
    Posts
    48
    Rep Power
    8

    Default

    Hi, just some feedback for others...
    I've removed accounts from LDAP and created them back in admin console - this worked now! So theoretically restore should work.
    Because of split domain, users have been migrated to another server so I didn't need to restore their mailboxes.

    What I did additionaly was droping mysql mboxgrups for these old accounts and recreate them with empty table structures.
    The reason was, some scripts failed because they're querying for data but got back errors because of corrupted databases or missing tables.
    Btw, I didn't delete those old records in zimbra.mailbox table (didn't had any issues with them).

    So far so good.
    This thread can be marked as solved
    Last edited by Aleks; 12-15-2012 at 04:30 PM. Reason: Solved!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Corrupted database cause mailbox to halt
    By simba5140 in forum Migration
    Replies: 3
    Last Post: 06-04-2012, 03:29 PM
  2. MYSQL database error
    By 3RiversTechAdmin in forum Administrators
    Replies: 2
    Last Post: 03-08-2009, 05:24 PM
  3. where is the postion of mysql database?
    By antelopeming in forum Developers
    Replies: 0
    Last Post: 05-23-2008, 03:08 AM
  4. MySQL database doesn't seem to want to get initialized
    By carllerche in forum Installation
    Replies: 2
    Last Post: 04-05-2007, 04:21 PM

Posting Permissions

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