SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: mysql key error
-
May 29, 2002, 14:07 #1
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
mysql key error
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
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:
pLast edited by Philip Toews; May 29, 2002 at 14:27.
Philip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
May 29, 2002, 15:04 #2
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
you would need to change
Code:KEY organisation_name (organisation_name, details, city, province, country)
Code:KEY organisation_name (organisation_name, details(n), city, province, country)
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.- Matt** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
"Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR
-
May 29, 2002, 15:10 #3
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm not sure either
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?
pPhilip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
May 29, 2002, 15:15 #4
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Re: I'm not sure either
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
Code:FULLTEXT (details)
As far as the "prefix" goes...I'm confused. What does that mean? How do I properly set a key length?
-
May 29, 2002, 15:38 #5
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
OK doc
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!
pPhilip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
May 29, 2002, 15:47 #6
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
OK, great.
Bookmarks