00001 # 00002 # Simple test of the serial event log for UPDATE statements 00003 # 00004 # We create a table and insert some records 00005 # into it. We then update the table. 00006 # 00007 # 00008 00009 --disable_warnings 00010 DROP TABLE IF EXISTS t1; 00011 --enable_warnings 00012 00013 CREATE TABLE t1 ( 00014 id INT NOT NULL 00015 , padding VARCHAR(200) NOT NULL 00016 , PRIMARY KEY (id) 00017 ); 00018 00019 INSERT INTO t1 VALUES (1, "I love testing."); 00020 INSERT INTO t1 VALUES (2, "I hate testing."); 00021 00022 # Simple PK update 00023 UPDATE t1 SET padding= "XXX" WHERE id= 1; 00024 00025 # UPDATE all records in table 00026 UPDATE t1 SET padding= "AAA"; 00027 00028 DROP TABLE t1; 00029 00030 # Test for LP Bug#440141: 00031 # 00032 # Replication generates incorrect update commands when 00033 # where clause uses a field contained in set clause 00034 # 00035 CREATE TABLE t1 ( 00036 id int AUTO_INCREMENT NOT NULL PRIMARY KEY 00037 , name varchar(1024) 00038 , alias varchar(1024) 00039 ); 00040 00041 INSERT INTO t1 (name,alias) VALUES ("jeff lebowski","dude"); 00042 00043 UPDATE t1 SET alias = "the dude" WHERE alias = "dude"; 00044 00045 DROP TABLE t1; 00046 00047 # Tests UPDATE statement which changes an existing row 00048 # by referencing the changed field. 00049 00050 CREATE TABLE t1 ( 00051 id INT NOT NULL 00052 , counter INT NOT NULL 00053 , PRIMARY KEY (id) 00054 ); 00055 00056 INSERT INTO t1 (id, counter) VALUES (1,1),(2,2),(3,3); 00057 00058 UPDATE t1 SET counter = counter + 1 WHERE id = 1; 00059 UPDATE t1 SET counter = counter + 1 WHERE id IN (2,3); 00060 00061 DROP TABLE t1; 00062 00063 # Test for updating a primary key value 00064 # in an UPDATE statement. LP Bug#480710 00065 00066 CREATE TABLE t1 ( 00067 id INT NOT NULL 00068 , padding VARCHAR(200) NOT NULL 00069 , PRIMARY KEY (id) 00070 ); 00071 00072 INSERT INTO t1 VALUES (1, "I love testing."); 00073 INSERT INTO t1 VALUES (2, "I hate testing."); 00074 00075 UPDATE t1 SET id = 4 WHERE id = 2; 00076 00077 DROP TABLE t1;