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
of cos, I am expecting the new release with Sequence support~
> ÅÇ´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
> 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 :(
> Joseph Bueno