I solved the problem, it was application problem. but, Joseph, your method doesn't work becos application is not continually logged in to the DB. if the process is newly login, last_insert_id is 0 then the update will update the auto increment field to 0 becos select last_insert_id() returns 0.
insert is fine, MySQL is fast enough, I can clear useless rows daily. of cos, I am expecting the new release with Sequence support~ Borus > ÅÇ´f¼Ù(Borus.Kung) a écrit : > > > > Hi all, > > > > I have a serious problem. > > I have a production system just launch using MySQL. > > auto increment field is used in the system by ASP, Visual Basic and C. > > System is using AIX 4.3.3, MySQL 3.23.49 > > gcc 2.9 source code compiled MySQL > > > > There are some C compiled process using auto increment field contained > > tables > > in which I used them as sequence, this way: > > Create table: > > create table my_num (id int not null auto_increment, primary key (id)); > > Insert table: > > insert into my_num values (null); > > Select table: > > select last_insert_id(); for ASP and VB > > or > > call mysql_insert_id() for C program > > > > then I got a sequence number. > > > > but unfortunately, after some time of running, I found the sequence > > increase by itself...... > > and the table gradually became very large. > > > > I though there must be a bug in my application. > > then I stopped all the applications including C processes and ASP. > > but the sequences keep on increasing. > > > > If the database is shutdown and start again, the tables back to normal > > for some time... > > it is unaffordable to repeatedly restart the system from time to time > > and it is sure that the sequence number will run out of stock soon. > > > > I still don't know how this error happened, but it just happened. > > Do anyone encountered similar problem before? > > now, some experiement is being performed on another machine > > to repeat the problem. > > > > please help > > > > Borus > > > Hi, > > If the sole purpose of 'my_num' table is to generate a unique sequence > number, you should not use inserts. One solution is to create a table > with a single record and update it: > > create table my_num (id int not null); > insert into my_num (id) values (1); > > and each time you want a new sequence number: > > update my_num set id=LAST_INSERT_ID(id+1); > select LAST_INSERT_ID(); > > Of course, you will have to change all your VB, C,... apps :( > > Regards > -- > Joseph Bueno > NetClub/Trader.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
