mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
129 lines
3.7 KiB
Text
129 lines
3.7 KiB
Text
#
|
|
# 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 TEMPORARY TABLES, 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 Engine-independent statistics collected
|
|
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
|
|
insert into t1 select a+100 from tmp2;
|
|
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
|
|
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
|
|
insert into t1 select a+200 from tmp5;
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
201
|
|
202
|
|
210
|
|
211
|
|
220
|
|
221
|
|
drop table tmp4,tmp5;
|
|
# Check what is logged. Only last create select and the insert...select's 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
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Annotate_rows # # insert into t1 select a+200 from tmp5
|
|
master-bin.000001 # Table_map # # table_id: # (test.t1)
|
|
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;
|