List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:September 12 2001 4:22pm
Subject:Re: error in sec index entry update
View as plain text  
Brian,

your table seems to get corrupt only
temporarily. It suggests I have a bug
in the interplay of UPDATE and purge.

Probably no need to reimport the table.

About keys: there is no need to define
the same key as the primary key and
a non-primary. Thus the key roster_ is
unnecessary.

Thanks for the info. I will now study
and test with a table of your kind, and
look what goes wrong.

Regards,

Heikki

At 12:05 PM 9/12/01 -0400, you wrote:
>Heikki,
>
>Here is the output from CHECK TABLE:
>
>mysql> check table roster;
>+------------------+-------+----------+----------+
>| Table            | Op    | Msg_type | Msg_text |
>+------------------+-------+----------+----------+
>| webassign.roster | check | status   | OK       |
>+------------------+-------+----------+----------+
>1 row in set (20.82 sec)
>
>Here was my original CREATE TABLE statement:
>
>create table roster (
>        class_id mediumint unsigned not null,
>        username char(50) not null,
>        user_id char(20) not null,
>        fullname char(50) not null,
>        user_end_datetime datetime default '0000-00-00 00:00:00' not
>null,
>        creation_date datetime default '0000-00-00 00:00:00' not null,
>        primary key (class_id, username),
>        key roster_ (class_id, username)
>) type = innodb;
>
>(Completely unrelated question but I've oft wondered if both the primary
>key and the key statements in create table are necesary. I've gotten
>into the habit of always using both but am I just adding overhead?)
>
>Here is the statement used to create the secondary index:
>
>create index user_ on roster (username);
>
>Also, here are the current indexes:
>
>mysql> show index from roster;
>+--------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+---------+
>| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
>Collation | Cardinality | Sub_part | Packed | Comment |
>+--------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+---------+
>| roster |          0 | PRIMARY  |            1 | class_id    |
>A         |        NULL |     NULL | NULL   |         |
>| roster |          0 | PRIMARY  |            2 | username    |
>A         |       98270 |     NULL | NULL   |         |
>| roster |          1 | roster_  |            1 | class_id    |
>A         |        NULL |     NULL | NULL   |         |
>| roster |          1 | roster_  |            2 | username    |
>A         |       98270 |     NULL | NULL   |         |
>| roster |          1 | user_    |            1 | username    |
>A         |       98270 |     NULL | NULL   |         |
>+--------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+---------+
>5 rows in set (0.03 sec)
>
>
>Let me know if there is any other information I can get for you.
>
>Thanks very much,
>Brian
>
>Heikki Tuuri wrote:
>> 
>> Brian,
>> 
>> the error message means that you updated a secondary
>> key of a row but InnoDB did not find the index entry
>> in the secondary index. The bug may be due to
>> an error in purge, insert buffer, or the buffer
>> pool. I am currently running tests on this and
>> studying it.
>> 
>> Could you please run CHECK TABLE on the table
>> webassign.rosterInnoDB and send the output to
>> me?
>> 
>> Also the CREATE TABLE statement would help in
>> hunting the bug.
>> 
>> You can repair your table by dropping it and
>> reimporting it, but the output of CHECK TABLE
>> should be seen first.
>> 
>> Regards,
>> 
>> Heikki
>> 
>> >Hello,
>> >
>> >I'm using mysql 3.23.40 with InnoDB tables. Recently I've been
>> >seeing several of the following messages in my logs related to indexes.
>> >InnoDB: error in sec index entry update in
>> >InnoDB: index user_ table webassign/rosterInnoDB: tuple  0: len 50; hex \
>> >646a61646f40727574676572732020202020202020202020202020202020202020202020202
>> 02020202020 \
>> >20202020202020; asc [username]                                     ;; 1:
>> len 3; hex \
>> >000cfa; asc ;; InnoDB: record RECORD: info bits 0 0: len 50; hex \
>> >646a61636f62736540756d642e756d696368202020202020202020202020202020202020202
>> 02020202020 \
>> >20202020202020; asc [username]                      ;; 1: len 3; hex
>> 000044; asc D;;
>> >I'm not sure what other information to give to describe the problem.
>> >I do not know exactly what caused the errors to appear nor how to
>> >duplicate the errors.
>> >I see no ill effects of the error. I have omitted the usernames reported
>> >in the error above but those users are still retrievable.
>> >Please let me know if more information is needed or if anyone else
>> >has experienced this problem.Thanks very much in advance,Brian Pittman
>> >System: SunOS 5.6 Generic_105181-17 sun4u sparc
SUNW,Ultra-4Architecture: sun4
>> >Some paths:  /bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc
>> >GCC: Reading specs from
>> /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.95.3/specs
>> >gcc version 2.95.3 20010315 (release)
>> >Compilation info: CC='gcc'  CFLAGS=''  CXX='gcc'  CXXFLAGS=''
LDFLAGS=''LIBC:
>> >-rw-r--r--   1 bin      bin      1607728 Dec 28  1999 /lib/libc.a
>> >lrwxrwxrwx   1 root     root          11 Apr 14  1998 /lib/libc.so ->
>> ./libc.so.1
>> >-rwxr-xr-x   1 bin      bin      1014088 Dec 28  1999 /lib/libc.so.1
>> >-rw-r--r--   1 bin      bin      1607728 Dec 28  1999 /usr/lib/libc.a
>> >lrwxrwxrwx   1 root     root          11 Apr 14  1998 /usr/lib/libc.so ->
>> ./libc.so.1
>> >-rwxr-xr-x   1 bin      bin      1014088 Dec 28  1999 /usr/lib/libc.so.1
>> >Configure command: ./configure
--with-unix-socket-path=/var/tmp/mysql.sock \
>> >--with-low-memory --with-mit-threads=yes --without-perl
>> --enable-thread-safe-client \
>> >--with-berkeley-db --with-innodb
>


Thread
error in sec index entry updatebmpittma11 Sep
Re:error in sec index entry updateHeikki Tuuri12 Sep
  • Re: error in sec index entry updateBrian Mard Pittman12 Sep
Re: error in sec index entry updateHeikki Tuuri12 Sep