Mark
asked on
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I will copy the way Wordpress stores it's settings, the variable vartype is long text.
Thanks
Thanks
>>>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&