Solved: Syntax Error or Access Violation: 1118 Row Size too Large in Magento 2
Ever faced an error in Magento that says:
“A syntax error or access violation: 1118 row size too large in Magento 2“.
On adding products in Magento 2, an error is displayed and even the product re-indexing fails. Without those indexes, the loading speed of several products decreases.
As a result, the bounce rate starts to increase and the sales start decreasing gradually.
“Syntax error or access violation” occurs when the amount of data that is given as an input is more than the table size. And generally, the maximum row size for the used table type is 65535 bytes.
What causes “Magento 2 Syntax error or access violation”?
A large amount of data about a single product such as description, attributes, price, date, etc. is stored in Magento. And this data is stored in 6 or more database tables.
The – “Magento 2 Syntax error or access violation” error usually occurs when you opt for a Flat catalog. Magento provides an option to put all the information in a single table, so that all the data of a particular product can be fetched at a time, making the store faster.
The only reason to use a single table is to reduce database query time. Every product can store only 65535 bytes of information. If the product has more than 64 KB of information, then it will not be inserted into the table and an error will be displayed.
This error can be resolved in two ways which are as follows:
- To make the flat catalog row data smaller
- To disable the flat catalog using full page caching
From the above two options, row data can be reduced easily. Whereas, to disable the flat catalog would be much better for complex stores.
Thus, you can use the below code to solve the error which is as below:
Method to resolve Syntax Error or Access Violation: 1118 Row Size too Large in Magento 2:
Syntax:
|
1 |
ALTER TABLE `table_name` ROW_FORMAT=DYNAMIC; |
Example:
|
1 |
ALTER TABLE sales_order_address ROW_FORMAT=DYNAMIC; |
That’s it.
Please mention any doubts about the error in the comment section below. I’d be happy to help you.
Also, I’d be grateful if you could share the solution with the Magento community via social media.
Thank you.
Prev
How to Add Event in Google Calendar Using PHP
Meetanshi Magento Christmas Offers 2021 – Amazing Deals on Magento Extensions
Next