Uniqueness in MySQL 'useraccounts'

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Uniqueness in MySQL 'useraccounts'

Kevin Buckley

I hope this is not covering old ground.  I’m playing with a member system for standalone OpenSim and I’m trying to decide the best way to deal with the FirstName/LastName uniqueness issue (as my sign-up process allows multiple, concurrent users).

 

I notice that the default useraccounts table has a composite key called Name (which consists of FirstName and LastName).  But it isn’t defined as a primary key.  So duplicate name inserts are not trapped as a result of that (I believe that is the reason, anyway).

 

Does anyone know if there is a reason ‘Name’ is not defined as a primary key?  Is there a problem with making it so?

 

I have other ways I can deal with this but trapping it at the useraccounts table insert would be the best way.

 

Thanks.


_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

M.E. Verhagen
the PrincipalID field is already the primary key.

There can be only one primary key.

This uuid key is the unique identifier on wich an avatar is idenified.

When you write you own member system with direct database manipulation
than you should check if the avatar is unique or else you will end up
with more than one avatar name attached to an uuid.



2012/3/4, Kevin Buckley <[hidden email]>:

> I hope this is not covering old ground.  I'm playing with a member system
> for standalone OpenSim and I'm trying to decide the best way to deal with
> the FirstName/LastName uniqueness issue (as my sign-up process allows
> multiple, concurrent users).
>
>
>
> I notice that the default useraccounts table has a composite key called Name
> (which consists of FirstName and LastName).  But it isn't defined as a
> primary key.  So duplicate name inserts are not trapped as a result of that
> (I believe that is the reason, anyway).
>
>
>
> Does anyone know if there is a reason 'Name' is not defined as a primary
> key?  Is there a problem with making it so?
>
>
>
> I have other ways I can deal with this but trapping it at the useraccounts
> table insert would be the best way.
>
>
>
> Thanks.
>
>


--
Groningen en Hannover Opensims:   secondlife://meverhagen.nl:8002:Hannover
ZW/
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

Kevin Buckley
Hi - Thanks for the quick response.

I understand the reasons why PrincipalID is the Primary Key and hence should
be unique (and I now realise my original question was malformed).

The actual question is: Why isn't 'Name' (the composite key including
FirstName and LastName) defined as Unique?

Defining it as Unique certainly works and causes MySQL to throw Error 1062
in response to an attempt to create  a duplicate FirstName/LastName entry
which is the (my) required behaviour.

So I am wondering why 'Name' isn't defined as Unique when the database
tables are created (or upgraded) by Opensim, given that it goes to the
trouble of defining it in the first place!

Thanks.

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of M.E. Verhagen
Sent: 04 March 2012 23:42
To: [hidden email]
Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'

the PrincipalID field is already the primary key.

There can be only one primary key.

This uuid key is the unique identifier on wich an avatar is idenified.

When you write you own member system with direct database manipulation
than you should check if the avatar is unique or else you will end up
with more than one avatar name attached to an uuid.



2012/3/4, Kevin Buckley <[hidden email]>:
> I hope this is not covering old ground.  I'm playing with a member system
> for standalone OpenSim and I'm trying to decide the best way to deal with
> the FirstName/LastName uniqueness issue (as my sign-up process allows
> multiple, concurrent users).
>
>
>
> I notice that the default useraccounts table has a composite key called
Name
> (which consists of FirstName and LastName).  But it isn't defined as a
> primary key.  So duplicate name inserts are not trapped as a result of
that

> (I believe that is the reason, anyway).
>
>
>
> Does anyone know if there is a reason 'Name' is not defined as a primary
> key?  Is there a problem with making it so?
>
>
>
> I have other ways I can deal with this but trapping it at the useraccounts
> table insert would be the best way.
>
>
>
> Thanks.
>
>


--
Groningen en Hannover Opensims:   secondlife://meverhagen.nl:8002:Hannover
ZW/
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

M.E. Verhagen
I think it is oki when you change the combined name index it to unique.

I do not think there is a reason for it why it should not to be unique.





_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

Karen Palen
In reply to this post by Kevin Buckley
I suspect the answer is that you need more information than just those
two fields.

I am thinking of the fairly common case in Hypergrid usage where two
avatars have the same name, distinguished only by the field that tells
their host system.

I have used this for testing on many occasions.

Karen

On 03/05/2012 04:45 AM, Kevin Buckley wrote:

