MySQL Forums
Forum List  »  Replication

Re: About AUTO-INC In different mode (1,2 or 3)
Posted by: five small
Date: April 22, 2021 02:31AM

In MySQL offical:

Using auto-increment with replication
If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the source and its replicas. Auto-increment values are not ensured to be the same on the replicas as on the source if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the source and replicas do not use the same lock mode.

and then I use next sql to insert and innodb_autoinc_lock_mode=1 :

tx1:insert into t1 values(1,"a"),(null,"b"),(3,"c"),(null,"d");
tx2:insert into t1 values(5,"a"),(null,"b"),(7,"c"),(null,"d");

In my opinion,if there is not a lock,we may get Auto_increment numbers interleaved? I think it's not safe for statement-based replication,

Options: ReplyQuote

Written By
Re: About AUTO-INC In different mode (1,2 or 3)
April 22, 2021 02:31AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.