I noticed that if there is "assertion failed"-error, then restore the database from backup copy and current log (for example, using -a database option) incorrectly restores the currval/nextval of sequence.
Probably, this is normal behavior because the checkpoint has not been confirmed.
Is it really?

asked 09 Aug '16, 10:10

Ilia63's gravatar image

Ilia63
776273350
accept rate: 16%


If your sequence definition is not declared with a NO CACHE clause there is a possibility this can happen. Fatal errors, assertions, power outages, unhandled systems exceptions could all cause the server to stop without a clean checkpoint.

If your 'gap' is less than the default 100 values (or whatever you have your CACHE setting set to) then the above could be your issue.

If your 'gap' is larger I would suspect an incomplete (-a) recovery.

permanent link

answered 09 Aug '16, 12:50

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

CACHE 100 sounds like a violation of The Watcom Rule, but what do I know?

I use DEFAULT AUTOINCREMENT and...

I also hate Java...

(09 Aug '16, 13:17) Breck Carter
Replies hidden

Thanks Nick.
Now I set the cache as CACHE 20 (INCREMENT BY 1).
Do I get rid of the my problem, if I set the default cache (CACHE 100)?
Or do you recommend 'NO CACHE'-parameter?
P.S. In my opinion, the use of 'NO CACHE'-parameter can degrade application performance.

Thanks Breck.
In my application numerous customers can request own ID at the same time. I am afraid to use 'DEFAULT AUTOINCREMENT', as I expect problems of locking the table (with 'DEFAULT AUTOINCREMENT') in this case.

(10 Aug '16, 02:24) Ilia63

Changing that to NO CACHE should prevent that entirely, but could have an affect upon performance.

Going to the default cache of 100 would increase the pential for an even bigger gap than your current setting.

In contrast assertion failures, fatal errors, unhandled expections, and system level shutdowns should be rare so the tradeoff is a function of rarity of the current risk verses the impact/consequences if/when it occurs.

(10 Aug '16, 09:03) Nick Elson S...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×4

question asked: 09 Aug '16, 10:10

question was seen: 201 times

last updated: 10 Aug '16, 09:03