--source include/have_debug.inc
--source include/have_binlog_format_row.inc
--source include/master-slave.inc
--disable_ps_protocol

call mtr.add_suppression("table already exists in recycle_bin");
call mtr.add_suppression("Fail to recycle table");

connection master;
# Set up some variables
LET $MYSQLD_DATADIR = `select @@datadir`;
LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';

connection master;
create database db_recycle;
create database db_recycle_dummy;
create user super_1@'%' identified by 'pass';
create user super_2@'%' identified by 'pass';

create user normal_1@'%' identified by 'pass';
create user normal_2@'%' identified by 'pass';
create user normal_3@'%' identified by 'pass';
create user normal_4@'%' identified by 'pass';
create user normal_5@'%' identified by 'pass';

grant all on *.* to super_1@'%';
grant all on *.* to super_2@'%';

grant all on db_recycle.* to normal_1@'%' ;
grant all on __recycle_bin__.* to normal_1@'%' ;
grant create tablespace on *.* to normal_1@'%' ;
grant SYSTEM_VARIABLES_ADMIN on *.* to normal_1@'%';
grant file on *.* to normal_1@'%' ;

grant all on db_recycle_dummy.* to normal_2@'%' ;

grant all on db_recycle.* to normal_3@'%' ;
grant all on __recycle_bin__.* to normal_3@'%' ;

grant all on db_recycle_dummy.* to normal_4@'%' ;

grant all on __recycle_bin__.* to normal_5@'%' ;

--sync_slave_with_master

connect(m_super_1, 127.0.0.1, super_1, pass, db_recycle, $MASTER_MYPORT);
connect(s_super_2, 127.0.0.1, super_1, pass, db_recycle, $SLAVE_MYPORT);

connect(m_normal_1, 127.0.0.1, normal_1, pass, db_recycle, $MASTER_MYPORT);
connect(m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT);

connect(m_normal_5, 127.0.0.1, normal_5, pass, , $MASTER_MYPORT);

connect(s_normal_3, 127.0.0.1, normal_3, pass, db_recycle, $SLAVE_MYPORT);
connect(s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT);


connection slave;
SET @start_read_only = @@global.read_only;
SET global read_only = true;

--echo ------------------------------------------------------
--echo 1. Privileges
--echo    -- Still require related privileges if want to
--echo       show recycle bin db;
--echo    -- No one can alter db except super_acl user;
--echo ------------------------------------------------------
connection m_normal_1;
show databases;

connection s_normal_3;
show databases;

connection m_normal_2;
show databases;

connection s_normal_4;
show databases;

connection m_normal_1;
use __recycle_bin__;
create table t1 (id int);
drop table t1;
show tables;

--echo 1.1 dbms_recycle.purge_table still require db.table privileges;

connection m_super_1;
set global recycle_scheduler=off;

connection m_normal_1;
use db_recycle;
create table t1(id int);
insert into t1 values(1);
truncate table t1;

--replace_column 5 # 6 #
call dbms_recycle.show_tables();

show create table t1;
select * from __recycle_bin__.__innodb_165;

connection m_normal_2;
--error ER_TABLEACCESS_DENIED_ERROR
call dbms_recycle.purge_table("__innodb_165");

connection m_normal_5;
--error ER_TABLEACCESS_DENIED_ERROR
call dbms_recycle.purge_table("__innodb_165");

connection m_super_1;
set global recycle_scheduler=on;
--sleep 3

connection m_normal_1;
use db_recycle;
set session recycle_bin=off;
drop table t1;
set session recycle_bin=on;

--echo ------------------------------------------------------
--echo 2. truncate table
--echo    -- Related object:
--echo       Column:
--echo       Index:
--echo       Foreign key:
--echo       Trigger:
--echo       View:
--echo       Constraint:
--echo ------------------------------------------------------

connection m_super_1;
set global recycle_scheduler=off;

connection m_normal_1;
use db_recycle;