> Hi - Thanks for the quick response.
>
> I understand the reasons why PrincipalID is the Primary Key and hence should
> be unique (and I now realise my original question was malformed).
>
> The actual question is: Why isn't 'Name' (the composite key including
> FirstName and LastName) defined as Unique?
>
> Defining it as Unique certainly works and causes MySQL to throw Error 1062
> in response to an attempt to create  a duplicate FirstName/LastName entry
> which is the (my) required behaviour.
>
> So I am wondering why 'Name' isn't defined as Unique when the database
> tables are created (or upgraded) by Opensim, given that it goes to the
> trouble of defining it in the first place!
>
> Thanks.
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of M.E. Verhagen
> Sent: 04 March 2012 23:42
> To: [hidden email]
> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>
> the PrincipalID field is already the primary key.
>
> There can be only one primary key.
>
> This uuid key is the unique identifier on wich an avatar is idenified.
>
> When you write you own member system with direct database manipulation
> than you should check if the avatar is unique or else you will end up
> with more than one avatar name attached to an uuid.
>
>
>
> 2012/3/4, Kevin Buckley <[hidden email]>:
>> I hope this is not covering old ground.  I'm playing with a member system
>> for standalone OpenSim and I'm trying to decide the best way to deal with
>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>> multiple, concurrent users).
>>
>>
>>
>> I notice that the default useraccounts table has a composite key called
> Name
>> (which consists of FirstName and LastName).  But it isn't defined as a
>> primary key.  So duplicate name inserts are not trapped as a result of
> that
>> (I believe that is the reason, anyway).
>>
>>
>>
>> Does anyone know if there is a reason 'Name' is not defined as a primary
>> key?  Is there a problem with making it so?
>>
>>
>>
>> I have other ways I can deal with this but trapping it at the useraccounts
>> table insert would be the best way.
>>
>>
>>
>> Thanks.
>>
>>
>
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

M.E. Verhagen
hypergrid visiters are not stored in the useraccounts table.

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

Kevin Buckley
In reply to this post by Karen Palen
Hi Karen

As far as I understand (or can guess!), the concept is that the AV UID
(PrincipalID) and asset UID's are created by using a timestamp variant of
UUID (so I'm using the one provided conveniently by MySQL which provides
temporal separation plus 48 bit random, spatial separation).  So that takes
care of the nuts and bolts.

And yes I can see that, in the hypergrid case, there is the issue of names
which are unique within each OS implementation (which I'm trying to deal
with now) but not able to be guaranteed unique everywhere as they are chosen
by people who are not known for their randomness.  I am assuming that the
combination of locally enforced, unique FirstName+LastName plus host ID,
provides a globally unique name scheme even in the hypergrid scenario.

Is that correct?

Is the host ID you referred to, the ServiceURL field in the same table
(useraccounts)?

Kevin

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Karen Palen
Sent: 05 March 2012 16:10
To: [hidden email]
Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'

I suspect the answer is that you need more information than just those
two fields.

I am thinking of the fairly common case in Hypergrid usage where two
avatars have the same name, distinguished only by the field that tells
their host system.

I have used this for testing on many occasions.

Karen

On 03/05/2012 04:45 AM, Kevin Buckley wrote:
> Hi - Thanks for the quick response.
>
> I understand the reasons why PrincipalID is the Primary Key and hence
should

> be unique (and I now realise my original question was malformed).
>
> The actual question is: Why isn't 'Name' (the composite key including
> FirstName and LastName) defined as Unique?
>
> Defining it as Unique certainly works and causes MySQL to throw Error 1062
> in response to an attempt to create  a duplicate FirstName/LastName entry
> which is the (my) required behaviour.
>
> So I am wondering why 'Name' isn't defined as Unique when the database
> tables are created (or upgraded) by Opensim, given that it goes to the
> trouble of defining it in the first place!
>
> Thanks.
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of M.E. Verhagen
> Sent: 04 March 2012 23:42
> To: [hidden email]
> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>
> the PrincipalID field is already the primary key.
>
> There can be only one primary key.
>
> This uuid key is the unique identifier on wich an avatar is idenified.
>
> When you write you own member system with direct database manipulation
> than you should check if the avatar is unique or else you will end up
> with more than one avatar name attached to an uuid.
>
>
>
> 2012/3/4, Kevin Buckley <[hidden email]>:
>> I hope this is not covering old ground.  I'm playing with a member system
>> for standalone OpenSim and I'm trying to decide the best way to deal with
>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>> multiple, concurrent users).
>>
>>
>>
>> I notice that the default useraccounts table has a composite key called
> Name
>> (which consists of FirstName and LastName).  But it isn't defined as a
>> primary key.  So duplicate name inserts are not trapped as a result of
> that
>> (I believe that is the reason, anyway).
>>
>>
>>
>> Does anyone know if there is a reason 'Name' is not defined as a primary
>> key?  Is there a problem with making it so?
>>
>>
>>
>> I have other ways I can deal with this but trapping it at the
useraccounts
>> table insert would be the best way.
>>
>>
>>
>> Thanks.
>>
>>
>
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

