javasqlelasticsearchcratecratedb

CRATE db tables are causing an error when upgrading to CRATE:4.3.4 from CRATE:4.2.7


Facing the below error while restoring snapshots or upgrade cratedb to a higher version from 4.2.7 to 4.3.4

SnapshotRestoreException[[repo_name:snapshot_name/******] cannot restore index [schema_name.table_name] because it cannot be upgraded]
org.elasticsearch.snapshots.SnapshotRestoreException: [repo_name:snapshot_name/******] cannot restore index [schema_name.table_name] because it cannot be upgraded
    at org.elasticsearch.snapshots.RestoreService$1.execute(RestoreService.java:273)
    at org.elasticsearch.cluster.ClusterStateUpdateTask.execute(ClusterStateUpdateTask.java:45)
    at org.elasticsearch.cluster.service.MasterService.executeTasks(MasterService.java:681)
    at org.elasticsearch.cluster.service.MasterService.calculateTaskOutputs(MasterService.java:307)
    at org.elasticsearch.cluster.service.MasterService.runTasks(MasterService.java:207)
    at org.elasticsearch.cluster.service.MasterService$Batcher.run(MasterService.java:139)
    at org.elasticsearch.cluster.service.TaskBatcher.runIfNotProcessed(TaskBatcher.java:153)
    at org.elasticsearch.cluster.service.TaskBatcher$BatchedTask.run(TaskBatcher.java:191)
    at org.elasticsearch.common.util.concurrent.PrioritizedEsThreadPoolExecutor$TieBreakingPrioritizedRunnable.runAndClean(PrioritizedEsThreadPoolExecutor.java:258)
    at org.elasticsearch.common.util.concurrent.PrioritizedEsThreadPoolExecutor$TieBreakingPrioritizedRunnable.run(PrioritizedEsThreadPoolExecutor.java:221)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
    at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: java.lang.IllegalStateException: unable to upgrade the mappings for the index [[repo_name:snapshot_name/******]]
    at org.elasticsearch.cluster.metadata.MetadataIndexUpgradeService.checkMappingsCompatibility(MetadataIndexUpgradeService.java:175)
    at org.elasticsearch.cluster.metadata.MetadataIndexUpgradeService.upgradeIndexMetadata(MetadataIndexUpgradeService.java:98)
    at org.elasticsearch.snapshots.RestoreService$1.execute(RestoreService.java:270)
    ... 12 more
Caused by: org.elasticsearch.index.mapper.MapperParsingException: Failed to parse mapping [default]: Mapping definition for [dynamicFieldName3] has unsupported parameters:  [fields : {keyword={type=keyword, ignore_above=256}}]
    at org.elasticsearch.index.mapper.MapperService.internalMerge(MapperService.java:285)
    at org.elasticsearch.index.mapper.MapperService.internalMerge(MapperService.java:272)
    at org.elasticsearch.index.mapper.MapperService.merge(MapperService.java:251)
    at org.elasticsearch.cluster.metadata.MetadataIndexUpgradeService.checkMappingsCompatibility(MetadataIndexUpgradeService.java:171)
    ... 14 more
Caused by: org.elasticsearch.index.mapper.MapperParsingException: Mapping definition for [dynamicFieldName3] has unsupported parameters:  [fields : {keyword={type=keyword, ignore_above=256}}]
    at org.elasticsearch.index.mapper.DocumentMapperParser.checkNoRemainingFields(DocumentMapperParser.java:153)
    at org.elasticsearch.index.mapper.DocumentMapperParser.checkNoRemainingFields(DocumentMapperParser.java:147)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseProperties(ObjectMapper.java:215)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseObjectOrDocumentTypeProperties(ObjectMapper.java:163)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parse(ObjectMapper.java:134)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseProperties(ObjectMapper.java:207)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseObjectOrDocumentTypeProperties(ObjectMapper.java:163)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parse(ObjectMapper.java:134)
    at org.elasticsearch.index.mapper.ArrayTypeParser.parse(ArrayTypeParser.java:51)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseProperties(ObjectMapper.java:207)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseObjectOrDocumentTypeProperties(ObjectMapper.java:163)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parse(ObjectMapper.java:134)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseProperties(ObjectMapper.java:207)
    at org.elasticsearch.index.mapper.ObjectMapper$TypeParser.parseObjectOrDocumentTypeProperties(ObjectMapper.java:163)
    at org.elasticsearch.index.mapper.RootObjectMapper$TypeParser.parse(RootObjectMapper.java:103)
    at org.elasticsearch.index.mapper.DocumentMapperParser.parse(DocumentMapperParser.java:101)
    at org.elasticsearch.index.mapper.DocumentMapperParser.parse(DocumentMapperParser.java:89)
    at org.elasticsearch.index.mapper.MapperService.internalMerge(MapperService.java:283)

Below is the sample table creation schema.

CREATE TABLE IF NOT EXISTS TABLE_NAME (
  "id" STRING,
  "field1" STRING NOT NULL,
  "field2" STRING NOT NULL,
  "field3" OBJECT NOT NULL,
  "createdAt" TIMESTAMP NOT NULL,
  "updatedAt" TIMESTAMP NOT NULL,
  PRIMARY KEY ("field1", "field2")
)

As field3 is the dynamic object, it'll allow adding more attributes into it.

So, now after data is inserted into the table the current schema is as follows

CREATE TABLE IF NOT EXISTS TABLE_NAME (
  "id" STRING,
  "field1" STRING NOT NULL,
  "field2" STRING NOT NULL,
  "field3" OBJECT(DYNAMIC) AS (
      "dynamicField1" BOOLEAN,
      "dynamicField2" TEXT,
      "dynamicField3" TEXT INDEX USING FULLTEXT,
      "dynamicField4" TEXT
   )  NOT NULL,
  "createdAt" TIMESTAMP NOT NULL,
  "updatedAt" TIMESTAMP NOT NULL,
  PRIMARY KEY ("field1", "field2")
)
...
...
...

I've taken a snapshot and now I'm trying to restore/upgrade to a higher version, which is facing with above-mentioned error.

Update 1:

SELECT DISTINCT version['created'], version['upgraded'] FROM "information_schema"."tables" WHERE table_schema not IN ('sys','information_schema','pg_catalog') limit 100;

Result from query:
version['created']  version['upgraded']
4.0.12              4.2.7 
4.1.5               4.2.7 
4.2.7               NULL 
SELECT distinct min_lucene_version FROM sys.shards limit 100;

Result from query:

min_lucene_version
8.0.0 
8.5.1 
8.4.0

Solution

  • The above mentioned error

    [fields : {keyword={type=keyword, ignore_above=256}}]

    hints, that you would want to upgrade/restore a table / snapshot created with a CrateDB version <3.0 to CrateDB 4.x

    CrateDB 4.x only supports tables created with 3.x and up CrateDB 3.x only supported tables created with 2.x and up

    A snapshot is basically just a copy of the Lucene indexes/segments

    As mentioned in the comment, you should run the following queries to check, which version the tables were created with:

    SELECT DISTINCT version['created'], version['upgraded'] FROM "information_schema"."tables" WHERE table_schema not IN ('sys','information_schema','pg_catalog')
    
    SELECT distinct min_lucene_version FROM sys.shards
    

    If only older tables are affected I would suggest to reindex those tables using 4.2.7 and then run the upgrade

    SHOW CREATE table old;
    INSERT INTO new SELECT * FROM old;
    --or
    COPY old TO;
    COPY new FROM;