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
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