CREATE TABLE p1 (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE c1 (
    id INT CHECK (id >= 1),
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES p1(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

create table l1(id int);

delimiter //;
CREATE TRIGGER tri_1
  before INSERT ON c1 FOR EACH ROW
BEGIN
  INSERT INTO l1 value(1);
END//
delimiter  ;//

create view v1 as select * from c1;

--error ER_TRUNCATE_ILLEGAL_FK
truncate table p1;

set foreign_key_checks=off;
truncate table p1;
insert into c1 values(1, 2);
set foreign_key_checks=on;

truncate table c1;

show create table c1;
show create table p1;

--replace_column 5 # 6 #
call dbms_recycle.show_tables();

show create table __recycle_bin__.__innodb_148;
show create table __recycle_bin__.__innodb_161;
select * from __recycle_bin__.__innodb_161;
select * from __recycle_bin__.__innodb_148;

--echo check the valid of view and trigger
insert into p1 values(2);
insert into c1 values(2, 2);
select * from p1;
select * from c1;
select * from v1;
select * from l1;


drop table c1;
drop table l1;
drop view v1;
drop table p1;
connection m_super_1;
set global recycle_scheduler=on;

--sleep 3

--echo ------------------------------------------------------
--echo 3. partition table
--echo ------------------------------------------------------
connection m_super_1;
set global recycle_scheduler=off;

connection m_normal_1;
use db_recycle;

CREATE TABLE t3 (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

truncate table t3;

--replace_column 5 # 6 #
call dbms_recycle.show_tables();

show create table __recycle_bin__.__innodb_167;
show create table t3;
call dbms_recycle.purge_table("__innodb_167");
drop table t3;

connection m_super_1;
set global recycle_scheduler=on;

--sleep 3
connection master;
--sync_slave_with_master

--echo ------------------------------------------------------
--echo 4. general tablespace
--echo ------------------------------------------------------
connection m_super_1;
set global recycle_scheduler=off;

connection m_normal_1;
use db_recycle;

CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
create table t4(id int) tablespace ts1;
truncate table t4;

--replace_column 5 # 6 #
call dbms_recycle.show_tables();

show create table __recycle_bin__.__innodb_168;
show create table t4;
call dbms_recycle.purge_table("__innodb_168");

drop table t4;
call dbms_recycle.purge_table("__innodb_168");
connection m_super_1;
set global recycle_scheduler=on;

--sleep 3
connection master;
--sync_slave_with_master

connection m_normal_1;
drop tablespace ts1;

--echo ------------------------------------------------------
--echo 5. Table options:
--echo      key_block_size
--echo      encrypt
--echo      data dir
--echo ------------------------------------------------------

connection m_super_1;
set global recycle_scheduler=off;

connection m_normal_1;
use db_recycle;
set session sql_log_bin=off;

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval create table t5 (a int, b int) $data_directory_clause;

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
show create table t5;

truncate table t5;

--replace_column 5 # 6 #
call dbms_recycle.show_tables();

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
show create table __recycle_bin__.__innodb_169;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
show create table t5;
call dbms_recycle.purge_table("__innodb_169");

drop table t5;
call dbms_recycle.purge_table("__innodb_169");

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t6 (a INT, b INT)
ENGINE = InnoDB key_block_size = 8
  PARTITION BY RANGE(a) SUBPARTITION BY KEY(b) (
    PARTITION tens VALUES LESS THAN (100)
      (SUBPARTITION subpart11,
       SUBPARTITION subpart12),
    PARTITION hundreds VALUES LESS THAN (1000)
      (SUBPARTITION subpart21,
       SUBPARTITION subpart22),
    PARTITION thousands VALUES LESS THAN (10000)
      (SUBPARTITION subpart31 DATA DIRECTORY '$MYSQL_TMP_DIR',
       SUBPARTITION subpart32 DATA DIRECTORY '$MYSQL_TMP_DIR' TABLESPACE `innodb_file_per_table`));

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
show create table t6;

truncate table t6;

--replace_column 5 # 6 #
call dbms_recycle.show_tables();

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
show create table __recycle_bin__.__innodb_170;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
show create table t6;
call dbms_recycle.purge_table("__innodb_170");

drop table t6;
call dbms_recycle.purge_table("__innodb_170");

connection m_normal_1;
set session sql_log_bin=on;


connection m_super_1;
set global recycle_scheduler=on;

--sleep 3

connection master;
drop database db_recycle;
drop database db_recycle_dummy;
drop user super_1@'%';
drop user super_2@'%';
drop user normal_1@'%';
drop user normal_2@'%';
drop user normal_3@'%';
drop user normal_4@'%';
drop user normal_5@'%';

connection slave;
set global read_only = @start_read_only;

connection master;
--sync_slave_with_master
--source include/rpl_end.inc
--enable_ps_protocol

--rmdir $MYSQL_TMP_DIR/alt_dir/__recycle_bin__
--rmdir $MYSQL_TMP_DIR/alt_dir/db_recycle
--rmdir $MYSQL_TMP_DIR/alt_dir
--rmdir $MYSQL_TMP_DIR/__recycle_bin__
--rmdir $MYSQL_TMP_DIR/db_recycle