ED2 KC- Index row size 3024 exceeds btree version 4 maximum 2704

---

Background

In this example of the error the significant error is this: index row size 3024 exceeds btree version 4 maximum 2704 for index "in_trackedentityinstance_trackedentityattribute_value". Which means in this instance that for the index in_trackedentityinstance_trackedentityattribute_value , which covers the value column of the trackedentityattributevalue, there are values which are longer than the max size of btree indexes (which is 2713 bytes).  This almost always comes from bad data entered as tracker attribute values.

Resolution

The resolution is to find and remove the data that exceeds the limits, as described by the error. Below are some queries for this example. Always confirm with the client about deleting or editing production data.

This query tells us there are 9 attribute values above 1000 characters: 

dhis2=> select count(*) from trackedentityattributevalue v where length(v.value) > 1000;
 count 
-------
     9

This query tells us which values are above 1000 characters:

select v.trackedentityinstanceid, v.trackedentityattributeid, v.value from trackedentityattributevalue v where length(v.value) > 1000;

As you can see, it is a mix of whitespace, text being copy-pasted, someone resting their arm on the keyboard, etc.

We have validation for these things now, but this might be either old data or data sneaking in outside the API somehow.

Always confirm with the client about deleting or editing production data:

delete from trackedentityattributevalue where length(value) > 1000;

Example Logs

09-Jul-2021 19:02:00.757 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class [org.springframework.web.context.ContextLoaderListener]
    org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'messageManager' defined in URL [jar:file:/var/lib/tomcat/webapps/ROOT/WEB-INF/lib/dhis-support-artemis-2.34.5.jar!/org/hisp/dhis/artemis/MessageManager.class]: Unsatisfied dependency expressed through constructor parameter 2; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.hisp.dhis.render.RenderService' defined in URL [jar:file:/var/lib/tomcat/webapps/ROOT/WEB-INF/lib/dhis-service-core-2.34.5.jar!/org/hisp/dhis/render/DefaultRenderService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.hisp.dhis.schema.SchemaService' defined in URL [jar:file:/var/lib/tomcat/webapps/ROOT/WEB-INF/lib/dhis-service-schema-2.34.5.jar!/org/hisp/dhis/schema/DefaultSchemaService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.hisp.dhis.schema.PropertyIntrospectorService': Unsatisfied dependency expressed through field 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in org.hisp.dhis.db.migration.config.FlywayConfig: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
Migration V2_34_34__Add_index_in_tracker_tables.sql failed
----------------------------------------------------------
SQL State  : 54000
Error Code : 0
Message    : ERROR: index row size 3024 exceeds btree version 4 maximum 2704 for index "in_trackedentityinstance_trackedentityattribute_value"
  Detail: Index row references tuple (32865,37) in relation "trackedentityattributevalue".
  Hint: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
Location   : org/hisp/dhis/db/migration/2.34/V2_34_34__Add_index_in_tracker_tables.sql (/usr/share/tomcat/file:/var/lib/tomcat/webapps/ROOT/WEB-INF/lib/dhis-support-db-migration-2.34.5.jar!/org/hisp/dhis/db/migration/2.34/V2_34_34__Add_index_in_tracker_tables.sql)
Line       : 3