Bug #5143 CREATE FULLTEXT INDEX deleting rows

• Submitted: 22 Aug 2004 23:07
• Modified: 29 Mar 2011 18:27
• Reporter: Roberto Spadim (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.3
• OS: Linux (linux, winxp)
• Assigned to: Matthew Lord
• CPU Architecture: Any

[22 Aug 2004 23:07] Roberto Spadim

Description:
create fulltext index indexname on tablename (colum)
delete some rows!
i didn't undestand why...

the table is on:
www.roberto.spadim.com.br/test.sql

How to repeat:
Execute
www.roberto.spadim.com.br/test.sql

more:
ALTER TABLE `titulos_movimento_obs` ADD FULLTEXT NewIndex (obs_cob)

Suggested fix:
!?!?

[23 Aug 2004 2:47] Matthew Lord

Dear User,

Thank you for your bug report!

I can't repeat the problem by executing the sql you specified and adding the fulltext index you 
stated.  I tried this on linux (2.4.21 #12 SMP) and windows 2000 using mysql 4.1.3-beta.

root@localhost:bugs~> select count(*) from titulos_movimento_obs;
+----------+
| count(*) |
+----------+
|      984 |
+----------+
1 row in set (0.04 sec)

root@localhost:bugs~> ALTER TABLE `titulos_movimento_obs` ADD FULLTEXT NewIndex 
(obs_cob);
Query OK, 984 rows affected (0.20 sec)
Records: 984  Duplicates: 0  Warnings: 0

Am I missing anything?

Best Regards

[24 Aug 2004 17:56] Roberto Spadim

flush tables before alter and before select

[24 Aug 2004 18:16] Matthew Lord

Hi,

I did a flush tables before the select and before the alter table.  I still cannot repeat the problem.

Once the table is created, could you paste the sql you run and the results you get back that show 
the problem?

Best Regards

[24 Aug 2004 20:53] Matthew Lord

Dear User,

Can you make sure you have enough disk space?  Look at the total size of the table, data and 
indexes.  An alter table statement will create a copy of the table, perform the updates on the 
table and when it's successful overwrite the original.  We have found a bug though that occurs 
when the disk is full.  The alter does not return an error although it isn't able to recreate all the 
rows in the copied table and thus rows are lost.

Please let me know if this is the problem for you as well.

Best Regards

[25 Aug 2004 20:09] Roberto Spadim

sorry i found the problem...
mysqld was 4.1.2 
i updated it to 4.1.3
and varchar was varbinary (in 4.1.3)
altering column to varchar the index was created sucessfuly, without the table dropped about 19000 rows!! the full table is now at
www.roberto.spadim.com.br/fulltable.sql

[25 Aug 2004 20:45] Roberto Spadim

I have 18Gb free, free space is not the problem

[29 Mar 2011 18:27] Roberto Spadim

i will close, in newer version works without problemc