Karen Palen
As was pointed out, hypergrid avatar names are not stored in the
database, however this is only one possible source of confusion or
duplication if only FirstName + LastName are used.

As far as I know, the only reliable identifier of an avatar is the UUID
in the database, but that is entirely local! The UUID for example is
used to identify the Creator and Owner of objects for permissions.

My understanding is that you are correct in identifying unique hypergrid
IDs, but uniquely identifying a hypergrid visitor is an area where much
work is being done. This is important when assigning permissions to
objects created/owned by a visiting Avatar for example.  

I have not looked at this for nearly a year now (i.e. several OpenSim
revisions!) so there are almost certainly things that are different now.

I have always found that a few test cases combined with inspection of
associated the database entries via an SQL manager  package is the
easiest and fastest way to nail down exactly what is happening (as a
Linux hacker I like MYSQL Admin, but it is only one of many). This
process is aided greatly by using a Diva distro to create a "one of"
grid with only the database entries of interest.

Karen

On 03/05/2012 09:54 AM, Kevin Buckley wrote:

> Hi Karen
>
> As far as I understand (or can guess!), the concept is that the AV UID
> (PrincipalID) and asset UID's are created by using a timestamp variant of
> UUID (so I'm using the one provided conveniently by MySQL which provides
> temporal separation plus 48 bit random, spatial separation).  So that takes
> care of the nuts and bolts.
>
> And yes I can see that, in the hypergrid case, there is the issue of names
> which are unique within each OS implementation (which I'm trying to deal
> with now) but not able to be guaranteed unique everywhere as they are chosen
> by people who are not known for their randomness.  I am assuming that the
> combination of locally enforced, unique FirstName+LastName plus host ID,
> provides a globally unique name scheme even in the hypergrid scenario.
>
> Is that correct?
>
> Is the host ID you referred to, the ServiceURL field in the same table
> (useraccounts)?
>
> Kevin
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Karen Palen
> Sent: 05 March 2012 16:10
> To: [hidden email]
> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>
> I suspect the answer is that you need more information than just those
> two fields.
>
> I am thinking of the fairly common case in Hypergrid usage where two
> avatars have the same name, distinguished only by the field that tells
> their host system.
>
> I have used this for testing on many occasions.
>
> Karen
>
> On 03/05/2012 04:45 AM, Kevin Buckley wrote:
>> Hi - Thanks for the quick response.
>>
>> I understand the reasons why PrincipalID is the Primary Key and hence
> should
>> be unique (and I now realise my original question was malformed).
>>
>> The actual question is: Why isn't 'Name' (the composite key including
>> FirstName and LastName) defined as Unique?
>>
>> Defining it as Unique certainly works and causes MySQL to throw Error 1062
>> in response to an attempt to create  a duplicate FirstName/LastName entry
>> which is the (my) required behaviour.
>>
>> So I am wondering why 'Name' isn't defined as Unique when the database
>> tables are created (or upgraded) by Opensim, given that it goes to the
>> trouble of defining it in the first place!
>>
>> Thanks.
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On Behalf Of M.E. Verhagen
>> Sent: 04 March 2012 23:42
>> To: [hidden email]
>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>
>> the PrincipalID field is already the primary key.
>>
>> There can be only one primary key.
>>
>> This uuid key is the unique identifier on wich an avatar is idenified.
>>
>> When you write you own member system with direct database manipulation
>> than you should check if the avatar is unique or else you will end up
>> with more than one avatar name attached to an uuid.
>>
>>
>>
>> 2012/3/4, Kevin Buckley <[hidden email]>:
>>> I hope this is not covering old ground.  I'm playing with a member system
>>> for standalone OpenSim and I'm trying to decide the best way to deal with
>>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>>> multiple, concurrent users).
>>>
>>>
>>>
>>> I notice that the default useraccounts table has a composite key called
>> Name
>>> (which consists of FirstName and LastName).  But it isn't defined as a
>>> primary key.  So duplicate name inserts are not trapped as a result of
>> that
>>> (I believe that is the reason, anyway).
>>>
>>>
>>>
>>> Does anyone know if there is a reason 'Name' is not defined as a primary
>>> key?  Is there a problem with making it so?
>>>
>>>
>>>
>>> I have other ways I can deal with this but trapping it at the
> useraccounts
>>> table insert would be the best way.
>>>
>>>
>>>
>>> Thanks.
>>>
>>>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

