mysql key error

[Philip Toews]

I am attempting to use phpMyAdmin to reverse dump a DB structure and data onto a remote host DB. I get the following sql error when PHPmyadmin trys to run the dump file:

CREATE TABLE company_info (
ID int(11) DEFAULT '0' NOT NULL,
organisation_name varchar(255) NOT NULL,
website varchar(255),
organisation_type varchar(255) NOT NULL,
telephone varchar(255) NOT NULL,
fax varchar(255) NOT NULL,
address varchar(255) NOT NULL,
city varchar(255) NOT NULL,
province varchar(255) NOT NULL,
country varchar(255) NOT NULL,
contactmethod varchar(255) NOT NULL,
number_teachers varchar(255) DEFAULT '0' NOT NULL,
number_students varchar(255) DEFAULT '0' NOT NULL,
details text NOT NULL,
hiring_months text NOT NULL,
validation int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (ID),
KEY organisation_name (organisation_name, details, city, province, country)
)

MySQL said:


BLOB column 'details' used in key specification without a key length

How do I fix this?

I looked at the MySQL docs and found the following:

Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index:

So how do I go back and specify the length of the index? What sql statement do I need to run?

p

[DR_LaRRY_PEpPeR]

you would need to change

KEY organisation_name (organisation_name, details, city, province, country)

to

KEY organisation_name (organisation_name, details(n), city, province, country)

where n is the length of the prefix you want to specify.

however, i don't see why you're including details in the index if it's a TEXT column and you're going to have that many characters (more than 255) in it. are you searching on the details column in any of your queries (e.g. is it in the WHERE clause)? and that doesn't include something like WHERE details LIKE '%word%'. if you're not searching on the prefix of details, remove it from the KEY () as it's useless.

[Philip Toews]

I'm sorry Doc, I'm confused. The details column is used in fulltext searches...that's why it is indexed. Does that make sense?

As far as the "prefix" goes...I'm confused. What does that mean? How do I properly set a key length?

p

[DR_LaRRY_PEpPeR]

Originally posted by Philip Toews
I'm sorry Doc, I'm confused. The details column is used in fulltext searches...that's why it is indexed. Does that make sense?

no, no. not that kind of index for fulltext searching. you need a FULLTEXT index, which, i believe, is specified as

FULLTEXT (details)

As far as the "prefix" goes...I'm confused. What does that mean? How do I properly set a key length?

like i said, it doesn't seem to apply in this case, but you use details(n) where n is the prefix length you want. so if you specified details(255) that would index the first 255 characters of the details column.

[Philip Toews]

I'll follow your advice and try dropping the index for the time being. I've coded a lot of queries and built a lot of tables in the last few weeks...I think I may have indexed that one by error.

I looked through my scripts and I don't think I actually do a fulltext search on that table anyway...so I must have added the index in a sleep deprived moment of stupidity.

Having dropped the index and used mysqldump to dump the db and mysql via SSH to reverse dump I have had success! I owe you another one!

Let me know if you ever want to visit Thailand...I'll take you out on the town!

p

[DR_LaRRY_PEpPeR]

OK, great.