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: 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.
How Flat Catalog is enabled in Magento 1.9
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.”
Disabling front end navigation of an attribute saves space in Flat Catalog table.
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.
Go to Stores –> Settings –> Configuration –> Advanced –> System.
Choose “Built-in Application” as the “Caching Application” under the “Full Page Cache” section.
Basic cache setup in Magento 2
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.
Many Magento users see the error “SQLSTATE: 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.
How to fix Magento error “Syntax error or access violation: 1118 Row size too large” was last modified: March 24th, 2018 by Visakh S
Is your website fast enough? Majority visitors abandon sites that take over 3 seconds to load. Slow websites lead to business loss, and that’s when server owners get panicky.
Among the various web servers available, Apache still holds a major market share. In our server administration services, we often get requests from server owners who complain about Apache slowness. (more…)
Apache slow? Boost your web server performance in 3 steps was last modified: March 17th, 2018 by Reeshma Mathews
Majority web applications are database driven. MySQL is a popular database server for open source applications, but we’ve seen that it can cause performance issues in the long run.
MySQL databases grow in size, and tables get fragmented over time. This contributes to MySQL load spikes. So, protecting a server from MySQL high CPU issues requires close monitoring and periodic optimization. (more…)
How to fix MySQL high CPU usage was last modified: March 20th, 2018 by Visakh S
An error we sometimes see in cPanel servers is : Account Creation Status: failed
This is usually appended with various reasons for the error such as “A DNS entry already exists”, or “Unable to find an IP address” or “domain is already setup” or “USERNAME is a reserved username on this system”. (more…)
How to fix “Account Creation Status: failed” errors in cPanel & WHM servers was last modified: March 9th, 2018 by Visakh S
Bobcares is a server management company that helps businesses deliver uninterrupted and secure online services. Our engineers manage close to 51,500 servers that include virtualized servers, cloud infrastructure, physical server clusters, and more.