List:General Discussion« Previous MessageNext Message »
From:龔惠樑(Borus.Kung) Date:June 14 2002 3:32am
Subject:Re: MYSQL Auto-Increment Field increment by itself
View as plain text  
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
Thread
FW: MYSQL Auto-Increment FieldKaan Oglakci12 Jun
  • MYSQL Auto-Increment Field increment by itself龔惠樑(Borus.Kung)12 Jun
  • Re: MYSQL Auto-Increment Field increment by itselfJoseph Bueno12 Jun
  • Re: FW: MYSQL Auto-Increment FieldVictoria Reznichenko12 Jun
  • Re: MYSQL Auto-Increment Field increment by itself龔惠樑(Borus.Kung)14 Jun