SQL Error 1118 Max row size 65535

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.
Dustin KingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
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.
Dustin KingAuthor 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.
Chris StanyonWebDevCommented:
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.
skullnobrainsCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
wamp

From novice to tech pro — start learning today.