Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL 5.6.0 - Getting "ERROR 1118 (42000): Row size too large" even though virtually all data is type "text not null" during table creation

Posted on 2010-09-20
9
Medium Priority
?
2,481 Views
Last Modified: 2013-12-12
Using MySQL Community Server 6.5.0-m4 on Red Hat Enterprise Linux Server release 5.3 (Tikanga) running under VMware. A query that worked fine against 5.0.67-log on a standard linux server (i.e. no VMs) now fails with error:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

However, of the 286 columns being created, only 1 is type INT(12). All other 287 are type TEXT NOT NULL. The only other difference between the two DBs that I can readily identify is that where the query works MySQL status shows:
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
and where it fails, the status command shows:
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8

I did try manually setting the client side to latin1 in a MySQL session and then running the create command manually, but got the same error. I have also tried changing the settings in my.cnf to make the client side settings latin1 so I can test it from the php scripts as well, but I can't seem to make that work. My real issue though, is that I get the error even though my data types are already "TEXT." Any one have a suggestion on how to make this previously working statement work in my new env or at least where to look for the underlying issue?
0
Comment
Question by:frankwmason
[X]
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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:Sander Stad
ID: 33718707
Make sure the symkey database is using latin1 as its characterset.  Type "status" at mysql command line to see what the DB characterset is.  

In MySQL admin tools, you can find the characterset setting under Startup Variables and the Advanced tab.
0
 

Author Comment

by:frankwmason
ID: 33719293
Thanks for the reply sstad! Maybe I'm not following you, but in the original message I pointed out that the DB character set in the DB that fails is set to latin1, but the client connection is not. However, manually setting the client connection charset to latin1 in a command line session and trying the query results in the same error message so I'm not completely convinced the issue is the charset (although I don't know what else it might be). Here is the output from my status command:
mysql  Ver 14.14 Distrib 5.6.0-m4, for linux2.6 (x86_64) using readline 5.1

Connection id:          30
Current database:
Current user:           solar@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.0-m4 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 3 days 30 min 7 sec

Are you referring to the Server characterset and the Db characterset settings as the potential fix? I guess I'm not clear on what you are referring to when you say the symkey database. Sorry for my ignorance.
Thanks - Frank
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 33719396
read
http://forums.mysql.com/read.php?10,382397,382678#msg-382678

and consider dividing the tables
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:frankwmason
ID: 33719709
Thanks for the reply Roads! However, as mentioned, this currently works in another MySQL installation without any issue (and has for years). Also, the data types for the Create statement are almost entirely TEXT NOT NULL so this wouldn't appear to be a VARCHAR or CHAR issue which it seemed the provided link was addressing. I'm hoping to avoid rewriting the php as I am neither the original author nor a php coder by trade. What I am really hoping for is someone who might be able to tell me what is probably wrong in the database build/configuration (or perhaps it's a problem in 5.6 that didn't exist in 5.0?). I appreciate the info and will try splitting the table if I must, but these queries are dynamically built within the code so I won't have a static situation where I know I need "X" tables to accommodate the data. Also, because all but one of the columns are TEXT data types, is seems to my limited understanding that I shouldn't be having this problem in the first place, but as you can see my knowledge of MySQL is indeed limited. Thanks again!
0
 
LVL 9

Expert Comment

by:Sander Stad
ID: 33722948
This website can help you change the startup variables of the MySQL server: http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html

0
 

Expert Comment

by:soerenhartvig
ID: 36025065
The problem lies with the storage of TEXT columns these take up, up to 764 bytes of the row for each column, unless you use the ROW_FORMAT=Dynamic when you create the table, which will result in the only taking up 20 bytes of the row.

It is required that you use InnoDB with the barracuda filesystem for this to work

So configure this in your my.cnf:

innodb_file_format = barracuda
innodb_file_per_table = true

and every tables having this problem create with ROW_FORMAT=Dynamic.

 
0
 

Accepted Solution

by:
frankwmason earned 0 total points
ID: 36026266
I actually resolved the problem some months back by adding "innodb_strict_mode = 0" in the [mysqld] section of the my.cnf file and restarted. I was able to create the tables following that change.
0
 

Expert Comment

by:soerenhartvig
ID: 36026289
Ah that is good.

I think the problem we had, was because it was at insert time it came up with that error.
0
 

Author Closing Comment

by:frankwmason
ID: 36171707
After working with a MySQL DBA in our company, I found his suggestion resolved the issue immediately.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 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