SQL file fails to produce fulltext indexes

[ppowell]

Following is a SQL file I created that should create indexes for a bunch of tables:

---

-- File: INDEXES.SQL
-- Created: 6/29/2004
-- Modified: 6/29/2004
-- Purpose: Create IVC table indexes
-- Dependencies: MySQL 3.23+ Database
-- Privacy Scope: SQL file. Be sure to render lockdown on /sql folder to prevent viewing/downloading of this script
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALTER TABLE image ADD INDEX ix_image_path (image_path);
ALTER TABLE image ADD INDEX ix_search FULLTEXT (image_name, image_alt, image_location_city, image_location_state, image_location_country);

ALTER TABLE album ADD INDEX ix_album_name (album_name);

ALTER TABLE department ADD INDEX ix_department_name (department_name);
ALTER TABLE department ADD INDEX ix_department_parent_id (department_parent_id);

ALTER TABLE person ADD INDEX ix_department_id (department_id);
ALTER TABLE person ADD INDEX ix_search FULLTEXT (first_name, last_name);

ALTER TABLE keyword ADD INDEX ix_search FULLTEXT (keyword_name);

ALTER TABLE event ADD INDEX ix_search FULLTEXT (event_name);

ALTER TABLE placement ADD INDEX ix_search FULLTEXT (placement_name);

This fails in all instances of MySQL, and if I use the following code:

---

-- File: INDEXES.SQL
-- Created: 6/29/2004
-- Modified: 6/29/2004
-- Purpose: Create IVC table indexes
-- Dependencies: MySQL 3.23+ Database
-- Privacy Scope: SQL file. Be sure to render lockdown on /sql folder to prevent viewing/downloading of this script
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALTER TABLE image ADD INDEX ix_image_path (image_path);
ALTER TABLE image ADD FULLTEXT ix_search (image_name, image_alt, image_location_city, image_location_state, image_location_country);

ALTER TABLE album ADD INDEX ix_album_name (album_name);

ALTER TABLE department ADD INDEX ix_department_name (department_name);
ALTER TABLE department ADD INDEX ix_department_parent_id (department_parent_id);

ALTER TABLE person ADD INDEX ix_department_id (department_id);
ALTER TABLE person ADD FULLTEXT ix_search (first_name, last_name);

ALTER TABLE keyword ADD FULLTEXT ix_search (keyword_name);

ALTER TABLE event ADD FULLTEXT ix_search (event_name);

ALTER TABLE placement ADD FULLTEXT ix_search (placement_name);

This malforms the indexes in MySQL 3.23 (but is ok in 4.0.10), making "FULLTEXT" a comment instead of "index_type" for the index, which then causes all of my fulltext SQL queries to fail in MySQL 3.23 (but works perfectly in 4.0.10).

I don't have the option of upgrading MySQL as this SQL file is part of a portable application that is required to work in MySQL 3.23.58+.

Please help, I don't know what else to do, I've looked at the MySQL site and it offered no viable answers to my problem.

Thanx
Phil

[ppowell]

Turns out, if you're interested, that the SQL file was never the problem to begin with; it was all having to do with the SQL query that uses the fulltext index in the first place.

I was using the clause "IN BOOLEAN MODE" for my querying, which is not supported in MySQL 3.23+ (though fulltext searches are, even if the fulltext index in MySQL 3.23+ is rather strange in format to me), so I had to readjust my PHP code to check for the version of MySQL:

if (preg_match('/^4\\..*$/i', trim(@mysql_get_client_info())) && strcmp(strtolower($dbBrandName), 'mysql') == 0) $booleanModeString = ' IN BOOLEAN MODE';

Phil