Any SQL experts here?

Andy Dingley wrote:
> On 6 Feb, 10:10, Dylan Parry <use....RemoveThis@dylanparry.com> wrote:
>
>> unique AFAIK, they are based on a combination of a MAC address, the
>> time and processor cycles or something, so they'll always be unique.
>
> GUID are always unique, but there's a rate limit to how fast they can
> be allocated (per MAC address). Potentially a machine with more NICs
> in it could allocate them at a proportionally faster rate, but I've
> never seen it done.
>

Wikipedia disagrees with you:

http://en.wikipedia.org/wiki/Globally_Unique_Identifier

"While each generated GUID is not guaranteed to be unique, the total
number of unique keys (2128 or 3.4×1038)[1] is so large that the
probability of the same number being generated twice is very small..."

The only way something can be guaranteed to be unique is to have it
perfectly unbounded - which we know is impossible.

> The _vast_ advantage of GUIDs is that they can be allocated uniquely
> by a number of separate non-communicating machines. If you need them
> more quickly, just have more machines doing the allocation work,
> conveniently the "client" machines that are feeding the central DB
> server. Allocate them outside the SP and pass them in as data
> parameters, you don't have to allocate them _when_ the new row is
> added to our "Table A".
>

The disadvantage is they require more disk space and put a lot of
unnecessary work on the server. Most identity columns are integers for
a simple reason - they're small and can be compared quickly.

Using a non-integer column increases the amount of space required for
each row. And since identity columns are also generally indexed, it
adds to the size of each entry in the index(es).

This affects performance in many ways - from the server needing to do
more reads to get indexes and rows to more memory required for keeping
indexes sorted. Even just comparing GUID's instead of integers is a
significantly longer process.

> Goodbye to the centralised Identity column allocation bottleneck! Now
> _that's_ worth having!!
>

Hello to the performance bottleneck. That's NOT worth having!!
>
>> The problem with GUIDs is that they take up a fair bit of space as they
>> are quite long, which of course makes them trickier to index.
>
> Red herring (although proving it in all cases isn't simple).
>

Not at all a red herring.

> Firstly they're not really that big. 128 bits is only 2 words (4 words
> on little hardware). 16 ASCII characters. You're not having to handle
> them as the big long string that they look like when printed!
>

See my notes above.

> Secondly, column length isn't such a big deal for indexing, compared
> to the number of index values and the size of the resultant indices.
> Entropy per bit in GUIDs is pretty low (certainly compared to ordinal
> word values) and using a longer type to address the rows themselves
> just doesn't change the index performance that much.
>

With small tables, no. With large tables, yes.

> Thirdly, just benchmark it. Not a problem.
>

I have - on large tables. Have you? Try it with a 100M row table, for
instance.

> Fouthly, Kimball recommended it and he does rather know what he's on
> about.
>

So? He's one person. He has his opinions.

But what you're saying is that MySQL, Microsoft, IBM and others know
less than this one guy?

And BTW - a little more searching and I found identity columns were
added to the SQL 2003 spec. I don't have a copy of the entire spec
here, so I don't know if they defined a way to return the column id yet.
But even ANSI agrees that identity columns are a good thing.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.RemoveThis@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:

>> The problem with GUIDs is that they take up a fair bit of space as they
>> are quite long, which of course makes them trickier to index.
>
> Red herring (although proving it in all cases isn't simple).
[...]

TBH, I was just going on what I have been told in the past. I've not
actually noticed a speed hit myself, but I always assumed that was
because I had puny databases!

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 17:47, Jerry Stuckle <jstuck... RemoveThis @attglobal.net> wrote:

Even though your posting style is always more interested in combative
point-scoring than conveying any real useful content, this one's a
doozy even by your standards.


> >> No, the mysql_insert_is() is a MySQL call, not PHP.
>
> > LAST_INSERT_ID() is a MySQL function, mysql_insert_id() is PHP
>
> Check again. mysql_insert_id() is a MySQL C function. The PHP call is
> just a wrapper around it.

Whatever. Whoever the guilty party is, mysql_insert_id() (callable
from PHP) isn't an exact facade for LAST_INSERT_ID() (not callable
from PHP)


> > Secondly it operates at different scope to the MySQL function, that of
> > the connection. It's a pathological case to construct an example where
> > that would break something, but it's still amateurish language design.
>
> Not at all. Every other database which has a similar function works in
> the same way.

