undo_log_tmp_table.test

--source include/have_innodb_max_16k.inc
--source include/big_test.inc

# Avoid CrashReporter popup on Mac
--source include/not_crashrep.inc
# run for page size >= 8k
--disable_warnings
--source include/have_innodb_min_8k.inc
--enable_warnings

####################################################################
# TC to test temp-table undolog changes correctness                #
# Sceanrio covered in single testcase :                            #
# - Tables with row format(redundant,compressed,dynamic,compact    #
# - Table with primary,composite,prefix,secondary INDEX            #
# - Insert/delete/update with transactioons                        #
# - Transaction with COMMIT,rollback,savepoint statements          #
# - Transaction having temporary table and normal table            #
# - Concurrency by execution of two clients creating tables with   #
#    same names                                                    #
# - Inserting data using                                           #
#   - Insert into .. , Load data infile..,insert ignore            #
#   - Insert into .. on duplicate update                           #
# - Check basic delete and upadte [ignore]                         #
# - Check constraints like duplicate key,default value             #
# - Alter ADD COLUMN , ADD PRIMARY KEY                             #
# - Flush Tables, logs command                                     #
# - Vary innodb_rollback_segments & innodb_redo_log_capacity       #
# - Verify rseg message from server log                            #
####################################################################

call mtr.ADD_suppression(".*Resizing redo log.*");
call mtr.ADD_suppression(".*Starting to delete and rewrite redo log files.*");
call mtr.ADD_suppression(".*New redo log files created.*");
# Save initial VALUES of server variable
--disable_query_log
let $innodb_file_per_table_orig=`SELECT @@innodb_file_per_table`;
--enable_query_log

