Fixes for binary logging --read-only mode

- Any temporary tables created under read-only mode will never be logged
  to binary log.  Any usage of these tables to update normal tables, even
  after read-only has been disabled, will use row base logging (as the
  temporary table will not be on the slave).
- Analyze, check and repair table will not be logged in read-only mode.

Other things:
- Removed not used varaibles in
  MYSQL_BIN_LOG::flush_and_set_pending_rows_event.
- Set table_share->table_creation_was_logged for all normal tables.
- THD::binlog_query() now returns -1 if statement was not logged., This
  is used to update table_share->table_creation_was_logged.
- Don't log admin statements in opt_readonly is set.
- Table's that doesn't have table_creation_was_logged will set binlog format to row
  logging.
- Removed not needed/wrong setting of table->s->table_creation_was_logged
  in create_table_from_items()

5 files changed, 296 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/r/read_only.result b/mysql-test/suite/binlog/r/read_only.result
new file mode 100644
index 00000000000..aa1893b56a3
--- /dev/null
+++ b/mysql-test/suite/binlog/r/read_only.result
@@ -0,0 +1,109 @@
+#
+# MDEV-17863 DROP TEMPORARY TABLE creates a transaction in
+# binary log on read only server
+# MDEV-19074 Improved read_only mode for slaves with
+# gtid_strict_mode enabled
+#
+create user test@localhost;
+grant CREATE, DROP, INSERT, SELECT on *.* to test@localhost;
+create table t1 (a int) engine=myisam;
+insert into t1 values (1),(2);
+reset master;
+set global read_only=1;
+# Ensure that optimize and analyze doesn't log to binary log
+connect  con1,localhost,test,,test;
+insert into t1 values(3);
+ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+repair table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	Error	The MariaDB server is running with the --read-only option so it cannot execute this statement
+test.t1	repair	error	Corrupt
+optimize table t1;
+ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
+# Ensure that using temporary tables is not logged
+create temporary table tmp1 (a int) engine=myisam;
+insert into tmp1 values (1),(2);
+update tmp1 set a=10 where a=2;
+delete from tmp1 where a=1;
+create temporary table tmp2 select * from t1;
+select * from tmp1;
+a
+10
+select * from tmp2;
+a
+1
+2
+create temporary table tmp3 like t1;
+create or replace temporary table tmp3 like t1;
+alter table tmp2 add column (b int);
+select * from tmp2;
+a	b
+1	NULL
+2	NULL
+drop table tmp1,tmp2,tmp3;
+# Clean up test connection
+disconnect con1;
+connection default;
+# Execute some commands as root that should not be logged
+optimize table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+repair table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+# Changes to temporary tables created under readonly should not
+# be logged
+create temporary table tmp4 (a int) engine=myisam;
+insert into tmp4 values (1),(2);
+create temporary table tmp5 (a int) engine=myisam;
+insert into tmp5 select * from tmp4;
+alter table tmp5 add column (b int);
+set global read_only=0;
+insert into tmp4 values (3),(4);
+insert into tmp5 values (10,3),(11,4);
+select * from tmp4;
+a
+1
+2
+3
+4
+select * from tmp5;
+a	b
+1	NULL
+2	NULL
+10	3
+11	4
+update tmp4 set a=10 where a=2;
+delete from tmp4 where a=1;
+create table t2 select * from tmp4;
+alter table tmp5 add column (c int);
+insert into tmp5 values (20,5,1),(21,5,2);
+select * from tmp5;
+a	b	c
+1	NULL	NULL
+2	NULL	NULL
+10	3	NULL
+11	4	NULL
+20	5	1
+21	5	2
+drop table tmp4,tmp5;
+# Check what is logged. Only the last create select should be row-logged
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL
+)
+master-bin.000001	#	Annotate_rows	#	#	create table t2 select * from tmp4
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+master-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+# Clean up
+drop user test@localhost;
+drop table t1,t2;
diff --git a/mysql-test/suite/binlog/r/read_only_statement.result b/mysql-test/suite/binlog/r/read_only_statement.result
new file mode 100644
index 00000000000..aa1893b56a3
--- /dev/null
+++ b/mysql-test/suite/binlog/r/read_only_statement.result
@@ -0,0 +1,109 @@
+#
+# MDEV-17863 DROP TEMPORARY TABLE creates a transaction in
+# binary log on read only server
+# MDEV-19074 Improved read_only mode for slaves with
+# gtid_strict_mode enabled
+#
+create user test@localhost;
+grant CREATE, DROP, INSERT, SELECT on *.* to test@localhost;
+create table t1 (a int) engine=myisam;
+insert into t1 values (1),(2);
+reset master;
+set global read_only=1;
+# Ensure that optimize and analyze doesn't log to binary log
+connect  con1,localhost,test,,test;
+insert into t1 values(3);
+ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+repair table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	Error	The MariaDB server is running with the --read-only option so it cannot execute this statement
+test.t1	repair	error	Corrupt
+optimize table t1;
+ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
+# Ensure that using temporary tables is not logged
+create temporary table tmp1 (a int) engine=myisam;
+insert into tmp1 values (1),(2);
+update tmp1 set a=10 where a=2;
+delete from tmp1 where a=1;
+create temporary table tmp2 select * from t1;
+select * from tmp1;
+a
+10
+select * from tmp2;
+a
+1
+2
+create temporary table tmp3 like t1;
+create or replace temporary table tmp3 like t1;
+alter table tmp2 add column (b int);
+select * from tmp2;
+a	b
+1	NULL
+2	NULL
+drop table tmp1,tmp2,tmp3;
+# Clean up test connection
+disconnect con1;
+connection default;
+# Execute some commands as root that should not be logged
+optimize table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+repair table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+# Changes to temporary tables created under readonly should not
+# be logged
+create temporary table tmp4 (a int) engine=myisam;
+insert into tmp4 values (1),(2);
+create temporary table tmp5 (a int) engine=myisam;
+insert into tmp5 select * from tmp4;
+alter table tmp5 add column (b int);
+set global read_only=0;
+insert into tmp4 values (3),(4);
+insert into tmp5 values (10,3),(11,4);
+select * from tmp4;
+a
+1
+2
+3
+4
+select * from tmp5;
+a	b
+1	NULL
+2	NULL
+10	3
+11	4
+update tmp4 set a=10 where a=2;
+delete from tmp4 where a=1;
+create table t2 select * from tmp4;
+alter table tmp5 add column (c int);
+insert into tmp5 values (20,5,1),(21,5,2);
+select * from tmp5;
+a	b	c
+1	NULL	NULL
+2	NULL	NULL
+10	3	NULL
+11	4	NULL
+20	5	1
+21	5	2
+drop table tmp4,tmp5;
+# Check what is logged. Only the last create select should be row-logged
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL
+)
+master-bin.000001	#	Annotate_rows	#	#	create table t2 select * from tmp4
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+master-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+# Clean up
+drop user test@localhost;
+drop table t1,t2;
diff --git a/mysql-test/suite/binlog/t/read_only.inc b/mysql-test/suite/binlog/t/read_only.inc
new file mode 100644
index 00000000000..75cd4a5dfb3
--- /dev/null
+++ b/mysql-test/suite/binlog/t/read_only.inc
@@ -0,0 +1,74 @@
+--echo #
+--echo # MDEV-17863 DROP TEMPORARY TABLE creates a transaction in
+--echo # binary log on read only server
+--echo # MDEV-19074 Improved read_only mode for slaves with
+--echo # gtid_strict_mode enabled
+--echo #
+
+create user test@localhost;
+grant CREATE, DROP, INSERT, SELECT on *.* to test@localhost;
+create table t1 (a int) engine=myisam;
+insert into t1 values (1),(2);
+reset master;
+
+set global read_only=1;
+--echo # Ensure that optimize and analyze doesn't log to binary log
+connect (con1,localhost,test,,test);
+--error ER_OPTION_PREVENTS_STATEMENT
+insert into t1 values(3);
+analyze table t1;
+check table t1;
+repair table t1;
+--error ER_OPTION_PREVENTS_STATEMENT
+optimize table t1;
+
+--echo # Ensure that using temporary tables is not logged
+create temporary table tmp1 (a int) engine=myisam;
+insert into tmp1 values (1),(2);
+update tmp1 set a=10 where a=2;
+delete from tmp1 where a=1;
+create temporary table tmp2 select * from t1;
+select * from tmp1;
+select * from tmp2;
+create temporary table tmp3 like t1;
+create or replace temporary table tmp3 like t1;
+alter table tmp2 add column (b int);
+select * from tmp2;
+drop table tmp1,tmp2,tmp3;
+
+--echo # Clean up test connection
+disconnect con1;
+connection default;
+
+--echo # Execute some commands as root that should not be logged
+optimize table t1;
+repair table t1;
+
+--echo # Changes to temporary tables created under readonly should not
+--echo # be logged
+create temporary table tmp4 (a int) engine=myisam;
+insert into tmp4 values (1),(2);
+create temporary table tmp5 (a int) engine=myisam;
+insert into tmp5 select * from tmp4;
+alter table tmp5 add column (b int);
+
+set global read_only=0;
+
+insert into tmp4 values (3),(4);
+insert into tmp5 values (10,3),(11,4);
+select * from tmp4;
+select * from tmp5;
+update tmp4 set a=10 where a=2;
+delete from tmp4 where a=1;
+create table t2 select * from tmp4;
+alter table tmp5 add column (c int);
+insert into tmp5 values (20,5,1),(21,5,2);
+select * from tmp5;
+drop table tmp4,tmp5;
+
+--echo # Check what is logged. Only the last create select should be row-logged
+source include/show_binlog_events.inc;
+
+--echo # Clean up
+drop user test@localhost;
+drop table t1,t2;
diff --git a/mysql-test/suite/binlog/t/read_only.test b/mysql-test/suite/binlog/t/read_only.test
new file mode 100644
index 00000000000..14a4650b36f
--- /dev/null
+++ b/mysql-test/suite/binlog/t/read_only.test
@@ -0,0 +1,2 @@
+--source include/have_binlog_format_mixed_or_row.inc
+--source read_only.inc
diff --git a/mysql-test/suite/binlog/t/read_only_statement.test b/mysql-test/suite/binlog/t/read_only_statement.test
new file mode 100644
index 00000000000..a976854fbb2
--- /dev/null
+++ b/mysql-test/suite/binlog/t/read_only_statement.test
@@ -0,0 +1,2 @@
+--source include/have_binlog_format_statement.inc
+--source read_only.inc