I've never seen (or at least used) another database that provided a
similar function _as_a_client_language_construct_. They provide them
as extensions to SQL, as for LAST_INSERT_ID(), which is as it should
be. It's what you need, where you need it, and it doesn't bring the
added scope risks of mysql_insert_id()


> No, it can't. You typically want the insert id immediately after the
> actual insertion is performed, so it they will be on the same connection
> if you don't specify a connection.

If you don't specify a connection on either of them. In which case
we're back to stamp-coupling through a shared global connection and
the risk of being hit by another piece of code returns.

> Or, the simple way is to just specify the connection on both calls.

You think typical PHP coders will do that?


> > Fourthly it uses that nasty PHP business of recompiling the PHP
> > language engine with MySQL support switched on, rather than having a
> > dynamically loadable (or even a statically bindable) driver component
> > for DBs.
>
> Yep, and there's not a thing wrong with compiling extensions.

This isn't an "extension" though, as it compiles into the core
language engine.

> In fact,
> if you want to use ANY database with ANY language, you have to compile
> that support in.

I have to compile in some support for an abstract DB. I shouldn't have
to compile in different support for each instance of a server
platform.

> Even ODBC support must be compiled into the package if
> you're going to use it.

ODBC is an API though, not a database. I don't have to recompile my
core if I swap my ODBC connection from pointing to Oracle to pointing
at MySQL.


> >> Every database nowadays has it's own extensions to SQL.
>
> > Of course. So we use the well-known principle of "encapsulation" to
> > _hide_ this, not make it more and more manifest thorugh higher levels
> > of our application language.
>
> So how do you access any database if you don't have any database
> extensions linked in?

I pass SQL _through_ my compiled-DB support and it's interpreted on
the server. As far as the connection cares, it's just "source code".


> >>> 4. Connection pooling. A pooled DB connection can certainly break this
> >>> scope model, if you make two allocating operations on it before
> >>> reading the identity value(s).
> >> Only if the pool is broken.
>
> > Perhaps. A good pool should detect this (as a scoping error) and raise
> > an exception, but it still won't work.
>
> No, a good pool should not allocate a connection which is still in use.

Again, it's hard to argue with that.

> It should find an open connection,

What's an "open" connection though? Are you asking for an explicit
"release" operation to be called upon it?



> > What's _wrong_ with using LAST_INSERT_ID() insert some neatly packaged
> > SQL module, then throwing the value away afterwards because it really
> > isn't necessary to know it afterwards? That's good design, according
> > to "modular programming" concepts that have been regarded as a basic
> > starting point for good design for 30-some years now.
>
> Why even use it if you're just going to throw it away?

Because the DB has already stored it. However the application code no
longer needs the value. In most cases, and where primary keys used for
foreign keys are anonymous sequences rather than visible SSNs etc,
then this is just as it should be. Locate the records through their
descriptive properties, leave the internal IDs inside the DB code as
much as possible. If you absolutely must, retrieve a "reference"
later and leave it alone as an opaque reference the app code doesn't
fiddle with.

> And just in case you haven't noticed - "good design" has changed over
> the last 30 years. It's hardly recognizable from what it was in the
> 1970's. Back then, even structured programming was an oddity, and
> object oriented programming virtually unheard of.

I referred to modular programming, rather than structured, but even
so, what's the problem? Most of the good stuff around today has been
around a long time too. The good ideas from this period didn't stop
working, even though we found even better ideas that built on top of
them.

OO or RDBM are the obvious inventions from Parc or IBM (and they've
been mainstream for 20 years now), but even MVC is another bright-idea
of today that dates from way back then.

> So do you still use goto statements? We did 30 years ago.

We used them, but no-one was claiming they were a great new idea.
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:
> On 6 Feb, 12:33, Jerry Stuckle <jstuck....TakeThisOut@attglobal.net> wrote:
>
>>> This is PHP's ghastly "swiss army knife" design principle
>>> where any random dregs get added to the language "just in case they
>>> come in handy", not from any sensible design principle.
>> No, the mysql_insert_is() is a MySQL call, not PHP.
>
> LAST_INSERT_ID() is a MySQL function, mysql_insert_id() is PHP
>

Check again. mysql_insert_id() is a MySQL C function. The PHP call is
just a wrapper around it.

> It's not even a good facade for LAST_INSERT_ID(). Most grievously the
> return type is wrong, so it doesn't work with bigints. That's just
> shoddy.
>

