SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict mmelen's Avatar
    Join Date
    Jul 2005
    Location
    NJ/NY USA
    Posts
    243
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    #1170 - BLOB/TEXT column 'other' used in key specification without a key length

    Can anyone make sense of the Myql Query? I am trying to make a simple table - any idea why I get that error?


    Error

    SQL query:

    CREATE TABLE IF NOT EXISTS `melennet_proxykey`.`pages` (
    `main` VARCHAR( 64 ) NOT NULL ,
    `other` TEXT NOT NULL ,
    `article` BLOB NOT NULL ,
    PRIMARY KEY ( `main` ) ,
    INDEX ( `other` , `article` ) ,
    FULLTEXT (
    `main` ,
    `other` ,
    `article`
    )
    ) ENGINE = MYISAM

    MySQL said: Documentation
    #1170 - BLOB/TEXT column 'other' used in key specification without a key length

  2. #2
    SitePoint Addict mmelen's Avatar
    Join Date
    Jul 2005
    Location
    NJ/NY USA
    Posts
    243
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I even tried:

    CREATE TABLE `melennet_proxykey`.`pages` (
    `main` VARCHAR( 64 ) NOT NULL ,
    `other` TEXT( 255) NOT NULL ,
    `article` BLOB( 65535) NOT NULL ,
    PRIMARY KEY ( `main` ) ,
    INDEX ( `other` , `article` ) ,
    FULLTEXT (
    `main` ,
    `other` ,
    `article`
    )
    ) ENGINE = MYISAM

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i believe the problem is here --
    Code:
    `other` TEXT( 255) NOT NULL ,
    `article` BLOB( 65535) NOT NULL ,
    INDEX ( `other` , `article` )
    the maximum prefix length is 255 (or 1000 bytes for MyISAM and InnoDB tables as of MySQL 4.1.2), and you have article's prefix as 65K

    if you think about it for a second, 100 oughta be plenty

    a regular INDEX, as opposed to a FULLTEXT index, is used to find a particular row, based on matching data values from left to right

    to have an index on (other,article) would be useful only if you had a WHERE clause like this --
    Code:
    WHERE other = 'a specific value which exactly equals the value in the column for exactly 100 bytes'
      AND article = 'another string which ... well, you get my point, yeah?'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict mmelen's Avatar
    Join Date
    Jul 2005
    Location
    NJ/NY USA
    Posts
    243
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got it working:

    CREATE TABLE `melennet_proxykey`.`proxykey` (
    `id` MEDIUMINT( 5 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
    `main` VARCHAR( 64 ) NOT NULL ,
    `other` TEXT NOT NULL ,
    `article` BLOB NOT NULL ,
    `type` VARCHAR( 32 ) NOT NULL ,
    PRIMARY KEY ( `id` )
    ) ENGINE = MYISAM

    Thanks!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you've abandoned the FULLTEXT index as well as the INDEX?

    yep, that'll certainly get rid of that pesky error message you were getting



    but how do you plan to search this table?

    and shouldn't you have a UNIQUE inde on the main column? after all, it was your PK for a while

    also, by the way, what's the type column for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •