frankwmason
asked on
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
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?
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?
ASKER
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
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
ASKER
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!
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ah that is good.
I think the problem we had, was because it was at insert time it came up with that error.
I think the problem we had, was because it was at insert time it came up with that error.
ASKER
After working with a MySQL DBA in our company, I found his suggestion resolved the issue immediately.
In MySQL admin tools, you can find the characterset setting under Startup Variables and the Advanced tab.