I don't think you can set a BIGINT as an autoincrement column. Could
be, because I've never tried it. I've never had a need to consider over
4B rows in a table.

> Secondly it operates at different scope to the MySQL function, that of
> the connection. It's a pathological case to construct an example where
> that would break something, but it's still amateurish language design.
>

Not at all. Every other database which has a similar function works in
the same way.

> Thirdly, and probably the most likely to raise a real live bug, it
> only scopes to a connection if you specify one by parameter, otherwise
> it defaults to the last one _opened_ (not the last one with an execute
> on it). So now you can break PHP your (sloppy) PHP code by changing
> startup code that's pages and pages away.
>

No, it can't. You typically want the insert id immediately after the
actual insertion is performed, so it they will be on the same connection
if you don't specify a connection. Or, the simple way is to just
specify the connection on both calls.

> Fourthly it uses that nasty PHP business of recompiling the PHP
> language engine with MySQL support switched on, rather than having a
> dynamically loadable (or even a statically bindable) driver component
> for DBs.
>

Yep, and there's not a thing wrong with compiling extensions. In fact,
if you want to use ANY database with ANY language, you have to compile
that support in. Even ODBC support must be compiled into the package if
you're going to use it.

>
>> Every database nowadays has it's own extensions to SQL.
>
> Of course. So we use the well-known principle of "encapsulation" to
> _hide_ this, not make it more and more manifest thorugh higher levels
> of our application language.
>

So how do you access any database if you don't have any database
extensions linked in?

As a matter of fact, I encapsulate my database specific stuff, also. I
can change the entire RDBMS being used simply by changing the base
class. And all RDBMS-specific extensions (like fetching an identity
column) are in the base class.

>> The real problem here is the ANSI SQL spec is lacking,
>
> Can't argue with that!
>
>
>>> 4. Connection pooling. A pooled DB connection can certainly break this
>>> scope model, if you make two allocating operations on it before
>>> reading the identity value(s).
>> Only if the pool is broken.
>
> Perhaps. A good pool should detect this (as a scoping error) and raise
> an exception, but it still won't work.
>

No, a good pool should not allocate a connection which is still in use.
It should find an open connection, or if one doesn't exist, return a
null value (or exception or similar) if one isn't available. It should
also log the error so the admin knows he/she needs to add more
connections to the pool.

Alternatively, a pool can be designed that, if it runs out of
connections, it attempts to fetch a temporary connection which is
released when completed. But even then it should be logged.


>
> What's _wrong_ with using LAST_INSERT_ID() insert some neatly packaged
> SQL module, then throwing the value away afterwards because it really
> isn't necessary to know it afterwards? That's good design, according
> to "modular programming" concepts that have been regarded as a basic
> starting point for good design for 30-some years now.
>
>
>

Why even use it if you're just going to throw it away?

And just in case you haven't noticed - "good design" has changed over
the last 30 years. It's hardly recognizable from what it was in the
1970's. Back then, even structured programming was an oddity, and
object oriented programming virtually unheard of.

So do you still use goto statements? We did 30 years ago.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 19:25, Jerry Stuckle <jstuck....RemoveThis@attglobal.net> wrote:
> John Bokma wrote:

> > A backup is not going to help you with data that gets mangled thanks to
> > misunderstanding, it's just backed up. And repairing the data when you
> > finally discover that IDs are out of order, is either hard or impossible
> > to fix.
>
> Sure it will. Just reload the backup from before the problem happened.
> It happens daily - not necessarily due to mangled data, but disk
> crashes and the link.

You really don't have any big-system experience, do you?!
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:
> On 6 Feb, 12:50, Jerry Stuckle <jstuck....TakeThisOut@attglobal.net> wrote:
>
>>> GUID are always unique, but there's a rate limit to how fast they can
>>> be allocated (per MAC address).
>> Wikipedia disagrees with you:
>> http://en.wikipedia.org/wiki/Globally_Unique_Identifier
>
> That's because I don't have time to fix _all_ of wikipedia.
>
>
>> "While each generated GUID is not guaranteed to be unique,
>
> GUIDs are unique provided they're merely global, not universal.
> Generating duplicates is forbidden in principle, but this does rely on
> industry rules about MAC allocation. Ask dorayme if you want to know
> how these operate off-planet.
>

I know how they work, and I still agree with Wikipedia. There is no way
members of a bounded set can be *guaranteed* unique. Admittedly, it is
a *very far* chance of a collision happening, but they can happen.


