Announcement

Announcement Module
Collapse
No announcement yet.

Nodes stop syncing after update to primary key --- autoincrement ???

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Nodes stop syncing after update to primary key --- autoincrement ???

    I am not sure if I need to set the auto_increment_increment and auto_increment_offset or set the slave_exec_mode=IDEMPOTENT setting.

    We have a 3 node cluster. Does this mean I need to do the following?

    Code:
     for server 1, this server generate ids 1,4,7,10,etc..
    auto_increment_increment = 3
    auto_increment_offset = 1
      for server 2, this server generate ids 2,5,8,11,etc..
    auto_increment_increment = 3
    auto_increment_offset = 2
      for server 2, this server generate ids 3,6,9,12,etc..
    auto_increment_increment = 3
    auto_increment_offset = 3


    The reason I ask is because we get this error when one node updates/deletes/changes a primary key:

    Code:
    140523  1:32:00 [ERROR] Slave SQL: Could not execute Update_rows event on table core.unitIdentity; Can't find record in 'unitIdentity', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 977, Error_code: 1032
    140523  1:32:00 [Warning] WSREP: RBR event 2 Update_rows apply warning: 120, 25647031
    140523  1:32:00 [ERROR] WSREP: Failed to apply trx: source: f34d5522-b9d6-11e3-0800-4357f09cb2c2 version: 2 local: 0 state: APPLYING flags: 1 conn_id: 13636572 trx_id: 520371507 seqnos (l: 4304736, g: 25647031, s: 25647030, d: 25647029, ts: 1400823120103528904)
    140523  1:32:00 [ERROR] WSREP: Failed to apply app buffer: seqno: 25647031, status: WSREP_FATAL
             at galera/src/replicator_smm.cpp:apply_wscoll():52
             at galera/src/replicator_smm.cpp:apply_trx_ws():118
    140523  1:32:00 [ERROR] WSREP: Node consistency compromized, aborting...
    140523  1:32:00 [Note] WSREP: Closing send monitor...
    140523  1:32:00 [Note] WSREP: Closed send monitor.
    140523  1:32:00 [Note] WSREP: gcomm: terminating thread
    140523  1:32:00 [Note] WSREP: gcomm: joining thread
    140523  1:32:00 [Note] WSREP: gcomm: closing backend
    140523  1:32:05 [Note] WSREP: evs::proto(0c861e3d-b9d7-11e3-0800-f2e0a3aaac9f, LEAVING, view_id(REG,0c861e3d-b9d7-11e3-0800-f2e0a3aaac9f,16)) suspecting node: 134a6b08-b9d7-11e3-0800-c4344cdf375f
    140523  1:32:06 [Note] WSREP: evs::proto(0c861e3d-b9d7-11e3-0800-f2e0a3aaac9f, LEAVING, view_id(REG,0c861e3d-b9d7-11e3-0800-f2e0a3aaac9f,16)) suspecting node: 134a6b08-b9d7-11e3-0800-c4344cdf375f
    140523  1:32:06 [Note] WSREP: view(view_id(NON_PRIM,0c861e3d-b9d7-11e3-0800-f2e0a3aaac9f,16) memb {
            0c861e3d-b9d7-11e3-0800-f2e0a3aaac9f,
    } joined {
    } left {
    } partitioned {
            134a6b08-b9d7-11e3-0800-c4344cdf375f,
            f34d5522-b9d6-11e3-0800-4357f09cb2c2,
    })
    140523  1:32:06 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
    140523  1:32:06 [Note] WSREP: view((empty))
    140523  1:32:06 [Note] WSREP: gcomm: closed
    140523  1:32:06 [Note] WSREP: Flow-control interval: [16, 16]
    140523  1:32:06 [Note] WSREP: Received NON-PRIMARY.
    140523  1:32:06 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 25647031)
    140523  1:32:06 [Note] WSREP: Received self-leave message.
    140523  1:32:06 [Note] WSREP: Flow-control interval: [0, 0]
    140523  1:32:06 [Note] WSREP: Received SELF-LEAVE. Closing connection.
    140523  1:32:06 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 25647031)
    140523  1:32:06 [Note] WSREP: RECV thread exiting 0: Success
    140523  1:32:06 [Note] WSREP: recv_thread() joined.
    140523  1:32:06 [Note] WSREP: Closing slave action queue.
    140523  1:32:06 [Note] WSREP: /usr/sbin/mysqld: Terminated.
    140523 01:32:06 mysqld_safe Number of processes running now: 0
    140523 01:32:06 mysqld_safe WSREP: not restarting wsrep node automatically
    140523 01:32:06 mysqld_safe mysqld from pid file /var/lib/mysql/balpercona2.bal.sagedining.com.pid ended

    I found some info from this link : http://www.mysqlperformanceblog.com/...rement_offset/

  • #2
    hi shockwave

    Im not sure exactly if its a primary key error but if you do have auto increment fields in your table which are primary keys, you will need to set server offsets for so that you do not get primary key conflicts

    Im not an expert but there are two ways of doing this,
    one using
    variable wsrep_auto_increment_control
    Yes
    Yes
    Global
    Yes
    ON
    This variable manages the auto_increment_increment and auto_increment_offset variables automatically depending on the size of the cluster. This helps prevent auto_increment replication conflicts across the cluster by giving each node it’s own range of auto_increment values. This may not be desirable depending on application’s use and assumptions of auto-increments. It can be turned off in Master/Slave clusters.
    By setting wsrep_auto_increment_control=1 it will sort this out for you on the whole cluster, however

    in our my.cnf file we we set them statically like this,
    I think is better use auto_increment_increment , auto_increment_offset.

    example if have 3 servers
    for server 1, this server generate ids 1,4,7,10,etc..
    auto_increment_increment = 3
    auto_increment_offset = 1

    for server 2, this server generate ids 2,5,8,11,etc..
    auto_increment_increment = 3
    auto_increment_offset = 2

    for server 2, this server generate ids 3,6,9,12,etc..
    auto_increment_increment = 3
    auto_increment_offset = 3


    x
    By setting them statically, we know for sure that our ids are set correctly as what we noticed if a node leaves the cluster, when using wsrep_auto_increment_control the node count changes based on the total node connected, not the total node that suppose to be in the cluster ( ie one fails due to network problems, it drops the id by 1 ). We were not sure how that will affect our application
    Last edited by zmahomedy; 05-24-2014, 02:34 AM.

    Comment


    • #3
      So you recommend that I add
      Code:
      wsrep_auto_increment_control=1
      to each nodes my.cnf and it will automatically set the proper ID when the cluster grows and shrinks?

      Or do you recommend the static setup that you mentioned?

      Comment

      Working...
      X