# Create procedure to perform 
# 1. Create temp table with row types , INDEX , sufficent data types
# 2. Perform DML with transaction 
delimiter |;
CREATE PROCEDURE populate_tables(IN id VARCHAR(10))
   begin
   declare n int default 20;
   set global innodb_file_per_table=on;
   DROP TABLE IF EXISTS t1,t2,t3,t4;

   CREATE TEMPORARY TABLE t1_temp(c1 int NOT NULL,
   c2 int NOT NULL,
   c3 char(255) NOT NULL,
   c4 text(600) NOT NULL,
   c5 blob(600) NOT NULL,
   c6 varchar(600) NOT NULL,
   c7 varchar(600) NOT NULL,
   c8 datetime,
   c9 decimal(6,3),
   PRIMARY KEY (c1),
   INDEX (c3,c4(50),c5(50)), 
   INDEX (c2))
   charset latin1 ENGINE=InnoDB ROW_FORMAT=redundant;

   set @s = concat("CREATE TABLE t1",id," ( c1 int NOT NULL,   c2 int NOT NULL,   c3 char(255) NOT NULL,   c4 text(600) NOT NULL,   c5 blob(600) NOT NULL,   c6 varchar(600) NOT NULL,   c7 varchar(600) NOT NULL,   c8 datetime,   c9 decimal(6,3),   PRIMARY KEY (c1),   INDEX (c3,c4(50),c5(50)),   INDEX (c2))   charset latin1 ENGINE=InnoDB ROW_FORMAT=redundant;");
   PREPARE createTable FROM @s;
   EXECUTE createTable;
   DEALLOCATE PREPARE createTable;


   CREATE TEMPORARY TABLE t2_temp(c1 int NOT NULL,
   c2 int NOT NULL,
   c3 char(255) NOT NULL,
   c4 text(600) NOT NULL,
   c5 blob(600) NOT NULL,
   c6 varchar(600) NOT NULL,
   c7 varchar(600) NOT NULL,
   c8 datetime,
   c9 decimal(6,3),
   PRIMARY KEY (c1),
   INDEX (c3,c4(50),c5(50)),
   INDEX (c2))
   charset latin1 ENGINE=InnoDB ROW_FORMAT=compact;

   set @s = concat("CREATE TABLE t2",id," (c1 int NOT NULL,   c2 int NOT NULL,   c3 char(255) NOT NULL,   c4 text(600) NOT NULL,   c5 blob(600) NOT NULL,   c6 varchar(600) NOT NULL,   c7 varchar(600) NOT NULL,   c8 datetime,   c9 decimal(6,3),   PRIMARY KEY (c1),   INDEX (c3,c4(50),c5(50)),   INDEX (c2))   charset latin1 ENGINE=InnoDB ROW_FORMAT=compact;");
   PREPARE createTable FROM @s;
   EXECUTE createTable;
   DEALLOCATE PREPARE createTable;

   set @s = concat("CREATE TABLE t3",id," (c1 int NOT NULL,   c2 int NOT NULL,   c3 char(255) NOT NULL,   c4 text(600) NOT NULL,   c5 blob(600) NOT NULL,   c6 varchar(600) NOT NULL,   c7 varchar(600) NOT NULL,   c8 datetime,   c9 decimal(6,3),   PRIMARY KEY (c1),   INDEX (c3,c4(50),c5(50)),   INDEX (c2))   charset latin1 ENGINE=InnoDB ROW_FORMAT=compressed key_block_size=4;");
   PREPARE createTable FROM @s;
   EXECUTE createTable;
   DEALLOCATE PREPARE createTable;

   CREATE TEMPORARY TABLE t4_temp(c1 int NOT NULL,
   c2 int NOT NULL,
   c3 char(255) NOT NULL,
   c4 text(600) NOT NULL,
   c5 blob(600) NOT NULL,
   c6 varchar(600) NOT NULL,
   c7 varchar(600) NOT NULL,
   c8 datetime,
   c9 decimal(6,3),
   PRIMARY KEY (c1),
   INDEX (c3,c4(50),c5(50)),
   INDEX (c2))
   charset latin1 ENGINE=InnoDB ROW_FORMAT=dynamic;

   set @s = concat("CREATE TABLE t4",id," (c1 int NOT NULL,   c2 int NOT NULL,   c3 char(255) NOT NULL,   c4 text(600) NOT NULL,   c5 blob(600) NOT NULL,   c6 varchar(600) NOT NULL,   c7 varchar(600) NOT NULL,   c8 datetime,   c9 decimal(6,3),   PRIMARY KEY (c1),   INDEX (c3,c4(50),c5(50)),   INDEX (c2))   charset latin1 ENGINE=InnoDB ROW_FORMAT=dynamic;");
   PREPARE createTable FROM @s;
   EXECUTE createTable;
   DEALLOCATE PREPARE createTable;

   while (n > 0) do
     START TRANSACTION;
     set @s = concat("INSERT INTO t1",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30),     REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70),     REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70),     NOW(),(100.55+",n,"));");     
     PREPARE insertIntoTable FROM @s;     
     EXECUTE insertIntoTable;     
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t1_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
     REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
     REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
     NOW(),(100.55+n));

     set @s = concat("INSERT INTO t2",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30),     REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70),     REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70),     NOW(),(100.55+",n,"));");     
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;

     INSERT INTO t2_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
     REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
     REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
     NOW(),(100.55+n));

     savepoint a;

     set @s = concat("INSERT INTO t3",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30),     REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70),     REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70),     NOW(),(100.55+",n,"));");     
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;

     savepoint b;

     set @s = concat("INSERT INTO t4",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30),     REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70),     REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70),     NOW(),(100.55+",n,"));");     
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;

     INSERT INTO t4_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
     REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
     REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
     NOW(),(100.55+n));


     if (n > 10) then
        if (n > 10 and n <=12) then
           ROLLBACK TO SAVEPOINT a;
           COMMIT;
        end if;
        if (n > 12 and n < 15) then
           ROLLBACK TO SAVEPOINT b;
           COMMIT;
        end if;
        if (n > 15) then
           COMMIT;
        end if;
        
     else     
        if (n > 5) then
           START TRANSACTION;
           DELETE FROM t1_temp WHERE c1 > 10 ;
           DELETE FROM t2_temp WHERE c1 > 10 ;
           DELETE FROM t4_temp WHERE c1 > 10 ;
 
           rollback;
           START TRANSACTION;
           update t1_temp set c1 = c1 + 1000 WHERE c1 > 10;
           update t2_temp set c1 = c1 + 1000 WHERE c1 > 10;
           update t4_temp set c1 = c1 + 1000 WHERE c1 > 10;
           rollback;
        end if;
     end if;    

     if (n < 5) then
        rollback; 
     end if;
 
     FLUSH logs;
     ALTER TABLE t1_temp DROP PRIMARY KEY;
     ALTER TABLE t1_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
     ALTER TABLE t2_temp DROP PRIMARY KEY;
     ALTER TABLE t2_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
     ALTER TABLE t4_temp DROP PRIMARY KEY;
     ALTER TABLE t4_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
     FLUSH tables;

     START TRANSACTION;
     set @s = concat("INSERT INTO t1",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t1_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     set @s = concat("INSERT INTO t2",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t2_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     set @s = concat("INSERT INTO t3",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     set @s = concat("INSERT INTO t4",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t4_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));


        DELETE FROM t1_temp WHERE c1 between 100 and 110;
        DELETE FROM t2_temp WHERE c1 between 100 and 110;
        DELETE FROM t4_temp WHERE c1 between 100 and 110;

        update t1_temp set c1 = c1+1 WHERE c1>110; 
        update t2_temp set c1 = c1+1 WHERE c1>110; 
        update t4_temp set c1 = c1+1 WHERE c1>110; 

        savepoint a;

     set @s = concat("INSERT INTO t1",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t1_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     set @s = concat("INSERT INTO t2",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t2_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     set @s = concat("INSERT INTO t3",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     set @s = concat("INSERT INTO t4",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t4_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     savepoint b;

     set @s = concat("INSERT INTO t1",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t1_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     set @s = concat("INSERT INTO t2",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t2_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     set @s = concat("INSERT INTO t3",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     set @s = concat("INSERT INTO t4",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30),        REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70),        REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60),        NOW(),(100.55+",n,"+100));");
     PREPARE insertIntoTable FROM @s;
     EXECUTE insertIntoTable;
     DEALLOCATE PREPARE insertIntoTable;
     INSERT INTO t4_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
        REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
        REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
        NOW(),(100.55+n+100));
     savepoint c;
     rollback to b;
     rollback to a;
     COMMIT;
     COMMIT;
     rollback;
     set n = n - 1; 
     end while;
end|
delimiter ;|

# Create two client for concurrent execution
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

--echo #---client 1 : dml operation ---"
connection con1;
-- disable_query_log
eval set global innodb_file_per_table=$innodb_file_per_table_orig;

-- enable_query_log
-- disable_query_log
# call procedure
--send call populate_tables('_1');
-- enable_query_log

--echo #---client 2 : dml operation ---"
connection con2;
-- disable_query_log
eval set global innodb_file_per_table=$innodb_file_per_table_orig;
-- enable_query_log
-- disable_query_log
# call procedure
--send call populate_tables('_2');

-- enable_query_log

# check data of client connection 1
--echo # In connection 1
connection con1;
--reap
# 20 rows exepceted in 5 tables
SELECT count(*) FROM t1_1;
SELECT count(*) FROM t2_1;
SELECT count(*) FROM t3_1;
SELECT count(*) FROM t4_1;
SELECT c1 FROM t1_1;
SELECT c1 FROM t2_1;
SELECT c1 FROM t3_1;
SELECT c1 FROM t4_1;
SELECT count(*) FROM t1_temp;
SELECT count(*) FROM t2_temp;
SELECT count(*) FROM t4_temp;
SELECT c1 FROM t1_temp;
SELECT c1 FROM t2_temp;
SELECT c1 FROM t4_temp;
# check data of client connection 2
--echo # In connection 2
connection con2;
--reap
# 20 rows exepceted in 5 tables
SELECT count(*) FROM t1_2;
SELECT count(*) FROM t2_2;
SELECT count(*) FROM t3_2;
SELECT count(*) FROM t4_2;
SELECT c1 FROM t1_2;
SELECT c1 FROM t2_2;
SELECT c1 FROM t3_2;
SELECT c1 FROM t4_2;
SELECT count(*) FROM t1_temp;
SELECT count(*) FROM t2_temp;
SELECT count(*) FROM t4_temp;
SELECT c1 FROM t1_temp;
SELECT c1 FROM t2_temp;
SELECT c1 FROM t4_temp;

--echo # In connection 1
connection con1;

set AUTOCOMMIT = 0;
ALTER TABLE t1_temp DROP PRIMARY KEY;
ALTER TABLE t1_temp ADD PRIMARY KEY (c1);
ALTER TABLE t2_temp DROP PRIMARY KEY;
ALTER TABLE t2_temp ADD PRIMARY KEY (c1);
ALTER TABLE t4_temp DROP PRIMARY KEY;
ALTER TABLE t4_temp ADD PRIMARY KEY (c1);
# Check duplicate key constraint + insert ignore
--error ER_DUP_ENTRY
INSERT INTO t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
insert ignore into t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
--error ER_DUP_ENTRY
INSERT INTO t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
insert ignore into t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
--error ER_DUP_ENTRY
INSERT INTO t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
insert ignore into t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);

# check rollback due to duplicate value in second record of insert
--error ER_DUP_ENTRY
INSERT INTO t1_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
(20,1,'a','a','a','a','a',NOW(),100.55);
--error ER_DUP_ENTRY
INSERT INTO t2_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
(20,1,'a','a','a','a','a',NOW(),100.55);
--error ER_DUP_ENTRY
INSERT INTO t4_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
(20,1,'a','a','a','a','a',NOW(),100.55);

set AUTOCOMMIT = 1;

SELECT c1,c2 FROM t1_temp WHERE c1 in (20,1);
SELECT c1,c2 FROM t2_temp WHERE c1 in (20,1);
SELECT c1,c2 FROM t4_temp WHERE c1 in (20,1);

#replace statement
REPLACE INTO t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
REPLACE INTO t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
REPLACE INTO t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
# verify row is replaced FROM (20,20) to (20,1)
SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t1_temp WHERE c1 = 20;
SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t2_temp WHERE c1 = 20;
SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t4_temp WHERE c1 = 20;

# Update ignore. statement is gonored as 20 value exits 
update ignore t1_temp set c1 = 20 WHERE c1 = 140 ;
update ignore t2_temp set c1 = 20 WHERE c1 = 140 ;
update ignore t4_temp set c1 = 20 WHERE c1 = 140 ;
# see record 140 is present as last update ignored
SELECT count(*) FROM t1_temp WHERE c1 = 140;
SELECT count(*) FROM t2_temp WHERE c1 = 140;
SELECT count(*) FROM t4_temp WHERE c1 = 140;

# Alter table to ADD COLUMN and PRIMARY KEY
ALTER TABLE t1_temp ADD COLUMN c10 int default 99 , 
ADD COLUMN c11 varchar(100) default 'test';
ALTER TABLE t1_temp DROP PRIMARY KEY;
ALTER TABLE t1_temp ADD PRIMARY KEY (c1);
INSERT INTO t1_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES (-1,-1,'a','a','a','a','a',NOW(),100.55);
SELECT c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 FROM t1_temp WHERE c1 < 0;
SELECT count(*) FROM t1_temp WHERE c10 = 99 and c11 like 'test';
# insert on duplicate key update
INSERT INTO t1_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES (-1,-1,'a','a','a','a','a',NOW(),100.55) 
ON DUPLICATE KEY UPDATE c1=-2,c2=-2;
SELECT c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 FROM t1_temp WHERE c1 < 0;

#

#cleanup
DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
disconnect con1;

connection con2;
DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
disconnect con2;


connection default;
#
## trying with VALUES innodb_rollback_segments, innodb_redo_log_capacity
##
let $restart_parameters = restart: --innodb_rollback_segments=20 --innodb_redo_log_capacity=20M;
--source include/restart_mysqld.inc

# Create two client for concurrent execution
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
#
#
connection con1;
--send call populate_tables('_1');
connection con2;
--send call populate_tables('_2');
--echo "#connection 1 - verify tables"
connection con1;
--reap
SELECT count(*) FROM t1_1;
SELECT count(*) FROM t2_1;
SELECT count(*) FROM t3_1;
SELECT count(*) FROM t4_1;
SELECT c1 FROM t1_1;
SELECT c1 FROM t2_1;
SELECT c1 FROM t3_1;
SELECT c1 FROM t4_1;
SELECT count(*) FROM t1_temp;
SELECT count(*) FROM t2_temp;
SELECT count(*) FROM t4_temp;
SELECT c1 FROM t1_temp;
SELECT c1 FROM t2_temp;
SELECT c1 FROM t4_temp;
DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
disconnect con1;
--echo "#connection 2 - verify tables"
connection con2;
--reap
SELECT count(*) FROM t1_2;
SELECT count(*) FROM t2_2;
SELECT count(*) FROM t3_2;
SELECT count(*) FROM t4_2;
SELECT c1 FROM t1_2;
SELECT c1 FROM t2_2;
SELECT c1 FROM t3_2;
SELECT c1 FROM t4_2;
SELECT count(*) FROM t1_temp;
SELECT count(*) FROM t2_temp;
SELECT count(*) FROM t4_temp;
SELECT c1 FROM t1_temp;
SELECT c1 FROM t2_temp;
SELECT c1 FROM t4_temp;
DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
disconnect con2;

connection default;
# innodb_rollback_segments > non redo rsegment
let $restart_parameters = restart: --innodb_rollback_segments=30 --innodb_redo_log_capacity=20M;
--source include/restart_mysqld.inc

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
--send call populate_tables('_1');
connection con2;
--send call populate_tables('_2');
--echo "#connection 1 - verify tables"
connection con1;
--reap
SELECT count(*) FROM t1_1;
SELECT count(*) FROM t2_1;
SELECT count(*) FROM t3_1;
SELECT count(*) FROM t4_1;
SELECT c1 FROM t1_1;
SELECT c1 FROM t2_1;
SELECT c1 FROM t3_1;
SELECT c1 FROM t4_1;
SELECT count(*) FROM t1_temp;
SELECT count(*) FROM t2_temp;
SELECT count(*) FROM t4_temp;
SELECT c1 FROM t1_temp;
SELECT c1 FROM t2_temp;
SELECT c1 FROM t4_temp;
DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
disconnect con1;
--echo "#connection 2 - verify tables"
connection con2;
--reap
SELECT count(*) FROM t1_2;
SELECT count(*) FROM t2_2;
SELECT count(*) FROM t3_2;
SELECT count(*) FROM t4_2;
SELECT c1 FROM t1_2;
SELECT c1 FROM t2_2;
SELECT c1 FROM t3_2;
SELECT c1 FROM t4_2;
SELECT count(*) FROM t1_temp;
SELECT count(*) FROM t2_temp;
SELECT count(*) FROM t4_temp;
SELECT c1 FROM t1_temp;
SELECT c1 FROM t2_temp;
SELECT c1 FROM t4_temp;
DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
disconnect con2;

#

connection default;
DROP PROCEDURE populate_tables;

SHOW TABLES;

-- disable_query_log
eval set global innodb_file_per_table=$innodb_file_per_table_orig;
-- enable_query_log

--let $restart_parameters= restart:
--source include/restart_mysqld_no_echo.inc