Duplicate value on primary key in SAP HANA

[Former Member]

    Hi

We have this issue with a table WDIOM where idDispatchOrder is Primary Key

As you can see in the images above there is a duplicated value (20) on primary key field and also if we run a query based on a value of the primary key field (18) the result returned is of a different value(20).

Please, can anyone tell me how is this posible and if there is a solution for this?

[Former Member]

Thank you all for your responses

Lars, idDispatchOrder is the primary key.

As you all sugested we run a consistency check for the table. Here is the result

I should add that the server we use for this is one that we use for testing, is not a SAP Certified server.

[lbreddemann]

Well, that only means that you won't get support for this from a product support point of view.

However it might be that the system setup led to this issue, Finding the root cause for corruptions is typically the hardest part.

What you can do now:

• try and recover a good backup and see if the corruption is already present.
• try and copy the table over to a new table w/o a primary key. Once all data is in the new table, you would have to review the duplicate entries, delete the ones you don't want to keep and re-enable the primary key.

If you don't find a SAP HANA bug that would match to the error and revision when the corruption happened, then I am afraid it's unlikely that the root cause will be found.

Stuff like this is the reason for the recommendation to regularly have the consistency check run.

[Former Member]

This looks like a bug: Selecting 18 and getting 20 is definitely wrong. Regardless of the existence of a primary key. I would suggest you to run CHECK_TABLE_CONSISTENCY on this table (SAP Note 1977584). Feel free to provide the result here.

[anindya_bose]

What is the data type for field DispatchOrder ?  Can you try

where "IdDispatcherOrder" Like %20%     and see what is coming ?

Could be because of inconsistency between Delta and Main memory ? Can you merge delta and see if the SQL fetches same record?

Cheers

Anindya

[Former Member]

Hi Anindya, thank you for your prompt response

So, data type field is integer.

I tried "WHERE idDispatchOrder LIKE 20 " and the result was with both records .

And sorry, but I don't know how to merge Delta.

As more info...  our versión is SAP HANA SPS10

Thank you

Francisco

[lbreddemann]

This has nothing to do with the delta store.

Can you please check in the table editor that the ID is actually a primary key?

Alternatively you might run the following SQL statement to see the primary key columns of the table:

select * from constraints

where is_primary_key ='TRUE' and table_name='WDIOM'

order by schema_name, table_name, column_name,

  position;

[anindya_bose]

Hi Lars

Can it be like that record got deleted/updated but invalid flag was not updated for some reason in main store?

This is really an interesting problem, would be interested to know the root cause.

Another interesting observation is when he used "18"  in where clause he got DispatchOrder 20.  When he used Like 20, he still got DispatchOrder 20.

How that can be possible even if that field is not PK ?

[lbreddemann]

Before assuming that something went wrong on a very deep, well tested layer of the column store, let's look at the more likely scenario first: that the PK is actually not set up.

Given what you mentioned about the different id back than what you asked, a corruption of the table might be possible.

Please run a consistency check for the table and have a support incident opened.