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

Question

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

Just to be sure ... You 1) Create a snapshot on a 4.2.7 cluster, 2) then start a new cluster with 4.3.4 and 3) restore the snapshot from the 4.2.7 cluster? — proddata, Sep 15 '21 at 11:27
@proddata Yes, that's correct. If there is any other alternative is there please do let me know. PS: I'm running crate on docker — hgsongra, Sep 16 '21 at 05:38
Was that snapshot repository created with an earlier (pre 4.0) version? I can't really replicate this. (Just tried with that exact schema) Also it seems like 4.2.7 had a bug, that would it make impossible to dynamically add a fulltext indexed column after table creation, which also doesn't help. — proddata, Sep 16 '21 at 08:45
Could you run ```sql SELECT DISTINCT version['created'], version['upgraded'] FROM "information_schema"."tables" WHERE table_schema not IN ('sys','information_schema','pg_catalog') ``` and ```sql SELECT distinct min_lucene_version FROM sys.shards ``` on the non-upgraded cluster? — proddata, Sep 16 '21 at 08:59

proddata · Accepted Answer · 2021-09-20T18:18:25.850

1

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;

edited Sep 20 '21 at 18:18

answered Sep 18 '21 at 15:20

proddata

216
1
7

I've updated the question with output, Please refer. I'm not sure why it was not saved previously. Anyways, query output says tables were created in 4.x onwards and upgraded to 4.2.7 – hgsongra Sep 20 '21 at 11:32
I tried to replicate this with the upgrade path, but can't. Which leads me to think that there is still some information missing. Which version was the affected table created with? – proddata Sep 20 '21 at 18:13
I would suggest to recreated the tables within 4.2.7 (using the full schema of `SHOW CREATE TABLE` and an `INSERT INTO x SELECT * FROM y` statement or `COPY TO / FROM` – proddata Sep 20 '21 at 18:15
The affected table was created in 4.0.12. Also on your suggestion create a table again is difficult in terms of process, because we've to migrate the whole database which has 100s of tables. – hgsongra Sep 21 '21 at 10:56
Let me give it a try with REINDEXing as all tables won't have issues(figure crossed) – hgsongra Sep 21 '21 at 11:54

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

1 Answers1