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
MarkProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarkProgrammerAuthor Commented:
I think I will copy the way Wordpress stores it's settings, the variable vartype is long text.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.