SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,388
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Having problem understanding PHPMyAdmin error message

    I am trying unsuccessfully to make an index of several fields from within PHPMyAdmin. (I have done so successfully with several other tables.) Here is the message.
    _________________________
    ALTER TABLE `profiles` ADD INDEX ( `ID` ( 3 ) , `FirstName` ( 30 ) , `LastName` ( 30 ) , `ThumbPhoto` ( 30 ) )

    MySQL said:

    #1089 - Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
    _________________________

    "Incorrect sub part key" - ?

    "the used key part isn't a string" - ?

    "the used length is longer than the key part" - the numbers match their varchar values. ID is tinytext, which I think means "3"

    "the storage engine doesn't support unique sub keys" - I'm using InnoDB.

    Can you give me a clue to what I need to do from here? I'm having this trouble with several tables, but no trouble with other tables. Not sure what the difference is.

    Thanks!

  2. #2
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not sure whether it is needed or not to give the size of the fields while adding the index because the field type and size will be already defined when the table is created. So just try to :
    Code mysql:
    ALTER TABLE `profiles` ADD INDEX (`ID` , `FirstName` ,`LastName` ,`ThumbPhoto`)

    Edit:
    Ahh sorry when I tried with the same syntax as you have tried works fine with one of my InnoDB table.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  3. #3
    Non-Member
    Join Date
    Mar 2008
    Location
    USA, New York--Born in India
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get that same friggin error, any solutions?!

  4. #4
    SitePoint Evangelist catweasel's Avatar
    Join Date
    Apr 2007
    Location
    Goldfields, VIC, Australia
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    I am trying unsuccessfully to make an index of several fields from within PHPMyAdmin. (I have done so successfully with several other tables.) Here is the message.
    _________________________
    ALTER TABLE `profiles` ADD INDEX ( `ID` ( 3 ) , `FirstName` ( 30 ) , `LastName` ( 30 ) , `ThumbPhoto` ( 30 ) )

    MySQL said:

    #1089 - Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
    _________________________
    is ID a string?
    Try process of elimination.. just add one index at a time till you find which one is causing the error.
    Code:
    ALTER TABLE `profiles` ADD INDEX ( `ID` ( 3 ))
    ALTER TABLE `profiles` ADD INDEX ( `FirstName` ( 30 ))
    ALTER TABLE `profiles` ADD INDEX ( `LastName` ( 30 ))  
    ALTER TABLE `profiles` ADD INDEX ( `ThumbPhoto` ( 30 ))

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,388
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by rajug View Post
    I am not sure whether it is needed or not to give the size of the fields while adding the index because the field type and size will be already defined when the table is created. So just try to :
    Code mysql:
    ALTER TABLE `profiles` ADD INDEX (`ID` , `FirstName` ,`LastName` ,`ThumbPhoto`)

    Edit:
    Ahh sorry when I tried with the same syntax as you have tried works fine with one of my InnoDB table.
    I tried it your revised way without the numbers and it worked!


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
  •