00001 # 00002 # Simple test of the transaction log for testing REPLACE command 00003 # 00004 # We create a table then fill it with a few records and then 00005 # issue a few REPLACE statements on it. 00006 # 00007 00008 --disable_warnings 00009 DROP TABLE IF EXISTS t1, t2; 00010 --enable_warnings 00011 00012 CREATE TABLE t1 ( 00013 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 00014 , padding VARCHAR(200) NOT NULL 00015 ) ENGINE=InnoDB; 00016 00017 INSERT INTO t1 VALUES (1, "I love testing."); 00018 INSERT INTO t1 VALUES (2, "I hate testing."); 00019 00020 # This will actually execute an UPDATE for InnoDB, 00021 # as this is an optimized scenario that can have the 00022 # REPLACE INTO converted into an INSERT ... ON DUPLICATE 00023 # KEY UPDATE. 00024 00025 REPLACE INTO t1 VALUE (2, "I love testing."); 00026 00027 DROP TABLE t1; 00028 00029 CREATE TABLE t1 ( 00030 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 00031 , padding VARCHAR(200) NOT NULL 00032 ) ENGINE=InnoDB; 00033 CREATE TABLE t2 ( 00034 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 00035 , fk_id INT NOT NULL 00036 , CONSTRAINT fk_t1 FOREIGN KEY (fk_id) REFERENCES t1 (id) ON DELETE CASCADE 00037 ) ENGINE=InnoDB; 00038 00039 INSERT INTO t1 VALUES (1, "I love testing."); 00040 INSERT INTO t1 VALUES (2, "I hate testing."); 00041 00042 # Should delete original and insert a new one 00043 # with a different "padding" column value... 00044 00045 REPLACE INTO t1 VALUE (2, "I love testing."); 00046 00047 DROP TABLE t2, t1; 00048 00049 # 00050 # Test when using replace on table with multiple unique keys 00051 # and a primary key of various data types. This tests code 00052 # that, during a REPLACE, deletes multiple conflicting rows. 00053 # (FYI, In the transaction log, there should be 2 DELETEs 00054 # and 1 UPDATE for each of these REPLACE statements.) 00055 # 00056 00057 create table t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c)); 00058 insert into t1 values (1,"a",1,1),(2,"b",2,2),(3,"c",3,3); 00059 replace into t1 values (1,"b",3,4); 00060 select * from t1 order by a; 00061 drop table t1; 00062 00063 create table t1 (a CHAR(5) NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c)); 00064 insert into t1 values ("a","a",1,1),("bb","b",2,2),("ccc","c",3,3); 00065 replace into t1 values ("a","b",3,4); 00066 select * from t1 order by a; 00067 drop table t1; 00068 00069 create table t1 (a DATE NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c)); 00070 insert into t1 values ("2010-01-01","a",1,1),("2010-02-02","b",2,2),("2010-03-03","c",3,3); 00071 replace into t1 values ("2010-01-01","b",3,4); 00072 select * from t1 order by a; 00073 drop table t1; 00074 00075 create table t1 (a DOUBLE NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c)); 00076 insert into t1 values (1.1,"a",1,1),(22.22,"b",2,2),(333.333,"c",3,3); 00077 replace into t1 values (1.1,"b",3,4); 00078 select * from t1 order by a; 00079 drop table t1; 00080 00081 create table t1 (a FLOAT NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c)); 00082 insert into t1 values (1.1,"a",1,1),(22.22,"b",2,2),(333.333,"c",3,3); 00083 replace into t1 values (1.1,"b",3,4); 00084 select * from t1 order by a; 00085 drop table t1; 00086 00087 create table t1 (a ENUM("a","bb","ccc") NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c)); 00088 insert into t1 values ("a","a",1,1),("bb","b",2,2),("ccc","c",3,3); 00089 replace into t1 values ("a","b",3,4); 00090 select * from t1 order by a; 00091 drop table t1;