I am trying to do a simple select * operation on column store tables in HANA Database. I observed that doing a select * on some of the tables in Database was resulting in the following error :
column store error: search table error: [6923] Attribute load failed.SAP DBTech JDBC: [2048]:
Some Column store tables are not getting loaded due to this error.
I restarted the database instance for cal.sap.com account allotted to me, but it did not solve the issue. I then suspended the instance and re-activated it, However it resulted in same issue with other tables in database which were getting selected properly before, i.e. I was able to read their data.
I noticed that it occurs when table has one or more columns of type ‘TIME’ /’TIMESTAMP’ in HANA, which typically store timestamp values in hours:minutes:seconds:millisecond format.
This is inconsistent behaviour as I am able to do select * operation on these tables all the time, except for few instances where select * fails and this issue crops up. I delete the table contents and then load them again with same data and then table load (selecting values from the table) works fine. I tried unloading table from memory and loading it again, but it does not help solving the issue.
UPDATE : Trace File Contents after this error occurs :
ste::Exception type AttributeStoreFile error '1090922094766': attribute value is not a date or wrong syntax message additionalInfo $ADDINFO$ exception throw location:
CS/OptimizeCompression/Helper.cpp:431
Attribute load failed; $message$=Attribute load failed;index=HAULMAX_UAT_SHIPMENT:Shipmenten,attribute='PickupTime' (228)
exception 6923:
CS/OptimizeCompression/Helper.cpp:431
Attribute load failed; $message$=Attribute load failed;index=HAULMAX_UAT_SHIPMENT:Shipmenten,attribute='ActualDelTime' (212)
(6923)' (table=HAULMAX_UAT_SHIPMENT:Shipment (t 1337), passport=)
[4810]{-1}[202/-1] 2017-06-27 10:48:52.458400 e optimize_compres OptimizeCompressionData.cpp(00857) : return value: false, error: exception 6923:
CS/OptimizeCompression/Helper.cpp:431
Attribute load failed; $message$=Attribute load failed;index=HAULMAX_UAT_SHIPMENT:Shipmenten,attribute='PickupTime' (228)
exception 6923:
CS/OptimizeCompression/Helper.cpp:431
Attribute load failed; $message$=Attribute load failed;index=HAULMAX_UAT_SHIPMENT:Shipmenten,attribute='ActualDelTime' (212)
(6923) (table=HAULMAX_UAT_SHIPMENT:Shipment (t 1337), passport=)
[4810]{-1}[200/16652466] 2017-06-27 10:48:52.459866 e Mergedog Mergedog.cpp(01338) : Optimize compression failed: {IndexName: HAULMAX_UAT_SHIPMENT:Shipment, Trigger: 7, ReclaimDelta: false, DeltaMerge: false, OptimizeCompression: true, MainSize: 1119210}, error: exception 6923:
CS/OptimizeCompression/Helper.cpp:431
Attribute load failed; $message$=Attribute load failed;index=HAULMAX_UAT_SHIPMENT:Shipmenten,attribute='PickupTime' (228)
exception 6923:
CS/OptimizeCompression/Helper.cpp:431
Attribute load failed; $message$=Attribute load failed;index=HAULMAX_UAT_SHIPMENT:Shipmenten,attribute='ActualDelTime' (212)
(6923)
To learn the actual reason for the column loading problem, you will have to check the indexserver trace file. My guess, however, at this point would be that you're facing an out-of-memory situation when trying to load the column.
With the trace file excerpt added to the question, the situation appears as follows:
during the "compression optimisation" part of the "delta merge" for your table "HAULMAX_UAT_SHIPMENT"."Shipment". During this step, all columns of a table are loaded into memory to determine the optimal compression technique for every column.
Since the sorting of data in a column not only affects which compression technique is most efficient for this column but also how all other columns must be sorted, this is a rather complex problem that the "compression optimisation" tries to solve here.
Anyhow, that is the reason for all columns being loaded without any action from user side.
stopping/re-starting the instance won't help here, as the compression optimisation will be triggered again by the next delta merge.
there's too little data available here, to determine whether this is a HANA bug or e.g. a data corruption in one of the columns ("PickupTime" or "ActualTime").
Therefore, I recommend opening a SAP support incident to ivestigate and solve this specific issue.
Meanwhile, the compression optimisation can be avoided by disabling the automatism for this specific table:
ALTER TABLE "HAULMAX_UAT_SHIPMENT"."Shipment" WITH PARAMETERS ('AUTO_OPTIMIZE_COMPRESSION' = 'OFF');
In addition it would be a good idea to run a consistency check on the database, to see if there are any other affected tables.