Bug #2189 An equality search on a BLOB/TEXT field indexed by fulltext index crashes 4.0.1
Submitted: 22 Dec 2003 9:44 Modified: 12 Jan 2004 6:32
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.17 OS:Any (all)
Assigned to: MySQL Verification Team CPU Architecture:Any

[22 Dec 2003 9:44] SINISA MILIVOJEVIC
Description:
> We have discovered what we think is a bug in mysql.  We had a table called
> artilces with a varchar(50) field called Title in it, and the following
> query kept crashing the mysql service:
>
> select max(ID) as ID2
> FROM articles
> WHERE Title=''
>
> (where ID was an auto-incrementing INT as a primary key)
>
> After much testing, and trying to replicate the error, we discovered the
> following:
>
> It did not matter if the ID field was an auto-increment or not.
>
> If the ID field was no longer a key, it worked fine.
>
> The query run without max or min worked fine.
>
> The query run without WHERE worked fine.
>
> And now, for the bizarre part:  we renamed Title to Title2, and then back
to
> Title, and the problem disappeared completely, with the query running
> perfectly in it's original form.
>
> When the service crashed, the error logs did not show anything unusual.
It
> would restart without a problem.
>
> Some details about our environment:
> Windows 2000 server, also tested (and the problem still occured) on win2k
> pro.
> (I also did some research on deja.com and found references to this same
> problem on Linux machines)
>
> MySQL  4.0.15  (4.0.12 on win2k pro machine)
>
>
>
> Conclusion:  I think the definition for the Title field became corrupted
> somehow, but it was such a small level of corruption that it only caused
> problems when the index field (ID) was used with a special function, such
as
> MIN or MAX  (on the references to this problem on linux machines, the
> DISTINCT function was also mentioned, but we haven't tested it).  I'm
> guessing that the action of renaming the field completely rebuilt the
> definition of the field in MySQL and therefore fixed whatever was
corrupted.
>
> Unfortunately, this error is very difficult to replicate, because we still
> don't know how the field definition became corrupted in the first place.
>
>
>
> Nat Ciesla
> wisnet.com, LLC

How to repeat:
Download articles.zip from Incoming directory and run a query:

select max(ID) as ID2  FROM articles > WHERE Title='';

Suggested fix:
Fix will come very soon, but in essence an underflow happens when calculating
index length.
[12 Jan 2004 6:32] MySQL Verification Team
Committed.