MySQL Bugs: #26420: Falcon: Can't find record with long multi-column index

Bug #26420	Falcon: Can't find record with long multi-column index
Submitted:	15 Feb 2007 18:52	Modified:	20 Apr 2007 14:43
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.2.4-falcon-alpha-debug	OS:	Linux (SUSE 10.0 / 64-bit, suse9.3 x86)
Assigned to:	Hakan Küçükyılmaz	CPU Architecture:	Any

Description:
I create a Falcon table with two VARCHAR(548) columns.
I create an index on both columns.
I insert one row.
I try to update the row.
I see: "ERROR 1032 (HY000): Can't find record in 't'".

The error message is the same as in
Bug #26328 errors from falcon: (1032) - Can't find record in 't1': delete from ...
But the cause is apparently different. This bug happens only when
there is a combined index on two VARCHAR columns whose
total length is close to the limit (1100 characters).

How to repeat:
mysql> create table t (s1 varchar(548), s2 varchar(548)) engine=falcon;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (null,null);
Query OK, 1 row affected (0.00 sec)

mysql> create index i on t (s1,s2);
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update t set s1 = repeat('a',548), s2 = repeat('a',548);
ERROR 1032 (HY000): Can't find record in 't'

verified on todays BK pull.
sbester@www:~/build/mysql-5.2-falcon> bkf changes|head
OK-root OK
ChangeSet@1.2434, 2007-02-14 15:48:22-06:00, klewis@klewis-mysql. +1 -0
  Enable falcon_bug_23818_C.test

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.2.4-falcon-alpha-debug |
+--------------------------+
1 row in set (0.03 sec)

mysql> create table t (s1 varchar(548), s2 varchar(548)) engine=falcon;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t values (null,null);
Query OK, 1 row affected (0.00 sec)

mysql> create index i on t (s1,s2);
Query OK, 1 row affected (2.98 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update t set s1 = repeat('a',548), s2 = repeat('a',548);
ERROR 1032 (HY000): Can't find record in 't'
mysql>

The real problem is that Falcon thought the key length was too long. The error for for a key that is too long in Falcon is not translatable through the SQL interface. So the correct error did not show up.  However, this key is not too long, so the error should not show up.

Added test case falcon_bug_26420.test and pushed to mysql-5.1-falcon tree.

Regards, Hakan

There was a >= comparison to make sure the key length does not go over the maximum length.  But it sshould have been a >.  It is OK to equal the max. 

Also, I changed the error code processing so that Falcon will return;

ERROR 1296 (HY000): Got error 124 'maximum index key length exceeded' from Falcon

This will still happen if you do this;
create table t (s1 varchar(552), s2 varchar(548)) engine=falcon;
insert into t values (null,null);
create index i on t (s1,s2);
update t set s1 = repeat('a',552), s2 = repeat('a',548);

falcon_bug_26420.test passes now:

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_26420               [ pass ]            295
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 0.295 seconds actually executing testcases

Regards, Hakan

I pushed a fix to the current request with this bug, that the error on a key that can be too big should occur when the index is created instead of when a key in created that is actually too big.  But the fix breaks several falcon bugs which assume the opposite.  So the change set pushed does not have the SQL exception error activated.  To activate this behavior, uncomment the SQLException at the end of Index::checkMaxKeyLength() which is at the end of index.cpp.  Make sure it is done if (sumKeyLen > maxKeyLen).

Hakan, I propose that you evaluate this fix to see if it is acceptable. If so, then the affected test scripts should be changed.  You can activate the error in the same push as the test scripts.

Pushed a fix which throws an error when an index is created that can be too large, according to Falcon internal rules.  So this create statement;
create table t (a varchar(552), b varchar(548), index(a,b)) engine=falcon;
will now fail with 
ERROR 1005 (HY000): Can't create table 'test.t' (errno: 140)

This is different from the error you get from 
create table t (a varchar(552), b varchar(549), index(a,b)) engine=falcon;
which is 
ERROR 1071 (42000): Specified key was too long; max key length is 1100 bytes

The difference is that, with the later, the server recognizes that 552 + 549 > 1100.  But in the former, Falcon must apply it's internal rules for storage overhead to determine that it will not fit.

Some minor changes could be made to the server to get the same error in both cases, but Sergei Golubchik objected to that solution.  "My complaint is that adding a new error code that is not used by anybody else and can hardly ever be, isn't generally a good thing. I mean, extending the interface to support every peculiarity of every storage engine."  

The long term goal for Falcon, by beta, is to make the MaxKeyLength hidden outside the Falcon engine and allow any legal MySQL index definition on any page size.  Falcon would actually index only that portion that fits, using the same algorithm as prefix keys.  Falcon can do this because the index scan is only a first shot at finding the right records.  It is OK to find too many. The final selection comes from looking directly at the records.

Since this error code can be considered a short term solution, there is no need to put something in the server that Sergei does not want.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24572

ChangeSet@1.2600, 2007-04-15 23:21:33+02:00, hakank@lu0011.wdf.sap.corp +1 -0
  Turn on error for Bug#26420

Peter,

please re evaluate.

Thanks,

Hakan

A note has been added to the 5.2.4 changelog.

This bug report entry has been moved to the 6.0.0 Falcon changelog.