Re: [PHP] Re: Getting last record ID created from DB
On Wed, 2007-03-21 at 10:37 -0400, Mark wrote: > Richard Lynch wrote: > > > On Tue, March 20, 2007 8:00 pm, Mark wrote: > > > > I'd agree with you, except now you've pulled the rug out and inserted > > a 3rd party connection pool. > > That's not "pulling the rug out," that is a legitimate possibility. > > > > > Well, duh, if you're dumb enough to think you can just slap it in and > > everything will work by magic, you deserve what you get. > > That is my point. > > > > > One would hope the connection pooling docs have at least SOME warning > > that they BREAK certain crucial functionality. > > > My point in this discussion is that there are ways that work better and more > widely across multiple platform than depending on specific features of > specific systems. There's always a tradeoff. As the master of my project I get to choose whether I want runtime speed, development simplicity, platform compatibility, etc, etc. All these things are never compatible at the exact same time. > One can write completely generic SQL that works correctly *regardless* of > connection pooling, transactional support, etc. The problem is that by not > thinking about the problem in the general case, you limit the applicability > of the project for no good reason. One *can* write completely generic SQL that works correctly... as you said... but then one can also write completely non generic SQL that runs faster and serves up more pages for your audience on a tighter budget. Developers quite often have a good reason for their choices-- whether you agree with it or not. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Richard Lynch wrote: > On Mon, March 19, 2007 10:58 pm, markw@mohawksoft.com wrote: >>> markw@mohawksoft.com wrote: > markw@mohawksoft.com wrote: >>> On Sat, 2007-03-17 at 12:19 -0400, Mark wrote: >>> Check the documentation - currval returns the last one *for that >>> session* - it does not return the last global change. >>> >>> http://www.postgresql.org/docs/current/static/functions-sequence.html >>> >>> It is perfectly safe to use this. >> >> In theory that may be true, but can the application developer make any >> assumption about the underlying architecture? Might you be familiar >> with >> connection pooling? Where multiple threads or processes share a >> database >> connection or "session?" > > If the pool breaks the documentation about current session, the pool > is broken. > > Fix it. > > :-) > > I don't think the "pool" will give you somebody else's ID, for the > record. It's smart enough to not do that, I *believe*, mostly on > faith. I've seen some pretty disturbing behavior in some Oracle connection pooling software. The problem is that you don't know it is the connection pool causing the problem until you chase it down. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Richard Lynch wrote: > On Tue, March 20, 2007 8:00 pm, Mark wrote: > > I'd agree with you, except now you've pulled the rug out and inserted > a 3rd party connection pool. That's not "pulling the rug out," that is a legitimate possibility. > > Well, duh, if you're dumb enough to think you can just slap it in and > everything will work by magic, you deserve what you get. That is my point. > > One would hope the connection pooling docs have at least SOME warning > that they BREAK certain crucial functionality. My point in this discussion is that there are ways that work better and more widely across multiple platform than depending on specific features of specific systems. One can write completely generic SQL that works correctly *regardless* of connection pooling, transactional support, etc. The problem is that by not thinking about the problem in the general case, you limit the applicability of the project for no good reason. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Tue, March 20, 2007 8:00 pm, Mark wrote: I'd agree with you, except now you've pulled the rug out and inserted a 3rd party connection pool. Well, duh, if you're dumb enough to think you can just slap it in and everything will work by magic, you deserve what you get. One would hope the connection pooling docs have at least SOME warning that they BREAK certain crucial functionality. I'm certainly not getting my knickers in a twist about a connection pool I'll *never* user any more than I'm going to write my PHP to not be subject to Perl attacks because somebody might take it and call it from Perl CGI or something inane. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Mon, March 19, 2007 10:58 pm, markw@mohawksoft.com wrote: >> markw@mohawksoft.com wrote: markw@mohawksoft.com wrote: >> On Sat, 2007-03-17 at 12:19 -0400, Mark wrote: >> Check the documentation - currval returns the last one *for that >> session* - it does not return the last global change. >> >> http://www.postgresql.org/docs/current/static/functions-sequence.html >> >> It is perfectly safe to use this. > > In theory that may be true, but can the application developer make any > assumption about the underlying architecture? Might you be familiar > with > connection pooling? Where multiple threads or processes share a > database > connection or "session?" If the pool breaks the documentation about current session, the pool is broken. Fix it. :-) I don't think the "pool" will give you somebody else's ID, for the record. It's smart enough to not do that, I *believe*, mostly on faith. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Chris wrote:
> markw@mohawksoft.com wrote:
>>> markw@mohawksoft.com wrote:
> http://www.postgresql.org/docs/current/static/functions-sequence.html
>
> It is perfectly safe to use this.
In theory that may be true, but can the application developer make any
assumption about the underlying architecture? Might you be familiar
with connection pooling? Where multiple threads or processes share a
database connection or "session?"
>>> If the developers of the database say it's safe, then I'm happy to take
>>> their word on it. If you can prove otherwise, please do so and post a
>>> bug report to them.
>>
>> This isn't abut the database, per se' but connection pooling between the
>> Apache/PHP process and the database. I can't tell you how many times I've
>> seen that burn people.
>>
>>
>>> In this case, tons of people are using connection
>>> pooling with postgres. If a problem had been discovered, then they would
>>> have complained and it would have been fixed.
>>
>> I periodically participate on the PG hackers list, and if such a bug were
>> reported, it would be bounced to the connection pool interface guys or
>> ignored, because, it is essentially out of the control of the PostgreSQL
>> developers.
>>
>>> Do I need to evaluate every section of code that a 3rd party provides to
>>> make sure the code does what the docs say it should? I'd never get
>>> anything done. At some point I just need to know how to use it and trust
>>> it works as it should. If it's a super critical application (eg
>>> financial information) where integrity is 100% required, then I'd test
>>> it. If I found a problem then I'd complain *shrug*.
>>
>> You are confusing "PostgreSQL" with the connection pooling interface. I
>> have seen this issue a few times with PostgreSQL and Oracle.
>>
>>> As Richard mentioned in another reply, the developers (whether it's
>>> mysql or postgres or "other") have taken a lot of pain to make sure
>>> these things work properly.
>>
>> Again, it isn't about "PostgreSQL," because yes it does work if you go
>> directly to PG, but if you use an interface that uses connection pooling
>> to reduce resource usage, you are going to see problems.
>>
>>> Why would I try and do exactly the same thing myself and most likely get
>>> it completely wrong?
>>
>> Because doing a a good web site, that really works, takes thinking about
>> it, it takes understanding it, you can't be lazy and "trust" that someone
>> else has done something, and then forget all the possible layers between
>> you and it. That is *the* recipe for failure.
>
> If I have to develop a website that handles a huge amount of traffic, of
> course I'm going to have to understand it all (well try to :P). 99.9% of
> sites don't and thus don't have to worry about this sort of thing. Until
> you get to that stage, is there a point in worrying about this sort of
> stuff?
There in lies the first fallacy, you don't need to worry abut it. You do
need to worry about it, because the problem still exists, it is just that
it will be an "odd bug" that you can't reproduce.
>
> Maybe I need to get into developing more high traffic websites :P
Not really.
>
> Going from a single webserver to multiple webservers and/or connection
> pooling is a big step, and you'd obviously have to test the crap out of
> your software/hardware before making the change "live".. and then deal
> with the issues that you discover.
One should not need to test to find the potential problems, one should test
to ensure that the potential problems are handled correctly.
>
>>> The rest of your rant I agree with in theory, but making something
>>> "flexible" is extremely difficult unless you understand what you're
>>> trying to accomplish from the beginning - and know each system well
>>> enough to know how each one works and the differences between them. Not
>>> even "LIMIT" is standard across databases let alone anything else like
>>> data types ('int(11)' compared to 'int', 'datetime' compared to
>>> 'timestamp' etc etc etc) or even date formats that they store.
>>>
>>> Taking all of that into account from the very beginning is pretty tough.
>>
>> If you going to do something, do it right, or else what's the point?
>> Sometimes it is harder, but that's why being a professional is different.
>
> So open source projects all need to be run & developed by professionals?
> Your original rant was about open source projects being "lazy" and
> limiting their thinking & support for different options. Doing a
> professional project is completely different to anyone starting an open
> source project.
I don't see how.
>
> Most projects start out as 2-3 people developing it and then deciding to
> make it open source. When that happens, it gradually builds up and more
> people come on board. I still say it's pretty hard to take everything
> like this into account when you're starting out.
>
> Anyway this is getting way OT, I guess we'll agree to disag
Re: [PHP] Re: Getting last record ID created from DB
markw@mohawksoft.com wrote:
markw@mohawksoft.com wrote:
http://www.postgresql.org/docs/current/static/functions-sequence.html
It is perfectly safe to use this.
In theory that may be true, but can the application developer make any
assumption about the underlying architecture? Might you be familiar with
connection pooling? Where multiple threads or processes share a database
connection or "session?"
If the developers of the database say it's safe, then I'm happy to take
their word on it. If you can prove otherwise, please do so and post a
bug report to them.
This isn't abut the database, per se' but connection pooling between the
Apache/PHP process and the database. I can't tell you how many times I've
seen that burn people.
In this case, tons of people are using connection
pooling with postgres. If a problem had been discovered, then they would
have complained and it would have been fixed.
I periodically participate on the PG hackers list, and if such a bug were
reported, it would be bounced to the connection pool interface guys or
ignored, because, it is essentially out of the control of the PostgreSQL
developers.
Do I need to evaluate every section of code that a 3rd party provides to
make sure the code does what the docs say it should? I'd never get
anything done. At some point I just need to know how to use it and trust
it works as it should. If it's a super critical application (eg
financial information) where integrity is 100% required, then I'd test
it. If I found a problem then I'd complain *shrug*.
You are confusing "PostgreSQL" with the connection pooling interface. I
have seen this issue a few times with PostgreSQL and Oracle.
As Richard mentioned in another reply, the developers (whether it's
mysql or postgres or "other") have taken a lot of pain to make sure
these things work properly.
Again, it isn't about "PostgreSQL," because yes it does work if you go
directly to PG, but if you use an interface that uses connection pooling
to reduce resource usage, you are going to see problems.
Why would I try and do exactly the same thing myself and most likely get
it completely wrong?
Because doing a a good web site, that really works, takes thinking about
it, it takes understanding it, you can't be lazy and "trust" that someone
else has done something, and then forget all the possible layers between
you and it. That is *the* recipe for failure.
If I have to develop a website that handles a huge amount of traffic, of
course I'm going to have to understand it all (well try to :P). 99.9% of
sites don't and thus don't have to worry about this sort of thing. Until
you get to that stage, is there a point in worrying about this sort of
stuff?
Maybe I need to get into developing more high traffic websites :P
Going from a single webserver to multiple webservers and/or connection
pooling is a big step, and you'd obviously have to test the crap out of
your software/hardware before making the change "live".. and then deal
with the issues that you discover.
The rest of your rant I agree with in theory, but making something
"flexible" is extremely difficult unless you understand what you're
trying to accomplish from the beginning - and know each system well
enough to know how each one works and the differences between them. Not
even "LIMIT" is standard across databases let alone anything else like
data types ('int(11)' compared to 'int', 'datetime' compared to
'timestamp' etc etc etc) or even date formats that they store.
Taking all of that into account from the very beginning is pretty tough.
If you going to do something, do it right, or else what's the point?
Sometimes it is harder, but that's why being a professional is different.
So open source projects all need to be run & developed by professionals?
Your original rant was about open source projects being "lazy" and
limiting their thinking & support for different options. Doing a
professional project is completely different to anyone starting an open
source project.
Most projects start out as 2-3 people developing it and then deciding to
make it open source. When that happens, it gradually builds up and more
people come on board. I still say it's pretty hard to take everything
like this into account when you're starting out.
Anyway this is getting way OT, I guess we'll agree to disagree ;)
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Martin Marques wrote: On Mon, 19 Mar 2007, Richard Lynch wrote: The MySQL developers spent a zillion hours making the LAST_INSERT_ID() function be tied to YOUR database connection. You get *your* LAST_INSERT_ID(), not some random one from some other database connection. That's why http://php.net/mysql_insert_id takes the connection as an argument. Okay, so it's an optional arg, and PHP uses the last-used connection by default, but that's another issue entirely... Best thing would be to use DB or MDB2 and let the object get the last id with it's internal functions. IMHO. Which would most likely call the last_insert_id function in mysql ;) -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
> markw@mohawksoft.com wrote:
>>>
>>> http://www.postgresql.org/docs/current/static/functions-sequence.html
>>>
>>> It is perfectly safe to use this.
>>
>> In theory that may be true, but can the application developer make any
>> assumption about the underlying architecture? Might you be familiar with
>> connection pooling? Where multiple threads or processes share a database
>> connection or "session?"
>
> If the developers of the database say it's safe, then I'm happy to take
> their word on it. If you can prove otherwise, please do so and post a
> bug report to them.
This isn't abut the database, per se' but connection pooling between the
Apache/PHP process and the database. I can't tell you how many times I've
seen that burn people.
> In this case, tons of people are using connection
> pooling with postgres. If a problem had been discovered, then they would
> have complained and it would have been fixed.
I periodically participate on the PG hackers list, and if such a bug were
reported, it would be bounced to the connection pool interface guys or
ignored, because, it is essentially out of the control of the PostgreSQL
developers.
>
> Do I need to evaluate every section of code that a 3rd party provides to
> make sure the code does what the docs say it should? I'd never get
> anything done. At some point I just need to know how to use it and trust
> it works as it should. If it's a super critical application (eg
> financial information) where integrity is 100% required, then I'd test
> it. If I found a problem then I'd complain *shrug*.
You are confusing "PostgreSQL" with the connection pooling interface. I
have seen this issue a few times with PostgreSQL and Oracle.
>
> As Richard mentioned in another reply, the developers (whether it's
> mysql or postgres or "other") have taken a lot of pain to make sure
> these things work properly.
Again, it isn't about "PostgreSQL," because yes it does work if you go
directly to PG, but if you use an interface that uses connection pooling
to reduce resource usage, you are going to see problems.
>
> Why would I try and do exactly the same thing myself and most likely get
> it completely wrong?
Because doing a a good web site, that really works, takes thinking about
it, it takes understanding it, you can't be lazy and "trust" that someone
else has done something, and then forget all the possible layers between
you and it. That is *the* recipe for failure.
>
>
> The rest of your rant I agree with in theory, but making something
> "flexible" is extremely difficult unless you understand what you're
> trying to accomplish from the beginning - and know each system well
> enough to know how each one works and the differences between them. Not
> even "LIMIT" is standard across databases let alone anything else like
> data types ('int(11)' compared to 'int', 'datetime' compared to
> 'timestamp' etc etc etc) or even date formats that they store.
>
> Taking all of that into account from the very beginning is pretty tough.
My father used to say the most sexist thing to me, it is kind of offensive
if you are a woman, but I know what it was intended to mean. "If it were
easy, they'd have women do it."
If you going to do something, do it right, or else what's the point?
Sometimes it is harder, but that's why being a professional is different.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Mon, 19 Mar 2007, Richard Lynch wrote: The MySQL developers spent a zillion hours making the LAST_INSERT_ID() function be tied to YOUR database connection. You get *your* LAST_INSERT_ID(), not some random one from some other database connection. That's why http://php.net/mysql_insert_id takes the connection as an argument. Okay, so it's an optional arg, and PHP uses the last-used connection by default, but that's another issue entirely... Best thing would be to use DB or MDB2 and let the object get the last id with it's internal functions. IMHO. -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Mon, 2007-03-19 at 23:58 -0400, markw@mohawksoft.com wrote: > > I'm not a web developer. I suck at PHP, I do know PHP, I've written a > number of PHP extensions, and have had my CVS account for about 8 years, > but I'm not a web developer. I'm an architect and it is problematic when > applications developers make assumptions about things that can change. Application developers must make the assumption that the documentation they are reading is correct. > > Open source is about more than simply getting the supposed job done, it is > about getting it done right too. It is about using best methods. And yes, > it is about ego and pride about doing it better. Open source is about whatever the developer wants it to be about provided their source is open. If that happens to coincide with your vision fine, if not then it is still open source according to their vision. > In every profession there are many things that work within a limited range > of use, but break down quickly when the situations change subtly. A > professional knows their "craft" well enough to anticipate these things. That depends on what aspect of the definition of profession you choose to focus upon. I think that most professionals are such by virtue of doing what they do by way of a profession, not necessarily by way of being an expert. > The authors of too many open source / GPL PHP projects take the easy way > out and ignore the larger architectural issues that would make their > projects more widely usable. How long did it take bugzilla to support > PostgreSQL? How any PHP projects on sourceforge are tied to MySQL instead > of being flexible? This depends on the focus of the developer/development team. Supporting multiple databases is a choice, that they can make at the expense of other features. There's a 1 to 1 time trade here. They can choose to support postgres as you say, or they can choose to add some other feature that their thousands of MySQL fans want. The very nature of open source allows Joe Coder to add Postgres support himself if it is that important to him. if he doesn't have the skill, then it's time for him to learn or suck it in and quit whining. > Developers, either proprietary or OS/GPL, seek to create software of > lasting value. If you choose to limit your software design, then you limit > its value. Value is such a subjective concept. What you suggest is that open source developers should have to support every database platform out there so that their user base can have lasting value in the event that they should choose to suddenly switch to some other db platform than the one the project creator originally cared about. It all comes down to one man's trash being another man's treasure. If a bugzilla user never wanted to switch to postgres then they've had lasting value regardless of the missing feature. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
markw@mohawksoft.com wrote:
markw@mohawksoft.com wrote:
markw@mohawksoft.com wrote:
On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
I too thought the same thing, but was corrected when I starting working
on a redesign for the billing system for the company that I am working
for. They use PostgreSQL and we do have a auto incremented unique id
field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
is a value that is controlled by the default value entry.
It then runs this:
nextval('customers_customer_id_seq'::regclass)
and uses this as the value of the id field for the newly inserted row.
Yea, I've been using PG for over 10 years now. Sequences are awesome,
but
there have an "auto number" column type as well. It simply creates a
sequence but it's there.
I guess maybe most databases support the notion of "auto number" in some
form. I haven't used MSSQL recently, but I think their solution is a
GUID.
Then instead of running a command like SELECT LAST_INSERT_ID(); you
have
to do some other stuff like this:
$SQL = "SELECT currval('{$table}_{$column}_seq');";
Again, this is absolutely wrong unless it is wrapped in a transaction.
You
will need a "begin" and a "commit" in an MVCC type system. If you don't
wrap it in a transaction you don't know who got the last "nextval" of
the
sequence.
Check the documentation - currval returns the last one *for that
session* - it does not return the last global change.
http://www.postgresql.org/docs/current/static/functions-sequence.html
It is perfectly safe to use this.
In theory that may be true, but can the application developer make any
assumption about the underlying architecture? Might you be familiar with
connection pooling? Where multiple threads or processes share a database
connection or "session?"
If the developers of the database say it's safe, then I'm happy to take
their word on it. If you can prove otherwise, please do so and post a
bug report to them. In this case, tons of people are using connection
pooling with postgres. If a problem had been discovered, then they would
have complained and it would have been fixed.
Do I need to evaluate every section of code that a 3rd party provides to
make sure the code does what the docs say it should? I'd never get
anything done. At some point I just need to know how to use it and trust
it works as it should. If it's a super critical application (eg
financial information) where integrity is 100% required, then I'd test
it. If I found a problem then I'd complain *shrug*.
As Richard mentioned in another reply, the developers (whether it's
mysql or postgres or "other") have taken a lot of pain to make sure
these things work properly.
Why would I try and do exactly the same thing myself and most likely get
it completely wrong?
The rest of your rant I agree with in theory, but making something
"flexible" is extremely difficult unless you understand what you're
trying to accomplish from the beginning - and know each system well
enough to know how each one works and the differences between them. Not
even "LIMIT" is standard across databases let alone anything else like
data types ('int(11)' compared to 'int', 'datetime' compared to
'timestamp' etc etc etc) or even date formats that they store.
Taking all of that into account from the very beginning is pretty tough.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
> markw@mohawksoft.com wrote:
>>> markw@mohawksoft.com wrote:
> On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
>>
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
>>> I too thought the same thing, but was corrected when I starting working
>>> on a redesign for the billing system for the company that I am working
>>> for. They use PostgreSQL and we do have a auto incremented unique id
>>> field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
>>> is a value that is controlled by the default value entry.
>>>
>>> It then runs this:
>>> nextval('customers_customer_id_seq'::regclass)
>>>
>>> and uses this as the value of the id field for the newly inserted row.
>>
>> Yea, I've been using PG for over 10 years now. Sequences are awesome,
>> but
>> there have an "auto number" column type as well. It simply creates a
>> sequence but it's there.
>>
>> I guess maybe most databases support the notion of "auto number" in some
>> form. I haven't used MSSQL recently, but I think their solution is a
>> GUID.
>>
>>> Then instead of running a command like SELECT LAST_INSERT_ID(); you
>>> have
>>> to do some other stuff like this:
>>>
>>> $SQL = "SELECT currval('{$table}_{$column}_seq');";
>>
>> Again, this is absolutely wrong unless it is wrapped in a transaction.
>> You
>> will need a "begin" and a "commit" in an MVCC type system. If you don't
>> wrap it in a transaction you don't know who got the last "nextval" of
>> the
>> sequence.
>
> Check the documentation - currval returns the last one *for that
> session* - it does not return the last global change.
>
> http://www.postgresql.org/docs/current/static/functions-sequence.html
>
> It is perfectly safe to use this.
In theory that may be true, but can the application developer make any
assumption about the underlying architecture? Might you be familiar with
connection pooling? Where multiple threads or processes share a database
connection or "session?"
I'm not a web developer. I suck at PHP, I do know PHP, I've written a
number of PHP extensions, and have had my CVS account for about 8 years,
but I'm not a web developer. I'm an architect and it is problematic when
applications developers make assumptions about things that can change.
Open source is about more than simply getting the supposed job done, it is
about getting it done right too. It is about using best methods. And yes,
it is about ego and pride about doing it better.
In every profession there are many things that work within a limited range
of use, but break down quickly when the situations change subtly. A
professional knows their "craft" well enough to anticipate these things.
The authors of too many open source / GPL PHP projects take the easy way
out and ignore the larger architectural issues that would make their
projects more widely usable. How long did it take bugzilla to support
PostgreSQL? How any PHP projects on sourceforge are tied to MySQL instead
of being flexible?
Developers, either proprietary or OS/GPL, seek to create software of
lasting value. If you choose to limit your software design, then you limit
its value.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sun, March 18, 2007 4:14 am, Jim Lucas wrote:
>> There in lies the biggest problem with LAMP, and that's MySQL. The
>> architecture of your methodology *only* works with MySQL, and not
>> more
>> capable databases like Oracle, DB2, or even PostgreSQL.
> I too thought the same thing, but was corrected when I starting
> working
> on a redesign for the billing system for the company that I am working
> for. They use PostgreSQL and we do have a auto incremented unique id
> field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
> is a value that is controlled by the default value entry.
>
> It then runs this:
> nextval('customers_customer_id_seq'::regclass)
>
> and uses this as the value of the id field for the newly inserted row.
>
> Then instead of running a command like SELECT LAST_INSERT_ID(); you
> have
> to do some other stuff like this:
>
> $SQL = "SELECT currval('{$table}_{$column}_seq');";
If you haven't turned OFF the built-in OID, you can also use
http://php.net/pg_last_oid to get the PostgreSQL internal OID (kind of
like a built-in auto_increment on each table, only not) and then use
that in a query to find your own ID by matching the built-in OID
field:
$query = "insert whatever into yeah ";
pg_exec($connection, $query) or die(pg_last_error($connection));
$oid = pg_last_oid($connection);
$query = "select whatever_id from yeah where oid = $oid";
$whatever_id = pg_exec($connection, $query) or
die(pg_last_error($connection));
$whatever_id = pg_result($whatever_id, 0, 0);
Same thing.
Different spelling.
They all do the same thing, pretty much, one way or another, no matter
which db you use.
And none of them match the other.
Well, okay, MS SQL and Sybase are probably the same, since MS bought
their code from Sybase originally.
And Oracle and SQL Server often end up supporting each other's syntax
oddities eventually, as one of them is trying to steal the other's
clients.
But God forbid they should just standardize on a single syntax/mechanism.
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some starving artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sat, March 17, 2007 1:08 pm, markw@mohawksoft.com wrote: > There in lies the biggest problem with LAMP, and that's MySQL. The > architecture of your methodology *only* works with MySQL, and not more > capable databases like Oracle, DB2, or even PostgreSQL. Therein lies the biggest problem of money-hungry commercial vendors refusing to use transportable standards so that they can maintain a vendor lock. Put the blame squarely where it belongs, please, on the SQL "standards" committee that squelches anything that might actually open up competition because it is weighted down with "legacy" requirements by biased committee members (read: employees of the vendors who would lose market share) and simply refuses to migrate to something cross-platform. It's not as if they didn't have the problem and viable solutions in front of them in 1992, 1999, and 2004. Notice a complete lack of a workable solution in the standards? Don't expect this problem to be solved anytime soon. Microsoft, Oracle, DB2 etc don't *want* you to be able to transfer your SQL code cleanly and easily to another vendor! -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sat, March 17, 2007 11:19 am, Mark wrote: > insert into the_table (..., email,..); > select user_id from the_table where email = '...'; > > Is probably the best way given the nature of the table and skill level > of > the developer asking the question. $query = "insert into the_table(...)"; mysql_query($query); $user_id = mysql_insert_id(); If you are writing a PHP/MySQL web application, and your skill level is not up to typing that last line of code above, stop writing a PHP/MySQL web application and go do something else. Something that requires no skill whatsoever. :-) Even if you just plain don't "get" what the concurrency problem is, you can STILL follow the basic instruction of "do this": $user_id = mysql_insert_id(); right after the INSERT is done, and you will get your auto_increment ID back. Anything else you try to do is just a time bomb ticking away. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Chris wrote:
markw@mohawksoft.com wrote:
markw@mohawksoft.com wrote:
On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
I too thought the same thing, but was corrected when I starting working
on a redesign for the billing system for the company that I am working
for. They use PostgreSQL and we do have a auto incremented unique id
field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
is a value that is controlled by the default value entry.
It then runs this:
nextval('customers_customer_id_seq'::regclass)
and uses this as the value of the id field for the newly inserted row.
Yea, I've been using PG for over 10 years now. Sequences are awesome, but
there have an "auto number" column type as well. It simply creates a
sequence but it's there.
I guess maybe most databases support the notion of "auto number" in some
form. I haven't used MSSQL recently, but I think their solution is a
GUID.
Then instead of running a command like SELECT LAST_INSERT_ID(); you have
to do some other stuff like this:
$SQL = "SELECT currval('{$table}_{$column}_seq');";
Again, this is absolutely wrong unless it is wrapped in a transaction.
You
will need a "begin" and a "commit" in an MVCC type system. If you don't
wrap it in a transaction you don't know who got the last "nextval" of the
sequence.
Check the documentation - currval returns the last one *for that
session* - it does not return the last global change.
http://www.postgresql.org/docs/current/static/functions-sequence.html
It is perfectly safe to use this.
Thanks!
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sat, March 17, 2007 10:16 am, Robert Cummings wrote: > No, it's a MySQL specific feature that is atomic with the insert and > so > you are guaranteed that the the returned ID is the exact automatic ID > associated with the most recent INSERT for the connection handle. > Unfortunately auto increment is MySQL specific and so it isn't > transferrable to other database engines. Every database package has SOME way to do this. It's practically criminal that db vendors won't standardize the mechanics. SQL-92, SQL-99, SQL-2002 (2004? whatever it was) they STILL can't sit down and come up with a single mechanism? One of the most basic fundamental database needs, never standardized. Probably never will be. Sigh. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sat, March 17, 2007 9:43 am, Myron Turner wrote: > An earlier post called attention to a concurrency problem. Wouldn't > getting the last inserted ID from LAST_INSERT_ID() > suffer from the same limitations as any of the other solutions which > do > a select to get the last ID? I assume if you are completely in > control > of the database, you could create a lock file using flock() and remove > the lock once the Id is retrieved using any of these methods. I guess > what I'm wondering is whether the simplest suggestion is the one that > would use the email address as a condition in the WHERE clause to > extract the ID? No, no, and no. The MySQL developers spent a zillion hours making the LAST_INSERT_ID() function be tied to YOUR database connection. You get *your* LAST_INSERT_ID(), not some random one from some other database connection. That's why http://php.net/mysql_insert_id takes the connection as an argument. Okay, so it's an optional arg, and PHP uses the last-used connection by default, but that's another issue entirely... Use the function provided, which was expressly designed to avoid the concurrency problem, which already has all the locking and whatnot built into it. Do not roll your own. You will only end up re-inventing a wheel, at best, and probably just screwing it up, most likely. These guys have spent a good chunk of their lives figuring this stuff out and becoming experts on it. Let them expertise. And to address a very specific suggestion: You never know when some goof is going to register twice with the same email, possibly hitting re-load, and so that's not a Good Idea. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
markw@mohawksoft.com wrote:
markw@mohawksoft.com wrote:
On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
I too thought the same thing, but was corrected when I starting working
on a redesign for the billing system for the company that I am working
for. They use PostgreSQL and we do have a auto incremented unique id
field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
is a value that is controlled by the default value entry.
It then runs this:
nextval('customers_customer_id_seq'::regclass)
and uses this as the value of the id field for the newly inserted row.
Yea, I've been using PG for over 10 years now. Sequences are awesome, but
there have an "auto number" column type as well. It simply creates a
sequence but it's there.
I guess maybe most databases support the notion of "auto number" in some
form. I haven't used MSSQL recently, but I think their solution is a GUID.
Then instead of running a command like SELECT LAST_INSERT_ID(); you have
to do some other stuff like this:
$SQL = "SELECT currval('{$table}_{$column}_seq');";
Again, this is absolutely wrong unless it is wrapped in a transaction. You
will need a "begin" and a "commit" in an MVCC type system. If you don't
wrap it in a transaction you don't know who got the last "nextval" of the
sequence.
Check the documentation - currval returns the last one *for that
session* - it does not return the last global change.
http://www.postgresql.org/docs/current/static/functions-sequence.html
It is perfectly safe to use this.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
- Original Message - From: I guess maybe most databases support the notion of "auto number" in some form. I haven't used MSSQL recently, but I think their solution is a GUID. You can set an auto-increment attribute on any integer data type. They also have GUIDs, which they recomend using if you plan on using replication, as any autonumeric sequence is local to each database and there will be repetitions over multiple servers, something GUIDs won't. Satyam -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
> markw@mohawksoft.com wrote:
>>> On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
>> There in lies the biggest problem with LAMP, and that's MySQL. The
>> architecture of your methodology *only* works with MySQL, and not more
>> capable databases like Oracle, DB2, or even PostgreSQL.
> I too thought the same thing, but was corrected when I starting working
> on a redesign for the billing system for the company that I am working
> for. They use PostgreSQL and we do have a auto incremented unique id
> field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
> is a value that is controlled by the default value entry.
>
> It then runs this:
> nextval('customers_customer_id_seq'::regclass)
>
> and uses this as the value of the id field for the newly inserted row.
Yea, I've been using PG for over 10 years now. Sequences are awesome, but
there have an "auto number" column type as well. It simply creates a
sequence but it's there.
I guess maybe most databases support the notion of "auto number" in some
form. I haven't used MSSQL recently, but I think their solution is a GUID.
>
> Then instead of running a command like SELECT LAST_INSERT_ID(); you have
> to do some other stuff like this:
>
> $SQL = "SELECT currval('{$table}_{$column}_seq');";
Again, this is absolutely wrong unless it is wrapped in a transaction. You
will need a "begin" and a "commit" in an MVCC type system. If you don't
wrap it in a transaction you don't know who got the last "nextval" of the
sequence. Also, in PostgreSQL, the pre-cache sequence values for
performance. So sequences are not always sequential.
(pseudo code)
begin;
insert ... values (...);
select currval('sequence_name');
commit;
or
$foo = select nextval('user_id_sequence');
insert into users(user_id, ...) values ($foo, ...)
(Without a transaction)
Sequences are far more flexible than auto number, don't you think?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
markw@mohawksoft.com wrote:
On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
Robert Cummings wrote:
On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote:
Colin Guthrie wrote:
Philip Thompson wrote:
For auto increment values, you don't have to specify the id. For
example:
INSERT INTO t_users (f_name, l_name, e_mail, b_date, pic)
VALUES ('$f_name', '$l_name', '$e_mail', '$b_date', null);
Then to find the latest entry:
SELECT user_id FROM t_users ORDER BY user_id DESC limit 1;
This is not the cleanest way and some databases do not actually
increment auto id fields (e.g. they could fill in the blanks from
previous deletes etc.).
Much better is to use the function in the MySQL API to get the
insert
id or if you really must use SQL, just run "SELECT
LAST_INSERT_ID();"
which does much the same thing.
Col
My own knowledge of mysql is about 5 years old and never really used.
But I was recently asked to do something that required some mysql
(noting too much, fortunately for me), so I've been doing some
reading
and am interested in questions that come up on the list.
An earlier post called attention to a concurrency problem. Wouldn't
getting the last inserted ID from LAST_INSERT_ID()
suffer from the same limitations as any of the other solutions which
do
a select to get the last ID?
No, it's a MySQL specific feature that is atomic with the insert and
so
you are guaranteed that the the returned ID is the exact automatic ID
associated with the most recent INSERT for the connection handle.
Unfortunately auto increment is MySQL specific and so it isn't
transferrable to other database engines.
The problem is if the ID returned is not somehow linked to the
transaction
which inserted the row. If you have an insert followed by a select to
retrieve the ID, in the interrum, another user may be created by another
process and you will retrieve the wrong ID.
I don't believe this is an issue for MySQL. The last inserted ID isn't
stored for the database table, it's stored for the connection to the
database. As such there's no race condition unless the developer does
something silly, like issue another insert over the same connection
before requesting the previous last inserted id. Just to be sure though
the following is from the MySQL online documentation:
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). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously
from multiple clients is perfectly valid. Each client will receive the
last inserted ID for the last statement that client executed.
http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
Cheers,
Rob.
--
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
I too thought the same thing, but was corrected when I starting working
on a redesign for the billing system for the company that I am working
for. They use PostgreSQL and we do have a auto incremented unique id
field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
is a value that is controlled by the default value entry.
It then runs this:
nextval('customers_customer_id_seq'::regclass)
and uses this as the value of the id field for the newly inserted row.
Then instead of running a command like SELECT LAST_INSERT_ID(); you have
to do some other stuff like this:
$SQL = "SELECT currval('{$table}_{$column}_seq');";
If you rely on oddities of a particular system, then you are doomed to be
stuck with it or pay the price of redesign when you need a more capable
system.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sat, 2007-03-17 at 14:08 -0400, markw@mohawksoft.com wrote: > > On Sat, 2007-03-17 at 12:19 -0400, Mark wrote: > >> Robert Cummings wrote: > > There in lies the biggest problem with LAMP, and that's MySQL. The > architecture of your methodology *only* works with MySQL, and not more > capable databases like Oracle, DB2, or even PostgreSQL. > > If you rely on oddities of a particular system, then you are doomed to be > stuck with it or pay the price of redesign when you need a more capable > system. I'd have to disagree, I'm currently working on largish project and an intentional decision was made to use the auto incrementing feature of MySQL for better speed. To move away from the MySQL auto increment feature in the future is as simple as changing code in one location. Many people using MySQL do so for a reason, to then discard the advantages of the chosen system should be weighed carefully against future expectations. Also, oddity implies a negative perspective, one man's oddity is another man's feature. I'm quite certain the developers at MySQL implemented auto increment as a feature to simplify the lives of developers working with databases. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
- Original Message - From: There in lies the biggest problem with LAMP, and that's MySQL. The architecture of your methodology *only* works with MySQL, and not more capable databases like Oracle, DB2, or even PostgreSQL. If you rely on oddities of a particular system, then you are doomed to be stuck with it or pay the price of redesign when you need a more capable system. Not so much. Most databases offer some sort of autoincrementing integer and a means to get its value immediately after an insert. The syntax both for creating it and retrieving it might vary, but it exists in some form or other. This does not excuse the overuse of autonumerics as primary keys and most often, the only key in a whole table. Significant keys, that is, keys that are meaningful for the data being stored, are far more valuable if they exist. Satyam -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
> On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
>> Robert Cummings wrote:
>>
>> > On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote:
>> >> Colin Guthrie wrote:
>> >> > Philip Thompson wrote:
>> >> >
>> >> >> For auto increment values, you don't have to specify the id. For
>> >> >> example:
>> >> >>
>> >> >> INSERT INTO t_users (f_name, l_name, e_mail, b_date, pic)
>> >> >> VALUES ('$f_name', '$l_name', '$e_mail', '$b_date', null);
>> >> >>
>> >> >> Then to find the latest entry:
>> >> >>
>> >> >> SELECT user_id FROM t_users ORDER BY user_id DESC limit 1;
>> >> >>
>> >> >
>> >> > This is not the cleanest way and some databases do not actually
>> >> > increment auto id fields (e.g. they could fill in the blanks from
>> >> > previous deletes etc.).
>> >> >
>> >> > Much better is to use the function in the MySQL API to get the
>> insert
>> >> > id or if you really must use SQL, just run "SELECT
>> LAST_INSERT_ID();"
>> >> > which does much the same thing.
>> >> >
>> >> > Col
>> >> >
>> >> >
>> >> My own knowledge of mysql is about 5 years old and never really used.
>> >> But I was recently asked to do something that required some mysql
>> >> (noting too much, fortunately for me), so I've been doing some
>> reading
>> >> and am interested in questions that come up on the list.
>> >>
>> >> An earlier post called attention to a concurrency problem. Wouldn't
>> >> getting the last inserted ID from LAST_INSERT_ID()
>> >> suffer from the same limitations as any of the other solutions which
>> do
>> >> a select to get the last ID?
>> >
>> > No, it's a MySQL specific feature that is atomic with the insert and
>> so
>> > you are guaranteed that the the returned ID is the exact automatic ID
>> > associated with the most recent INSERT for the connection handle.
>> > Unfortunately auto increment is MySQL specific and so it isn't
>> > transferrable to other database engines.
>>
>> The problem is if the ID returned is not somehow linked to the
>> transaction
>> which inserted the row. If you have an insert followed by a select to
>> retrieve the ID, in the interrum, another user may be created by another
>> process and you will retrieve the wrong ID.
>
> I don't believe this is an issue for MySQL. The last inserted ID isn't
> stored for the database table, it's stored for the connection to the
> database. As such there's no race condition unless the developer does
> something silly, like issue another insert over the same connection
> before requesting the previous last inserted id. Just to be sure though
> the following is from the MySQL online documentation:
>
>
> 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). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously
> from multiple clients is perfectly valid. Each client will receive the
> last inserted ID for the last statement that client executed.
>
>
> http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
>
> Cheers,
> Rob.
> --
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
If you rely on oddities of a particular system, then you are doomed to be
stuck with it or pay the price of redesign when you need a more capable
system.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Robert Cummings wrote: On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote: An earlier post called attention to a concurrency problem. Wouldn't getting the last inserted ID from LAST_INSERT_ID() suffer from the same limitations as any of the other solutions which do a select to get the last ID? No, it's a MySQL specific feature that is atomic with the insert and so you are guaranteed that the the returned ID is the exact automatic ID associated with the most recent INSERT for the connection handle. Unfortunately auto increment is MySQL specific and so it isn't transferrable to other database engines. The problem is if the ID returned is not somehow linked to the transaction which inserted the row. If you have an insert followed by a select to retrieve the ID, in the interrum, another user may be created by another process and you will retrieve the wrong ID. I don't believe this is an issue for MySQL. The last inserted ID isn't stored for the database table, it's stored for the connection to the database. As such there's no race condition unless the developer does something silly, like issue another insert over the same connection before requesting the previous last inserted id. Just to be sure though the following is from the MySQL online documentation: 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). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed. http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html Cheers, Rob. Thanks. Very interesting, and makes a lot of sense. -- _ Myron Turner http://www.room535.org http://www.bstatzero.org http://www.mturner.org/XML_PullParser/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
> Robert Cummings wrote:
>
> > On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote:
> >> Colin Guthrie wrote:
> >> > Philip Thompson wrote:
> >> >
> >> >> For auto increment values, you don't have to specify the id. For
> >> >> example:
> >> >>
> >> >> INSERT INTO t_users (f_name, l_name, e_mail, b_date, pic)
> >> >> VALUES ('$f_name', '$l_name', '$e_mail', '$b_date', null);
> >> >>
> >> >> Then to find the latest entry:
> >> >>
> >> >> SELECT user_id FROM t_users ORDER BY user_id DESC limit 1;
> >> >>
> >> >
> >> > This is not the cleanest way and some databases do not actually
> >> > increment auto id fields (e.g. they could fill in the blanks from
> >> > previous deletes etc.).
> >> >
> >> > Much better is to use the function in the MySQL API to get the insert
> >> > id or if you really must use SQL, just run "SELECT LAST_INSERT_ID();"
> >> > which does much the same thing.
> >> >
> >> > Col
> >> >
> >> >
> >> My own knowledge of mysql is about 5 years old and never really used.
> >> But I was recently asked to do something that required some mysql
> >> (noting too much, fortunately for me), so I've been doing some reading
> >> and am interested in questions that come up on the list.
> >>
> >> An earlier post called attention to a concurrency problem. Wouldn't
> >> getting the last inserted ID from LAST_INSERT_ID()
> >> suffer from the same limitations as any of the other solutions which do
> >> a select to get the last ID?
> >
> > No, it's a MySQL specific feature that is atomic with the insert and so
> > you are guaranteed that the the returned ID is the exact automatic ID
> > associated with the most recent INSERT for the connection handle.
> > Unfortunately auto increment is MySQL specific and so it isn't
> > transferrable to other database engines.
>
> The problem is if the ID returned is not somehow linked to the transaction
> which inserted the row. If you have an insert followed by a select to
> retrieve the ID, in the interrum, another user may be created by another
> process and you will retrieve the wrong ID.
I don't believe this is an issue for MySQL. The last inserted ID isn't
stored for the database table, it's stored for the connection to the
database. As such there's no race condition unless the developer does
something silly, like issue another insert over the same connection
before requesting the previous last inserted id. Just to be sure though
the following is from the MySQL online documentation:
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). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously
from multiple clients is perfectly valid. Each client will receive the
last inserted ID for the last statement that client executed.
http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
Cheers,
Rob.
--
..
| InterJinn Application Framework - http://www.interjinn.com |
::
| An application and templating framework for PHP. Boasting |
| a powerful, scalable system for accessing system services |
| such as forms, properties, sessions, and caches. InterJinn |
| also provides an extremely flexible architecture for |
| creating re-usable components quickly and easily. |
`'
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Robert Cummings wrote:
> On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote:
>> Colin Guthrie wrote:
>> > Philip Thompson wrote:
>> >
>> >> For auto increment values, you don't have to specify the id. For
>> >> example:
>> >>
>> >> INSERT INTO t_users (f_name, l_name, e_mail, b_date, pic)
>> >> VALUES ('$f_name', '$l_name', '$e_mail', '$b_date', null);
>> >>
>> >> Then to find the latest entry:
>> >>
>> >> SELECT user_id FROM t_users ORDER BY user_id DESC limit 1;
>> >>
>> >
>> > This is not the cleanest way and some databases do not actually
>> > increment auto id fields (e.g. they could fill in the blanks from
>> > previous deletes etc.).
>> >
>> > Much better is to use the function in the MySQL API to get the insert
>> > id or if you really must use SQL, just run "SELECT LAST_INSERT_ID();"
>> > which does much the same thing.
>> >
>> > Col
>> >
>> >
>> My own knowledge of mysql is about 5 years old and never really used.
>> But I was recently asked to do something that required some mysql
>> (noting too much, fortunately for me), so I've been doing some reading
>> and am interested in questions that come up on the list.
>>
>> An earlier post called attention to a concurrency problem. Wouldn't
>> getting the last inserted ID from LAST_INSERT_ID()
>> suffer from the same limitations as any of the other solutions which do
>> a select to get the last ID?
>
> No, it's a MySQL specific feature that is atomic with the insert and so
> you are guaranteed that the the returned ID is the exact automatic ID
> associated with the most recent INSERT for the connection handle.
> Unfortunately auto increment is MySQL specific and so it isn't
> transferrable to other database engines.
The problem is if the ID returned is not somehow linked to the transaction
which inserted the row. If you have an insert followed by a select to
retrieve the ID, in the interrum, another user may be created by another
process and you will retrieve the wrong ID.
If you have no users, it is unlikely, the more traffic your site has, the
more likely the problem. It is a *bad* design and any 1st year CS student
should know better.
>
>> I assume if you are completely in control
>> of the database, you could create a lock file using flock() and remove
>> the lock once the Id is retrieved using any of these methods.
>
> You can, but locking is expensive, especial if there's a lot of activity
> in the table.
MySQL locks too much already, one more lock won't hurt :-)
>
>> I guess what I'm wondering is whether the simplest suggestion is the one
>> that would use the email address as a condition in the WHERE clause to
>> extract the ID?
>
> No.
insert into the_table (..., email,..);
select user_id from the_table where email = '...';
Is probably the best way given the nature of the table and skill level of
the developer asking the question.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote:
> Colin Guthrie wrote:
> > Philip Thompson wrote:
> >
> >> For auto increment values, you don't have to specify the id. For example:
> >>
> >> INSERT INTO t_users (f_name, l_name, e_mail, b_date, pic)
> >> VALUES ('$f_name', '$l_name', '$e_mail', '$b_date', null);
> >>
> >> Then to find the latest entry:
> >>
> >> SELECT user_id FROM t_users ORDER BY user_id DESC limit 1;
> >>
> >
> > This is not the cleanest way and some databases do not actually
> > increment auto id fields (e.g. they could fill in the blanks from
> > previous deletes etc.).
> >
> > Much better is to use the function in the MySQL API to get the insert id
> > or if you really must use SQL, just run "SELECT LAST_INSERT_ID();" which
> > does much the same thing.
> >
> > Col
> >
> >
> My own knowledge of mysql is about 5 years old and never really used.
> But I was recently asked to do something that required some mysql
> (noting too much, fortunately for me), so I've been doing some reading
> and am interested in questions that come up on the list.
>
> An earlier post called attention to a concurrency problem. Wouldn't
> getting the last inserted ID from LAST_INSERT_ID()
> suffer from the same limitations as any of the other solutions which do
> a select to get the last ID?
No, it's a MySQL specific feature that is atomic with the insert and so
you are guaranteed that the the returned ID is the exact automatic ID
associated with the most recent INSERT for the connection handle.
Unfortunately auto increment is MySQL specific and so it isn't
transferrable to other database engines.
> I assume if you are completely in control
> of the database, you could create a lock file using flock() and remove
> the lock once the Id is retrieved using any of these methods.
You can, but locking is expensive, especial if there's a lot of activity
in the table.
> I guess what I'm wondering is whether the simplest suggestion is the one that
> would use the email address as a condition in the WHERE clause to
> extract the ID?
No.
Cheers,
Rob.
--
..
| InterJinn Application Framework - http://www.interjinn.com |
::
| An application and templating framework for PHP. Boasting |
| a powerful, scalable system for accessing system services |
| such as forms, properties, sessions, and caches. InterJinn |
| also provides an extremely flexible architecture for |
| creating re-usable components quickly and easily. |
`'
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Getting last record ID created from DB
Colin Guthrie wrote:
Philip Thompson wrote:
For auto increment values, you don't have to specify the id. For example:
INSERT INTO t_users (f_name, l_name, e_mail, b_date, pic)
VALUES ('$f_name', '$l_name', '$e_mail', '$b_date', null);
Then to find the latest entry:
SELECT user_id FROM t_users ORDER BY user_id DESC limit 1;
This is not the cleanest way and some databases do not actually
increment auto id fields (e.g. they could fill in the blanks from
previous deletes etc.).
Much better is to use the function in the MySQL API to get the insert id
or if you really must use SQL, just run "SELECT LAST_INSERT_ID();" which
does much the same thing.
Col
My own knowledge of mysql is about 5 years old and never really used.
But I was recently asked to do something that required some mysql
(noting too much, fortunately for me), so I've been doing some reading
and am interested in questions that come up on the list.
An earlier post called attention to a concurrency problem. Wouldn't
getting the last inserted ID from LAST_INSERT_ID()
suffer from the same limitations as any of the other solutions which do
a select to get the last ID? I assume if you are completely in control
of the database, you could create a lock file using flock() and remove
the lock once the Id is retrieved using any of these methods. I guess
what I'm wondering is whether the simplest suggestion is the one that
would use the email address as a condition in the WHERE clause to
extract the ID?
--
_
Myron Turner
http://www.room535.org
http://www.bstatzero.org
http://www.mturner.org/XML_PullParser/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