M.E. Verhagen
we seem to drifting a bit off from sign up prossesses to identifying hg visitors

anyhow the first and lastname belog together, in a signup proces the
uniqueness test should be there.

and since duplicate combi's of the first and last names are not
allowed on the same local grid the check can be performed by mysql by
changing the name index (wich is already there) to unique.




2012/3/5, Karen Palen <[hidden email]>:

> As was pointed out, hypergrid avatar names are not stored in the
> database, however this is only one possible source of confusion or
> duplication if only FirstName + LastName are used.
>
> As far as I know, the only reliable identifier of an avatar is the UUID
> in the database, but that is entirely local! The UUID for example is
> used to identify the Creator and Owner of objects for permissions.
>
> My understanding is that you are correct in identifying unique hypergrid
> IDs, but uniquely identifying a hypergrid visitor is an area where much
> work is being done. This is important when assigning permissions to
> objects created/owned by a visiting Avatar for example.
>
> I have not looked at this for nearly a year now (i.e. several OpenSim
> revisions!) so there are almost certainly things that are different now.
>
> I have always found that a few test cases combined with inspection of
> associated the database entries via an SQL manager  package is the
> easiest and fastest way to nail down exactly what is happening (as a
> Linux hacker I like MYSQL Admin, but it is only one of many). This
> process is aided greatly by using a Diva distro to create a "one of"
> grid with only the database entries of interest.
>
> Karen
>
> On 03/05/2012 09:54 AM, Kevin Buckley wrote:
>> Hi Karen
>>
>> As far as I understand (or can guess!), the concept is that the AV UID
>> (PrincipalID) and asset UID's are created by using a timestamp variant of
>> UUID (so I'm using the one provided conveniently by MySQL which provides
>> temporal separation plus 48 bit random, spatial separation).  So that
>> takes
>> care of the nuts and bolts.
>>
>> And yes I can see that, in the hypergrid case, there is the issue of names
>> which are unique within each OS implementation (which I'm trying to deal
>> with now) but not able to be guaranteed unique everywhere as they are
>> chosen
>> by people who are not known for their randomness.  I am assuming that the
>> combination of locally enforced, unique FirstName+LastName plus host ID,
>> provides a globally unique name scheme even in the hypergrid scenario.
>>
>> Is that correct?
>>
>> Is the host ID you referred to, the ServiceURL field in the same table
>> (useraccounts)?
>>
>> Kevin
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On Behalf Of Karen Palen
>> Sent: 05 March 2012 16:10
>> To: [hidden email]
>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>
>> I suspect the answer is that you need more information than just those
>> two fields.
>>
>> I am thinking of the fairly common case in Hypergrid usage where two
>> avatars have the same name, distinguished only by the field that tells
>> their host system.
>>
>> I have used this for testing on many occasions.
>>
>> Karen
>>
>> On 03/05/2012 04:45 AM, Kevin Buckley wrote:
>>> Hi - Thanks for the quick response.
>>>
>>> I understand the reasons why PrincipalID is the Primary Key and hence
>> should
>>> be unique (and I now realise my original question was malformed).
>>>
>>> The actual question is: Why isn't 'Name' (the composite key including
>>> FirstName and LastName) defined as Unique?
>>>
>>> Defining it as Unique certainly works and causes MySQL to throw Error
>>> 1062
>>> in response to an attempt to create  a duplicate FirstName/LastName entry
>>> which is the (my) required behaviour.
>>>
>>> So I am wondering why 'Name' isn't defined as Unique when the database
>>> tables are created (or upgraded) by Opensim, given that it goes to the
>>> trouble of defining it in the first place!
>>>
>>> Thanks.
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:[hidden email]] On Behalf Of M.E.
>>> Verhagen
>>> Sent: 04 March 2012 23:42
>>> To: [hidden email]
>>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>>
>>> the PrincipalID field is already the primary key.
>>>
>>> There can be only one primary key.
>>>
>>> This uuid key is the unique identifier on wich an avatar is idenified.
>>>
>>> When you write you own member system with direct database manipulation
>>> than you should check if the avatar is unique or else you will end up
>>> with more than one avatar name attached to an uuid.
>>>
>>>
>>>
>>> 2012/3/4, Kevin Buckley <[hidden email]>:
>>>> I hope this is not covering old ground.  I'm playing with a member
>>>> system
>>>> for standalone OpenSim and I'm trying to decide the best way to deal
>>>> with
>>>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>>>> multiple, concurrent users).
>>>>
>>>>
>>>>
>>>> I notice that the default useraccounts table has a composite key called
>>> Name
>>>> (which consists of FirstName and LastName).  But it isn't defined as a
>>>> primary key.  So duplicate name inserts are not trapped as a result of
>>> that
>>>> (I believe that is the reason, anyway).
>>>>
>>>>
>>>>
>>>> Does anyone know if there is a reason 'Name' is not defined as a primary
>>>> key?  Is there a problem with making it so?
>>>>
>>>>
>>>>
>>>> I have other ways I can deal with this but trapping it at the
>> useraccounts
>>>> table insert would be the best way.
>>>>
>>>>
>>>>
>>>> Thanks.
>>>>
>>>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


