Re: BUG: Receive "ERROR 1032: Can"t find record in "tablename"" on select (3.23.52)

David,

thank you for a repeatable bug report.

The bug occurred when the user did not create a PRIMARY KEY to the table and
added later UNIQUE indexes with CREATE INDEX.

MySQL/InnoDB got confused what is the primary key number in this case. MySQL
seems to make the first UNIQUE index as the primary key internally.

MySQL-4.0 did not exhibit the problem, but the bug may have surfaced also
there under different circumstances.

I have now fixed this to 3.23.54 and 4.0.5.

Thank you,

Heikki
Innobase Oy
...................

From: David Ozenne
Date: Tue, 29 Oct 2002 18:43:38 -0800

I am using MySQL 3.23.52 using the mysqld-max-nt binary on Windows NT
4.0 (SP 6).

How-To-Repeat:

From mysql command-lin interface, execute the following:

drop table if exists big_index;
CREATE TABLE big_index (
col1 int(11) NOT NULL default '0',
col2 int(11) NOT NULL default '0',
col3 char(2) NOT NULL default '',
col4 char(2) default NULL,
col5 int(11) NOT NULL default '0',
col6 char(2) NOT NULL default '',
col7 int(11) NOT NULL default '0'
) TYPE=InnoDB;
create unique index big_index_u1 on big_index (col1);
create unique index big_index_u2 on big_index
(col2,col3,col4,col5,col6,col7);
insert into big_index (col1,col2,col3,col4, col5, col6, col7) values
(1,1,'AA',null,1,'AA',1);
select col3 from big_index order by 1;



The result is ERROR 1032, as shown here.

mysql> drop table if exists big_index;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE big_index (
-> col1 int(11) NOT NULL default '0',
-> col2 int(11) NOT NULL default '0',
-> col3 char(2) NOT NULL default '',
-> col4 char(2) default NULL,
-> col5 int(11) NOT NULL default '0',
-> col6 char(2) NOT NULL default '',
-> col7 int(11) NOT NULL default '0'
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> create unique index big_index_u1 on big_index (col1);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create unique index big_index_u2 on big_index
(col2,col3,col4,col5,col6,col7);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into big_index (col1,col2,col3,col4, col5, col6, col7)
values (1,1,'AA',null,1,'AA',1);
Query OK, 1 row affected (0.02 sec)

mysql> select col3 from big_index order by 1;
ERROR 1032: Can't find record in 'big_index'
mysql>



If I create ...u1 as the PRIMARY KEY instead of a unique index, I do
not get the error. (This provides something of a workaround for me.) If
I provide a non-null value for col4, I do not get the error. If I
eliminate col5, col6, and col7 from the index, I do not get the error.
If I remove the "order by", I get a different plan and I do not get the
error. If I switch the table type to MyISAM, I do not get the error.

I can also reproduce the problem with the following somewhat smaller
test case:

drop table if exists indexbug;
CREATE TABLE indexbug (
col1 int(11) NOT NULL default '0',
col2 int(11) default NULL,
col3 int(11) NOT NULL default '0'
, unique key indexbug_u1 (col1)
, unique key indexbug_u2 (col2, col3)
) TYPE=InnoDB;
insert into indexbug (col1,col2,col3) values (1,null,1);
select col3 from indexbug order by 1 asc;



David Ozenne (dozenne@10fold.com)
Director, TenFold Database Library
TenFold Corporation






------------------------------------------------------------ ---------
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 bugs-thread12878@lists.mysql.com
To unsubscribe, e-mail