Re: [Maria-discuss] Reg replication and commit

2016-10-14 Thread Dipti Joshi
Karthick:

You should post this in maxscale group as well.

Dipti

On Fri, Oct 14, 2016 at 6:34 PM, Kristian Nielsen 
wrote:

> 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

2016-10-14 Thread Kristian Nielsen
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


Re: [Maria-discuss] Reg replication and commit

2016-10-14 Thread Karthick Subramanian
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