>
>> The only way something can be guaranteed to be unique is to have it
>> perfectly unbounded - which we know is impossible.
>
> That's one way. The other, and simpler way, is just to restrict the
> number that can exist.
>

Nope. Eventually you will run out of numbers. Then what do you do?

>
>> The disadvantage is they require more disk space
>
> Can't afford 128 bits? You must be paying your developers awfully
> cheaply if that's a remotely good trade-off.
>

Let's see.. 128 bits is 16 bytes. An integer is 4 bytes.

Let's look at a table with 10M rows. You will have 160M bytes just for
your key field. I will have 40M bytes. At 100M rows, that increases to
1.6B bytes for you and 400M bytes for me. That 1.2B bytes can be
important, especially if you are restricted by the OS to a 4GB table.

And lets look at the indexes. Indexes typically use a 4 byte field to
reference the row location in the table. So at 10M rows, your index
will take up 200M bytes; mine will take up 80M bytes. At 100M rows,
yours will require 2B bytes, mine 800M bytes. That's a significant
difference when the index needs to be sorted (i.e. any INSERT or
DELETE). Additionally, integer comparison is a single machine
instruction and very fast. GUID's require a loop and multiple
comparisons, and is much slower.

In short, your method of using GUID's is not very scalable.


>> and put a lot of unnecessary work on the server.
>
> They're more efficient to allocate than sequences (especially for
> isolated sequences on Oracle or DB2). The algorithm is trivial,
> because it's simply generative, not collaborative. A new GUID can be
> allocated without any reference to the last value stored. In most
> cases (at slower rates) it doesn't even need to check the last value
> generated.
>

The allocation of the GUID itself may be more efficient. But that's
minor compared to the overhead of USING the GUID.

> It's also possible to off-load them entirely from the central server,
> and to allocate them safely and reliably on the clients.
>

Still the same problem.

>
>> Most identity columns are integers for
>> a simple reason - they're small and can be compared quickly.
>
> Yes, two reasons which are simply irerelvant these days. Comparing
> execution speeds for compare operations on 64 bits vs. 128 bits just
> isn't something I've worried about this decade. For practical purposes
> on DB servers today, GUIDs are also "small" and "quick". You might
> have a point for embedded systems, but we're talking about web-app DB
> servers here.
>

Not at all. They are completely relevant when your database gets large.
And there are some very large databases out there.

>
>> Using a non-integer column increases the amount of space required for
>> each row. And since identity columns are also generally indexed, it
>> adds to the size of each entry in the index(es).
>
>> This affects performance in many ways
>
> It only affect the size if you count bytes, not entries. It may then
> affect size in pages (i.e. rows per page), but this isn't as big an
> effect as you might expect. For non-primary keys (i.e. the search
> operation is taking place over a non-GUID value and the GUID is merely
> used for referencing), then the effect is trivial.
>

Yes, but I have yet to see any processor which works in "entries".
Every computer I've ever seen uses bytes. So it is very relevant. And
the effect is not trivial.

>> Even just comparing GUID's instead of integers is a significantly longer process.
>
> Of the order of a few clock cycles. That's just not important.
>

On the order of 10-20x the number of clock cycles for the actual
comparison, depending on the processor. It's a huge difference when
you're looking at large databases.

And when you're using them as foreign keys, those comparisons must be
done multiple times using both tables. This is done much more often
than just creating GUID's.

>
>>> Goodbye to the centralised Identity column allocation bottleneck! Now
>>> _that's_ worth having!!
>> Hello to the performance bottleneck. That's NOT worth having!!
>
> I know you haven't used or benchmarked them, so why would you think
> that?
>

No, I haven't benchmarked GUIDs themselves. But I have benchmarked
other types of keys of smaller and larger size. Have you benchmarked
them with a 100M row table and compared them?

>>> Secondly, column length isn't such a big deal for indexing, compared
>>> to the number of index values and the size of the resultant indices.
>>> Entropy per bit in GUIDs is pretty low (certainly compared to ordinal
>>> word values) and using a longer type to address the rows themselves
>>> just doesn't change the index performance that much.
>> With small tables, no. With large tables, yes.
>
> Why does the index length (in entries) affect this linear scaling
> factor?
>

As noted above.

>>> Thirdly, just benchmark it. Not a problem.
>> I have - on large tables. Have you?
>
> Read my patents.
>

I could care less about your patents, Andy.

