ÅÇ´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
