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
566 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 47
I have many databases and one main one, how can I take there column names from the main one? 4 65
mysql update statement 3 22
two ways encryption with php 3 24
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

8 Experts available now in Live!

Get 1:1 Help Now