>> Try it with a 100M row table, for instance.
>
> Why would I want to do such a pointless test? Stop worrying about
> number of rows in a single table and doing a simple SELECT, start
> thinking about multi-table situations.
>

You're the one who indicates the effect is irrelevant. Now you aren't
willing to run benchmarks to prove your hypothesis?

> In particular, how did you generate those 100M rows in the first
> place? If you have a high append rate from multiple writers to one
> long table, then you're going to be affected badly by the allocation
> rate of an identity column. The big advantage of GUIDs is the trivial
> parrallelization of their allocation algorithm.
>

I've worked on some very large databases over the years - even
mainframes with > 100M entries. The size of the key used to
cross-reference tables is very critical - as anyone with experience in
large databases will tell you.

>
>>> Fouthly, Kimball recommended it and he does rather know what he's on
>>> about.
>> So? He's one person. He has his opinions.
>
> He's also a Lensman and has a natty line in grey leather jodhpurs.
>

So? He's still one guy with an opinion.

>
>> But what you're saying is that MySQL, Microsoft, IBM and others know
>> less than this one guy?
>
> Very much so! They don't go to Jerry Stuckle for advice, but they
> certainly do go to Kimball.
>

A lot of people come to me for advice, also, Andy. My customers, for
instance. And other consultants. And they don't go to Kimball.

>
>> But even ANSI agrees that identity columns are a good thing.
>
> Of course they are - they're far better than using max(). GUIDs are an
> even better approach (in almost all circumstances). They're a
> massively better approach for some, and they're almost never harmful
> either.
>

And ANSI disagrees with you. As do IBM, Microsoft, and other major
RDBMS players. Otherwise they could have implemented an automatic GUID
creation in their database. But they didn't.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

mynameisnobodyodyssea.TakeThisOut@googlemail.com wrote:
> On Feb 6, 12:33 pm, Jerry Stuckle wrote:
>
>> mynameisnobodyodys... wrote:
>>> On Feb 5, 11:03 pm, Jerry Stuckle wrote:
>>>> Time is not necessarily unique.
>>> High resolution time can be unique... sometimes.
>> The salient point key being "sometimes".
>
> The salient point key was meant to be high resolution
> (for a non-parallel system).
> Anyway, time was just a suggestion
> (or a combination of things including time
> plus maybe a random string),
> but anything unique would obviously do.
>
>

Well, what happens, for instance, if your NTP daemon kicks in and sets
the clock back 2 seconds? Or come the end of daylight savings time when
the entire system goes back one hour?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:
> On 6 Feb, 17:47, Jerry Stuckle <jstuck... RemoveThis @attglobal.net> wrote:
>
> Even though your posting style is always more interested in combative
> point-scoring than conveying any real useful content, this one's a
> doozy even by your standards.
>
>
>>>> No, the mysql_insert_is() is a MySQL call, not PHP.
>>> LAST_INSERT_ID() is a MySQL function, mysql_insert_id() is PHP
>> Check again. mysql_insert_id() is a MySQL C function. The PHP call is
>> just a wrapper around it.
>
> Whatever. Whoever the guilty party is, mysql_insert_id() (callable
> from PHP) isn't an exact facade for LAST_INSERT_ID() (not callable
> from PHP)
>

You should check your facts better, Andy. SELECT LAST_INSERT_ID() works
fine in PHP. And they are exactly the same. Just one is a little more
efficient than the other (no need to parse the SQL).

>
>>> Secondly it operates at different scope to the MySQL function, that of
>>> the connection. It's a pathological case to construct an example where
>>> that would break something, but it's still amateurish language design.
>> Not at all. Every other database which has a similar function works in
>> the same way.
>
> I've never seen (or at least used) another database that provided a
> similar function _as_a_client_language_construct_. They provide them
> as extensions to SQL, as for LAST_INSERT_ID(), which is as it should
> be. It's what you need, where you need it, and it doesn't bring the
> added scope risks of mysql_insert_id()
>

No, MySQL has just made it a little more efficient. And as I said
before, you can call LAST_INSERT_ID() from PHP, also.

>
>> No, it can't. You typically want the insert id immediately after the
>> actual insertion is performed, so it they will be on the same connection
>> if you don't specify a connection.
>
> If you don't specify a connection on either of them. In which case
> we're back to stamp-coupling through a shared global connection and
> the risk of being hit by another piece of code returns.
>

