Jump to content

SQL max size


EricOnAdventure
Go to solution Solved by kicken,

Recommended Posts

Hey guys, I am trying to put dynamically generated text into a database. The issue is the text can be 500 words+ when you include the HTML wrapping. I am not sure a database can hold it. I tried...

CREATE TABLE `ericgonzp`.`3RLresult` (
`id` INT(4) NOT NULL AUTO_INCREMENT ,
`userhash` VARCHAR(50) NOT NULL ,
`rl1ready` VARCHAR(3) NOT NULL ,
`rl2ready` VARCHAR(3) NOT NULL ,
`rl3ready` VARCHAR(3) NOT NULL , 
`rl1result` VARCHAR(8000) NOT NULL ,
`rl2result` VARCHAR(8000) NOT NULL ,
`rl3result` VARCHAR(8000) NOT NULL ,                                      
 PRIMARY KEY (`id`)) ENGINE = InnoDB;

Sadly this doesn't work...

 

#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

 

Any idea what I should do?

Link to comment
Share on other sites

  • Solution

If you want to store a large amount of text in a column, then you'd do as the error suggests and use the TEXT data type. This type stores the text in a separate location from the row and then just stores a pointer to it within the row itself.

 

That said, you could also re-evaluate how big you need the columns to be. For example if your 500 word block of text had an average word length of 6 characters, you'd be using only about 3,500 characters total meaning you could make your columns 4,000 characters long rather than 8,000.

 

You could also re-design your DB so you have more rows but less columns. For example:

create table result (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 , userhash VARCHAR(50) NOT NULL
 , resultNumber INT
 , ready VARCHAR(3) NOT NULL
 , result VARCHAR(8000) NOT NULL
)
Instead of 3 ready/result columns, you have 3 rows, each with a single ready/result column plus a column indicating the result number (1, 2, 3...). This is known as normalization and is generally a good idea. If you find yourself having a bunch of numbered columns in a table, that is a good sign you should re-think that design.

 

Think about your design now. What happens if next month you need to add a 4th result? You'd have to add more columns, which could cause all kinds of problems. All the existing rows would have to be expanded to include the new columns. Your queries may break if you are doing something like SELECT * due to the new columns. You might fail to add the columns do to the row size limit you already discovered.

 

With the alternate row based design you just add another row with resultNumber=4, no problems at all. There's no limit to the number of results you could potentially have with the alternate design. If you want to have a limit, you enforce it in your code. If you need to raise the limit later, it's a simple code change, not a whole database refactor.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.