How to delete accounts of corrupted mysql database

[Aleks]

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?

[Krishopper]

What error are you getting when you attempt to remove them from the admin console or zmprov?

[Aleks]

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

[Krishopper]

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.

[Aleks]

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"

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

[Krishopper]

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.

[Aleks]

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!

[Krishopper]

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.

[Aleks]

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