best way to have a unique key

Due to certain reasons, the company I am doing business with has decided
that the primary key, for an orders table, be a unique key; however, I
don't like the possibility of it conflicting if moved to another machine.

What are some pitfalls of using a unique key, that is generated by a
server side script, rather than by mysql?
What are the best ways to do this?

Please keep in mind this variable will also be displayed on the
customer's Receipt, but again, since it's random, it doesn't have to
mean anything.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

--000e0cd5f302285d01049a4b86ec
Content-Type: text/plain; charset=ISO-8859-1

uuid()

Krishna

On Fri, Jan 21, 2011 at 12:02 AM, Anthony Pace wrote:

> Due to certain reasons, the company I am doing business with has decided
> that the primary key, for an orders table, be a unique key; however, I don't
> like the possibility of it conflicting if moved to another machine.
>
> What are some pitfalls of using a unique key, that is generated by a server
> side script, rather than by mysql?
> What are the best ways to do this?
>
> Please keep in mind this variable will also be displayed on the customer's
> Receipt, but again, since it's random, it doesn't have to mean anything.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
>
>

--000e0cd5f302285d01049a4b86ec--

Re: best way to have a unique key

Dude, come on. I know that all primary keys have to be unique; however,
I was obviously referring to the use of uuid over auto incrementation.

On 1/20/2011 1:36 PM, Michael Dykman wrote:
> It is axiomatic in the relational model that a primary must be unique.
> This is not a quirk put forth by your current employer. Neither
> MySQL nor any other RDBMS will allow you to establish a primary key
> that is not unique.
>
> - michael dykman
>
> On Thu, Jan 20, 2011 at 1:32 PM, Anthony Pace wrote:
>> Due to certain reasons, the company I am doing business with has decided
>> that the primary key, for an orders table, be a unique key; however, I don't
>> like the possibility of it conflicting if moved to another machine.
>>
>> What are some pitfalls of using a unique key, that is generated by a server
>> side script, rather than by mysql?
>> What are the best ways to do this?
>>
>> Please keep in mind this variable will also be displayed on the customer's
>> Receipt, but again, since it's random, it doesn't have to mean anything.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>
>>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

I know of uuid() my problem is that there can be conflicts when copying
the DB to a different machine, or working with sections of the db on
different machines for load balancing.

On 1/20/2011 1:44 PM, Krishna Chandra Prajapati wrote:
> > Please keep in mind this variable will also be displayed on the customer's
> > Receipt, but again, since it's random, it doesn't have to mean anything.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

Although I did berate you for your obvious cheek, I will of course
complement the acuteness of your response.

On 1/20/2011 2:10 PM, Anthony Pace wrote:
> Dude, come on. I know that all primary keys have to be unique;
> however, I was obviously referring to the use of uuid over auto
> incrementation.
>
> On 1/20/2011 1:36 PM, Michael Dykman wrote:
>> It is axiomatic in the relational model that a primary must be unique.
>> This is not a quirk put forth by your current employer. Neither
>> MySQL nor any other RDBMS will allow you to establish a primary key
>> that is not unique.
>>
>> - michael dykman
>>
>> On Thu, Jan 20, 2011 at 1:32 PM, Anthony
>> Pace wrote:
>>> Due to certain reasons, the company I am doing business with has
>>> decided
>>> that the primary key, for an orders table, be a unique key; however,
>>> I don't
>>> like the possibility of it conflicting if moved to another machine.
>>>
>>> What are some pitfalls of using a unique key, that is generated by a
>>> server
>>> side script, rather than by mysql?
>>> What are the best ways to do this?
>>>
>>> Please keep in mind this variable will also be displayed on the
>>> customer's
>>> Receipt, but again, since it's random, it doesn't have to mean
>>> anything.
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>>
>>>
>>
>>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What conflicts are you expecting? according to the documentation:

A UUID is designed as a number that is globally unique in space and
time. Two calls to UUID() are expected to generate two different
values, even if these calls are performed on two separate computers
that are not connected to each other.

On 11-01-20 14:11, Anthony Pace wrote:
> I know of uuid() my problem is that there can be conflicts when copying
> the DB to a different machine, or working with sections of the db on
> different machines for load balancing.
>
> On 1/20/2011 1:44 PM, Krishna Chandra Prajapati wrote:
>> > Please keep in mind this variable will also be displayed on the
>> customer's
>> > Receipt, but again, since it's random, it doesn't have to mean
>> anything.
>
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.14 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJNOJQBAAoJENgwSj9ZOOwr7NcIAJUVGGPhtiRRrNjrSUBZ iO4c
0tsAKbsugnlJ7EI/61ALD8UCelBKKvDnRD0MFfCLHLTukbPkF+4YmwAi1tOM Vo0J
OteGOxXroo0dZhKt6/SommvtM9uXnHT6WJiTs8w5uP/TEUmqIECp4x3M0Fwj s9HY
HzV/Tqo/pqlBpbdagahm+pm+9mK+g5AYR7xenBXwynu05XqClUCptSdh6NIh nBD5
fLw9e6AVOAeG1sbswR51pFtuDpXT0IlHn3U/7rdIioglYakphT21MQ5oM2ku Tuis
LE3xAR/YydiKa9GUPfghR/+0Xp7DGes1+HAXq4dkmnSWFEw218Jt5y6r131/ EPg=
=fra3
-----END PGP SIGNATURE-----


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

I should have read more carefully.. I apologize for my snap response.

At a guess: as I recall, under M$ SQLServer the typical (only?) form
of unique identifier used is something very UUID-like. MY information
might be dated. I was certified as a SQL Server administrator perhaps
12 years agoI would not be terribly surprised to learn that was their
motivation. I recall, many years ago, having this same, largely
theoretical concern regarding MD5 hashes. Since then experience has
taught me that my worries were unfounded. UUID() should be very safe,
even for very large datasets.

- michael dykman


On Thu, Jan 20, 2011 at 2:20 PM, Anthony Pace wr=
ote:
> Although I did berate you for your obvious cheek, I will of course
> complement the acuteness of your response.
>
> On 1/20/2011 2:10 PM, Anthony Pace wrote:
>>
>> Dude, come on. =A0I know that all primary keys have to be unique; howeve=
r, I
>> was obviously referring to the use of uuid over auto incrementation.
>>
>> On 1/20/2011 1:36 PM, Michael Dykman wrote:
>>>
>>> It is axiomatic in the relational model that a primary must be unique.
>>> =A0This is not a quirk put forth by your current employer. =A0Neither
>>> MySQL nor any other RDBMS will allow you to establish a primary key
>>> that is not unique.
>>>
>>> =A0- michael dykman
>>>
>>> On Thu, Jan 20, 2011 at 1:32 PM, Anthony Pace
>>> =A0wrote:
>>>>
>>>> Due to certain reasons, the company I am doing business with has decid=
ed
>>>> that the primary key, for an orders table, be a unique key; however, I
>>>> don't
>>>> like the possibility of it conflicting if moved to another machine.
>>>>
>>>> What are some pitfalls of using a unique key, that is generated by a
>>>> server
>>>> side script, rather than by mysql?
>>>> What are the best ways to do this?
>>>>
>>>> Please keep in mind this variable will also be displayed on the
>>>> customer's
>>>> Receipt, but again, since it's random, it doesn't have to mean anythin=
g.
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gm=
ail.com
>>>>
>>>>
>>>
>>>
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: best way to have a unique key

http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-no t-to-uuid/

> -----Original Message-----
> From: Krishna Chandra Prajapati [mailto:prajapatikc@gmail.com]
> Sent: Thursday, January 20, 2011 10:45 AM
> To: Anthony Pace
> Cc: mysql.
> Subject: Re: best way to have a unique key
>
> uuid()
>
> Krishna
>
> On Fri, Jan 21, 2011 at 12:02 AM, Anthony Pace
> wrote:
>
> > Due to certain reasons, the company I am doing business
> with has decided
> > that the primary key, for an orders table, be a unique key;
> however, I don't
> > like the possibility of it conflicting if moved to another machine.
> >
> > What are some pitfalls of using a unique key, that is
> generated by a server
> > side script, rather than by mysql?
> > What are the best ways to do this?
> >
> > Please keep in mind this variable will also be displayed on
> the customer's
> > Receipt, but again, since it's random, it doesn't have to
> mean anything.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
> >
> >
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

--0015174c1c823e9d72049a5545d3
Content-Type: text/plain; charset=ISO-8859-1

I have to say, something similar was my first thought, too - you never
mention uuid in your original post. As already stated, uuid() should be a
Universal Unique IDentifier. It's afaik a random 128-bit number; given the
space to choose from it should be rather unique. I have to admit that I'm
not entirely confident about that myself, either, though: as Pratchett put
it, one-in-a-million chances tend to pop up nine times out of ten.

The code should have bits for handling duplicate primaries regardless of the
method used to generate it, tough, so there's no reason to not do it. Having
two subsequent UUID() calls generate pre-existing numbers seems to me to be
likely in the same way as having Bush return his dirty oil dollars to Irak.

On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace wrote:

> Dude, come on. I know that all primary keys have to be unique; however, I
> was obviously referring to the use of uuid over auto incrementation.
>
> On 1/20/2011 1:36 PM, Michael Dykman wrote:
>
>> It is axiomatic in the relational model that a primary must be unique.
>> This is not a quirk put forth by your current employer. Neither
>> MySQL nor any other RDBMS will allow you to establish a primary key
>> that is not unique.
>>
>> - michael dykman
>>
>> On Thu, Jan 20, 2011 at 1:32 PM, Anthony Pace
>> wrote:
>>
>>> Due to certain reasons, the company I am doing business with has decided
>>> that the primary key, for an orders table, be a unique key; however, I
>>> don't
>>> like the possibility of it conflicting if moved to another machine.
>>>
>>> What are some pitfalls of using a unique key, that is generated by a
>>> server
>>> side script, rather than by mysql?
>>> What are the best ways to do this?
>>>
>>> Please keep in mind this variable will also be displayed on the
>>> customer's
>>> Receipt, but again, since it's random, it doesn't have to mean anything.
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>>
>>>
>>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0015174c1c823e9d72049a5545d3--

RE: best way to have a unique key

>-----Original Message-----
>From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De
>Meersman
>Sent: Friday, January 21, 2011 1:22 AM
>To: Anthony Pace
>Cc: Michael Dykman; mysql.
>Subject: Re: best way to have a unique key
>
>I have to say, something similar was my first thought, too - you never
>mention uuid in your original post. As already stated, uuid() should be a
>Universal Unique IDentifier. It's afaik a random 128-bit number; given the
>space to choose from it should be rather unique. I have to admit that I'm
>not entirely confident about that myself, either, though: as Pratchett put
>it, one-in-a-million chances tend to pop up nine times out of ten.
>
[JS] A UUID (what Microsoft calls a GUID) is based in part on the MAC address
of the generating device. Since MAC addresses are supposed to be unique across
the known universe, so should a UUID.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>The code should have bits for handling duplicate primaries regardless of the
>method used to generate it, tough, so there's no reason to not do it. Having
>two subsequent UUID() calls generate pre-existing numbers seems to me to be
>likely in the same way as having Bush return his dirty oil dollars to Irak.
>
>On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace
>wrote:
>
>> Dude, come on. I know that all primary keys have to be unique; however, I
>> was obviously referring to the use of uuid over auto incrementation.
>>
>> On 1/20/2011 1:36 PM, Michael Dykman wrote:
>>
>>> It is axiomatic in the relational model that a primary must be unique.
>>> This is not a quirk put forth by your current employer. Neither
>>> MySQL nor any other RDBMS will allow you to establish a primary key
>>> that is not unique.
>>>
>>> - michael dykman
>>>
>>> On Thu, Jan 20, 2011 at 1:32 PM, Anthony Pace
>>> wrote:
>>>
>>>> Due to certain reasons, the company I am doing business with has decided
>>>> that the primary key, for an orders table, be a unique key; however, I
>>>> don't
>>>> like the possibility of it conflicting if moved to another machine.
>>>>
>>>> What are some pitfalls of using a unique key, that is generated by a
>>>> server
>>>> side script, rather than by mysql?
>>>> What are the best ways to do this?
>>>>
>>>> Please keep in mind this variable will also be displayed on the
>>>> customer's
>>>> Receipt, but again, since it's random, it doesn't have to mean anything.
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>>>
>>>>
>>>>
>>>
>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>>
>>
>
>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

