[4.0.14] Small bug with OPTIMIZE/ANALYZE and FULLTEXT index

Hi all,

I am running 4.0.14-nt on Win2k and discovered that running OPTIMIZE or
ANALYZE on a table that 1) has a FULLTEXT index and 2) hasn't been
modified since the last OPTIMIZE/ANALYZE does not say "Table is already
up to date" but instead "OK" after it runs, as if the table has changed.
The exception, where the expected behavior occurs, is if there's a small
amount of text/words in the column (table?).

Run these queries to see the problem:

CREATE TABLE test (text TEXT NOT NULL, FULLTEXT (text));
INSERT INTO test VALUES ('Some words.');
OPTIMIZE TABLE test;

-- Works as expected
OPTIMIZE TABLE test;
ANALYZE TABLE test;

-- ...But use more text/words
UPDATE test SET text='Four words test text.';
OPTIMIZE TABLE test;

-- Doesn't work as before
-- Says "OK" instead of "Table is already up to date"
OPTIMIZE TABLE test;
ANALYZE TABLE test;


4.1.0-alpha-nt works correctly BTW.


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [4.0.14] Small bug with OPTIMIZE/ANALYZE and FULLTEXT index

Matt W writes:
> Hi all,
>
> I am running 4.0.14-nt on Win2k and discovered that running OPTIMIZE or
> ANALYZE on a table that 1) has a FULLTEXT index and 2) hasn't been
> modified since the last OPTIMIZE/ANALYZE does not say "Table is already
> up to date" but instead "OK" after it runs, as if the table has changed.
> The exception, where the expected behavior occurs, is if there's a small
> amount of text/words in the column (table?).
>
> Run these queries to see the problem:
>
> CREATE TABLE test (text TEXT NOT NULL, FULLTEXT (text));
> INSERT INTO test VALUES ('Some words.');
> OPTIMIZE TABLE test;
>
> -- Works as expected
> OPTIMIZE TABLE test;
> ANALYZE TABLE test;
>
> -- ...But use more text/words
> UPDATE test SET text='Four words test text.';
> OPTIMIZE TABLE test;
>
> -- Doesn't work as before
> -- Says "OK" instead of "Table is already up to date"
> OPTIMIZE TABLE test;
> ANALYZE TABLE test;
>
>


HI!

First of all there is no need to run both OPTIMIZE / ANALYZE as first
will do the second.

Next, with dynamic records OPTIMIZE always has something to do.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [4.0.14] Small bug with OPTIMIZE/ANALYZE and FULLTEXT index

Hi Sinisa!

Thanks for replying, but... you can't see that this is a bug? :-) The
behavior is *NOT* consistent with *any* other version of MySQL I've
*ever* used OPTIMIZE/ANALYZE on with MyISAM tables. If it's not a bug,
then all the other versions must have the bug. :-)

I thought it would a simple bug report. But now I have investigated
further to give more details about the problem. I hope you can see the
bug. ;-) The problem seems to be, as I discovered with myisamchk, that
the table is not remembering that it was analyzed when it has a FULLTEXT
index.

----- Original Message -----
From: "Sinisa Milivojevic"
Sent: Monday, August 25, 2003 5:56 AM

> HI!
>
> First of all there is no need to run both OPTIMIZE / ANALYZE as first
> will do the second.

Yes, I'm aware of that. I included both to show that they're both messed
up.


> Next, with dynamic records OPTIMIZE always has something to do.

Not if the table *HAS NOT* changed! :-) And ANALYZE doesn't care if it's
fixed or dynamic since it only works on the indexes, right? Anyway, the
fact that my example used dynamic records is irrelevant. I'll use a CHAR
this time, and the results will be the same with fixed records. ;-)

OK, follow along here:

CREATE TABLE test (text CHAR(255) NOT NULL, FULLTEXT (text));
INSERT INTO test VALUES ('Some words.');

Let's see what myisamchk says at this point:

myisamchk -dv path/to/db/test
....
Status: open,changed
....

Marked as changed of course. Now...

OPTIMIZE TABLE test;
-- Msg_text: OK

Fine. Now let's see myisamchk:

myisamchk -dv path/to/db/test
....
Status: checked,analyzed,sorted index pages
....

