RE: AUTO_INCREMENT columns randomly restart counting from 1
Hi Bruce 01.02.02 15:41:49, Bruce Stewart [EMAIL PROTECTED] wrote: Hi Andreas, I think AUTO_INCREMENT is on a per-connection basis. So if you're doing this across different database connections, it will reset to 0. [snip] do a 'select * from tablename where test=1' and you will see that the 2 new values will be visible to both of the clients. As I understand it, this is correct, and by design. MSSQL functions in the [...] Correct, and it should! Don't know if I've helped you here, or only confused you more, but this functionality is by design, and I believe makes sense. the mail you replied to was my explanation to Richard Bolen's statement: # I think AUTO_INCREMENT is on a per-connection basis. So if you're doing # this across different database connections, it will reset to 0. My problem is somewhat different: the counter randomly resets, and that is no behaviour by design. regards Andreas mysql, sql, select - 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
RE: AUTO_INCREMENT columns randomly restart counting from 1
30.01.02 17:48:06, Richard Bolen [EMAIL PROTECTED] wrote: I think AUTO_INCREMENT is on a per-connection basis. So if you're doing this across different database connections, it will reset to 0. no, that's not the case. just try it. crate a table with column id as auto_increment int and a column test as int; do an 'insert into tablename values( NULL, 1)' from two different clients, do a 'select * from tablename where test=1' and you will see that the 2 new values will be visible to both of the clients. I don't yet fully understand what the manual tries to tell, but the increment works fine also after disconnecting/reconnecting a client or restarting the database. the only special thing to recognize is with function last_insert_id(). if one client creates a new autoincrement value, the other client can't see this, the result for 'select last_insert_id()' is 0! only the client performing the increment is able to determine the new value valid for _this_ client. imagine that both clients did their increments on the same table, last_insert_id() gives different answers to both of them. on the other hand: 'select max(id)' gives the absolute maximum to both of the clients. Andreas MySQL - 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
RE: AUTO_INCREMENT columns randomly restart counting from 1
Hi Andreas, I think AUTO_INCREMENT is on a per-connection basis. So if you're doing this across different database connections, it will reset to 0. [snip] do a 'select * from tablename where test=1' and you will see that the 2 new values will be visible to both of the clients. As I understand it, this is correct, and by design. MSSQL functions in the same way (@@IDENTITY). Selecting last_insert_id() returns the last insert id for YOUR client connection, and no-one elses. You should normally call last_insert_id() to find out what value MySQL assigned to the autoinc column in your newly inserted record, so that you can use that last_insert_id() value when inserting other related data into other tables, where the relationship is based on that ID field. the only special thing to recognize is with function last_insert_id(). if one client creates a new autoincrement value, the other client can't see this, the result for 'select last_insert_id()' is 0! only the client performing the increment is able to determine the new value valid for _this_ client. imagine that both clients did their increments on the same table, last_insert_id() gives different answers to both of them. on the other hand: 'select max(id)' gives the absolute maximum to both of the clients. Correct, and it should! Don't know if I've helped you here, or only confused you more, but this functionality is by design, and I believe makes sense. Cheers, Bruce - 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
RE: AUTO_INCREMENT columns randomly restart counting from 1
BMJI, But isn't 'last_insert_id()' only meant to be called after inserting a record?? If you've never inserted a record (using the current connection), it seems like calling 'last_insert_id()' would be a meaningless call. It could return the highest value of the auto-increment column, but it could just as easily return -1 or 0 or Avagadro's number. I thought it was only meant to retrieve the value that is automatically inserted into an auto-increment column. The column isn't supposed to be set by the INSERT statement, the backend engine will figure out the next available number and put it in. But the client can't know in advance what that value is going to be. So if the client needs to know what was 'decided' by the engine, it can call 'last_insert_id()' AFTER inserting the record. Or am I all wet here... Mike Fochtman -Original Message- From: Bruce Stewart [mailto:[EMAIL PROTECTED]] Sent: Friday, February 01, 2002 9:42 AM To: 'Andreas Schoelver'; [EMAIL PROTECTED] Subject: RE: AUTO_INCREMENT columns randomly restart counting from 1 Hi Andreas, I think AUTO_INCREMENT is on a per-connection basis. So if you're doing this across different database connections, it will reset to 0. [snip] do a 'select * from tablename where test=1' and you will see that the 2 new values will be visible to both of the clients. As I understand it, this is correct, and by design. MSSQL functions in the same way (@@IDENTITY). Selecting last_insert_id() returns the last insert id for YOUR client connection, and no-one elses. You should normally call last_insert_id() to find out what value MySQL assigned to the autoinc column in your newly inserted record, so that you can use that last_insert_id() value when inserting other related data into other tables, where the relationship is based on that ID field. the only special thing to recognize is with function last_insert_id(). if one client creates a new autoincrement value, the other client can't see this, the result for 'select last_insert_id()' is 0! only the client performing the increment is able to determine the new value valid for _this_ client. imagine that both clients did their increments on the same table, last_insert_id() gives different answers to both of them. on the other hand: 'select max(id)' gives the absolute maximum to both of the clients. Correct, and it should! Don't know if I've helped you here, or only confused you more, but this functionality is by design, and I believe makes sense. Cheers, Bruce - 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 - 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
AUTO_INCREMENT columns randomly restart counting from 1
Hi there We have some table used as sequences. They only have 2 columns (ID, PID), with the AUTO_INCREMENT flag set for one of them (ID). By default the tables are empty! The increment process is done by inserting a new record with a random value for the non-auto_increment column. After that the new sequence value is fetched by selecting the random value. In the next step the one and only new row will we deleted using a 'delete from seq_xx WHERE PID = random_value' statement, as described in the manual. randomly the incrementation of any of the tables fails and the counter resets to 1, which breaks the whole application. this appears long before any overflow might happen. has anyone else ever noticed this? any idea what might be wrong ? the version of the server is 3.23.42 . best regards Andreas Schoelver - 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
Re: AUTO_INCREMENT columns randomly restart counting from 1
At 17:15 +0100 1/30/02, Andreas Schoelver wrote: Hi there We have some table used as sequences. They only have 2 columns (ID, PID), with the AUTO_INCREMENT flag set for one of them (ID). Is the ID column one of the integer types? By default the tables are empty! The increment process is done by inserting a new record with a random value for the non-auto_increment column. After that the new sequence value is fetched by selecting the random value. In the next step the one and only new row will we deleted using a 'delete from seq_xx WHERE PID = random_value' statement, as described in the manual. randomly the incrementation of any of the tables fails and the counter resets to 1, which breaks the whole application. this appears long before any overflow might happen. has anyone else ever noticed this? any idea what might be wrong ? What is the table type? (ISAM, MyISAM ... ?) the version of the server is 3.23.42 . best regards Andreas Schoelver - 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
RE: AUTO_INCREMENT columns randomly restart counting from 1
I think AUTO_INCREMENT is on a per-connection basis. So if you're doing this across different database connections, it will reset to 0. Rich Hi there We have some table used as sequences. They only have 2 columns (ID, PID), with the AUTO_INCREMENT flag set for one of them (ID). By default the tables are empty! The increment process is done by inserting a new record with a random value for the non-auto_increment column. After that the new sequence value is fetched by selecting the random value. In the next step the one and only new row will we deleted using a 'delete from seq_xx WHERE PID = random_value' statement, as described in the manual. randomly the incrementation of any of the tables fails and the counter resets to 1, which breaks the whole application. this appears long before any overflow might happen. has anyone else ever noticed this? any idea what might be wrong ? the version of the server is 3.23.42 . best regards Andreas Schoelver - 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 - 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
