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
528 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
Comment Utility
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
Comment Utility
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
Comment Utility
>>>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
Comment Utility
I think I will copy the way Wordpress stores it's settings, the variable vartype is long text.

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now