Re: [Maria-discuss] Reg replication and commit
Karthick: You should post this in maxscale group as well. Dipti On Fri, Oct 14, 2016 at 6:34 PM, Kristian Nielsenwrote: > Karthick Subramanian 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 > ___ 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
Re: [Maria-discuss] Reg replication and commit
Karthick Subramanianwrites: > 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
Re: [Maria-discuss] Reg replication and commit
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 < ksubraman...@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 > > ___ 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