0

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)

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Shubha Lakshmi
  • 127
  • 2
  • 2
  • 15

1 Answers1

1

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.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • It does not seem to be out of memory issue from the preliminary investigation of the traces. Moreover the table in question has hardly 1-2 rows. The issue is with loading the time/timestamp attributes. Loading them leads to the error "Attribute Load Failed" for that particular column, reason being invalid date/time values even when the values are valid or there is no data in them. – Shubha Lakshmi Jun 22 '17 at 05:59
  • 1
    Ok, then I propose to do some further investigation - from the available information, there is nothing to be concluded left. – Lars Br. Jun 22 '17 at 06:00
  • looks like compressing/decompressing datetime values for column store optimization is failing, as evident from traces. Is it a bug in Hana? – Shubha Lakshmi Jun 27 '17 at 11:20
  • Thanks..Will raise an incident with SAP.. Also, Not too sure about data corruption as this issue occurred multiple times without any data in the said table.. As It is a trial license of Hana Instance given to us (which is HANA1SPS12) which is also shared by multiple test users , so it could be a possibility that using proper latest production version DB instance allotted only to us may resolve this issue. – Shubha Lakshmi Jun 28 '17 at 04:36
  • Did a consistency check on catalog which turned up nothing. Did a consistency check on the said table with the following command : `CALL CHECK_TABLE_CONSISTENCY('CHECK','HAULMAX_UAT_SHIPMENT','"Shipment"')` Which showed up the above discussed columns with error code 5199 and error message 'Cannot Check Main Dictionary' – Shubha Lakshmi Jun 28 '17 at 05:02
  • 1
    The catalog consistency check shouldn't report any error for this issue - it checks the entries in the database catalog, while the current error likely is in the actual physical storage structure underpinnning the table. As the table consistency reported a problem with the column dictionaries, I would look into getting the last error free backup out and restoring this... – Lars Br. Jun 28 '17 at 05:07
  • 1
    While software bugs can lead to consistencies these are more often caused by other factors (bit flips, controller errors, faulty RAM elements, disks,...). Changing to a standalone instance or a newer HANA revision likely won't fixed the issue then. The trial instance you use is as "proper" as any other HANA instance in this regard. – Lars Br. Jun 28 '17 at 05:09