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