UNIQUE KEY vs UNIQUE INDEX

UNIQUE KEY vs UNIQUE INDEX

am 30.07.2009 16:13:26 von B

Are UNIQUE KEY & UNIQUE INDEX two ways of specifying the same thing? If
not, what are the differences?

Feel free to tell me to RTFM but please post manual chapters. I've been
looking but haven't been able to find 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: UNIQUE KEY vs UNIQUE INDEX

am 30.07.2009 18:26:48 von mos

At 09:13 AM 7/30/2009, b wrote:
>Are UNIQUE KEY & UNIQUE INDEX two ways of specifying the same thing? If
>not, what are the differences?
>
>Feel free to tell me to RTFM but please post manual chapters. I've been
>looking but haven't been able to find anything.

They are the same thing. If you meant "Primary Key" and "Unique Index"
then they too are basically the same except Primary Key is NOT NULL and the
optimizer will infer the primary key when doing RI joins if an index is not
specified.

There may be other subtle differences that others can comment on.

Mike


--
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

Replication recovery on restart

am 30.07.2009 20:00:15 von bcantwell

I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log =
databases both replicating wonderfully. They are configured in a dual =
master scenario so that one can take over for the other in my HA =
environment I've built. All is working great until... If one or the =
other box reboots or the mysql restarts, the replication gets out of =
whack. Especially if I simulate both of them crashing in a worst case =
scenario, they are then both trying to sync from the wrong =
Master_log_file and Read_Master_Log_Pos...

Since catastrpohe WILL happen eventually (heence the need for HA) how do =
I direct the newly restarted boxes to the right position in the correct =
files on restart?

Thanks

--
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: UNIQUE KEY vs UNIQUE INDEX

am 30.07.2009 20:23:25 von Joerg Bruehe

Hi !

mos wrote:
> At 09:13 AM 7/30/2009, b wrote:
>> Are UNIQUE KEY & UNIQUE INDEX two ways of specifying the same thin=
g?
>> If not, what are the differences?
>>
>> Feel free to tell me to RTFM but please post manual chapters. I've
>> been looking but haven't been able to find anything.
>=20
> They are the same thing. If you meant "Primary Key" and "Unique In=
dex"
> then they too are basically the same except Primary Key is NOT NULL=
and
> the optimizer will infer the primary key when doing RI joins if an =
index
> is not specified.
>=20
> There may be other subtle differences that others can comment on.

For all practical purposes, I agree.

=46rom the database (or SQL) theory point of view, a "unique constrai=
nt"
is a logical concept ("there cannot be any two cars having the same
registration number"), and any index is a physical means of
implementation (to speed up access).

I have never heard of any DBMS implementing a unique constraint in an=
y
way but using a "unique index", but it could be done: at a huge
performance cost (sequential search through the table).


For "primary key", there is also the school that holds a primary key
cannot change, it is a permanent attribute. If you share that idea, t=
hen
there is a difference to the uniqueness concept.
Take the example of a car: It has a manufacturer number which is
permanent, but the registration will change when it is moved (or sold=
)
to another area or country (depending on local law).
So the manufacturer number could be used as the primary key (will nev=
er
change), whereas the registration is guaranteed to be unique (at any
time) but may vary over time.


Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
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: Replication recovery on restart

am 31.07.2009 00:07:56 von Gavin Towey

Hi Bryan,

Please define "out of whack." Tell us exactly what you're doing when you r=
estart, and what the replication state is before and after, and where the u=
pdates are coming from.

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Thursday, July 30, 2009 11:00 AM
To: mysql@lists.mysql.com
Subject: Replication recovery on restart

I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databa=
ses both replicating wonderfully. They are configured in a dual master scen=
ario so that one can take over for the other in my HA environment I've buil=
t. All is working great until... If one or the other box reboots or the mys=
ql restarts, the replication gets out of whack. Especially if I simulate bo=
th of them crashing in a worst case scenario, they are then both trying to =
sync from the wrong Master_log_file and Read_Master_Log_Pos...

Since catastrpohe WILL happen eventually (heence the need for HA) how do I =
direct the newly restarted boxes to the right position in the correct files=
on restart?

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
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: UNIQUE KEY vs UNIQUE INDEX

am 31.07.2009 03:36:07 von B

On 07/30/2009 02:23 PM, Joerg Bruehe wrote:
> Hi !
>
> mos wrote:
>> At 09:13 AM 7/30/2009, b wrote:
>>> Are UNIQUE KEY& UNIQUE INDEX two ways of specifying the same thing?
>>> If not, what are the differences?
>>>
>>> Feel free to tell me to RTFM but please post manual chapters. I've
>>> been looking but haven't been able to find anything.
>> They are the same thing. If you meant "Primary Key" and "Unique Index"
>> then they too are basically the same except Primary Key is NOT NULL and
>> the optimizer will infer the primary key when doing RI joins if an index
>> is not specified.


No, PK I understand.

>> There may be other subtle differences that others can comment on.
>
> For all practical purposes, I agree.
>
> From the database (or SQL) theory point of view, a "unique constraint"
> is a logical concept ("there cannot be any two cars having the same
> registration number"), and any index is a physical means of
> implementation (to speed up access).
>
> I have never heard of any DBMS implementing a unique constraint in any
> way but using a "unique index", but it could be done: at a huge
> performance cost (sequential search through the table).
>

This is what I was thinking, also.


> For "primary key", there is also the school that holds a primary key
> cannot change, it is a permanent attribute. If you share that idea, then
> there is a difference to the uniqueness concept.

Yes, I treat a PK as being something that's over and above simply being
unique. But this touches upon the reason for my question: I guess I was
wondering if they might be subtly different, though "merging" the two
would result in a PK :-)

> Take the example of a car: It has a manufacturer number which is
> permanent, but the registration will change when it is moved (or sold)
> to another area or country (depending on local law).
> So the manufacturer number could be used as the primary key (will never
> change), whereas the registration is guaranteed to be unique (at any
> time) but may vary over time.
>

Thanks, both, for your replies.


--
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: Replication recovery on restart

am 31.07.2009 19:08:12 von bcantwell

Before I simulate a total server failure, master1 is using binary file =
msyql-bin00001 position 2231467 and it's slave master2 is following the =
correct file at the correct position. This is after initial setup. Once =
I restart master1, it will then start to use msyql-bin00002 position 98 =
and master 2 is still trying to follow msyql-bin00001 position 2231467. =
=20

And since I have this as dual master setup, if I simulate both boxes =
restarting in a total catastrophe, the masters both change files and the =
slaves remain trying to follow on the old information.



-----Original Message-----
From: Gavin Towey [mailto:gtowey@ffn.com]=20
Sent: Thursday, July 30, 2009 5:08 PM
To: Cantwell, Bryan; mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Hi Bryan,

Please define "out of whack." Tell us exactly what you're doing when =
you restart, and what the replication state is before and after, and =
where the updates are coming from.

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Thursday, July 30, 2009 11:00 AM
To: mysql@lists.mysql.com
Subject: Replication recovery on restart

I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log =
databases both replicating wonderfully. They are configured in a dual =
master scenario so that one can take over for the other in my HA =
environment I've built. All is working great until... If one or the =
other box reboots or the mysql restarts, the replication gets out of =
whack. Especially if I simulate both of them crashing in a worst case =
scenario, they are then both trying to sync from the wrong =
Master_log_file and Read_Master_Log_Pos...

Since catastrpohe WILL happen eventually (heence the need for HA) how do =
I direct the newly restarted boxes to the right position in the correct =
files on restart?

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged =
and confidential information. It is intended only for the use of the =
person(s) named above. If you are not the intended recipient, you are =
hereby notified that any review, dissemination, distribution or =
duplication of this communication is strictly prohibited. If you are not =
the intended recipient, please contact the sender by reply email and =
destroy all copies of the original message.

--
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: Replication recovery on restart

am 31.07.2009 20:20:55 von Gavin Towey

Bryan,

How are you restarting mysql? In the case a master crashes, it's definitely=
common for the slave to miss the fact that the master is using a different=
binlog. The slave advances to a position past the end of the previous bin=
log, and stops with and error like "tried to read impossible position." In=
this case you do have to intervene, but that's an easy enough case to writ=
e a script to handle.

When restarting mysql normally, you shouldn't have this problem: i.e. servi=
ce mysql restart / /etc/ini.d/mysql restart

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Friday, July 31, 2009 10:08 AM
To: mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Before I simulate a total server failure, master1 is using binary file msyq=
l-bin00001 position 2231467 and it's slave master2 is following the correc=
t file at the correct position. This is after initial setup. Once I restart=
master1, it will then start to use msyql-bin00002 position 98 and master 2=
is still trying to follow msyql-bin00001 position 2231467.

And since I have this as dual master setup, if I simulate both boxes restar=
ting in a total catastrophe, the masters both change files and the slaves r=
emain trying to follow on the old information.



-----Original Message-----
From: Gavin Towey [mailto:gtowey@ffn.com]
Sent: Thursday, July 30, 2009 5:08 PM
To: Cantwell, Bryan; mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Hi Bryan,

Please define "out of whack." Tell us exactly what you're doing when you r=
estart, and what the replication state is before and after, and where the u=
pdates are coming from.

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Thursday, July 30, 2009 11:00 AM
To: mysql@lists.mysql.com
Subject: Replication recovery on restart

I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databa=
ses both replicating wonderfully. They are configured in a dual master scen=
ario so that one can take over for the other in my HA environment I've buil=
t. All is working great until... If one or the other box reboots or the mys=
ql restarts, the replication gets out of whack. Especially if I simulate bo=
th of them crashing in a worst case scenario, they are then both trying to =
sync from the wrong Master_log_file and Read_Master_Log_Pos...

Since catastrpohe WILL happen eventually (heence the need for HA) how do I =
direct the newly restarted boxes to the right position in the correct files=
on restart?

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
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: Replication recovery on restart

am 31.07.2009 21:51:09 von bcantwell

Yes I am trying to simulate total failure. In this test case I am using =
2 Virtual Machines and I just kill one and then when it comes back I =
have the challenge described.
How can I go about getting the slave back in tune with the newly =
restarted master?=20

Thanks

-----Original Message-----
From: Gavin Towey [mailto:gtowey@ffn.com]=20
Sent: Friday, July 31, 2009 1:21 PM
To: Cantwell, Bryan; mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Bryan,

How are you restarting mysql? In the case a master crashes, it's =
definitely common for the slave to miss the fact that the master is =
using a different binlog. The slave advances to a position past the end =
of the previous binlog, and stops with and error like "tried to read =
impossible position." In this case you do have to intervene, but that's =
an easy enough case to write a script to handle.

When restarting mysql normally, you shouldn't have this problem: i.e. =
service mysql restart / /etc/ini.d/mysql restart

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Friday, July 31, 2009 10:08 AM
To: mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Before I simulate a total server failure, master1 is using binary file =
msyql-bin00001 position 2231467 and it's slave master2 is following the =
correct file at the correct position. This is after initial setup. Once =
I restart master1, it will then start to use msyql-bin00002 position 98 =
and master 2 is still trying to follow msyql-bin00001 position 2231467.

And since I have this as dual master setup, if I simulate both boxes =
restarting in a total catastrophe, the masters both change files and the =
slaves remain trying to follow on the old information.



-----Original Message-----
From: Gavin Towey [mailto:gtowey@ffn.com]
Sent: Thursday, July 30, 2009 5:08 PM
To: Cantwell, Bryan; mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Hi Bryan,

Please define "out of whack." Tell us exactly what you're doing when =
you restart, and what the replication state is before and after, and =
where the updates are coming from.

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Thursday, July 30, 2009 11:00 AM
To: mysql@lists.mysql.com
Subject: Replication recovery on restart

I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log =
databases both replicating wonderfully. They are configured in a dual =
master scenario so that one can take over for the other in my HA =
environment I've built. All is working great until... If one or the =
other box reboots or the mysql restarts, the replication gets out of =
whack. Especially if I simulate both of them crashing in a worst case =
scenario, they are then both trying to sync from the wrong =
Master_log_file and Read_Master_Log_Pos...

Since catastrpohe WILL happen eventually (heence the need for HA) how do =
I direct the newly restarted boxes to the right position in the correct =
files on restart?

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged =
and confidential information. It is intended only for the use of the =
person(s) named above. If you are not the intended recipient, you are =
hereby notified that any review, dissemination, distribution or =
duplication of this communication is strictly prohibited. If you are not =
the intended recipient, please contact the sender by reply email and =
destroy all copies of the original message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged =
and confidential information. It is intended only for the use of the =
person(s) named above. If you are not the intended recipient, you are =
hereby notified that any review, dissemination, distribution or =
duplication of this communication is strictly prohibited. If you are not =
the intended recipient, please contact the sender by reply email and =
destroy all copies of the original message.

--
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: Replication recovery on restart

am 31.07.2009 22:17:27 von Gavin Towey

Bryan,

When the slave encounters that error, you can simply set it to replicate fr=
om the next binlog file in the sequence starting at position 98. It should=
be easy to have a script automate this process.

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Friday, July 31, 2009 12:51 PM
To: mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Yes I am trying to simulate total failure. In this test case I am using 2 =
Virtual Machines and I just kill one and then when it comes back I have the=
challenge described.
How can I go about getting the slave back in tune with the newly restarted =
master?

Thanks

-----Original Message-----
From: Gavin Towey [mailto:gtowey@ffn.com]
Sent: Friday, July 31, 2009 1:21 PM
To: Cantwell, Bryan; mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Bryan,

How are you restarting mysql? In the case a master crashes, it's definitely=
common for the slave to miss the fact that the master is using a different=
binlog. The slave advances to a position past the end of the previous bin=
log, and stops with and error like "tried to read impossible position." In=
this case you do have to intervene, but that's an easy enough case to writ=
e a script to handle.

When restarting mysql normally, you shouldn't have this problem: i.e. servi=
ce mysql restart / /etc/ini.d/mysql restart

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Friday, July 31, 2009 10:08 AM
To: mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Before I simulate a total server failure, master1 is using binary file msyq=
l-bin00001 position 2231467 and it's slave master2 is following the correc=
t file at the correct position. This is after initial setup. Once I restart=
master1, it will then start to use msyql-bin00002 position 98 and master 2=
is still trying to follow msyql-bin00001 position 2231467.

And since I have this as dual master setup, if I simulate both boxes restar=
ting in a total catastrophe, the masters both change files and the slaves r=
emain trying to follow on the old information.



-----Original Message-----
From: Gavin Towey [mailto:gtowey@ffn.com]
Sent: Thursday, July 30, 2009 5:08 PM
To: Cantwell, Bryan; mysql@lists.mysql.com
Subject: RE: Replication recovery on restart

Hi Bryan,

Please define "out of whack." Tell us exactly what you're doing when you r=
estart, and what the replication state is before and after, and where the u=
pdates are coming from.

Regards,
Gavin Towey

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@firescope.com]
Sent: Thursday, July 30, 2009 11:00 AM
To: mysql@lists.mysql.com
Subject: Replication recovery on restart

I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databa=
ses both replicating wonderfully. They are configured in a dual master scen=
ario so that one can take over for the other in my HA environment I've buil=
t. All is working great until... If one or the other box reboots or the mys=
ql restarts, the replication gets out of whack. Especially if I simulate bo=
th of them crashing in a worst case scenario, they are then both trying to =
sync from the wrong Master_log_file and Read_Master_Log_Pos...

Since catastrpohe WILL happen eventually (heence the need for HA) how do I =
direct the newly restarted boxes to the right position in the correct files=
on restart?

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
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