Error while indexing of flat data

Question

I am getting the following error while re-indexing the product flat data and i am also unable to import products it throws the 503 error.

Product Flat Data index process unknown error: exception 'PDOException' with message '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. You have to change some columns to TEXT or BLOBs'

From the error i can understand that i have too many attributes because which it is exceeding the limit but how should i resolve this ?

Thanks

Answer 1

Just set the flag 'Used in product listing' for the attributes that you don't actually need in the list and specially those that might have large content (usually textareas). I'm almost positive that you don't use those in the product list. Showing a large description in the product list might make the list unreadable.

Answer 2

If that doesn't work you can always look into column types other than VARCHAR. By default VARCHAR is set to 255 characters and say under UTF encoding it requires 2 x 255 bytes for storage. With a maximum of around 64k bytes per row you can see how you can easily eat away at that with many attributes set to VARCHAR.

If you can, then assigning columns as text or blob or another input type with variable or a smaller default you may be able to address the issue as well. Text and blob are also stored separately from the rest of the row so the limitations are alleviated naturally by using these column types.