The simple answer is - use good coding practices. That's why you can
pass the connection to the functions.

>> Or, the simple way is to just specify the connection on both calls.
>
> You think typical PHP coders will do that?
>

It's up to them.

>
>>> Fourthly it uses that nasty PHP business of recompiling the PHP
>>> language engine with MySQL support switched on, rather than having a
>>> dynamically loadable (or even a statically bindable) driver component
>>> for DBs.
>> Yep, and there's not a thing wrong with compiling extensions.
>
> This isn't an "extension" though, as it compiles into the core
> language engine.
>

It is considered an extension. It is something additional to the basic
PHP code.

>> In fact,
>> if you want to use ANY database with ANY language, you have to compile
>> that support in.
>
> I have to compile in some support for an abstract DB. I shouldn't have
> to compile in different support for each instance of a server
> platform.
>

And how do you access Oracle, MS SQL and DB2 from your "abstract DB"
without the appropriate libraries? ODBC? So you need to compile in the
ODBC library code.

>> Even ODBC support must be compiled into the package if
>> you're going to use it.
>
> ODBC is an API though, not a database. I don't have to recompile my
> core if I swap my ODBC connection from pointing to Oracle to pointing
> at MySQL.
>

That's true. But you still have to compile in the ODBC code. And not
only is it slower than native access to any of the databases, you are
also limited to the least common denominator across all databases. You
can't take advantage of the database-specific features.

And BTW - have you looked at the cost of the MS SQL ODBC driver for
Linux? $1,500 just to access the database.

>
>>>> Every database nowadays has it's own extensions to SQL.
>>> Of course. So we use the well-known principle of "encapsulation" to
>>> _hide_ this, not make it more and more manifest thorugh higher levels
>>> of our application language.
>> So how do you access any database if you don't have any database
>> extensions linked in?
>
> I pass SQL _through_ my compiled-DB support and it's interpreted on
> the server. As far as the connection cares, it's just "source code".
>

So you send the SQL to another system, parse it to see what needs to be
done, perform the operation, then send the data back across the link.
Seems to be an awful lot of overhead.

My programs call the database directly across the link. That's one less
thing which needs to be done, and one less thing which can go wrong.

>
>>>>> 4. Connection pooling. A pooled DB connection can certainly break this
>>>>> scope model, if you make two allocating operations on it before
>>>>> reading the identity value(s).
>>>> Only if the pool is broken.
>>> Perhaps. A good pool should detect this (as a scoping error) and raise
>>> an exception, but it still won't work.
>> No, a good pool should not allocate a connection which is still in use.
>
> Again, it's hard to argue with that.
>
>> It should find an open connection,
>
> What's an "open" connection though? Are you asking for an explicit
> "release" operation to be called upon it?
>

Yes, when you use a pool, you request a connection from the pool then
release the connection when you're done. Otherwise the pool doesn't
know whether the connection is active or not.

>
>
>>> What's _wrong_ with using LAST_INSERT_ID() insert some neatly packaged
>>> SQL module, then throwing the value away afterwards because it really
>>> isn't necessary to know it afterwards? That's good design, according
>>> to "modular programming" concepts that have been regarded as a basic
>>> starting point for good design for 30-some years now.
>> Why even use it if you're just going to throw it away?
>
> Because the DB has already stored it. However the application code no
> longer needs the value. In most cases, and where primary keys used for
> foreign keys are anonymous sequences rather than visible SSNs etc,
> then this is just as it should be. Locate the records through their
> descriptive properties, leave the internal IDs inside the DB code as
> much as possible. If you absolutely must, retrieve a "reference"
> later and leave it alone as an opaque reference the app code doesn't
> fiddle with.
>

True - but you need to fetch the insert id to add rows to the related
tables.

>> And just in case you haven't noticed - "good design" has changed over
>> the last 30 years. It's hardly recognizable from what it was in the
>> 1970's. Back then, even structured programming was an oddity, and
>> object oriented programming virtually unheard of.
>
> I referred to modular programming, rather than structured, but even
> so, what's the problem? Most of the good stuff around today has been
> around a long time too. The good ideas from this period didn't stop
> working, even though we found even better ideas that built on top of
> them.
>

No, most "good stuff" has appeared in the 30 years. Structured and OO
programming, for instance.

> OO or RDBM are the obvious inventions from Parc or IBM (and they've
> been mainstream for 20 years now), but even MVC is another bright-idea
> of today that dates from way back then.
>

