Error creating new express attribute via dashboard

I tried adding a new checkbox attribute to an express entity through the dashboard and it threw this error:

An exception occurred while executing 'ALTER TABLE AcademicProgramExpressSearchIndexAttributes ADD ak_academic_program_create_page TINYINT(1) DEFAULT '0'':

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 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs (0)

If I try to add the new attribute again, it tells me an attribute already exists with that handle, but it doesn’t show in the attribute list. Any ideas about what happened and how to get it to show up properly?

The actual error message relates to the index table. This is where copies of express attribute values are kept to facilitate searching. MySQL has a maximum cumulative size for a row in a table. The simplest way to get away from that is to not make every attribute searchable.

However, I don’t know if clearing the searchability checkbox on some attributes will automatically remove their columns from that table. The core is not very good at tidying up after itself. Running rebuild search index may do that, but again I don’t know. You may end up having to both clear the searchability checkbox and manually edit the schema for that table to match.

While reducing the number of searchable attributes, when fully cleaned up, should avoid the MySQL error. I don’t know if that will avoid your problem with ‘handle already exists’. You could use a different handle, but that would also leave the initial handle as unreclaimed garbage in the database.

Ah, ok. Thanks @JohntheFish. I will mess around with search settings and reindexing to see if that helps. An attribute gets added to this table if either the search index or advanced search boxes are checked, or only the search index?

If you are using MySQL 5.6, upgrade to >= 5.7.

Here’s some background that might help with regards to other settings: LongKeysUnsupportedByCollation exception too technical? · Issue #7360 · concretecms/concretecms · GitHub

1 Like