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
Managing a website can be hard. To keep your site business ready, you need to routinely optimize the databases, replace bulky plugins, renew SSL certificates, upgrade software, optimize pages for SEO, and more.
That’s quite a lot of work, considering you still need to update your pages, product list and features to keep your site user friendly. But where do you get the time for all this when you have a business to run? That is why you need a dedicated expert to maintain your website.
Which leads us to the question : How do you choose a reliable website support company for your business? (more…)
How to choose the right web support company – 5 point checklist was last modified: March 1st, 2018 by Visakh S
Are you a WordPress website owner? You’d have come across this notification in your WordPress admin dashboard atleast once – ‘An updated version of WordPress is available.’
WordPress is the most popular CMS and they often releases updates to their software, with feature updates or security patches. You’d also find an ‘Update Now’ button in the dashboard to update WordPress version in a click. (more…)
How to update WordPress version without website downtime was last modified: January 27th, 2018 by Reeshma Mathews
WordPress powers 19% of the web, and 48 of the top 100 blog sites online. With a strong community of users and developers, the WordPress platform is evolving day by day, with more features and Add-ons.
This, in turn, poses some threats as well. Vulnerabilities and hacks can end up disrupting the website functioning. There have been many instances where a blog owner lost complete access to his site. (more…)
How to secure WordPress website from hacks! was last modified: September 7th, 2017 by Reeshma Mathews
Google forums get bombarded by customer complaints whenever they see a ‘502 server error – Bad Gateway’ in their Google applications. Though an intermittent error, it can often shoo away visitors from your website.
Many often, development companies have multiple WordPress projects, which creates so much overhead and can incur software conflicts. From software development to DevOps, we cover all the stages involved in the project management for WordPress, for our customers. (more…)
How to handle multiple project management for WordPress without incurring software conflict was last modified: July 18th, 2017 by Reeshma Mathews
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.