--
Groningen en Hannover Opensims:   secondlife://meverhagen.nl:8002:Hannover
ZW/
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

Diva Canto
In reply to this post by Karen Palen
Users are uniquely identified with UUIDs. If you search out there,
you'll find the probability of UUID collisions, which is very small. But
that mathematical probability doesn't really matter compared to the high
probability that *people* will do all sorts of weird things regarding
UUID reuse, some by accident, some maliciously.

The Hypergrid has security in place for dealing with collisions of user
UUIDs. Basically if an HG visitor with a certain UUID comes into a grid
where there is a local user with the same UUID, that visitor is denied
entry.

On 3/5/2012 12:26 PM, Karen Palen wrote:

> As was pointed out, hypergrid avatar names are not stored in the
> database, however this is only one possible source of confusion or
> duplication if only FirstName + LastName are used.
>
> As far as I know, the only reliable identifier of an avatar is the UUID
> in the database, but that is entirely local! The UUID for example is
> used to identify the Creator and Owner of objects for permissions.
>
> My understanding is that you are correct in identifying unique hypergrid
> IDs, but uniquely identifying a hypergrid visitor is an area where much
> work is being done. This is important when assigning permissions to
> objects created/owned by a visiting Avatar for example.
>
> I have not looked at this for nearly a year now (i.e. several OpenSim
> revisions!) so there are almost certainly things that are different now.
>
> I have always found that a few test cases combined with inspection of
> associated the database entries via an SQL manager  package is the
> easiest and fastest way to nail down exactly what is happening (as a
> Linux hacker I like MYSQL Admin, but it is only one of many). This
> process is aided greatly by using a Diva distro to create a "one of"
> grid with only the database entries of interest.
>
> Karen
>
> On 03/05/2012 09:54 AM, Kevin Buckley wrote:
>> Hi Karen
>>
>> As far as I understand (or can guess!), the concept is that the AV UID
>> (PrincipalID) and asset UID's are created by using a timestamp variant of
>> UUID (so I'm using the one provided conveniently by MySQL which provides
>> temporal separation plus 48 bit random, spatial separation).  So that takes
>> care of the nuts and bolts.
>>
>> And yes I can see that, in the hypergrid case, there is the issue of names
>> which are unique within each OS implementation (which I'm trying to deal
>> with now) but not able to be guaranteed unique everywhere as they are chosen
>> by people who are not known for their randomness.  I am assuming that the
>> combination of locally enforced, unique FirstName+LastName plus host ID,
>> provides a globally unique name scheme even in the hypergrid scenario.
>>
>> Is that correct?
>>
>> Is the host ID you referred to, the ServiceURL field in the same table
>> (useraccounts)?
>>
>> Kevin
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On Behalf Of Karen Palen
>> Sent: 05 March 2012 16:10
>> To: [hidden email]
>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>
>> I suspect the answer is that you need more information than just those
>> two fields.
>>
>> I am thinking of the fairly common case in Hypergrid usage where two
>> avatars have the same name, distinguished only by the field that tells
>> their host system.
>>
>> I have used this for testing on many occasions.
>>
>> Karen
>>
>> On 03/05/2012 04:45 AM, Kevin Buckley wrote:
>>> Hi - Thanks for the quick response.
>>>
>>> I understand the reasons why PrincipalID is the Primary Key and hence
>> should
>>> be unique (and I now realise my original question was malformed).
>>>
>>> The actual question is: Why isn't 'Name' (the composite key including
>>> FirstName and LastName) defined as Unique?
>>>
>>> Defining it as Unique certainly works and causes MySQL to throw Error 1062
>>> in response to an attempt to create  a duplicate FirstName/LastName entry
>>> which is the (my) required behaviour.
>>>
>>> So I am wondering why 'Name' isn't defined as Unique when the database
>>> tables are created (or upgraded) by Opensim, given that it goes to the
>>> trouble of defining it in the first place!
>>>
>>> Thanks.
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:[hidden email]] On Behalf Of M.E. Verhagen
>>> Sent: 04 March 2012 23:42
>>> To: [hidden email]
>>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>>
>>> the PrincipalID field is already the primary key.
>>>
>>> There can be only one primary key.
>>>
>>> This uuid key is the unique identifier on wich an avatar is idenified.
>>>
>>> When you write you own member system with direct database manipulation
>>> than you should check if the avatar is unique or else you will end up
>>> with more than one avatar name attached to an uuid.
>>>
>>>
>>>
>>> 2012/3/4, Kevin Buckley<[hidden email]>:
>>>> I hope this is not covering old ground.  I'm playing with a member system
>>>> for standalone OpenSim and I'm trying to decide the best way to deal with
>>>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>>>> multiple, concurrent users).
>>>>
>>>>
>>>>
>>>> I notice that the default useraccounts table has a composite key called
>>> Name
>>>> (which consists of FirstName and LastName).  But it isn't defined as a
>>>> primary key.  So duplicate name inserts are not trapped as a result of
>>> that
>>>> (I believe that is the reason, anyway).
>>>>
>>>>
>>>>
>>>> Does anyone know if there is a reason 'Name' is not defined as a primary
>>>> key?  Is there a problem with making it so?
>>>>
>>>>
>>>>
>>>> I have other ways I can deal with this but trapping it at the
>> useraccounts
>>>> table insert would be the best way.
>>>>
>>>>
>>>>
>>>> Thanks.
>>>>
>>>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

