SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: mysql key error

  1. #1
    SitePoint Addict Philip Toews's Avatar
    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
    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
    Last 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

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    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)
    to

    Code:
    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.
    - 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

  3. #3
    SitePoint Addict Philip Toews's Avatar
    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?

    p
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    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?
    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.

  5. #5
    SitePoint Addict Philip Toews's Avatar
    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!

    p
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, great.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •