Page 1 of 1
mysql_insert_id()
Posted: Fri May 12, 2006 2:06 pm
by s.dot
Does this grab the last insert id that was generated by the script per run?
Lets say two people ran the script at the exact same time, which ID would it grab?
Posted: Fri May 12, 2006 2:26 pm
by Burrito
it grabs the id from the script that inserted it... there's no way for it to cross users.
I have wondered how this works though...if anyone knows I'd love to find out.
in the old days of my cold fusion coding I used a function that selected max(id) where ipaddress = remote_addr.
this of course sucked because I had to have an ipaddress field on all of my tables.
Posted: Fri May 12, 2006 2:44 pm
by Christopher
I believe it gives the last insert id for the current connection.
Posted: Fri May 12, 2006 2:55 pm
by timvw
It's explained quite clear in
http://www.php.net/mysql_insert_id. (If i get it right, it's possible that two users use the same database link if the link was made via mysql_pconnect and thus they could see the same last insert id).
Posted: Fri May 12, 2006 3:43 pm
by Christopher
I don't think it is possible for two requests to share the same connection, even with pconnect. I don't see that mentioned in the docs and it has never happened in my experience.
The function takes a link parameter (or assumes/creates one) so after reading the docs it is definitely the last insert id on that connection which will be unique to that request.
Posted: Fri May 12, 2006 3:54 pm
by timvw
arborint wrote:I don't think it is possible for two requests to share the same connection, even with pconnect. I don't see that mentioned in the docs and it has never happened in my experience.
I just noticed that is documented
http://be2.php.net/manual/en/features.p ... ctions.php
Warning
There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does.
Posted: Fri May 12, 2006 4:06 pm
by timvw
I simply eddited my.ini (my.cnf on *nix) so that it allows only 1 connection:
max_connections=1
And then i wrote to simple php scripts:
first browse to this one to insert a row...
Code: Select all
<?php
mysql_pconnect('localhost', 'user', 'password');
mysql_select_db('test');
mysql_query("INSERT INTO persons (name) VALUES ('john')");
echo "last insert id:" . mysql_insert_id();
?>
next browse to this one and notice that the last_insert_id still exists (since it's the same 'link/connection'...)
Code: Select all
<?php
mysql_pconnect('localhost', 'user', 'password');
mysql_select_db('test');
echo "last insert id:" . mysql_insert_id();
?>
Posted: Fri May 12, 2006 4:38 pm
by Burrito
very interesting....
note to self: don't use pconnect
Posted: Fri May 12, 2006 5:26 pm
by Christopher
I think there is some confusion here, and I think my responses have not been very clear as well.
The behavior of last_insert_id should not matter whether you use pconnect or connect. It will report the last id inserted in that table whichever is used. So running Tim's script with connect will produce the exact same results -- which are the correct results -- which are to give the last id inserted into the table.
The most important thing is to answer this question:
scottayy wrote:Lets say two people ran the script at the exact same time, which ID would it grab?
The answer is that it would depends on which order MySQL processed those two inserts. There is no "exact same time" for MySQL. It will make the inserts atomic and it will make the calls to last_insert_id atomic as well. But each script will get a valid id at the time the call was made. That does not mean that the value returned for the id is the current state of the data table at some later time. So, for example, you would not want to increment the value to use as the next id.
Posted: Fri May 12, 2006 6:04 pm
by s.dot
So I could safely replace this:
Code: Select all
mysql_query("INSERT INTO `names` (`name`) VALUES('john')") or die(mysql_error());
$result = mysql_query("SELECT `id` FROM `table` WHERE `userid` = '$currentuser' ORDER BY `id` DESC LIMIT 1");
$id = mysql_result($result,0);
with this:
Code: Select all
mysql_query("INSERT INTO `names` (`name`) VALUES('john')") or die(mysql_error());
$id = mysql_insert_id();
on a script that will be used (regular connect) by several people at roughly the same time?
Posted: Fri May 12, 2006 6:16 pm
by Christopher
scottayy wrote:on a script that will be used (regular connect) by several people at roughly the same time?
Yes, and you can then to UPDATEs on that record using that id. But remember that if you do other queries then the value may change, and as Tim showed if you have not done an INSERT then it probably gives the last value for that table.
Posted: Fri May 12, 2006 11:56 pm
by dibyendrah
timvw wrote:I simply eddited my.ini (my.cnf on *nix) so that it allows only 1 connection:
max_connections=1
And then i wrote to simple php scripts:
first browse to this one to insert a row...
Code: Select all
<?php
mysql_pconnect('localhost', 'user', 'password');
mysql_select_db('test');
mysql_query("INSERT INTO persons (name) VALUES ('john')");
echo "last insert id:" . mysql_insert_id();
?>
next browse to this one and notice that the last_insert_id still exists (since it's the same 'link/connection'...)
Code: Select all
<?php
mysql_pconnect('localhost', 'user', 'password');
mysql_select_db('test');
echo "last insert id:" . mysql_insert_id();
?>
I think mysql connection doesn't matter but the column in the table must have auto_increment value for primary key instead for mysql_insert_id to work.
Posted: Sat May 13, 2006 4:03 am
by timvw
dibyendrah wrote:
I think mysql connection doesn't matter but the column in the table must have auto_increment value for primary key instead for mysql_insert_id to work.
Actually, it's really does matter (which was the point of my post). If you increase the max_allowed connections to something more than 1 and use mysql_connect instead of mysql_pconnect it's pretty sure you get two different 'links/connections'. And in that situation the result for mysql_insert_id in the script that didn't insert anything will be 0.
http://dev.mysql.com/doc/refman/5.0/en/ ... ue-id.html
For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).
Posted: Sun May 14, 2006 3:05 am
by dibyendrah
Thanks timvw for the information. I'll give a try ! I have been using the mysql_insert_id() so long and never found a problem. actually I have a web application in a central server and database on same machine and many other user connects to that machine from different other machines and add data . In that I used mysql_insert_id() function to know what users has added in that day . In that case I haven't found any problem .
And regarding connection, I have been using mysql_connect insetad of mysql_pconnect() function .
Dibyendra
Posted: Sun May 14, 2006 4:18 am
by timvw
I've been experimenting to find an example where one script does the following: insert, wait for a long time (while script2 is being run), get last insert id and notice that the insert_id is actually one generated in script2.
Apparently php assigns the 'link/connection' to the first script, and does not release it untill the script has terminated. Which makes it impossible for script2 to connect and insert.. And thus the race-condition does not exist.
(Still don't know what happens when the mysql server goes down and up while a script is running. Is the 'link' reestablished or is a new one created? Anyway, this situation seems quite unlikely..)