MySQL Bugs: #17937: index entry update error in innodb table

Bug #17937	index entry update error in innodb table
Submitted:	5 Mar 2006 13:54	Modified:	7 Mar 2006 7:45
Reporter:	li zhan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Red Hat Linux release 8.0 (Psych)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
We have a table named useracct, a script will delete some records in this table everyday morning, but today's morning we saw the following message in error log.

Where is wrong? The table or the mysql self?

InnoDB: error in sec index entry update in
InnoDB: index username of table `XX_User/useracct`
InnoDB: tuple DATA TUPLE: 2 fields;
 0: len 32; hex a1efbfc9bfdabfc9c0d6a1ed2020202020202020202020202020202020202020; asc                                 ;; 1: len 4; hex 0006b7df; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 2; 1-byte offsets; info bits 0
 0: len 30; hex a3a8cfe3b8dba3a9c1f9bacfb2cab9abcbbe202020202020202020202020; asc                               ;...(truncated); 1: len 4; hex 0003b98d; asc     ;;

TRANSACTION 0 3540803286, ACTIVE 0 sec, process no 2590, OS thread id 180236 updating or deleting
mysql tables in use 1, locked 1
94 lock struct(s), heap size 11584, undo log entries 65
MySQL thread id 42, query id 15834547 updating
DELETE FROM XX_User.useracct  WHERE  userid = '440287'

InnoDB: Submit a detailed bug report to http://bugs.mysql.com
InnoDB: error in sec index entry update in
InnoDB: index username of table `XX_User/useracct`
InnoDB: tuple DATA TUPLE: 2 fields;
 0: len 32; hex d5c5baeac0f72020202020202020202020202020202020202020202020202020; asc                                 ;; 1: len 4; hex 0006b7e6; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 2; 1-byte offsets; info bits 0
 0: len 30; hex d5bfbdadcad0b8dbbae3bbf5d4cbb7fecef1d3d0cfdeb9abcbbe20202020; asc                               ;...(truncated); 1: len 4; hex 0006bed6; asc     ;;

TRANSACTION 0 3540803300, ACTIVE 0 sec, process no 2590, OS thread id 180236 updating or deleting
mysql tables in use 1, locked 1
79 lock struct(s), heap size 11584, undo log entries 3
MySQL thread id 42, query id 15834584 updating
DELETE FROM XX_User.useracct  WHERE  userid = '440294'

InnoDB: Submit a detailed bug report to http://bugs.mysql.com

How to repeat:
I don't konw how to repeat it, this is the first time for me to see this error message.

Li Zhan,

please run CHECK TABLE on the associated table, and attach the COMPLETE, UNEDITED .err log to this bug report. Do not omit anything, put the .err file as you have it.

Regards,

Heikki

Please see the attached file for full error log.

And the result of check is:
mysql> check table useracct;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| XX_User.useracct | check | error    | Corrupt  |
+------------------+-------+----------+----------+

It seem that the table is corrupted. But it is an innodb table, Mysql manual doesn't tell us how to repair innodb tables.

Li Zhan,

you upgraded from 3.23 or 4.0 to 5.0.

It may be a problem in character sets:

060303 22:15:57 [Warning] './SF_User/userinfo' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

You can probably fix the problem simply by rebuilding the table:

ALTER TABLE SF_user.userinfo ENGINE = InnoDB;

But beware: if the table is big (many gigabytes), then the rebuild can take hours or days.

Regards,

Heikki

I see...
After rebuild, the table seems OK:

+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| XX_User.useracct | check | status   | OK       |
+------------------+-------+----------+----------+

Thanks a lot.