Adding column generates error

CrateDB SQL
1

The instruction: ALTER TABLE {tablename} ADD {column name} double precision; generates the error: mapper has different length_limit settings, current [512], merged [null]. What does this mean?

The instruction works in our development environment but fails in production.

1 Like
2

Hi @wvdamme

Could I ask you provide some more information?

  • CrateDB Version
  • table schema (i.e. SHOW CREATE TABLE <table_name>)
3

CrateDB version:

CrateDB 4.7.0 (built 033afc5/2022-01-26T08:07:43Z
Linux 4.19.0-18-amd64 amd64
OpenJDK 64-Bit Server VM 17.0.1+12)

Table script

CREATE TABLE IF NOT EXISTS "data"."meterreading" (
   "servicepointassetenergytixcode" VARCHAR(512),
   "datetime" TIMESTAMP WITH TIME ZONE,
   "recalculatedconsumption1" DOUBLE PRECISION,
   "recalculatedconsumption2" DOUBLE PRECISION,
   "recalculatedinjection1" DOUBLE PRECISION,
   "recalculatedinjection2" DOUBLE PRECISION,
   "recalculatedactualconsumption" DOUBLE PRECISION,
   "recalculatedactualinjection" DOUBLE PRECISION,
   "recalculatedtotalapparentpower" DOUBLE PRECISION,
   "recalculatedtotalreactivepower" DOUBLE PRECISION,
   "recalculatedactivepowerphase1" DOUBLE PRECISION,
   "recalculatedactivepowerphase2" DOUBLE PRECISION,
   "recalculatedactivepowerphase3" DOUBLE PRECISION,
   "recalculatedvoltagel1l2" DOUBLE PRECISION,
   "recalculatedvoltagel2l3" DOUBLE PRECISION,
   "recalculatedvoltagel3l1" DOUBLE PRECISION,
   "recalculatedvoltagellavg" DOUBLE PRECISION,
   "recalculatedvoltagel1n" DOUBLE PRECISION,
   "recalculatedvoltagel2n" DOUBLE PRECISION,
   "recalculatedvoltagel3n" DOUBLE PRECISION,
   "recalculatedvoltagelnavg" DOUBLE PRECISION,
   "recalculatedcurrentphase1" DOUBLE PRECISION,
   "recalculatedcurrentphase2" DOUBLE PRECISION,
   "recalculatedcurrentphase3" DOUBLE PRECISION,
   "recalculatedcurrentavg" DOUBLE PRECISION,
   "recalculatedtotalpowerfactor" DOUBLE PRECISION,
   "recalculatedfrequency" DOUBLE PRECISION,
   "recalculateddatagramdatetime" TIMESTAMP WITH TIME ZONE,
   "recalculatedproduction1" DOUBLE PRECISION,
   "recalculatedproduction2" DOUBLE PRECISION,
   "recalculatedactualproduction" DOUBLE PRECISION,
   "recalculatedpulsequantity2" DOUBLE PRECISION,
   PRIMARY KEY ("servicepointassetenergytixcode", "datetime")
)
CLUSTERED INTO 6 SHARDS
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'default',
   column_policy = 'strict',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

i have seen that the datatype of the field servicepointassetenergytixcode is varchar(512) in our development is this Text. Could this be the reason? How can we change this datatype?

1 Like
4

Hi @wvdamme

This unfortunately seems to be a bug with CrateDB, when VARCHARs are used as PRIMARY KEY.

I created an issue in the crate/crate repository:

Thank you for reporting this.

VARCHAR as primary key leads to an exception

CREATE TABLE tab ( 
        x VARCHAR(512), 
        PRIMARY KEY(x) 
        );                                                                                                                                                                                  
--CREATE OK, 1 row affected  (2.033 sec)
ALTER TABLE tab ADD COLUMN y DOUBLE;                                                                                                                                                    
--SQLParseException[mapper [x] has different length_limit settings, current [512], merged [null]]

TEXT as primary key works as expected

CREATE TABLE tab ( 
        x TEXT, 
        PRIMARY KEY(x) 
        );                                                                                                                                                                                  
--CREATE OK, 1 row affected  (1.806 sec)
ALTER TABLE tab ADD COLUMN y DOUBLE;                                                                                                                                                    
--ALTER OK, -1 rows affected  (0.218 sec)
1 Like