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,397 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

828 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