Hello everyone, Hope all are doing good.
Currently we using cratedb version 4.2.6.
In previously we are able to see all partition name , but suddenly partition is not showing but data means rows is showing per partition means ident is showing.
Tried refresh table also but issue is still there.
Hi,
There was a similar issue but it was fixed in 4.1.1
Could you share how week is defined in the table?
CREATE TABLE IF NOT EXISTS "ap_db_crate"."gpsdata_old" (
"deviceid" TEXT,
"assetid" TEXT,
"tsdbtime" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"doctype" TEXT,
"docversion" TEXT,
"info" OBJECT(DYNAMIC) AS (
"alarmstate" OBJECT(DYNAMIC) AS (
"boxstatus" TEXT,
"ignstatus" TEXT,
"msgpressed" TEXT,
"txnreason" TEXT
),
"assetInfo" OBJECT(DYNAMIC) AS (
"assetStatus" TEXT,
"assetid" TEXT,
"assetname" TEXT,
"hierarchyPath" TEXT,
"plantId" TEXT,
"plantName" TEXT,
"transporterCode" TEXT,
"transporterId" TEXT,
"transporterName" TEXT
),
"cellsite" OBJECT(DYNAMIC) AS (
"currentcellid" TEXT,
"lac" TEXT,
"mcc" TEXT,
"mnc" TEXT
),
"command" OBJECT(DYNAMIC) AS (
"auxipcode" TEXT,
"auxvalue" TEXT,
"cmdkey" TEXT,
"cmdvalue" TEXT
),
"deviceinfo" OBJECT(DYNAMIC) AS (
"devicetype" TEXT,
"hwversion" TEXT,
"id" TEXT,
"msgno" TEXT,
"rcvddts" TEXT,
"swversion" TEXT
),
"epf" OBJECT(DYNAMIC) AS (
"endtime" BIGINT,
"epfduration" BIGINT,
"epfstatus" BIGINT,
"noofevent" BIGINT,
"starttime" BIGINT,
"uid" TEXT
),
"gpsinfo" OBJECT(DYNAMIC) AS (
"altitude" TEXT,
"arialodometer" TEXT,
"course" TEXT,
"deviceodometer" TEXT,
"distance" TEXT,
"geofence" ARRAY(OBJECT(DYNAMIC) AS (
"address" OBJECT(DYNAMIC) AS (
"addressLine1" TEXT,
"addressLine2" TEXT,
"city" TEXT,
"country" TEXT,
"district" TEXT,
"pincode" TEXT,
"state" TEXT
),
"citycode" TEXT,
"hierarchyPath" TEXT,
"id" BIGINT,
"loctype" TEXT,
"name" TEXT
)),
"geofenceDO" ARRAY(OBJECT(DYNAMIC) AS (
"address" TEXT,
"citycode" TEXT,
"companyid" BIGINT,
"hierarchyPath" TEXT,
"id" BIGINT,
"loctype" TEXT,
"name" TEXT
)),
"gpsdttime" TEXT,
"gpsmode" TEXT,
"gpsvalidity" TEXT,
"lat" TEXT,
"location" TEXT,
"lon" TEXT,
"noofsatellite" TEXT,
"odometer" TEXT,
"packetStatus" TEXT,
"poi" OBJECT(DYNAMIC) AS (
"address" OBJECT(DYNAMIC) AS (
"addressLine1" TEXT,
"addressLine2" TEXT,
"city" TEXT,
"country" TEXT,
"district" TEXT,
"pincode" TEXT,
"state" TEXT
),
"distance" REAL,
"location" TEXT
),
"speed" TEXT
),
"idling" OBJECT(DYNAMIC) AS (
"arialDistance" REAL,
"endtime" BIGINT,
"idlingduration" BIGINT,
"idlingstatus" BIGINT,
"noofevent" BIGINT,
"starttime" BIGINT,
"uid" TEXT
),
"operatorinfo" OBJECT(DYNAMIC) AS (
"cgreg" TEXT,
"creg" TEXT,
"csq" TEXT,
"imsi" TEXT,
"nwop" TEXT
),
"power" OBJECT(DYNAMIC) AS (
"batterystatus" TEXT,
"batteryvoltage" TEXT,
"mainstatus" TEXT,
"mainvoltage" TEXT
)
),
"week" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS CAST(date_trunc('week', "tsdbtime") AS timestamp without time zone),
"insertiontime" TIMESTAMP WITHOUT TIME ZONE
)
CLUSTERED INTO 6 SHARDS
PARTITIONED BY ("week")
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',
refresh_interval = 1000,
"routing.allocation.enable" = 'all',
"routing.allocation.total_shards_per_node" = -1,
"store.type" = 'fs',
"translog.durability" = 'ASYNC',
"translog.flush_threshold_size" = 536870912,
"translog.sync_interval" = 5000,
"unassigned.node_left.delayed_timeout" = 60000,
"warmer.enabled" = true,
"write.wait_for_active_shards" = '1'
)
Hi,
I have not been able to reproduce this.
Could you see if the week values are visible with this query?
SELECT table_partitions.table_schema
,table_partitions.table_name
,table_partitions.partition_ident
,table_partitions.values['week']::TIMESTAMP
,table_partitions.number_of_replicas
,count (*) AS ConfShards
,count (*) FILTER (WHERE shards.STATE = 'STARTED') AS StartedShards
FROM sys.shards
JOIN information_schema.table_partitions ON shards.partition_ident = table_partitions.partition_ident
WHERE table_partitions.table_name = 'gpsdata_old'
GROUP BY 1,2,3,4,5
ORDER BY 1,2,3,4,5;
Hi Hernanc,
Yes it is not showing now. but 4-5 days before its showing properly.
And suddenly its not showing.
Hi,
Is the cluster otherwise ok and the data on these partitions accessible? (health checks?)
Do you have snapshots from before this problem started?
Would an upgrade be something you could try?
Hi,
data is accessible, even query is also working fine no issue is found.
Sorry don’t have any snapshot before the problem.
For time-being we created same table structure with another name then restore (insert into) and after fully insert, partition is showing properly to new table.
Thanks you for your help.
Hi,
Even after upgrading from 4.2.6 to 4.8.4 issue is still same.
Any update for solving this bug?
Thanks
Vinayak Katkar
Hi @Vinayak_Katkar,
you wrote earlier that the problem didn’t persist after reindexing data in a separate table. Do you see the issue now on the old or on the reindexed table?
