diff options
Diffstat (limited to 'mysql-test')
28 files changed, 782 insertions, 32 deletions
diff --git a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test index b75a326d5a8..47e1ffb23c7 100644 --- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test +++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test @@ -234,8 +234,8 @@ select (@after-@before) >= 2; drop table t1,t2; commit; -# test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in the middle -# of a transaction +# test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in +# the middle of a transaction connection con2; begin; @@ -265,6 +265,68 @@ drop table t0,t2; # End of 4.1 tests +# +# Test behaviour of CREATE ... SELECT when mixing MyISAM and InnoDB tables +# + +set autocommit=0; +CREATE TABLE t1 (a int, b int) engine=myisam; +reset master; +INSERT INTO t1 values (1,1),(1,2); +--error 1062 +CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1; +# This should give warning +DROP TABLE if exists t2; +INSERT INTO t1 values (3,3); +--error 1062 +CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1; +ROLLBACK; +# This should give warning +DROP TABLE IF EXISTS t2; + +CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; +INSERT INTO t1 VALUES (4,4); +--error 1062 +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +SELECT * from t2; +TRUNCATE table t2; +INSERT INTO t1 VALUES (5,5); +--error 1062 +INSERT INTO t2 select * from t1; +SELECT * FROM t2; +DROP TABLE t2; + +INSERT INTO t1 values (6,6); +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; +INSERT INTO t1 values (7,7); +ROLLBACK; +INSERT INTO t1 values (8,8); +--error 1062 +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +COMMIT; +INSERT INTO t1 values (9,9); +--error 1062 +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ROLLBACK; +SELECT * from t2; +TRUNCATE table t2; +INSERT INTO t1 values (10,10); +--error 1062 +INSERT INTO t2 select * from t1; +SELECT * from t1; +INSERT INTO t2 values (100,100); +--error 1062 +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +COMMIT; +INSERT INTO t2 values (101,101); +--error 1062 +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ROLLBACK; +SELECT * from t2; +DROP TABLE t1,t2; +--replace_regex /table_id: [0-9]+/table_id: #/ +show binlog events from 102; + # Test for BUG#16559 (ROLLBACK should always have a zero error code in # binlog). Has to be here and not earlier, as the SELECTs influence # XIDs differently between normal and ps-protocol (and SHOW BINLOG @@ -283,3 +345,4 @@ disconnect con3; connection con4; select get_lock("a",10); # wait for rollback to finish + diff --git a/mysql-test/install_test_db.sh b/mysql-test/install_test_db.sh index 4554b92857e..9006957019a 100644 --- a/mysql-test/install_test_db.sh +++ b/mysql-test/install_test_db.sh @@ -34,7 +34,6 @@ if [ x$1 = x"-slave" ] then shift 1 data=var/slave-data - ldata=$fix_bin/var/slave-data else if [ x$1 = x"-1" ] then @@ -42,8 +41,8 @@ else else data=var/master-data fi - ldata=$fix_bin/$data fi +ldata=$fix_bin/$data mdata=$data/mysql EXTRA_ARG="" @@ -81,9 +80,7 @@ basedir=. EXTRA_ARG="--language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/" fi -mysqld_boot=" $execdir/mysqld --no-defaults --bootstrap --skip-grant-tables \ - --basedir=$basedir --datadir=$ldata --skip-innodb --skip-ndbcluster --skip-bdb \ - $EXTRA_ARG" +mysqld_boot=" $execdir/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=$basedir --datadir=$ldata --skip-innodb --skip-ndbcluster --skip-bdb --tmpdir=. $EXTRA_ARG" echo "running $mysqld_boot" if $scriptdir/mysql_create_system_tables test $mdata $hostname | $mysqld_boot diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 0087459e1dd..120ae00ca86 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1927,6 +1927,7 @@ sub install_db ($$) { mtr_add_arg($args, "--skip-innodb"); mtr_add_arg($args, "--skip-ndbcluster"); mtr_add_arg($args, "--skip-bdb"); + mtr_add_arg($args, "--tmpdir=."); if ( ! $opt_netware ) { diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index f56099f3a49..c018181b929 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -1121,7 +1121,10 @@ mysql_install_db () { if [ ! -z "$USE_NDBCLUSTER" ] then $ECHO "Installing Master Databases 1" - $INSTALL_DB -1 +# $INSTALL_DB -1 + $RM -rf var/master-data1 + mkdir var/master-data1 + cp -r var/master-data/* var/master-data1 if [ $? != 0 ]; then error "Could not install master test DBs 1" exit 1 @@ -1129,7 +1132,9 @@ mysql_install_db () { fi $ECHO "Installing Slave Databases" $RM -rf $SLAVE_MYDDIR $MY_LOG_DIR/* - $INSTALL_DB -slave +# $INSTALL_DB -slave + mkdir var/slave-data + cp -r var/master-data/* var/slave-data if [ $? != 0 ]; then error "Could not install slave test DBs" exit 1 @@ -2158,6 +2163,7 @@ then # Remove files that can cause problems $RM -rf $MYSQL_TEST_DIR/var/ndbcluster + $RM -rf $MYSQL_TEST_DIR/var/tmp/snapshot* $RM -f $MYSQL_TEST_DIR/var/run/* $MYSQL_TEST_DIR/var/tmp/* # Remove old berkeley db log files that can confuse the server diff --git a/mysql-test/r/binlog_row_mix_innodb_myisam.result b/mysql-test/r/binlog_row_mix_innodb_myisam.result index 078a95d5abd..84959684c42 100644 --- a/mysql-test/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/r/binlog_row_mix_innodb_myisam.result @@ -234,8 +234,6 @@ commit; begin; create temporary table ti (a int) engine=innodb; rollback; -Warnings: -Warning 1196 Some non-transactional changed tables couldn't be rolled back insert into ti values(1); set autocommit=0; create temporary table t1 (a int) engine=myisam; @@ -285,6 +283,162 @@ master-bin.000001 1260 Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 1294 Query 1 # use `test`; create table t2 (n int) engine=innodb do release_lock("lock1"); drop table t0,t2; +set autocommit=0; +CREATE TABLE t1 (a int, b int) engine=myisam; +reset master; +INSERT INTO t1 values (1,1),(1,2); +CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +DROP TABLE if exists t2; +Warnings: +Note 1051 Unknown table 't2' +INSERT INTO t1 values (3,3); +CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +DROP TABLE IF EXISTS t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; +INSERT INTO t1 VALUES (4,4); +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +TRUNCATE table t2; +INSERT INTO t1 VALUES (5,5); +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * FROM t2; +a b +DROP TABLE t2; +INSERT INTO t1 values (6,6); +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; +INSERT INTO t1 values (7,7); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +INSERT INTO t1 values (8,8); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +COMMIT; +INSERT INTO t1 values (9,9); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +SELECT * from t2; +a b +TRUNCATE table t2; +INSERT INTO t1 values (10,10); +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t1; +a b +1 1 +1 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +INSERT INTO t2 values (100,100); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +COMMIT; +INSERT INTO t2 values (101,101); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ROLLBACK; +SELECT * from t2; +a b +100 100 +DROP TABLE t1,t2; +show binlog events from 102; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 102 Table_map 1 142 table_id: # (test.t1) +master-bin.000001 142 Write_rows 1 189 table_id: # flags: STMT_END_F +master-bin.000001 189 Query 1 257 use `test`; BEGIN +master-bin.000001 257 Query 1 182 use `test`; CREATE TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT '0', + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB +master-bin.000001 439 Table_map 1 222 table_id: # (test.t2) +master-bin.000001 479 Write_rows 1 260 table_id: # flags: STMT_END_F +master-bin.000001 517 Xid 1 544 COMMIT /* xid= */ +master-bin.000001 544 Query 1 630 use `test`; DROP TABLE if exists t2 +master-bin.000001 630 Table_map 1 670 table_id: # (test.t1) +master-bin.000001 670 Write_rows 1 708 table_id: # flags: STMT_END_F +master-bin.000001 708 Query 1 776 use `test`; BEGIN +master-bin.000001 776 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT '0', + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB +master-bin.000001 968 Query 1 1039 use `test`; ROLLBACK +master-bin.000001 1039 Query 1 1125 use `test`; DROP TABLE IF EXISTS t2 +master-bin.000001 1125 Query 1 1249 use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb +master-bin.000001 1249 Table_map 1 1289 table_id: # (test.t1) +master-bin.000001 1289 Write_rows 1 1327 table_id: # flags: STMT_END_F +master-bin.000001 1327 Query 1 1395 use `test`; BEGIN +master-bin.000001 1395 Query 1 182 use `test`; CREATE TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT '0', + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB +master-bin.000001 1577 Table_map 1 222 table_id: # (test.t2) +master-bin.000001 1617 Write_rows 1 260 table_id: # flags: STMT_END_F +master-bin.000001 1655 Xid 1 1682 COMMIT /* xid= */ +master-bin.000001 1682 Query 1 80 use `test`; TRUNCATE table t2 +master-bin.000001 1762 Xid 1 1789 COMMIT /* xid= */ +master-bin.000001 1789 Table_map 1 1829 table_id: # (test.t1) +master-bin.000001 1829 Write_rows 1 1867 table_id: # flags: STMT_END_F +master-bin.000001 1867 Query 1 1935 use `test`; BEGIN +master-bin.000001 1935 Table_map 1 40 table_id: # (test.t2) +master-bin.000001 1975 Write_rows 1 78 table_id: # flags: STMT_END_F +master-bin.000001 2013 Xid 1 2040 COMMIT /* xid= */ +master-bin.000001 2040 Query 1 2116 use `test`; DROP TABLE t2 +master-bin.000001 2116 Table_map 1 2156 table_id: # (test.t1) +master-bin.000001 2156 Write_rows 1 2194 table_id: # flags: STMT_END_F +master-bin.000001 2194 Table_map 1 2234 table_id: # (test.t1) +master-bin.000001 2234 Write_rows 1 2272 table_id: # flags: STMT_END_F +master-bin.000001 2272 Table_map 1 2312 table_id: # (test.t1) +master-bin.000001 2312 Write_rows 1 2350 table_id: # flags: STMT_END_F +master-bin.000001 2350 Query 1 2418 use `test`; BEGIN +master-bin.000001 2418 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT '0', + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB +master-bin.000001 2610 Xid 1 2637 COMMIT /* xid= */ +master-bin.000001 2637 Table_map 1 2677 table_id: # (test.t1) +master-bin.000001 2677 Write_rows 1 2715 table_id: # flags: STMT_END_F +master-bin.000001 2715 Query 1 2783 use `test`; BEGIN +master-bin.000001 2783 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT '0', + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB +master-bin.000001 2975 Query 1 3046 use `test`; ROLLBACK +master-bin.000001 3046 Query 1 80 use `test`; TRUNCATE table t2 +master-bin.000001 3126 Xid 1 3153 COMMIT /* xid= */ +master-bin.000001 3153 Table_map 1 3193 table_id: # (test.t1) +master-bin.000001 3193 Write_rows 1 3231 table_id: # flags: STMT_END_F +master-bin.000001 3231 Query 1 3299 use `test`; BEGIN +master-bin.000001 3299 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT '0', + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB +master-bin.000001 3491 Xid 1 3518 COMMIT /* xid= */ +master-bin.000001 3518 Query 1 3622 use `test`; DROP TABLE `t1` /* generated by server */ reset master; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; diff --git a/mysql-test/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/r/binlog_stm_mix_innodb_myisam.result index c5abcff4246..e836cae0b15 100644 --- a/mysql-test/r/binlog_stm_mix_innodb_myisam.result +++ b/mysql-test/r/binlog_stm_mix_innodb_myisam.result @@ -209,8 +209,6 @@ commit; begin; create temporary table ti (a int) engine=innodb; rollback; -Warnings: -Warning 1196 Some non-transactional changed tables couldn't be rolled back insert into ti values(1); set autocommit=0; create temporary table t1 (a int) engine=myisam; @@ -256,6 +254,107 @@ master-bin.000001 1654 Query 1 # use `test`; create table t2 (n int) engine=inno master-bin.000001 1754 Query 1 # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`t1`,`test`.`ti` do release_lock("lock1"); drop table t0,t2; +set autocommit=0; +CREATE TABLE t1 (a int, b int) engine=myisam; +reset master; +INSERT INTO t1 values (1,1),(1,2); +CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +DROP TABLE if exists t2; +Warnings: +Note 1051 Unknown table 't2' +INSERT INTO t1 values (3,3); +CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +DROP TABLE IF EXISTS t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; +INSERT INTO t1 VALUES (4,4); +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +TRUNCATE table t2; +INSERT INTO t1 VALUES (5,5); +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * FROM t2; +a b +DROP TABLE t2; +INSERT INTO t1 values (6,6); +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; +INSERT INTO t1 values (7,7); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +INSERT INTO t1 values (8,8); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +COMMIT; +INSERT INTO t1 values (9,9); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +SELECT * from t2; +a b +TRUNCATE table t2; +INSERT INTO t1 values (10,10); +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t1; +a b +1 1 +1 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +INSERT INTO t2 values (100,100); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +COMMIT; +INSERT INTO t2 values (101,101); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ROLLBACK; +SELECT * from t2; +a b +100 100 +DROP TABLE t1,t2; +show binlog events from 102; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 102 Query 1 198 use `test`; INSERT INTO t1 values (1,1),(1,2) +master-bin.000001 198 Query 1 284 use `test`; DROP TABLE if exists t2 +master-bin.000001 284 Query 1 374 use `test`; INSERT INTO t1 values (3,3) +master-bin.000001 374 Query 1 460 use `test`; DROP TABLE IF EXISTS t2 +master-bin.000001 460 Query 1 584 use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb +master-bin.000001 584 Query 1 674 use `test`; INSERT INTO t1 VALUES (4,4) +master-bin.000001 674 Query 1 80 use `test`; TRUNCATE table t2 +master-bin.000001 754 Xid 1 781 COMMIT /* xid= */ +master-bin.000001 781 Query 1 871 use `test`; INSERT INTO t1 VALUES (5,5) +master-bin.000001 871 Query 1 947 use `test`; DROP TABLE t2 +master-bin.000001 947 Query 1 1037 use `test`; INSERT INTO t1 values (6,6) +master-bin.000001 1037 Query 1 1171 use `test`; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb +master-bin.000001 1171 Query 1 1261 use `test`; INSERT INTO t1 values (7,7) +master-bin.000001 1261 Query 1 1351 use `test`; INSERT INTO t1 values (8,8) +master-bin.000001 1351 Query 1 1441 use `test`; INSERT INTO t1 values (9,9) +master-bin.000001 1441 Query 1 80 use `test`; TRUNCATE table t2 +master-bin.000001 1521 Xid 1 1548 COMMIT /* xid= */ +master-bin.000001 1548 Query 1 1640 use `test`; INSERT INTO t1 values (10,10) +master-bin.000001 1640 Query 1 1708 use `test`; BEGIN +master-bin.000001 1708 Query 1 94 use `test`; INSERT INTO t2 values (100,100) +master-bin.000001 1802 Xid 1 1829 COMMIT /* xid= */ +master-bin.000001 1829 Query 1 1908 use `test`; DROP TABLE t1,t2 reset master; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index d45960cf787..6f8c319eb6f 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -266,6 +266,7 @@ select * from t1; 0 1 2 0 0 1 drop table t1; +flush status; create table t1 (a int not null, b int, primary key (a)); insert into t1 values (1,1); create table if not exists t1 select 2; @@ -281,6 +282,13 @@ Warnings: Note 1050 Table 't1' already exists create table if not exists t1 select 3 as 'a',3 as 'b'; ERROR 23000: Duplicate entry '3' for key 'PRIMARY' +show warnings; +Level Code Message +Note 1050 Table 't1' already exists +Error 1062 Duplicate entry '3' for key 'PRIMARY' +show status like "Opened_tables"; +Variable_name Value +Opened_tables 2 select * from t1; a b 1 1 @@ -778,3 +786,41 @@ Warnings: Warning 1071 Specified key was too long; max key length is 765 bytes insert into t1 values('aaa'); drop table t1; +CREATE TABLE t1 (a int, b int); +insert into t1 values (1,1),(1,2); +CREATE TABLE t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +drop table if exists t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +drop table if exists t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TABLE t2 (a int, b int, primary key (a)); +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +1 1 +TRUNCATE table t2; +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +1 1 +drop table t2; +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +1 1 +TRUNCATE table t2; +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +1 1 +drop table t1,t2; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index 5f735ebe926..b3d6e10448b 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -1601,6 +1601,7 @@ fld_cid fld_name fld_parentid fld_delt 5 Torkel 0 0 DROP TABLE federated.t1; DROP TABLE federated.bug_17377_table; +DROP TABLE federated.t1; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 62b4ddf868a..773efe50749 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -309,6 +309,12 @@ a grp 1 2 2 4,3 3 5 +select group_concat(c order by (select concat(5-t1.c,group_concat(c order by a)) from t2 where t2.a=t1.a)) as grp from t1; +grp +5,4,3,2 +select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1; +grp +2,3,4,5 select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp; a c grp 3 5 3,3 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index b5fd35d926f..b9b717cb39c 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -758,6 +758,7 @@ time_format('100:00:00', '%H %k %h %I %l') 100 100 04 04 4 create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); +drop function if exists t_slow_sysdate; create function t_slow_sysdate() returns timestamp begin do sleep(2); diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 878c5cb5451..fe9940f753f 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1 +1,59 @@ -drop table if exists t1; +drop table if exists t1.t2; +set storage_engine=innodb; +CREATE TABLE t1 (a int, b int); +insert into t1 values (1,1),(1,2); +CREATE TABLE t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +drop table if exists t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +drop table if exists t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TABLE t2 (a int, b int, primary key (a)); +BEGIN; +INSERT INTO t2 values(100,100); +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +100 100 +ROLLBACK; +SELECT * from t2; +a b +100 100 +TRUNCATE table t2; +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +drop table t2; +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); +BEGIN; +INSERT INTO t2 values(100,100); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +100 100 +COMMIT; +BEGIN; +INSERT INTO t2 values(101,101); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +100 100 +101 101 +ROLLBACK; +SELECT * from t2; +a b +100 100 +TRUNCATE table t2; +INSERT INTO t2 select * from t1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +SELECT * from t2; +a b +drop table t1,t2; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 5e9ab480558..18bd6d7e796 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -2,8 +2,8 @@ drop table if exists t1,t2,t3; create table t1 (a int not null); insert into t1 values (1); insert into t1 values (a+2); -insert into t1 values (a+3); -insert into t1 values (4),(a+5); +insert into t1 values (a+3),(a+4); +insert into t1 values (5),(a+6); select * from t1; a 1 @@ -11,6 +11,7 @@ a 3 4 5 +6 drop table t1; create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username)); insert into t1 values (0,"mysql"); @@ -299,3 +300,24 @@ select count(*) from t2; count(*) 25500 drop table t1,t2,t3; +create table t1 (a int, b int); +insert into t1 (a,b) values (a,b); +insert into t1 SET a=1, b=a+1; +insert into t1 (a,b) select 1,2; +INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a); +prepare stmt1 from ' replace into t1 (a,a) select 100, ''hundred'' '; +execute stmt1; +ERROR 42000: Column 'a' specified twice +insert into t1 (a,b,b) values (1,1,1); +ERROR 42000: Column 'b' specified twice +insert into t1 (a,a) values (1,1,1); +ERROR 21S01: Column count doesn't match value count at row 1 +insert into t1 (a,a) values (1,1); +ERROR 42000: Column 'a' specified twice +insert into t1 SET a=1,b=2,a=1; +ERROR 42000: Column 'a' specified twice +insert into t1 (b,b) select 1,2; +ERROR 42000: Column 'b' specified twice +INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a); +ERROR 42000: Column 'b' specified twice +drop table t1; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 0b314defece..72beee4b2e3 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -115,6 +115,15 @@ select @a, @b; @a @b NULL 15 truncate table t1; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 set c=b; +Warnings: +Warning 1261 Row 1 doesn't contain data for all columns +Warning 1261 Row 2 doesn't contain data for all columns +select * from t1; +a b c +NULL 10 10 +NULL 15 15 +truncate table t1; load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow"; select * from t1; a b c diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index ea02a703c65..6fdd105fd6c 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -519,3 +519,83 @@ a 30 drop view v1; drop table t1, t2; +create table t1 (i1 int, i2 int, i3 int); +create table t2 (id int, c1 varchar(20), c2 varchar(20)); +insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); +insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); +select * from t1 order by i1; +i1 i2 i3 +1 5 10 +2 2 2 +3 7 12 +4 5 2 +9 10 15 +select * from t2; +id c1 c2 +9 abc def +5 opq lmn +2 test t t test +update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; +select * from t1 order by i1; +i1 i2 i3 +1 5 10 +2 15 2 +3 7 12 +4 5 2 +9 15 15 +select * from t2 order by id; +id c1 c2 +2 test t ppc +5 opq lmn +9 abc ppc +delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; +select * from t1 order by i1; +i1 i2 i3 +2 15 2 +3 7 12 +9 15 15 +select * from t2 order by id; +id c1 c2 +2 test t ppc +9 abc ppc +drop table t1, t2; +create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1)); +create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id)); +insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); +insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); +select * from t1 order by i1; +i1 i2 i3 +1 5 10 +2 2 2 +3 7 12 +4 5 2 +9 10 15 +select * from t2 order by id; +id c1 c2 +2 test t t test +5 opq lmn +9 abc def +update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; +select * from t1 order by i1; +i1 i2 i3 +1 5 10 +2 15 2 +3 7 12 +4 5 2 +9 15 15 +select * from t2 order by id; +id c1 c2 +2 test t ppc +5 opq lmn +9 abc ppc +delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; +select * from t1 order by i1; +i1 i2 i3 +2 15 2 +3 7 12 +9 15 15 +select * from t2 order by id; +id c1 c2 +2 test t ppc +9 abc ppc +drop table t1, t2; diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result index 497ad973e8b..e18125f159e 100644 --- a/mysql-test/r/ndb_index_unique.result +++ b/mysql-test/r/ndb_index_unique.result @@ -144,7 +144,7 @@ b int unsigned not null, c int unsigned, UNIQUE USING HASH (b, c) ) engine=ndbcluster; -ERROR 42000: Column 'c' is used with UNIQUE or INDEX but is not defined as NOT NULL +ERROR 42000: Table handler doesn't support NULL in given index. Please change column 'c' to be NOT NULL or use another handler CREATE TABLE t3 ( a int unsigned NOT NULL, b int unsigned not null, diff --git a/mysql-test/r/ndb_replace.result b/mysql-test/r/ndb_replace.result index 5e49968ca64..cdcd935bfcc 100644 --- a/mysql-test/r/ndb_replace.result +++ b/mysql-test/r/ndb_replace.result @@ -30,4 +30,4 @@ REPLACE INTO t1 (i,j) VALUES (17,2); SELECT * from t1 ORDER BY i; i j k 3 1 42 -17 2 24 +17 2 NULL diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result index e828f2c1e31..cfa2b4dc76d 100644 --- a/mysql-test/r/rpl_ddl.result +++ b/mysql-test/r/rpl_ddl.result @@ -359,8 +359,6 @@ MAX(f1) -------- switch to master ------- ROLLBACK; -Warnings: -Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT MAX(f1) FROM t1; MAX(f1) 5 @@ -579,8 +577,6 @@ MAX(f1) -------- switch to master ------- ROLLBACK; -Warnings: -Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT MAX(f1) FROM t1; MAX(f1) 8 diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 928e3635ec6..2a27dba6101 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -1,5 +1,5 @@ drop database if exists mysqltest; -drop view if exists v1; +drop view if exists v1,v2,v3; grant create view on test.* to test@localhost; show grants for test@localhost; Grants for test@localhost diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index fe8cfe70c4e..e4a7d1cd9af 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -226,6 +226,7 @@ drop table t1; # Test create table if not exists with duplicate key error # +flush status; create table t1 (a int not null, b int, primary key (a)); insert into t1 values (1,1); create table if not exists t1 select 2; @@ -233,6 +234,8 @@ select * from t1; create table if not exists t1 select 3 as 'a',4 as 'b'; --error 1062 create table if not exists t1 select 3 as 'a',3 as 'b'; +show warnings; +show status like "Opened_tables"; select * from t1; drop table t1; @@ -676,3 +679,37 @@ insert into t1 values('aaa'); drop table t1; # End of 5.0 tests + +# +# Test of behaviour with CREATE ... SELECT +# + +CREATE TABLE t1 (a int, b int); +insert into t1 values (1,1),(1,2); +--error 1062 +CREATE TABLE t2 (primary key (a)) select * from t1; +# This should give warning +drop table if exists t2; +--error 1062 +CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; +# This should give warning +drop table if exists t2; +CREATE TABLE t2 (a int, b int, primary key (a)); +--error 1062 +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +SELECT * from t2; +TRUNCATE table t2; +--error 1062 +INSERT INTO t2 select * from t1; +SELECT * from t2; +drop table t2; + +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); +--error 1062 +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +SELECT * from t2; +TRUNCATE table t2; +--error 1062 +INSERT INTO t2 select * from t1; +SELECT * from t2; +drop table t1,t2; diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 3c43fb1d1f9..f6799f3630a 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1,6 +1,6 @@ # should work with embedded server after mysqltest is fixed --- source include/not_embedded.inc -source include/federated.inc; +--source include/not_embedded.inc +--source include/federated.inc connection slave; DROP TABLE IF EXISTS federated.t1; @@ -1310,6 +1310,57 @@ select * from federated.t1 where fld_parentid=0 and fld_delt=0; DROP TABLE federated.t1; connection slave; DROP TABLE federated.bug_17377_table; +DROP TABLE federated.t1; + +# +# Test multi updates and deletes without keys +# + +# The following can be enabled when bug #19773 has been fixed +--disable_parsing +connection slave; +create table federated.t1 (i1 int, i2 int, i3 int); +create table federated.t2 (id int, c1 varchar(20), c2 varchar(20)); +connection master; +eval create table federated.t1 (i1 int, i2 int, i3 int) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; +eval create table federated.t2 (id int, c1 varchar(20), c2 varchar(20)) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2'; +insert into federated.t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); +insert into federated.t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); +select * from federated.t1 order by i1; +select * from federated.t2; +update federated.t1,federated.t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; +select * from federated.t1 order by i1; +select * from federated.t2 order by id; +delete t1.*,t2.* from federated.t1,federated.t2 where t1.i2=t2.id; +select * from federated.t1 order by i1; +select * from federated.t2 order by id; +drop table federated.t1, federated.t2; +connection slave; +drop table federated.t1, federated.t2; +connection master; +# Test multi updates and deletes with keys + +connection slave; +create table federated.t1 (i1 int, i2 int, i3 int, primary key (i1)); +create table federated.t2 (id int, c1 varchar(20), c2 varchar(20), primary key (id)); +connection master; +eval create table federated.t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1)) ENGINE=FEDERATED ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; +eval create table federated.t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id)) ENGINE=FEDERATED ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2'; +insert into federated.t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); +insert into federated.t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); +select * from federated.t1 order by i1; +select * from federated.t2 order by id; +update federated.t1,federated.t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; +select * from federated.t1 order by i1; +select * from federated.t2 order by id; +delete t1.*,t2.* from federated.t1,federated.t2 where t1.i2=t2.id; +select * from federated.t1 order by i1; +select * from federated.t2 order by id; +drop table federated.t1, federated.t2; +connection slave; +drop table federated.t1, federated.t2; +connection master; +--enable_parsing -source include/federated_cleanup.inc; +--source include/federated_cleanup.inc diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index fbfdfa3b5d0..7fd7edddf28 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -176,8 +176,8 @@ select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1; # The following returns random results as we are sorting on blob addresses -# select group_concat(c order by (select group_concat(c order by a) from t2 where t2.a=t1.a)) as grp from t1; -# select group_concat(c order by (select group_concat(c) from t2 where a=t1.a)) as grp from t1; +select group_concat(c order by (select concat(5-t1.c,group_concat(c order by a)) from t2 where t2.a=t1.a)) as grp from t1; +select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1; select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp; drop table t1,t2; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 4e4fb8f777a..229b24c73ca 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -379,6 +379,7 @@ select time_format('100:00:00', '%H %k %h %I %l'); create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); delimiter //; +drop function if exists t_slow_sysdate; create function t_slow_sysdate() returns timestamp begin do sleep(2); diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index b942b9fbc0d..c0fb3ab917b 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -1,5 +1,54 @@ -- source include/have_innodb.inc --disable_warnings -drop table if exists t1; +drop table if exists t1.t2; --enable_warnings + +# +# Test of behaviour with CREATE ... SELECT +# + +set storage_engine=innodb; +CREATE TABLE t1 (a int, b int); +insert into t1 values (1,1),(1,2); +--error 1062 +CREATE TABLE t2 (primary key (a)) select * from t1; +# This should give warning +drop table if exists t2; +--error 1062 +CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; +# This should give warning +drop table if exists t2; +CREATE TABLE t2 (a int, b int, primary key (a)); +BEGIN; +INSERT INTO t2 values(100,100); +--error 1062 +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +SELECT * from t2; +ROLLBACK; +SELECT * from t2; +TRUNCATE table t2; +--error 1062 +INSERT INTO t2 select * from t1; +SELECT * from t2; +drop table t2; + +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); +BEGIN; +INSERT INTO t2 values(100,100); +--error 1062 +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +SELECT * from t2; +COMMIT; +BEGIN; +INSERT INTO t2 values(101,101); +--error 1062 +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +SELECT * from t2; +ROLLBACK; +SELECT * from t2; +TRUNCATE table t2; +--error 1062 +INSERT INTO t2 select * from t1; +SELECT * from t2; +drop table t1,t2; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index f3dd8e7e199..e2514083ea7 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -9,8 +9,8 @@ drop table if exists t1,t2,t3; create table t1 (a int not null); insert into t1 values (1); insert into t1 values (a+2); -insert into t1 values (a+3); -insert into t1 values (4),(a+5); +insert into t1 values (a+3),(a+4); +insert into t1 values (5),(a+6); select * from t1; drop table t1; @@ -176,3 +176,28 @@ insert into t2 select t1.* from t1, t2 t, t3 where t1.id1 = t.id2 and t.id2 = select count(*) from t2; drop table t1,t2,t3; +# +# Test different cases of duplicate fields +# + +create table t1 (a int, b int); +insert into t1 (a,b) values (a,b); +insert into t1 SET a=1, b=a+1; +insert into t1 (a,b) select 1,2; +INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a); +prepare stmt1 from ' replace into t1 (a,a) select 100, ''hundred'' '; +--error 1110 +execute stmt1; +--error 1110 +insert into t1 (a,b,b) values (1,1,1); +--error 1136 +insert into t1 (a,a) values (1,1,1); +--error 1110 +insert into t1 (a,a) values (1,1); +--error 1110 +insert into t1 SET a=1,b=2,a=1; +--error 1110 +insert into t1 (b,b) select 1,2; +--error 1110 +INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a); +drop table t1; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index fcd127e3e98..aff5efa51cb 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -92,6 +92,10 @@ load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, @b); select * from t1; select @a, @b; truncate table t1; +# Reading of all columns with set +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 set c=b; +select * from t1; +truncate table t1; # now going to test fixed field-row file format load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow"; select * from t1; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index acc816ae921..04c33e9d709 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -532,3 +532,33 @@ select * from t1; select * from t2; drop view v1; drop table t1, t2; + +# +# Test multi updates and deletes using primary key and without. +# +create table t1 (i1 int, i2 int, i3 int); +create table t2 (id int, c1 varchar(20), c2 varchar(20)); +insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); +insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); +select * from t1 order by i1; +select * from t2; +update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; +select * from t1 order by i1; +select * from t2 order by id; +delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; +select * from t1 order by i1; +select * from t2 order by id; +drop table t1, t2; +create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1)); +create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id)); +insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); +insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); +select * from t1 order by i1; +select * from t2 order by id; +update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; +select * from t1 order by i1; +select * from t2 order by id; +delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; +select * from t1 order by i1; +select * from t2 order by id; +drop table t1, t2; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 8deff474587..e057ba537f6 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -3,7 +3,7 @@ --disable_warnings drop database if exists mysqltest; -drop view if exists v1; +drop view if exists v1,v2,v3; --enable_warnings diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index 69e4ee933b8..1a08abcf953 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -173,3 +173,17 @@ fun:my_thread_init fun:kill_server_thread } + +# +# Warning when printing stack trace (to suppress some not needed warnings) +# + +{ + vprintf on stacktrace + Memcheck:Cond + fun:vfprintf + fun:uffered_vfprintf + fun:vfprintf + fun:fprintf + fun:print_stacktrace +} |