One of the components of the UUID is drawn form the mac address of the
server.. While in practice this is not true of all systems

(except from http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functio ns=
..html#function_uuid)
Currently, the MAC address of an interface is taken into
account only on FreeBSD and Linux. On other operating systems, MySQL
uses a randomly generated 48-bit number.
(end except)

that potentially 48-bit random portion has 281474976710656
possibilities, which makes in far, far more likely that your server is
hit by a meteor during a snowstorm in August while the Dalai Lama is
doing an Elvis impression for the Chinese premier.

- michael dykman

On Fri, Jan 21, 2011 at 1:22 AM, Johan De Meersman wro=
te:
> I have to say, something similar was my first thought, too - you never
> mention uuid in your original post. As already stated, uuid() should be a
> Universal Unique IDentifier. It's afaik a random 128-bit number; given th=
e
> space to choose from it should be rather unique. I have to admit that I'm
> not entirely confident about that myself, either, though: as Pratchett pu=
t
> it, one-in-a-million chances tend to pop up nine times out of ten.
>
> The code should have bits for handling duplicate primaries regardless of =
the
> method used to generate it, tough, so there's no reason to not do it. Hav=
ing
> two subsequent UUID() calls generate pre-existing numbers seems to me to =
be
> likely in the same way as having Bush return his dirty oil dollars to Ira=
k.
>
> On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace w=
rote:
>
>> Dude, come on. =A0I know that all primary keys have to be unique; howeve=
r, I
>> was obviously referring to the use of uuid over auto incrementation.
>>
>> On 1/20/2011 1:36 PM, Michael Dykman wrote:
>>
>>> It is axiomatic in the relational model that a primary must be unique.
>>> =A0This is not a quirk put forth by your current employer. =A0Neither
>>> MySQL nor any other RDBMS will allow you to establish a primary key
>>> that is not unique.
>>>
>>> =A0- michael dykman
>>>
>>> On Thu, Jan 20, 2011 at 1:32 PM, Anthony Pace
>>> =A0wrote:
>>>
>>>> Due to certain reasons, the company I am doing business with has decid=
ed
>>>> that the primary key, for an orders table, be a unique key; however, I
>>>> don't
>>>> like the possibility of it conflicting if moved to another machine.
>>>>
>>>> What are some pitfalls of using a unique key, that is generated by a
>>>> server
>>>> side script, rather than by mysql?
>>>> What are the best ways to do this?
>>>>
>>>> Please keep in mind this variable will also be displayed on the
>>>> customer's
>>>> Receipt, but again, since it's random, it doesn't have to mean anythin=
g.
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gm=
ail.com
>>>>
>>>>
>>>>
>>>
>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dvegivamp@tux=
era.be
>>
>>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: best way to have a unique key

>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@gmail.com]
>Sent: Friday, January 21, 2011 11:35 AM
>To: Johan De Meersman
>Cc: Anthony Pace; mysql.
>Subject: Re: best way to have a unique key
>
>One of the components of the UUID is drawn form the mac address of the
>server.. While in practice this is not true of all systems
>
>(except from http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-
>functions.html#function_uuid)
> Currently, the MAC address of an interface is taken into
>account only on FreeBSD and Linux. On other operating systems, MySQL
>uses a randomly generated 48-bit number.
>(end except)
>
>that potentially 48-bit random portion has 281474976710656
>possibilities, which makes in far, far more likely that your server is
>hit by a meteor during a snowstorm in August while the Dalai Lama is
>doing an Elvis impression for the Chinese premier.
>
[JS] Boy, if I had a nickel for every time I've heard that...

Seriously, though I've written and used enough pseudo-random number generators
to have a tiny nagging doubt. I didn't realize that MySQL didn't use the MAC
address on Windows platforms.

Also, it is possible to programmatically change a MAC address (for system
failover, for example).

If you're not the kind of person who stays up nights worrying about proton
decay, you're probably justified in shrugging this off. You'll likely be
collecting your pension before anything nasty happens.


> - michael dykman
>
>On Fri, Jan 21, 2011 at 1:22 AM, Johan De Meersman
>wrote:
>> I have to say, something similar was my first thought, too - you never
>> mention uuid in your original post. As already stated, uuid() should be a
>> Universal Unique IDentifier. It's afaik a random 128-bit number; given the
>> space to choose from it should be rather unique. I have to admit that I'm
>> not entirely confident about that myself, either, though: as Pratchett put
>> it, one-in-a-million chances tend to pop up nine times out of ten.
>>
>> The code should have bits for handling duplicate primaries regardless of
>> the
>> method used to generate it, tough, so there's no reason to not do it.
>> Having
>> two subsequent UUID() calls generate pre-existing numbers seems to me to be
>> likely in the same way as having Bush return his dirty oil dollars to Irak.
>>
>> On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace
>wrote:
>>
>>> Dude, come on. I know that all primary keys have to be unique; however, I
>>> was obviously referring to the use of uuid over auto incrementation.
>>>
>>> On 1/20/2011 1:36 PM, Michael Dykman wrote:
>>>
>>>> It is axiomatic in the relational model that a primary must be unique.
>>>> This is not a quirk put forth by your current employer. Neither
>>>> MySQL nor any other RDBMS will allow you to establish a primary key
>>>> that is not unique.
>>>>
>>>> - michael dykman
>>>>
>>>> On Thu, Jan 20, 2011 at 1:32 PM, Anthony Pace
>>>> wrote:
>>>>
>>>>> Due to certain reasons, the company I am doing business with has decided
>>>>> that the primary key, for an orders table, be a unique key; however, I
>>>>> don't
>>>>> like the possibility of it conflicting if moved to another machine.
>>>>>
>>>>> What are some pitfalls of using a unique key, that is generated by a
>>>>> server
>>>>> side script, rather than by mysql?
>>>>> What are the best ways to do this?
>>>>>
>>>>> Please keep in mind this variable will also be displayed on the
>>>>> customer's
>>>>> Receipt, but again, since it's random, it doesn't have to mean anything.
>>>>>
>>>>> --
>>>>> MySQL General Mailing List
>>>>> For list archives: http://lists.mysql.com/mysql
>>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>>>
>>>
>>
>>
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>
>
>
>--
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: best way to have a unique key

On Friday, January 21, 2011 09:23:47 am Jerry Schwartz wrote:

>
> [JS] A UUID (what Microsoft calls a GUID) is based in part on the MAC
> address of the generating device. Since MAC addresses are supposed to be
> unique across the known universe, so should a UUID.
>

Not entirely true - and even when true your conclusion doesn't follow.

I'll be the first to admit that we are straining at gnats, here. Generating a
UUID does not require a machine to have an Ethernet card. The presence of a
Mac address is, therefore, not guaranteed. Even if the machine does have an
ethernet card, the definition of the UUID suggests - but does not require -
that it be used (I believe this is discussed in another reply). Even if it is
used, there are still a (admittedly large) portion of it that is created by a
random number generation. Assuming a reasonably good generator, it is still
possible for a collision on a single machine.

The question then becomes, how much of a problem is that? The odds are that
I'll be LONG gone before that happens. But it could happen tomorrow. If it's
just a matter of "Oops, lets do a rollback and go again", who cares? If it's a
life sustaining mission critical problem where an atomic war starts if the
transaction fails ... you might want to at least think about another key.
Personally, I wouldn't lose sleep over it.

Another reasonable - and faster - source of a unique key would be to have a
human set unique server id on each machine generating the key. In that case a
two field primary key with the first being the server id and the second part
being an auto_increment field should guarantee a unique key - at least until it
wraps around. This, of course, assumes that the administrator that sets up the
servers doesn't make a mistake with setting the server id. Which brings up the
"how much of a problem would that be" question again.

Bottom line ... exactly how paranoid are you?

---Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org