When setting is catalog_flat_product = YES than below error is display when we do re-index using ssh

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, query was: CREATE TEMPORARY TABLE catalog_product_entity_varchar_tmp_indexer............

Eav and catalog search is skipping Kindly help i am stuck on this why this is coming.. almost 550 attributes is there.

Product EAV index is locked by another reindex process. Skipping. Catalog Search index is locked by another reindex process. Skipping.


I guess you don't need all that product attributes in category or search result lists.

I would suggest to verify which attributes have the option "Use in Product Listing" set to "Yes". Change this to "No" for all attributes you don't need in category or search result lists.

Only attributes with that option set to "Yes" come in the flat table, so you should be able to significantly reduce the amount of columns in flat table.

I hope that helps you.

  • ok i will check
    – Satish V
    Mar 2 '19 at 19:16
  • Hi, I have changed the "Use in Product Listing" = "NO" not worked any other solution I see long sql query in console for row size is too large ..
    – Satish V
    Mar 2 '19 at 19:46
  • How many attributes, especially of type varchar you have with "Use in product listing" = yes? You can easily calculate that you can use no more than ~250 varchar attributes to be below the MySQL limit. You can check the number of attributes using SQL: select count(*) from catalog_eav_attribute where used_in_product_listing = 1; This must be below 250 than you will be able to build the flat table.
    – HelgeB
    Mar 2 '19 at 19:58
  • total 460 attribute i change all to NO now its 0
    – Satish V
    Mar 2 '19 at 20:07
  • dev.mysql.com/doc/refman/8.0/en/column-count-limit.html here is the reference, the number of varchar(255) columns is lower than I assumed in my comment since MySQL needs 767 bytes for a varchar(255) column. So we have a limit of only ~85 varchar columns
    – HelgeB
    Mar 2 '19 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.