how to adjust auto increment id offset?

Hi

On 15 November 2011 11:44, Yan Chunlu <springrider@gmail.com> wrote:

I would like to implement two master db with even-odd id sharding. in
mysql it is fairly easy by using the configuration:

auto_increment_offset = 1
auto_increment_increment = 2

but I have searched a lot didn't find anything related to this, some users
doing this via trigger like "rubyrep".

is there an easy way to do this? thanks!

http://www.postgresql.org/docs/8.1/static/sql-createsequence.html

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On 11/15/11 12:56 AM, Bèrto ëd Sèra wrote:

Hi

On 15 November 2011 11:44, Yan Chunlu <springrider@gmail.com
<mailto:springrider@gmail.com>> wrote:

I would like to implement two master db with even-odd id sharding.
in mysql it is fairly easy by using the configuration:

auto_increment_offset = 1
auto_increment_increment = 2

but I have searched a lot didn't find anything related to this,
some users doing this via trigger like "rubyrep".

is there an easy way to do this? thanks!

http://www.postgresql.org/docs/8.1/static/sql-createsequence.html

also see ALTER SEQUENCE.

basically, you'll need to fix up every sequence (these are created
automatically if you have fields of type SERIAL).... on your 2nd server,

ALTER SEQUENCE somesequencename INCREMENT BY 2 RESTART WITH 2;

and on your 1st server,

ALTER SEQUENCE somesequencename INCREMENT BY 2;

do this before inserting any data.

thats a fairly unusual sharding technique, how do you plan on doing
queries across both sets of data?

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Tue, Nov 15, 2011 at 04:44:23PM +0800, Yan Chunlu wrote:

I would like to implement two master db with even-odd id sharding. in
mysql it is fairly easy by using the configuration:

auto_increment_offset = 1
auto_increment_increment = 2

but I have searched a lot didn't find anything related to this, some users
doing this via trigger like "rubyrep".

is there an easy way to do this? thanks!

Why not adjust the underlying sequences to have different start values
and to advance by 2?

A

--
Andrew Sullivan
ajs@crankycanuck.ca

thanks a lot for the tip!

sorry for used the wrong word, it is just multi-master but not sharding, I
would like to setup two master server across two datacenter. one's id
increased by 1, and the other by 2.
so I could have a queue sync the record in the background by myself. kind
of a dumb way but it seems they only choice for me, the delay within
several minutes is acceptable.

On Tue, Nov 15, 2011 at 5:12 PM, John R Pierce <pierce@hogranch.com> wrote:

On Tue, Nov 15, 2011 at 9:03 PM, Yan Chunlu <springrider@gmail.com> wrote:

thanks a lot for the tip!
sorry for used the wrong word, it is just multi-master but not sharding,  I
would like to setup two master server across two datacenter.  one's id
increased by 1, and the other by 2.
so I could have a queue sync the record in the background by myself.  kind
of a dumb way but it seems they only choice for me,  the delay within
several minutes is acceptable.

you might want to look at either using a larger skip, like 10 or 20,
so you can add more servers at a later date if you need to. The other
way is to start each sequence at some huge offset like 2Billion and be
sure to use a bigint not a regular int.