Re: Accessing last_insert_id problem.
LAST_INSERT_ID is held for the database connection, not agaist the server So, as long as you do not do another insert using the same database connection, LAST_INSERT_ID will be fine. (for database connection, $dbh=DBI-connect. ) No table locking required. Regards M On Wed, 18 Dec 2002, Jeff Snoxell wrote: Date: Wed, 18 Dec 2002 12:21:14 + From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Accessing last_insert_id problem. Hi, I'm adding records to a db using the Perl DBI. Subsequent to adding a record I need to know the value of the auto-incrementing 'Ref' field so that I can place a copy of the relavent details into a log file. I could query for the LAST_INSERT_ID but what if another process has added another record in the interim? Is this a job for table locking? Many thanks, Jeff - 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 -- Matthew Smith Nominet UK - 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: Accessing last_insert_id problem.
Jeff Snoxell wrote: At 09:46 19/12/02 -0500, you wrote: Jeff Snoxell wrote: Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table Are you using InnoDB tables? You'll have to do something akin to ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) No, I'm using MyISAM I believe. Jeff If you want to set the ID back to zero, then I assume you are deleteing all of the records in the table. If so, why not simply drop the table and recreate it? Seems to work for me as the session below demonstrates. If you want to do something else, you better ask again so we can answer you real question. clip mysql create table test ( id int auto_increment, d int, primary key (id) ); Query OK, 0 rows affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql select * from test; ++--+ | id | d| ++--+ | 1 |2 | | 2 |2 | | 3 |2 | ++--+ 3 rows in set (0.00 sec) mysql drop table test; Query OK, 0 rows affected (0.00 sec) mysql create table test ( id int auto_increment, d int, primary key (id) ); Query OK, 0 rows affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.01 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql select * from test; ++--+ | id | d| ++--+ | 1 |2 | | 2 |2 | | 3 |2 | ++--+ 3 rows in set (0.00 sec) mysql = end clip = -- Will Will Merrell Virtual Assistant [EMAIL PROTECTED] Moreland Business Solutions - Your partner in business. http://www.morelandsolutions.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: Accessing last_insert_id problem.
I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset the auto-increment value... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even include anything about TRUNCATE TABLE my_table_name. Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table then: INSERT INTO my_table SET Name='Jeff' then: SELECT * FROM my_table and lo-and-behold, the 'Ref' primary key auto-update field is 37062, not 1 as I hoped. I am managing to reset it to '1' by using a windows mysql client program but it doesn't show me what SQL it's executing in order to obtain the desired result. Thanks, Jeff - 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: Accessing last_insert_id problem.
http://www.mysql.com/doc/en/SET_OPTION.html last option(s) maybe you can do somehting with that Gr At 10:54 19-12-02 +, Jeff Snoxell wrote: I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset the auto-increment value... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even include anything about TRUNCATE TABLE my_table_name. Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table then: INSERT INTO my_table SET Name='Jeff' then: SELECT * FROM my_table and lo-and-behold, the 'Ref' primary key auto-update field is 37062, not 1 as I hoped. I am managing to reset it to '1' by using a windows mysql client program but it doesn't show me what SQL it's executing in order to obtain the desired result. Thanks, Jeff - 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: Accessing last_insert_id problem.
At 09:46 19/12/02 -0500, you wrote: Jeff Snoxell wrote: Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table Are you using InnoDB tables? You'll have to do something akin to ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) No, I'm using MyISAM I believe. Jeff MySQL, query, SQL - 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: Accessing last_insert_id problem.
At 15:13 + 12/19/02, Jeff Snoxell wrote: At 09:46 19/12/02 -0500, you wrote: Jeff Snoxell wrote: Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table Are you using InnoDB tables? You'll have to do something akin to ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) No, I'm using MyISAM I believe. Can you check for sure. With MyISAM, TRUNCATE TABLE should always reset the counter, I believe. Here's an example: mysql CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) mysql SHOW CREATE TABLE t; +---+--+ | Table | Create Table | +---+--+ | t | CREATE TABLE `t` ( `i` int(11) NOT NULL auto_increment, PRIMARY KEY (`i`) ) TYPE=MyISAM | +---+--+ 1 row in set (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.01 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) mysql TRUNCATE TABLE t; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) With InnoDB, what I observe is that you have to issue an ALTER TABLE after truncating the table to force the counter back to 1: mysql CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; Query OK, 0 rows affected (0.02 sec) mysql SHOW CREATE TABLE t; +---+--+ | Table | Create Table | +---+--+ | t | CREATE TABLE `t` ( `i` int(11) NOT NULL auto_increment, PRIMARY KEY (`i`) ) TYPE=InnoDB | +---+--+ 1 row in set (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) mysql TRUNCATE TABLE t; Query OK, 2 rows affected (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 3 | +---+ 1 row in set (0.00 sec) mysql TRUNCATE TABLE t; Query OK, 1 row affected (0.00 sec) mysql ALTER TABLE t AUTO_INCREMENT = 1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) Jeff MySQL, query, SQL - 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
Accessing last_insert_id problem.
Hi, I'm adding records to a db using the Perl DBI. Subsequent to adding a record I need to know the value of the auto-incrementing 'Ref' field so that I can place a copy of the relavent details into a log file. I could query for the LAST_INSERT_ID but what if another process has added another record in the interim? Is this a job for table locking? Many thanks, Jeff - 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: Accessing last_insert_id problem.
On Wednesday 18 December 2002 14:21, Jeff Snoxell wrote: I'm adding records to a db using the Perl DBI. Subsequent to adding a record I need to know the value of the auto-incrementing 'Ref' field so that I can place a copy of the relavent details into a log file. I could query for the LAST_INSERT_ID but what if another process has added another record in the interim? Is this a job for table locking? You get value that was inserted from your client. read more about this function at: http://www.mysql.com/doc/en/Miscellaneous_functions.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 - 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: Accessing last_insert_id problem.
Hi, I've got that sussed now and am happily using the aquired ref to subsequently play with the record etc. One other related problem and I recon I'm sorted How do I reset the auto-increment value? I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset it... but it doesnt'. What SQL do I use to reset the val. Ta, Jeff - 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: Accessing last_insert_id problem.
At 15:31 18-12-02 +, Jeff Snoxell wrote: Hi, I've got that sussed now and am happily using the aquired ref to subsequently play with the record etc. One other related problem and I recon I'm sorted How do I reset the auto-increment value? I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name truncate should reset it... but it doesnt'. What SQL do I use to reset the val. Ta, Jeff - 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: Accessing last_insert_id problem.
Jeff Snoxell wrote: DELETE FROM my_table_name should reset it... but it doesnt'. I'm glad the DELETE FROM doesn't, or else my foreign keys would all get screwed up :) TRUNCATE should do what you want (as someone else pointed out). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. ... SQL http://www.fibrespeed.net/~mbabcock - 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: Accessing last_insert_id problem.
At 12:21 + 12/18/02, Jeff Snoxell wrote: Hi, I'm adding records to a db using the Perl DBI. Subsequent to adding a record I need to know the value of the auto-incrementing 'Ref' field so that I can place a copy of the relavent details into a log file. I could query for the LAST_INSERT_ID but what if another process has added another record in the interim? Doesn't matter. Is this a job for table locking? No, it's probably more of a job for reading the section on LAST_INSERT_ID() in the manual again, particularly the part about cannot be changed by another client. :-) http://www.mysql.com/doc/en/Miscellaneous_functions.html Many thanks, Jeff - 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: Accessing last_insert_id problem.
At 15:31 + 12/18/02, Jeff Snoxell wrote: Hi, I've got that sussed now and am happily using the aquired ref to subsequently play with the record etc. One other related problem and I recon I'm sorted How do I reset the auto-increment value? I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset it... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even include anything about TRUNCATE TABLE my_table_name. Ta, Jeff - 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
