Can a table become corrupt?

[patrickbachmann]

Hi experts,

I'm having this bizarre problem with a very simple join on BSEG.  When I'm joining a very small subset of data to another of our tables I'm getting NULL values even though the join is clearly correct and I can see values in both tables I'm joining.  If I take BSEG and make a copy of the table definition and copy all of the data into my new copied table and switch my join to point to the COPIED version of BSEG everything works great.  So same EXACT data in each table yet the join doesn't work in the original.  Since clearly the data is the same in each table I was wondering if there's some sort of corruption happening in the original.  I tried to unload from memory and I've tried merge deltas with no success.  Then I started wondering what, if anything, could be corrupt.  Is it possible for their compression values to become corrupt for example?  Are there other secret indices somewhere that I'm not seeing that could be corrupt?

Example of code that returns nulls from MYZTABLE:

select B.BUKRS, B.BELNR, C.FUNC_CURR_TYPE from "MYSCHEMA"."BSEG" B LEFT JOIN

"MYSCHEMA"."MYZTABLE" C on B.BUKRS = C.BUKRS and B.MANDT = C.MANDT

where B.BELNR = '1234567890'

Example of code that works and returns matches from MYZTABLE:

(same code except points to a COPY of original bseg, called BSEGCOPY.  Same exact data in this table, and MYZTABLE is same exact table untouched.)

select B.BUKRS, B.BELNR, C.FUNC_CURR_TYPE from "MYSCHEMA"."BSEGCOPY" B LEFT JOIN

"MYSCHEMA"."MYZTABLE" C on B.BUKRS = C.BUKRS and B.MANDT = C.MANDT

where B.BELNR = '1234567890'

Any theories on what types of things could be wrong or possibly corrupt in my BSEG table?  Since data is the same in BSEG vs BSEGCOPY could there be some sort of decompression problem with BSEG that is not occuring in my BSEGCOPY for example?   Also note: in my JOIN ON condition these fields are exactly the same data types in both tables being joined. 

Thanks,

-Patrick

[lbreddemann · Accepted solution]

And just to briefly answer your question:

Sure enough tables can become corrupt in SAP HANA too - just like any other system we're not immune against real world physics, programming bug or plan bad luck

With SPS6 (I believe) some more administration tools are on the menu to make checking for inconsistencies and corruptions easier.

- Lars

[patrickbachmann · Accepted solution]

Hi Ravi, actually I did indeed create SAP message and they are checking to see if there is any index corruption as we have also had issues with index server process crashing.  They mentioned uniquechecker script/check_table_consistency.  I'm now going to read what I can on index server process.

Lars, I copied the table by first going into the original definition, clicking Export SQL button and did a find and replace on BSEG replacing with BSEGCOPY and then executed.  That created the definition.  To populate the empty BSEGCOPY I did INSERT into BSEGCOPY select * from BSEG where GJAHR = '2013' and BUKRS = 'xxx'.  When I tried to copy entire year I was getting out of memory errors (which never happened in past) and so I actually did about 10 inserts by company code ranges.  I believe I tried your example exactly when I was testing but I will try again today and let you know.

Thanks guys.

[former_member184768 · Accepted solution]

Hi Patrick,

Couple of thoughts:

1) Do you also have history table associated with BSEG. Did you check if that is consistent. Earlier I had a similar issue where SAP looked into HANA and mentioned that the history index was corrupt and asked me to restore the history table from the backup or something. Since we did not have the backup (it was on a test system), we closed the OSS message.

2) If you have some time time and memory to play around, can you please try

CREATE TABLE xxx like yyy with data;

-- this is very likely to fail with memory issue if your first large insert didn't go through.

Alternatively you can try

CREATE TABLE xxx like yyy with NO data;

and then try the insert into statements.

I am not completely sure if this will help simulate the corrupt issue.

Since SAP is also looking into index corruption, I am sure they will come up with something liking it back to earlier indexserver crash and you may have to ignore this as one stray incident.

Regards,

Ravi

[patrickbachmann · Accepted solution]

Guys I haven't forgotten to close this thread, I'm waiting for results of consistency check on my table however I've had a couple of disconnect issues and now this most recent time I have an out of memory error after running for 72 hours.  Working with SAP directly and will update this thread eventually with findings.

[patrickbachmann · Accepted solution]

FYI.  Still working with SAP to see if this table is corrupt, no luck with consistency check as it eventually errors out.  During all of this we've re-replicated BSEG into a new schema and the join is working correctly against the new version.  It seems obvious that there's corruption and so closing this thread.  Thanks for your feedback guys as always.

-Patrick

[patrickbachmann · Accepted solution]

