mysql auto increment id paranioa!!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
jazz090
Forum Contributor
Posts: 176
Joined: Sun Apr 12, 2009 3:29 pm
Location: England

mysql auto increment id paranioa!!

Post by jazz090 »

ive been searching for a way to get the auto increment id generated by mysql back into php, so far ive been generating them with uniqid() which seems to work. but what if i need to insert and get its id, i know that there are some functions for this and this is where tha paranoia comes in. im afaraid that incase 2 inserts are executed on 2 different machines simultaneously it will jeapordise the value which can be obtained using php pr mysql. i have read somewhere that this can be avided completly in mysql. can someone help me?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql auto increment id paranioa!!

Post by califdon »

According to the manual, you can recover the last inserted id on a connection basis, which I think answers your need. http://dev.mysql.com/doc/refman/5.0/en/ ... ue-id.html
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: mysql auto increment id paranioa!!

Post by mikemike »

Isn't this what mysql_insert_id is for?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql auto increment id paranioa!!

Post by califdon »

mikemike wrote:Isn't this what mysql_insert_id is for?
Yes, there are several overlapping functions, and I honestly don't know how they differ, without reading the manual. The issue here, I believe, was whether there is a risk that if different users were adding new rows in extremely close timing, could one of these functions possibly return the wrong "latest insert" id. Apparently the answer is no, since at least the one I looked at says that it is by connection, which I believe means there is no risk of someone connecting to the same table but from a different script affecting the value you get from this function.
User avatar
jazz090
Forum Contributor
Posts: 176
Joined: Sun Apr 12, 2009 3:29 pm
Location: England

Re: mysql auto increment id paranioa!!

Post by jazz090 »

ok i just ran a test with mysql_insert_id() in FF and Chrome (to ensure different sessions);

Code: Select all

<?php
require_once("connect.php");
query("insert into test values (null, 'Dummy')");
sleep(20);
echo mysql_insert_id();
?>
first i ran that ^^^ in FF and then quickly afterwards before the 20 seconds was up, i removed the sleep and ran it in chrome,
the chrome came back with 2 and FF was 1 which means although the FF query was executed before Chromes query but its insert id was retrived after chromes query was executed, it suggests that it returns the values as it should regradless of any other clients submitting queries at the sametime. unless im missing some big info on sleep() such as if it behaved like headers(executed once the script was over) and it caused the mysql_insert_id() to be executed before Chromes query was executed, i would deem this valid.

if we can have an expert verify this VALID, it would be awesome any one?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql auto increment id paranioa!!

Post by califdon »

Well, I'm certainly not an expert (at least not in this field!), but I take the MySQL manual's word for it. It says quite clearly that "The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients." http://dev.mysql.com/doc/refman/4.1/en/ ... rt-id.html
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: mysql auto increment id paranioa!!

Post by mikemike »

I concur, manual is the best expert there is.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: mysql auto increment id paranioa!!

Post by requinix »

jazz090 wrote:if we can have an expert verify this VALID, it would be awesome
Considering how (a) the function exists - would be useless if the ID was shared across the entire database server, and (b) it's used in countless sites around the web I think we can safely assume that it works exactly as the PHP and MySQL documentation says it does.
User avatar
jazz090
Forum Contributor
Posts: 176
Joined: Sun Apr 12, 2009 3:29 pm
Location: England

Re: mysql auto increment id paranioa!!

Post by jazz090 »

tasairis wrote:Considering how (a) the function exists - would be useless if the ID was shared across the entire database server, and (b) it's used in countless sites around the web I think we can safely assume that it works exactly as the PHP and MySQL documentation says it does.
i dont think PHP made a reference to how mysql_insert_id() worked and neither was anything mentioned on the mysql reference. the only reference i read and confirmed that worked on a per-connection basis was MYSQL's last_insert_id(). i dont recall reading anything about PHPs function which is why i did the test to begin with. but if you think about it theres nothing on the PHP site. all it says is that it returns that last primary key generated by mysql. but what if you executed a insert and in a nanosecond on a different server another insert was executed, this would leave you thinking what if it returned the primary key from the insert that was on another connection becuase technicaly it was the last primary key created on mysql.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql auto increment id paranioa!!

Post by califdon »

So use the MySQL last_insert_id() function. :? I'm reasonably sure that the PHP function relies on the same thing (how else could it obtain the information??), but if you are really worried about it, just execute a query, then the manual assures you that other connections are not an issue.
http://fernvale.wordpress.com/2007/05/0 ... -in-mysql/
User avatar
jazz090
Forum Contributor
Posts: 176
Joined: Sun Apr 12, 2009 3:29 pm
Location: England

Re: mysql auto increment id paranioa!!

Post by jazz090 »

yh thats what i did:)
Post Reply