corrupt data definition

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

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]