Just an update for anybody interested.  SAP had a join specialist look at the problem and they determined that BSEG was definitely corrupted.  Also because the UniqueChecker would eventually fail with out of memory error they said as an alternative we can run check against just unique constraints only and NOT 'null constraints' according to note 1666976.  

-Patrick

[henrique_pinto · Accepted solution]

Hi Patrick.

but didn't SAP gave a way to recover the corrupted table (apart of copying, deleting the original, recovering)?

Best,

Henrique.

[patrickbachmann · Accepted solution]

Here's what they said below;  also we already had reloaded into a new schema because it was taking so long to troubleshoot the issue anyway.

Normally to fix such a table it will depends on the type of column which was corrupted, e.g. it could be retrieved if the column value was calculated from other columns or in case of a concatenated attribute it can be dropped and recreated from scratch or if all values are the same. However in your case it looks like the best option to reload the table.

[henrique_pinto · Accepted solution]

Thanks, Patrick.

[stefan_koehler · Accepted solution]

Hi Patrick,

isn't the table BSEG a core FI table in the SAP Business Suite? Did you lose data? How can you reload a corrupted table (without data loss), if it is the data base itself? Did SAP find the root cause for that corruption?

Maybe i am missing something here

Thanks.

Regards

Stefan

[lbreddemann · Accepted solution]

Hi Stefan,

the options to recover from corrupted data structures in SAP HANA are basically the same as in all other DBMS platforms.

Data that is not stored somewhere else too (e.g. in an index, in the source column of a computed column, in a backup, ...) can only be recreated (e.g. by application logic) but not recovered.

To get existing copies back into place, it depends on where the data is located.

If for example the corruption occurs in an internal compound column (that is sometimes created automatically) then one option is of course to re-create this compound column from the original part columns.

Exactly this seemed to have been the case here, as the compound column ($BUKRS$MANDT$) data in the copied table had been re-created from the original part columns MANDT and BUKRS.

With the changed select statement I proposed earlier I _suspect_ that for the join the compound column was not used anymore - but the filter had been applied to MANDT on both tables first and then the join was executed via BUKRS only. That's why it worked in this case.

(once again just a lucky shot into the blue ).

- Lars

[stefan_koehler · Accepted solution]

Hi Lars,

thanks for clarification.

> once again just a lucky shot into the blue

You are honored like a god here (in SAP HANA section) - so i would call it "supernatural" instead of "lucky"

> Exactly this seemed to have been the case here, as the compound column ($BUKRS$MANDT$) data in the copied table had been re-created from the original part columns MANDT and BUKRS.

Ah ok i get it now. It is pretty interesting that it seems like the compound column is stored physically and not "created on-the-fly" like a virtual column in the RDBMS that i am aware of.

Thanks.

Regards

Stefan

[lbreddemann]

Hi Patrick,

how did you copy the table data?

What if you change the original join statement to something like this:

select B.BUKRS, B.BELNR, C.FUNC_CURR_TYPE 
from 
          "MYSCHEMA"."BSEG" B LEFT JOIN
          "MYSCHEMA"."MYZTABLE" C 
     on B.BUKRS = C.BUKRS
where 
     B.BELNR = '1234567890
and B.MANDT = '100' 
and C.MANDT = '100'

(replace 100 by your actual client number)

Does the join work then?

In any case, could you please post the execution plans for both joins (original table and copy table)?

- Lars

[patrickbachmann]

Lars when I hardcode the MANDT in the where clause, just like your example my join is indeed working. 

[former_member184768]

, I think this behavior needs explanation from . I am sure he knows something that we don't know.

Why did the join NOT work with just equi join condition and why did it work when the MANDT column is passed with a constant value.

Regards,

Ravi

[patrickbachmann]

Execution plan against BSEG

Execution plan against BSEGCOPY

[former_member184768]

Hi Patrick,

I think the visual plan will help more in this context. It will provide more details.

Regards,

Ravi

[lbreddemann]

Hi Patrick,

unfortunately, there is no easy way to check why the join seems to be executed incorrectly here.

If I were to analyse the issue on the system I'd probably try to use some of the traces, but as these tend to become rather large, difficult to understand and sometimes don't fulfill reasonable expectations (from the non-developer point of view) it doesn't make sense to try to do it via this forum thread.

The only thing I can think of to "fix" the issue would be to try and drop the concatenated column used for the join on the original table.

Once the concat attribute is dropped, it will be rebuild upon the next join execution.

However, doing so would eliminate any root cause analysis, so I'd recommend to have this thing analysed by our support beforehand.

- Lars

[patrickbachmann]

Thanks Lars.  I'm currently running 'call CHECK_TABLE_CONSISTENCY' as first step from SAP and it's very slow going.  Will update this post afterwards.

-Patrick

[former_member184768]

Hi Patrick,

My suggestion would be to open SAP message, which I am very sure you might have already done.

Regards,

Ravi