Kevin Buckley
The original question was whether there was an issue with giving the
composite key 'Name' (in useraccounts) the UNIQUE attribute as that is
useful when implementing a concurrent sign-up system.

And I guess the next question would be: is it possible to make 'Name' UNIQUE
by default in the Opensim release?


-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Diva Canto
Sent: 06 March 2012 03:31
To: [hidden email]
Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'

Users are uniquely identified with UUIDs. If you search out there,
you'll find the probability of UUID collisions, which is very small. But
that mathematical probability doesn't really matter compared to the high
probability that *people* will do all sorts of weird things regarding
UUID reuse, some by accident, some maliciously.

The Hypergrid has security in place for dealing with collisions of user
UUIDs. Basically if an HG visitor with a certain UUID comes into a grid
where there is a local user with the same UUID, that visitor is denied
entry.

On 3/5/2012 12:26 PM, Karen Palen wrote:

> As was pointed out, hypergrid avatar names are not stored in the
> database, however this is only one possible source of confusion or
> duplication if only FirstName + LastName are used.
>
> As far as I know, the only reliable identifier of an avatar is the UUID
> in the database, but that is entirely local! The UUID for example is
> used to identify the Creator and Owner of objects for permissions.
>
> My understanding is that you are correct in identifying unique hypergrid
> IDs, but uniquely identifying a hypergrid visitor is an area where much
> work is being done. This is important when assigning permissions to
> objects created/owned by a visiting Avatar for example.
>
> I have not looked at this for nearly a year now (i.e. several OpenSim
> revisions!) so there are almost certainly things that are different now.
>
> I have always found that a few test cases combined with inspection of
> associated the database entries via an SQL manager  package is the
> easiest and fastest way to nail down exactly what is happening (as a
> Linux hacker I like MYSQL Admin, but it is only one of many). This
> process is aided greatly by using a Diva distro to create a "one of"
> grid with only the database entries of interest.
>
> Karen
>
> On 03/05/2012 09:54 AM, Kevin Buckley wrote:
>> Hi Karen
>>
>> As far as I understand (or can guess!), the concept is that the AV UID
>> (PrincipalID) and asset UID's are created by using a timestamp variant of
>> UUID (so I'm using the one provided conveniently by MySQL which provides
>> temporal separation plus 48 bit random, spatial separation).  So that
takes
>> care of the nuts and bolts.
>>
>> And yes I can see that, in the hypergrid case, there is the issue of
names
>> which are unique within each OS implementation (which I'm trying to deal
>> with now) but not able to be guaranteed unique everywhere as they are
chosen

>> by people who are not known for their randomness.  I am assuming that the
>> combination of locally enforced, unique FirstName+LastName plus host ID,
>> provides a globally unique name scheme even in the hypergrid scenario.
>>
>> Is that correct?
>>
>> Is the host ID you referred to, the ServiceURL field in the same table
>> (useraccounts)?
>>
>> Kevin
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On Behalf Of Karen Palen
>> Sent: 05 March 2012 16:10
>> To: [hidden email]
>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>
>> I suspect the answer is that you need more information than just those
>> two fields.
>>
>> I am thinking of the fairly common case in Hypergrid usage where two
>> avatars have the same name, distinguished only by the field that tells
>> their host system.
>>
>> I have used this for testing on many occasions.
>>
>> Karen
>>
>> On 03/05/2012 04:45 AM, Kevin Buckley wrote:
>>> Hi - Thanks for the quick response.
>>>
>>> I understand the reasons why PrincipalID is the Primary Key and hence
>> should
>>> be unique (and I now realise my original question was malformed).
>>>
>>> The actual question is: Why isn't 'Name' (the composite key including
>>> FirstName and LastName) defined as Unique?
>>>
>>> Defining it as Unique certainly works and causes MySQL to throw Error
1062
>>> in response to an attempt to create  a duplicate FirstName/LastName
entry

