Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

Fixing MySQL error Error: Row size too large (> 8123). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORM! AT=COMPRESSED may help. In current row format, BLOB prefix of 768

I built an entire application which stores all its settings in one MySQL table but in 1 row (each setting is another column).

I see now this is a poor design but it would be too much work to change the application.

There are many users using this application so the MySQL table has to be right:

I am getting the following error when trying to update the row (a setting):

Error: Row size too large (> 8123). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORM! AT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

I have already changed all the columns I can to TEXT (changing some of the INT ones may break the application)

Under PHP my admin I only see Compact and redundant row formats of which I can switch to.

Please help
0
Mark
Asked:
Mark
  • 2
  • 2
1 Solution
 
COBOLdinosaurCommented:
This is the key phrase:
>>>I see now this is a poor design but it would be too much work to change the application.

You need to suck it up.  When you have a serious design error, trying to find a hack may give temporary relief, but hacks breed hacks.  You are going to condemn yourself to endless patches to fix patches, and every new version of mysql will become a threat to bring on the meltdown that virtually always ends the useable life of something with serious design flaws.

So "too much work"? No you have two option search for hacks that will make the app even less stable and watch it die a death by a thousand cuts.  Or sit down and fix the design and endure some temporary anger from the users affected by the change.

Cd&
0
 
MarkProgrammerAuthor Commented:
So what would be the best redesign:

Specifically, what vartype should I set the value to

Example:
Name VARCHAR(255)
Value TEXT?

Some settings contain large text bodies and some are a simple Boolean setting but I understand MySQL stores TEXT as an additional object draining resources...

I know I can make 2 tables but it would be a lot more work.

Please advise
0
 
COBOLdinosaurCommented:
>>>I know I can make 2 tables but it would be a lot more work.

As long as your focus is on minimizing the work you have to do you are not going to come up with the best design.  The primary focus should almost always be on producing a design that is the most efficient way to service the apps using the data.

Without the requirements, I have no idea of what the design should be; how the data should be normalized; what optimization is possible;  or what the access stats for the table look like with various data models.

You are right that text gets stored outside of the primary dataset as a file.  The fact that the row is too big does not necessarily mean you need to be using text as a datatype for some columns.  It is much more likely that you are treating a single table like a flat file instead of using indexed relationships across several tables.  unless the applications require every column in the row for ever access to the data; then a single table will almost always be the wrong design.

Cd&
0
 
MarkProgrammerAuthor Commented:
I think I will copy the way Wordpress stores it's settings, the variable vartype is long text.

Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now