[Maria-discuss] Reg replication and commit
Hi All, I have the below topology: Master -> Slave (using GTID replication) Master -> Maxscale (Binlog Router) Maxscale -> DR Site (via binlog router) my.cnf is same across all sites: lower-case-table-names=1 autocommit=0 #replication variables log-bin=binlog binlog-format=ROW replicate_annotate_row_events=OFF binlog_annotate_row_events=OFF I have created a table and I could see on slave DB as well as at DR Site DB. When I INSERT records, this is not shown up in Slave DB as well DR Site DB. I have checked replication status and both are sync with master server binlog pos. But the rows reflected in slave and DR site DB only when we COMMIT on both Slave as well DR site DB. Below when I try at Slave DB: MariaDB [dr_repl]> select * from test_dr_repl; Empty set (0.00 sec) MariaDB [dr_repl]> commit; Query OK, 0 rows affected (0.00 sec) MariaDB [dr_repl]> select * from test_dr_repl; +----+------+ | id | val | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec) I am wondering whether I did any weird mistake. Could anyone please help me to understand and a solution for this. Regards, Karthick
I didn't expect that autocommit = OFF will affect the replication feature. I thought replication will be handled irrespective what commit type is enabled in the DB Server. So whenever I promote any new master from a slave, I have to taken care this auto commit accordingly. Is my understanding correct? Without mentioning in my.cnf file, is there a way we can handle this behavior during start of the server, like mysqld --auto-commit=ON/OFF. Thank you. On Fri, Oct 14, 2016 at 5:11 PM, Karthick Subramanian < ksubramanian@paycommerce.com> wrote:
Hi All,
I have the below topology:
Master -> Slave (using GTID replication)
Master -> Maxscale (Binlog Router)
Maxscale -> DR Site (via binlog router)
my.cnf is same across all sites:
lower-case-table-names=1 autocommit=0
#replication variables log-bin=binlog binlog-format=ROW replicate_annotate_row_events=OFF binlog_annotate_row_events=OFF
I have created a table and I could see on slave DB as well as at DR Site DB. When I INSERT records, this is not shown up in Slave DB as well DR Site DB. I have checked replication status and both are sync with master server binlog pos.
But the rows reflected in slave and DR site DB only when we COMMIT on both Slave as well DR site DB.
Below when I try at Slave DB:
MariaDB [dr_repl]> select * from test_dr_repl; Empty set (0.00 sec)
MariaDB [dr_repl]> commit; Query OK, 0 rows affected (0.00 sec)
MariaDB [dr_repl]> select * from test_dr_repl; +----+------+ | id | val | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec)
I am wondering whether I did any weird mistake. Could anyone please help me to understand and a solution for this.
Regards, Karthick
Karthick Subramanian <ksubramanian@paycommerce.com> writes:
Below when I try at Slave DB:
MariaDB [dr_repl]> select * from test_dr_repl; Empty set (0.00 sec)
MariaDB [dr_repl]> commit; Query OK, 0 rows affected (0.00 sec)
MariaDB [dr_repl]> select * from test_dr_repl; +----+------+ | id | val | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec)
I wasn't really able to fully understand your explanation of your problem. However, the above suggests you have an open transaction with isolation level REPEATABLE READ. This is the only situation I can think of where a COMMIT will affect the visibility of other rows. When you open a transaction with REPEATABLE READ (with BEGIN, or with autocommit off), no new changes will be visible until COMMIT or ROLLBACK. This is a basic feature of InnoDB transactions, independent of replication. - Kristian.
Karthick: You should post this in maxscale group as well. Dipti On Fri, Oct 14, 2016 at 6:34 PM, Kristian Nielsen <knielsen@knielsen-hq.org> wrote:
Karthick Subramanian <ksubramanian@paycommerce.com> writes:
Below when I try at Slave DB:
MariaDB [dr_repl]> select * from test_dr_repl; Empty set (0.00 sec)
MariaDB [dr_repl]> commit; Query OK, 0 rows affected (0.00 sec)
MariaDB [dr_repl]> select * from test_dr_repl; +----+------+ | id | val | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec)
I wasn't really able to fully understand your explanation of your problem.
However, the above suggests you have an open transaction with isolation level REPEATABLE READ. This is the only situation I can think of where a COMMIT will affect the visibility of other rows. When you open a transaction with REPEATABLE READ (with BEGIN, or with autocommit off), no new changes will be visible until COMMIT or ROLLBACK. This is a basic feature of InnoDB transactions, independent of replication.
- Kristian.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Dipti Joshi -
Karthick Subramanian -
Kristian Nielsen