"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]