DB2 has been around a lot longer than 20 years. OO is a completely new
concept. And MVC may have been talked about, but was seldom used 30
years ago.

>> So do you still use goto statements? We did 30 years ago.
>
> We used them, but no-one was claiming they were a great new idea.
>


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex RemoveThis @attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

On Tue, 5 Feb 2008 12:20:48 -0800 (PST), Andy Dingley put finger to
keyboard and typed:

>On 5 Feb, 20:06, Mark Goodge <use....DeleteThis@listmail.good-stuff.co.uk> wrote:
>
>> I've worked with PHP and Perl accessing MySQL and using
>> LAST_INSERT_ID() on large (ISP level) installations and never had a
>> problem with it yet.
>
>Then they weren't large installations.

How big do you call "large"?

>If you meet a DBA who _hasn't_ yet encountered some sort of obscure
>problem caused by mis-use of code for reading allocated identity
>values (usually other peoples' code), then they're still pretty low-
>mileage.

Oh, I've encontered problems with all sorts of code. But it's always
been the code that was the problem, not MySQL. I have never known
LAST_INSERT_ID() to return an incorrect value. I have known code that
misuses that value (or misunderstands that value), though.

Mark
--
http://www.BritishSurnames.co.uk - What does your surname say about you?
"We were meant to live for so much more"
 >> Stay informed about: Any SQL experts here? 

John Bokma wrote:

> No, the moral of the story is to really hire someone when small mistakes
> can result in huge issues.
>
> A backup is not going to help you with data that gets mangled thanks to
> misunderstanding, it's just backed up. And repairing the data when you
> finally discover that IDs are out of order, is either hard or impossible
> to fix.
>
> And don't get me started how newbies, especially using PHP, overlook
> security and SQL injection. (Even the developers at WordPress, for
> example, seem to have a hard time with it).
>

John you don't learn anything at all by getting someone to do all the
hard bits for you. I'd rather find out for myself; if it becomes to
complex or time-consuming, then I'll consider hiring someone with more
experience.

I'm not a n00b to web design, or web application design, and we're not
using PHP. I came looking for advice so that I could experiment and
learn how to do a particular query myself.

Plus... DBA's are really expensive to hire!

--

x theSpaceGirl (miranda)

http://www.northleithmill.com

-.-

Kammy has a new home: http://www.bitesizedjapan.com
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:
> On 6 Feb, 19:25, Jerry Stuckle <jstuck....RemoveThis@attglobal.net> wrote:
>> John Bokma wrote:
>
>>> A backup is not going to help you with data that gets mangled thanks to
>>> misunderstanding, it's just backed up. And repairing the data when you
>>> finally discover that IDs are out of order, is either hard or impossible
>>> to fix.
>> Sure it will. Just reload the backup from before the problem happened.
>> It happens daily - not necessarily due to mangled data, but disk
>> crashes and the link.
>
> You really don't have any big-system experience, do you?!
>

To start with, 8 years of mainframes while working for IBM. Some awful
big databases out there.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.RemoveThis@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Jerry Stuckle wrote:
> mynameisnobodyodyssea DeleteThis @googlemail.com wrote:
>> On Feb 6, 12:33 pm, Jerry Stuckle wrote:
>>
>>> mynameisnobodyodys... wrote:
>>>> On Feb 5, 11:03 pm, Jerry Stuckle wrote:
>>>>> Time is not necessarily unique.
>>>> High resolution time can be unique... sometimes.
>>> The salient point key being "sometimes".
>>
>> The salient point key was meant to be high resolution
>> (for a non-parallel system).
>> Anyway, time was just a suggestion
>> (or a combination of things including time
>> plus maybe a random string),
>> but anything unique would obviously do.
>>
>>
>
> Well, what happens, for instance, if your NTP daemon kicks in and sets
> the clock back 2 seconds? Or come the end of daylight savings time when
> the entire system goes back one hour?
>

Epoch time always goes forward tho... it's timezone / daylight saving
agnostic. But don't know if you can access that from within SQL or
scripting languages.

But you made a good point - I've seen some very smart people get unstuck
just this year because of one little extra day... It's easy to forget
these things.

--

x theSpaceGirl (miranda)

http://www.northleithmill.com

-.-

Kammy has a new home: http://www.bitesizedjapan.com
 >> Stay informed about: Any SQL experts here? 

This message is not archived
 >> Stay informed about: Any SQL experts here?