List:General Discussion« Previous MessageNext Message »
From:Duncan Salada Date:January 29 2003 2:44pm
Subject:RE: problems creating fulltext index
View as plain text  
Hi!

> -----Original Message-----
> From: Erlend Hopsø Strømsvik [mailto:erlend.stromsvik@stripped]
> 
> > -----Original Message-----
> > From: Salada, Duncan [mailto:Duncan.Salada@stripped]
> > 
> > 
> > Hello everyone,
> > 
> > I'm running Ver 8.39 Distrib 4.0.9-gamma, for intel-linux on 
> > i686.  I'm
> > trying to create a fulltext index on an existing myisam table 
> > and running up
> > against an error.  From what I can tell, I'm using the alter 
> > table command
> > correctly, but I keep getting errno 140.  Can anyone point 
> > out my error?
> > 
> > mysql> alter table materials add fulltext title
> > (title,variant_title,ss_title,term_1,term_2,term_3,term_4,
> > term_5,abstract,notes,personal_name_1,personal_name_2,personal
> > _name_3,person
> > al_name_4,
> > personal_name_5,personal_name_6);
> > 
> > ERROR 1005: Can't create table './lincs/#sql-6d12_7.frm' (errno: 140)
> 
> Have you tried using "create index"?
> From the documentation (http://www.mysql.com/doc/en/CREATE_INDEX.html): 
> CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name
> (col_name[(length)],... )
> 
> So it would become
> CREATE FULLTEXT INDEX title_idx ON materials
>
(title,variant_title,ss_title,term_1,term_2,term_3,term_4,term_5,abstract,no
> tes,personal_name_1,personal_name_2,personal_name_3,person_al_name_4,
> personal_name_5,personal_name_);

Yes, I tried using "CREATE INDEX", and I received exactly the same error.  I
probably should have mentioned that in my original email.  I also failed to
mention that I am trying to upgrade from 3.23.54.  I still have 3.23.54
running on the same machine with 4.0.9 running on a different port and
socket.  Both installations were built from source as well.  I copied "var"
from the 3.23.54 install to the 4.0.9 install.  Changed directory owners and
groups and ran "mysql_fix_privilege_tables" as stated in the manual.  I also
ran "REPAIR TABLE materials USE_FRM" because the table already has another
fulltext index (created in 3.23.x) yielding...

mysql> REPAIR TABLE materials USE_FRM;
+-----------------+--------+----------+-------------------------------------
--+
| Table           | Op     | Msg_type | Msg_text
|
+-----------------+--------+----------+-------------------------------------
--+
| lincs.materials | repair | warning  | Number of rows changed from 0 to
8700 |
| lincs.materials | repair | status   | OK
|
+-----------------+--------+----------+-------------------------------------
--+
2 rows in set (2 min 16.23 sec)

I tried to create my new fulltext index (for those just joining us see
orginal message) and got error 140.  I also tried dropping the other old
fulltext indexes and then creating the new index with the same result.
Perhaps I'm missing something with the changeover to 4.0.9?  Any help would
be greatly appreciated. 

Thanks,
Duncan Salada
Thread
problems creating fulltext indexDuncan Salada28 Jan
  • Re: problems creating fulltext indexDan Nelson29 Jan
RE: problems creating fulltext indexErlend Hopsø Strømsvik29 Jan
RE: problems creating fulltext indexDuncan Salada29 Jan
  • Re: problems creating fulltext indexSergei Golubchik29 Jan
RE: problems creating fulltext indexDuncan Salada31 Jan