SQL Error 1118 Max row size 65535

Dustin King
Dustin King used Ask the Experts™
on
I am using WAMP on my Windows 10 machine.

I am having the same issue on my Ubuntu machine.

When I add to many columns to my database I get an error:

Rebuild database fault: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.

Open in new window


I have gone to the my.ini file and added:
innodb_strict_mode=0

I have also tried increasing my log file size to 512 and the buffer size to 2048 just to see. Still no change.

Any time I try and add another column in mysql I get the error.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
The maximum amount of data a row (record) can hold is about 64k. It sounds like your table definition is exceeding this limit, so you'll need to reduce the amount of columns or the size of the data in those columns. For example, if you try to define 7 VARCHAR columns, each set to a size of 10000, then you'll exceed the limit and get that error.

Author

Commented:
So I was able to get this working in the past on another server having the same issue.

I cannot remember what I did exactly, but I know it was a few settings on the server and it was able to override the issue.

I'm hoping someone can point me to how to override the issue if possible.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
From the mySQL docs:

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows.

So, you can't have a row larger that 64k, no matter what server settings you use. You can however reduce the row size by changing some of the column definitions. The maximum row size of 64k does not take into account columns that are set as TEXT or BLOB, because the data for these columns is not actually stored in the row. If you have a lot of VARCHAR columns for example, you could try changing them to TEXT. Alternatively, you can reduce the size of some of the columns defs - for example, instead of VARCHAR(1000), try VARCHAR(256) - obviously this will depend on the data you need to store.
you can try to increase the  innodb_page_size to 65k * 2 : the default page size will limit you to about 8k per row


if you already use bigger page sizes, read on

blob and text column require at most 12 bytes for the biggest possible blobs so converting CHAR or VARCHARS to TEXT or BLOBS should help.

you can also split the data in multiple tables and possibly join them with a view.

you can use collations that use less bytes per character if you do not need UTF8 or other multibyte charsets.

you may want to post the creation query.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial