Sync two similar database at different location

I planned to use two similar MySQL databases which are distributed
geographically. I have a script which autogenerates primary key for a
table using following pseudocode

var x = select max(id) from tablename;
return (x + 1);

At some scenario, the common scripts can be run concurrently from both
location.

say, the max(id) is 5000

test run from location 1 using DB1 get 5001
test run from location 2 using DB2(similar copy of DB1) may also get the
same id "5001", which will create problem.

I can avoid this by
var x = select max(id) from tablename group by location having
location='?';
return (x+1)

in this case, I am using (id,location) as the primary key. But, this
doesn't seems to be a good design.

I am still in analysis & design phase. Yet to implement the design. I
am bit new to database.

How can I sync two database such that there are no any conflicts? Any
utilities available for this?
Is there any other approach to auto-generate primary key?

Regards,
Pradeep Bojan.


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Sync two similar database at different location

--=_alternative 0073EB9B85256FA1_=
Content-Type: text/plain; charset="US-ASCII"

Pradeep wrote on 02/07/2005 03:35:59 PM:

> I planned to use two similar MySQL databases which are distributed
> geographically. I have a script which autogenerates primary key for a
> table using following pseudocode
>
> var x = select max(id) from tablename;
> return (x + 1);
>
> At some scenario, the common scripts can be run concurrently from both
> location.
>
> say, the max(id) is 5000
>
> test run from location 1 using DB1 get 5001
> test run from location 2 using DB2(similar copy of DB1) may also get the

> same id "5001", which will create problem.
>
> I can avoid this by
> var x = select max(id) from tablename group by location having
> location='?';
> return (x+1)
>
> in this case, I am using (id,location) as the primary key. But, this
> doesn't seems to be a good design.
>
> I am still in analysis & design phase. Yet to implement the design. I
> am bit new to database.
>
> How can I sync two database such that there are no any conflicts? Any
> utilities available for this?
> Is there any other approach to auto-generate primary key?
>
> Regards,
> Pradeep Bojan.
>
>

What you can do varies by version. What version of MySQL are you running
and what platform (operating system) is it on?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0073EB9B85256FA1_=--

Re: Sync two similar database at different location

--------------090903060809030702010005
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi Shawn,

I am using MySQL version 4.1.9-0 on Linux platform (Red hat Fedora).

Thanks,
Pradeep Bojan.

SGreen@unimin.com wrote:

>Pradeep wrote on 02/07/2005 03:35:59 PM:
>
>
>
>>I planned to use two similar MySQL databases which are distributed
>>geographically. I have a script which autogenerates primary key for a
>>table using following pseudocode
>>
>> var x = select max(id) from tablename;
>> return (x + 1);
>>
>>At some scenario, the common scripts can be run concurrently from both
>>location.
>>
>>say, the max(id) is 5000
>>
>>test run from location 1 using DB1 get 5001
>>test run from location 2 using DB2(similar copy of DB1) may also get the
>>
>>
>
>
>
>>same id "5001", which will create problem.
>>
>>I can avoid this by
>> var x = select max(id) from tablename group by location having
>>location='?';
>> return (x+1)
>>
>>in this case, I am using (id,location) as the primary key. But, this
>>doesn't seems to be a good design.
>>
>>I am still in analysis & design phase. Yet to implement the design. I
>>am bit new to database.
>>
>>How can I sync two database such that there are no any conflicts? Any
>>utilities available for this?
>>Is there any other approach to auto-generate primary key?
>>
>>Regards,
>>Pradeep Bojan.
>>
>>
>>
>>
>
>What you can do varies by version. What version of MySQL are you running
>and what platform (operating system) is it on?
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>

--------------090903060809030702010005--

Google type searched of a database.

------=_NextPart_000_002C_01C50D30.EFF63AD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Does anybody know the best way in MySQL to do an author/title sort of =
search -- basically like the fast searches performed by google?=20

What I have is a database with fields for author, title, ISBN and =
(verbose) description. I would like to be able to enter a search key =
comprised of multiple words or a stock number, and to bring up a list of =
records which are sorted by "best match" as to my search string.=20

If this question goes beyond the basics of MySQL, is there an article or =
book on this subject that I might read?

Thanks and have a great day!

------=_NextPart_000_002C_01C50D30.EFF63AD0--

Re: Sync two similar database at different location

--=_alternative 00510A7785256FA2_=
Content-Type: text/plain; charset="US-ASCII"

Based on the fact that you are running a version higher than 4.1.2, I
suggest you use the UUID() function to generate your primary key values.

http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.ht ml

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Pradeep wrote on 02/07/2005 06:42:05 PM:

> Hi Shawn,
>
> I am using MySQL version 4.1.9-0 on Linux platform (Red hat Fedora).
>
> Thanks,
> Pradeep Bojan.
>
> SGreen@unimin.com wrote:
>
> >Pradeep wrote on 02/07/2005 03:35:59 PM:
> >
> >
> >
> >>I planned to use two similar MySQL databases which are distributed
> >>geographically. I have a script which autogenerates primary key for a

> >>table using following pseudocode
> >>
> >> var x = select max(id) from tablename;
> >> return (x + 1);
> >>
> >>At some scenario, the common scripts can be run concurrently from both

> >>location.
> >>
> >>say, the max(id) is 5000
> >>
> >>test run from location 1 using DB1 get 5001
> >>test run from location 2 using DB2(similar copy of DB1) may also get
the
> >>
> >>
> >
> >
> >
> >>same id "5001", which will create problem.
> >>
> >>I can avoid this by
> >> var x = select max(id) from tablename group by location having
> >>location='?';
> >> return (x+1)
> >>
> >>in this case, I am using (id,location) as the primary key. But, this
> >>doesn't seems to be a good design.
> >>
> >>I am still in analysis & design phase. Yet to implement the design. I

> >>am bit new to database.
> >>
> >>How can I sync two database such that there are no any conflicts? Any
> >>utilities available for this?
> >>Is there any other approach to auto-generate primary key?
> >>
> >>Regards,
> >>Pradeep Bojan.
> >>
> >>
> >>
> >>
> >
> >What you can do varies by version. What version of MySQL are you
running
> >and what platform (operating system) is it on?
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >

--=_alternative 00510A7785256FA2_=--

Re: Google type searched of a database.

full text index on author, title then

select id, title, author, match(author,title) against('unbearable
lightness') from books where match(author,title) against('unbearable
lightness') order by match(author,title) against('unbearable
lightness') DESC;

The query optimizer only does the comparison once, btw.

Corey

On Feb 7, 2005, at 6:20 PM, mdangus@psychtest.com wrote:

> Does anybody know the best way in MySQL to do an author/title sort of
> search -- basically like the fast searches performed by google?
>
> What I have is a database with fields for author, title, ISBN and
> (verbose) description. I would like to be able to enter a search key
> comprised of multiple words or a stock number, and to bring up a list
> of records which are sorted by "best match" as to my search string.
>
> If this question goes beyond the basics of MySQL, is there an article
> or book on this subject that I might read?
>
> Thanks and have a great day!


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org