SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Row size too large

    Why isn't my code working?

    Code SQL:
    CREATE TABLE article(
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
    title VARCHAR( 250 ) NOT NULL UNIQUE ,
    subtitle VARCHAR( 250 ) ,
    article_date DATE NOT NULL ,
    author VARCHAR( 100 ) ,
    body VARCHAR( 65000 ) NOT NULL ,
    end_notes VARCHAR( 10000 ) ,
    created_on DATETIME NOT NULL ,
    updated_on DATETIME,
    PRIMARY KEY ( id )
    )

    MySQL said: Documentation
    #1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

    I would like the body field to be the maximum VARCHAR size of 65,535 to hold really long articles. (I estimate this should hold up to about 40 pages?!)

    I tried using VARCHAR and also VARCHAR() but nothing works...


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    use TEXT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    use TEXT
    Why?

    And why isn't my attempts at the VARCHAR working?


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Why?
    because TEXT is intended for large text columns

    Quote Originally Posted by DoubleDee View Post
    And why isn't my attempts at the VARCHAR working?
    because you're exceeding the maximum row size
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    because TEXT is intended for large text columns
    Then what is VARCHAR intended for?


    because you're exceeding the maximum row size
    But VARCHAR is supposed to go up to 65,535 so why doesn't

    Code SQL:
    VARCHAR(65,535)

    work?!



    Debbie

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Then what is VARCHAR intended for?
    Smaller text columns
    But VARCHAR is supposed to go up to 65,535 so why doesn't

    Code SQL:
    VARCHAR(65,535)

    work?!
    It works. It doesn't give you an error on the column. But there is a limit for the entire table row size (the sum of all column sizes) as well.
    Like the error tells you
    The maximum row size for the used table type, not counting BLOBs, is 65535.
    So you could create a column with varchar(65535) but it would have to be the only column in the table.

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Smaller text columns

    It works. It doesn't give you an error on the column. But there is a limit for the entire table row size (the sum of all column sizes) as well.
    Like the error tells you

    So you could create a column with varchar(65535) but it would have to be the only column in the table.
    "I see!" said the blind man!

    Thanks for helping me get it!



    Debbie


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
  •