>>> which is the (my) required behaviour.
>>>
>>> So I am wondering why 'Name' isn't defined as Unique when the database
>>> tables are created (or upgraded) by Opensim, given that it goes to the
>>> trouble of defining it in the first place!
>>>
>>> Thanks.
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:[hidden email]] On Behalf Of M.E.
Verhagen

>>> Sent: 04 March 2012 23:42
>>> To: [hidden email]
>>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>>
>>> the PrincipalID field is already the primary key.
>>>
>>> There can be only one primary key.
>>>
>>> This uuid key is the unique identifier on wich an avatar is idenified.
>>>
>>> When you write you own member system with direct database manipulation
>>> than you should check if the avatar is unique or else you will end up
>>> with more than one avatar name attached to an uuid.
>>>
>>>
>>>
>>> 2012/3/4, Kevin Buckley<[hidden email]>:
>>>> I hope this is not covering old ground.  I'm playing with a member
system
>>>> for standalone OpenSim and I'm trying to decide the best way to deal
with

>>>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>>>> multiple, concurrent users).
>>>>
>>>>
>>>>
>>>> I notice that the default useraccounts table has a composite key called
>>> Name
>>>> (which consists of FirstName and LastName).  But it isn't defined as a
>>>> primary key.  So duplicate name inserts are not trapped as a result of
>>> that
>>>> (I believe that is the reason, anyway).
>>>>
>>>>
>>>>
>>>> Does anyone know if there is a reason 'Name' is not defined as a
primary

>>>> key?  Is there a problem with making it so?
>>>>
>>>>
>>>>
>>>> I have other ways I can deal with this but trapping it at the
>> useraccounts
>>>> table insert would be the best way.
>>>>
>>>>
>>>>
>>>> Thanks.
>>>>
>>>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Uniqueness in MySQL 'useraccounts'

justincc
The problem here is the ScopeID, which I believe allows a useraccounts table to host users on different grids.  Two
users on different grids could have the same name.

I think this question is much better asked on the opensim-dev mailing list since not all developers subscribe to this list.

On 06/03/12 13:54, Kevin Buckley wrote:

