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,392 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

776 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