HANA identity column issues after system restart

[Former Member]

Hello all,

Few questions regarding HANA identity column:

We are using HANA SPS09.

1) So lets say we use HANA identity column functionality for primary keys in our DB, smth like this:

create column table "tmp" (
   "Id" INTEGER primary key generated by default as identity ,
   "name" NVARCHAR(6)
)

If table has 0 records, after system restart sequence returs error:
"invalid sequence exception: invalid sequence: RESET BY query returns null value ".

Problem that reset by query is generated without coalesce(), so when table has no records reset by query returns null.
select "sequence_name".NEXTVAL from dummy  doesnt work.

Does anyone know solution for this? Because so far I've found few - either drop/recreate table or insert fake record to table with PK equal to 0.
Both solutions are ugly especialy when you have lots of tables.

2) Second error related with primary key is that when you rename table using  RENAME TABLE A TO B; and do system restart, insert to renamed table fails with error: invalid sequence: RESET BY query is invalid.  This is because table in reset by query is not renamed.

Are these know issues/bugs and are planned to be solved in near future? Or maybe some solutions/workarounds are known?

[former_member299936]

Hi Juste / Lars ,

We have faced the second issue (rename table with primary key and identity)for a production customer system. And the issue was resolved by performing a permanent workaround (own) as I mentioned below.

Basically the table didn't have a primary key and identity earlier. Later the identity along with PK was added to one column by creating a temp table with identity and PK  (e.g table1_tmp) and then the records were inserted into the tmp table excluding the PK column. Later the original table(table1)  was dropped and table table1_tmp was renamed to table1. After few weeks when the HANA DB was restarted , we faced this error : invalid sequence: RESET BY query is invalid.

After investigation and thorough testing , came to a conclusion that the issue is due to missing / in-appropriate sequence.please note that the system sequence _sys_sequence_xxxxx was originally created for table table1_tmp when you created the identity. And once you renamed the table , it seems only table name mapping is being changed for table to table1 which is in memory. If you open a sequence definition you will still see it as : select max(identity_column)+1 from table1_tmp.

So , after restart it tries to find the table table1_tmp in db which it could not find and throws this error.looks to be a bug but could not find any solution or fix anywhere.

So there are 2 workaround for this issue :

A. Temporary workaround till next db restart but quick.

B. Permanent workaround - will take some time as shown below.

A. Temporary workaround :

1. select max(identity_column) from table1. lets say if the value is 1000.

2. Create a table1_tmp table again but identity column with clause (START WITH 1000 increment by 1)

3. insert into table1_tmp select col1,col2....(excluding identity_column) from table1 where identity_column=1000.

(in this way the last value in temp table will be same as table1) and again the sequence mapping will be restored.

4. you can now drop the table1_tmp table.

5. start the normal insert in table1 and it works , the next value inserted should be 1001

B. Permanent workaround :

1. note down the max identity row value from table1 (e.g. its 1000)

2. take a binary export of table on OS.

3. backup the create.sql file somewhere else.

4. now edit the create.sql file and put the clause : (START WITH 1001 increment by 1)

5. import the table back into db. ( you may choose the backup the table in db by rename table (e.g. rename table table1 to table1_back) if its highly crucial until the import is successful and you are confirm that the issue is resolved , then you can drop the backup table.)

6. start the normal insert into the table1 table excluding the identity column.

in my case , the table disk size was 125GB and table is on node with 2 TB RAM in a scale out so the export took ~20 mins and import ~25 mins.

In this way you have created the own system generated "sequence" for table table1 (select max(identity_column)+1 from table1) and there is no mapping involved hence db restart will not raise this error.

tested this on 85.0 and 85.02 and implemented on 85.0 prod.

Regards,

Ganesh Sawale.

[lbreddemann]

Thanks for sharing this experience!

Although it's possible to use a workaround like this, I'd say, that it is still a design bug that the table rename leads to restart-errors (due to the non-adapted reset-queries).

Just tried it on Rev. 101 and this hasn't been fixed yet.

[former_member299936]

Hi Lars,

Yes , no doubt its a bug !!

But till the time we get a fix , one can use these workaround to avoid business downtime due to this issue like we did. Please let us know if anyone hear about the fix in near future.

Regards,

Ganesh Sawale

[rasmus_seidelinstokholm]

Just tried it on Rev 110 (SP11) the bug is still there, but only for old objects.

So if you drop and recreate the table the seqence is created with the correct reset by statement.

Now is just the problem off getting your data back into with the old sequence numbers.

Maybe it can be fixed altering the identity column i don't know.

Message was edited by: Rasmus Seidelin Stokholm

[Former Member]

Hello SAP,

we have also same problems with identity column. Is there still no SAP note or bug fix for these problems ???

Best regards,

Y. Hu

[lbreddemann]

On my rev 94 system, the reset by clause is generated as:

select MAP( mod((max( "<column>") - (1) ),1),  0, max("<column>") +1,  max("<column>") - mod((max( "<column>") - (1)), 1 ) +1 )

from "<schema>"."<table>"

Upon restart with an empty table there is no error, but as soon as new data should be entered, a

SAP DBTech JDBC: [313]: invalid sequence: RESET BY query returns null value

is returned.

So, looks like the problem is still around.

I would recommend to open a support incident for this.

- Lars

[Former Member]

Hi Lars,

Thank you very much for your response.

Unfortunately this is bad news. The porblem is still not solved.

I think the RESET BY logic is no easy. The easiest logic is to use the function IFNULL, e.g. select ifnull(max("Id"),0) + 1 from "<schema>"."<table>".

But this will be very complicated if the parameter START WITH, INCREMENT BY … must be also considered.

I hope there is soon a bugfix first for the problem with NULL. It is very grateful if you can contact with sap support. Thanks again.

Best regards,

Yujun

[lbreddemann]

For more control about the sequence behavior, you may consider to simply use a custom sequence.

IDENTITY is syntactic-sugar; a feature to make a simple use case really very simple to implement.

Quite obviously there are currently bugs present in the implementation of this feature, but it doesn't mean that what the feature does needs to be extended or changed.

In many cases it is time well spent to actually consider what kind of IDs the data model should have and how those should be implemented. Personally I'd consider IDENTITY only for relatively unimportant situations - like building a test data set or something similar.

- Lars

[Former Member]

Hi Lars,

yes, IDENTIY column is a very simple easy feature. We use it only as auto-increment ID column without extra sequence and trigger. Normally for auto-increment ID column we need only default parameter START WITH 1, INCREMENT BY 1 etc.  Currently we try to avoid the NULL problem.

Thanks again for your explanation.

Best regards

Yujun