As a server administration company, we often get support requests from website and server owners.

Recently we were called on to fix this error in a Magento 2 server:

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. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Everything started when the customer added new products into Magento.

Soon after this error was displayed, and Product Re-Indexing failed (uh oh).

Indexing of several products failed, and without those indexes, the product pages started loading slow (15 secs load time instead of the usual 3 secs).

Visitors started bouncing from the site, and orders started taking a dip.

Needless to say, it was an Emergency situation, and we started working on it through our priority channel : Emergency Support Services.

Here are the various solutions we considered, and if you are in a similar situation, here’s how you can fix this error.

 

What is “Syntax error or access violation”?

That is MySQL’s way of saying it was asked to do something that’s not permitted.

In this case, the amount of data you’ve given as input was larger than the table size.

That is what it meant by saying “The Maximum row size for the used table type is 65535.”

 

What causes “Syntax error or access violation” in Magento?

Magento is built to store large amounts of data about a single product. This includes, description, attributes, price, dates, and more.

Usually Magento places these data in 6 or more database tables.

But that causes a performance penalty. It takes several seconds (if not more) to execute queries on multiple tables and then display a single product.

To make the store fast, Magento provides an option to put all this data in to a single database table,  so that all information about a product can be fetched with a single query.

This option is called a Flat Catalog.

 

 

The problem with Flat Catalog

Using a single table is a great idea to reduce database query time.

But it comes with a limitation – a limitation imposed by MySQL, that is, each product can store only 65535 Bytes (= 64 KB) of information.

If any product has more than 64 KB of information (as in descriptions, attributes, production options, etc.), it cannot be inserted into the table. MySQL will throw the error “The Maximum row size for the used table type is 65535.”

If you are not sure this is the error you are facing, click here to talk to a Magento Expert. We’re online 24/7, and can get on your case within minutes.

 

How to resolve “Syntax error or access violation” in Magento

In Magento, Flat Catalog is the most common epicenter for this error. If you’ve enabled Flat Catalog, there are basically two ways to resolve this issue:

1. Make the flat catalog row data smaller.
2. Disabling flat catalog and using full page caching to improve page loading speed.

The first option is the easier way, and the second one is for better for more complex stores. We’ll look at these options one by one.

 

1. Remove rarely used product attributes

Product attributes are a great way for your customers to quickly find out what they need.

However, you can always find out (via analytics) which attributes are used more, that lead to the most purchases.

You can then make an educated choice as to which attributes are important, and which ones can be removed.

To fix this error, find attributes that have the most amount of content, but that are used less.

If you can get rid of 3 to 5 attributes of that nature, you are done!

If you are not sure how to find out which attributes are least used, we can help. Click here to consult a Magento expert. We’re online 24/7 and we can assist you in a few minutes.

 

PS: This is the option that our customer chose to resolve this error. He had included many extra attributes to improve searches, but many were redundant in reality.

 

2. Prevent complex attributes getting into Flat Catalog

Some attributes can have multiple values (via multi-select), like say “Cloth Size”, or “Sleeve Length”.

Such attributes introduce many values in one field, and can quickly eat up space.

If you prevent these attributes from being displayed on the front end, it’ll not be listed in the Flat Catalog, and you can save space.

To do this, go to the attributes that have large content (Catalog –> Attributes –> Manage Attributes in admin area), and disable them in front end Navigation and Product listing as shown here.

If you are not sure which attributes can be harmlessly disabled, we can help you. Click here to consult a Magento expert. We’re online 24/7 and we can assist you in a few minutes.

 

 

3. Change “Multi-Select” to “Dropdown” for attribute input type

If you really can’t disable any complex attributes, try changing the attribute input type to “Dropdown” instead of “Multiselect”.

As you can imagine, using a “Multiselect” will require passing multiple values of a single attribute in a single search or listing.

Instead, if you use Dropdown, you can limit your listing and searches to 1 value per attribute, thereby saving space.

You may need to re-create the chosen attributes and change it as shown here:

 

 

4. Disable Flat Catalog and enable Page Caching

The purpose of Flat Catalog is to prevent costly multiple table database queries.

The same effect can be achieved by using a Page Caching system like Varnish, where a copy of each page will be cached in the server memory.

So, when a visitor requests a page, Magento doesn’t even have to go to the database. It can simply serve the page from memory.

 

How to disable Flat Catalog

As the first step, disable Flat Catalog by going to System –> Configuration, and toggling the settings shown here:

 

How to enable page caching in Magento 2

Magento 2 has out of the box support for Varnish, but it needs to be customized for your store.

The full details are out of the scope of this article, however, here’s a general guide.

1. Go to Stores –> Settings –> Configuration –> Advanced –> System.
2. Choose “Built-in Application” as the “Caching Application” under the “Full Page Cache” section.

 

 

This should get your store out of the ditches, but to make your store truly fast, you’ll need to use a specialized HTML caching system like Varnish.

For that you’ll need to install and setup Varnish in your server, and then choose “Varnish Caching” as your “Caching Application”.

It should be done only after considering many factors like your available server resources, size of the store, daily traffic and more.

You can do this by trial and error (and suffer a few site downtimes), or you can get our Magento experts to do this for you. Click here to consult our Magento experts. We’re online 24/7 and we can assist you in a few minutes.

 

How to enable page caching in Magento 1.x

Unlike Magento 2, there’s no built-in page cache for Magento 1.x.

However, you can use an external add-on like Turpentine (http://www.magentocommerce.com/magento-connect/turpentine-6332.html) to integrate Varnish into your Magento.

Again, this could get messy if you don’t configure the Varnish server to suit your store and traffic.

Click here to get this done for you within a few hours.

 

Conclusion

Many Magento users see the error “SQLSTATE[42000]: Syntax error or access violation:1118 Row size too large.” when updating products. A common cause for this is large data sets associated with product attributes. Today we’ve seen various ways in which this issue can be resolved.