full text search

[AdRock]

I am trying to alter my table so i can do full text searches but mysql is kicking up a fuss with this error message

#1005 - Can't create table '.\mycarshare\#sql-210_55.frm' (errno: 140)

 

This is the output from phpmyadmin with mysql version

-- phpMyAdmin SQL Dump

-- version 2.6.4-pl1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Mar 18, 2008 at 02:36 PM

-- Server version: 4.0.23

-- PHP Version: 5.0.2

 

here is my table structure

CREATE TABLE `carshare` (
  `id` int(4) NOT NULL auto_increment,
  `userid` int(4) NOT NULL default '0',
  `seats_available` int(1) NOT NULL default '0',
  `start_street` varchar(30) NOT NULL default '',
  `start_postcode` varchar(9) NOT NULL default '',
  `start_lat` varchar(9) NOT NULL default '',
  `start_long` varchar(9) NOT NULL default '',
  `end_street` varchar(30) NOT NULL default '',
  `end_postcode` varchar(9) NOT NULL default '',
  `end_lat` varchar(9) NOT NULL default '',
  `end_long` varchar(9) NOT NULL default '',
  `depart_time` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=23 ;

 

and this is how i am trying to add the fulltext search

alter table carshare add fulltext car_search (seats_available, start_street, start_postcode, end_street, end_postcode, depart_time)

[fenway]

You can't have a fulltext index cover non-text columns! e.g. int ( = seats_available), or times.