MySQL Bugs: #26067: Falcon: SELECT fails on a database after many changes

Bug #26067	Falcon: SELECT fails on a database after many changes
Submitted:	5 Feb 2007 2:56	Modified:	6 Sep 2007 21:11
Reporter:	Peter Gulutzan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.2.2-falcon-alpha-debug-log	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Christopher Powers	CPU Architecture:	Any

Description:
I have a table which contains rows that contain columns
that start with the letter 'a' or 'A'. I do some
changes to this table, including many deletions
and a split into two partitions. I add more rows
that contain columns that start with the letter 'A'.
I search for all rows that begin with 'a'.
I don't find them all.

The symptoms were previously noted as a comment
on bug#26055.

How to repeat:
Say (using the database produced by the steps that
I explained originally for Bug#22065):

delete from tu where char_length(s1)>1 and s1<'b';
delete from tu where char_length(s1)>1 and s1<'e';
delete from tu where char_length(s1)>1 and s1<'h';
delete from tu where char_length(s1)>1 and s1<'k';
delete from tu where char_length(s1)>1 and s1<'n';
delete from tu where char_length(s1)>1 and s1<'q';
delete from tu where char_length(s1)>1 and s1<'t';
delete from tu where char_length(s1)>1 and s1<'w';
delete from tu where char_length(s1)>1;
alter table tu add column cp932 varchar(5);
update tu set cp932=s1;
alter table tu add column otto integer unique auto_increment;
alter table tu partition by range (otto) (partition p1 values less than (30000),
partition p2 values less than maxvalue); /* necessary */
update tu set cp932 = concat(cp932,'',cp932,' ',cp932,0x00);
create unique index itu on tu (cp932,s1,otto);
select * from tu where cp932 like 'a%'; /* NEW */
insert into tu (cp932) values (0x4141204120); 
insert into tu (cp932) values (0x41412041);
select * from tu where cp932 like 'a%';

The final SELECT will produce only one row:

mysql> select * from tu where cp932 like 'a%';
+------+-------+------+
| s1   | cp932 | otto |
+------+-------+------+
| A    | AA A  |   66 |
+------+-------+------+
1 row in set (0.00 sec)

The description about how to populate the the table is in 

  BUG#26055

Regards,

Hakan

After populating the table as described in BUG#26055 I could reproduce the error.

I had to use following setting:

  SET names utf8;
  SET sql_mode='';
  SET autocommit=1;

mysql> select * from tu where cp932 like 'a%';
+------+-------+------+
| s1   | cp932 | otto |
+------+-------+------+
| A    | AA A  |   66 | 
+------+-------+------+
1 row in set (0.01 sec)

mysql> insert into tu (cp932) values (0x4141204120); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into tu (cp932) values (0x41412041);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tu where cp932 like 'a%';
+------+-------+------+
| s1   | cp932 | otto |
+------+-------+------+
| A    | AA A  |   66 | 
+------+-------+------+
1 row in set (0.00 sec)

Best regards, Hakan

Reproduced in post alpha IV (release 6.0.2) build, changeset 1.2730.

Unable to reproduce in the post-5.1 merge codebase, changeset 1.2592 and later.

NOTE: The fix for Bug#27405 introduced a regression that crashes the server when Falcon tables containing autoincrement fields are partitioned (see Bug#30878). The test scenario for this bug will only work if these lines in ha_partition::write_row() are commented out:

    //autoincrement_lock= true;
    //pthread_mutex_lock(&table_share->mutex);