> The original question was whether there was an issue with giving the
> composite key 'Name' (in useraccounts) the UNIQUE attribute as that is
> useful when implementing a concurrent sign-up system.
>
> And I guess the next question would be: is it possible to make 'Name' UNIQUE
> by default in the Opensim release?
>
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Diva Canto
> Sent: 06 March 2012 03:31
> To: [hidden email]
> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>
> Users are uniquely identified with UUIDs. If you search out there,
> you'll find the probability of UUID collisions, which is very small. But
> that mathematical probability doesn't really matter compared to the high
> probability that *people* will do all sorts of weird things regarding
> UUID reuse, some by accident, some maliciously.
>
> The Hypergrid has security in place for dealing with collisions of user
> UUIDs. Basically if an HG visitor with a certain UUID comes into a grid
> where there is a local user with the same UUID, that visitor is denied
> entry.
>
> On 3/5/2012 12:26 PM, Karen Palen wrote:
>> As was pointed out, hypergrid avatar names are not stored in the
>> database, however this is only one possible source of confusion or
>> duplication if only FirstName + LastName are used.
>>
>> As far as I know, the only reliable identifier of an avatar is the UUID
>> in the database, but that is entirely local! The UUID for example is
>> used to identify the Creator and Owner of objects for permissions.
>>
>> My understanding is that you are correct in identifying unique hypergrid
>> IDs, but uniquely identifying a hypergrid visitor is an area where much
>> work is being done. This is important when assigning permissions to
>> objects created/owned by a visiting Avatar for example.
>>
>> I have not looked at this for nearly a year now (i.e. several OpenSim
>> revisions!) so there are almost certainly things that are different now.
>>
>> I have always found that a few test cases combined with inspection of
>> associated the database entries via an SQL manager  package is the
>> easiest and fastest way to nail down exactly what is happening (as a
>> Linux hacker I like MYSQL Admin, but it is only one of many). This
>> process is aided greatly by using a Diva distro to create a "one of"
>> grid with only the database entries of interest.
>>
>> Karen
>>
>> On 03/05/2012 09:54 AM, Kevin Buckley wrote:
>>> Hi Karen
>>>
>>> As far as I understand (or can guess!), the concept is that the AV UID
>>> (PrincipalID) and asset UID's are created by using a timestamp variant of
>>> UUID (so I'm using the one provided conveniently by MySQL which provides
>>> temporal separation plus 48 bit random, spatial separation).  So that
> takes
>>> care of the nuts and bolts.
>>>
>>> And yes I can see that, in the hypergrid case, there is the issue of
> names
>>> which are unique within each OS implementation (which I'm trying to deal
>>> with now) but not able to be guaranteed unique everywhere as they are
> chosen
>>> by people who are not known for their randomness.  I am assuming that the
>>> combination of locally enforced, unique FirstName+LastName plus host ID,
>>> provides a globally unique name scheme even in the hypergrid scenario.
>>>
>>> Is that correct?
>>>
>>> Is the host ID you referred to, the ServiceURL field in the same table
>>> (useraccounts)?
>>>
>>> Kevin
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:[hidden email]] On Behalf Of Karen Palen
>>> Sent: 05 March 2012 16:10
>>> To: [hidden email]
>>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>>
>>> I suspect the answer is that you need more information than just those
>>> two fields.
>>>
>>> I am thinking of the fairly common case in Hypergrid usage where two
>>> avatars have the same name, distinguished only by the field that tells
>>> their host system.
>>>
>>> I have used this for testing on many occasions.
>>>
>>> Karen
>>>
>>> On 03/05/2012 04:45 AM, Kevin Buckley wrote:
>>>> Hi - Thanks for the quick response.
>>>>
>>>> I understand the reasons why PrincipalID is the Primary Key and hence
>>> should
>>>> be unique (and I now realise my original question was malformed).
>>>>
>>>> The actual question is: Why isn't 'Name' (the composite key including
>>>> FirstName and LastName) defined as Unique?
>>>>
>>>> Defining it as Unique certainly works and causes MySQL to throw Error
> 1062
>>>> in response to an attempt to create  a duplicate FirstName/LastName
> entry
>>>> which is the (my) required behaviour.
>>>>
>>>> So I am wondering why 'Name' isn't defined as Unique when the database
>>>> tables are created (or upgraded) by Opensim, given that it goes to the
>>>> trouble of defining it in the first place!
>>>>
>>>> Thanks.
>>>>
>>>> -----Original Message-----
>>>> From: [hidden email]
>>>> [mailto:[hidden email]] On Behalf Of M.E.
> Verhagen
>>>> Sent: 04 March 2012 23:42
>>>> To: [hidden email]
>>>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>>>
>>>> the PrincipalID field is already the primary key.
>>>>
>>>> There can be only one primary key.
>>>>
>>>> This uuid key is the unique identifier on wich an avatar is idenified.
>>>>
>>>> When you write you own member system with direct database manipulation
>>>> than you should check if the avatar is unique or else you will end up
>>>> with more than one avatar name attached to an uuid.
>>>>
>>>>
>>>>
>>>> 2012/3/4, Kevin Buckley<[hidden email]>:
>>>>> I hope this is not covering old ground.  I'm playing with a member
> system
>>>>> for standalone OpenSim and I'm trying to decide the best way to deal
> with
>>>>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>>>>> multiple, concurrent users).
>>>>>
>>>>>
>>>>>
>>>>> I notice that the default useraccounts table has a composite key called
>>>> Name
>>>>> (which consists of FirstName and LastName).  But it isn't defined as a
>>>>> primary key.  So duplicate name inserts are not trapped as a result of
>>>> that
>>>>> (I believe that is the reason, anyway).
>>>>>
>>>>>
>>>>>
>>>>> Does anyone know if there is a reason 'Name' is not defined as a
> primary
>>>>> key?  Is there a problem with making it so?
>>>>>
>>>>>
>>>>>
>>>>> I have other ways I can deal with this but trapping it at the
>>> useraccounts
>>>>> table insert would be the best way.
>>>>>
>>>>>
>>>>>
>>>>> Thanks.
>>>>>
>>>>>
>>> _______________________________________________
>>> Opensim-users mailing list
>>> [hidden email]
>>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>>
>>> _______________________________________________
>>> Opensim-users mailing list
>>> [hidden email]
>>> https://lists.berlios.de/mailman/listinfo/opensim-users
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>>
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


--
Justin Clark-Casey (justincc)
http://justincc.org/blog
http://twitter.com/justincc
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users