Notice "analyzed." And when we run OPTIMIZE again:

OPTIMIZE TABLE test;
-- Msg_text: Table is already up to date

This is what should happen because the table is no longer marked as
changed and it's been analyzed. This shows that, for some reason, it
works correctly with a small amount of text/words in the column
(table?). Now to add a bit more text:

UPDATE test SET text='Four words test text.';

Checking myisamchk again:

myisamchk -dv path/to/db/test
....
Status: open,changed,sorted index pages
....

All normal so far. Now to OPTIMIZE it:

OPTIMIZE TABLE test;
Msg_text: OK

All normal still. What does myisamchk say?:

myisamchk -dv path/to/db/test
....
Status: checked,sorted index pages
....

Uh oh! Where's analyzed that was there last time we ran OPTIMIZE? :-)
What will happen if we run OPTIMIZE again?

OPTIMIZE TABLE test;
Msg_text: OK

No, no, no! That supposed to say "Table is already up to date." And
myisamchk again:

myisamchk -dv path/to/db/test
....
Status: checked,sorted index pages
....

See, it still doesn't remember that it's been analyzed. This bug means
that on my 3 million row table that it analyzes the table again for 5
minutes when nothing has changed! It should know that it's up to date.
This works *CORRECTLY* in 4.0.12 and 4.1.0-alpha (Status includes
analyzed), just like it does in every other version I've used except
4.0.14.

Let's drop the FULLTEXT index:

ALTER TABLE test DROP INDEX text;

myisamchk still indicates the same now... until we OPTIMIZE:

OPTIMIZE TABLE test;
Msg_text: OK

Then:

myisamchk -dv path/to/db/test
....
Status: checked,analyzed,sorted index pages
....

Bingo! It's now correct again, and OPTIMIZE knows this:

OPTIMIZE TABLE test;
Msg_text: Table is already up to date


This is the same behavior as in my 3M row table that has other
columns/indexes in it. The problem of not being marked as analyzed goes
away if the FULLTEXT index is removed or by using 4.0.12 etc. --
anything but 4.0.14.

I hope this is enough information. :-)

Regards,
Matt



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [4.0.14] Small bug with OPTIMIZE/ANALYZE and FULLTEXT index

Matt W writes:
> Hi Sinisa!
>
> Thanks for replying, but... you can't see that this is a bug? :-) The
> behavior is *NOT* consistent with *any* other version of MySQL I've
> *ever* used OPTIMIZE/ANALYZE on with MyISAM tables. If it's not a bug,
> then all the other versions must have the bug. :-)
>
> I thought it would a simple bug report. But now I have investigated
> further to give more details about the problem. I hope you can see the
> bug. ;-) The problem seems to be, as I discovered with myisamchk, that
> the table is not remembering that it was analyzed when it has a FULLTEXT
> index.
>

You have UPDATE'd a tuple with longer row. Then there is some work for
OPTIMIZE.

Regarding error message, this has been fixed , and will come in 4.0.15.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [4.0.14] Small bug with OPTIMIZE/ANALYZE and FULLTEXT index

Hi!

On Aug 23, Matt W wrote:
> Hi all,
>
> I am running 4.0.14-nt on Win2k and discovered that running OPTIMIZE or
> ANALYZE on a table that 1) has a FULLTEXT index and 2) hasn't been
> modified since the last OPTIMIZE/ANALYZE does not say "Table is already
> up to date" but instead "OK" after it runs, as if the table has changed.
> The exception, where the expected behavior occurs, is if there's a small
> amount of text/words in the column (table?).
>
> Run these queries to see the problem:
>
> CREATE TABLE test (text TEXT NOT NULL, FULLTEXT (text));
> INSERT INTO test VALUES ('Some words.');
> OPTIMIZE TABLE test;
>
> -- Works as expected
> OPTIMIZE TABLE test;
> ANALYZE TABLE test;
>
> -- ...But use more text/words
> UPDATE test SET text='Four words test text.';
> OPTIMIZE TABLE test;
>
> -- Doesn't work as before
> -- Says "OK" instead of "Table is already up to date"
> OPTIMIZE TABLE test;
> ANALYZE TABLE test;

Thanks. Fixed in 4.0.15

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org