RE: Auto-Increment - how can I avoid repeating IDs?
Rus wrote:
That's strange. I thought that auto_increment field not suppose
to use same
value. May be it's problem connected with transaction. Check doc's on
COMMIT. Try to set AUTOCOMMIT=1
It's real. Here is a demonstration. First, the table definition...
Auto_increment fields will re-use values for ISAM tables (i.e.
tables created in versions before 3.23 or being created with
TYPE=ISAM). This behaviour was changes with the introduction
of MyISAM tables (introduced in v. 3.23).
See also http://www.bitbybit.dk/mysqlfaq/faq.html#ch6_0_0
/ Carsten
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| vik_ID | int(10) unsigned | | PRI | 0 | auto_increment |
| vikname | varchar(255) | | | ||
+-+--+--+-+-++
And here is a demonstration of the behavior. It is pretty weird:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 3.22.32
Type 'help' for help.
mysql select * from Vik;
++-+
| vik_ID | vikname |
++-+
| 1 | Da man |
| 2 | Da sow |
++-+
2 rows in set (0.01 sec)
mysql delete from Vik where vik_ID=2;
Query OK, 1 row affected (0.00 sec)
mysql select * from Vik;
++-+
| vik_ID | vikname |
++-+
| 1 | Da man |
++-+
1 row in set (0.00 sec)
mysql insert into Vik (vikname) VALUES ('George');
Query OK, 1 row affected (0.00 sec)
mysql select * from Vik;
++-+
| vik_ID | vikname |
++-+
| 1 | Da man |
| 2 | George |
++-+
2 rows in set (0.00 sec)
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
-
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 - how can I avoid repeating IDs?
That's nice :)
- Original Message -
From: Harald Fuchs [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, January 31, 2001 8:34 PM
Subject: Re: Auto-Increment - how can I avoid repeating IDs?
In article 009f01c08b0f$c85bbec0$be66bcd4@ruscomp,
[EMAIL PROTECTED] ("Rus") writes:
That's strange. I thought that auto_increment field not suppose to use
same
value.
Even worse, it's documented :-)
If you delete the row containing the maximum value for an
`AUTO_INCREMENT' column, the value will be reused with an ISAM
table but not with a `MyISAM' table. If you delete all rows in the
table with `DELETE FROM table_name' (without a `WHERE') in
`AUTOCOMMIT' mode, the sequence starts over for both table types.
-
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 - how can I avoid repeating IDs?
I am using MySQL ver. 3.22.32 When a table has an auto-incrementing ID field and the last inserted record had an ID of, say, 100, then I delete that record, the database engine will re-use the 100 value. How can I get the engine to never repeat an ID value to avoid corrupted data correlations? - 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 - how can I avoid repeating IDs?
Upgrade to 3.23. -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger -Original Message- From: Viken Nokhoudian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 30, 2001 14:58 I am using MySQL ver. 3.22.32 When a table has an auto-incrementing ID field and the last inserted record had an ID of, say, 100, then I delete that record, the database engine will re-use the 100 value. How can I get the engine to never repeat an ID value to avoid corrupted data correlations? - 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 - how can I avoid repeating IDs?
Use your own id's. I personally hate auto increment. I do all my work in php so I make my id's like this. srand(time()); $id = md5(uniqid(rand())); This will make a 32 character id. -Original Message- From: Viken Nokhoudian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 30, 2001 3:58 PM To: [EMAIL PROTECTED] Subject: Auto-Increment - how can I avoid repeating IDs? I am using MySQL ver. 3.22.32 When a table has an auto-incrementing ID field and the last inserted record had an ID of, say, 100, then I delete that record, the database engine will re-use the 100 value. How can I get the engine to never repeat an ID value to avoid corrupted data correlations? - 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
RE: Auto-Increment - how can I avoid repeating IDs?
Write your own Serial Assigned Key (SAK) routine. Put a table in your database : SAK SAKID auto_increment tinyint table varChar(80) not null field varChar(80) not null nextValue bigint lockedBy varCar(10) Now, each time you do an insert follow these steps: 1: Select the record from SAK for the table.field you want to use. 2: See if there is a value in lockedBy. If so, someone else is already using it, wait or bail. If not, then update the record with your userID or if in PHP, your session number. basically anything that's unique to this client and or session. 3: Select to see if the lockedBy field is what you put in it. Just to make sure you got the lock and not someone else. 4: select to get the value in nextValue 5: Update to increment the value by 1 and to remove the lock. Now, it doesn't really take 5 steps to do this, you can combine 3 4. I broke them out logically so you could see what is going on. Also, if you are the *only* person using the database, all the locking stuff is superfluous. Hope this helps, Cal http://www.calevans.com -Original Message- From: Viken Nokhoudian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 30, 2001 2:58 PM To: [EMAIL PROTECTED] Subject: Auto-Increment - how can I avoid repeating IDs? I am using MySQL ver. 3.22.32 When a table has an auto-incrementing ID field and the last inserted record had an ID of, say, 100, then I delete that record, the database engine will re-use the 100 value. How can I get the engine to never repeat an ID value to avoid corrupted data correlations? - 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
Re: Auto-Increment - how can I avoid repeating IDs?
Easiest way, upgrade to 3.23:) Other way: create a table just to make auto_increment IDs and take the number from it each time to put in your original table. Viken Nokhoudian wrote: I am using MySQL ver. 3.22.32 When a table has an auto-incrementing ID field and the last inserted record had an ID of, say, 100, then I delete that record, the database engine will re-use the 100 value. How can I get the engine to never repeat an ID value to avoid corrupted data correlations? - 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 -- Angela Curtis Innovative Business Consultants http://www.ibc2001.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
Re: Auto-Increment - how can I avoid repeating IDs?
So sprach Viken Nokhoudian am Tue, Jan 30, 2001 at 12:57:50PM -0800: When a table has an auto-incrementing ID field and the last inserted record had an ID of, say, 100, then I delete that record, the database engine will re-use the 100 value. How can I get the engine to never repeat an ID value to avoid corrupted data correlations? Huh?? This does not happen here with mysql .31! mysql describe TEST; +---++--+-+-++-+ | Field | Type | Null | Key | Default | Extra | Privileges || +---++--+-+-++-+ | ID| tinyint(4) | | PRI | NULL| auto_increment | |select,insert,update,references | | Value | tinyint(4) | | | 0 || |select,insert,update,references | +---++--+-+-++-+ mysql insert into TEST (Value) values (1); Query OK, 1 row affected (0.00 sec) mysql select * from TEST; ++---+ | ID | Value | ++---+ | 1 | 1 | ++---+ 1 row in set (0.00 sec) mysql delete from TEST where Value='1'; Query OK, 1 row affected (0.00 sec) mysql insert into TEST (Value) values (2); Query OK, 1 row affected (0.00 sec) mysql select * from TEST; ++---+ | ID | Value | ++---+ | 2 | 2 | ++---+ 1 row in set (0.00 sec) Alexander Skwar -- How to quote: http://learn.to/quote (german) http://quote.6x.to (english) Homepage: http://www.digitalprojects.com | http://www.iso-top.de iso-top.de - Die guenstige Art an Linux Distributionen zu kommen Uptime: 2 hours 38 minutes - 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 - how can I avoid repeating IDs?
Rus wrote:
That's strange. I thought that auto_increment field not suppose to use same
value. May be it's problem connected with transaction. Check doc's on
COMMIT. Try to set AUTOCOMMIT=1
It's real. Here is a demonstration. First, the table definition...
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| vik_ID | int(10) unsigned | | PRI | 0 | auto_increment |
| vikname | varchar(255) | | | ||
+-+--+--+-+-++
And here is a demonstration of the behavior. It is pretty weird:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 3.22.32
Type 'help' for help.
mysql select * from Vik;
++-+
| vik_ID | vikname |
++-+
| 1 | Da man |
| 2 | Da sow |
++-+
2 rows in set (0.01 sec)
mysql delete from Vik where vik_ID=2;
Query OK, 1 row affected (0.00 sec)
mysql select * from Vik;
++-+
| vik_ID | vikname |
++-+
| 1 | Da man |
++-+
1 row in set (0.00 sec)
mysql insert into Vik (vikname) VALUES ('George');
Query OK, 1 row affected (0.00 sec)
mysql select * from Vik;
++-+
| vik_ID | vikname |
++-+
| 1 | Da man |
| 2 | George |
++-+
2 rows in set (0.00 sec)
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
