diff options
Diffstat (limited to 'mysql-test')
45 files changed, 1317 insertions, 783 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_row_delayed_ins.test b/mysql-test/extra/rpl_tests/rpl_row_delayed_ins.test index c4e6dbc84c2..214027160a9 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_delayed_ins.test +++ b/mysql-test/extra/rpl_tests/rpl_row_delayed_ins.test @@ -15,7 +15,6 @@ SELECT * FROM t1 ORDER BY a; sync_slave_with_master; connection master; -source include/show_binlog_events.inc; sync_slave_with_master; SELECT * FROM t1 ORDER BY a; connection master; diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index eb8d942ab6e..b83f40626aa 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -326,9 +326,9 @@ sub mtr_report_stats ($) { /Slave SQL:.*(?:Error_code: \d+|Query:.*)/ or /Sort aborted/ or /Time-out in NDB/ or - /Warning:\s+One can only use the --user.*root/ or - /Warning:\s+Setting lower_case_table_names=2/ or - /Warning:\s+Table:.* on (delete|rename)/ or + /One can only use the --user.*root/ or + /Setting lower_case_table_names=2/ or + /Table:.* on (delete|rename)/ or /You have an error in your SQL syntax/ or /deprecated/ or /description of time zone/ or diff --git a/mysql-test/r/ctype_cp932_binlog_row.result b/mysql-test/r/ctype_cp932_binlog_row.result index 39d7727b58a..0370b7a1cf6 100644 --- a/mysql-test/r/ctype_cp932_binlog_row.result +++ b/mysql-test/r/ctype_cp932_binlog_row.result @@ -9,8 +9,10 @@ EXECUTE stmt1 USING @var1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1(f1 blob) +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT SELECT HEX(f1) FROM t1; HEX(f1) 8300 diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index b2a9eb04c04..15e4c3f15b3 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -682,3 +682,26 @@ a a b 1 1 3 DROP TABLE t1; End of 5.0 tests +CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, +PRIMARY KEY(a,b,c,d,e), +KEY(a,b,d,c) +); +INSERT INTO t1(a, b, c) VALUES (1, 1, 1), +(1, 1, 2), +(1, 1, 3), +(1, 2, 1), +(1, 2, 2), +(1, 2, 3); +EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 16 NULL 6 Using index +SELECT DISTINCT a, b, d, c FROM t1; +a b d c +1 1 0 1 +1 1 0 2 +1 1 0 3 +1 2 0 1 +1 2 0 2 +1 2 0 3 +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 268f290ddca..30c4d1e4ca1 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1128,6 +1128,152 @@ id c1 c2 4 2 3 1 5 1 DROP TABLE t1; +# +# Bug#27219: Aggregate functions in ORDER BY. +# +SET @save_sql_mode=@@sql_mode; +SET @@sql_mode='ONLY_FULL_GROUP_BY'; +CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0); +INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4); +CREATE TABLE t2 SELECT * FROM t1; +SELECT 1 FROM t1 ORDER BY COUNT(*); +1 +1 +SELECT 1 FROM t1 ORDER BY COUNT(*) + 1; +1 +1 +SELECT 1 FROM t1 ORDER BY COUNT(*) + a; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT 1 FROM t1 ORDER BY COUNT(*), 1; +1 +1 +SELECT 1 FROM t1 ORDER BY COUNT(*), a; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT 1 FROM t1 ORDER BY SUM(a); +1 +1 +SELECT 1 FROM t1 ORDER BY SUM(a + 1); +1 +1 +SELECT 1 FROM t1 ORDER BY SUM(a) + 1; +1 +1 +SELECT 1 FROM t1 ORDER BY SUM(a), b; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT a FROM t1 ORDER BY COUNT(b); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2); +a +3 +2 +3 +2 +3 +4 +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 +WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 GROUP BY t1.a +HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 GROUP BY t1.a +HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); +a +2 +3 +4 +SELECT t1.a FROM t1 GROUP BY t1.a +HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 GROUP BY t1.a +HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 +WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT 1 FROM t1 GROUP BY t1.a +HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +1 +1 +1 +1 +SELECT 1 FROM t1 GROUP BY t1.a +HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +1 +1 +1 +1 +SELECT 1 FROM t1 GROUP BY t1.a +HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +1 +1 +1 +1 +SELECT 1 FROM t1 GROUP BY t1.a +HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT 1 FROM t1 GROUP BY t1.a +HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT 1 FROM t1 GROUP BY t1.a +HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT t1.a FROM t1 +WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a +ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1); +a +4 +SELECT t1.a, SUM(t1.b) FROM t1 +WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a +ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1) +GROUP BY t1.a; +a SUM(t1.b) +4 4 +SELECT t1.a, SUM(t1.b) FROM t1 +WHERE t1.a = (SELECT SUM(t2.b) FROM t2 +ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1) +GROUP BY t1.a; +a SUM(t1.b) +SELECT t1.a, SUM(t1.b) FROM t1 +WHERE t1.a = (SELECT SUM(t2.b) FROM t2 +ORDER BY SUM(t2.b + t1.a) LIMIT 1) +GROUP BY t1.a; +a SUM(t1.b) +SELECT t1.a FROM t1 GROUP BY t1.a +HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1); +a +select avg ( +(select +(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt +from t1 as outr order by outr.a limit 1)) +from t1 as most_outer; +avg ( +(select +(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt +from t1 as outr order by outr.a limit 1)) +29.0000 +select avg ( +(select ( +(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt +from t1 as outr order by count(outr.a) limit 1)) as tt +from t1 as most_outer; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a; +tt +29 +29 +35 +35 +35 +41 +SET sql_mode=@save_sql_mode; +DROP TABLE t1, t2; End of 5.0 tests CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 98e221b0dea..3a23d9de6b3 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -261,6 +261,82 @@ SELECT * FROM t1; c1 c2 c3 c4 10 1970-02-01 01:02:03 1.1e-100 1.1e+100 DROP TABLE t1; + +# -- +# -- Bug#35469: server crash with LOAD DATA INFILE to a VIEW. +# -- + +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP VIEW IF EXISTS v2; +DROP VIEW IF EXISTS v3; + +CREATE TABLE t1(c1 INT, c2 VARCHAR(255)); + +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1; +CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2; + +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v1 +FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (c1, c2); + +SELECT * FROM t1; +c1 c2 +1 "string1" +2 "string2" +3 "string3" + +SELECT * FROM v1; +c1 c2 +1 "string1" +2 "string2" +3 "string3" + +DELETE FROM t1; + +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2 +FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (c1, c2); + +SELECT * FROM t1; +c1 c2 +1 "string1" +2 "string2" +3 "string3" + +SELECT * FROM v2; +c0 c1 c2 +3 1 "string1" +3 2 "string2" +3 3 "string3" + +DELETE FROM t1; + +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2 +FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (c0, c2); +ERROR HY000: Invalid column reference (v2.c0) in LOAD DATA + +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v3 +FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (d1, d2); +ERROR HY000: The target table v3 of the LOAD is not updatable + +DROP TABLE t1; +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; + +# -- End of Bug#35469. CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1); SET NAMES latin1; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 4320a7764de..faab8e49880 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1428,3 +1428,33 @@ set session max_sort_length= 2180; select * from t1 order by b; ERROR HY001: Out of sort memory; increase server sort buffer size drop table t1; +CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, +UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); +CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); +CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b)); +INSERT INTO t3 SELECT * FROM t1; +EXPLAIN +SELECT d FROM t1, t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b b 4 const 4 Using where; Using temporary; Using filesort +1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using where +SELECT d FROM t1, t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; +d +52.5 +EXPLAIN +SELECT d FROM t3 AS t1, t2 AS t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where +1 SIMPLE t1 ref a a 39 test.t2.a,const 10 Using where; Using index +SELECT d FROM t3 AS t1, t2 AS t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; +d +52.5 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index f67a8f4cae6..cc5e8d2be96 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1166,6 +1166,45 @@ EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 35 NULL 3 Using where; Using index DROP TABLE t1; +CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (127),(254),(0),(1),(255); +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255; +COUNT(*) +4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1; +COUNT(*) +0 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1; +COUNT(*) +5 +DROP TABLE t1; +CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127); +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127; +COUNT(*) +4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0; +COUNT(*) +5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128; +COUNT(*) +4 +DROP TABLE t1; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, b int, filler char(100)); diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 1c846700346..6df09463d02 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -184,3 +184,14 @@ select * from t1; a 42 drop table t1; +CREATE TEMPORARY TABLE t1(a INT, b VARCHAR(20)); +INSERT INTO t1 VALUES(1, 'val1'), (2, 'val2'), (3, 'val3'); +DELETE FROM t1 WHERE a=1; +SELECT count(*) FROM t1; +count(*) +2 +DELETE FROM t1; +SELECT * FROM t1; +a b +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/std_data/bug35469.dat b/mysql-test/std_data/bug35469.dat new file mode 100644 index 00000000000..afcd9e9cc95 --- /dev/null +++ b/mysql-test/std_data/bug35469.dat @@ -0,0 +1,3 @@ +"1", "string1" +"2", "string2" +"3", "string3" diff --git a/mysql-test/suite/binlog/r/binlog_base64_flag.result b/mysql-test/suite/binlog/r/binlog_base64_flag.result index 557f584fcc1..72a16010031 100644 --- a/mysql-test/suite/binlog/r/binlog_base64_flag.result +++ b/mysql-test/suite/binlog/r/binlog_base64_flag.result @@ -1,3 +1,4 @@ +DROP TABLE IF EXISTS t1; ==== Test BUG#32407 ==== select * from t1; a diff --git a/mysql-test/suite/binlog/r/binlog_multi_engine.result b/mysql-test/suite/binlog/r/binlog_multi_engine.result index 8574a2351a1..ffe7915f1f8 100644 --- a/mysql-test/suite/binlog/r/binlog_multi_engine.result +++ b/mysql-test/suite/binlog/r/binlog_multi_engine.result @@ -78,8 +78,10 @@ UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c; ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1m) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1n) master-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status) diff --git a/mysql-test/suite/binlog/r/binlog_row_binlog.result b/mysql-test/suite/binlog/r/binlog_row_binlog.result index 8da441d5865..f830b69bf9d 100644 --- a/mysql-test/suite/binlog/r/binlog_row_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result @@ -1085,9 +1085,11 @@ show binlog events from 0; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 4 Format_desc 1 106 Server version, Binlog ver: 4 master-bin.000001 106 Query 1 227 use `test`; create table t1 (a bigint unsigned, b bigint(20) unsigned) -master-bin.000001 227 Table_map 1 269 table_id: # (test.t1) -master-bin.000001 269 Write_rows 1 315 table_id: # flags: STMT_END_F -master-bin.000001 315 Query 1 391 use `test`; drop table t1 +master-bin.000001 227 Query 1 295 use `test`; BEGIN +master-bin.000001 295 Table_map 1 337 table_id: # (test.t1) +master-bin.000001 337 Write_rows 1 383 table_id: # flags: STMT_END_F +master-bin.000001 383 Query 1 452 use `test`; COMMIT +master-bin.000001 452 Query 1 528 use `test`; drop table t1 End of 5.0 tests reset master; create table t1 (id tinyint auto_increment primary key); @@ -1111,8 +1113,10 @@ use test; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key) +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; drop table t1 master-bin.000001 # Query # # use `test`; create table t1 (a int) master-bin.000001 # Query # # use `test`; BEGIN @@ -1123,12 +1127,18 @@ master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t3` ( `a` int(11) DEFAULT NULL ) +master-bin.000001 # Query # # use `mysql`; BEGIN master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysql`; COMMIT +master-bin.000001 # Query # # use `mysql`; BEGIN master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysql`; COMMIT +master-bin.000001 # Query # # use `mysql`; BEGIN master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysql`; COMMIT drop table t1,t2,t3,tt1; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; @@ -1138,8 +1148,10 @@ insert delayed into t1 values (300); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key) +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; drop table t1 master-bin.000001 # Query # # use `test`; create table t1 (a int) master-bin.000001 # Query # # use `test`; BEGIN @@ -1150,20 +1162,32 @@ master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t3` ( `a` int(11) DEFAULT NULL ) +master-bin.000001 # Query # # use `mysql`; BEGIN master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysql`; COMMIT +master-bin.000001 # Query # # use `mysql`; BEGIN master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysql`; COMMIT +master-bin.000001 # Query # # use `mysql`; BEGIN master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysql`; COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2`,`t3` /* generated by server */ master-bin.000001 # Query # # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT insert delayed into t1 values (null),(null),(null),(null); insert delayed into t1 values (null),(null),(400),(null); 11 == 11 diff --git a/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result b/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result index 4f4e7bcedd7..a066928fc13 100644 --- a/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result +++ b/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result @@ -5,13 +5,30 @@ reset master; insert into t2 values (@v); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT flush logs; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; +use test/*!*/; +SET TIMESTAMP=10000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=10000/*!*/; +COMMIT +/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; diff --git a/mysql-test/suite/binlog/r/binlog_row_insert_select.result b/mysql-test/suite/binlog/r/binlog_row_insert_select.result index cd6ddafc47b..d4370c4de12 100644 --- a/mysql-test/suite/binlog/r/binlog_row_insert_select.result +++ b/mysql-test/suite/binlog/r/binlog_row_insert_select.result @@ -8,8 +8,10 @@ insert into t1 select * from t2; ERROR 23000: Duplicate entry '2' for key 'a' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; ROLLBACK select * from t1; a 1 diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index d26913da2b2..419aea5b581 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -117,8 +117,10 @@ master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT delete from t1; delete from t2; reset master; @@ -131,8 +133,10 @@ master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT insert into t1 values(11); commit; show binlog events from <binlog_start>; @@ -141,8 +145,10 @@ master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F @@ -272,14 +278,20 @@ master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; drop table t1,t2 master-bin.000001 # Query # # use `test`; create table t0 (n int) +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t0) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t0) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; create table t2 (n int) engine=innodb do release_lock("lock1"); drop table t0,t2; @@ -362,38 +374,52 @@ a b DROP TABLE t1,t2; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE if exists t2 +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t2 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Query # # use `test`; TRUNCATE table t2 master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; BEGIN -master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE t2 +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Query # # use `test`; TRUNCATE table t2 master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ reset master; create table t1 (a int) engine=innodb; @@ -489,9 +515,11 @@ insert into t2 values (bug27417(2)); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; ROLLBACK /* only (!) with fixes for #23333 will show there is the query */; select count(*) from t1 /* must be 3 */; count(*) @@ -506,9 +534,11 @@ count(*) 2 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT /* the query must be in regardless of #23333 */; select count(*) from t1 /* must be 5 */; count(*) @@ -564,11 +594,13 @@ update t3 set b=b+bug27417(1); ERROR 23000: Duplicate entry '4' for key 'b' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # master-bin.000001 # Update_rows # # table_id: # master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; ROLLBACK /* the output must denote there is the query */; select count(*) from t1 /* must be 2 */; count(*) diff --git a/mysql-test/suite/binlog/r/binlog_stm_binlog.result b/mysql-test/suite/binlog/r/binlog_stm_binlog.result index 54a2ceda9b3..20d7281d5cc 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result @@ -648,12 +648,18 @@ master-bin.000001 # Query # # use `mysql`; UPDATE user SET password=password('An master-bin.000001 # Query # # use `mysql`; DELETE FROM user WHERE host='localhost' AND user='@#@' master-bin.000001 # Query # # use `test`; drop table t1,t2,t3,tt1 master-bin.000001 # Query # # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT insert delayed into t1 values (null),(null),(null),(null); insert delayed into t1 values (null),(null),(400),(null); 11 == 11 diff --git a/mysql-test/suite/binlog/t/binlog_base64_flag.test b/mysql-test/suite/binlog/t/binlog_base64_flag.test index a25c6b68420..abf35a6bfd8 100644 --- a/mysql-test/suite/binlog/t/binlog_base64_flag.test +++ b/mysql-test/suite/binlog/t/binlog_base64_flag.test @@ -9,6 +9,9 @@ # BINLOG statement does not work in embedded mode. source include/not_embedded.inc; +disable_warnings; +DROP TABLE IF EXISTS t1; +enable_warnings; # Test to show BUG#32407. This reads a binlog created with the # mysql-5.1-telco-6.1 tree, specifically at the tag diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result index ad67e3ecf80..da8151262d1 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result @@ -434,7 +434,7 @@ Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1364 -Last_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 262 +Last_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # @@ -452,7 +452,7 @@ Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1364 -Last_SQL_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 262 +Last_SQL_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; *** Create t10 on slave *** diff --git a/mysql-test/suite/rpl/r/rpl_rbr_to_sbr.result b/mysql-test/suite/rpl/r/rpl_rbr_to_sbr.result index 1c9d8701232..8f145b94046 100644 --- a/mysql-test/suite/rpl/r/rpl_rbr_to_sbr.result +++ b/mysql-test/suite/rpl/r/rpl_rbr_to_sbr.result @@ -18,8 +18,10 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Format_desc 1 # Server ver: VERSION, Binlog ver: 4 master-bin.000001 # Query 1 # use `test`; CREATE TABLE t1 (a INT, b LONG) master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 # Query 1 # use `test`; BEGIN master-bin.000001 # Table_map 1 # table_id: # (test.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; COMMIT **** On Slave **** SHOW SLAVE STATUS; Slave_IO_State # @@ -28,7 +30,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 457 +Read_Master_Log_Pos 594 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 @@ -43,7 +45,7 @@ Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 -Exec_Master_Log_Pos 457 +Exec_Master_Log_Pos 594 Relay_Log_Space # Until_Condition None Until_Log_File @@ -66,6 +68,8 @@ slave-bin.000001 # Format_desc 2 # Server ver: VERSION, Binlog ver: 4 slave-bin.000001 # Query 2 # use `test`; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9 slave-bin.000001 # Query 1 # use `test`; CREATE TABLE t1 (a INT, b LONG) slave-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +slave-bin.000001 # Query 1 # use `test`; BEGIN slave-bin.000001 # Table_map 1 # table_id: # (test.t1) slave-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +slave-bin.000001 # Query 1 # use `test`; COMMIT DROP TABLE IF EXISTS t1; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result b/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result index f49c23248e5..670456ba6fd 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result @@ -27,8 +27,10 @@ INSERT INTO t2 VALUES (3,3), (4,4); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT) +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT **** On Slave **** SHOW DATABASES; Database @@ -56,8 +58,10 @@ SHOW BINLOG EVENTS; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 4 Format_desc 1 106 Server ver: SERVER_VERSION, Binlog ver: 4 master-bin.000001 106 Query 1 192 use `test`; CREATE TABLE t1 (a INT) -master-bin.000001 192 Table_map 1 233 table_id: # (test.t1) -master-bin.000001 233 Write_rows 1 272 table_id: # flags: STMT_END_F +master-bin.000001 192 Query 1 260 use `test`; BEGIN +master-bin.000001 260 Table_map 1 301 table_id: # (test.t1) +master-bin.000001 301 Write_rows 1 340 table_id: # flags: STMT_END_F +master-bin.000001 340 Query 1 409 use `test`; COMMIT DROP TABLE t1; ================ Test for BUG#17620 ================ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result index a8011756bbb..ebfb576c42d 100644 --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result @@ -140,8 +140,10 @@ a b SHOW BINLOG EVENTS FROM 1374; Log_name Pos Event_type Server_id End_log_pos Info # 1374 Query # 1474 use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE) -# 1474 Table_map # 1516 table_id: # (test.t7) -# 1516 Write_rows # 1572 table_id: # flags: STMT_END_F +# 1474 Query # 1542 use `test`; BEGIN +# 1542 Table_map # 1584 table_id: # (test.t7) +# 1584 Write_rows # 1640 table_id: # flags: STMT_END_F +# 1640 Query # 1711 use `test`; ROLLBACK SELECT * FROM t7 ORDER BY a,b; a b 1 2 @@ -154,10 +156,12 @@ INSERT INTO t7 SELECT a,b FROM tt4; ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back -SHOW BINLOG EVENTS FROM 1572; +SHOW BINLOG EVENTS FROM 1711; Log_name Pos Event_type Server_id End_log_pos Info -# 1572 Table_map # 1614 table_id: # (test.t7) -# 1614 Write_rows # 1670 table_id: # flags: STMT_END_F +# 1711 Query # 1779 use `test`; BEGIN +# 1779 Table_map # 1821 table_id: # (test.t7) +# 1821 Write_rows # 1877 table_id: # flags: STMT_END_F +# 1877 Query # 1946 use `test`; COMMIT SELECT * FROM t7 ORDER BY a,b; a b 1 2 @@ -192,10 +196,10 @@ Create Table CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -SHOW BINLOG EVENTS FROM 1670; +SHOW BINLOG EVENTS FROM 1946; Log_name Pos Event_type Server_id End_log_pos Info -# 1670 Query # 1756 use `test`; CREATE TABLE t8 LIKE t4 -# 1756 Query # 1895 use `test`; CREATE TABLE `t9` ( +# 1946 Query # 2032 use `test`; CREATE TABLE t8 LIKE t4 +# 2032 Query # 2171 use `test`; CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) @@ -276,31 +280,35 @@ SHOW BINLOG EVENTS; Log_name Pos Event_type Server_id End_log_pos Info # 4 Format_desc # 106 Server ver: #, Binlog ver: # # 106 Query # 192 use `test`; CREATE TABLE t1 (a INT) -# 192 Table_map # 233 table_id: # (test.t1) -# 233 Write_rows # 277 table_id: # flags: STMT_END_F -# 277 Query # 345 use `test`; BEGIN -# 345 Query # 470 use `test`; CREATE TABLE `t2` ( +# 192 Query # 260 use `test`; BEGIN +# 260 Table_map # 301 table_id: # (test.t1) +# 301 Write_rows # 345 table_id: # flags: STMT_END_F +# 345 Query # 414 use `test`; COMMIT +# 414 Query # 482 use `test`; BEGIN +# 482 Query # 607 use `test`; CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB -# 470 Table_map # 511 table_id: # (test.t2) -# 511 Write_rows # 555 table_id: # flags: STMT_END_F -# 555 Xid # 582 COMMIT /* XID */ -# 582 Query # 650 use `test`; BEGIN -# 650 Query # 775 use `test`; CREATE TABLE `t3` ( +# 607 Table_map # 648 table_id: # (test.t2) +# 648 Write_rows # 692 table_id: # flags: STMT_END_F +# 692 Xid # 719 COMMIT /* XID */ +# 719 Query # 787 use `test`; BEGIN +# 787 Query # 912 use `test`; CREATE TABLE `t3` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB -# 775 Table_map # 816 table_id: # (test.t3) -# 816 Write_rows # 860 table_id: # flags: STMT_END_F -# 860 Xid # 887 COMMIT /* XID */ -# 887 Query # 955 use `test`; BEGIN -# 955 Query # 1080 use `test`; CREATE TABLE `t4` ( +# 912 Table_map # 953 table_id: # (test.t3) +# 953 Write_rows # 997 table_id: # flags: STMT_END_F +# 997 Xid # 1024 COMMIT /* XID */ +# 1024 Query # 1092 use `test`; BEGIN +# 1092 Query # 1217 use `test`; CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB -# 1080 Table_map # 1121 table_id: # (test.t4) -# 1121 Write_rows # 1165 table_id: # flags: STMT_END_F -# 1165 Xid # 1192 COMMIT /* XID */ -# 1192 Table_map # 1233 table_id: # (test.t1) -# 1233 Write_rows # 1277 table_id: # flags: STMT_END_F +# 1217 Table_map # 1258 table_id: # (test.t4) +# 1258 Write_rows # 1302 table_id: # flags: STMT_END_F +# 1302 Xid # 1329 COMMIT /* XID */ +# 1329 Query # 1397 use `test`; BEGIN +# 1397 Table_map # 1438 table_id: # (test.t1) +# 1438 Write_rows # 1482 table_id: # flags: STMT_END_F +# 1482 Query # 1551 use `test`; COMMIT SHOW TABLES; Tables_in_test t1 @@ -367,15 +375,17 @@ SHOW BINLOG EVENTS; Log_name Pos Event_type Server_id End_log_pos Info # 4 Format_desc # 106 Server ver: #, Binlog ver: # # 106 Query # 192 use `test`; CREATE TABLE t1 (a INT) -# 192 Table_map # 233 table_id: # (test.t1) -# 233 Write_rows # 277 table_id: # flags: STMT_END_F -# 277 Query # 377 use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB -# 377 Query # 445 use `test`; BEGIN -# 445 Table_map # 486 table_id: # (test.t2) -# 486 Write_rows # 530 table_id: # flags: STMT_END_F -# 530 Table_map # 571 table_id: # (test.t2) -# 571 Write_rows # 610 table_id: # flags: STMT_END_F -# 610 Xid # 637 COMMIT /* XID */ +# 192 Query # 260 use `test`; BEGIN +# 260 Table_map # 301 table_id: # (test.t1) +# 301 Write_rows # 345 table_id: # flags: STMT_END_F +# 345 Query # 414 use `test`; COMMIT +# 414 Query # 514 use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB +# 514 Query # 582 use `test`; BEGIN +# 582 Table_map # 623 table_id: # (test.t2) +# 623 Write_rows # 667 table_id: # flags: STMT_END_F +# 667 Table_map # 708 table_id: # (test.t2) +# 708 Write_rows # 747 table_id: # flags: STMT_END_F +# 747 Xid # 774 COMMIT /* XID */ SELECT * FROM t2 ORDER BY a; a 1 @@ -396,17 +406,14 @@ Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT * FROM t2 ORDER BY a; a -SHOW BINLOG EVENTS FROM 637; +SHOW BINLOG EVENTS FROM 949; Log_name Pos Event_type Server_id End_log_pos Info -# 637 Query # 705 use `test`; BEGIN -# 705 Query # 785 use `test`; TRUNCATE TABLE t2 -# 785 Xid # 812 COMMIT /* XID */ -# 812 Query # 880 use `test`; BEGIN -# 880 Table_map # 921 table_id: # (test.t2) -# 921 Write_rows # 965 table_id: # flags: STMT_END_F -# 965 Table_map # 1006 table_id: # (test.t2) -# 1006 Write_rows # 1045 table_id: # flags: STMT_END_F -# 1045 Query # 1116 use `test`; ROLLBACK +# 949 Query # 1017 use `test`; BEGIN +# 1017 Table_map # 1058 table_id: # (test.t2) +# 1058 Write_rows # 1102 table_id: # flags: STMT_END_F +# 1102 Table_map # 1143 table_id: # (test.t2) +# 1143 Write_rows # 1182 table_id: # flags: STMT_END_F +# 1182 Query # 1253 use `test`; ROLLBACK SELECT * FROM t2 ORDER BY a; a DROP TABLE t1,t2; diff --git a/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result b/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result index 800a39bd567..90b68428008 100644 --- a/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result +++ b/mysql-test/suite/rpl/r/rpl_row_delayed_ins.result @@ -14,16 +14,6 @@ a 1 2 3 -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; create table t1(a int not null primary key) engine=myisam -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `test`; flush tables SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/suite/rpl/r/rpl_row_flsh_tbls.result b/mysql-test/suite/rpl/r/rpl_row_flsh_tbls.result index 072f15cbbd3..09626754428 100644 --- a/mysql-test/suite/rpl/r/rpl_row_flsh_tbls.result +++ b/mysql-test/suite/rpl/r/rpl_row_flsh_tbls.result @@ -12,13 +12,13 @@ create table t4 (a int); insert into t4 select * from t3; rename table t1 to t5, t2 to t1; flush no_write_to_binlog tables; -SHOW BINLOG EVENTS FROM 623 ; +SHOW BINLOG EVENTS FROM 897 ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `test`; rename table t1 to t5, t2 to t1 select * from t3; a flush tables; -SHOW BINLOG EVENTS FROM 623 ; +SHOW BINLOG EVENTS FROM 897 ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `test`; rename table t1 to t5, t2 to t1 master-bin.000001 # Query 1 # use `test`; flush tables diff --git a/mysql-test/suite/rpl/r/rpl_row_inexist_tbl.result b/mysql-test/suite/rpl/r/rpl_row_inexist_tbl.result index 70e923bc4f4..71c825183f7 100644 --- a/mysql-test/suite/rpl/r/rpl_row_inexist_tbl.result +++ b/mysql-test/suite/rpl/r/rpl_row_inexist_tbl.result @@ -24,7 +24,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 605 +Read_Master_Log_Pos 1153 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 @@ -39,7 +39,7 @@ Replicate_Wild_Ignore_Table Last_Errno 1146 Last_Error Error 'Table 'test.t1' doesn't exist' on opening table `test`.`t1` Skip_Counter 0 -Exec_Master_Log_Pos 530 +Exec_Master_Log_Pos 941 Relay_Log_Space # Until_Condition None Until_Log_File diff --git a/mysql-test/suite/rpl/r/rpl_row_log.result b/mysql-test/suite/rpl/r/rpl_row_log.result index 33a6c3b9781..af46d6af9c1 100644 --- a/mysql-test/suite/rpl/r/rpl_row_log.result +++ b/mysql-test/suite/rpl/r/rpl_row_log.result @@ -20,22 +20,26 @@ show binlog events; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Format_desc 1 # Server ver: VERSION, Binlog ver: 4 master-bin.000001 # Query 1 # use `test`; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM +master-bin.000001 # Query 1 # use `test`; BEGIN master-bin.000001 # Table_map 1 # table_id: # (test.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; COMMIT master-bin.000001 # Query 1 # use `test`; drop table t1 master-bin.000001 # Query 1 # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM +master-bin.000001 # Query 1 # use `test`; BEGIN master-bin.000001 # Table_map 1 # table_id: # (test.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Query 1 # use `test`; COMMIT show binlog events from 106 limit 1; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `test`; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM show binlog events from 106 limit 2; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `test`; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM -master-bin.000001 # Table_map 1 # table_id: # (test.t1) +master-bin.000001 # Query 1 # use `test`; BEGIN show binlog events from 106 limit 2,1; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map 1 # table_id: # (test.t1) flush logs; create table t3 (a int)ENGINE=MyISAM; select * from t1 order by 1 asc; @@ -192,47 +196,59 @@ insert into t2 values (1); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; drop table t1 master-bin.000001 # Query # # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Rotate # # master-bin.000002;pos=4 show binlog events in 'master-bin.000002'; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000002 # Format_desc 1 # Server ver: VERSION, Binlog ver: 4 master-bin.000002 # Query 1 # use `test`; create table t3 (a int)ENGINE=MyISAM master-bin.000002 # Query 1 # use `test`; create table t2 (n int)ENGINE=MyISAM +master-bin.000002 # Query 1 # use `test`; BEGIN master-bin.000002 # Table_map 1 # table_id: # (test.t2) master-bin.000002 # Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000002 # Query 1 # use `test`; COMMIT show binary logs; Log_name File_size -master-bin.000001 1266 -master-bin.000002 379 +master-bin.000001 1540 +master-bin.000002 516 start slave; show binary logs; Log_name File_size -slave-bin.000001 1364 -slave-bin.000002 280 +slave-bin.000001 1638 +slave-bin.000002 417 show binlog events in 'slave-bin.000001' from 4; Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000001 # Format_desc 2 # Server ver: VERSION, Binlog ver: 4 slave-bin.000001 # Query 1 # use `test`; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM +slave-bin.000001 # Query 1 # use `test`; BEGIN slave-bin.000001 # Table_map 1 # table_id: # (test.t1) slave-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +slave-bin.000001 # Query 1 # use `test`; COMMIT slave-bin.000001 # Query 1 # use `test`; drop table t1 slave-bin.000001 # Query 1 # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM +slave-bin.000001 # Query 1 # use `test`; BEGIN slave-bin.000001 # Table_map 1 # table_id: # (test.t1) slave-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F +slave-bin.000001 # Query 1 # use `test`; COMMIT slave-bin.000001 # Query 1 # use `test`; create table t3 (a int)ENGINE=MyISAM slave-bin.000001 # Rotate 2 # slave-bin.000002;pos=4 show binlog events in 'slave-bin.000002' from 4; Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000002 # Format_desc 2 # Server ver: VERSION, Binlog ver: 4 slave-bin.000002 # Query 1 # use `test`; create table t2 (n int)ENGINE=MyISAM +slave-bin.000002 # Query 1 # use `test`; BEGIN slave-bin.000002 # Table_map 1 # table_id: # (test.t2) slave-bin.000002 # Write_rows 1 # table_id: # flags: STMT_END_F +slave-bin.000002 # Query 1 # use `test`; COMMIT SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 @@ -240,7 +256,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000002 -Read_Master_Log_Pos 379 +Read_Master_Log_Pos 516 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000002 @@ -255,7 +271,7 @@ Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 -Exec_Master_Log_Pos 379 +Exec_Master_Log_Pos 516 Relay_Log_Space # Until_Condition None Until_Log_File @@ -284,8 +300,10 @@ set insert_id=5; insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id()); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT select * from t1; a b 1 1 diff --git a/mysql-test/suite/rpl/r/rpl_row_log_innodb.result b/mysql-test/suite/rpl/r/rpl_row_log_innodb.result index e08c47558a0..dc0f9545bc4 100644 --- a/mysql-test/suite/rpl/r/rpl_row_log_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_log_innodb.result @@ -300,8 +300,10 @@ set insert_id=5; insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id()); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT select * from t1; a b 1 1 diff --git a/mysql-test/suite/rpl/r/rpl_row_until.result b/mysql-test/suite/rpl/r/rpl_row_until.result index 383d1a03a22..f544ef95702 100644 --- a/mysql-test/suite/rpl/r/rpl_row_until.result +++ b/mysql-test/suite/rpl/r/rpl_row_until.result @@ -26,7 +26,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 750 +Read_Master_Log_Pos 1161 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 @@ -41,7 +41,7 @@ Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 -Exec_Master_Log_Pos 317 +Exec_Master_Log_Pos 454 Relay_Log_Space # Until_Condition Master Until_Log_File master-bin.000001 @@ -72,7 +72,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 750 +Read_Master_Log_Pos 1161 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 @@ -87,7 +87,7 @@ Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 -Exec_Master_Log_Pos 317 +Exec_Master_Log_Pos 454 Relay_Log_Space # Until_Condition Master Until_Log_File master-no-such-bin.000001 @@ -104,7 +104,7 @@ Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 0 Last_SQL_Error -start slave until relay_log_file='slave-relay-bin.000004', relay_log_pos=728; +start slave until relay_log_file='slave-relay-bin.000004', relay_log_pos=1014; select * from t2; n 1 @@ -116,7 +116,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 750 +Read_Master_Log_Pos 1161 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 @@ -131,11 +131,11 @@ Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 -Exec_Master_Log_Pos 594 +Exec_Master_Log_Pos 868 Relay_Log_Space # Until_Condition Relay Until_Log_File slave-relay-bin.000004 -Until_Log_Pos 728 +Until_Log_Pos 1014 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path @@ -158,7 +158,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 750 +Read_Master_Log_Pos 1161 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 @@ -173,7 +173,7 @@ Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 -Exec_Master_Log_Pos 750 +Exec_Master_Log_Pos 1161 Relay_Log_Space # Until_Condition Master Until_Log_File master-bin.000001 diff --git a/mysql-test/suite/rpl/r/rpl_slave_skip.result b/mysql-test/suite/rpl/r/rpl_slave_skip.result index f89fa34e319..bb03c3b6cca 100644 --- a/mysql-test/suite/rpl/r/rpl_slave_skip.result +++ b/mysql-test/suite/rpl/r/rpl_slave_skip.result @@ -17,14 +17,20 @@ show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT) master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (c INT, d INT) +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Update_rows # # table_id: # master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT SELECT * FROM t1; a b 1 1 @@ -36,7 +42,7 @@ c d 2 16 3 54 **** On Slave **** -START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=484; +START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=762; SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 @@ -44,7 +50,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 722 +Read_Master_Log_Pos 1133 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 @@ -59,11 +65,11 @@ Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 -Exec_Master_Log_Pos 488 +Exec_Master_Log_Pos 762 Relay_Log_Space # Until_Condition Master Until_Log_File master-bin.000001 -Until_Log_Pos 484 +Until_Log_Pos 762 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path diff --git a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result index cae8492abbd..7447d12f9b2 100644 --- a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result +++ b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result @@ -425,673 +425,5 @@ INSERT INTO t13 VALUES (USER()); INSERT INTO t13 VALUES (my_user()); INSERT INTO t13 VALUES (CURRENT_USER()); INSERT INTO t13 VALUES (my_current_user()); -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # drop database if exists mysqltest1 -master-bin.000001 # Query # # create database mysqltest1 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t1 (a varchar(100)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_8_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_7_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_9_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_7_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("for_10_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_11_" -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_13_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_12_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_14_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_12_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("for_15_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_16_" -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_18_") -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_17_' -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_21_" -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_17_' -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_24_" -master-bin.000001 # Query # # use `mysqltest1`; BEGIN -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t2` ( - `rpad(UUID(),100,' ')` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; COMMIT -master-bin.000001 # Query # # use `mysqltest1`; BEGIN -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t3` ( - `1` varbinary(108) NOT NULL DEFAULT '' -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t3) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; COMMIT -master-bin.000001 # Query # # use `mysqltest1`; BEGIN -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t4` ( - `a` varchar(100) DEFAULT NULL -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t4) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; COMMIT -master-bin.000001 # Query # # use `mysqltest1`; create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t5) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo() -begin -insert into t1 values("work_25_"); -insert into t1 values(concat("for_26_",UUID())); -insert into t1 select "yesterday_27_"; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2() -begin -insert into t1 values(concat("emergency_28_",UUID())); -insert into t1 values("work_29_"); -insert into t1 values(concat("for_30_",UUID())); -set session binlog_format=row; # accepted for stored procs -insert into t1 values("more work_31_"); -set session binlog_format=mixed; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned -begin -set session binlog_format=row; # rejected for stored funcs -insert into t1 values("alarm"); -return 100; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo4(x varchar(100)) -begin -insert into t1 values(concat("work_250_",x)); -insert into t1 select "yesterday_270_"; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(concat("work_250_", NAME_CONST('x',_latin1'hello'))) -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_270_" -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(concat("work_250_", NAME_CONST('x',_latin1'world'))) -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_270_" -master-bin.000001 # Query # # use `mysqltest1`; drop function foo3 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned -begin -insert into t1 values("foo3_32_"); -call foo(); -return 100; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo4() returns bigint unsigned -begin -insert into t2 select foo3(); -return 100; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo5() returns bigint unsigned -begin -insert into t2 select UUID(); -return 100; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo6(x varchar(100)) returns bigint unsigned -begin -insert into t2 select x; -return 100; -end -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`foo6`(_latin1'foo6_1_') -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select uuid() -master-bin.000001 # Query # # use `mysqltest1`; create table t11 (data varchar(255)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t11) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') -master-bin.000001 # Query # # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') -master-bin.000001 # Query # # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger t11_bi before insert on t11 for each row -begin -set NEW.data = concat(NEW.data,UUID()); -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t11) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; create table t20 select * from t1 -master-bin.000001 # Query # # use `mysqltest1`; create table t21 select * from t2 -master-bin.000001 # Query # # use `mysqltest1`; create table t22 select * from t3 -master-bin.000001 # Query # # use `mysqltest1`; drop table t1,t2,t3 -master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; create table t2 (a int primary key auto_increment, b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; create table t3 (b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f (x varchar(100)) returns int deterministic -begin -insert into t1 values(null,x); -insert into t2 values(null,x); -return 1; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Intvar # # INSERT_ID=3 -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(null,"try_44_") -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; create table t12 select * from t1 -master-bin.000001 # Query # # use `mysqltest1`; drop table t1 -master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int, b varchar(100), key(a)) -master-bin.000001 # Intvar # # INSERT_ID=4 -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`f`(_latin1'try_45_') -master-bin.000001 # Query # # use `mysqltest1`; create table t13 select * from t1 -master-bin.000001 # Query # # use `mysqltest1`; drop table t1 -master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; drop function f -master-bin.000001 # Query # # use `mysqltest1`; create table t14 (unique (a)) select * from t2 -master-bin.000001 # Query # # use `mysqltest1`; truncate table t2 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f1 (x varchar(100)) returns int deterministic -begin -insert into t1 values(null,x); -return 1; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic -begin -insert into t2 values(null,x); -return 1; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t3) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; drop function f2 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic -begin -declare y int; -insert into t1 values(null,x); -set y = (select count(*) from t2); -return y; -end -master-bin.000001 # Intvar # # INSERT_ID=4 -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`f1`(_latin1'try_53_') -master-bin.000001 # Intvar # # INSERT_ID=5 -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`f2`(_latin1'try_54_') -master-bin.000001 # Query # # use `mysqltest1`; drop function f2 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row -begin -insert into t2 values(null,"try_55_"); -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; alter table t1 modify a int, drop primary key -master-bin.000001 # Intvar # # INSERT_ID=5 -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(null,"try_57_") -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t16` ( - `UUID()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT '' -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t16) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t16) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t16 values("try_66_") -master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t11 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t11 (song VARCHAR(255)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t11) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; INSERT INTO t11 VALUES('Careful With That Axe, Eugene') -master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t12 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t12 (data LONG) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t12) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_user() -RETURNS CHAR(64) -BEGIN -DECLARE user CHAR(64); -SELECT USER() INTO user; -RETURN user; -END -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_current_user() -RETURNS CHAR(64) -BEGIN -DECLARE user CHAR(64); -SELECT CURRENT_USER() INTO user; -RETURN user; -END -master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t13 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t13 (data CHAR(64)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # drop database if exists mysqltest1 -master-bin.000001 # Query # # create database mysqltest1 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t1 (a varchar(100)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_8_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_7_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_9_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_7_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("for_10_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_11_" -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_13_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_12_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_14_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_12_' -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("for_15_") -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_16_" -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values("work_18_") -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_17_' -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_21_" -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select 'emergency_17_' -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_24_" -master-bin.000001 # Query # # use `mysqltest1`; BEGIN -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t2` ( - `rpad(UUID(),100,' ')` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; COMMIT -master-bin.000001 # Query # # use `mysqltest1`; BEGIN -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t3` ( - `1` varbinary(108) NOT NULL DEFAULT '' -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t3) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; COMMIT -master-bin.000001 # Query # # use `mysqltest1`; BEGIN -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t4` ( - `a` varchar(100) DEFAULT NULL -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t4) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; COMMIT -master-bin.000001 # Query # # use `mysqltest1`; create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t5) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo() -begin -insert into t1 values("work_25_"); -insert into t1 values(concat("for_26_",UUID())); -insert into t1 select "yesterday_27_"; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2() -begin -insert into t1 values(concat("emergency_28_",UUID())); -insert into t1 values("work_29_"); -insert into t1 values(concat("for_30_",UUID())); -set session binlog_format=row; # accepted for stored procs -insert into t1 values("more work_31_"); -set session binlog_format=mixed; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned -begin -set session binlog_format=row; # rejected for stored funcs -insert into t1 values("alarm"); -return 100; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo4(x varchar(100)) -begin -insert into t1 values(concat("work_250_",x)); -insert into t1 select "yesterday_270_"; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(concat("work_250_", NAME_CONST('x',_latin1'hello'))) -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_270_" -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(concat("work_250_", NAME_CONST('x',_latin1'world'))) -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 select "yesterday_270_" -master-bin.000001 # Query # # use `mysqltest1`; drop function foo3 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned -begin -insert into t1 values("foo3_32_"); -call foo(); -return 100; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo4() returns bigint unsigned -begin -insert into t2 select foo3(); -return 100; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo5() returns bigint unsigned -begin -insert into t2 select UUID(); -return 100; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo6(x varchar(100)) returns bigint unsigned -begin -insert into t2 select x; -return 100; -end -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`foo6`(_latin1'foo6_1_') -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select uuid() -master-bin.000001 # Query # # use `mysqltest1`; create table t11 (data varchar(255)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t11) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') -master-bin.000001 # Query # # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') -master-bin.000001 # Query # # use `mysqltest1`; insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11') -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger t11_bi before insert on t11 for each row -begin -set NEW.data = concat(NEW.data,UUID()); -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t11) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; create table t20 select * from t1 -master-bin.000001 # Query # # use `mysqltest1`; create table t21 select * from t2 -master-bin.000001 # Query # # use `mysqltest1`; create table t22 select * from t3 -master-bin.000001 # Query # # use `mysqltest1`; drop table t1,t2,t3 -master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; create table t2 (a int primary key auto_increment, b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; create table t3 (b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f (x varchar(100)) returns int deterministic -begin -insert into t1 values(null,x); -insert into t2 values(null,x); -return 1; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Intvar # # INSERT_ID=3 -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(null,"try_44_") -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; create table t12 select * from t1 -master-bin.000001 # Query # # use `mysqltest1`; drop table t1 -master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int, b varchar(100), key(a)) -master-bin.000001 # Intvar # # INSERT_ID=4 -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`f`(_latin1'try_45_') -master-bin.000001 # Query # # use `mysqltest1`; create table t13 select * from t1 -master-bin.000001 # Query # # use `mysqltest1`; drop table t1 -master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100)) -master-bin.000001 # Query # # use `mysqltest1`; drop function f -master-bin.000001 # Query # # use `mysqltest1`; create table t14 (unique (a)) select * from t2 -master-bin.000001 # Query # # use `mysqltest1`; truncate table t2 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f1 (x varchar(100)) returns int deterministic -begin -insert into t1 values(null,x); -return 1; -end -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic -begin -insert into t2 values(null,x); -return 1; -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t3) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; drop function f2 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic -begin -declare y int; -insert into t1 values(null,x); -set y = (select count(*) from t2); -return y; -end -master-bin.000001 # Intvar # # INSERT_ID=4 -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`f1`(_latin1'try_53_') -master-bin.000001 # Intvar # # INSERT_ID=5 -master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`f2`(_latin1'try_54_') -master-bin.000001 # Query # # use `mysqltest1`; drop function f2 -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row -begin -insert into t2 values(null,"try_55_"); -end -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t1) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t2) -master-bin.000001 # Write_rows # # table_id: # -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; alter table t1 modify a int, drop primary key -master-bin.000001 # Intvar # # INSERT_ID=5 -master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(null,"try_57_") -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE `t16` ( - `UUID()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT '' -) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t16) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t16) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; insert into t16 values("try_66_") -master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t11 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t11 (song VARCHAR(255)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t11) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; INSERT INTO t11 VALUES('Careful With That Axe, Eugene') -master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t12 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t12 (data LONG) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t12) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_user() -RETURNS CHAR(64) -BEGIN -DECLARE user CHAR(64); -SELECT USER() INTO user; -RETURN user; -END -master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_current_user() -RETURNS CHAR(64) -BEGIN -DECLARE user CHAR(64); -SELECT CURRENT_USER() INTO user; -RETURN user; -END -master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t13 -master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t13 (data CHAR(64)) -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F drop database mysqltest1; set global binlog_format =@my_binlog_format; diff --git a/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result b/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result index 7eee31dab7a..1ae98706975 100644 --- a/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result @@ -114,8 +114,10 @@ show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 master-bin.000001 # Query # # use `test`; DROP TABLE t1 RESET MASTER; @@ -230,9 +232,13 @@ show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE t1 RESET MASTER; diff --git a/mysql-test/suite/rpl/t/rpl_row_create_table.test b/mysql-test/suite/rpl/t/rpl_row_create_table.test index ffddfd2ce4a..bfeb939f30f 100644 --- a/mysql-test/suite/rpl/t/rpl_row_create_table.test +++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test @@ -94,7 +94,7 @@ INSERT INTO t7 SELECT a,b FROM tt4; ROLLBACK; --replace_column 1 # 4 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS FROM 1572; +SHOW BINLOG EVENTS FROM 1711; SELECT * FROM t7 ORDER BY a,b; sync_slave_with_master; SELECT * FROM t7 ORDER BY a,b; @@ -110,7 +110,7 @@ CREATE TEMPORARY TABLE tt7 SELECT 1; --query_vertical SHOW CREATE TABLE t9 --replace_column 1 # 4 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS FROM 1670; +SHOW BINLOG EVENTS FROM 1946; sync_slave_with_master; --echo **** On Slave **** --query_vertical SHOW CREATE TABLE t8 @@ -227,7 +227,7 @@ ROLLBACK; SELECT * FROM t2 ORDER BY a; --replace_column 1 # 4 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS FROM 637; +SHOW BINLOG EVENTS FROM 949; sync_slave_with_master; SELECT * FROM t2 ORDER BY a; diff --git a/mysql-test/suite/rpl/t/rpl_row_flsh_tbls.test b/mysql-test/suite/rpl/t/rpl_row_flsh_tbls.test index bfa356fbfb4..667e1d9a1a8 100644 --- a/mysql-test/suite/rpl/t/rpl_row_flsh_tbls.test +++ b/mysql-test/suite/rpl/t/rpl_row_flsh_tbls.test @@ -1,7 +1,7 @@ # depends on the binlog output -- source include/have_binlog_format_row.inc -let $rename_event_pos= 623; +let $rename_event_pos= 897; # Bug#18326: Do not lock table for writing during prepare of statement # The use of the ps protocol causes extra table maps in the binlog, so diff --git a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test index be76ac9f3f6..192c1e325bf 100644 --- a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test +++ b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test @@ -164,12 +164,12 @@ connection master; # this test for position option -# By setting this position to 413, we should only get the create of t3 +# By setting this position to 416, we should only get the create of t3 --disable_query_log select "--- Test 2 position test --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=416 $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=416 --stop-position=569 $MYSQLTEST_VARDIR/log/master-bin.000001 # These are tests for remote binlog. # They should return the same as previous test. @@ -180,7 +180,7 @@ select "--- Test 3 First Remote test --" as ""; # This is broken now --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --stop-position=569 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 # This part is disabled due to bug #17654 @@ -246,12 +246,17 @@ connection master; --exec rm $MYSQLTEST_VARDIR/tmp/remote.sql ################### End Bug 17654 ###################### +# What is the point of this test? It seems entirely pointless. It +# might make sense for statement-based replication, but for row-based +# replication the LOAD DATA INFILE is printed just as empty +# transactions. /Matz + # LOAD DATA --disable_query_log select "--- Test 5 LOAD DATA --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --stop-position=106 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 # Bug#7853 (mysqlbinlog does not accept input from stdin) @@ -259,13 +264,13 @@ select "--- Test 5 LOAD DATA --" as ""; select "--- Test 6 reading stdin --" as ""; --enable_query_log --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR ---exec $MYSQL_BINLOG --short-form - < $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=569 - < $MYSQLTEST_VARDIR/log/master-bin.000001 --disable_query_log select "--- Test 7 reading stdin w/position --" as ""; --enable_query_log --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR ---exec $MYSQL_BINLOG --short-form --position=416 - < $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --position=416 --stop-position=569 - < $MYSQLTEST_VARDIR/log/master-bin.000001 # Bug#16217 (mysql client did not know how not switch its internal charset) --disable_query_log diff --git a/mysql-test/suite/rpl/t/rpl_row_until.test b/mysql-test/suite/rpl/t/rpl_row_until.test index 610eec305df..bdf7351d9a5 100644 --- a/mysql-test/suite/rpl/t/rpl_row_until.test +++ b/mysql-test/suite/rpl/t/rpl_row_until.test @@ -42,7 +42,7 @@ select * from t1; source include/show_slave_status.inc; # try replicate all up to and not including the second insert to t2; -start slave until relay_log_file='slave-relay-bin.000004', relay_log_pos=728; +start slave until relay_log_file='slave-relay-bin.000004', relay_log_pos=1014; --source include/wait_for_slave_sql_to_stop.inc select * from t2; source include/show_slave_status.inc; diff --git a/mysql-test/suite/rpl/t/rpl_slave_skip.test b/mysql-test/suite/rpl/t/rpl_slave_skip.test index 6783098fd7c..8a5b1ae0ae4 100644 --- a/mysql-test/suite/rpl/t/rpl_slave_skip.test +++ b/mysql-test/suite/rpl/t/rpl_slave_skip.test @@ -26,7 +26,7 @@ save_master_pos; connection slave; # Stop when reaching the the first table map event. -START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=484; +START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=762; wait_for_slave_to_stop; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 # diff --git a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test index adda4f67694..8e6a04104bd 100644 --- a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test +++ b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test @@ -556,7 +556,6 @@ INSERT INTO t13 VALUES (my_user()); INSERT INTO t13 VALUES (CURRENT_USER()); INSERT INTO t13 VALUES (my_current_user()); -source include/show_binlog_events.inc; sync_slave_with_master; # as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID @@ -571,7 +570,6 @@ sync_slave_with_master; diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql; connection master; -source include/show_binlog_events.inc; # Now test that mysqlbinlog works fine on a binlog generated by the # mixed mode diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result index 326506b0396..3bf5ea108ae 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result @@ -316,8 +316,10 @@ set insert_id=5; insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id()); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; COMMIT select * from t1; a b 1 1 diff --git a/mysql-test/t/change_user.test b/mysql-test/t/change_user.test index 685b6272482..f200c76196c 100644 --- a/mysql-test/t/change_user.test +++ b/mysql-test/t/change_user.test @@ -5,20 +5,20 @@ --echo Bug#20023 SELECT @@session.sql_big_selects; # The exact value depends on the server build flags ---replace_result 4294967295 HA_POS_ERROR 18446744073709551615 HA_POS_ERROR +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR SELECT @@global.max_join_size; --echo change_user --change_user SELECT @@session.sql_big_selects; # The exact value depends on the server build flags ---replace_result 4294967295 HA_POS_ERROR 18446744073709551615 HA_POS_ERROR +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR SELECT @@global.max_join_size; SET @@global.max_join_size = 10000; SET @@session.max_join_size = default; --echo change_user --change_user SELECT @@session.sql_big_selects; -# May produce a warning depending on server build flags +# On some machines the following will result into a warning --disable_warnings SET @@global.max_join_size = -1; --enable_warnings diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index bfdb5f8b9f8..559dde44b81 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -553,3 +553,26 @@ SELECT DISTINCT a, a, b FROM t1; DROP TABLE t1; --echo End of 5.0 tests + +# +# Bug #34928: Confusion by having Primary Key and Index +# +CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, + PRIMARY KEY(a,b,c,d,e), + KEY(a,b,d,c) +); + +INSERT INTO t1(a, b, c) VALUES (1, 1, 1), + (1, 1, 2), + (1, 1, 3), + (1, 2, 1), + (1, 2, 2), + (1, 2, 3); + +EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; + +SELECT DISTINCT a, b, d, c FROM t1; + +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 3211db5d6ed..d02e8e10a2b 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -823,6 +823,123 @@ SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC; DROP TABLE t1; +--echo # +--echo # Bug#27219: Aggregate functions in ORDER BY. +--echo # +SET @save_sql_mode=@@sql_mode; +SET @@sql_mode='ONLY_FULL_GROUP_BY'; + +CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0); +INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4); +CREATE TABLE t2 SELECT * FROM t1; + +SELECT 1 FROM t1 ORDER BY COUNT(*); +SELECT 1 FROM t1 ORDER BY COUNT(*) + 1; +--error 1140 +SELECT 1 FROM t1 ORDER BY COUNT(*) + a; +SELECT 1 FROM t1 ORDER BY COUNT(*), 1; +--error 1140 +SELECT 1 FROM t1 ORDER BY COUNT(*), a; + +SELECT 1 FROM t1 ORDER BY SUM(a); +SELECT 1 FROM t1 ORDER BY SUM(a + 1); +SELECT 1 FROM t1 ORDER BY SUM(a) + 1; +--error 1140 +SELECT 1 FROM t1 ORDER BY SUM(a), b; + +--error 1140 +SELECT a FROM t1 ORDER BY COUNT(b); + +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2); + +--error 1140 +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2); +--error 1140 +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a); +--error 1140 +SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); + +--error 1140 +SELECT t1.a FROM t1 + WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); +--error 1140 +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1); + +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); +--error 1140 +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); +--error 1140 +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); + +--error 1140 +SELECT t1.a FROM t1 + WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); + +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); + +--error 1140 +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1); +--error 1140 +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); +--error 1140 +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); + +# Both SUMs are aggregated in the subquery, no mixture: +SELECT t1.a FROM t1 + WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a + ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1); + +# SUM(t1.b) is aggregated in the subquery, no mixture: +SELECT t1.a, SUM(t1.b) FROM t1 + WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a + ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1) + GROUP BY t1.a; + +# 2nd SUM(t1.b) is aggregated in the subquery, no mixture: +SELECT t1.a, SUM(t1.b) FROM t1 + WHERE t1.a = (SELECT SUM(t2.b) FROM t2 + ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1) + GROUP BY t1.a; + +# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture: +SELECT t1.a, SUM(t1.b) FROM t1 + WHERE t1.a = (SELECT SUM(t2.b) FROM t2 + ORDER BY SUM(t2.b + t1.a) LIMIT 1) + GROUP BY t1.a; + +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1); + +select avg ( + (select + (select sum(outr.a + innr.a) from t1 as innr limit 1) as tt + from t1 as outr order by outr.a limit 1)) +from t1 as most_outer; + +--error 1140 +select avg ( + (select ( + (select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt + from t1 as outr order by count(outr.a) limit 1)) as tt +from t1 as most_outer; + +select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a; + +SET sql_mode=@save_sql_mode; +DROP TABLE t1, t2; + --echo End of 5.0 tests # Bug #21174: Index degrades sort performance and # optimizer does not honor IGNORE INDEX. diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 5433d787c14..affb6c3bac7 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -240,6 +240,88 @@ SELECT * FROM t1; remove_file $MYSQLTEST_VARDIR/tmp/t1; DROP TABLE t1; +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#35469: server crash with LOAD DATA INFILE to a VIEW. +--echo # -- + +--echo +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP VIEW IF EXISTS v2; +DROP VIEW IF EXISTS v3; +--enable_warnings + +--echo +CREATE TABLE t1(c1 INT, c2 VARCHAR(255)); + +--echo +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1; +CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2; + +--echo +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v1 + FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (c1, c2); + +--echo +SELECT * FROM t1; + +--echo +SELECT * FROM v1; + +--echo +DELETE FROM t1; + +--echo +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2 + FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (c1, c2); + +--echo +SELECT * FROM t1; + +--echo +SELECT * FROM v2; + +--echo +DELETE FROM t1; + +--echo +--error ER_LOAD_DATA_INVALID_COLUMN +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2 + FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (c0, c2); + +--echo +--error ER_NON_UPDATABLE_TABLE +LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v3 + FIELDS ESCAPED BY '\\' + TERMINATED BY ',' + ENCLOSED BY '"' + LINES TERMINATED BY '\n' (d1, d2); + +--echo +DROP TABLE t1; +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; + +--echo +--echo # -- End of Bug#35469. + +########################################################################### + # End of 5.0 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 898742c2682..64665c6b5bd 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -847,3 +847,465 @@ set session max_sort_length= 2180; select * from t1 order by b; drop table t1; +# +# Bug #35206: select query result different if the key is indexed or not +# + +CREATE TABLE t2 (a varchar(32), b int(11), c float, d double, + UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); + +CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); +CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b)); + +--disable_query_log +INSERT INTO t1 (a, b) VALUES +('domestic', 'CH'), ('domestic', 'LI'), ('plfcz1', 'FR'), ('all', 'AD'), +('all', 'AE'), ('all', 'AF'), ('all', 'AG'), ('all', 'AI'), ('all', 'AL'), +('all', 'AM'), ('all', 'AN'), ('all', 'AO'), ('all', 'AP'), ('all', 'AQ'), +('all', 'AR'), ('all', 'AS'), ('all', 'AT'), ('all', 'AU'), ('all', 'AW'), +('all', 'AZ'), ('all', 'BA'), ('all', 'BB'), ('all', 'BD'), ('all', 'BE'), +('all', 'BF'), ('all', 'BG'), ('all', 'BH'), ('all', 'BI'), ('all', 'BJ'), +('all', 'BM'), ('all', 'BN'), ('all', 'BO'), ('all', 'BR'), ('all', 'BS'), +('all', 'BT'), ('all', 'BV'), ('all', 'BW'), ('all', 'BY'), ('all', 'BZ'), +('all', 'CA'), ('all', 'CC'), ('all', 'CD'), ('all', 'CF'), ('all', 'CG'), +('all', 'CH'), ('all', 'CI'), ('all', 'CK'), ('all', 'CL'), ('all', 'CM'), +('all', 'CN'), ('all', 'CO'), ('all', 'CR'), ('all', 'CU'), ('all', 'CV'), +('all', 'CX'), ('all', 'CY'), ('all', 'CZ'), ('all', 'DE'), ('all', 'DJ'), +('all', 'DK'), ('all', 'DM'), ('all', 'DO'), ('all', 'DZ'), ('all', 'EC'), +('all', 'EE'), ('all', 'EG'), ('all', 'EH'), ('all', 'EI'), ('all', 'ER'), +('all', 'ES'), ('all', 'ET'), ('all', 'FI'), ('all', 'FJ'), ('all', 'FK'), +('all', 'FM'), ('all', 'FO'), ('all', 'FR'), ('all', 'FX'), ('all', 'GA'), +('all', 'GB'), ('all', 'GD'), ('all', 'GE'), ('all', 'GF'), ('all', 'GH'), +('all', 'GI'), ('all', 'GL'), ('all', 'GM'), ('all', 'GN'), ('all', 'GP'), +('all', 'GQ'), ('all', 'GR'), ('all', 'GS'), ('all', 'GT'), ('all', 'GU'), +('all', 'GW'), ('all', 'GY'), ('all', 'HK'), ('all', 'HM'), ('all', 'HN'), +( 'all', 'HR'), ( 'all', 'HT'), ( 'all', 'HU'), ( 'all', 'ID'), ( 'all', 'IE'), +( 'all', 'IL'), ( 'all', 'IN'), ( 'all', 'IO'), ( 'all', 'IQ'), ( 'all', 'IR'), +( 'all', 'IS'), ( 'all', 'IT'), ( 'all', 'JM'), ( 'all', 'JO'), ( 'all', 'JP'), +( 'all', 'KE'), ( 'all', 'KG'), ( 'all', 'KH'), ( 'all', 'KI'), ( 'all', 'KM'), +( 'all', 'KN'), ( 'all', 'KP'), ( 'all', 'KR'), ( 'all', 'KW'), ( 'all', 'KY'), +( 'all', 'KZ'), ( 'all', 'LA'), ( 'all', 'LB'), ( 'all', 'LC'), ( 'all', 'LI'), +( 'all', 'LK'), ( 'all', 'LR'), ( 'all', 'LS'), ( 'all', 'LT'), ( 'all', 'LU'), +( 'all', 'LV'), ( 'all', 'LY'), ( 'all', 'MA'), ( 'all', 'MC'), ( 'all', 'MD'), +( 'all', 'ME'), ( 'all', 'MG'), ( 'all', 'MH'), ( 'all', 'MK'), ( 'all', 'ML'), +( 'all', 'MM'), ( 'all', 'MN'), ( 'all', 'MO'), ( 'all', 'MP'), ( 'all', 'MQ'), +( 'all', 'MR'), ( 'all', 'MS'), ( 'all', 'MT'), ( 'all', 'MU'), ( 'all', 'MV'), +( 'all', 'MW'), ( 'all', 'MX'), ( 'all', 'MY'), ( 'all', 'MZ'), ( 'all', 'NA'), +( 'all', 'NC'), ( 'all', 'NE'), ( 'all', 'NF'), ( 'all', 'NG'), ( 'all', 'NI'), +( 'all', 'NL'), ( 'all', 'NO'), ( 'all', 'NP'), ( 'all', 'NR'), ( 'all', 'NU'), +( 'all', 'NV'), ( 'all', 'NZ'), ( 'all', 'OM'), ( 'all', 'PA'), ( 'all', 'PE'), +( 'all', 'PF'), ( 'all', 'PG'), ( 'all', 'PH'), ( 'all', 'PK'), ( 'all', 'PL'), +( 'all', 'PM'), ( 'all', 'PN'), ( 'all', 'PR'), ( 'all', 'PS'), ( 'all', 'PT'), +( 'all', 'PW'), ( 'all', 'PY'), ( 'all', 'QA'), ( 'all', 'RE'), ( 'all', 'RO'), +( 'all', 'RU'), ( 'all', 'RW'), ( 'all', 'SA'), ( 'all', 'SB'), ( 'all', 'SC'), +( 'all', 'SD'), ( 'all', 'SE'), ( 'all', 'SG'), ( 'all', 'SH'), ( 'all', 'SI'), +( 'all', 'SJ'), ( 'all', 'SK'), ( 'all', 'SL'), ( 'all', 'SM'), ( 'all', 'SN'), +( 'all', 'SO'), ( 'all', 'SR'), ( 'all', 'ST'), ( 'all', 'SV'), ( 'all', 'SY'), +( 'all', 'SZ'), ( 'all', 'TA'), ( 'all', 'TC'), ( 'all', 'TD'), ( 'all', 'TF'), +( 'all', 'TG'), ( 'all', 'TH'), ( 'all', 'TJ'), ( 'all', 'TK'), ( 'all', 'TM'), +( 'all', 'TN'), ( 'all', 'TO'), ( 'all', 'TP'), ( 'all', 'TR'), ( 'all', 'TT'), +( 'all', 'TV'), ( 'all', 'TW'), ( 'all', 'TZ'), ( 'all', 'UA'), ( 'all', 'UG'), +( 'all', 'UM'), ( 'all', 'US'), ( 'all', 'UY'), ( 'all', 'UZ'), ( 'all', 'VA'), +( 'all', 'VC'), ( 'all', 'VE'), ( 'all', 'VG'), ( 'all', 'VI'), ( 'all', 'VN'), +( 'all', 'VU'), ( 'all', 'WF'), ( 'all', 'WS'), ( 'plfcz1', 'FI'), +( 'all', 'XE'), ( 'all', 'XS'), ( 'all', 'XU'), ( 'plfcz1', 'XE'), +( 'all', 'YE'), ( 'all', 'YT'), ( 'all', 'YU'), ( 'all', 'ZA'), ( 'all', 'ZM'), +( 'all', 'ZR'), ( 'all', 'ZW'), ( 'foreign', 'AD'), ( 'foreign', 'AE'), +( 'foreign', 'AF'), ( 'foreign', 'AG'), ( 'foreign', 'AI'), +( 'foreign', 'AL'), ( 'foreign', 'AM'), ( 'foreign', 'AN'), ( 'foreign', 'AO'), +( 'foreign', 'AP'), ( 'foreign', 'AQ'), ( 'foreign', 'AR'), ( 'foreign', 'AS'), +( 'foreign', 'AT'), ( 'foreign', 'AU'), ( 'foreign', 'AW'), ( 'foreign', 'AZ'), +( 'foreign', 'BA'), ( 'foreign', 'BB'), ( 'foreign', 'BD'), ( 'foreign', 'BE'), +( 'foreign', 'BF'), ( 'foreign', 'BG'), ( 'foreign', 'BH'), ( 'foreign', 'BI'), +( 'foreign', 'BJ'), ( 'foreign', 'BM'), ( 'foreign', 'BN'), ( 'foreign', 'BO'), +( 'foreign', 'BR'), ( 'foreign', 'BS'), ( 'foreign', 'BT'), ( 'foreign', 'BV'), +( 'foreign', 'BW'), ( 'foreign', 'BY'), ( 'foreign', 'BZ'), ( 'foreign', 'CA'), +( 'foreign', 'CC'), ( 'foreign', 'CD'), ( 'foreign', 'CF'), ( 'foreign', 'CG'), +( 'foreign', 'CI'), ( 'foreign', 'CK'), ( 'foreign', 'CL'), ( 'foreign', 'CM'), +( 'foreign', 'CN'), ( 'foreign', 'CO'), ( 'foreign', 'CR'), ( 'foreign', 'CU'), +( 'foreign', 'CV'), ( 'foreign', 'CX'), ( 'foreign', 'CY'), ( 'foreign', 'CZ'), +( 'foreign', 'DE'), ( 'foreign', 'DJ'), ( 'foreign', 'DK'), ( 'foreign', 'DM'), +( 'foreign', 'DO'), ( 'foreign', 'DZ'), ( 'foreign', 'EC'), ( 'foreign', 'EE'), +( 'foreign', 'EG'), ( 'foreign', 'EH'), ( 'foreign', 'EI'), ( 'foreign', 'ER'), +( 'foreign', 'ES'), ( 'foreign', 'ET'), ( 'foreign', 'FI'), ( 'foreign', 'FJ'), +( 'foreign', 'FK'), ( 'foreign', 'FM'), ( 'foreign', 'FO'), ( 'foreign', 'FR'), +( 'foreign', 'FX'), ( 'foreign', 'GA'), ( 'foreign', 'GB'), ( 'foreign', 'GD'), +( 'foreign', 'GE'), ( 'foreign', 'GF'), ( 'foreign', 'GH'), ( 'foreign', 'GI'), +( 'foreign', 'GL'), ( 'foreign', 'GM'), ( 'foreign', 'GN'), ( 'foreign', 'GP'), +( 'foreign', 'GQ'), ( 'foreign', 'GR'), ( 'foreign', 'GS'), ( 'foreign', 'GT'), +( 'foreign', 'GU'), ( 'foreign', 'GW'), ( 'foreign', 'GY'), ( 'foreign', 'HK'), +( 'foreign', 'HM'), ( 'foreign', 'HN'), ( 'foreign', 'HR'), ( 'foreign', 'HT'), +( 'foreign', 'HU'), ( 'foreign', 'ID'), ( 'foreign', 'IE'), ( 'foreign', 'IL'), +( 'foreign', 'IN'), ( 'foreign', 'IO'), ( 'foreign', 'IQ'), ( 'foreign', 'IR'), +( 'foreign', 'IS'), ( 'foreign', 'IT'), ( 'foreign', 'JM'), ( 'foreign', 'JO'), +( 'foreign', 'JP'), ( 'foreign', 'KE'), ( 'foreign', 'KG'), ( 'foreign', 'KH'), +( 'foreign', 'KI'), ( 'foreign', 'KM'), ( 'foreign', 'KN'), ( 'foreign', 'KP'), +( 'foreign', 'KR'), ( 'foreign', 'KW'), ( 'foreign', 'KY'), ( 'foreign', 'KZ'), +( 'foreign', 'LA'), ( 'foreign', 'LB'), ( 'foreign', 'LC'), ( 'foreign', 'LK'), +( 'foreign', 'LR'), ( 'foreign', 'LS'), ( 'foreign', 'LT'), ( 'foreign', 'LU'), +( 'foreign', 'LV'), ( 'foreign', 'LY'), ( 'foreign', 'MA'), ( 'foreign', 'MC'), +( 'foreign', 'MD'), ( 'foreign', 'ME'), ( 'foreign', 'MG'), ( 'foreign', 'MH'), +( 'foreign', 'MK'), ( 'foreign', 'ML'), ( 'foreign', 'MM'), ( 'foreign', 'MN'), +( 'foreign', 'MO'), ( 'foreign', 'MP'), ( 'foreign', 'MQ'), ( 'foreign', 'MR'), +( 'foreign', 'MS'), ( 'foreign', 'MT'), ( 'foreign', 'MU'), ( 'foreign', 'MV'), +( 'foreign', 'MW'), ( 'foreign', 'MX'), ( 'foreign', 'MY'), ( 'foreign', 'MZ'), +( 'foreign', 'NA'), ( 'foreign', 'NC'), ( 'foreign', 'NE'), ( 'foreign', 'NF'), +( 'foreign', 'NG'), ( 'foreign', 'NI'), ( 'foreign', 'NL'), ( 'foreign', 'NO'), +( 'foreign', 'NP'), ( 'foreign', 'NR'), ( 'foreign', 'NU'), ( 'foreign', 'NV'), +( 'foreign', 'NZ'), ( 'foreign', 'OM'), ( 'foreign', 'PA'), ( 'foreign', 'PE'), +( 'foreign', 'PF'), ( 'foreign', 'PG'), ( 'foreign', 'PH'), ( 'foreign', 'PK'), +( 'foreign', 'PL'), ( 'foreign', 'PM'), ( 'foreign', 'PN'), ( 'foreign', 'PR'), +( 'foreign', 'PS'), ( 'foreign', 'PT'), ( 'foreign', 'PW'), ( 'foreign', 'PY'), +( 'foreign', 'QA'), ( 'foreign', 'RE'), ( 'foreign', 'RO'), ( 'foreign', 'RU'), +( 'foreign', 'RW'), ( 'foreign', 'SA'), ( 'foreign', 'SB'), ( 'foreign', 'SC'), +( 'foreign', 'SD'), ( 'foreign', 'SE'), ( 'foreign', 'SG'), ( 'foreign', 'SH'), +( 'foreign', 'SI'), ( 'foreign', 'SJ'), ( 'foreign', 'SK'), ( 'foreign', 'SL'), +( 'foreign', 'SM'), ( 'foreign', 'SN'), ( 'foreign', 'SO'), ( 'foreign', 'SR'), +( 'foreign', 'ST'), ( 'foreign', 'SV'), ( 'foreign', 'SY'), ( 'foreign', 'SZ'), +( 'foreign', 'TA'), ( 'foreign', 'TC'), ( 'foreign', 'TD'), ( 'foreign', 'TF'), +( 'foreign', 'TG'), ( 'foreign', 'TH'), ( 'foreign', 'TJ'), ( 'foreign', 'TK'), +( 'foreign', 'TM'), ( 'foreign', 'TN'), ( 'foreign', 'TO'), ( 'foreign', 'TP'), +( 'foreign', 'TR'), ( 'foreign', 'TT'), ( 'foreign', 'TV'), ( 'foreign', 'TW'), +( 'foreign', 'TZ'), ( 'foreign', 'UA'), ( 'foreign', 'UG'), ( 'foreign', 'UM'), +( 'foreign', 'US'), ( 'foreign', 'UY'), ( 'foreign', 'UZ'), ( 'foreign', 'VA'), +( 'foreign', 'VC'), ( 'foreign', 'VE'), ( 'foreign', 'VG'), ( 'foreign', 'VI'), +( 'foreign', 'VN'), ( 'foreign', 'VU'), ( 'foreign', 'WF'), ( 'foreign', 'WS'), +( 'plfcz1', 'DK'), ( 'foreign', 'XE'), ( 'foreign', 'XS'), ( 'foreign', 'XU'), +( 'plfcz1', 'BE'), ( 'foreign', 'YE'), ( 'foreign', 'YT'), ( 'foreign', 'YU'), +( 'foreign', 'ZA'), ( 'foreign', 'ZM'), ( 'foreign', 'ZR'), ( 'foreign', 'ZW'), +( 'plfcz1', 'DE'), ( 'plfcz1', 'GI'), ( 'plfcz1', 'GR'), ( 'plfcz1', 'IS'), +( 'plfcz1', 'EI'), ( 'plfcz1', 'IT'), ( 'plfcz1', 'LU'), ( 'plfcz1', 'NL'), +( 'plfcz1', 'NO'), ( 'plfcz1', 'ES'), ( 'plfcz1', 'SE'), ( 'plfcz1', 'AL'), +( 'plfcz1', 'AD'), ( 'plfcz1', 'BY'), ( 'plfcz1', 'BA'), ( 'plfcz1', 'BG'), +( 'plfcz1', 'EE'), ( 'plfcz1', 'FO'), ( 'plfcz1', 'GL'), ( 'plfcz1', 'GB'), +( 'plfcz1', 'HR'), ( 'plfcz1', 'LV'), ( 'plfcz1', 'LT'), ( 'plfcz1', 'MT'), +( 'plfcz1', 'MK'), ( 'plfcz1', 'MD'), ( 'plfcz1', 'MC'), ( 'plfcz1', 'AT'), +( 'plfcz1', 'PL'), ( 'plfcz1', 'PT'), ( 'plfcz1', 'RO'), ( 'plfcz1', 'RU'), +( 'plfcz1', 'SM'), ( 'plfcz1', 'XS'), ( 'plfcz1', 'SK'), ( 'plfcz1', 'SI'), +( 'plfcz1', 'CZ'), ( 'plfcz1', 'TR'), ( 'plfcz1', 'UA'), ( 'plfcz1', 'HU'), +( 'plfcz1', 'VA'), ( 'plfcz1', 'CY'), ( 'plfcz2', 'AF'), ( 'plfcz2', 'DZ'), +( 'plfcz2', 'AS'), ( 'plfcz2', 'AO'), ( 'plfcz2', 'AI'), ( 'plfcz2', 'AQ'), +( 'plfcz2', 'AG'), ( 'plfcz2', 'AR'), ( 'plfcz2', 'AM'), ( 'plfcz2', 'AW'), +( 'plfcz2', 'AU'), ( 'plfcz2', 'AZ'), ( 'plfcz2', 'AP'), ( 'plfcz2', 'BS'), +( 'plfcz2', 'BH'), ( 'plfcz2', 'BD'), ( 'plfcz2', 'BB'), ( 'plfcz2', 'BZ'), +( 'plfcz2', 'BJ'), ( 'plfcz2', 'BM'), ( 'plfcz2', 'BT'), ( 'plfcz2', 'BO'), +( 'plfcz2', 'BW'), ( 'plfcz2', 'BV'), ( 'plfcz2', 'BR'), ( 'plfcz2', 'IO'), +( 'plfcz2', 'VG'), ( 'plfcz2', 'BN'), ( 'plfcz2', 'BF'), ( 'plfcz2', 'BI'), +( 'plfcz2', 'KH'), ( 'plfcz2', 'CM'), ( 'plfcz2', 'CA'), ( 'plfcz2', 'CV'), +( 'plfcz2', 'KY'), ( 'plfcz2', 'CF'), ( 'plfcz2', 'TD'), ( 'plfcz2', 'CL'), +( 'plfcz2', 'CN'), ( 'plfcz2', 'CX'), ( 'plfcz2', 'CC'), ( 'plfcz2', 'CO'), +( 'plfcz2', 'KM'), ( 'plfcz2', 'CG'), ( 'plfcz2', 'CD'), ( 'plfcz2', 'CK'), +( 'plfcz2', 'CR'), ( 'plfcz2', 'CI'), ( 'plfcz2', 'CU'), ( 'plfcz2', 'DJ'), +( 'plfcz2', 'DM'), ( 'plfcz2', 'DO'), ( 'plfcz2', 'TP'), ( 'plfcz2', 'EC'), +( 'plfcz2', 'EG'), ( 'plfcz2', 'SV'), ( 'plfcz2', 'GQ'), ( 'plfcz2', 'ER'), +( 'plfcz2', 'ET'), ( 'plfcz2', 'FK'), ( 'plfcz2', 'FJ'), ( 'plfcz2', 'FX'), +( 'plfcz2', 'GF'), ( 'plfcz2', 'PF'), ( 'plfcz2', 'TA'), ( 'plfcz2', 'TF'), +( 'plfcz2', 'GA'), ( 'plfcz2', 'GM'), ( 'plfcz2', 'GE'), ( 'plfcz2', 'GH'), +( 'plfcz2', 'GD'), ( 'plfcz2', 'GP'), ( 'plfcz2', 'GU'), ( 'plfcz2', 'GT'), +( 'plfcz2', 'GN'), ( 'plfcz2', 'GW'), ( 'plfcz2', 'GY'), ( 'plfcz2', 'HT'), +( 'plfcz2', 'HM'), ( 'plfcz2', 'HN'), ( 'plfcz2', 'HK'), ( 'plfcz2', 'IN'), +( 'plfcz2', 'ID'), ( 'plfcz2', 'IR'), ( 'plfcz2', 'IQ'), ( 'plfcz2', 'IE'), +( 'plfcz2', 'IL'), ( 'plfcz2', 'JM'), ( 'plfcz2', 'JP'), ( 'plfcz2', 'JO'), +( 'plfcz2', 'KZ'), ( 'plfcz2', 'KE'), ( 'plfcz2', 'KI'), ( 'plfcz2', 'KP'), +( 'plfcz2', 'KW'), ( 'plfcz2', 'KG'), ( 'plfcz2', 'LA'), ( 'plfcz2', 'LB'), +( 'plfcz2', 'LS'), ( 'plfcz2', 'LR'), ( 'plfcz2', 'LY'), ( 'plfcz2', 'MO'), +( 'plfcz2', 'MG'), ( 'plfcz2', 'ME'), ( 'plfcz2', 'MW'), ( 'plfcz2', 'MY'), +( 'plfcz2', 'MV'), ( 'plfcz2', 'ML'), ( 'plfcz2', 'MH'), ( 'plfcz2', 'MQ'), +( 'plfcz2', 'MR'), ( 'plfcz2', 'MU'), ( 'plfcz2', 'YT'), ( 'plfcz2', 'MX'), +( 'plfcz2', 'FM'), ( 'plfcz2', 'MN'), ( 'plfcz2', 'MS'), ( 'plfcz2', 'MA'), +( 'plfcz2', 'MZ'), ( 'plfcz2', 'MM'), ( 'plfcz2', 'NA'), ( 'plfcz2', 'NR'), +( 'plfcz2', 'NP'), ( 'plfcz2', 'AN'), ( 'plfcz2', 'NC'), ( 'plfcz2', 'NZ'), +( 'plfcz2', 'NI'), ( 'plfcz2', 'NE'), ( 'plfcz2', 'NG'), ( 'plfcz2', 'NU'), +( 'plfcz2', 'NF'), ( 'plfcz2', 'MP'), ( 'plfcz2', 'OM'), ( 'plfcz2', 'PK'), +( 'plfcz2', 'PW'), ( 'plfcz2', 'PS'), ( 'plfcz2', 'PA'), ( 'plfcz2', 'PG'), +( 'plfcz2', 'PY'), ( 'plfcz2', 'PE'), ( 'plfcz2', 'PH'), ( 'plfcz2', 'PN'), +( 'plfcz2', 'PR'), ( 'plfcz2', 'QA'), ( 'plfcz2', 'RE'), ( 'plfcz2', 'RW'), +( 'plfcz2', 'KN'), ( 'plfcz2', 'ST'), ( 'plfcz2', 'SA'), ( 'plfcz2', 'SN'), +( 'plfcz2', 'SC'), ( 'plfcz2', 'SL'), ( 'plfcz2', 'SG'), ( 'plfcz2', 'SB'), +( 'plfcz2', 'SO'), ( 'plfcz2', 'ZA'), ( 'plfcz2', 'GS'), ( 'plfcz2', 'KR'), +( 'plfcz2', 'LK'), ( 'plfcz2', 'NV'), ( 'plfcz2', 'SH'), ( 'plfcz2', 'LC'), +( 'plfcz2', 'PM'), ( 'plfcz2', 'VC'), ( 'plfcz2', 'SD'), ( 'plfcz2', 'SR'), +( 'plfcz2', 'SJ'), ( 'plfcz2', 'SZ'), ( 'plfcz2', 'SY'), ( 'plfcz2', 'TW'), +( 'plfcz2', 'TJ'), ( 'plfcz2', 'TZ'), ( 'plfcz2', 'TH'), ( 'plfcz2', 'TG'), +( 'plfcz2', 'TK'), ( 'plfcz2', 'TO'), ( 'plfcz2', 'TT'), ( 'plfcz2', 'XU'), +( 'plfcz2', 'TN'), ( 'plfcz2', 'TM'), ( 'plfcz2', 'TC'), ( 'plfcz2', 'TV'), +( 'plfcz2', 'UG'), ( 'plfcz2', 'AE'), ( 'plfcz2', 'US'), ( 'plfcz2', 'UM'), +( 'plfcz2', 'UY'), ( 'plfcz2', 'UZ'), ( 'plfcz2', 'VU'), ( 'plfcz2', 'VE'), +( 'plfcz2', 'VN'), ( 'plfcz2', 'VI'), ( 'plfcz2', 'WF'), ( 'plfcz2', 'EH'), +( 'plfcz2', 'WS'), ( 'plfcz2', 'YE'), ( 'plfcz2', 'YU'), ( 'plfcz2', 'ZR'), +( 'plfcz2', 'ZM'), ( 'plfcz2', 'ZW'), ( 'ppfcz1', 'AT'), ( 'ppfcz1', 'BE'), +( 'ppfcz1', 'DE'), ( 'ppfcz1', 'FR'), ( 'ppfcz1', 'FX'), ( 'ppfcz1', 'IT'), +( 'ppfcz1', 'LU'), ( 'ppfcz1', 'MC'), ( 'ppfcz1', 'NL'), ( 'ppfcz1', 'SM'), +( 'ppfcz1', 'VA'), ( 'ppfcz1', 'XE'), ( 'ppfcz2', 'AD'), ( 'ppfcz2', 'AL'), +( 'ppfcz2', 'BA'), ( 'ppfcz2', 'BG'), ( 'ppfcz2', 'BY'), ( 'ppfcz2', 'CY'), +( 'ppfcz2', 'CZ'), ( 'ppfcz2', 'DK'), ( 'ppfcz2', 'EE'), ( 'ppfcz2', 'EI'), +( 'ppfcz2', 'ES'), ( 'ppfcz2', 'FI'), ( 'ppfcz2', 'FO'), ( 'ppfcz2', 'GB'), +( 'ppfcz2', 'GI'), ( 'ppfcz2', 'GL'), ( 'ppfcz2', 'GR'), ( 'ppfcz2', 'HR'), +( 'ppfcz2', 'HU'), ( 'ppfcz2', 'IE'), ( 'ppfcz2', 'IS'), ( 'ppfcz2', 'LT'), +( 'ppfcz2', 'LV'), ( 'ppfcz2', 'MD'), ( 'ppfcz2', 'MK'), ( 'ppfcz2', 'MT'), +( 'ppfcz2', 'NO'), ( 'ppfcz2', 'PL'), ( 'ppfcz2', 'PT'), ( 'ppfcz2', 'RO'), +( 'ppfcz2', 'RU'), ( 'ppfcz2', 'SE'), ( 'ppfcz2', 'SI'), ( 'ppfcz2', 'SK'), +( 'ppfcz2', 'TR'), ( 'ppfcz2', 'UA'), ( 'ppfcz2', 'XS'), ( 'ppfcz2', 'YU'), +( 'ppfcz3', 'CA'), ( 'ppfcz3', 'DZ'), ( 'ppfcz3', 'EG'), ( 'ppfcz3', 'IL'), +( 'ppfcz3', 'JO'), ( 'ppfcz3', 'LB'), ( 'ppfcz3', 'LY'), ( 'ppfcz3', 'MA'), +( 'ppfcz3', 'MX'), ( 'ppfcz3', 'PM'), ( 'ppfcz3', 'SY'), ( 'ppfcz3', 'TN'), +( 'ppfcz3', 'US'), ( 'ppfcz4', 'AE'), ( 'ppfcz4', 'AF'), ( 'ppfcz4', 'AM'), +( 'ppfcz4', 'AO'), ( 'ppfcz4', 'AZ'), ( 'ppfcz4', 'BD'), ( 'ppfcz4', 'BF'), +( 'ppfcz4', 'BH'), ( 'ppfcz4', 'BI'), ( 'ppfcz4', 'BJ'), ( 'ppfcz4', 'BT'), +( 'ppfcz4', 'BV'), ( 'ppfcz4', 'BW'), ( 'ppfcz4', 'CF'), ( 'ppfcz4', 'CG'), +( 'ppfcz4', 'CI'), ( 'ppfcz4', 'CM'), ( 'ppfcz4', 'CN'), ( 'ppfcz4', 'DJ'), +( 'ppfcz4', 'DO'), ( 'ppfcz4', 'ER'), ( 'ppfcz4', 'ET'), ( 'ppfcz4', 'GA'), +( 'ppfcz4', 'GE'), ( 'ppfcz4', 'GH'), ( 'ppfcz4', 'GM'), ( 'ppfcz4', 'GN'), +( 'ppfcz4', 'GQ'), ( 'ppfcz4', 'GW'), ( 'ppfcz4', 'HK'), ( 'ppfcz4', 'IN'), +( 'ppfcz4', 'IQ'), ( 'ppfcz4', 'IR'), ( 'ppfcz4', 'JP'), ( 'ppfcz4', 'KE'), +( 'ppfcz4', 'KG'), ( 'ppfcz4', 'KH'), ( 'ppfcz4', 'KP'), ( 'ppfcz4', 'KW'), +( 'ppfcz4', 'KZ'), ( 'ppfcz4', 'LA'), ( 'ppfcz4', 'LK'), ( 'ppfcz4', 'LR'), +( 'ppfcz4', 'LS'), ( 'ppfcz4', 'MG'), ( 'ppfcz4', 'ML'), ( 'ppfcz4', 'MM'), +( 'ppfcz4', 'MN'), ( 'ppfcz4', 'MO'), ( 'ppfcz4', 'MR'), ( 'ppfcz4', 'MU'), +( 'ppfcz4', 'MV'), ( 'ppfcz4', 'MW'), ( 'ppfcz4', 'MY'), ( 'ppfcz4', 'MZ'), +( 'ppfcz4', 'NA'), ( 'ppfcz4', 'NE'), ( 'ppfcz4', 'NG'), ( 'ppfcz4', 'NP'), +( 'ppfcz4', 'OM'), ( 'ppfcz4', 'PK'), ( 'ppfcz4', 'QA'), ( 'ppfcz4', 'RE'), +( 'ppfcz4', 'RW'), ( 'ppfcz4', 'SA'), ( 'ppfcz4', 'SC'), ( 'ppfcz4', 'SD'), +( 'ppfcz4', 'SG'), ( 'ppfcz4', 'SH'), ( 'ppfcz4', 'SL'), ( 'ppfcz4', 'SN'), +( 'ppfcz4', 'SO'), ( 'ppfcz4', 'SZ'), ( 'ppfcz4', 'TD'), ( 'ppfcz4', 'TG'), +( 'ppfcz4', 'TH'), ( 'ppfcz4', 'TJ'), ( 'ppfcz4', 'TM'), ( 'ppfcz4', 'TW'), +( 'ppfcz4', 'TZ'), ( 'ppfcz4', 'UG'), ( 'ppfcz4', 'UZ'), ( 'ppfcz4', 'VN'), +( 'ppfcz4', 'XU'), ( 'ppfcz4', 'YT'), ( 'ppfcz4', 'ZA'), ( 'ppfcz4', 'ZW'), +( 'ppfcz5', 'AG'), ( 'ppfcz5', 'AI'), ( 'ppfcz5', 'AN'), ( 'ppfcz5', 'AP'), +( 'ppfcz5', 'AQ'), ( 'ppfcz5', 'AR'), ( 'ppfcz5', 'AS'), ( 'ppfcz5', 'AU'), +( 'ppfcz5', 'AW'), ( 'ppfcz5', 'BB'), ( 'ppfcz5', 'BM'), ( 'ppfcz5', 'BN'), +( 'ppfcz5', 'BO'), ( 'ppfcz5', 'BR'), ( 'ppfcz5', 'BS'), ( 'ppfcz5', 'BZ'), +( 'ppfcz5', 'CC'), ( 'ppfcz5', 'CD'), ( 'ppfcz5', 'CK'), ( 'ppfcz5', 'CL'), +( 'ppfcz5', 'CO'), ( 'ppfcz5', 'CR'), ( 'ppfcz5', 'CU'), ( 'ppfcz5', 'CV'), +( 'ppfcz5', 'CX'), ( 'ppfcz5', 'DM'), ( 'ppfcz5', 'EC'), ( 'ppfcz5', 'EH'), +( 'ppfcz5', 'FJ'), ( 'ppfcz5', 'FK'), ( 'ppfcz5', 'FM'), ( 'ppfcz5', 'GD'), +( 'ppfcz5', 'GF'), ( 'ppfcz5', 'GP'), ( 'ppfcz5', 'GS'), ( 'ppfcz5', 'GT'), +( 'ppfcz5', 'GU'), ( 'ppfcz5', 'GY'), ( 'ppfcz5', 'HM'), ( 'ppfcz5', 'HN'), +( 'ppfcz5', 'HT'), ( 'ppfcz5', 'ID'), ( 'ppfcz5', 'IO'), ( 'ppfcz5', 'JM'), +( 'ppfcz5', 'KI'), ( 'ppfcz5', 'KM'), ( 'ppfcz5', 'KN'), ( 'ppfcz5', 'KR'), +( 'ppfcz5', 'KY'), ( 'ppfcz5', 'LC'), ( 'ppfcz5', 'ME'), ( 'ppfcz5', 'MH'), +( 'ppfcz5', 'MP'), ( 'ppfcz5', 'MQ'), ( 'ppfcz5', 'MS'), ( 'ppfcz5', 'NC'), +( 'ppfcz5', 'NF'), ( 'ppfcz5', 'NI'), ( 'ppfcz5', 'NR'), ( 'ppfcz5', 'NU'), +( 'ppfcz5', 'NZ'), ( 'ppfcz5', 'PA'), ( 'ppfcz5', 'PE'), ( 'ppfcz5', 'PF'), +( 'ppfcz5', 'PG'), ( 'ppfcz5', 'PH'), ( 'ppfcz5', 'PN'), ( 'ppfcz5', 'PR'), +( 'ppfcz5', 'PS'), ( 'ppfcz5', 'PW'), ( 'ppfcz5', 'PY'), ( 'ppfcz5', 'SB'), +( 'ppfcz5', 'SJ'), ( 'ppfcz5', 'SR'), ( 'ppfcz5', 'ST'), ( 'ppfcz5', 'SV'), +( 'ppfcz5', 'TA'), ( 'ppfcz5', 'TC'), ( 'ppfcz5', 'TF'), ( 'ppfcz5', 'TK'), +( 'ppfcz5', 'TO'), ( 'ppfcz5', 'TP'), ( 'ppfcz5', 'TT'), ( 'ppfcz5', 'TV'), +( 'ppfcz5', 'UM'), ( 'ppfcz5', 'UY'), ( 'ppfcz5', 'VC'), ( 'ppfcz5', 'VE'), +( 'ppfcz5', 'VG'), ( 'ppfcz5', 'VI'), ( 'ppfcz5', 'VU'), ( 'ppfcz5', 'WF'), +( 'ppfcz5', 'WS'), ( 'ppfcz5', 'YE'), ( 'ppfcz5', 'ZM'), ( 'ppfcz5', 'ZR'); + +INSERT INTO t2 (a, b, c, d) VALUES +('domestic', 26, 0.25, 4.7), ('domestic', 27, 0.25, 6), +('domestic', 19, 2, 6.3), ('domestic', 19, 5, 7.77), +('domestic', 19, 10, 10.3), ('domestic', 19, 20, 14.83), +('domestic', 19, 30, 20.88), ('domestic', 20, 2, 7.3), +('domestic', 20, 5, 8.77), ('domestic', 20, 10, 11.3), +('domestic', 20, 20, 15.83), ('domestic', 20, 30, 21.88), +('domestic', 23, 2, 18.8), ('domestic', 23, 5, 20.8), +('domestic', 23, 10, 24.8), ('domestic', 23, 20, 27.8), +('domestic', 23, 30, 30.8), ('domestic', 24, 2, 21.1405), +('domestic', 24, 5, 22.3705), ('domestic', 24, 10, 25.0905), +('domestic', 24, 20, 29.7705), ('domestic', 24, 30, 35.9605), +('domestic', 17, 2, 7.2), ('domestic', 17, 5, 8.43), +('domestic', 17, 10, 11.15), ('domestic', 17, 20, 15.83), +('domestic', 17, 30, 22.02), ('domestic', 18, 2, 8.2), +('domestic', 18, 5, 9.43), ('domestic', 18, 10, 12.15), +('domestic', 18, 20, 16.83), ('domestic', 18, 30, 23.02), +('domestic', 28, 2, 17), ('domestic', 28, 5, 19), +('domestic', 28, 10, 22), ('domestic', 28, 20, 28), +('domestic', 28, 30, 35), ('domestic', 29, 30, 29.5), +('foreign', 25, 200, 0), ('domestic', 3, 100, 59), +('foreign', 10, 30, 0), ('foreign', 22, 0, 0), +('foreign', 11, 30, 0), ('foreign', 12, 30, 0), +('all', 1, 10000, 0), ('all', 2, 10000, 0), +('domestic', 9, 10000, 0), ('domestic', 4, 500, 0), +('domestic', 5, 500, 0), ('domestic', 6, 500, 0), +('domestic', 7, 500, 0), ('domestic', 8, 500, 0), +('domestic', 21, 3.9, 10.8), ('domestic', 21, 4.9, 12.2), +('domestic', 21, 9.9, 15.3), ('domestic', 21, 19.9, 20.6), +('domestic', 21, 30, 28.1), ('plfcz1', 16, 0.5, 19), +('plfcz2', 16, 0.5, 25), ( 'ppfcz2', 15, 16, 76.5), +( 'ppfcz2', 15, 15, 75.5), ( 'ppfcz2', 15, 14, 73.5), +( 'ppfcz2', 15, 13, 71.5), ( 'ppfcz2', 15, 12, 69.5), +( 'ppfcz2', 15, 11, 67.5), ( 'ppfcz2', 15, 10, 65.5), +( 'ppfcz2', 15, 9, 62.5), ( 'ppfcz2', 15, 8, 59.5), +( 'ppfcz2', 15, 7, 56.5), ( 'ppfcz2', 15, 6, 53.5), +( 'ppfcz2', 15, 5, 50.5), ( 'ppfcz2', 15, 4, 46.5), +( 'ppfcz2', 15, 3, 42.5), ( 'ppfcz2', 15, 2, 38.5), +('ppfcz1', 15, 2, 33.5), ('ppfcz1', 15, 3, 36.5), +('ppfcz1', 15, 4, 39.5), ('ppfcz1', 15, 5, 41.5), +('ppfcz1', 15, 6, 42.5), ('ppfcz1', 15, 7, 43.5), +('ppfcz1', 15, 8, 44.5), ('ppfcz1', 15, 9, 45.5), +('ppfcz1', 15, 10, 46.5), ('ppfcz1', 15, 11, 47.5), +( 'ppfcz1', 15, 12, 48.5), ( 'ppfcz1', 15, 13, 49.5), ( 'ppfcz1', 15, 14, 50.5), +( 'ppfcz1', 15, 15, 51.5), ( 'ppfcz1', 15, 16, 52.5), ( 'ppfcz1', 15, 17, 53.5), +( 'ppfcz1', 15, 18, 54.5), ( 'ppfcz1', 15, 19, 55.5), ( 'ppfcz1', 15, 20, 56.5), +( 'ppfcz1', 15, 21, 57.5), ( 'ppfcz1', 15, 22, 58.5), ( 'ppfcz1', 15, 23, 59.5), +( 'ppfcz1', 15, 24, 60.5), ( 'ppfcz1', 15, 25, 61.5), ( 'ppfcz1', 15, 26, 62.5), +( 'ppfcz1', 15, 27, 63.5), ( 'ppfcz1', 15, 28, 64.5), ( 'ppfcz1', 15, 29, 65.5), +( 'ppfcz1', 15, 30, 66.5), ( 'ppfcz2', 15, 17, 77.5), ( 'ppfcz2', 15, 18, 78.5), +( 'ppfcz2', 15, 19, 79.5), ( 'ppfcz2', 15, 20, 80.5), ( 'ppfcz2', 15, 21, 81.5), +( 'ppfcz2', 15, 22, 82.5), ( 'ppfcz2', 15, 23, 83.5), ( 'ppfcz2', 15, 24, 84.5), +( 'ppfcz2', 15, 25, 85.5), ( 'ppfcz2', 15, 26, 86.5), ( 'ppfcz2', 15, 27, 87.5), +( 'ppfcz2', 15, 28, 88.5), ( 'ppfcz2', 15, 29, 89.5), ( 'ppfcz2', 15, 30, 90.5), +( 'ppfcz3', 15, 2, 39.5), ( 'ppfcz3', 15, 3, 45.5), ( 'ppfcz3', 15, 4, 51.5), +( 'ppfcz3', 15, 5, 57.5), ( 'ppfcz3', 15, 6, 63.5), ( 'ppfcz3', 15, 7, 69.5), +( 'ppfcz3', 15, 8, 75.5), ( 'ppfcz3', 15, 9, 81.5), ( 'ppfcz3', 15, 10, 87.5), +( 'ppfcz3', 15, 11, 93.5), ( 'ppfcz3', 15, 12, 99.5), ( 'ppfcz3', 15, 13, 105.5), +( 'ppfcz3', 15, 14, 111.5), ( 'ppfcz3', 15, 15, 117.5), ( 'ppfcz3', 15, 16, 122.5), +( 'ppfcz3', 15, 17, 127.5), ( 'ppfcz3', 15, 18, 132.5), ( 'ppfcz3', 15, 19, 137.5), +( 'ppfcz3', 15, 20, 142.5), ( 'ppfcz3', 15, 21, 146.5), ( 'ppfcz3', 15, 22, 150.5), +( 'ppfcz3', 15, 23, 154.5), ( 'ppfcz3', 15, 24, 158.5), ( 'ppfcz3', 15, 25, 162.5), +( 'ppfcz3', 15, 26, 166.5), ( 'ppfcz3', 15, 27, 170.5), ( 'ppfcz3', 15, 28, 174.5), +( 'ppfcz3', 15, 29, 178.5), ( 'ppfcz3', 15, 30, 182.5), ( 'ppfcz4', 15, 2, 44.5), +( 'ppfcz4', 15, 3, 51.5), ( 'ppfcz4', 15, 4, 58.5), ( 'ppfcz4', 15, 5, 65.5), +( 'ppfcz4', 15, 6, 72.5), ( 'ppfcz4', 15, 7, 79.5), ( 'ppfcz4', 15, 8, 86.5), +( 'ppfcz4', 15, 9, 93.5), ( 'ppfcz4', 15, 10, 100.5), ( 'ppfcz4', 15, 11, 105.5), +( 'ppfcz4', 15, 12, 110.5), ( 'ppfcz4', 15, 13, 115.5), ( 'ppfcz4', 15, 14, 120.5), +( 'ppfcz4', 15, 15, 125.5), ( 'ppfcz4', 15, 16, 130.5), ( 'ppfcz4', 15, 17, 135.5), +( 'ppfcz4', 15, 18, 140.5), ( 'ppfcz4', 15, 19, 145.5), ( 'ppfcz4', 15, 20, 150.5), +( 'ppfcz4', 15, 21, 154.5), ( 'ppfcz4', 15, 22, 158.5), ( 'ppfcz4', 15, 23, 162.5), +( 'ppfcz4', 15, 24, 166.5), ( 'ppfcz4', 15, 25, 170.5), ( 'ppfcz4', 15, 26, 174.5), +( 'ppfcz4', 15, 27, 178.5), ( 'ppfcz4', 15, 28, 182.5), ( 'ppfcz4', 15, 29, 186.5), +( 'ppfcz4', 15, 30, 190.5), ( 'ppfcz5', 15, 2, 48.5), ( 'ppfcz5', 15, 3, 56.5), +( 'ppfcz5', 15, 4, 64.5), ( 'ppfcz5', 15, 5, 72.5), ( 'ppfcz5', 15, 6, 80.5), +( 'ppfcz5', 15, 7, 88.5), ( 'ppfcz5', 15, 8, 96.5), ( 'ppfcz5', 15, 9, 104.5), +( 'ppfcz5', 15, 10, 112.5), ( 'ppfcz5', 15, 11, 119.5), ( 'ppfcz5', 15, 12, 126.5), +( 'ppfcz5', 15, 13, 133.5), ( 'ppfcz5', 15, 14, 140.5), ( 'ppfcz5', 15, 15, 147.5), +( 'ppfcz5', 15, 16, 153.5), ( 'ppfcz5', 15, 17, 161.5), ( 'ppfcz5', 15, 18, 167.5), +( 'ppfcz5', 15, 19, 173.5), ( 'ppfcz5', 15, 20, 179.5), ( 'ppfcz5', 15, 21, 185.5), +( 'ppfcz5', 15, 22, 191.5), ( 'ppfcz5', 15, 23, 197.5), ( 'ppfcz5', 15, 24, 203.5), +( 'ppfcz5', 15, 25, 207.5), ( 'ppfcz5', 15, 26, 212.5), ( 'ppfcz5', 15, 27, 217.5), +( 'ppfcz5', 15, 28, 222.5), ( 'ppfcz5', 15, 29, 227.5), ( 'ppfcz5', 15, 30, 232.5), +( 'ppfcz1', 14, 2, 37.5), ( 'ppfcz1', 14, 3, 41.5), ( 'ppfcz1', 14, 4, 45.5), +( 'ppfcz1', 14, 5, 48.5), ( 'ppfcz1', 14, 6, 52.5), ( 'ppfcz1', 14, 7, 55.5), +( 'ppfcz1', 14, 8, 57.5), ( 'ppfcz1', 14, 9, 59.5), ( 'ppfcz1', 14, 10, 61.5), +( 'ppfcz1', 14, 11, 62.5), ( 'ppfcz1', 14, 12, 63.5), ( 'ppfcz1', 14, 13, 64.5), +( 'ppfcz1', 14, 14, 65.5), ( 'ppfcz1', 14, 15, 66.5), ( 'ppfcz1', 14, 16, 67.5), +( 'ppfcz1', 14, 17, 68.5), ( 'ppfcz1', 14, 18, 69.5), ( 'ppfcz1', 14, 19, 70.5), +( 'ppfcz1', 14, 20, 71.5), ( 'ppfcz1', 14, 21, 72.5), ( 'ppfcz1', 14, 22, 73.5), +( 'ppfcz1', 14, 23, 74.5), ( 'ppfcz1', 14, 24, 75.5), ( 'ppfcz1', 14, 25, 76.5), +( 'ppfcz1', 14, 26, 77.5), ( 'ppfcz1', 14, 27, 78.5), ( 'ppfcz1', 14, 28, 79.5), +( 'ppfcz1', 14, 29, 80.5), ( 'ppfcz1', 14, 30, 81.5), ( 'ppfcz2', 14, 2, 43.5), +( 'ppfcz2', 14, 3, 48.5), ( 'ppfcz2', 14, 4, 53.5), ( 'ppfcz2', 14, 5, 57.5), +( 'ppfcz2', 14, 6, 61.5), ( 'ppfcz2', 14, 7, 65.5), ( 'ppfcz2', 14, 8, 69.5), +( 'ppfcz2', 14, 9, 73.5), ( 'ppfcz2', 14, 10, 77.5), ( 'ppfcz2', 14, 11, 80.5), +( 'ppfcz2', 14, 12, 83.5), ( 'ppfcz2', 14, 13, 86.5), ( 'ppfcz2', 14, 14, 89.5), +( 'ppfcz2', 14, 15, 92.5), ( 'ppfcz2', 14, 16, 94.5), ( 'ppfcz2', 14, 17, 96.5), +( 'ppfcz2', 14, 18, 98.5), ( 'ppfcz2', 14, 19, 99.5), ( 'ppfcz2', 14, 20, 100.5), +( 'ppfcz2', 14, 21, 101.5), ( 'ppfcz2', 14, 22, 102.5), ( 'ppfcz2', 14, 23, 103.5), +( 'ppfcz2', 14, 24, 104.5), ( 'ppfcz2', 14, 25, 105.5), ( 'ppfcz2', 14, 26, 106.5), +( 'ppfcz2', 14, 27, 107.5), ( 'ppfcz2', 14, 28, 108.5), ( 'ppfcz2', 14, 29, 109.5), +( 'ppfcz2', 14, 30, 110.5), ( 'ppfcz3', 14, 2, 47.5), ( 'ppfcz3', 14, 3, 56.5), +( 'ppfcz3', 14, 4, 67.5), ( 'ppfcz3', 14, 5, 78.5), ( 'ppfcz3', 14, 6, 87.5), +( 'ppfcz3', 14, 7, 96.5), ( 'ppfcz3', 14, 8, 105.5), ( 'ppfcz3', 14, 9, 114.5), +( 'ppfcz3', 14, 10, 123.5), ( 'ppfcz3', 14, 11, 131.5), ( 'ppfcz3', 14, 12, 139.5), +( 'ppfcz3', 14, 13, 147.5), ( 'ppfcz3', 14, 14, 155.5), ( 'ppfcz3', 14, 15, 163.5), +( 'ppfcz3', 14, 16, 171.5), ( 'ppfcz3', 14, 17, 179.5), ( 'ppfcz3', 14, 18, 187.5), +( 'ppfcz3', 14, 19, 195.5), ( 'ppfcz3', 14, 20, 203.5), ( 'ppfcz3', 14, 21, 210.5), +( 'ppfcz3', 14, 22, 217.5), ( 'ppfcz3', 14, 23, 224.5), ( 'ppfcz3', 14, 24, 231.5), +( 'ppfcz3', 14, 25, 238.5), ( 'ppfcz3', 14, 26, 245.5), ( 'ppfcz3', 14, 27, 252.5), +( 'ppfcz3', 14, 28, 259.5), ( 'ppfcz3', 14, 29, 266.5), ( 'ppfcz3', 14, 30, 273.5), +( 'ppfcz4', 14, 2, 54.5), ( 'ppfcz4', 14, 3, 68.5), ( 'ppfcz4', 14, 4, 81.5), +( 'ppfcz4', 14, 5, 95.5), ( 'ppfcz4', 14, 6, 108.5), ( 'ppfcz4', 14, 7, 121.5), +( 'ppfcz4', 14, 8, 134.5), ( 'ppfcz4', 14, 9, 147.5), ( 'ppfcz4', 14, 10, 160.5), +( 'ppfcz4', 14, 11, 168.5), ( 'ppfcz4', 14, 12, 178.5), ( 'ppfcz4', 14, 13, 188.5), +( 'ppfcz4', 14, 14, 198.5), ( 'ppfcz4', 14, 15, 208.5), ( 'ppfcz4', 14, 16, 216.5), +( 'ppfcz4', 14, 17, 224.5), ( 'ppfcz4', 14, 18, 232.5), ( 'ppfcz4', 14, 19, 240.5), +( 'ppfcz4', 14, 20, 248.5), ( 'ppfcz4', 14, 21, 256.5), ( 'ppfcz4', 14, 22, 264.5), +( 'ppfcz4', 14, 23, 272.5), ( 'ppfcz4', 14, 24, 280.5), ( 'ppfcz4', 14, 25, 288.5), +( 'ppfcz4', 14, 26, 296.5), ( 'ppfcz4', 14, 27, 304.5), ( 'ppfcz4', 14, 28, 312.5), +( 'ppfcz4', 14, 29, 320.5), ( 'ppfcz4', 14, 30, 328.5), ( 'ppfcz5', 14, 2, 66.5), +( 'ppfcz5', 14, 3, 84.5), ( 'ppfcz5', 14, 4, 102.5), ( 'ppfcz5', 14, 5, 120.5), +( 'ppfcz5', 14, 6, 137.5), ( 'ppfcz5', 14, 7, 154.5), ( 'ppfcz5', 14, 8, 171.5), +( 'ppfcz5', 14, 9, 188.5), ( 'ppfcz5', 14, 10, 205.5), ( 'ppfcz5', 14, 11, 220.5), +( 'ppfcz5', 14, 12, 235.5), ( 'ppfcz5', 14, 13, 250.5), ( 'ppfcz5', 14, 14, 265.5), +( 'ppfcz5', 14, 15, 280.5), ( 'ppfcz5', 14, 16, 295.5), ( 'ppfcz5', 14, 17, 310.5), +( 'ppfcz5', 14, 18, 325.5), ( 'ppfcz5', 14, 19, 340.5), ( 'ppfcz5', 14, 20, 355.5), +( 'ppfcz5', 14, 21, 368.5), ( 'ppfcz5', 14, 22, 381.5), ( 'ppfcz5', 14, 23, 394.5), +( 'ppfcz5', 14, 24, 407.5), ( 'ppfcz5', 14, 25, 420.5), ( 'ppfcz5', 14, 26, 433.5), +( 'ppfcz5', 14, 27, 446.5), ( 'ppfcz5', 14, 28, 459.5), ( 'ppfcz5', 14, 29, 472.5), +( 'ppfcz5', 14, 30, 485.5), ( 'ppfcz1', 30, 0.5, 56.5), ( 'ppfcz1', 30, 1, 63.5), +( 'ppfcz1', 30, 1.5, 69.5), ( 'ppfcz1', 30, 2, 75.5), ( 'ppfcz1', 30, 2.5, 80.5), +( 'ppfcz1', 30, 3, 86.5), ( 'ppfcz1', 30, 3.5, 92.5), ( 'ppfcz1', 30, 4, 99.5), +( 'ppfcz1', 30, 4.5, 105.5), ( 'ppfcz1', 30, 5, 111.5), ( 'ppfcz1', 30, 6, 118.5), +( 'ppfcz1', 30, 7, 126.5), ( 'ppfcz1', 30, 8, 133.5), ( 'ppfcz1', 30, 9, 141.5), +( 'ppfcz1', 30, 10, 148.5), ( 'ppfcz1', 30, 11, 156.5), ( 'ppfcz1', 30, 12, 163.5), +( 'ppfcz1', 30, 13, 171.5), ( 'ppfcz1', 30, 14, 178.5), ( 'ppfcz1', 30, 15, 186.5), +( 'ppfcz1', 30, 16, 193.5), ( 'ppfcz1', 30, 17, 201.5), ( 'ppfcz1', 30, 18, 209.5), +( 'ppfcz1', 30, 19, 216.5), ( 'ppfcz1', 30, 20, 224.5), ( 'ppfcz1', 30, 21, 231.5), +( 'ppfcz1', 30, 22, 239.5), ( 'ppfcz1', 30, 23, 246.5), ( 'ppfcz1', 30, 24, 254.5), +( 'ppfcz1', 30, 25, 261.5), ( 'ppfcz1', 30, 26, 269.5), ( 'ppfcz1', 30, 27, 276.5), +( 'ppfcz1', 30, 28, 284.5), ( 'ppfcz1', 30, 29, 291.5), ( 'ppfcz1', 30, 30, 299.5), +( 'ppfcz2', 30, 0.5, 61.5), ( 'ppfcz2', 30, 1, 65.5), ( 'ppfcz2', 30, 1.5, 75.5), +( 'ppfcz2', 30, 2, 80.5), ( 'ppfcz2', 30, 2.5, 86.5), ( 'ppfcz2', 30, 3, 99.5), +( 'ppfcz2', 30, 3.5, 109.5), ( 'ppfcz2', 30, 4, 113.5), ( 'ppfcz2', 30, 4.5, 121.5), +( 'ppfcz2', 30, 5, 129.5), ( 'ppfcz2', 30, 6, 139.5), ( 'ppfcz2', 30, 7, 149.5), +( 'ppfcz2', 30, 8, 159.5), ( 'ppfcz2', 30, 9, 169.5), ( 'ppfcz2', 30, 10, 180.5), +( 'ppfcz2', 30, 11, 189.5), ( 'ppfcz2', 30, 12, 199.5), ( 'ppfcz2', 30, 13, 210.5), +( 'ppfcz2', 30, 14, 219.5), ( 'ppfcz2', 30, 15, 229.5), ( 'ppfcz2', 30, 16, 240.5), +( 'ppfcz2', 30, 17, 249.5), ( 'ppfcz2', 30, 18, 259.5), ( 'ppfcz2', 30, 19, 270.5), +( 'ppfcz2', 30, 20, 280.5), ( 'ppfcz2', 30, 21, 289.5), ( 'ppfcz2', 30, 22, 300.5), +( 'ppfcz2', 30, 23, 310.5), ( 'ppfcz2', 30, 24, 320.5), ( 'ppfcz2', 30, 25, 330.5), +( 'ppfcz2', 30, 26, 340.5), ( 'ppfcz2', 30, 27, 350.5), ( 'ppfcz2', 30, 28, 360.5), +( 'ppfcz2', 30, 29, 370.5), ( 'ppfcz2', 30, 30, 381.5), ( 'ppfcz3', 30, 0.5, 74.5), +( 'ppfcz3', 30, 1, 83.5), ( 'ppfcz3', 30, 1.5, 90.5), ( 'ppfcz3', 30, 2, 99.5), +( 'ppfcz3', 30, 2.5, 107.5), ( 'ppfcz3', 30, 3, 114.5), ( 'ppfcz3', 30, 3.5, 122.5), +( 'ppfcz3', 30, 4, 130.5), ( 'ppfcz3', 30, 4.5, 140.5), ( 'ppfcz3', 30, 5, 147.5), +( 'ppfcz3', 30, 6, 162.5), ( 'ppfcz3', 30, 7, 174.5), ( 'ppfcz3', 30, 8, 188.5), +( 'ppfcz3', 30, 9, 201.5), ( 'ppfcz3', 30, 10, 213.5), ( 'ppfcz3', 30, 11, 227.5), +( 'ppfcz3', 30, 12, 240.5), ( 'ppfcz3', 30, 13, 252.5), ( 'ppfcz3', 30, 14, 266.5), +( 'ppfcz3', 30, 15, 278.5), ( 'ppfcz3', 30, 16, 290.5), ( 'ppfcz3', 30, 17, 304.5), +( 'ppfcz3', 30, 18, 317.5), ( 'ppfcz3', 30, 19, 330.5), ( 'ppfcz3', 30, 20, 343.5), +( 'ppfcz3', 30, 21, 354.5), ( 'ppfcz3', 30, 22, 363.5), ( 'ppfcz3', 30, 23, 375.5), +( 'ppfcz3', 30, 24, 385.5), ( 'ppfcz3', 30, 25, 396.5), ( 'ppfcz3', 30, 26, 405.5), +( 'ppfcz3', 30, 27, 417.5), ( 'ppfcz3', 30, 28, 428.5), ( 'ppfcz3', 30, 29, 438.5), +( 'ppfcz3', 30, 30, 448.5), ( 'ppfcz4', 30, 0.5, 90.5), ( 'ppfcz4', 30, 1, 104.5), +( 'ppfcz4', 30, 1.5, 118.5), ( 'ppfcz4', 30, 2, 134.5), ( 'ppfcz4', 30, 2.5, 146.5), +( 'ppfcz4', 30, 3, 163.5), ( 'ppfcz4', 30, 3.5, 179.5), ( 'ppfcz4', 30, 4, 195.5), +( 'ppfcz4', 30, 4.5, 211.5), ( 'ppfcz4', 30, 5, 232.5), ( 'ppfcz4', 30, 6, 257.5), +( 'ppfcz4', 30, 7, 278.5), ( 'ppfcz4', 30, 8, 300.5), ( 'ppfcz4', 30, 9, 321.5), +( 'ppfcz4', 30, 10, 343.5), ( 'ppfcz4', 30, 11, 364.5), ( 'ppfcz4', 30, 12, 386.5), +( 'ppfcz4', 30, 13, 407.5), ( 'ppfcz4', 30, 14, 429.5), ( 'ppfcz4', 30, 15, 450.5), +( 'ppfcz4', 30, 16, 472.5), ( 'ppfcz4', 30, 17, 493.5), ( 'ppfcz4', 30, 18, 515.5), +( 'ppfcz4', 30, 19, 536.5), ( 'ppfcz4', 30, 20, 558.5), ( 'ppfcz4', 30, 21, 579.5), +( 'ppfcz4', 30, 22, 601.5), ( 'ppfcz4', 30, 23, 622.5), ( 'ppfcz4', 30, 24, 644.5), +( 'ppfcz4', 30, 25, 665.5), ( 'ppfcz4', 30, 26, 687.5), ( 'ppfcz4', 30, 27, 708.5), +( 'ppfcz4', 30, 28, 730.5), ( 'ppfcz4', 30, 29, 751.5), ( 'ppfcz4', 30, 30, 773.5), +( 'ppfcz5', 30, 0.5, 97.5), ( 'ppfcz5', 30, 1, 114.5), ( 'ppfcz5', 30, 1.5, 131.5), +( 'ppfcz5', 30, 2, 148.5), ( 'ppfcz5', 30, 2.5, 165.5), ( 'ppfcz5', 30, 3, 183.5), +( 'ppfcz5', 30, 3.5, 200.5), ( 'ppfcz5', 30, 4, 221.5), ( 'ppfcz5', 30, 4.5, 243.5), +( 'ppfcz5', 30, 5, 264.5), ( 'ppfcz5', 30, 6, 289.5), ( 'ppfcz5', 30, 7, 313.5), +( 'ppfcz5', 30, 8, 336.5), ( 'ppfcz5', 30, 9, 360.5), ( 'ppfcz5', 30, 10, 384.5), +( 'ppfcz5', 30, 11, 407.5), ( 'ppfcz5', 30, 12, 431.5), ( 'ppfcz5', 30, 13, 455.5), +( 'ppfcz5', 30, 14, 478.5), ( 'ppfcz5', 30, 15, 502.5), ( 'ppfcz5', 30, 16, 526.5), +( 'ppfcz5', 30, 17, 549.5), ( 'ppfcz5', 30, 18, 573.5), ( 'ppfcz5', 30, 19, 597.5), +( 'ppfcz5', 30, 20, 620.5), ( 'ppfcz5', 30, 21, 644.5), ( 'ppfcz5', 30, 22, 668.5), +( 'ppfcz5', 30, 23, 691.5), ( 'ppfcz5', 30, 24, 715.5), ( 'ppfcz5', 30, 25, 738.5), +( 'ppfcz5', 30, 26, 762.5), ( 'ppfcz5', 30, 27, 786.5), ( 'ppfcz5', 30, 28, 809.5), +( 'ppfcz5', 30, 29, 833.5), ( 'ppfcz5', 30, 30, 857.5), ( 'foreign', 13, 30, 0), +( 'all', 32, 10000, 23.2342007434944); + +--enable_query_log + +INSERT INTO t3 SELECT * FROM t1; + +EXPLAIN +SELECT d FROM t1, t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; +SELECT d FROM t1, t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; + +EXPLAIN +SELECT d FROM t3 AS t1, t2 AS t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; +SELECT d FROM t3 AS t1, t2 AS t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 1; + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 514e3074292..e1411e7fd46 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -972,6 +972,52 @@ EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; DROP TABLE t1; +# +# Bug #34731: highest possible value for INT erroneously filtered by WHERE +# + +# test UNSIGNED. only occurs when indexed. +CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1)); + +INSERT INTO t1 VALUES (127),(254),(0),(1),(255); + +# test upper bound +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256; +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0; +# count 4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255; + +# show we don't fiddle with lower bound on UNSIGNED +# count 0 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1; +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1; + +DROP TABLE t1; + + +# test signed. only occurs when index. +CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1)); + +INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127); + +# test upper bound +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128; +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0; +# count 4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127; + +# test lower bound +# count 5 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129; +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0; +# count 4 +SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128; + +DROP TABLE t1; + # End of 5.0 tests # BUG#22393 fix: Adjust 'ref' estimate if we have 'range' estimate for diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index d1ad7ab8ffa..4ab8a982e63 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -191,3 +191,17 @@ truncate t1; insert into t1 values (42); select * from t1; drop table t1; + +# +# Bug #35392: Delete all statement does not execute properly after +# few delete statements +# +CREATE TEMPORARY TABLE t1(a INT, b VARCHAR(20)); +INSERT INTO t1 VALUES(1, 'val1'), (2, 'val2'), (3, 'val3'); +DELETE FROM t1 WHERE a=1; +SELECT count(*) FROM t1; +DELETE FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo End of 5.1 tests |