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
2,356 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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:Sander Stad
Comment Utility
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
Comment Utility
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
Comment Utility
read
http://forums.mysql.com/read.php?10,382397,382678#msg-382678

and consider dividing the tables
0
 

Author Comment

by:frankwmason
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 9

Expert Comment

by:Sander Stad
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
After working with a MySQL DBA in our company, I found his suggestion resolved the issue immediately.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Router for PHP reqeusts 12 29
mySql Syntax 7 20
php variable basic question 12 27
Help cleaning out CSS 2 21
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 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

10 Experts available now in Live!

Get 1:1 Help Now