diff options
Diffstat (limited to 'mysql-test')
94 files changed, 1646 insertions, 378 deletions
diff --git a/mysql-test/Makefile.am b/mysql-test/Makefile.am index 1e9371e5bf2..774008bc106 100644 --- a/mysql-test/Makefile.am +++ b/mysql-test/Makefile.am @@ -71,7 +71,7 @@ dist-hook: $(INSTALL_DATA) $(srcdir)/std_data/ndb_backup50/BACKUP* $(distdir)/std_data/ndb_backup50 $(INSTALL_DATA) $(srcdir)/std_data/ndb_backup51/BACKUP* $(distdir)/std_data/ndb_backup51 $(INSTALL_DATA) $(srcdir)/lib/*.pl $(distdir)/lib - -rm -rf `find $(distdir)/suite -type d -name SCCS` + -rm -rf `find $(distdir)/suite -type d -name SCCS` $(distdir)/suite/row_lock install-data-local: $(mkinstalldirs) \ @@ -113,7 +113,7 @@ install-data-local: $(INSTALL_DATA) $(srcdir)/std_data/ndb_backup50/BACKUP* $(DESTDIR)$(testdir)/std_data/ndb_backup50 $(INSTALL_DATA) $(srcdir)/std_data/ndb_backup51/BACKUP* $(DESTDIR)$(testdir)/std_data/ndb_backup51 $(INSTALL_DATA) $(srcdir)/lib/*.pl $(DESTDIR)$(testdir)/lib - for f in `(cd $(srcdir); find suite -type f | grep -v SCCS)`; \ + for f in `(cd $(srcdir); find suite -type f | egrep -v 'SCCS|row_lock')`; \ do \ d=$(DESTDIR)$(testdir)/`dirname $$f`; \ mkdir -p $$d ; \ diff --git a/mysql-test/extra/binlog_tests/binlog.test b/mysql-test/extra/binlog_tests/binlog.test index 779ac122f93..e838acbed35 100644 --- a/mysql-test/extra/binlog_tests/binlog.test +++ b/mysql-test/extra/binlog_tests/binlog.test @@ -2,6 +2,7 @@ # misc binlogging tests that do not require a slave running # +-- source include/have_log_bin.inc -- source include/not_embedded.inc -- source include/have_innodb.inc -- source include/have_log_bin.inc diff --git a/mysql-test/extra/binlog_tests/drop_temp_table.test b/mysql-test/extra/binlog_tests/drop_temp_table.test index 75e3afc9380..7d37fca2bef 100644 --- a/mysql-test/extra/binlog_tests/drop_temp_table.test +++ b/mysql-test/extra/binlog_tests/drop_temp_table.test @@ -1,5 +1,3 @@ ---source include/have_log_bin.inc - --disable_warnings drop database if exists `drop-temp+table-test`; diff --git a/mysql-test/include/federated.inc b/mysql-test/include/federated.inc index dde24cd8198..925ecdd9682 100644 --- a/mysql-test/include/federated.inc +++ b/mysql-test/include/federated.inc @@ -1,3 +1,4 @@ +--source include/have_log_bin.inc --source include/not_embedded.inc --source ./include/have_federated_db.inc diff --git a/mysql-test/include/have_archive.inc b/mysql-test/include/have_archive.inc index 9f0038db97a..82399ca4c6c 100644 --- a/mysql-test/include/have_archive.inc +++ b/mysql-test/include/have_archive.inc @@ -1,4 +1,4 @@ --disable_query_log --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'archive'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'archive'; --enable_query_log diff --git a/mysql-test/include/have_blackhole.inc b/mysql-test/include/have_blackhole.inc index e13cff52094..6c4da01d61d 100644 --- a/mysql-test/include/have_blackhole.inc +++ b/mysql-test/include/have_blackhole.inc @@ -1,4 +1,4 @@ disable_query_log; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'blackhole'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'blackhole'; enable_query_log; diff --git a/mysql-test/include/have_csv.inc b/mysql-test/include/have_csv.inc index 3175fc16fe7..410caa95285 100644 --- a/mysql-test/include/have_csv.inc +++ b/mysql-test/include/have_csv.inc @@ -1,4 +1,4 @@ disable_query_log; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'csv'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'csv'; enable_query_log; diff --git a/mysql-test/include/have_exampledb.inc b/mysql-test/include/have_exampledb.inc index e3fd068b485..db3985e3c7c 100644 --- a/mysql-test/include/have_exampledb.inc +++ b/mysql-test/include/have_exampledb.inc @@ -1,4 +1,4 @@ disable_query_log; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'example'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'example'; enable_query_log; diff --git a/mysql-test/include/have_federated_db.inc b/mysql-test/include/have_federated_db.inc index abef5a64d30..041a29f460b 100644 --- a/mysql-test/include/have_federated_db.inc +++ b/mysql-test/include/have_federated_db.inc @@ -1,4 +1,4 @@ disable_query_log; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'federated'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'federated'; enable_query_log; diff --git a/mysql-test/include/have_innodb.inc b/mysql-test/include/have_innodb.inc index be8850725e5..cbffe6a2574 100644 --- a/mysql-test/include/have_innodb.inc +++ b/mysql-test/include/have_innodb.inc @@ -1,4 +1,4 @@ disable_query_log; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'innodb'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'innodb'; enable_query_log; diff --git a/mysql-test/include/have_multi_ndb.inc b/mysql-test/include/have_multi_ndb.inc index 819518b2674..e9baf7d56e5 100644 --- a/mysql-test/include/have_multi_ndb.inc +++ b/mysql-test/include/have_multi_ndb.inc @@ -10,7 +10,7 @@ drop table if exists t1, t2; --enable_warnings flush tables; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'ndbcluster'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'ndbcluster'; enable_query_log; # Check that server2 has NDB support @@ -21,7 +21,7 @@ drop table if exists t1, t2; --enable_warnings flush tables; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'ndbcluster'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'ndbcluster'; enable_query_log; # Check should be here as well... diff --git a/mysql-test/include/have_ndb.inc b/mysql-test/include/have_ndb.inc index c9603634508..638a88f3956 100644 --- a/mysql-test/include/have_ndb.inc +++ b/mysql-test/include/have_ndb.inc @@ -1,7 +1,7 @@ # Check that server is compiled and started with support for NDB disable_query_log; --require r/true.require -select support = 'Enabled' as `TRUE` from information_schema.engines where engine = 'ndbcluster'; +select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'ndbcluster'; enable_query_log; diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 98323e44e97..cc2f0e0588e 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -641,6 +641,37 @@ alter table t1 comment '123'; show create table t1; drop table t1; +# +# Bug #25866: Getting "#HY000 Can't find record in..." on and INSERT +# +CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8; +INSERT INTO t1 VALUES ('uk'),('bg'); +SELECT * FROM t1 WHERE a = 'uk'; +DELETE FROM t1 WHERE a = 'uk'; +SELECT * FROM t1 WHERE a = 'uk'; +UPDATE t1 SET a = 'us' WHERE a = 'uk'; +SELECT * FROM t1 WHERE a = 'uk'; + +CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB; +INSERT INTO t2 VALUES ('uk'),('bg'); +SELECT * FROM t2 WHERE a = 'uk'; +DELETE FROM t2 WHERE a = 'uk'; +SELECT * FROM t2 WHERE a = 'uk'; +INSERT INTO t2 VALUES ('uk'); +UPDATE t2 SET a = 'us' WHERE a = 'uk'; +SELECT * FROM t2 WHERE a = 'uk'; + +CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM; +INSERT INTO t3 VALUES ('uk'),('bg'); +SELECT * FROM t3 WHERE a = 'uk'; +DELETE FROM t3 WHERE a = 'uk'; +SELECT * FROM t3 WHERE a = 'uk'; +INSERT INTO t3 VALUES ('uk'); +UPDATE t3 SET a = 'us' WHERE a = 'uk'; +SELECT * FROM t3 WHERE a = 'uk'; + +DROP TABLE t1,t2,t3; + --echo End of 5.0 tests # diff --git a/mysql-test/include/rpl_events.inc b/mysql-test/include/rpl_events.inc index 04885f31997..bbe52d3628b 100644 --- a/mysql-test/include/rpl_events.inc +++ b/mysql-test/include/rpl_events.inc @@ -29,6 +29,9 @@ SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name # wait 3 seconds, so the event can trigger --real_sleep 3 +let $wait_condition= + SELECT count(*) = 1 FROM t1 WHERE c = 'from justonce'; +--source include/wait_condition.inc # check that table t1 contains something --echo "in the master" diff --git a/mysql-test/include/unsafe_binlog.inc b/mysql-test/include/unsafe_binlog.inc index 6fbbdcb6d6c..2fc2ad77bc9 100644 --- a/mysql-test/include/unsafe_binlog.inc +++ b/mysql-test/include/unsafe_binlog.inc @@ -23,7 +23,7 @@ # --disable_warnings -drop table if exists t1,t2; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; --enable_warnings eval create table t1 (id int not null, f_id int not null, f int not null, primary key(f_id, id)) engine = $engine_type; @@ -59,7 +59,7 @@ set autocommit = 0; # # S-lock to records (2,2),(4,2), and (6,2) should not be released in a update # ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT select * from t1 where a = 2 and b = 2 for update; connection a; commit; @@ -213,39 +213,39 @@ set autocommit = 0; create table t10(a int not null, b int, primary key(a)) select * from t2 for update; connection b; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection c; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection d; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection e; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection f; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection g; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection h; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection i; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection j; ---error 1205 +--error ER_LOCK_WAIT_TIMEOUT reap; connection a; diff --git a/mysql-test/include/wait_for_slave_io_to_stop.inc b/mysql-test/include/wait_for_slave_io_to_stop.inc index 87be1819227..004f8bbbd8d 100644 --- a/mysql-test/include/wait_for_slave_io_to_stop.inc +++ b/mysql-test/include/wait_for_slave_io_to_stop.inc @@ -6,10 +6,12 @@ #Details: # 1) Fill in and setup variables # 2) loop through looking for -# IO thread to stop +# sql threads to stop # 3) If loops too long die. #################################################### connection slave; +let $my_show= SHOW SLAVE STATUS; +let $sql_running= Slave_IO_Running; let $row_number= 1; let $run= 1; let $counter= 300; diff --git a/mysql-test/include/wait_for_slave_param.inc b/mysql-test/include/wait_for_slave_param.inc new file mode 100644 index 00000000000..fed97195aba --- /dev/null +++ b/mysql-test/include/wait_for_slave_param.inc @@ -0,0 +1,26 @@ +# include/wait_for_slave_param.inc +# +# SUMMARY +# +# Waits until SHOW SLAVE STATUS has returned a spicified value. +# +# USAGE +# +# let $slave_param= Slave_SQL_Running; +# let $slave_param_value= No; +# --source include/slave_wait_param.inc + +let $slave_wait_param_counter= 300; +let $slave_value= query_get_value("SHOW SLAVE STATUS", $slave_param, 1); +while (`select "$slave_value" != "$slave_param_value"`) +{ + dec $slave_wait_param_counter; + if (!$slave_wait_param_counter) + { + --echo ERROR: failed while waiting for slave parameter $slave_param: $slave_param_value + query_vertical show slave status; + exit; + } + sleep 0.1; + let $slave_value= query_get_value("SHOW SLAVE STATUS", $slave_param, 1); +} diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 8354392d1f8..970790f4141 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3821,8 +3821,7 @@ sub mysqld_arguments ($$$$) { "%s--log-slow-queries=%s-slow.log", $prefix, $log_base_path); # Check if "extra_opt" contains --skip-log-bin - my $skip_binlog= grep(/^--skip-log-bin/, @$extra_opt); - + my $skip_binlog= grep(/^--skip-log-bin/, @$extra_opt, @opt_extra_mysqld_opt); if ( $mysqld->{'type'} eq 'master' ) { if (! ($opt_skip_master_binlog || $skip_binlog) ) diff --git a/mysql-test/ndb/ndb_config_1_node.ini b/mysql-test/ndb/ndb_config_1_node.ini index 787bf82d391..4e0be7796dc 100644 --- a/mysql-test/ndb/ndb_config_1_node.ini +++ b/mysql-test/ndb/ndb_config_1_node.ini @@ -10,7 +10,8 @@ DataDir= CHOOSE_FILESYSTEM MaxNoOfOrderedIndexes= CHOOSE_MaxNoOfOrderedIndexes MaxNoOfAttributes= CHOOSE_MaxNoOfAttributes TimeBetweenGlobalCheckpoints= 500 -NoOfFragmentLogFiles= 3 +NoOfFragmentLogFiles= 8 +FragmentLogFileSize= 6M DiskPageBufferMemory= CHOOSE_DiskPageBufferMemory # diff --git a/mysql-test/ndb/ndb_config_2_node.ini b/mysql-test/ndb/ndb_config_2_node.ini index 42769d3f4a4..6bcb148d471 100644 --- a/mysql-test/ndb/ndb_config_2_node.ini +++ b/mysql-test/ndb/ndb_config_2_node.ini @@ -10,8 +10,10 @@ DataDir= CHOOSE_FILESYSTEM MaxNoOfOrderedIndexes= CHOOSE_MaxNoOfOrderedIndexes MaxNoOfAttributes= CHOOSE_MaxNoOfAttributes TimeBetweenGlobalCheckpoints= 500 -NoOfFragmentLogFiles= 3 +NoOfFragmentLogFiles= 4 +FragmentLogFileSize=12M DiskPageBufferMemory= CHOOSE_DiskPageBufferMemory +ODirect= 1 # the following parametes just function as a small regression # test that the parameter exists InitialNoOfOpenFiles= 27 diff --git a/mysql-test/r/backup.result b/mysql-test/r/backup.result index be1a3efc5c6..154b577e61f 100644 --- a/mysql-test/r/backup.result +++ b/mysql-test/r/backup.result @@ -4,28 +4,24 @@ create table t4(n int); backup table t4 to '../bogus'; Table Op Msg_type Msg_text test.t4 backup error Failed copying .frm file (errno: X) +test.t4 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead +test.t4 backup Error Can't create/write to file 'MYSQLTEST_VARDIR/bogus/t4.frm' (Errcode: X) test.t4 backup status Operation failed -Warnings: -Warning 1543 The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead -Error 1 Can't create/write to file 'MYSQLTEST_VARDIR/bogus/t4.frm' (Errcode: X) backup table t4 to '../tmp'; Table Op Msg_type Msg_text +test.t4 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t4 backup status OK -Warnings: -Warning 1543 The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead backup table t4 to '../tmp'; Table Op Msg_type Msg_text test.t4 backup error Failed copying .frm file (errno: X) +test.t4 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead +test.t4 backup Error Can't create/write to file 'MYSQLTEST_VARDIR/tmp/t4.frm' (Errcode: X) test.t4 backup status Operation failed -Warnings: -Warning 1543 The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead -Error 1 Can't create/write to file 'MYSQLTEST_VARDIR/tmp/t4.frm' (Errcode: X) drop table t4; restore table t4 from '../tmp'; Table Op Msg_type Msg_text +test.t4 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t4 restore status OK -Warnings: -Warning 1543 The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead select count(*) from t4; count(*) 0 @@ -33,9 +29,8 @@ create table t1(n int); insert into t1 values (23),(45),(67); backup table t1 to '../tmp'; Table Op Msg_type Msg_text +test.t1 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 backup status OK -Warnings: -Warning 1543 The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead drop table t1; restore table t1 from '../bogus'; Table Op Msg_type Msg_text @@ -45,9 +40,8 @@ Warning 1543 The syntax 'RESTORE TABLE' is deprecated and will be removed in MyS Error 29 File 'MYSQLTEST_VARDIR/bogus/t1.frm' not found (Errcode: X) restore table t1 from '../tmp'; Table Op Msg_type Msg_text +test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK -Warnings: -Warning 1543 The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead select n from t1; n 23 @@ -59,18 +53,16 @@ insert into t2 values (123),(145),(167); insert into t3 values (223),(245),(267); backup table t2,t3 to '../tmp'; Table Op Msg_type Msg_text +test.t2 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t2 backup status OK test.t3 backup status OK -Warnings: -Warning 1543 The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead drop table t1,t2,t3; restore table t1,t2,t3 from '../tmp'; Table Op Msg_type Msg_text +test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK test.t2 restore status OK test.t3 restore status OK -Warnings: -Warning 1543 The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead select n from t1; n 23 @@ -89,32 +81,28 @@ k drop table t1,t2,t3,t4; restore table t1 from '../tmp'; Table Op Msg_type Msg_text +test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK -Warnings: -Warning 1543 The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead rename table t1 to t5; lock tables t5 write; backup table t5 to '../tmp'; unlock tables; Table Op Msg_type Msg_text +test.t5 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t5 backup status OK -Warnings: -Warning 1543 The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead drop table t5; DROP TABLE IF EXISTS `t+1`; CREATE TABLE `t+1` (c1 INT); INSERT INTO `t+1` VALUES (1), (2), (3); BACKUP TABLE `t+1` TO '../tmp'; Table Op Msg_type Msg_text +test.t+1 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t+1 backup status OK -Warnings: -Warning 1543 The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead DROP TABLE `t+1`; RESTORE TABLE `t+1` FROM '../tmp'; Table Op Msg_type Msg_text +test.t+1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t+1 restore status OK -Warnings: -Warning 1543 The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead SELECT * FROM `t+1`; c1 1 diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index ede5d9a32fd..34dc1cb5b2e 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5203,6 +5203,27 @@ select * from bug15205; val drop table bug15205; drop table bug15205_2; +set names latin1; +create table t1 ( +c varchar(1), +name varchar(64) +) character set latin1 engine=csv; +insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE'); +insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE'); +insert into t1 values (0xEE,'LATIN SMALL LETTER I WITH CIRCUMFLEX'); +insert into t1 values (0xFE,'LATIN SMALL LETTER THORN'); +insert into t1 values (0xF7,'DIVISION SIGN'); +insert into t1 values (0xFF,'LATIN SMALL LETTER Y WITH DIAERESIS'); +select hex(c), c, name from t1 order by 1; +hex(c) c name +C0 À LATIN CAPITAL LETTER A WITH GRAVE +E0 à LATIN SMALL LETTER A WITH GRAVE +EE î LATIN SMALL LETTER I WITH CIRCUMFLEX +F7 ÷ DIVISION SIGN +FE þ LATIN SMALL LETTER THORN +FF ÿ LATIN SMALL LETTER Y WITH DIAERESIS +drop table t1; +End of 5.0 tests create table bug22080_1 (id int,string varchar(64)) Engine=CSV; create table bug22080_2 (id int,string varchar(64)) Engine=CSV; create table bug22080_3 (id int,string varchar(64)) Engine=CSV; diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result new file mode 100644 index 00000000000..77599ba303b --- /dev/null +++ b/mysql-test/r/error_simulation.result @@ -0,0 +1,19 @@ +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 ( +a varchar(32) character set utf8 collate utf8_bin NOT NULL, +b varchar(32) character set utf8 collate utf8_bin NOT NULL ) +ENGINE=MyISAM DEFAULT CHARSET=utf8; +INSERT INTO t1 VALUES +('AAAAAAAAAA','AAAAAAAAAA'), ('AAAAAAAAAB','AAAAAAAAAB '), +('AAAAAAAAAB','AAAAAAAAAB'), ('AAAAAAAAAC','AAAAAAAAAC'), +('AAAAAAAAAD','AAAAAAAAAD'), ('AAAAAAAAAE','AAAAAAAAAE'), +('AAAAAAAAAF','AAAAAAAAAF'), ('AAAAAAAAAG','AAAAAAAAAG'), +('AAAAAAAAAH','AAAAAAAAAH'), ('AAAAAAAAAI','AAAAAAAAAI'), +('AAAAAAAAAJ','AAAAAAAAAJ'), ('AAAAAAAAAK','AAAAAAAAAK'); +set tmp_table_size=1024; +SELECT MAX(a) FROM t1 GROUP BY a,b; +ERROR 23000: Can't write; duplicate key in table 'tmp_table' +set tmp_table_size=default; +DROP TABLE t1; diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index 7eb7fd16edb..dcbffd6f7c8 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -55,3 +55,19 @@ flush tables with read lock; insert into t2 values(1); unlock tables; drop table t1, t2; +End of 5.0 tests +set @old_general_log= @@general_log; +set @old_read_only= @@read_only; +set global general_log= on; +flush tables with read lock; +flush logs; +unlock tables; +set global read_only=1; +flush logs; +unlock tables; +flush tables with read lock; +flush logs; +unlock tables; +set global general_log= @old_general_log; +set global read_only= @old_read_only; +End of 5.1 tests diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index b50eb40182d..4896210c19d 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -865,6 +865,27 @@ SELECT Overlaps(@horiz1, @point2) FROM DUAL; Overlaps(@horiz1, @point2) 0 DROP TABLE t1; +create table t1(f1 geometry, f2 point, f3 linestring); +select f1 from t1 union select f1 from t1; +f1 +insert into t1 (f2,f3) values (GeomFromText('POINT(1 1)'), +GeomFromText('LINESTRING(0 0,1 1,2 2)')); +select AsText(f2),AsText(f3) from t1; +AsText(f2) AsText(f3) +POINT(1 1) LINESTRING(0 0,1 1,2 2) +select AsText(a) from (select f2 as a from t1 union select f3 from t1) t; +AsText(a) +POINT(1 1) +LINESTRING(0 0,1 1,2 2) +create table t2 as select f2 as a from t1 union select f3 from t1; +desc t2; +Field Type Null Key Default Extra +a point YES NULL +select AsText(a) from t2; +AsText(a) +POINT(1 1) +LINESTRING(0 0,1 1,2 2) +drop table t1, t2; End of 5.0 tests create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); create view v1 as select * from t1; diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index 8f3c8605cdd..21bad4fddcb 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -404,4 +404,22 @@ use test; drop database mysqltest_1; drop database mysqltest_2; drop user mysqltest_u1@localhost; +grant all on `mysqltest\_%`.* to mysqltest_1@localhost with grant option; +grant usage on *.* to mysqltest_2@localhost; +create database mysqltest_1; +use mysqltest_1; +create table t1 (f1 int); +grant create on `mysqltest\_1`.* to mysqltest_2@localhost; +grant select on mysqltest_1.t1 to mysqltest_2@localhost; +create database mysqltest_3; +ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest_3' +use mysqltest_1; +create table t2(f1 int); +select * from t1; +f1 +drop database mysqltest_1; +revoke all privileges, grant option from mysqltest_1@localhost; +revoke all privileges, grant option from mysqltest_2@localhost; +drop user mysqltest_1@localhost; +drop user mysqltest_2@localhost; End of 5.0 tests diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index e3cd90b9b3d..ebe59331357 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1055,47 +1055,47 @@ INSERT INTO t1 SELECT a + 8,b FROM t1; INSERT INTO t1 SELECT a + 16,b FROM t1; INSERT INTO t1 SELECT a + 32,b FROM t1; INSERT INTO t1 SELECT a + 64,b FROM t1; -INSERT INTO t1 SELECT a + 128,b FROM t1; +INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT a FROM t1 WHERE a < 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 1 Using where; Using index EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +1 SIMPLE t1 ALL NULL NULL NULL NULL 144 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +1 SIMPLE t1 ALL NULL NULL NULL NULL 144 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 256 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 256 Using index; Using filesort +1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) IGNORE INDEX FOR GROUP BY (i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 256 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +1 SIMPLE t1 index NULL i2 9 NULL 144 Using index EXPLAIN SELECT a FROM t1 FORCE INDEX (i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +1 SIMPLE t1 index NULL i2 9 NULL 144 Using index EXPLAIN SELECT a FROM t1 USE INDEX (); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +1 SIMPLE t1 ALL NULL NULL NULL NULL 144 EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +1 SIMPLE t1 ALL NULL NULL NULL NULL 144 EXPLAIN SELECT a FROM t1 FORCE INDEX (PRIMARY) IGNORE INDEX FOR GROUP BY (i2) @@ -1104,7 +1104,7 @@ USE INDEX (i2); ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX (); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +1 SIMPLE t1 index NULL i2 9 NULL 144 Using index EXPLAIN SELECT a FROM t1 FORCE INDEX (); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 EXPLAIN SELECT a FROM t1 IGNORE INDEX (); @@ -1112,34 +1112,34 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) USE INDEX FOR GROUP BY (i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +1 SIMPLE t1 index NULL i2 9 NULL 144 Using index EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) FORCE INDEX FOR GROUP BY (i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL i2 4 NULL 257 Using index for group-by +1 SIMPLE t1 range NULL i2 4 NULL 145 Using index for group-by EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +1 SIMPLE t1 ALL NULL NULL NULL NULL 144 EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX (); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +1 SIMPLE t1 ALL NULL NULL NULL NULL 144 EXPLAIN SELECT a FROM t1 USE INDEX FOR GROUP BY (i2) USE INDEX FOR ORDER BY (i2) USE INDEX FOR JOIN (i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +1 SIMPLE t1 index NULL i2 9 NULL 144 Using index EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) USE INDEX FOR JOIN (i2) USE INDEX FOR JOIN (i2,i2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +1 SIMPLE t1 index NULL i2 9 NULL 144 Using index EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL PRIMARY 4 NULL 256 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 256 Using where +1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; @@ -1152,7 +1152,7 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 256 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where SHOW VARIABLES LIKE 'old'; Variable_name Value old OFF diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 745ec2e2248..fc111430a4e 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1397,10 +1397,21 @@ TABLE_PRIVILEGES information_schema.TABLE_PRIVILEGES 1 TRIGGERS information_schema.TRIGGERS 1 USER_PRIVILEGES information_schema.USER_PRIVILEGES 1 VIEWS information_schema.VIEWS 1 +create table t1(f1 int); +create view v1 as select f1+1 as a from t1; +create table t2 (f1 int, f2 int); +create view v2 as select f1+1 as a, f2 as b from t2; +select table_name, is_updatable from information_schema.views; +table_name is_updatable +v1 NO +v2 YES +delete from v1; +drop view v1,v2; +drop table t1,t2; End of 5.0 tests. select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS -MyISAM ENABLED Default engine as of MySQL 3.23 with great performance NO NO NO +MyISAM DEFAULT Default engine as of MySQL 3.23 with great performance NO NO NO grant select on *.* to user3148@localhost; select user,db from information_schema.processlist; user db diff --git a/mysql-test/r/information_schema_part.result b/mysql-test/r/information_schema_part.result index df3abdbee0a..8455c8e014e 100644 --- a/mysql-test/r/information_schema_part.result +++ b/mysql-test/r/information_schema_part.result @@ -7,9 +7,9 @@ partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); select * from information_schema.partitions where table_schema="test" and table_name="t1"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 x1 NULL 1 NULL LIST NULL b*a NULL 1 0 0 0 # 1024 0 # # NULL NULL default default ts1 -NULL test t1 x2 NULL 2 NULL LIST NULL b*a NULL 3,11,5,7 0 0 0 # 1024 0 # # NULL NULL default default ts2 -NULL test t1 x3 NULL 3 NULL LIST NULL b*a NULL 16,8,24,27 0 0 0 # 1024 0 # # NULL NULL default default ts3 +NULL test t1 x1 NULL 1 NULL LIST NULL b*a NULL 1 0 0 0 # 1024 0 # # NULL NULL default ts1 +NULL test t1 x2 NULL 2 NULL LIST NULL b*a NULL 3,11,5,7 0 0 0 # 1024 0 # # NULL NULL default ts2 +NULL test t1 x3 NULL 3 NULL LIST NULL b*a NULL 16,8,24,27 0 0 0 # 1024 0 # # NULL NULL default ts3 create table t2 (a int not null,b int not null,c int not null, primary key(a,b)) partition by range (a) partitions 3 @@ -19,27 +19,27 @@ partition x3 values less than maxvalue tablespace ts3); select * from information_schema.partitions where table_schema="test" and table_name="t2"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t2 x1 NULL 1 NULL RANGE NULL a NULL 5 0 0 0 # 1024 0 # # NULL NULL default default ts1 -NULL test t2 x2 NULL 2 NULL RANGE NULL a NULL 10 0 0 0 # 1024 0 # # NULL NULL default default ts2 -NULL test t2 x3 NULL 3 NULL RANGE NULL a NULL MAXVALUE 0 0 0 # 1024 0 # # NULL NULL default default ts3 +NULL test t2 x1 NULL 1 NULL RANGE NULL a NULL 5 0 0 0 # 1024 0 # # NULL NULL default ts1 +NULL test t2 x2 NULL 2 NULL RANGE NULL a NULL 10 0 0 0 # 1024 0 # # NULL NULL default ts2 +NULL test t2 x3 NULL 3 NULL RANGE NULL a NULL MAXVALUE 0 0 0 # 1024 0 # # NULL NULL default ts3 create table t3 (f1 date) partition by hash(month(f1)) partitions 3; select * from information_schema.partitions where table_schema="test" and table_name="t3"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t3 p0 NULL 1 NULL HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL -NULL test t3 p1 NULL 2 NULL HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL -NULL test t3 p2 NULL 3 NULL HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL +NULL test t3 p0 NULL 1 NULL HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL +NULL test t3 p1 NULL 2 NULL HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL +NULL test t3 p2 NULL 3 NULL HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL create table t4 (f1 date, f2 int) partition by key(f1,f2) partitions 3; select * from information_schema.partitions where table_schema="test" and table_name="t4"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t4 p0 NULL 1 NULL KEY NULL f1,f2 NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL -NULL test t4 p1 NULL 2 NULL KEY NULL f1,f2 NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL -NULL test t4 p2 NULL 3 NULL KEY NULL f1,f2 NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL +NULL test t4 p0 NULL 1 NULL KEY NULL f1,f2 NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL +NULL test t4 p1 NULL 2 NULL KEY NULL f1,f2 NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL +NULL test t4 p2 NULL 3 NULL KEY NULL f1,f2 NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL drop table t1,t2,t3,t4; create table t1 (a int not null,b int not null,c int not null,primary key (a,b)) partition by range (a) @@ -63,14 +63,14 @@ subpartition x22 tablespace t2) ); select * from information_schema.partitions where table_schema="test"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 x1 x11 1 1 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL default default t1 -NULL test t1 x1 x12 1 2 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL default default t2 -NULL test t1 x2 x21 2 1 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL default default t1 -NULL test t1 x2 x22 2 2 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL default default t2 -NULL test t2 x1 x11 1 1 RANGE KEY a a 1 0 0 0 # 1024 0 # # NULL NULL default default t1 -NULL test t2 x1 x12 1 2 RANGE KEY a a 1 0 0 0 # 1024 0 # # NULL NULL default default t2 -NULL test t2 x2 x21 2 1 RANGE KEY a a 5 0 0 0 # 1024 0 # # NULL NULL default default t1 -NULL test t2 x2 x22 2 2 RANGE KEY a a 5 0 0 0 # 1024 0 # # NULL NULL default default t2 +NULL test t1 x1 x11 1 1 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL default t1 +NULL test t1 x1 x12 1 2 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL default t2 +NULL test t1 x2 x21 2 1 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL default t1 +NULL test t1 x2 x22 2 2 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL default t2 +NULL test t2 x1 x11 1 1 RANGE KEY a a 1 0 0 0 # 1024 0 # # NULL NULL default t1 +NULL test t2 x1 x12 1 2 RANGE KEY a a 1 0 0 0 # 1024 0 # # NULL NULL default t2 +NULL test t2 x2 x21 2 1 RANGE KEY a a 5 0 0 0 # 1024 0 # # NULL NULL default t1 +NULL test t2 x2 x22 2 2 RANGE KEY a a 5 0 0 0 # 1024 0 # # NULL NULL default t2 drop table t1,t2; create table t1 ( a int not null, @@ -88,10 +88,10 @@ subpartition x22 tablespace t2 nodegroup 1) ); select * from information_schema.partitions where table_schema="test"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 x1 x11 1 1 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL default 0 t1 -NULL test t1 x1 x12 1 2 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL default 1 t2 -NULL test t1 x2 x21 2 1 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL default 0 t1 -NULL test t1 x2 x22 2 2 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL default 1 t2 +NULL test t1 x1 x11 1 1 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL 0 t1 +NULL test t1 x1 x12 1 2 RANGE HASH a a+b 1 0 0 0 # 1024 0 # # NULL NULL 1 t2 +NULL test t1 x2 x21 2 1 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL 0 t1 +NULL test t1 x2 x22 2 2 RANGE HASH a a+b 5 0 0 0 # 1024 0 # # NULL NULL 1 t2 show tables; Tables_in_test t1 @@ -107,9 +107,9 @@ partitions 3; select * from information_schema.partitions where table_schema="test" and table_name="t1"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 p0 NULL 1 NULL LINEAR HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL -NULL test t1 p1 NULL 2 NULL LINEAR HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL -NULL test t1 p2 NULL 3 NULL LINEAR HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default default NULL +NULL test t1 p0 NULL 1 NULL LINEAR HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL +NULL test t1 p1 NULL 2 NULL LINEAR HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL +NULL test t1 p2 NULL 3 NULL LINEAR HASH NULL month(f1) NULL NULL 0 0 0 # 1024 0 # # NULL NULL default NULL drop table t1; create table t1 (a int) PARTITION BY RANGE (a) diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 7aa4aec8d28..afecf30b94f 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -629,6 +629,42 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='123' drop table t1; +CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8; +INSERT INTO t1 VALUES ('uk'),('bg'); +SELECT * FROM t1 WHERE a = 'uk'; +a +uk +DELETE FROM t1 WHERE a = 'uk'; +SELECT * FROM t1 WHERE a = 'uk'; +a +UPDATE t1 SET a = 'us' WHERE a = 'uk'; +SELECT * FROM t1 WHERE a = 'uk'; +a +CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB; +INSERT INTO t2 VALUES ('uk'),('bg'); +SELECT * FROM t2 WHERE a = 'uk'; +a +uk +DELETE FROM t2 WHERE a = 'uk'; +SELECT * FROM t2 WHERE a = 'uk'; +a +INSERT INTO t2 VALUES ('uk'); +UPDATE t2 SET a = 'us' WHERE a = 'uk'; +SELECT * FROM t2 WHERE a = 'uk'; +a +CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM; +INSERT INTO t3 VALUES ('uk'),('bg'); +SELECT * FROM t3 WHERE a = 'uk'; +a +uk +DELETE FROM t3 WHERE a = 'uk'; +SELECT * FROM t3 WHERE a = 'uk'; +a +INSERT INTO t3 VALUES ('uk'); +UPDATE t3 SET a = 'us' WHERE a = 'uk'; +SELECT * FROM t3 WHERE a = 'uk'; +a +DROP TABLE t1,t2,t3; End of 5.0 tests CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 020c587959f..704cf444681 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -393,3 +393,17 @@ id c1 cnt 1 0 3 2 2 1 DROP TABLE t1; +create table t1(f1 int primary key, +f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP); +insert into t1(f1) values(1); +select @stamp1:=f2 from t1; +@stamp1:=f2 +# +insert into t1(f1) values(1) on duplicate key update f1=1; +select @stamp2:=f2 from t1; +@stamp2:=f2 +# +select if( @stamp1 = @stamp2, "correct", "wrong"); +if( @stamp1 = @stamp2, "correct", "wrong") +correct +drop table t1; diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index a1bf3d0e128..08d8059f61b 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -191,10 +191,8 @@ cache index t1 in unknown_key_cache; ERROR HY000: Unknown key cache 'unknown_key_cache' cache index t1 key (unknown_key) in keycache1; Table Op Msg_type Msg_text -test.t1 assign_to_keycache error Key 'unknown_key' doesn't exist in table 't1' +test.t1 assign_to_keycache Error Key 'unknown_key' doesn't exist in table 't1' test.t1 assign_to_keycache status Operation failed -Warnings: -Error 1176 Key 'unknown_key' doesn't exist in table 't1' select @@keycache2.key_buffer_size; @@keycache2.key_buffer_size 4194304 @@ -341,3 +339,30 @@ Warning 1438 Cannot drop default keycache select @@global.key_buffer_size; @@global.key_buffer_size 2097152 +SET @bug28478_key_cache_block_size= @@global.key_cache_block_size; +SET GLOBAL key_cache_block_size= 1536; +CREATE TABLE t1 ( +id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, +c1 CHAR(150), +c2 CHAR(150), +c3 CHAR(150), +KEY(c1, c2, c3) +) ENGINE= MyISAM; +INSERT INTO t1 (c1, c2, c3) VALUES +('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'), +('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'), +('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'), +('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'), +('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'), +('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z'); +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SHOW VARIABLES LIKE 'key_cache_block_size'; +Variable_name Value +key_cache_block_size 1536 +SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size; +DROP TABLE t1; diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result index 1a2099b7a94..e2000a9ec91 100644 --- a/mysql-test/r/lock.result +++ b/mysql-test/r/lock.result @@ -40,7 +40,8 @@ test.t1 check status OK lock tables t1 write; check table t2; Table Op Msg_type Msg_text -test.t2 check error Table 't2' was not locked with LOCK TABLES +test.t2 check Error Table 't2' was not locked with LOCK TABLES +test.t2 check error Corrupt insert into t1 select index1,nr from t1; ERROR HY000: Table 't1' was not locked with LOCK TABLES unlock tables; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 17a3bc0877e..bb4fc654b38 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -847,4 +847,36 @@ insert into t1 values (1); ERROR HY000: Table 't1' is read only drop table t2; drop table t1; +CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2); +SELECT * FROM tm1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +CHECK TABLE tm1; +Table Op Msg_type Msg_text +test.tm1 check Error Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist +test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist +test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +test.tm1 check error Corrupt +CREATE TABLE t1(a INT); +SELECT * FROM tm1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +CHECK TABLE tm1; +Table Op Msg_type Msg_text +test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist +test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +test.tm1 check error Corrupt +CREATE TABLE t2(a BLOB); +SELECT * FROM tm1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +CHECK TABLE tm1; +Table Op Msg_type Msg_text +test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist +test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +test.tm1 check error Corrupt +ALTER TABLE t2 MODIFY a INT; +SELECT * FROM tm1; +a +CHECK TABLE tm1; +Table Op Msg_type Msg_text +test.tm1 check status OK +DROP TABLE tm1, t1, t2; End of 5.0 tests diff --git a/mysql-test/r/ndb_backup_print.result b/mysql-test/r/ndb_backup_print.result new file mode 100644 index 00000000000..fdd929802b2 --- /dev/null +++ b/mysql-test/r/ndb_backup_print.result @@ -0,0 +1,64 @@ +use test; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; +Connected to Management Server at: : +Waiting for completed, this may take several minutes +Node : Backup started from node +Node : Backup started from node completed + StartGCP: StopGCP: + #Records: #LogRecords: + Data: bytes Log: bytes +create table t1 +(pk int key +,a1 BIT(1), a2 BIT(5), a3 BIT(33), a4 BIT(63), a5 BIT(64) +,b1 TINYINT, b2 TINYINT UNSIGNED +,c1 SMALLINT, c2 SMALLINT UNSIGNED +,d1 INT, d2 INT UNSIGNED +,e1 BIGINT, e2 BIGINT UNSIGNED +,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY +,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY +,h1 BINARY(1), h2 BINARY(8), h3 BINARY(255) +,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000) +) engine ndb; +insert into t1 values +(1 +,0x1, 0x17, 0x789a, 0x789abcde, 0xfedc0001 +,127, 255 +,32767, 65535 +,2147483647, 4294967295 +,9223372036854775807, 18446744073709551615 +,'1','12345678901234567890123456789012','123456789' + ,'1','12345678901234567890123456789012','123456789' + ,0x12,0x123456789abcdef0, 0x012345 +,0x12,0x123456789abcdef0, 0x00123450 +); +insert into t1 values +(2 +,0, 0, 0, 0, 0 +,-128, 0 +,-32768, 0 +,-2147483648, 0 +,-9223372036854775808, 0 +,'','','' + ,'','','' + ,0x0,0x0,0x0 +,0x0,0x0,0x0 +); +insert into t1 values +(3 +,NULL,NULL,NULL,NULL,NULL +,NULL,NULL +,NULL,NULL +,NULL,NULL +,NULL,NULL +,NULL,NULL,NULL +,NULL,NULL,NULL +,NULL,NULL,NULL +,NULL,NULL,NULL +); +Connected to Management Server at: : +Waiting for completed, this may take several minutes +Node : Backup started from node +Node : Backup started from node completed + StartGCP: StopGCP: + #Records: #LogRecords: + Data: bytes Log: bytes diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result index 16eb36bac10..4eddaeb1227 100644 --- a/mysql-test/r/ndb_basic.result +++ b/mysql-test/r/ndb_basic.result @@ -6,6 +6,34 @@ attr1 INT NOT NULL, attr2 INT, attr3 VARCHAR(10) ) ENGINE=ndbcluster; +drop table t1; +SHOW GLOBAL STATUS LIKE 'ndb%'; +Variable_name Value +Ndb_cluster_node_id # +Ndb_config_from_host # +Ndb_config_from_port # +Ndb_number_of_data_nodes # +SHOW GLOBAL VARIABLES LIKE 'ndb%'; +Variable_name Value +ndb_autoincrement_prefetch_sz # +ndb_cache_check_time # +ndb_connectstring # +ndb_extra_logging # +ndb_force_send # +ndb_index_stat_cache_entries # +ndb_index_stat_enable # +ndb_index_stat_update_freq # +ndb_report_thresh_binlog_epoch_slip # +ndb_report_thresh_binlog_mem_usage # +ndb_use_copying_alter_table # +ndb_use_exact_count # +ndb_use_transactions # +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL, +attr2 INT, +attr3 VARCHAR(10) +) ENGINE=ndbcluster; SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 pk1 A 0 NULL NULL BTREE @@ -770,35 +798,6 @@ c abc ab d ab ab e abc abc DROP TABLE t1; -End of 5.0 tests -CREATE TABLE t1 (a VARCHAR(255) NOT NULL, -CONSTRAINT pk_a PRIMARY KEY (a))engine=ndb; -CREATE TABLE t2(a VARCHAR(255) NOT NULL, -b VARCHAR(255) NOT NULL, -c VARCHAR(255) NOT NULL, -CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), -CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb; -drop table t1, t2; -create table t1 (a int not null primary key, b int) engine=ndb; -insert into t1 values(1,1),(2,2),(3,3); -create table t2 like t1; -insert into t2 select * from t1; -select * from t1 order by a; -a b -1 1 -2 2 -3 3 -select * from t2 order by a; -a b -1 1 -2 2 -3 3 -drop table t1, t2; -create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; -create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; -create table t2 like t1; -rename table t1 to t10, t2 to t20; -drop table t10,t20; create table t1 (a int not null primary key, b int not null) engine=ndb; create table t2 (a int not null primary key, b int not null) engine=ndb; insert into t1 values (1,10), (2,20), (3,30); @@ -867,7 +866,36 @@ select * from t1 order by a; a b 1 10 2 10 -3 1 +3 30 4 1 drop table t1,t2; +End of 5.0 tests +CREATE TABLE t1 (a VARCHAR(255) NOT NULL, +CONSTRAINT pk_a PRIMARY KEY (a))engine=ndb; +CREATE TABLE t2(a VARCHAR(255) NOT NULL, +b VARCHAR(255) NOT NULL, +c VARCHAR(255) NOT NULL, +CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), +CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb; +drop table t1, t2; +create table t1 (a int not null primary key, b int) engine=ndb; +insert into t1 values(1,1),(2,2),(3,3); +create table t2 like t1; +insert into t2 select * from t1; +select * from t1 order by a; +a b +1 1 +2 2 +3 3 +select * from t2 order by a; +a b +1 1 +2 2 +3 3 +drop table t1, t2; +create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +create table t2 like t1; +rename table t1 to t10, t2 to t20; +drop table t10,t20; End of 5.1 tests diff --git a/mysql-test/r/ndb_dd_alter.result b/mysql-test/r/ndb_dd_alter.result index e4bd96ab384..94426546115 100644 --- a/mysql-test/r/ndb_dd_alter.result +++ b/mysql-test/r/ndb_dd_alter.result @@ -314,11 +314,11 @@ a1 20 SELECT * FROM information_schema.partitions WHERE table_name= 't1' AND partition_name = 'p0'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 p0 NULL 1 NULL KEY NULL NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default ts +NULL test t1 p0 NULL 1 NULL KEY NULL NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default ts ALTER TABLE test.t1 ADD a2 FLOAT, ADD a3 DOUBLE; SELECT * FROM information_schema.partitions WHERE table_name= 't1' AND partition_name = 'p0'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 p0 NULL 1 NULL KEY NULL NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default ts +NULL test t1 p0 NULL 1 NULL KEY NULL NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default ts SELECT * FROM test.t1 ORDER BY a1; a1 a2 a3 1 2.2345 20000001 diff --git a/mysql-test/r/ndb_dd_backuprestore.result b/mysql-test/r/ndb_dd_backuprestore.result index 1dd609b932d..c82fe560121 100644 --- a/mysql-test/r/ndb_dd_backuprestore.result +++ b/mysql-test/r/ndb_dd_backuprestore.result @@ -223,31 +223,31 @@ t6 CREATE TABLE `t6` ( ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (pk1) (PARTITION x1 VALUES LESS THAN (333) ENGINE = ndbcluster, PARTITION x2 VALUES LESS THAN (720) ENGINE = ndbcluster) */ SELECT * FROM information_schema.partitions WHERE table_name= 't1'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 -NULL test t1 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 -NULL test t1 p2 NULL 3 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 -NULL test t1 p3 NULL 4 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 +NULL test t1 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 +NULL test t1 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 +NULL test t1 p2 NULL 3 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 +NULL test t1 p3 NULL 4 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 SELECT * FROM information_schema.partitions WHERE table_name= 't2'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t2 p0 NULL 1 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 -NULL test t2 p1 NULL 2 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 +NULL test t2 p0 NULL 1 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 +NULL test t2 p1 NULL 2 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 SELECT * FROM information_schema.partitions WHERE table_name= 't3'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t3 x1 NULL 1 NULL RANGE NULL c3 NULL 105 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 -NULL test t3 x2 NULL 2 NULL RANGE NULL c3 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 -NULL test t3 x3 NULL 3 NULL RANGE NULL c3 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 +NULL test t3 x1 NULL 1 NULL RANGE NULL c3 NULL 105 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 +NULL test t3 x2 NULL 2 NULL RANGE NULL c3 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 +NULL test t3 x3 NULL 3 NULL RANGE NULL c3 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 SELECT * FROM information_schema.partitions WHERE table_name= 't4'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t4 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL -NULL test t4 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL +NULL test t4 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL +NULL test t4 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL SELECT * FROM information_schema.partitions WHERE table_name= 't5'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t5 p0 NULL 1 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL -NULL test t5 p1 NULL 2 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL +NULL test t5 p0 NULL 1 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL +NULL test t5 p1 NULL 2 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL SELECT * FROM information_schema.partitions WHERE table_name= 't6'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t6 x1 NULL 1 NULL RANGE NULL pk1 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL -NULL test t6 x2 NULL 2 NULL RANGE NULL pk1 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL +NULL test t6 x1 NULL 1 NULL RANGE NULL pk1 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL +NULL test t6 x2 NULL 2 NULL RANGE NULL pk1 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL SELECT COUNT(*) FROM test.t1; COUNT(*) 250 @@ -389,31 +389,31 @@ t6 CREATE TABLE `t6` ( ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (pk1) (PARTITION x1 VALUES LESS THAN (333) ENGINE = ndbcluster, PARTITION x2 VALUES LESS THAN (720) ENGINE = ndbcluster) */ SELECT * FROM information_schema.partitions WHERE table_name= 't1'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 -NULL test t1 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 -NULL test t1 p2 NULL 3 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 -NULL test t1 p3 NULL 4 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space1 +NULL test t1 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 +NULL test t1 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 +NULL test t1 p2 NULL 3 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 +NULL test t1 p3 NULL 4 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space1 SELECT * FROM information_schema.partitions WHERE table_name= 't2'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t2 p0 NULL 1 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 -NULL test t2 p1 NULL 2 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 +NULL test t2 p0 NULL 1 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 +NULL test t2 p1 NULL 2 NULL KEY NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 SELECT * FROM information_schema.partitions WHERE table_name= 't3'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t3 x1 NULL 1 NULL RANGE NULL c3 NULL 105 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 -NULL test t3 x2 NULL 2 NULL RANGE NULL c3 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 -NULL test t3 x3 NULL 3 NULL RANGE NULL c3 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default default table_space2 +NULL test t3 x1 NULL 1 NULL RANGE NULL c3 NULL 105 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 +NULL test t3 x2 NULL 2 NULL RANGE NULL c3 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 +NULL test t3 x3 NULL 3 NULL RANGE NULL c3 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default table_space2 SELECT * FROM information_schema.partitions WHERE table_name= 't4'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t4 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL -NULL test t4 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL +NULL test t4 p0 NULL 1 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL +NULL test t4 p1 NULL 2 NULL HASH NULL c3 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL SELECT * FROM information_schema.partitions WHERE table_name= 't5'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t5 p0 NULL 1 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL -NULL test t5 p1 NULL 2 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL +NULL test t5 p0 NULL 1 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL +NULL test t5 p1 NULL 2 NULL KEY NULL pk1 NULL NULL 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL SELECT * FROM information_schema.partitions WHERE table_name= 't6'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t6 x1 NULL 1 NULL RANGE NULL pk1 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL -NULL test t6 x2 NULL 2 NULL RANGE NULL pk1 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default default NULL +NULL test t6 x1 NULL 1 NULL RANGE NULL pk1 NULL 333 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL +NULL test t6 x2 NULL 2 NULL RANGE NULL pk1 NULL 720 0 0 0 NULL 0 0 NULL NULL NULL NULL default NULL SELECT COUNT(*) FROM test.t1; COUNT(*) 250 diff --git a/mysql-test/r/ndb_insert.result b/mysql-test/r/ndb_insert.result index 7551dc71823..51b346bbf7b 100644 --- a/mysql-test/r/ndb_insert.result +++ b/mysql-test/r/ndb_insert.result @@ -657,3 +657,172 @@ a b 2 NULL 3 NULL drop table t1; +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER; +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=MYISAM; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +pk b c +1 1 0 +11 2 1 +21 3 2 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +3 +TRUNCATE t1; +TRUNCATE t2; +SET @@session.auto_increment_offset=5; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); +SELECT * FROM t1 ORDER BY pk; +pk b c +5 1 0 +15 2 1 +25 3 2 +27 4 3 +35 5 4 +99 6 5 +105 7 6 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +7 +TRUNCATE t1; +TRUNCATE t2; +SET @@session.auto_increment_increment=2; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +pk b c +1 1 0 +3 2 1 +5 3 2 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +3 +DROP TABLE t1, t2; +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 7; +SET @@session.auto_increment_offset=1; +SET @@session.auto_increment_increment=1; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +pk b c +7 1 0 +8 2 1 +9 3 2 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +3 +DROP TABLE t1, t2; +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3; +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 3; +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +pk b c +5 1 0 +15 2 1 +25 3 2 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +3 +DROP TABLE t1, t2; +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 7; +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +pk b c +15 1 0 +25 2 1 +35 3 2 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +3 +DROP TABLE t1, t2; +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5; +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 5; +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +pk b c +5 1 0 +15 2 1 +25 3 2 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +3 +DROP TABLE t1, t2; +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100; +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +b INT NOT NULL, +c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 100; +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +pk b c +105 1 0 +115 2 1 +125 3 2 +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +COUNT(t1.pk) +3 +DROP TABLE t1, t2; diff --git a/mysql-test/r/ndb_partition_range.result b/mysql-test/r/ndb_partition_range.result index 8057ac59613..0c717ed55e9 100644 --- a/mysql-test/r/ndb_partition_range.result +++ b/mysql-test/r/ndb_partition_range.result @@ -17,9 +17,9 @@ INSERT into t1 values (10, 1, 1); INSERT into t1 values (15, 1, 1); select * from information_schema.partitions where table_name= 't1'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME -NULL test t1 x1 NULL 1 NULL RANGE NULL a NULL 5 0 0 0 # 0 0 # # NULL NULL default default NULL -NULL test t1 x2 NULL 2 NULL RANGE NULL a NULL 10 0 0 0 # 0 0 # # NULL NULL default default NULL -NULL test t1 x3 NULL 3 NULL RANGE NULL a NULL 20 0 0 0 # 0 0 # # NULL NULL default default NULL +NULL test t1 x1 NULL 1 NULL RANGE NULL a NULL 5 0 0 0 # 0 0 # # NULL NULL default NULL +NULL test t1 x2 NULL 2 NULL RANGE NULL a NULL 10 0 0 0 # 0 0 # # NULL NULL default NULL +NULL test t1 x3 NULL 3 NULL RANGE NULL a NULL 20 0 0 0 # 0 0 # # NULL NULL default NULL select * from t1 order by a; a b c 1 1 1 diff --git a/mysql-test/r/ndb_restore.result b/mysql-test/r/ndb_restore.result index 8ecffa437b0..d1c76192cef 100644 --- a/mysql-test/r/ndb_restore.result +++ b/mysql-test/r/ndb_restore.result @@ -18,7 +18,7 @@ CREATE TABLE `t2_c` ( PRIMARY KEY (`capgotod`), KEY `i quadaddsvr` (`gotod`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; -INSERT INTO `t2_c` VALUES (500,4,'','q3.net','addavp:MK_CASELECTOR=1','postorod rattoaa'),(2,1,'4','','addavp:MK_BRANDTAD=345','REDS Brandtad'),(3,2,'4','q3.net','execorder','fixedRatediPO REDS'),(1,1,'3','','addavp:MK_BRANDTAD=123','TEST Brandtad'),(6,5,'','told.q3.net','addavp:MK_BRANDTAD=123','Brandtad Toldzone'),(4,3,'3','q3.net','addavp:MK_POOLHINT=2','ratedi PO TEST'); +INSERT INTO `t2_c` VALUES (500,4,'','q3.net','addavp:MK_CASELECTOR=1','postorod rattoaa'),(2,1,'4','','addavp:MK_BRANDTAD=345','REDS Brandtad'),(3,2,'4','q3.net','execorder','fixedRatediPO REDS'),(1,1,'3','','addavp:MK_BRANDTAD=123','TEST Brandtad'),(6,5,'','told.q3.net','addavp:MK_BRANDTAD=123','Brandtad Toldzone'),(4,3,'3','q3.net','addavp:MK_POOLHINT=2','ratedi PO TEST'),(5,0,'',NULL,NULL,''); CREATE TABLE `t3_c` ( `CapGoaledatta` smallint(5) unsigned NOT NULL default '0', `capgotod` smallint(5) unsigned NOT NULL default '0', @@ -154,15 +154,15 @@ count(*) 5 select count(*) from t2; count(*) -6 +7 select count(*) from t2_c; count(*) -6 +7 select count(*) from (select * from t2 union select * from t2_c) a; count(*) -6 +7 select count(*) from t3; count(*) 4 @@ -286,15 +286,15 @@ count(*) 5 select count(*) from t2; count(*) -6 +7 select count(*) from t2_c; count(*) -6 +7 select count(*) from (select * from t2 union select * from t2_c) a; count(*) -6 +7 select count(*) from t3; count(*) 4 @@ -386,15 +386,15 @@ count(*) 5 select count(*) from t2; count(*) -6 +7 select count(*) from t2_c; count(*) -6 +7 select count(*) from (select * from t2 union select * from t2_c) a; count(*) -6 +7 select count(*) from t3; count(*) 4 diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index dddd82f3a8d..5d985d053fc 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1249,4 +1249,22 @@ show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Fixed 3 7 21 0 0 0 4 NULL NULL NULL latin1_swedish_ci NULL partitioned drop table t1; +CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a)) +PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32)); +INSERT INTO t1 VALUES (1, REPEAT('a', 10)); +INSERT INTO t1 SELECT a + 1, b FROM t1; +INSERT INTO t1 SELECT a + 2, b FROM t1; +INSERT INTO t1 SELECT a + 4, b FROM t1; +INSERT INTO t1 SELECT a + 8, b FROM t1; +ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64)); +ALTER TABLE t1 DROP PARTITION p1; +DROP TABLE t1; +USE mysql; +SET GLOBAL general_log = 0; +ALTER TABLE general_log ENGINE = MyISAM; +ALTER TABLE general_log PARTITION BY RANGE (TO_DAYS(event_time)) +(PARTITION p0 VALUES LESS THAN (733144), PARTITION p1 VALUES LESS THAN (3000000)); +ERROR HY000: Incorrect usage of PARTITION and log table +ALTER TABLE general_log ENGINE = CSV; +SET GLOBAL general_log = default; End of 5.1 tests diff --git a/mysql-test/r/preload.result b/mysql-test/r/preload.result index 145fd22ffb6..24a6e594a14 100644 --- a/mysql-test/r/preload.result +++ b/mysql-test/r/preload.result @@ -143,10 +143,9 @@ Key_read_requests 0 Key_reads 0 load index into cache t3, t2 key (primary,b) ; Table Op Msg_type Msg_text -test.t3 preload_keys error Table 'test.t3' doesn't exist +test.t3 preload_keys Error Table 'test.t3' doesn't exist +test.t3 preload_keys error Corrupt test.t2 preload_keys status OK -Warnings: -Error 1146 Table 'test.t3' doesn't exist show status like "key_read%"; Variable_name Value Key_read_requests 478 @@ -159,12 +158,10 @@ Key_read_requests 0 Key_reads 0 load index into cache t3 key (b), t2 key (c) ; Table Op Msg_type Msg_text -test.t3 preload_keys error Table 'test.t3' doesn't exist -test.t2 preload_keys error Key 'c' doesn't exist in table 't2' +test.t3 preload_keys Error Table 'test.t3' doesn't exist +test.t3 preload_keys error Corrupt +test.t2 preload_keys Error Key 'c' doesn't exist in table 't2' test.t2 preload_keys status Operation failed -Warnings: -Error 1146 Table 'test.t3' doesn't exist -Error 1176 Key 'c' doesn't exist in table 't2' show status like "key_read%"; Variable_name Value Key_read_requests 0 diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 76d3dbc1b72..84fcb8634d6 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1386,45 +1386,39 @@ prepare stmt from "repair table t1, t4, t3"; execute stmt; Table Op Msg_type Msg_text test.t1 repair status OK -test.t4 repair error Table 'test.t4' doesn't exist +test.t4 repair Error Table 'test.t4' doesn't exist +test.t4 repair error Corrupt test.t3 repair status OK -Warnings: -Error 1146 Table 'test.t4' doesn't exist execute stmt; Table Op Msg_type Msg_text test.t1 repair status OK -test.t4 repair error Table 'test.t4' doesn't exist +test.t4 repair Error Table 'test.t4' doesn't exist +test.t4 repair error Corrupt test.t3 repair status OK -Warnings: -Error 1146 Table 'test.t4' doesn't exist prepare stmt from "optimize table t1, t3, t4"; execute stmt; Table Op Msg_type Msg_text test.t1 optimize status OK test.t3 optimize status OK -test.t4 optimize error Table 'test.t4' doesn't exist -Warnings: -Error 1146 Table 'test.t4' doesn't exist +test.t4 optimize Error Table 'test.t4' doesn't exist +test.t4 optimize error Corrupt execute stmt; Table Op Msg_type Msg_text test.t1 optimize status Table is already up to date test.t3 optimize status Table is already up to date -test.t4 optimize error Table 'test.t4' doesn't exist -Warnings: -Error 1146 Table 'test.t4' doesn't exist +test.t4 optimize Error Table 'test.t4' doesn't exist +test.t4 optimize error Corrupt prepare stmt from "analyze table t4, t1"; execute stmt; Table Op Msg_type Msg_text -test.t4 analyze error Table 'test.t4' doesn't exist +test.t4 analyze Error Table 'test.t4' doesn't exist +test.t4 analyze error Corrupt test.t1 analyze status Table is already up to date -Warnings: -Error 1146 Table 'test.t4' doesn't exist execute stmt; Table Op Msg_type Msg_text -test.t4 analyze error Table 'test.t4' doesn't exist +test.t4 analyze Error Table 'test.t4' doesn't exist +test.t4 analyze error Corrupt test.t1 analyze status Table is already up to date -Warnings: -Error 1146 Table 'test.t4' doesn't exist deallocate prepare stmt; drop table t1, t2, t3; create database mysqltest_long_database_name_to_thrash_heap; diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index 80bebe35c3b..7fc09c43f4b 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -26,16 +26,14 @@ t1 1 st_key 1 st A NULL NULL NULL YES BTREE disabled drop table t1; repair table t1 use_frm; Table Op Msg_type Msg_text -test.t1 repair error Table 'test.t1' doesn't exist -Warnings: -Error 1146 Table 'test.t1' doesn't exist +test.t1 repair Error Table 'test.t1' doesn't exist +test.t1 repair error Corrupt create table t1 engine=myisam SELECT 1,"table 1"; flush tables; repair table t1; Table Op Msg_type Msg_text -test.t1 repair error Incorrect file format 't1' -Warnings: -Error 130 Incorrect file format 't1' +test.t1 repair Error Incorrect file format 't1' +test.t1 repair error Corrupt repair table t1 use_frm; Table Op Msg_type Msg_text test.t1 repair warning Number of rows changed from 0 to 1 diff --git a/mysql-test/r/rpl_events.result b/mysql-test/r/rpl_events.result index debfcce0072..0d7e7bb28a7 100644 --- a/mysql-test/r/rpl_events.result +++ b/mysql-test/r/rpl_events.result @@ -157,3 +157,9 @@ test slave_terminate SLAVESIDE_DISABLED 2 DROP EVENT test.slave_terminate; "in the master" DROP TABLE t1; +CREATE EVENT event1 ON SCHEDULE EVERY 1 YEAR +DO BEGIN +select * from t1; +END;| +ALTER EVENT event1 RENAME TO event2; +DROP EVENT event2; diff --git a/mysql-test/r/rpl_failed_optimize.result b/mysql-test/r/rpl_failed_optimize.result index ec0b3856ae7..7e397550331 100644 --- a/mysql-test/r/rpl_failed_optimize.result +++ b/mysql-test/r/rpl_failed_optimize.result @@ -15,9 +15,8 @@ Warnings: Error 1205 Lock wait timeout exceeded; try restarting transaction OPTIMIZE TABLE non_existing; Table Op Msg_type Msg_text -test.non_existing optimize error Table 'test.non_existing' doesn't exist -Warnings: -Error 1146 Table 'test.non_existing' doesn't exist +test.non_existing optimize Error Table 'test.non_existing' doesn't exist +test.non_existing optimize error Corrupt select * from t1; a 1 diff --git a/mysql-test/r/rpl_log_pos.result b/mysql-test/r/rpl_log_pos.result index 28ddbaf33a0..7543814578a 100644 --- a/mysql-test/r/rpl_log_pos.result +++ b/mysql-test/r/rpl_log_pos.result @@ -7,27 +7,80 @@ start slave; show master status; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 106 <Binlog_Ignore_DB> -show slave status; -Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 106 # # master-bin.000001 Yes Yes 0 0 106 # None 0 No # No -stop slave; -change master to master_log_pos=75; -start slave; stop slave; change master to master_log_pos=75; show slave status; -Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 75 # # master-bin.000001 No No 0 0 75 # None 0 No # No +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 75 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running No +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 75 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +Master_SSL_Verify_Server_Cert No start slave; -show slave status; -Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 75 # # master-bin.000001 No Yes 0 0 75 # None 0 No # No stop slave; -change master to master_log_pos=178; -start slave; show slave status; -Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 178 # # master-bin.000001 # Yes 0 0 178 # None 0 No # No +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 75 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running No +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 75 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +Master_SSL_Verify_Server_Cert No show master status; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 106 <Binlog_Ignore_DB> @@ -35,7 +88,6 @@ create table if not exists t1 (n int); drop table if exists t1; create table t1 (n int); insert into t1 values (1),(2),(3); -stop slave; change master to master_log_pos=106; start slave; select * from t1 ORDER BY n; @@ -44,3 +96,4 @@ n 2 3 drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/rpl_ssl.result b/mysql-test/r/rpl_ssl.result index 908f0020188..64d52d63f78 100644 --- a/mysql-test/r/rpl_ssl.result +++ b/mysql-test/r/rpl_ssl.result @@ -96,3 +96,4 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No drop user replssl@localhost; drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index acab2f17910..66cd929ee05 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -752,4 +752,6 @@ Tables_in_test Table_type été BASE TABLE drop table `été`; set names latin1; +show columns from `#mysql50#????????`; +Got one of the listed errors End of 5.1 tests diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 6c0330537ca..1c3fd53b0b2 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4404,8 +4404,10 @@ call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK Table Op Msg_type Msg_text +test.t1 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 backup status OK Table Op Msg_type Msg_text +test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 5.2. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK drop procedure bug13012| create view v1 as select * from t1| @@ -4420,61 +4422,58 @@ Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE +test.v1 repair Error 'test.v1' is not BASE TABLE +test.v1 repair error Corrupt Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE +test.v1 optimize Error 'test.v1' is not BASE TABLE +test.v1 optimize error Corrupt Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -Error 1347 'test.v1' is not BASE TABLE -Error 1347 'test.v1' is not BASE TABLE +test.v1 analyze Error 'test.v1' is not BASE TABLE +test.v1 analyze error Corrupt call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE +test.v1 repair Error 'test.v1' is not BASE TABLE +test.v1 repair error Corrupt Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE +test.v1 optimize Error 'test.v1' is not BASE TABLE +test.v1 optimize error Corrupt Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -Error 1347 'test.v1' is not BASE TABLE -Error 1347 'test.v1' is not BASE TABLE +test.v1 analyze Error 'test.v1' is not BASE TABLE +test.v1 analyze error Corrupt call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE +test.v1 repair Error 'test.v1' is not BASE TABLE +test.v1 repair error Corrupt Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE +test.v1 optimize Error 'test.v1' is not BASE TABLE +test.v1 optimize error Corrupt Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -Error 1347 'test.v1' is not BASE TABLE -Error 1347 'test.v1' is not BASE TABLE +test.v1 analyze Error 'test.v1' is not BASE TABLE +test.v1 analyze error Corrupt drop procedure bug13012| drop view v1| select * from t1 order by data| diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 8d4a5ee32be..92cd58f2ba3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2853,7 +2853,6 @@ a 3 4 DROP TABLE t1,t2,t3; -purge master logs before (select adddate(current_timestamp(), interval -4 day)); CREATE TABLE t1 (f1 INT); CREATE TABLE t2 (f2 INT); INSERT INTO t1 VALUES (1); @@ -4081,6 +4080,32 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res` DROP TABLE t1; +CREATE TABLE t1 ( +a varchar(255) default NULL, +b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, +INDEX idx(a,b) +); +CREATE TABLE t2 ( +a varchar(255) default NULL +); +INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24'); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26'); +INSERT INTO `t2` VALUES ('abcdefghijk'); +INSERT INTO `t2` VALUES ('asdf'); +SET session sort_buffer_size=8192; +SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2; +d1 +1 +1 +DROP TABLE t1,t2; End of 5.0 tests. CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); diff --git a/mysql-test/r/subselect_notembedded.result b/mysql-test/r/subselect_notembedded.result index dd4b0701c32..44ae055425e 100644 --- a/mysql-test/r/subselect_notembedded.result +++ b/mysql-test/r/subselect_notembedded.result @@ -1 +1,3 @@ purge master logs before (select adddate(current_timestamp(), interval -4 day)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select adddate(current_timestamp(), interval -4 day))' at line 1 +purge master logs before adddate(current_timestamp(), interval -4 day); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 1b4e0decb62..59ed4511aed 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1454,19 +1454,22 @@ CREATE TABLE t2 (id INTEGER); INSERT INTO t2 VALUES (1),(2); CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (new.id); -SELECT GET_LOCK('B26162',20); -GET_LOCK('B26162',20) +SELECT GET_LOCK('B26162',120); +GET_LOCK('B26162',120) 1 -SELECT 'rl_acquirer', GET_LOCK('B26162',5), id FROM t2 WHERE id = 1; +SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1; SET SESSION LOW_PRIORITY_UPDATES=1; SET GLOBAL LOW_PRIORITY_UPDATES=1; INSERT INTO t1 VALUES (5); SELECT 'rl_contender', id FROM t2 WHERE id > 1; SELECT RELEASE_LOCK('B26162'); RELEASE_LOCK('B26162') -0 -rl_acquirer GET_LOCK('B26162',5) id -rl_acquirer 0 1 +1 +rl_acquirer GET_LOCK('B26162',120) id +rl_acquirer 1 1 +SELECT RELEASE_LOCK('B26162'); +RELEASE_LOCK('B26162') +1 rl_contender id rl_contender 2 DROP TRIGGER t1_test; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 4e59e7ace2a..ffe4bac142d 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -411,6 +411,22 @@ if(@bug28261 = f1, '', @bug28261:= f1) 2001-01-01 2002-02-02 drop table t1; +create table t1(f1 datetime); +insert into t1 values('2001-01-01'),('2002-02-02'); +select * from t1 where f1 between 20020101 and 20070101000000; +f1 +2002-02-02 00:00:00 +select * from t1 where f1 between 2002010 and 20070101000000; +f1 +2001-01-01 00:00:00 +2002-02-02 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '2002010' for column 'f1' at row 1 +select * from t1 where f1 between 20020101 and 2007010100000; +f1 +Warnings: +Warning 1292 Incorrect datetime value: '2007010100000' for column 'f1' at row 1 +drop table t1; set @org_mode=@@sql_mode; create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); Warnings: diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index dfbd6619436..efac8cbe580 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -790,3 +790,12 @@ Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' drop table t1; +CREATE TABLE t1 ( +qty decimal(16,6) default NULL, +dps tinyint(3) unsigned default NULL +); +INSERT INTO t1 VALUES (1.1325,3); +SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1; +ROUND(qty,3) dps ROUND(qty,dps) +1.133 3 1.133 +DROP TABLE t1; diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index c7a9b0982a9..bba722e523c 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -311,4 +311,27 @@ drop function f3; drop function metaphon; drop function myfunc_double; drop function myfunc_int; +CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +create table t1 (a char); +set GLOBAL query_cache_size=1355776; +reset query cache; +select metaphon('MySQL') from t1; +metaphon('MySQL') +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +select metaphon('MySQL') from t1; +metaphon('MySQL') +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t1; +drop function metaphon; +set GLOBAL query_cache_size=default; End of 5.0 tests. diff --git a/mysql-test/r/unsafe_binlog_innodb.result b/mysql-test/r/unsafe_binlog_innodb.result index 54a24a52d57..b2cf16ad58f 100644 --- a/mysql-test/r/unsafe_binlog_innodb.result +++ b/mysql-test/r/unsafe_binlog_innodb.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; create table t1 (id int not null, f_id int not null, f int not null, primary key(f_id, id)) engine = InnoDB; create table t2 (id int not null,s_id int not null,s varchar(200), diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index e3368a4aeab..ff43993cfdb 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -970,3 +970,8 @@ set global server_id =@my_server_id; set global slow_launch_time =@my_slow_launch_time; set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; +show global variables where Variable_name='table_definition_cache' or +Variable_name='table_lock_wait_timeout'; +Variable_name Value +table_definition_cache # +table_lock_wait_timeout # diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 5ed06855f19..e0620c2fa57 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -23,6 +23,9 @@ c 5 6 11 +select is_updatable from information_schema.views where table_name='v1'; +is_updatable +NO create temporary table t1 (a int, b int); select * from t1; a b @@ -322,6 +325,12 @@ create table t1 (a int, b int, primary key(a)); insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; +select is_updatable from information_schema.views where table_name='v2'; +is_updatable +NO +select is_updatable from information_schema.views where table_name='v1'; +is_updatable +YES update v1 set c=a+c; ERROR HY000: Column 'c' is not updatable update v2 set a=a+c; @@ -604,6 +613,10 @@ insert into t1 values(5,'Hello, world of views'); create view v1 as select * from t1; create view v2 as select * from v1; update v2 set col2='Hello, view world'; +select is_updatable from information_schema.views; +is_updatable +YES +YES select * from t1; col1 col2 5 Hello, view world @@ -1378,7 +1391,9 @@ test.t1 check status OK drop table t1; check table v1; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check Error Table 'test.t1' doesn't exist +test.v1 check Error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check error Corrupt drop view v1; create table t1 (a int); create table t2 (a int); @@ -1902,11 +1917,17 @@ CREATE VIEW v6 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; DROP TABLE t1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check Error Table 'test.t1' doesn't exist +test.v1 check Error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check error Corrupt test.v2 check status OK -test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v3 check Error Table 'test.t1' doesn't exist +test.v3 check Error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v3 check error Corrupt test.v4 check status OK -test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v5 check Error Table 'test.t1' doesn't exist +test.v5 check Error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v5 check error Corrupt test.v6 check status OK drop view v1, v2, v3, v4, v5, v6; drop table t2; @@ -1926,11 +1947,17 @@ CREATE VIEW v6 AS SELECT f2() FROM t3; drop function f1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check Error FUNCTION test.f1 does not exist +test.v1 check Error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check error Corrupt test.v2 check status OK -test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v3 check Error FUNCTION test.f1 does not exist +test.v3 check Error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v3 check error Corrupt test.v4 check status OK -test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v5 check Error FUNCTION test.f1 does not exist +test.v5 check Error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v5 check error Corrupt test.v6 check status OK create function f1 () returns int return (select max(col1) from t1); DROP TABLE t1; @@ -2366,35 +2393,29 @@ CREATE TABLE t1(id INT); CREATE VIEW v1 AS SELECT id FROM t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text -test.v1 optimize error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE +test.v1 optimize Error 'test.v1' is not BASE TABLE +test.v1 optimize error Corrupt ANALYZE TABLE v1; Table Op Msg_type Msg_text -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE +test.v1 analyze Error 'test.v1' is not BASE TABLE +test.v1 analyze error Corrupt REPAIR TABLE v1; Table Op Msg_type Msg_text -test.v1 repair error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE +test.v1 repair Error 'test.v1' is not BASE TABLE +test.v1 repair error Corrupt DROP TABLE t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text -test.v1 optimize error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE +test.v1 optimize Error 'test.v1' is not BASE TABLE +test.v1 optimize error Corrupt ANALYZE TABLE v1; Table Op Msg_type Msg_text -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE +test.v1 analyze Error 'test.v1' is not BASE TABLE +test.v1 analyze error Corrupt REPAIR TABLE v1; Table Op Msg_type Msg_text -test.v1 repair error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE +test.v1 repair Error 'test.v1' is not BASE TABLE +test.v1 repair error Corrupt DROP VIEW v1; create definer = current_user() sql security invoker view v1 as select 1; show create view v1; diff --git a/mysql-test/t/binlog_stm_ps.test b/mysql-test/t/binlog_stm_ps.test index fa29eea7890..83add5af3d7 100644 --- a/mysql-test/t/binlog_stm_ps.test +++ b/mysql-test/t/binlog_stm_ps.test @@ -1,5 +1,6 @@ # This test is to verify replication with PS +-- source include/not_embedded.inc -- source include/have_binlog_format_mixed_or_statement.inc -- disable_query_log diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 62482d576b2..c7c7f3e13ab 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1596,6 +1596,27 @@ select * from bug15205; drop table bug15205; drop table bug15205_2; + +# +# Bug#28862 "Extended Latin1 characters get lost in CVS engine" +# +set names latin1; +create table t1 ( + c varchar(1), + name varchar(64) +) character set latin1 engine=csv; +insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE'); +insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE'); +insert into t1 values (0xEE,'LATIN SMALL LETTER I WITH CIRCUMFLEX'); +insert into t1 values (0xFE,'LATIN SMALL LETTER THORN'); +insert into t1 values (0xF7,'DIVISION SIGN'); +insert into t1 values (0xFF,'LATIN SMALL LETTER Y WITH DIAERESIS'); +select hex(c), c, name from t1 order by 1; +drop table t1; + +--echo End of 5.0 tests + + # # Bug#22080 "CHECK fails to identify some corruption" # diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 1b8d68b26ef..27e3b9bc614 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -43,8 +43,8 @@ rpl_ndb_ddl : BUG#28798 2007-05-31 lars Valgrind failure in NDB #rpl_ndb_dd_advance : Bug#25913 rpl_ndb_dd_advance fails randomly -rpl_ndb_stm_innodb : Bug#26783 ndb_partition_error2 : HF is not sure if the test can work as internded on all the platforms im_options_set : Bug#20294: Instance manager tests fail randomly im_options_unset : Bug#20294: Instance manager tests fail randomly +mysql_upgrade : Bug#28560 test links to /usr/local/mysql/lib libraries, causes non-determinism and failures on ABI breakage diff --git a/mysql-test/t/error_simulation-master.opt b/mysql-test/t/error_simulation-master.opt new file mode 100644 index 00000000000..edb77cfa85e --- /dev/null +++ b/mysql-test/t/error_simulation-master.opt @@ -0,0 +1 @@ +--loose-debug=d,raise_error diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test new file mode 100644 index 00000000000..2f6ea5eac87 --- /dev/null +++ b/mysql-test/t/error_simulation.test @@ -0,0 +1,30 @@ +-- source include/have_debug.inc + +# +# Bug #28499: crash for grouping query when tmp_table_size is too small +# + +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1 ( + a varchar(32) character set utf8 collate utf8_bin NOT NULL, + b varchar(32) character set utf8 collate utf8_bin NOT NULL ) +ENGINE=MyISAM DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES + ('AAAAAAAAAA','AAAAAAAAAA'), ('AAAAAAAAAB','AAAAAAAAAB '), + ('AAAAAAAAAB','AAAAAAAAAB'), ('AAAAAAAAAC','AAAAAAAAAC'), + ('AAAAAAAAAD','AAAAAAAAAD'), ('AAAAAAAAAE','AAAAAAAAAE'), + ('AAAAAAAAAF','AAAAAAAAAF'), ('AAAAAAAAAG','AAAAAAAAAG'), + ('AAAAAAAAAH','AAAAAAAAAH'), ('AAAAAAAAAI','AAAAAAAAAI'), + ('AAAAAAAAAJ','AAAAAAAAAJ'), ('AAAAAAAAAK','AAAAAAAAAK'); + +set tmp_table_size=1024; + +--replace_regex /in table '[^']+'/in table 'tmp_table'/ +--error ER_DUP_KEY +SELECT MAX(a) FROM t1 GROUP BY a,b; + +set tmp_table_size=default; + +DROP TABLE t1; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index 3a4f2f2f5f2..d58d038c3ea 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -133,4 +133,28 @@ disconnect con3; connection default; drop table t1, t2; -# End of 5.0 tests +--echo End of 5.0 tests + +# +# Bug #26380: LOCK TABLES + FLUSH LOGS causes deadlock +# +set @old_general_log= @@general_log; +set @old_read_only= @@read_only; +set global general_log= on; + +flush tables with read lock; +flush logs; +unlock tables; + +set global read_only=1; +flush logs; +unlock tables; + +flush tables with read lock; +flush logs; +unlock tables; + +set global general_log= @old_general_log; +set global read_only= @old_read_only; + +--echo End of 5.1 tests diff --git a/mysql-test/t/flush_block_commit_notembedded.test b/mysql-test/t/flush_block_commit_notembedded.test index e3b59e0fc45..4a0300acf78 100644 --- a/mysql-test/t/flush_block_commit_notembedded.test +++ b/mysql-test/t/flush_block_commit_notembedded.test @@ -3,6 +3,8 @@ # We verify that we did not introduce a deadlock. # This is intended to mimick how mysqldump and innobackup work. +-- source include/have_log_bin.inc + # And it requires InnoDB -- source include/have_log_bin.inc -- source include/have_innodb.inc diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 3845bdcb983..426eec4be6d 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -558,6 +558,19 @@ SELECT Overlaps(@horiz1, @point2) FROM DUAL; DROP TABLE t1; +# +# Bug#28763: Selecting geometry fields in UNION caused server crash. +# +create table t1(f1 geometry, f2 point, f3 linestring); +select f1 from t1 union select f1 from t1; +insert into t1 (f2,f3) values (GeomFromText('POINT(1 1)'), + GeomFromText('LINESTRING(0 0,1 1,2 2)')); +select AsText(f2),AsText(f3) from t1; +select AsText(a) from (select f2 as a from t1 union select f3 from t1) t; +create table t2 as select f2 as a from t1 union select f3 from t1; +desc t2; +select AsText(a) from t2; +drop table t1, t2; --echo End of 5.0 tests diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index f455914a0cd..f6075ba2ee4 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -555,5 +555,35 @@ drop database mysqltest_1; drop database mysqltest_2; drop user mysqltest_u1@localhost; +# +# Bug#18660 Can't grant any privileges on single table in database +# with underscore char +# +grant all on `mysqltest\_%`.* to mysqltest_1@localhost with grant option; +grant usage on *.* to mysqltest_2@localhost; +connect (con18600_1,localhost,mysqltest_1,,); + +create database mysqltest_1; +use mysqltest_1; +create table t1 (f1 int); + +grant create on `mysqltest\_1`.* to mysqltest_2@localhost; +grant select on mysqltest_1.t1 to mysqltest_2@localhost; +connect (con3,localhost,mysqltest_2,,); +connection con3; +--error 1044 +create database mysqltest_3; +use mysqltest_1; +create table t2(f1 int); +select * from t1; +connection default; +drop database mysqltest_1; + +revoke all privileges, grant option from mysqltest_1@localhost; +revoke all privileges, grant option from mysqltest_2@localhost; +drop user mysqltest_1@localhost; +drop user mysqltest_2@localhost; + + --echo End of 5.0 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 4e21568377f..3db8972bc6b 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -780,7 +780,7 @@ INSERT INTO t1 SELECT a + 8,b FROM t1; INSERT INTO t1 SELECT a + 16,b FROM t1; INSERT INTO t1 SELECT a + 32,b FROM t1; INSERT INTO t1 SELECT a + 64,b FROM t1; -INSERT INTO t1 SELECT a + 128,b FROM t1; +INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16; ANALYZE TABLE t1; EXPLAIN SELECT a FROM t1 WHERE a < 2; EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index a49044e63c1..755419cbf52 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1024,6 +1024,21 @@ where t.table_schema = 'information_schema' and group by c2.column_type order by num limit 1) group by t.table_name order by num1, t.table_name; +# +# Bug#28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong +# +create table t1(f1 int); +create view v1 as select f1+1 as a from t1; +create table t2 (f1 int, f2 int); +create view v2 as select f1+1 as a, f2 as b from t2; +select table_name, is_updatable from information_schema.views; +# +# Note: we can perform 'delete' for non updatable view. +# +delete from v1; +drop view v1,v2; +drop table t1,t2; + --echo End of 5.0 tests. # # Show engines diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index f75b5aedffe..67f21731afe 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -290,3 +290,19 @@ INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2 SELECT * FROM t1; DROP TABLE t1; + +# +# Bug#28904: INSERT .. ON DUPLICATE was silently updating rows when it +# shouldn't. +# +create table t1(f1 int primary key, + f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP); +insert into t1(f1) values(1); +--replace_column 1 # +select @stamp1:=f2 from t1; +--sleep 2 +insert into t1(f1) values(1) on duplicate key update f1=1; +--replace_column 1 # +select @stamp2:=f2 from t1; +select if( @stamp1 = @stamp2, "correct", "wrong"); +drop table t1; diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index 3044964ebc3..4c14dc96aaa 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -219,4 +219,31 @@ set global key_cache_block_size= @my_key_cache_block_size; set @@global.key_buffer_size=0; select @@global.key_buffer_size; +# +# Bug#28478 - Improper key_cache_block_size corrupts MyISAM tables +# +SET @bug28478_key_cache_block_size= @@global.key_cache_block_size; +SET GLOBAL key_cache_block_size= 1536; +CREATE TABLE t1 ( + id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c1 CHAR(150), + c2 CHAR(150), + c3 CHAR(150), + KEY(c1, c2, c3) + ) ENGINE= MyISAM; +INSERT INTO t1 (c1, c2, c3) VALUES + ('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'), + ('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'), + ('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'), + ('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'), + ('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'), + ('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z'); +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +CHECK TABLE t1; +SHOW VARIABLES LIKE 'key_cache_block_size'; +SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size; +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index e136dc0ca3c..fd479276b3b 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -490,4 +490,25 @@ insert into t1 values (1); drop table t2; drop table t1; +# +# BUG#26976 - Missing table in merge not noted in related error msg + SHOW +# CREATE TABLE fails +# +CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2); +--error 1168 +SELECT * FROM tm1; +CHECK TABLE tm1; +CREATE TABLE t1(a INT); +--error 1168 +SELECT * FROM tm1; +CHECK TABLE tm1; +CREATE TABLE t2(a BLOB); +--error 1168 +SELECT * FROM tm1; +CHECK TABLE tm1; +ALTER TABLE t2 MODIFY a INT; +SELECT * FROM tm1; +CHECK TABLE tm1; +DROP TABLE tm1, t1, t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index f26a495c7aa..0f1fee453a5 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2,6 +2,9 @@ --source include/not_embedded.inc --source include/have_log_bin.inc +# Binlog is required +--source include/have_log_bin.inc + --disable_warnings DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa, t3; drop database if exists mysqldump_test_db; diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index b7bb0a6687a..aa0e2f89382 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -1,3 +1,5 @@ +-- source include/have_log_bin.inc + # This test should work in embedded server after mysqltest is fixed -- source include/not_embedded.inc diff --git a/mysql-test/t/ndb_backup_print.test b/mysql-test/t/ndb_backup_print.test new file mode 100644 index 00000000000..1e516f03ae6 --- /dev/null +++ b/mysql-test/t/ndb_backup_print.test @@ -0,0 +1,66 @@ +-- source include/have_ndb.inc +-- source include/ndb_default_cluster.inc +-- source include/not_embedded.inc + +--disable_warnings +use test; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; +--enable_warnings + +#NO.1 test output of backup +--exec $NDB_MGM --no-defaults -e "start backup" |sed -e 's/[0-9]//g' |sed -e 's/localhost//g' |sed -e 's/\.\.\.*//g' + +create table t1 + (pk int key + ,a1 BIT(1), a2 BIT(5), a3 BIT(33), a4 BIT(63), a5 BIT(64) + ,b1 TINYINT, b2 TINYINT UNSIGNED + ,c1 SMALLINT, c2 SMALLINT UNSIGNED + ,d1 INT, d2 INT UNSIGNED + ,e1 BIGINT, e2 BIGINT UNSIGNED + ,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY + ,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY + ,h1 BINARY(1), h2 BINARY(8), h3 BINARY(255) + ,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000) + ) engine ndb; + +insert into t1 values + (1 + ,0x1, 0x17, 0x789a, 0x789abcde, 0xfedc0001 + ,127, 255 + ,32767, 65535 + ,2147483647, 4294967295 + ,9223372036854775807, 18446744073709551615 + ,'1','12345678901234567890123456789012','123456789' + ,'1','12345678901234567890123456789012','123456789' + ,0x12,0x123456789abcdef0, 0x012345 + ,0x12,0x123456789abcdef0, 0x00123450 + ); + +insert into t1 values + (2 + ,0, 0, 0, 0, 0 + ,-128, 0 + ,-32768, 0 + ,-2147483648, 0 + ,-9223372036854775808, 0 + ,'','','' + ,'','','' + ,0x0,0x0,0x0 + ,0x0,0x0,0x0 + ); + +insert into t1 values + (3 + ,NULL,NULL,NULL,NULL,NULL + ,NULL,NULL + ,NULL,NULL + ,NULL,NULL + ,NULL,NULL + ,NULL,NULL,NULL + ,NULL,NULL,NULL + ,NULL,NULL,NULL + ,NULL,NULL,NULL + ); + +#NO.2 test output of backup after some simple SQL operations +--exec $NDB_MGM --no-defaults -e "start backup" |sed -e 's/[0-9]//g' |sed -e 's/localhost//g' |sed -e 's/\.\.\.*//g' diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test index f8b83b2b9ec..b9ccdf9fd0d 100644 --- a/mysql-test/t/ndb_basic.test +++ b/mysql-test/t/ndb_basic.test @@ -6,16 +6,16 @@ DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7; drop database if exists mysqltest; --enable_warnings -## workaround for bug#16445 -## remove to reproduce bug and run tests from ndb start -## and with ndb_autodiscover disabled. Fails on Linux 50 % of the times -#CREATE TABLE t1 ( -# pk1 INT NOT NULL PRIMARY KEY, -# attr1 INT NOT NULL, -# attr2 INT, -# attr3 VARCHAR(10) -#) ENGINE=ndbcluster; -#drop table t1; +# workaround for bug#16445 +# remove to reproduce bug and run tests from ndb start +# and with ndb_autodiscover disabled. Fails on Linux 50 % of the times +CREATE TABLE t1 ( + pk1 INT NOT NULL PRIMARY KEY, + attr1 INT NOT NULL, + attr2 INT, + attr3 VARCHAR(10) +) ENGINE=ndbcluster; +drop table t1; # # Basic test to show that the NDB @@ -23,6 +23,14 @@ drop database if exists mysqltest; # # +# Show status and variables +# +--replace_column 2 # +SHOW GLOBAL STATUS LIKE 'ndb%'; +--replace_column 2 # +SHOW GLOBAL VARIABLES LIKE 'ndb%'; + +# # Create a normal table with primary key # CREATE TABLE t1 ( @@ -752,46 +760,6 @@ INSERT INTO t1 VALUES SELECT * FROM t1 ORDER BY a; DROP TABLE t1; -# End of 5.0 tests ---echo End of 5.0 tests - - -# -# Bug #18483 Cannot create table with FK constraint -# ndb does not support foreign key constraint, it is silently ignored -# in line with other storage engines -# -CREATE TABLE t1 (a VARCHAR(255) NOT NULL, - CONSTRAINT pk_a PRIMARY KEY (a))engine=ndb; -CREATE TABLE t2(a VARCHAR(255) NOT NULL, - b VARCHAR(255) NOT NULL, - c VARCHAR(255) NOT NULL, - CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), - CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb; -drop table t1, t2; - -# bug#24301 -create table t1 (a int not null primary key, b int) engine=ndb; -insert into t1 values(1,1),(2,2),(3,3); -create table t2 like t1; -insert into t2 select * from t1; -select * from t1 order by a; -select * from t2 order by a; -drop table t1, t2; - -# create table if not exists ---disable_warnings -create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; -create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; ---enable_warnings - -# create like -create table t2 like t1; - -# multi rename -rename table t1 to t10, t2 to t20; -drop table t10,t20; - # delete create table t1 (a int not null primary key, b int not null) engine=ndb; create table t2 (a int not null primary key, b int not null) engine=ndb; @@ -832,4 +800,44 @@ update ignore t1,t2 set a = 1, c = 1 where a = 3 and c = 3; select * from t1 order by a; drop table t1,t2; +# End of 5.0 tests +--echo End of 5.0 tests + + +# +# Bug #18483 Cannot create table with FK constraint +# ndb does not support foreign key constraint, it is silently ignored +# in line with other storage engines +# +CREATE TABLE t1 (a VARCHAR(255) NOT NULL, + CONSTRAINT pk_a PRIMARY KEY (a))engine=ndb; +CREATE TABLE t2(a VARCHAR(255) NOT NULL, + b VARCHAR(255) NOT NULL, + c VARCHAR(255) NOT NULL, + CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), + CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb; +drop table t1, t2; + +# bug#24301 +create table t1 (a int not null primary key, b int) engine=ndb; +insert into t1 values(1,1),(2,2),(3,3); +create table t2 like t1; +insert into t2 select * from t1; +select * from t1 order by a; +select * from t2 order by a; +drop table t1, t2; + +# create table if not exists +--disable_warnings +create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +--enable_warnings + +# create like +create table t2 like t1; + +# multi rename +rename table t1 to t10, t2 to t20; +drop table t10,t20; + --echo End of 5.1 tests diff --git a/mysql-test/t/ndb_insert.test b/mysql-test/t/ndb_insert.test index bffb2edb54c..5b74cc9202c 100644 --- a/mysql-test/t/ndb_insert.test +++ b/mysql-test/t/ndb_insert.test @@ -639,4 +639,141 @@ insert ignore into t1 values (1,0), (2,0), (2,null), (3,null); select * from t1 order by a; drop table t1; +# Bug#26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB cluster + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER; + +CREATE TABLE t2 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=MYISAM; + +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +TRUNCATE t1; +TRUNCATE t2; +SET @@session.auto_increment_offset=5; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +TRUNCATE t1; +TRUNCATE t2; +SET @@session.auto_increment_increment=2; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +DROP TABLE t1, t2; + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; + +CREATE TABLE t2 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 7; + +SET @@session.auto_increment_offset=1; +SET @@session.auto_increment_increment=1; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +DROP TABLE t1, t2; + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3; + +CREATE TABLE t2 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 3; + +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +DROP TABLE t1, t2; + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; + +CREATE TABLE t2 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 7; + +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +DROP TABLE t1, t2; + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5; + +CREATE TABLE t2 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 5; + +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +DROP TABLE t1, t2; + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100; + +CREATE TABLE t2 ( + pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + b INT NOT NULL, + c INT NOT NULL UNIQUE +) ENGINE=MYISAM AUTO_INCREMENT = 100; + +SET @@session.auto_increment_offset=5; +SET @@session.auto_increment_increment=10; +INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); +INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); +SELECT * FROM t1 ORDER BY pk; +SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; +DROP TABLE t1, t2; + # End of 4.1 tests diff --git a/mysql-test/t/ndb_restore.test b/mysql-test/t/ndb_restore.test index 61927a1f90a..7f0cafdfd77 100644 --- a/mysql-test/t/ndb_restore.test +++ b/mysql-test/t/ndb_restore.test @@ -33,7 +33,7 @@ CREATE TABLE `t2_c` ( PRIMARY KEY (`capgotod`), KEY `i quadaddsvr` (`gotod`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; -INSERT INTO `t2_c` VALUES (500,4,'','q3.net','addavp:MK_CASELECTOR=1','postorod rattoaa'),(2,1,'4','','addavp:MK_BRANDTAD=345','REDS Brandtad'),(3,2,'4','q3.net','execorder','fixedRatediPO REDS'),(1,1,'3','','addavp:MK_BRANDTAD=123','TEST Brandtad'),(6,5,'','told.q3.net','addavp:MK_BRANDTAD=123','Brandtad Toldzone'),(4,3,'3','q3.net','addavp:MK_POOLHINT=2','ratedi PO TEST'); +INSERT INTO `t2_c` VALUES (500,4,'','q3.net','addavp:MK_CASELECTOR=1','postorod rattoaa'),(2,1,'4','','addavp:MK_BRANDTAD=345','REDS Brandtad'),(3,2,'4','q3.net','execorder','fixedRatediPO REDS'),(1,1,'3','','addavp:MK_BRANDTAD=123','TEST Brandtad'),(6,5,'','told.q3.net','addavp:MK_BRANDTAD=123','Brandtad Toldzone'),(4,3,'3','q3.net','addavp:MK_POOLHINT=2','ratedi PO TEST'),(5,0,'',NULL,NULL,''); # Added ROW_FORMAT=FIXED to use below to see that setting is preserved # by restore diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 35b9cf3a016..d2b9a5bcf83 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1464,5 +1464,34 @@ insert into t1 values (null), (null), (null); --replace_column 9 0 12 NULL 13 NULL 14 NULL show table status; drop table t1; +# Bug #28488: Incorrect information in file: './test/t1_test#.frm' +# + +CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a)) + PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32)); +INSERT INTO t1 VALUES (1, REPEAT('a', 10)); +INSERT INTO t1 SELECT a + 1, b FROM t1; +INSERT INTO t1 SELECT a + 2, b FROM t1; +INSERT INTO t1 SELECT a + 4, b FROM t1; +INSERT INTO t1 SELECT a + 8, b FROM t1; + +ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64)); +ALTER TABLE t1 DROP PARTITION p1; + +DROP TABLE t1; + +# +# Bug #27816: Log tables ran with partitions crashes the server when logging +# is enabled. +# + +USE mysql; +SET GLOBAL general_log = 0; +ALTER TABLE general_log ENGINE = MyISAM; +--error ER_WRONG_USAGE +ALTER TABLE general_log PARTITION BY RANGE (TO_DAYS(event_time)) + (PARTITION p0 VALUES LESS THAN (733144), PARTITION p1 VALUES LESS THAN (3000000)); +ALTER TABLE general_log ENGINE = CSV; +SET GLOBAL general_log = default; --echo End of 5.1 tests diff --git a/mysql-test/t/rpl_events.test b/mysql-test/t/rpl_events.test index 895e94c438b..62ffead7dcb 100644 --- a/mysql-test/t/rpl_events.test +++ b/mysql-test/t/rpl_events.test @@ -22,3 +22,26 @@ set binlog_format=statement; # Embedded server doesn't support binlogging --source include/rpl_events.inc +# +# Bug #28953 Using events in a replication let the slave crash. +# + +connection master; + +DELIMITER |; +CREATE EVENT event1 ON SCHEDULE EVERY 1 YEAR +DO BEGIN + select * from t1; +END;| +DELIMITER ;| + +ALTER EVENT event1 RENAME TO event2; + +sync_slave_with_master; + +connection master; + +DROP EVENT event2; + +sync_slave_with_master; + diff --git a/mysql-test/t/rpl_log_pos.test b/mysql-test/t/rpl_log_pos.test index b98d89c1dc3..2c6c1d9868d 100644 --- a/mysql-test/t/rpl_log_pos.test +++ b/mysql-test/t/rpl_log_pos.test @@ -14,31 +14,29 @@ source include/master-slave.inc; --replace_column 3 <Binlog_Ignore_DB> show master status; sync_slave_with_master; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 23 # 33 # -show slave status; -stop slave; -change master to master_log_pos=75; -start slave; -sleep 5; + stop slave; +--source include/wait_for_slave_to_stop.inc change master to master_log_pos=75; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # -show slave status; +query_vertical show slave status; + start slave; -sleep 5; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 23 # 33 # -show slave status; +let $slave_param= Slave_SQL_Running; +let $slave_param_value= Yes; +--source include/wait_for_slave_param.inc +let $slave_param= Slave_IO_Running; +let $slave_param_value= No; +--source include/wait_for_slave_param.inc stop slave; -change master to master_log_pos=178; -start slave; -sleep 2; +--source include/wait_for_slave_to_stop.inc + --replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 11 # 23 # 33 # -show slave status; +--replace_column 1 # 8 # 9 # 23 # 33 # +query_vertical show slave status; + connection master; --replace_column 3 <Binlog_Ignore_DB> show master status; @@ -48,7 +46,6 @@ create table t1 (n int); insert into t1 values (1),(2),(3); save_master_pos; connection slave; -stop slave; change master to master_log_pos=106; start slave; sync_with_master; @@ -57,4 +54,4 @@ connection master; drop table t1; sync_slave_with_master; -# End of 4.1 tests +--echo End of 5.0 tests diff --git a/mysql-test/t/rpl_ssl.test b/mysql-test/t/rpl_ssl.test index dd03d5533b0..a0317028557 100644 --- a/mysql-test/t/rpl_ssl.test +++ b/mysql-test/t/rpl_ssl.test @@ -58,6 +58,7 @@ connection master; # the sync has something to do insert into t1 values (NULL); sync_slave_with_master; +--source include/wait_for_slave_to_start.inc --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 # query_vertical show slave status; @@ -67,3 +68,4 @@ drop user replssl@localhost; drop table t1; sync_slave_with_master; +--echo End of 5.0 tests diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 09b21db062f..af2c044ee10 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -593,4 +593,10 @@ show full tables; drop table `été`; set names latin1; +# +# Bug#26402 Server crashes with old-style named table +# +--error ER_NO_SUCH_TABLE,ER_FILE_NOT_FOUND +show columns from `#mysql50#????????`; + --echo End of 5.1 tests diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index c07178b3230..0b04b9d7668 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -2,6 +2,7 @@ # tests that require InnoDB... # +-- source include/have_log_bin.inc -- source include/have_innodb.inc --disable_warnings diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 0d1b71d8707..e88ded56914 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1822,13 +1822,6 @@ SELECT * FROM t1 DROP TABLE t1,t2,t3; # -# BUG #10308: purge log with subselect -# - -purge master logs before (select adddate(current_timestamp(), interval -4 day)); - - -# # Bug#18503: Queries with a quantified subquery returning empty set may # return a wrong result. # @@ -2920,6 +2913,38 @@ SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res; DROP TABLE t1; +# +# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1 +# + +CREATE TABLE t1 ( + a varchar(255) default NULL, + b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + INDEX idx(a,b) +); +CREATE TABLE t2 ( + a varchar(255) default NULL +); + +INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24'); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26'); +INSERT INTO `t2` VALUES ('abcdefghijk'); +INSERT INTO `t2` VALUES ('asdf'); + +SET session sort_buffer_size=8192; + +SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2; + +DROP TABLE t1,t2; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/subselect_notembedded.test b/mysql-test/t/subselect_notembedded.test index c5b23f6dac8..c112272e8ad 100644 --- a/mysql-test/t/subselect_notembedded.test +++ b/mysql-test/t/subselect_notembedded.test @@ -1,8 +1,9 @@ -- source include/not_embedded.inc # -# BUG #10308: purge log with subselect +# BUG#10308: purge log with subselect +# Bug#28553: mysqld crash in "purge master log before(select time from information_schema)" # - +--error 1064 purge master logs before (select adddate(current_timestamp(), interval -4 day)); - +purge master logs before adddate(current_timestamp(), interval -4 day); diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 31739f6c41b..6dc8c71176b 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1763,6 +1763,9 @@ select * from t1; select * from t3; drop table t1, t2, t3; +disconnect addconroot1; +disconnect addconroot2; +disconnect addconwithoutdb; # # Bug #26162: Trigger DML ignores low_priority_updates setting # @@ -1776,19 +1779,23 @@ INSERT INTO t2 VALUES (1),(2); CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (new.id); +CONNECT (rl_holder, localhost, root,,); CONNECT (rl_acquirer, localhost, root,,); CONNECT (wl_acquirer, localhost, root,,); CONNECT (rl_contender, localhost, root,,); -SELECT GET_LOCK('B26162',20); +CONNECTION rl_holder; +SELECT GET_LOCK('B26162',120); CONNECTION rl_acquirer; --send -SELECT 'rl_acquirer', GET_LOCK('B26162',5), id FROM t2 WHERE id = 1; +SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1; CONNECTION wl_acquirer; SET SESSION LOW_PRIORITY_UPDATES=1; SET GLOBAL LOW_PRIORITY_UPDATES=1; +#need to wait for rl_acquirer to lock on the B26162 lock +sleep 2; --send INSERT INTO t1 VALUES (5); @@ -1798,13 +1805,16 @@ CONNECTION rl_contender; --send SELECT 'rl_contender', id FROM t2 WHERE id > 1; -CONNECTION default; +CONNECTION rl_holder; +#need to wait for wl_acquirer and rl_contender to lock on t2 +sleep 2; SELECT RELEASE_LOCK('B26162'); -CONNECTION wl_acquirer; ---reap CONNECTION rl_acquirer; --reap +SELECT RELEASE_LOCK('B26162'); +CONNECTION wl_acquirer; +--reap CONNECTION rl_contender; --reap @@ -1812,6 +1822,7 @@ CONNECTION default; DISCONNECT rl_acquirer; DISCONNECT wl_acquirer; DISCONNECT rl_contender; +DISCONNECT rl_holder; DROP TRIGGER t1_test; DROP TABLE t1,t2; diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 78297a175dd..d8d137d81e6 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -272,6 +272,16 @@ select if(@bug28261 = f1, '', @bug28261:= f1) from t1; select if(@bug28261 = f1, '', @bug28261:= f1) from t1; drop table t1; +# +# Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an +# integer constants. +# +create table t1(f1 datetime); +insert into t1 values('2001-01-01'),('2002-02-02'); +select * from t1 where f1 between 20020101 and 20070101000000; +select * from t1 where f1 between 2002010 and 20070101000000; +select * from t1 where f1 between 20020101 and 2007010100000; +drop table t1; # # Test of storing datetime into date fields diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 4fdb0c8458f..5538f19f5f9 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -394,3 +394,17 @@ create table t1 as from (select 1 as s,'t' as t union select null, null ) as sub1; select group_concat(t) from t1 group by week(date)/10; drop table t1; + +# +# Bug#28980: ROUND(<dec expr>, <int col>) returned double values +# + +CREATE TABLE t1 ( + qty decimal(16,6) default NULL, + dps tinyint(3) unsigned default NULL +); +INSERT INTO t1 VALUES (1.1325,3); + +SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 7d870a86bc8..07a4a2d870b 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -340,4 +340,28 @@ drop function metaphon; drop function myfunc_double; drop function myfunc_int; +# +# Bug #28921: Queries containing UDF functions are cached +# + +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; +create table t1 (a char); + +set GLOBAL query_cache_size=1355776; +reset query cache; + +select metaphon('MySQL') from t1; +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; + +select metaphon('MySQL') from t1; +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; + +drop table t1; +drop function metaphon; +set GLOBAL query_cache_size=default; + + --echo End of 5.0 tests. diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 645a4d29633..efa2ce4a27c 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -760,3 +760,9 @@ set global slow_launch_time =@my_slow_launch_time; set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; +# +# Bug#28580 Repeatation of status variables +# +--replace_column 2 # +show global variables where Variable_name='table_definition_cache' or +Variable_name='table_lock_wait_timeout'; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 68817b0771a..03d7a52c640 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -32,6 +32,7 @@ create view v1 (c,d) as select a,b from t1 # simple view create view v1 (c) as select b+1 from t1; select c from v1; +select is_updatable from information_schema.views where table_name='v1'; # temporary table should not hide table of view create temporary table t1 (a int, b int); @@ -228,6 +229,8 @@ create table t1 (a int, b int, primary key(a)); insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; +select is_updatable from information_schema.views where table_name='v2'; +select is_updatable from information_schema.views where table_name='v1'; # try to update expression -- error 1348 update v1 set c=a+c; @@ -497,6 +500,7 @@ insert into t1 values(5,'Hello, world of views'); create view v1 as select * from t1; create view v2 as select * from v1; update v2 set col2='Hello, view world'; +select is_updatable from information_schema.views; select * from t1; drop view v2, v1; drop table t1; |