Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-11-15
4
578 Views
Last Modified: 2014-11-16
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
Comment
Question by:Mark
  • 2
  • 2
4 Comments
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 40445771
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
 

Author Comment

by:Mark
ID: 40445806
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
 
LVL 53

Accepted Solution

by:
COBOLdinosaur earned 500 total points
ID: 40445825
>>>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
 

Author Comment

by:Mark
ID: 40445833
I think I will copy the way Wordpress stores it's settings, the variable vartype is long text.

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question