Link to home
Start Free TrialLog in
Avatar of Dustin King
Dustin King

asked on

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.
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Dustin King
Dustin King

ASKER

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.
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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.