0

I am running dynamic queries based on adding removing fields to categories. Here is an example query:

ALTER TABLE se_classifiedvalues 
  ADD classifiedvalue_402 varchar(250) NOT NULL default ''

MYSQL isn't complaining about syntax but returns false when I var dump the query. When I threw that query into phpMyAdmin and tried to execute the sql it threw this:

#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

any one have any idea what I can do to fix this?

thanks

Improve this question
2
  • any way to extend this limit or do I have to restructure? Commented Mar 22, 2012 at 14:48
  • 1
    No way to extend except to make some of the columns BLOB (ie. TEXT), which reduces the storage in the row to a pointer, as the actual contents are stored outside of the row. This, strangely, is exactly what the error said. :) Commented Mar 22, 2012 at 14:49

2 Answers 2

Reset to default
5

How many columns are in this table already? 402? (I hope not). MySQL has a limit as to the amount of data (bytes) it can store in one row of a table, and it looks like you've reached that limit.

Edit: Here's a link on the MySQL site where you can read about data limits: http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

I think you need to re-think your data structures.

Side question: Are you using ExpressionEngine?

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

bah. great. looks like I'll have to restructure. I was hoping not to have to but client made ridiculous request.
there aren't 402 thats just the autoincrement key value. Some of the 402 were deleted. I'm not really sure, its an old framework and I was hoping to not rewrite some parts by using its dynamic field generator.
No I'm customizing an old Social Engine 3 site. Stupid client wants to combine advanced searching for members with advanced searching for classified ads. the query for that is already gonna run soooo slow if any decent amount of people start using the site and I told them this but why listen to me....
Are there other programmers involved? If it's just you(or your company) on contract, I really recommend trying to stand your ground. Pitch them why their current way isn't working, what needs to be done, and how much it'll cost. And make sure that if they do NOT follow your advice, that you warned them about performance issues and you're off the hook. Remember, you are the professional. That's why they hired you.
2

Make the total size of all columns less than the limit 65,535 bytes. Note that UTF8 columns count as 3 bytes per character.

65,535 is the maximum row size in bytes.

From MySQL:

The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.