ShadowManager is broken due to corrupted migration

Details:

• greengrass componentVersion: 2.10.1
• shadow manager componentVersion: 2.3.2

Core device ended up in a state where the ShadowManager component cannot startup in a healthy state. It consistently starts in "BROKEN". I have tried a) restarting greengrass b) revising the deployment

This looks similar to an issue that was reported to be fixed in ShadowManager 2.3.2 "Fixes an issue where shadow manager enters the BROKEN state when the local shadow database is corrupted."

Here are the most relevant log statements I can find:

root@Cognito30:/greengrass/v2/logs# cat greengrass.log | grep -i error | head
{"thread":"pool-2-thread-26","level":"ERROR","eventType":null,"message":"Migration of schema \"PUBLIC\" to version 1 - InitialRelease failed! Please restore backups and roll back database and code!","contexts":{},"loggerName":"org.flywaydb.core.internal.command.DbMigrate","timestamp":1692577140506,"cause":null}
{"thread":"pool-2-thread-26","level":"ERROR","eventType":"service-errored","message":"","contexts":{"serviceName":"aws.greengrass.ShadowManager","currentState":"NEW"},"loggerName":"com.aws.greengrass.shadowmanager.ShadowManager","timestamp":1692577142255,"cause":{"message":"(Message 42S01 not found); SQL statement:\nCREATE TABLE documents (\n    thingName VARCHAR(255) NOT NULL,\n    shadowName VARCHAR(255) NOT NULL,\n    document TEXT,\n    version NUMBER DEFAULT 1,\n    deleted BIT DEFAULT 0,\n    updateTime NUMBER,\n    PRIMARY KEY (thingName, shadowName)\n) [42101-200]","suppressed":[],"stackTrace":[{"methodName":"getJdbcSQLException","fileName":"DbException.java","lineNumber":453,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.message.DbException","nativeMethod":false},{"methodName":"getJdbcSQLException","fileName":"DbException.java","lineNumber":429,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.message.DbException","nativeMethod":false},{"methodName":"get","fileName":"DbException.java","lineNumber":205,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.message.DbException","nativeMethod":false},{"methodName":"get","fileName":"DbException.java","lineNumber":181,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.message.DbException","nativeMethod":false},{"methodName":"update","fileName":"CreateTable.java","lineNumber":89,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.command.ddl.CreateTable","nativeMethod":false},{"methodName":"update","fileName":"CommandContainer.java","lineNumber":198,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.command.CommandContainer","nativeMethod":false},{"methodName":"executeUpdate","fileName":"Command.java","lineNumber":251,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.command.Command","nativeMethod":false},{"methodName":"executeInternal","fileName":"JdbcStatement.java","lineNumber":228,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.jdbc.JdbcStatement","nativeMethod":false},{"methodName":"execute","fileName":"JdbcStatement.java","lineNumber":201,"className":"com.aws.greengrass.shadowmanager.lib.org.h2.jdbc.JdbcStatement","nativeMethod":false},{"methodName":"executeStatement","fileName":"JdbcTemplate.java","lineNumber":244,"className":"org.flywaydb.core.internal.jdbc.JdbcTemplate","nativeMethod":false},{"methodName":"execute","fileName":"ParsedSqlStatement.java","lineNumber":111,"className":"org.flywaydb.core.internal.sqlscript.ParsedSqlStatement","nativeMethod":false},{"methodName":"executeStatement","fileName":"DefaultSqlScriptExecutor.java","lineNumber":208,"className":"org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor","nativeMethod":false},{"methodName":"execute","fileName":"DefaultSqlScriptExecutor.java","lineNumber":127,"className":"org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor","nativeMethod":false},{"methodName":"executeOnce","fileName":"SqlMigrationExecutor.java","lineNumber":88,"className":"org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor","nativeMethod":false},{"methodName":"access$000","fileName":"SqlMigrationExecutor.java","lineNumber":33,"className":"org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor","nativeMethod":false},{"methodName":"call","fileName":"SqlMigrationExecutor.java","lineNumber":77,"className":"org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1","nativeMethod":false},{"methodName":"call","fileName":"SqlMigrationExecutor.java","lineNumber":74,"className":"org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1","nativeMethod":false},{"methodName":"execute","fileName":"DefaultExecutionStrategy.java","lineNumber":28,"className":"org.flywaydb.core.internal.database.DefaultExecutionStrategy","nativeMethod":false},{"methodName":"execute","fileName":"SqlMigrationExecutor.java","lineNumber":74,"className":"org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor","nativeMethod":false},{"methodName":"doMigrateGroup","fileName":"DbMigrate.java","lineNumber":366,"className":"org.flywaydb.core.internal.command.DbMigrate","nativeMethod":false},{"methodName":"access$200","fileName":"DbMigrate.java","lineNumber":54,"className":"org.flywaydb.core.internal.command.DbMigrate","nativeMethod":false},{"methodName":"call","fileName":"DbMigrate.java","lineNumber":282,"className":"org.flywaydb.core.internal.command.DbMigrate$3","nativeMethod":false},{"methodName":"execute","fileName":"TransactionalExecutionTemplate.java","lineNumber":66,"className":"org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate","nativeMethod":false},{"methodName":"applyMigrations","fileName":"DbMigrate.java","lineNumber":279,"className":"org.flywaydb.core.internal.command.DbMigrate","nativeMethod":false},{"methodName":"migrateGroup","fileName":"DbMigrate.java","lineNumber":244,"className":"org.flywaydb.core.internal.command.DbMigrate","nativeMethod":false},{"methodName":"access$100","fileName":"DbMigrate.java","lineNumber":54,"className":"org.flywaydb.core.internal.command.DbMigrate","nativeMethod":false},{"methodName":"call","fileName":"DbMigrate.java","lineNumber":162,"className":"org.flywaydb.core.internal.command.DbMigrate$2","nativeMethod":false},{"methodName":"call","fileName":"DbMigrate.java","lineNumber":159,"className":"org.flywaydb.core.internal.command.DbMigrate$2","nativeMethod":false},{"methodName":"call","fileName":"TableLockingExecutionTemplate.java","lineNumber":38,"className":"org.flywaydb.core.internal.jdbc.TableLockingExecutionTemplate$1","nativeMethod":false},{"methodName":"execute","fileName":"TransactionalExecutionTemplate.java","lineNumber":66,"className":"org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate","nativeMethod":false},{"methodName":"execute","fileName":"TableLockingExecutionTemplate.java","lineNumber":33,"className":"org.flywaydb.core.internal.jdbc.TableLockingExecutionTemplate","nativeMethod":false},{"methodName":"lock","fileName":"Connection.java","lineNumber":129,"className":"org.flywaydb.core.internal.database.base.Connection","nativeMethod":false},{"methodName":"lock","fileName":"JdbcTableSchemaHistory.java","lineNumber":140,"className":"org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory","nativeMethod":false},{"methodName":"migrateAll","fileName":"DbMigrate.java","lineNumber":159,"className":"org.flywaydb.core.internal.command.DbMigrate","nativeMethod":false},{"methodName":"migrate","fileName":"DbMigrate.java","lineNumber":137,"className":"org.flywaydb.core.internal.command.DbMigrate","nativeMethod":false},{"methodName":"execute","fileName":"Flyway.java","lineNumber":206,"className":"org.flywaydb.core.Flyway$1","nativeMethod":false},{"methodName":"execute","fileName":"Flyway.java","lineNumber":158,"className":"org.flywaydb.core.Flyway$1","nativeMethod":false},{"methodName":"execute","fileName":"Flyway.java","lineNumber":527,"className":"org.flywaydb.core.Flyway","nativeMethod":false},{"methodName":"migrate","fileName":"Flyway.java","lineNumber":158,"className":"org.flywaydb.core.Flyway","nativeMethod":false},{"methodName":"migrateDB","fileName":"ShadowManagerDatabase.java","lineNumber":103,"className":"com.aws.greengrass.shadowmanager.ShadowManagerDatabase","nativeMethod":false},{"methodName":"migrateAndGetResult","fileName":"ShadowManagerDatabase.java","lineNumber":108,"className":"com.aws.greengrass.shadowmanager.ShadowManagerDatabase","nativeMethod":false},{"methodName":"install","fileName":"ShadowManagerDatabase.java","lineNumber":86,"className":"com.aws.greengrass.shadowmanager.ShadowManagerDatabase","nativeMethod":false},{"methodName":"install","fileName":"ShadowManager.java","lineNumber":236,"className":"com.aws.greengrass.shadowmanager.ShadowManager","nativeMethod":false},{"methodName":"install","fileName":"ShadowManager.java","lineNumber":225,"className":"com.aws.greengrass.shadowmanager.ShadowManager","nativeMethod":false},{"methodName":"lambda$handleCurrentStateNew$5","fileName":"Lifecycle.java","lineNumber":502,"className":"com.aws.greengrass.lifecyclemanager.Lifecycle","nativeMethod":false},{"methodName":"call","fileName":"Executors.java","lineNumber":511,"className":"java.util.concurrent.Executors$RunnableAdapter","nativeMethod":false},{"methodName":"run","fileName":"FutureTask.java","lineNumber":266,"className":"java.util.concurrent.FutureTask","nativeMethod":false},{"methodName":"runWorker","fileName":"ThreadPoolExecutor.java","lineNumber":1149,"className":"java.util.concurrent.ThreadPoolExecutor","nativeMethod":false},{"methodName":"run","fileName":"ThreadPoolExecutor.java","lineNumber":624,"className":"java.util.concurrent.ThreadPoolExecutor$Worker","nativeMethod":false},{"methodName":"run","fileName":"Thread.java","lineNumber":750,"className":"java.lang.Thread","nativeMethod":false}],"cause":null}}
{"thread":"pool-2-thread-26","level":"DEBUG","eventType":"service-report-state","message":"","contexts":{"serviceName":"aws.greengrass.ShadowManager","currentState":"NEW","newState":"ERRORED"},"loggerName":"com.aws.greengrass.shadowmanager.ShadowManager","timestamp":1692577142471,"cause":null}

Questions:

1. What is the root cause?
2. How do I fix this?
3. How do I prevent this from happening again?