How to recreate corrupted index (mysql).

How to recreate corrupted index (mysql)

   KB 23245        Last updated on 06/21/2017  




0.00
(0 votes)


Symptom

When a user is trying to login to WebUI using Ajax, the login gets stuck and is staying on the login page. Using the HTML version allowed the user to login. When he navigates to the Calendar tab, at the top of the calendar, there is an error message that says: A network error has occurred..


Error

While the user was logged in using HTML, he navigated to the Calendar tab. As he did that, we saw the following in the mailbox.log:

2017-06-21 15:18:13,781 INFO  [qtp509886383-119865:http://127.0.0.1:8080/service/soap/SearchRequest] 
[name=user@domain.com;mid=1141;oip=10.0.0.1;ua=zclient/8.6.0_GA_1200;] SoapEngine - handler exception 
com.zimbra.common.service.ServiceException: system failure: fetching calendar items for mailbox 1141 
ExceptionId:qtp509886383-119865:http://127.0.0.1:8080/service/soap/SearchRequest:1498051093780:d054eccc246e1e40 
Code:service.FAILURE 
       at com.zimbra.common.service.ServiceException.FAILURE(ServiceException.java:260)
       at com.zimbra.cs.db.DbMailItem.getCalendarItems(DbMailItem.java:3902)
       at com.zimbra.cs.mailbox.Mailbox.getCalendarItemsForRange(Mailbox.java:4537)

Caused by: java.sql.SQLException: Index mail_item is corrupted

Resolution

The next step was to login to mysql and check the mail_item table:

MariaDB [mboxgroup41]> check table mail_item;
+-----------------------+-------+----------+-------------------------------------------------------+
| Table                 | Op    | Msg_type | Msg_text                                              |
+-----------------------+-------+----------+-------------------------------------------------------+
| mboxgroup41.mail_item | check | Warning  | InnoDB: Index "i_mod_metadata" is marked as corrupted |
| mboxgroup41.mail_item | check | error    | Corrupt                                               |
+-----------------------+-------+----------+-------------------------------------------------------+ 
2 rows in set (0.05 sec)

We see the index "i_mod_metadata" is corrupted. To fix this we first drop this index:

MariaDB [mboxgroup41]> alter table mail_item drop index i_mod_metadata;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

And then we re-create it:

MariaDB [mboxgroup41]> alter table mail_item add i_mod_metadata  int(10) unsigned DEFAULT NULL, add index (i_mod_metadata);
Query OK, 0 rows affected (0.79 sec)
Records: 0  Duplicates: 0  Warnings: 0

Finally we check again:

MariaDB [mboxgroup41]> check table mail_item;
+-----------------------+-------+----------+----------+
| Table                 | Op    | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| mboxgroup41.mail_item | check | status   | OK       |
+-----------------------+-------+----------+----------+
1 row in set (0.11 sec)


Additional Information

Q: Where to find the commands for creating the index?
A: The simplest way to find out the creation statements for all tables in zimbra is by using this command:

MariaDB [(none)]> SHOW CREATE TABLE mboxgroup1.mail_item \G;



Verified Against: Zimbra Collaboration 8.7, 8.6, 8.0 Date Created: 21/06/2017
Article ID: https://wiki.zimbra.com/index.php?title=How_to_recreate_corrupted_index_(mysql). Date Modified: 06/21/2017



Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »


Wiki/KB reviewed by SME1 SME2 COPY EDITOR Last edit by Teodor Vizirov
Jump to: navigation, search