Hi, There were older versions with some problems, for example some alter tables causing much internal work incl. re-creating the table internally, which caused forgetting the newest value for serial. Therefore please use the newest version available. Or the reason could have been a loading with some option causing the fast-loading (with pre-build records/pages). Then, in older versions the highest inserted serial-value was forgotten. Or the case may be that after dropping the default serial and re-creating it with a higher value the cache did not catch this newer, higher value and used those values cached before the default-drop. Then some inserts (eating up the cache) and some deletes by hand will help.
With versions > 7.4.3.26 (or the newest 7.5-version) no such problem should occur any more. Which version do you use? Elke SAP Labs berlin > -----Urspr�ngliche Nachricht----- > Von: Erik Rehrmann [mailto:[EMAIL PROTECTED] > Gesendet: Montag, 11. April 2005 16:41 > An: Zabach, Elke > Betreff: RE: Sync serial value after manually inserted records > > Hi Elke, > > you're right! It worked the way you described: > > INSERT (serial,...) values (1000,...) > INSERT (...) values (...) > CURRVAL=1001 > > But: All records in this table are initially inserted this way (manually), > highest ID = 862 with some gaps. > Why did the number generator produce a new serial value of (9) ??? > > I do not understand the system behind and that's why I'm doubtfully think > it can happen again! > > Is it necessary to ask the CURRVAL afer auto-inserting? > > -- > Erik Rehrmann > IOn AG > Vorstand, Forschung & Entwicklung > Board Member, R&D > http://www.ion.ag > http://ionic.ion.ag > GnuPG Fingerprint: FA40 4DA8 F27B B4F6 FBA2 3455 0452 0C54 D111 546A > > > > -----Original Message----- > > From: Zabach, Elke [mailto:[EMAIL PROTECTED] > > Sent: Monday, April 11, 2005 4:02 PM > > To: Erik Rehrmann; [email protected] > > Subject: AW: Sync serial value after manually inserted records > > > > Erik Rehrmann [mailto:[EMAIL PROTECTED] > > > Gesendet: Freitag, 8. April 2005 12:47 > > > An: [email protected] > > > Betreff: Sync serial value after manually inserted records > > > > > > Hi all, > > > > > > I've encountered a very serious problem using serials. > > > > > > A MaxDB is first filled via an ETL-process as a copy of a different > > DBMS, > > > i.e. all tables containing serial columns are filled not by > > the MaxDB > > > number generator, but by a manual insert. > > > > > > The source database is already used and, because of some normal > > deletions, > > > the serial ids are not subsequent. > > > > > > Using the new filled MaxDB our application gets lots of > > "Duplicate key > > > error", because the next generated serial id is already in > > use within > > the > > > table. > > > > > > So here are the questions: > > > > > > +) Is it possible to synchronize the max value of a serial with the > > > highest value of the serial id within the table? > > > > > > +) Is there any way, maybe as dba, to change the current > > serial value > > > within the cataloge? > > > > > > +) Is there any other way to use serials for tables wich values can > > also > > > be manually inserted? > > > > Assuming a table t1 with serial column s, having the current > > value of 1000, You can 'set' the serial value using this: > > > > Select max(s) from t1 --> finding the 1000 Insert t1 (s,<all > > other mandatory columns>) values (1001, ....) Delete t1 where s = 1001 > > > > Meaning: if a serial-column's value is given by hand and > > greater than the current serial value the user-given value > > will be used as new max value, meaning the next implicit > > serial value will be 1 greater then the user-given one. > > > > If you always have a mixture of user-given values (sometime > > being smaller than the max serial value), then you always > > cause the possibility of duplicate keys. > > Then using serial column is no good decision. > > For a first reset after some filling from somewhere the > > method given above will help. > > > > Elke > > SAP Labs Berlin > > > > > > > > Environment is > > > - Linux / WindowsXP SP2 > > > - MaxDB version: 7.5.00.18 build 018-121-079-776 > > > - JDBC-driver-version: 7.6.0 build 000-000-003-264 > > > > > > Different combinations of other (newer/older) versions are > > also tested > > > without success. > > > > > > Any help is greatly appreciate. > > > > > > -- > > > Erik Rehrmann > > > > > > -- > > > MaxDB Discussion Mailing List > > > For list archives: http://lists.mysql.com/maxdb To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
