Mrs Reznichenko, About the ID problem, I´d create a single table just for ID´s (and ´d keep it as thin as possible). This would avoid the repeating id numbers.
Sincerely Leandro da Rocar. <[EMAIL PROTECTED]> ----- Original Message ----- From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 12, 2004 9:35 AM Subject: Re: auto_increment id > "Stefan Schuster" <[EMAIL PROTECTED]> wrote: > >> "Stefan Schuster" <[EMAIL PROTECTED]> wrote: > >>> Hi, > >>> > >>> I have a question about auto_increment: > >>> > >>> I have 2 tables, on of them holds my "online transactions", the other > >>> one the "offline transactions". Every transaction is created in the > >>> first table (call it t1) and then moved to t2. The id is generated using > >>> auto_increment. > >>> > >>> My problem is that the id's should be unique across both > >>> tables. If I dont restart the server this works fine, but if I > >>> restart it, mySQL assigns the lowest number for the new row in > >>> t1, but this id exits already in t2 which results in an error > >>> if I try to move from t1 to t2. > >>> > >>> I hope I explained the problem in a appropriate way. > >>> > >>> How can I tell mySQL to hold the auto_increment counter for > >>> Table t1 persistent ? > >> > >> Do you use InnoDB tables? If so, it's a known behavior. If you restart > >> MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. > >> > > > > I need transaction support, as I understand it InnoDB > > is the only tabletype that supports this. Is there any way > > to change the behaviour, maybe a table type that > > has the desired (persistent incrementcounter) behaviour > > AND supports transactions ? > > > > BDB tables are also support transaction, but they are reuse deleted auto_increment values not only if server restarts. > > For InnoDB tables you can initialize the auto-increment counter by yourself. > > You should insert into t1 dummy row with max id value from t2 and then remove this row. > Something like: > > INSERT IGNORE INTO db_name.t1(id) SELECT greatest(max(t1.id), max(t2.id)) as id from db_name.t1, db_name.t2; > > So, with SELECT statement you find greatest value and insert this value into t1 table. If this value is already exists, INSERT statement is ignored. > > Then you should remove this dummy row: > DELETE db_name.t1 FROM db_name.t1, db_name.t2 WHERE db_name.t1.id=db_name.t2.id; > > Put INSERT and DELETE commands to the file and specify file name with --init-file option of mysqld. MySQL server will read this file at startup: > http://www.mysql.com/doc/en/Server_options.html > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Victoria Reznichenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > <___/ www.mysql.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
