diff options
| author | Nirbhay Choubey <nirbhay@mariadb.com> | 2015-12-19 14:24:38 -0500 |
|---|---|---|
| committer | Nirbhay Choubey <nirbhay@mariadb.com> | 2015-12-19 14:24:38 -0500 |
| commit | dad555a09c8d590132c77c192a18d7fc1f8fe91e (patch) | |
| tree | 38fb545e5df0a24333b8284c816f5bea95d19a03 /mysql-test | |
| parent | 18173ddfc4081407832d9a6703d1b8356b7defe9 (diff) | |
| parent | 90ea0145856338221803ebb9b446ed2a6e082412 (diff) | |
| download | mariadb-git-dad555a09c8d590132c77c192a18d7fc1f8fe91e.tar.gz | |
Merge tag 'mariadb-10.0.23' into 10.0-galera
Diffstat (limited to 'mysql-test')
120 files changed, 2147 insertions, 170 deletions
diff --git a/mysql-test/extra/binlog_tests/database.test b/mysql-test/extra/binlog_tests/database.test index cd0266434ff..82e8b396357 100644 --- a/mysql-test/extra/binlog_tests/database.test +++ b/mysql-test/extra/binlog_tests/database.test @@ -52,7 +52,7 @@ eval SELECT 'hello' INTO OUTFILE 'fake_file.$prefix'; # Use '/' instead of '\' in the error message. On windows platform, dir is # formed with '\'. ---replace_regex /\\testing_1\\*/\/testing_1\// /66/39/ +--replace_regex /\\testing_1\\*/\/testing_1\// /66/39/ /17/39/ /File exists/Directory not empty/ --error 1010 DROP DATABASE testing_1; let $wait_binlog_event= DROP TABLE IF EXIST; diff --git a/mysql-test/extra/rpl_tests/rpl_record_compare.test b/mysql-test/extra/rpl_tests/rpl_record_compare.test index 210aee025d0..b674b76dfdf 100644 --- a/mysql-test/extra/rpl_tests/rpl_record_compare.test +++ b/mysql-test/extra/rpl_tests/rpl_record_compare.test @@ -62,24 +62,3 @@ UPDATE t1 SET c1= 0; DROP TABLE t1; -- sync_slave_with_master -# -# BUG#11766865: 60091: RBR + NO PK + UPDATE NULL VALUE --> SLAVE BREAK WITH ERROR HA_ERR_END_OF_ -# - ---connection master ---source include/rpl_reset.inc ---connection master - ---eval CREATE TABLE t1 (c1 int(11) NOT NULL, c2 int(11) NOT NULL, c3 int(11) DEFAULT '-1') ENGINE=$engine DEFAULT CHARSET=latin1 - -INSERT INTO t1 VALUES (1,2,NULL); -UPDATE t1 SET c1=1, c2=2, c3=-1 WHERE c1=1 AND c2=2 AND ISNULL(c3); - ---sync_slave_with_master - ---let $diff_tables=master:test.t1, slave:test.t1 ---source include/diff_tables.inc - ---connection master -DROP TABLE t1; ---sync_slave_with_master diff --git a/mysql-test/extra/rpl_tests/rpl_row_basic.test b/mysql-test/extra/rpl_tests/rpl_row_basic.test index 8ce45505c46..da8787d22f2 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_basic.test +++ b/mysql-test/extra/rpl_tests/rpl_row_basic.test @@ -440,7 +440,7 @@ INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; # 1677 = ER_SLAVE_CONVERSION_FAILED ---let $slave_sql_errno= 1677 +--let $slave_sql_errno= 1677 --let $show_slave_sql_error= 1 --source include/wait_for_slave_sql_error.inc @@ -453,7 +453,7 @@ INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; # 1677 = ER_SLAVE_CONVERSION_FAILED ---let $slave_sql_errno= 1677 +--let $slave_sql_errno= 1677 --let $show_slave_sql_error= 1 --source include/wait_for_slave_sql_error.inc diff --git a/mysql-test/extra/rpl_tests/rpl_sv_relay_space.test b/mysql-test/extra/rpl_tests/rpl_sv_relay_space.test index 1687a233914..868b8e73d0a 100644 --- a/mysql-test/extra/rpl_tests/rpl_sv_relay_space.test +++ b/mysql-test/extra/rpl_tests/rpl_sv_relay_space.test @@ -15,7 +15,8 @@ eval CREATE TABLE t1 (name varchar(64), age smallint(3))ENGINE=$engine_type; INSERT INTO t1 SET name='Andy', age=31; INSERT INTO t1 SET name='Jacob', age=2; INSERT INTO t1 SET name='Caleb', age=1; -ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY; +ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY, + ALGORITHM=COPY; SELECT * FROM t1 ORDER BY id; sync_slave_with_master; SELECT * FROM t1 ORDER BY id; diff --git a/mysql-test/include/check_slave_param.inc b/mysql-test/include/check_slave_param.inc index 243e87a9b65..7f9971d63ea 100644 --- a/mysql-test/include/check_slave_param.inc +++ b/mysql-test/include/check_slave_param.inc @@ -28,7 +28,7 @@ if ($_param_value != $slave_param_value) { --source include/show_rpl_debug_info.inc --echo Wrong value for $slave_param. Expected '$slave_param_value', got '$_param_value' - --die Wrong value for slave parameter + --die Wrong value for slave parameter $slave_param } diff --git a/mysql-test/include/default_mysqld.cnf b/mysql-test/include/default_mysqld.cnf index 04321475691..7b84117639c 100644 --- a/mysql-test/include/default_mysqld.cnf +++ b/mysql-test/include/default_mysqld.cnf @@ -31,7 +31,6 @@ debug-no-sync # Retry bind as this may fail on busy server port-open-timeout=10 -bind-address=127.0.0.1 log-bin-trust-function-creators=1 key_buffer_size= 1M @@ -40,6 +39,9 @@ max_heap_table_size= 1M loose-aria-pagecache-buffer-size=8M loose-feedback-user-info= mysql-test +loose-feedback-debug-startup-interval=20 +loose-feedback-debug-first-interval=60 +loose-feedback-debug-interval=60 loose-innodb_data_file_path= ibdata1:12M:autoextend loose-innodb_buffer_pool_size= 8M diff --git a/mysql-test/include/show_binlog_events2.inc b/mysql-test/include/show_binlog_events2.inc index e72522dc272..b036709b81d 100644 --- a/mysql-test/include/show_binlog_events2.inc +++ b/mysql-test/include/show_binlog_events2.inc @@ -10,7 +10,8 @@ if ($binlog_file) { --let $_in_binlog_file=in '$binlog_file' } ---replace_result $_binlog_start <binlog_start> $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--let $_from_binlog_start=from $_binlog_start +--replace_result "$_from_binlog_start" "from <binlog_start>" $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_column 2 # 5 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /file_id=[0-9]+/file_id=#/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ --eval show binlog events $_in_binlog_file from $_binlog_start diff --git a/mysql-test/lib/My/ConfigFactory.pm b/mysql-test/lib/My/ConfigFactory.pm index 4e8507a5c4a..b8ce038f23e 100644 --- a/mysql-test/lib/My/ConfigFactory.pm +++ b/mysql-test/lib/My/ConfigFactory.pm @@ -169,6 +169,13 @@ sub fix_log { return "$dir/mysqld.log"; } +sub fix_bind_address { + if (IS_WINDOWS) { + return "*"; + } else { + return "127.0.0.1"; + } +} sub fix_log_slow_queries { my ($self, $config, $group_name, $group)= @_; my $dir= dirname($group->value('datadir')); @@ -251,6 +258,7 @@ my @mysqld_rules= { 'ssl-ca' => \&fix_ssl_ca }, { 'ssl-cert' => \&fix_ssl_server_cert }, { 'ssl-key' => \&fix_ssl_server_key }, + { 'bind-address' => \&fix_bind_address }, ); if (IS_WINDOWS) @@ -325,6 +333,7 @@ my @cluster_config_rules= # my @client_rules= ( + { 'character-sets-dir' => \&fix_charset_dir }, ); @@ -347,7 +356,6 @@ my @mysqltest_rules= # my @mysqlbinlog_rules= ( - { 'character-sets-dir' => \&fix_charset_dir }, ); diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 79dc19a9896..df832d54ec7 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -842,7 +842,7 @@ sub run_test_server ($$$) { redo; } - # Limit number of parallell NDB tests + # Limit number of parallel NDB tests if ($t->{ndb_test} and $num_ndb_tests >= $max_ndb){ #mtr_report("Skipping, num ndb is already at max, $num_ndb_tests"); next; @@ -6417,10 +6417,10 @@ Options to control what engine/variation to run: non-blocking-api Use the non-blocking client API compress Use the compressed protocol between client and server ssl Use ssl protocol between client and server - skip-ssl Dont start server with support for ssl connections + skip-ssl Don't start server with support for ssl connections vs-config Visual Studio configuration used to create executables (default: MTR_VS_CONFIG environment variable) - parallel=# How many parallell test should be run + parallel=# How many parallel test should be run defaults-file=<config template> Use fixed config template for all tests defaults-extra-file=<config template> Extra config template to add to @@ -6551,7 +6551,7 @@ Options for debugging the product up disks for heavily crashing server). Defaults to $opt_max_save_datadir, set to 0 for no limit. Set it's default with MTR_MAX_SAVE_DATADIR - max-test-fail Limit the number of test failurs before aborting + max-test-fail Limit the number of test failures before aborting the current test run. Defaults to $opt_max_test_fail, set to 0 for no limit. Set it's default with MTR_MAX_TEST_FAIL @@ -6598,7 +6598,7 @@ Misc options --mysqld (if any) wait-all If --start or --start-dirty option is used, wait for all servers to exit before finishing the process - fast Run as fast as possible, dont't wait for servers + fast Run as fast as possible, don't wait for servers to shutdown etc. force-restart Always restart servers between tests parallel=N Run tests in N parallel threads (default 1) @@ -6633,7 +6633,7 @@ Misc options actions. Disable facility with NUM=0. gcov Collect coverage information after the test. The result is a gcov file per source and header file. - gcov-src-dir=subdir Colllect coverage only within the given subdirectory. + gcov-src-dir=subdir Collect coverage only within the given subdirectory. For example, if you're only developing the SQL layer, it makes sense to use --gcov-src-dir=sql gprof Collect profiling information using gprof. diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index fde821b801d..088cd9e531d 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -2656,5 +2656,11 @@ t1 CREATE TABLE `t1` ( `c` char(32) AS (convert(cast(n as char), char)) PERSISTENT ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +set @@session.collation_server=filename; +create table t1(a enum('','')); +Warnings: +Note 1291 Column 'a' has duplicated value '' in ENUM +drop table t1; +set @@session.collation_server=default; create table t1; ERROR 42000: A table must have at least 1 column diff --git a/mysql-test/r/ctype_filename.result b/mysql-test/r/ctype_filename.result index acc32c7dedf..ac8df476137 100644 --- a/mysql-test/r/ctype_filename.result +++ b/mysql-test/r/ctype_filename.result @@ -11,3 +11,6 @@ create table com1 (a int); drop table com1; create table `clock$` (a int); drop table `clock$`; +select convert(convert(',' using filename) using binary); +convert(convert(',' using filename) using binary) +@002c diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 1f44d67661b..384703e1039 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -6055,6 +6055,33 @@ SELECT (SELECT CONCAT(a),1 FROM t1) <=> (SELECT CONCAT(a),1 FROM t2); ERROR 21000: Subquery returns more than 1 row DROP TABLE t1, t2; # +# MDEV-8630 Datetime value dropped in "INSERT ... SELECT ... ON DUPLICATE KEY" +# +SET NAMES utf8; +CREATE TABLE t1 (id2 int, ts timestamp); +INSERT INTO t1 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24'); +CREATE TABLE t2 AS SELECT +COALESCE(ts, 0) AS c0, +GREATEST(COALESCE(ts, 0), COALESCE(ts, 0)) AS c1, +GREATEST(CASE WHEN 1 THEN ts ELSE 0 END, CASE WHEN 1 THEN ts ELSE 0 END) AS c2, +GREATEST(IFNULL(ts,0), IFNULL(ts,0)) AS c3, +GREATEST(IF(1,ts,0), IF(1,ts,0)) AS c4 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c0` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT '', + `c1` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT '', + `c2` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT '', + `c3` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT '', + `c4` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +c0 c1 c2 c3 c4 +2012-06-11 15:17:34 2012-06-11 15:17:34 2012-06-11 15:17:34 2012-06-11 15:17:34 2012-06-11 15:17:34 +2012-06-11 15:18:24 2012-06-11 15:18:24 2012-06-11 15:18:24 2012-06-11 15:18:24 2012-06-11 15:18:24 +DROP TABLE t2, t1; +# # End of 5.5 tests # # diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index 62e3b1c7331..b0d28a81043 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -6,16 +6,16 @@ hex(COLUMN_CREATE(1, NULL AS char character set utf8)) 000000 select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8)); hex(COLUMN_CREATE(1, "afaf" AS char character set utf8)) -0001000100030861666166 +0001000100032161666166 select hex(COLUMN_CREATE(1, 1212 AS char character set utf8)); hex(COLUMN_CREATE(1, 1212 AS char character set utf8)) -0001000100030831323132 +0001000100032131323132 select hex(COLUMN_CREATE(1, 12.12 AS char character set utf8)); hex(COLUMN_CREATE(1, 12.12 AS char character set utf8)) -0001000100030831322E3132 +0001000100032131322E3132 select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8)); hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8)) -000100010003083939393939393939393939393939393939393939393939393939393939 +000100010003213939393939393939393939393939393939393939393939393939393939 select hex(COLUMN_CREATE(1, NULL AS unsigned int)); hex(COLUMN_CREATE(1, NULL AS unsigned int)) 000000 @@ -144,7 +144,7 @@ hex(COLUMN_CREATE(1, "afaf" AS char character set utf8, 6, "2011-04-05" AS date, 7, "- 0:45:49.000001" AS time, 8, "2011-04-05 0:45:49.000001" AS datetime)) -01080001000300020029000300380004004A0005008C000600AE000700C7000800F5000861666166BC0478093D0AD7A3703D284002028C0C85B60F010010B7000485B60F010010B70000 +01080001000300020029000300380004004A0005008C000600AE000700C7000800F5002161666166BC0478093D0AD7A3703D284002028C0C85B60F010010B7000485B60F010010B70000 explain extended select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8, 2, 1212 AS unsigned int, @@ -1693,10 +1693,10 @@ ERROR 22007: Illegal value used as argument of dynamic column function # select column_json(column_create("int", -1212 as int, "uint", 12334 as unsigned int, "decimal", "23.344" as decimal, "double", 1.23444e50 as double, "string", 'gdgd\\dhdjh"dhdhd' as char, "time", "0:45:49.000001" AS time, "datetime", "2011-04-05 0:45:49.000001" AS datetime, "date", "2011-04-05" AS date)); column_json(column_create("int", -1212 as int, "uint", 12334 as unsigned int, "decimal", "23.344" as decimal, "double", 1.23444e50 as double, "string", 'gdgd\\dhdjh"dhdhd' as char, "time", "0:45:49.000001" AS time, "datetime", "2011-04-05 0:45:49.000001" -{"int":-1212,"date":"2011-04-05","time":"00:45:49.000001","uint":12334,"double":"1.2e50","string":"gdgd\\dhdjh\"dhdhd","decimal":23.344,"datetime":"2011-04-05 00:45:49.000001"} +{"int":-1212,"date":"2011-04-05","time":"00:45:49.000001","uint":12334,"double":1.23444e50,"string":"gdgd\\dhdjh\"dhdhd","decimal":23.344,"datetime":"2011-04-05 00:45:49.000001"} select column_json(column_create(1, -1212 as int, 2, 12334 as unsigned int, 3, "23.344" as decimal, 4, 1.23444e50 as double, 5, 'gdgd\\dhdjh"dhdhd' as char, 6, "0:45:49.000001" AS time, 7, "2011-04-05 0:45:49.000001" AS datetime, 8, "2011-04-05" AS date)); column_json(column_create(1, -1212 as int, 2, 12334 as unsigned int, 3, "23.344" as decimal, 4, 1.23444e50 as double, 5, 'gdgd\\dhdjh"dhdhd' as char, 6, "0:45:49.000001" AS time, 7, "2011-04-05 0:45:49.000001" AS datetime, 8, "2011-04-05" AS date)) -{"1":-1212,"2":12334,"3":23.344,"4":"1.2e50","5":"gdgd\\dhdjh\"dhdhd","6":"00:45:49.000001","7":"2011-04-05 00:45:49.000001","8":"2011-04-05"} +{"1":-1212,"2":12334,"3":23.344,"4":1.23444e50,"5":"gdgd\\dhdjh\"dhdhd","6":"00:45:49.000001","7":"2011-04-05 00:45:49.000001","8":"2011-04-05"} # # CHECK test # @@ -1820,5 +1820,58 @@ SELECT COLUMN_JSON(COLUMN_CREATE('a',1,'b','1')); COLUMN_JSON(COLUMN_CREATE('a',1,'b','1')) {"a":1,"b":"1"} # +# MDEV-8401: COLUMN_CREATE(name, value as DOUBLE) results in string +# +SELECT COLUMN_JSON( +COLUMN_CREATE( +'one', 123.456, +'two', 123.456 as DOUBLE +) +); +COLUMN_JSON( +COLUMN_CREATE( +'one', 123.456, +'two', 123.456 as DOUBLE +) +) +{"one":123.456,"two":123.456} +# +# MDEV-8521: Drastic loss of precision in COLUMN_JSON() on DOUBLEs +# +select column_get(column_create('float', 1.23456789012345E+100 as double), 'float' as double); +column_get(column_create('float', 1.23456789012345E+100 as double), 'float' as double) +1.23456789012345e100 +select column_json(column_create('float', 1.23456789012345E+100 as double)); +column_json(column_create('float', 1.23456789012345E+100 as double)) +{"float":1.23456789012345e100} +select column_json(column_create('float', 1.23456789012345E+10 as double)); +column_json(column_create('float', 1.23456789012345E+10 as double)) +{"float":12345678901.2345} +# +# MDEV-9147: Character set is ignored in Dynamic Column for saved string +# +SET NAMES utf8; +SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET latin1), 1 AS CHAR CHARACTER SET utf8) AS a; +a +¢ +SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET utf8), 1 AS CHAR CHARACTER SET utf8) AS a; +a +¢ +# +# MDEV-9167: COLUMN_CHECK fails on valid decimal data +# +SELECT COLUMN_CHECK(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)); +COLUMN_CHECK(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) +1 +SELECT COLUMN_CHECK(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)); +COLUMN_CHECK(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) +1 +SELECT COLUMN_JSON(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)); +COLUMN_JSON(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) +{"a":0,"b":1} +SELECT COLUMN_JSON(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)); +COLUMN_JSON(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) +{"a":1,"b":1} +# # end of 10.0 tests # diff --git a/mysql-test/r/events_1.result b/mysql-test/r/events_1.result index 475a190d73d..e03ccf51d8b 100644 --- a/mysql-test/r/events_1.result +++ b/mysql-test/r/events_1.result @@ -114,8 +114,7 @@ create table t_event3 (a int, b float); drop event if exists event3; Warnings: Note 1305 Event event3 does not exist -create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20251010", interval 5 day) -comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); +create event event3 on schedule every 50 + 10 minute starts date_add(curdate(), interval 5 minute) ends date_add(curdate(), interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); select count(*) from t_event3; count(*) 0 diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 4c67ab5693a..5678f0f3968 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -393,3 +393,17 @@ Warnings: Note 1003 select NULL AS `a` from (select NULL AS `a` from `test`.`t1` where 0) `t` set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2; +# +# MDEV-7215: EXPLAIN REPLACE produces an error: +# Column count doesn't match value count +# +create table t1 (a int); +insert into t1 values (1); +create table t2 (b int, c int); +replace into t2 select 100, (select a from t1); +explain replace into t2 select 100, (select a from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 system NULL NULL NULL NULL 1 +drop table t1, t2; +# End of 10.1 tests diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 0cac910886b..8bf948e0d72 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -551,7 +551,7 @@ MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) DROP TABLE t1; CREATE TABLE t1(a TEXT); SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); -ERROR HY000: Incorrect arguments to AGAINST +ERROR HY000: Incorrect arguments to MATCH DROP TABLE t1; CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)); INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); diff --git a/mysql-test/r/lowercase_fs_off.result b/mysql-test/r/lowercase_fs_off.result index 9b819cf3843..d7e1b8c9e5b 100644 --- a/mysql-test/r/lowercase_fs_off.result +++ b/mysql-test/r/lowercase_fs_off.result @@ -63,3 +63,8 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; +create table t1 (a int); +create trigger t1_bi before insert on t1 for each row set new.a= 1; +show triggers like '%T1%'; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +drop table t1; diff --git a/mysql-test/r/mysql_plugin.result b/mysql-test/r/mysql_plugin.result index 636b039047e..0bcb47e4a10 100644 --- a/mysql-test/r/mysql_plugin.result +++ b/mysql-test/r/mysql_plugin.result @@ -102,7 +102,7 @@ ERROR: Missing --plugin_dir option. # Show the help. # mysql_plugin Ver V.V.VV Distrib XX.XX.XX -Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. +Copyright (c) 2011, 2015, Oracle and/or its affiliates. All rights reserved. Enable or disable plugins. diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 737f8b63783..9ba5344fad0 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -45,7 +45,7 @@ Phase 6/6: Running 'FLUSH PRIVILEGES' OK Run it again - should say already completed This installation of MySQL is already upgraded to VERSION, use --force if you still need to run mysql_upgrade -Force should run it regardless of wether it's been run before +Force should run it regardless of whether it has been run before Phase 1/6: Checking and upgrading mysql database Processing databases mysql @@ -393,6 +393,13 @@ test Phase 6/6: Running 'FLUSH PRIVILEGES' OK # +# Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR +# +Run mysql_upgrade with unauthorized access +Version check failed. Got the following error when calling the 'mysql' command line client +ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) +FATAL ERROR: Upgrade failed +# # MDEV-4332 Increase username length from 16 characters # MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names # diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index ee2b23428aa..1725291d4c6 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5317,3 +5317,29 @@ Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] For more options, use mysqldump --help +# +# MDEV-9001 - [PATCH] Fix DB name quoting in mysqldump --routine +# +CREATE DATABASE `a\"'``b`; +USE `a\"'``b`; +CREATE PROCEDURE p1() BEGIN END; +ALTER DATABASE `a\"'``b` COLLATE utf8_general_ci; +ALTER DATABASE `a\"'``b` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +BEGIN END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +ALTER DATABASE `a\"'``b` CHARACTER SET utf8 COLLATE utf8_general_ci ; +DROP DATABASE `a\"'``b`; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index f2ad42ebc8d..89fac898414 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -2100,6 +2100,19 @@ count(*) 40960 drop table t1; set names default; +create table t2 (a int, b int, c int, d int, key x(a, b)); +insert into t2 values (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), +(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); +insert into t2 select * from t2; +insert into t2 values (0, 0, 0, 0), (1, 1, 1, 1); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +select a, b from t2 where (a, b) in ((0, 0), (1, 1)); +a b +0 0 +1 1 +drop table t2; # # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 16b35448c50..0350e36eec9 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -2102,6 +2102,19 @@ count(*) 40960 drop table t1; set names default; +create table t2 (a int, b int, c int, d int, key x(a, b)); +insert into t2 values (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), +(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); +insert into t2 select * from t2; +insert into t2 values (0, 0, 0, 0), (1, 1, 1, 1); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +select a, b from t2 where (a, b) in ((0, 0), (1, 1)); +a b +0 0 +1 1 +drop table t2; # # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # diff --git a/mysql-test/r/show_row_order-9226.result b/mysql-test/r/show_row_order-9226.result new file mode 100644 index 00000000000..b8c8de647ef --- /dev/null +++ b/mysql-test/r/show_row_order-9226.result @@ -0,0 +1,80 @@ +create table test_table ( +column_number_1 enum('1','2') not null, +column_number_2 enum('1','2','3','4','5','6','7','8','9','10','11','12') not null, +column_number_3 varchar(10) not null, +column_number_4 varchar(10) not null, +column_number_5 enum( +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa01', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa02', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa03', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa04', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa05', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa06', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa07', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa08', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa09', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa10', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa11', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa13', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa14', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa15', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa16', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa17', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa18', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa19', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa20', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa21', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa22', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa23', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa24', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa25', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa26', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa27', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa28', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa29', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa30', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa31', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa32', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa33', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa34', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa35', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa36', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa37', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa38', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa39', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa40', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa41', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa42', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa43', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa44', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa45', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa46', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa47', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa48', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa49', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa50', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa51', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa52' + ) not null, +column_number_6 enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131') not null, +column_number_7 enum('1','2','3','4','5','6','7') not null, +column_number_8 enum('8') not null, +column_number_9 enum('9') not null, +column_number_10 varchar(10) not null, +column_number_11 enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49') not null +) default charset=utf8mb4; +show columns from test_table; +Field Type Null Key Default Extra +column_number_1 enum('1','2') NO NULL +column_number_2 enum('1','2','3','4','5','6','7','8','9','10','11','12') NO NULL +column_number_3 varchar(10) NO NULL +column_number_4 varchar(10) NO NULL +column_number_5 enum('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa01','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa02','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa03','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa04','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa05','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa06','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa07','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa08','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa09','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa10','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa11','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa13','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa14','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa15','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa16','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa17','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa18','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa19','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa20','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa21','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa22','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa23','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa24','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa25','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa26','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa27','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa28','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa29','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa30','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa31','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa32','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa33','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa34','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa35','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa36','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa37','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa38','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa39','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa40','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa41','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa42','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa43','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa44','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa45','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa46','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa47','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa48','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa49','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa50','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa51','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa52') NO NULL +column_number_6 enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131') NO NULL +column_number_7 enum('1','2','3','4','5','6','7') NO NULL +column_number_8 enum('8') NO NULL +column_number_9 enum('9') NO NULL +column_number_10 varchar(10) NO NULL +column_number_11 enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49') NO NULL +drop table test_table; diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index 285284596c4..fcced761283 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -421,4 +421,99 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where DROP TABLE t1,t2; +# +# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists +# +drop database if exists db1; +drop database if exists db1; +create database db1; +create database db2; +use db1; +# +# First, run the original testcase: +# +create table t1 (i int); +insert into t1 values (10),(20); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +db1.t1 analyze status Engine-independent statistics collected +db1.t1 analyze status OK +rename table t1 to db2.t1; +# Verify that stats in the old database are gone: +select * from mysql.column_stats where db_name='db1' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.table_stats where db_name='db1' and table_name='t1'; +db_name table_name cardinality +# Verify that stats are present in the new database: +select * from mysql.column_stats where db_name='db2' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t1 i 10 20 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.table_stats where db_name='db2' and table_name='t1'; +db_name table_name cardinality +db2 t1 2 +# +# Now, try with more than one column and with indexes: +# +use test; +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +use db1; +create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b)); +insert into t2 select a/10, a/2, a from test.t1; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +db1.t2 analyze status Engine-independent statistics collected +db1.t2 analyze status Table is already up to date +alter table t2 rename db2.t2; +# Verify that stats in the old database are gone: +select * from mysql.table_stats where db_name='db1' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db1' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db1' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Verify that stats are present in the new database: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +db2 t2 10 +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t2 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t2 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t2 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +db2 t2 IDX1 1 5.0000 +db2 t2 IDX2 1 5.0000 +db2 t2 IDX2 2 1.6667 +use db2; +# +# Now, rename within the same database and verify: +# +rename table t2 to t3; +# No stats under old name: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Stats under the new name: +select * from mysql.table_stats where db_name='db2' and table_name='t3'; +db_name table_name cardinality +db2 t3 10 +select * from mysql.column_stats where db_name='db2' and table_name='t3'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t3 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t3 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t3 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t3'; +db_name table_name index_name prefix_arity avg_frequency +db2 t3 IDX1 1 5.0000 +db2 t3 IDX2 1 5.0000 +db2 t3 IDX2 2 1.6667 +use test; +drop database db1; +drop database db2; +drop table t1; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 301c093ce9c..0e866755532 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -448,6 +448,101 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where DROP TABLE t1,t2; +# +# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists +# +drop database if exists db1; +drop database if exists db1; +create database db1; +create database db2; +use db1; +# +# First, run the original testcase: +# +create table t1 (i int); +insert into t1 values (10),(20); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +db1.t1 analyze status Engine-independent statistics collected +db1.t1 analyze status OK +rename table t1 to db2.t1; +# Verify that stats in the old database are gone: +select * from mysql.column_stats where db_name='db1' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.table_stats where db_name='db1' and table_name='t1'; +db_name table_name cardinality +# Verify that stats are present in the new database: +select * from mysql.column_stats where db_name='db2' and table_name='t1'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t1 i 10 20 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.table_stats where db_name='db2' and table_name='t1'; +db_name table_name cardinality +db2 t1 2 +# +# Now, try with more than one column and with indexes: +# +use test; +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +use db1; +create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b)); +insert into t2 select a/10, a/2, a from test.t1; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +db1.t2 analyze status Engine-independent statistics collected +db1.t2 analyze status OK +alter table t2 rename db2.t2; +# Verify that stats in the old database are gone: +select * from mysql.table_stats where db_name='db1' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db1' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db1' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Verify that stats are present in the new database: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +db2 t2 10 +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t2 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t2 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t2 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +db2 t2 IDX1 1 5.0000 +db2 t2 IDX2 1 5.0000 +db2 t2 IDX2 2 1.6667 +use db2; +# +# Now, rename within the same database and verify: +# +rename table t2 to t3; +# No stats under old name: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +db_name table_name cardinality +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +db_name table_name index_name prefix_arity avg_frequency +# Stats under the new name: +select * from mysql.table_stats where db_name='db2' and table_name='t3'; +db_name table_name cardinality +db2 t3 10 +select * from mysql.column_stats where db_name='db2' and table_name='t3'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +db2 t3 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL +db2 t3 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL +db2 t3 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL +select * from mysql.index_stats where db_name='db2' and table_name='t3'; +db_name table_name index_name prefix_arity avg_frequency +db2 t3 IDX1 1 5.0000 +db2 t3 IDX2 1 5.0000 +db2 t3 IDX2 2 1.6667 +use test; +drop database db1; +drop database db2; +drop table t1; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index a2d3d392f45..bd6a0849e3d 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1622,3 +1622,22 @@ test t2 id 1 1024 0.0000 8.0000 63 SINGLE_PREC_HB 03070B0F13171B1F23272B2F33373B set histogram_size=default; drop table t1, t2; set use_stat_tables=@save_use_stat_tables; +# +# Bug MDEV-7383: min/max value for a column not utf8 compatible +# +create table t1 (a varchar(100)) engine=MyISAM; +insert into t1 values(unhex('D879626AF872675F73E662F8')); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show warnings; +Level Code Message +select db_name, table_name, column_name, +HEX(min_value), HEX(max_value), +nulls_ratio, avg_frequency, +hist_size, hist_type, HEX(histogram) +FROM mysql.column_stats; +db_name table_name column_name HEX(min_value) HEX(max_value) nulls_ratio avg_frequency hist_size hist_type HEX(histogram) +test t1 a D879626AF872675F73E662F8 D879626AF872675F73E662F8 0.0000 1.0000 0 NULL NULL +drop table t1; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 4c106ae2e5a..53797e2a0a4 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -287,8 +287,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index 4c106ae2e5a..53797e2a0a4 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -287,8 +287,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index 4c106ae2e5a..53797e2a0a4 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -287,8 +287,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index 4c106ae2e5a..53797e2a0a4 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -287,8 +287,8 @@ column_stats CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, - `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, - `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, diff --git a/mysql-test/r/udf_notembedded.result b/mysql-test/r/udf_notembedded.result new file mode 100644 index 00000000000..3fdcdbbe9d3 --- /dev/null +++ b/mysql-test/r/udf_notembedded.result @@ -0,0 +1,6 @@ +create function sequence returns integer soname "UDF_EXAMPLE_LIB"; +create table t1 (n int key not null auto_increment, msg int as (sequence()) virtual); +select * from t1; +n msg +drop table t1; +drop function sequence; diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index db7c2380398..a0c35c6e0ca 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -619,6 +619,65 @@ Handler_update 5 ROLLBACK; DROP TABLE t1, t2; # +# MDEV-8938: Server Crash on Update with joins +# +CREATE TABLE `t1` ( +`name` varchar(255) NOT NULL, +`value` varchar(4095) DEFAULT NULL, +PRIMARY KEY (`name`) +); +UPDATE `t1` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `t1` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain"; +drop table t1; +CREATE TABLE `t1` ( +`name` varchar(255) NOT NULL, +`value` varchar(4095) DEFAULT NULL, +PRIMARY KEY (`name`) +); +create table t2 ( +`name` varchar(255) NOT NULL, +`value` varchar(4095) DEFAULT NULL, +PRIMARY KEY (`name`) +); +UPDATE t1 +SET value = (SELECT value FROM t2 WHERE `name`= t1.name) +WHERE value is null ; +drop table t1,t2; +# +#MDEV-8701: Crash on derived query +# +CREATE TABLE t1 ( +data_exit_entry_id int(11) NOT NULL, +data_entry_id int(11) NOT NULL, +data_entry_exit_id int(11) NOT NULL, +data_exit_entry_quantity double NOT NULL +) DEFAULT CHARSET=utf8; +CREATE TABLE t2 ( +data_entry_id int(11) NOT NULL, +data_entry_cost double NOT NULL, +data_entry_quantity double NOT NULL +) DEFAULT CHARSET=utf8; +create algorithm=temptable view v1 as SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost +FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id; +UPDATE t2 +SET data_entry_cost += ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost) +FROM +v1 AS query +WHERE data_entry_exit_id = t2.data_entry_id +) +); +UPDATE t2 +SET data_entry_cost += ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost) +FROM +( SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost +FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id) AS query +WHERE data_entry_exit_id = t2.data_entry_id +) +); +drop view v1; +drop table t1, t2; +# # MDEV-4410: update does not want to use a covering index, but select uses it. # create table t2(a int); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index f697f63ca76..aa67013757f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5477,6 +5477,39 @@ UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM v3 ); EXECUTE stmt; DROP TABLE t1, t2, t3; DROP VIEW v3; +# +# MDEV-8632: Segmentation fault on INSERT +# +CREATE TABLE `t1` ( +`id` int(10) unsigned NOT NULL, +`r` float NOT NULL, +PRIMARY KEY (`id`) +) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +create view v1 as select id, if(r=r,1,2) as d from t1; +create view v2 as +select id, +d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p +from v1; +insert into t1 (id, r) +select id,p from +( +select id, +d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p +from ( +select id, if(r=r,1,2) as d +from t1 +) a +) b +on duplicate key update r=p; +insert into t1 (id, r) +select id,p from v2 +on duplicate key update r=p; +prepare stmt from "insert into t1 (id, r) select id,p from v2 on duplicate key update r=p"; +execute stmt; +execute stmt; +deallocate prepare stmt; +drop view v1,v2; +drop table `t1`; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- @@ -5699,6 +5732,47 @@ idAlbum strAlbum strMusicBrainzAlbumID strArtists strGenres iYear strMoods strSt 1 strAlbum1 strMusicBrainzAlbumID1 strArtists1 strGenres1 2000 NULL NULL NULL NULL NULL NULL NULL NULL 0 0 album 1 1 strArtist1 strMusicBrainzArtistID 0 0 drop view v1,v2; drop table t1,t2,t3,t4; +# +# MDEV-8913: Derived queries with same column names as final +# projection causes issues when using Order By +# +create table t1 (field int); +insert into t1 values (10),(5),(3),(8),(20); +SELECT sq.f2 AS f1, sq.f1 AS f2 +FROM ( SELECT field AS f1, 1 AS f2 FROM t1) AS sq +ORDER BY sq.f1; +f1 f2 +1 3 +1 5 +1 8 +1 10 +1 20 +create view v1 as SELECT field AS f1, 1 AS f2 FROM t1; +SELECT sq.f2 AS f1, sq.f1 AS f2 +FROM v1 AS sq +ORDER BY sq.f1; +f1 f2 +1 3 +1 5 +1 8 +1 10 +1 20 +drop view v1; +create table t2 SELECT field AS f1, 1 AS f2 FROM t1; +SELECT +sq.f2 AS f1, +sq.f1 AS f2 +FROM t2 AS sq +ORDER BY sq.f1; +f1 f2 +1 3 +1 5 +1 8 +1 10 +1 20 +drop table t1, t2; +SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1; +ERROR 42S22: Unknown column 'SOME_GARBAGE.b.a' in 'field list' # ----------------------------------------------------------------- # -- End of 10.0 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/suite/federated/error_on_close-8313.result b/mysql-test/suite/federated/error_on_close-8313.result new file mode 100644 index 00000000000..93b0edb4e49 --- /dev/null +++ b/mysql-test/suite/federated/error_on_close-8313.result @@ -0,0 +1,27 @@ +CREATE DATABASE federated; +CREATE DATABASE federated; +connection slave; +create table t1 (foo int, bar int); +connection master; +create server 's1' foreign data wrapper 'mysql' options +(HOST 'localhost', +DATABASE 'test', +USER 'root', +PASSWORD '', +SOCKET 'SLAVE_MYSOCK'); +create table t1 (foo integer, bar integer) engine=federated +connection='s1'; +select * from t1; +foo bar +connection slave; +connection master; +drop table t1; +drop server s1; +connection slave; +drop table t1; +connection master; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; +connection slave; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/suite/federated/error_on_close-8313.test b/mysql-test/suite/federated/error_on_close-8313.test new file mode 100644 index 00000000000..b0b12861a66 --- /dev/null +++ b/mysql-test/suite/federated/error_on_close-8313.test @@ -0,0 +1,38 @@ +# +# MDEV-8313 Got an error writing communication packets +# +source include/federated.inc; + +enable_connect_log; + +connection slave; +create table t1 (foo int, bar int); + +connection master; + +--replace_result $SLAVE_MYSOCK SLAVE_MYSOCK +eval create server 's1' foreign data wrapper 'mysql' options + (HOST 'localhost', + DATABASE 'test', + USER 'root', + PASSWORD '', + SOCKET '$SLAVE_MYSOCK'); + + +eval create table t1 (foo integer, bar integer) engine=federated + connection='s1'; + +select * from t1; + +connection slave; +source include/restart_mysqld.inc; + +connection master; +drop table t1; +drop server s1; + +connection slave; +drop table t1; + +source include/federated_cleanup.inc; + diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index e55ffe55f01..a0617727a16 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -16,8 +16,8 @@ def mysql column_stats db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8 def mysql column_stats histogram 11 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) select,insert,update,references def mysql column_stats hist_size 9 NULL YES tinyint NULL NULL 3 0 NULL NULL NULL tinyint(3) unsigned select,insert,update,references def mysql column_stats hist_type 10 NULL YES enum 14 42 NULL NULL NULL utf8 utf8_bin enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') select,insert,update,references -def mysql column_stats max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references -def mysql column_stats min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references +def mysql column_stats max_value 5 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) select,insert,update,references +def mysql column_stats min_value 4 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) select,insert,update,references def mysql column_stats nulls_ratio 6 NULL YES decimal NULL NULL 12 4 NULL NULL NULL decimal(12,4) select,insert,update,references def mysql column_stats table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql db Alter_priv 13 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references @@ -330,8 +330,8 @@ NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql column_stats db_name varchar 64 192 utf8 utf8_bin varchar(64) 3.0000 mysql column_stats table_name varchar 64 192 utf8 utf8_bin varchar(64) 3.0000 mysql column_stats column_name varchar 64 192 utf8 utf8_bin varchar(64) -3.0000 mysql column_stats min_value varchar 255 765 utf8 utf8_bin varchar(255) -3.0000 mysql column_stats max_value varchar 255 765 utf8 utf8_bin varchar(255) +1.0000 mysql column_stats min_value varbinary 255 255 NULL NULL varbinary(255) +1.0000 mysql column_stats max_value varbinary 255 255 NULL NULL varbinary(255) NULL mysql column_stats nulls_ratio decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_length decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_frequency decimal NULL NULL NULL NULL decimal(12,4) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result index 44b3bbfef16..54f63a557e6 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result @@ -16,8 +16,8 @@ def mysql column_stats db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8 def mysql column_stats histogram 11 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) def mysql column_stats hist_size 9 NULL YES tinyint NULL NULL 3 0 NULL NULL NULL tinyint(3) unsigned def mysql column_stats hist_type 10 NULL YES enum 14 42 NULL NULL NULL utf8 utf8_bin enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') -def mysql column_stats max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) -def mysql column_stats min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) +def mysql column_stats max_value 5 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) +def mysql column_stats min_value 4 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) def mysql column_stats nulls_ratio 6 NULL YES decimal NULL NULL 12 4 NULL NULL NULL decimal(12,4) def mysql column_stats table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI def mysql db Alter_priv 13 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') @@ -330,8 +330,8 @@ NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql column_stats db_name varchar 64 192 utf8 utf8_bin varchar(64) 3.0000 mysql column_stats table_name varchar 64 192 utf8 utf8_bin varchar(64) 3.0000 mysql column_stats column_name varchar 64 192 utf8 utf8_bin varchar(64) -3.0000 mysql column_stats min_value varchar 255 765 utf8 utf8_bin varchar(255) -3.0000 mysql column_stats max_value varchar 255 765 utf8 utf8_bin varchar(255) +1.0000 mysql column_stats min_value varbinary 255 255 NULL NULL varbinary(255) +1.0000 mysql column_stats max_value varbinary 255 255 NULL NULL varbinary(255) NULL mysql column_stats nulls_ratio decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_length decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_frequency decimal NULL NULL NULL NULL decimal(12,4) diff --git a/mysql-test/suite/innodb/r/dropdb.result b/mysql-test/suite/innodb/r/dropdb.result new file mode 100644 index 00000000000..6b11b5e5205 --- /dev/null +++ b/mysql-test/suite/innodb/r/dropdb.result @@ -0,0 +1,9 @@ +# +# Bug #19929435 DROP DATABASE HANGS WITH MALFORMED TABLE +# +set session default_storage_engine=innodb; +create database `b`; +use `b`; +create table `#mysql50#q.q` select 1; +ERROR 42000: Incorrect table name '#mysql50#q.q' +drop database `b`; diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result index 5c341bd6b98..4f285b6f9be 100644 --- a/mysql-test/suite/innodb/r/innodb-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-autoinc.result @@ -568,7 +568,7 @@ Variable_name Value auto_increment_increment 65535 auto_increment_offset 65535 INSERT INTO t1 VALUES (NULL),(NULL); -ERROR 22003: Out of range value for column 'c1' at row 1 +ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t1; c1 1 @@ -642,6 +642,18 @@ PRIMARY KEY (m)) ENGINE = InnoDB; INSERT INTO t2 (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); +SELECT * FROM t2; +m n o +1 1 TRUE +2 1 FALSE +3 2 TRUE +4 2 FALSE +5 3 TRUE +6 3 FALSE +7 4 TRUE +8 4 FALSE +9 5 TRUE +10 5 FALSE SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( @@ -649,7 +661,7 @@ t2 CREATE TABLE `t2` ( `n` int(10) unsigned NOT NULL, `o` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`m`) -) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SHOW CREATE TABLE t1; Table Create Table diff --git a/mysql-test/suite/innodb/r/innodb-dict.result b/mysql-test/suite/innodb/r/innodb-dict.result new file mode 100644 index 00000000000..e3b2f0d5288 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-dict.result @@ -0,0 +1,40 @@ +CREATE TABLE t1 (D INT) ENGINE=innodb; +INSERT INTO t1 VALUES (10); +ALTER TABLE t1 MODIFY COLUMN d INT; +ALTER TABLE t1 ADD INDEX my_d (d); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` int(11) DEFAULT NULL, + KEY `my_d` (`d`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); +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; +EXPLAIN SELECT d FROM t1 WHERE d = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +EXPLAIN SELECT D FROM t1 WHERE D = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +ALTER TABLE t1 DROP INDEX my_d; +ALTER TABLE t1 MODIFY COLUMN D INT; +ALTER TABLE t1 ADD INDEX my_d (D); +EXPLAIN SELECT d FROM t1 WHERE d = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +EXPLAIN SELECT D FROM t1 WHERE D = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `D` int(11) DEFAULT NULL, + KEY `my_d` (`D`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result index a022f07936c..eddedfc3620 100644 --- a/mysql-test/suite/innodb/r/innodb-fk-warnings.result +++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result @@ -25,7 +25,7 @@ create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=i ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key a (a) references t1(a)) engine=innodb. +Warning 150 Create table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key a (a) references t1(a)) engine=innodb'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; @@ -42,7 +42,7 @@ alter table t2 add constraint b foreign key (b) references t2(b); ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key (b) references t2(b). +Warning 150 Alter table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key (b) references t2(b)'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t2, t1; @@ -51,7 +51,7 @@ alter table t1 add constraint c1 foreign key (f1) references t11(f1); ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1). +Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary near ' foreign key (f1) references t11(f1)'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; @@ -90,14 +90,14 @@ alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update se ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column f1 is defined as NOT NULL in foreign key (f1) references t1(f1) on update set null close to on update set null. +Warning 150 Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column 'f1' is defined as NOT NULL in ' foreign key (f1) references t1(f1) on update set null' near ' on update set null'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint create table t2(a int not null, foreign key(a) references t1(f1) on delete set null) engine=innodb; ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table `test`.`t2` with foreign key constraint failed. You have defined a SET NULL condition but column a is defined as NOT NULL in foreign key(a) references t1(f1) on delete set null) engine=innodb close to on delete set null) engine=innodb. +Warning 150 Create table `test`.`t2` with foreign key constraint failed. You have defined a SET NULL condition but column 'a' is defined as NOT NULL in 'foreign key(a) references t1(f1) on delete set null) engine=innodb' near ' on delete set null) engine=innodb'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; @@ -106,7 +106,7 @@ create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=inn ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column a does not mach referenced column f1 close to foreign key(a) references t1(f1)) engine=innodb +Warning 150 Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column 'a' does not mach referenced column 'f1' near 'foreign key(a) references t1(f1)) engine=innodb'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result index dee20d282f7..c916d665bf0 100644 --- a/mysql-test/suite/innodb/r/innodb-fk.result +++ b/mysql-test/suite/innodb/r/innodb-fk.result @@ -50,8 +50,8 @@ CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Create table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary close to FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE -) ENGINE=InnoDB. +Warning 150 Create table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary near ' FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE +) ENGINE=InnoDB'. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint CREATE TABLE t2 ( @@ -65,7 +65,7 @@ ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary close to FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE. +Warning 150 Alter table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary near ' FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE'. Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint drop table t2; diff --git a/mysql-test/suite/innodb/r/innodb-get-fk.result b/mysql-test/suite/innodb/r/innodb-get-fk.result new file mode 100644 index 00000000000..0dca82c2d10 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-get-fk.result @@ -0,0 +1,71 @@ +CREATE SCHEMA `repro`; +CREATE TABLE `repro`.`crew` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; +CREATE TABLE `repro`.`pilot` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; +CREATE TABLE `repro`.`crew_role_assigned` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`crew_id` int(11) NOT NULL, +`role_code` varchar(50) NOT NULL, +PRIMARY KEY (`id`), +KEY `fk_crewRoleAssigned_roleCode` (`role_code`), +CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB COMMENT="This is a comment about tables"; +# Restart mysqld --innodb_read_only_mode=1 +SHOW CREATE TABLE `repro`.`crew_role_assigned`; +Table Create Table +crew_role_assigned CREATE TABLE `crew_role_assigned` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `crew_id` int(11) NOT NULL, + `role_code` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_crewRoleAssigned_roleCode` (`role_code`), + KEY `fk_crewRoleAssigned_pilotId` (`crew_id`), + CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This is a comment about tables' +ALTER TABLE `repro`.`crew_role_assigned` COMMENT = "This is a new comment about tables"; +SHOW CREATE TABLE `repro`.`crew_role_assigned`; +Table Create Table +crew_role_assigned CREATE TABLE `crew_role_assigned` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `crew_id` int(11) NOT NULL, + `role_code` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_crewRoleAssigned_roleCode` (`role_code`), + KEY `fk_crewRoleAssigned_pilotId` (`crew_id`), + CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This is a new comment about tables' +# Restart mysqld --innodb_read_only_mode=1 +SHOW CREATE TABLE `repro`.`crew_role_assigned`; +Table Create Table +crew_role_assigned CREATE TABLE `crew_role_assigned` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `crew_id` int(11) NOT NULL, + `role_code` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_crewRoleAssigned_roleCode` (`role_code`), + KEY `fk_crewRoleAssigned_pilotId` (`crew_id`), + CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This is a new comment about tables' +DROP TABLE `repro`.`crew_role_assigned`; +DROP TABLE `repro`.`pilot`; +DROP TABLE `repro`.`crew`; +DROP SCHEMA `repro`; diff --git a/mysql-test/suite/innodb/r/innodb-stats-modified-counter.result b/mysql-test/suite/innodb/r/innodb-stats-modified-counter.result new file mode 100644 index 00000000000..db56c6ba81a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-stats-modified-counter.result @@ -0,0 +1,20 @@ +set global innodb_stats_auto_recalc=off; +CREATE TABLE t1 (i int) ENGINE=InnoDB; +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 0 0 +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 2 2 +DELETE FROM t1 WHERE i = 1; +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 1 3 +UPDATE t1 SET i = 4 WHERE i = 2; +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 1 4 +DROP TABLE t1; +set global innodb_stats_auto_recalc=default; diff --git a/mysql-test/suite/innodb/r/innodb_bug56947.result b/mysql-test/suite/innodb/r/innodb_bug56947.result index 54af78047dd..878450144a2 100644 --- a/mysql-test/suite/innodb/r/innodb_bug56947.result +++ b/mysql-test/suite/innodb/r/innodb_bug56947.result @@ -2,7 +2,7 @@ SET GLOBAL innodb_file_per_table=0; create table bug56947(a int not null) engine = innodb; SET DEBUG_DBUG='+d,ib_rebuild_cannot_rename'; alter table bug56947 add unique index (a); -ERROR HY000: Got error 11 "Resource temporarily unavailable" from storage engine InnoDB +ERROR HY000: Got error 11 "xxx" from storage engine InnoDB SET DEBUG_DBUG='-d,ib_rebuild_cannot_rename'; check table bug56947; Table Op Msg_type Msg_text diff --git a/mysql-test/suite/innodb/r/innodb_information_schema_tables.result b/mysql-test/suite/innodb/r/innodb_information_schema_tables.result new file mode 100644 index 00000000000..ea713ea6f6a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_information_schema_tables.result @@ -0,0 +1,2 @@ +CREATE TABLE t1 ENGINE=InnoDB AS SELECT * FROM mysql.help_topic; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/dropdb.test b/mysql-test/suite/innodb/t/dropdb.test new file mode 100644 index 00000000000..7f5ac809d7c --- /dev/null +++ b/mysql-test/suite/innodb/t/dropdb.test @@ -0,0 +1,12 @@ +--source include/have_innodb.inc + +--echo # +--echo # Bug #19929435 DROP DATABASE HANGS WITH MALFORMED TABLE +--echo # + +set session default_storage_engine=innodb; +create database `b`; +use `b`; +--error ER_WRONG_TABLE_NAME +create table `#mysql50#q.q` select 1; +drop database `b`; diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index 7d5a9be0777..3c75521060d 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -351,7 +351,7 @@ INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2 SELECT * FROM t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; SHOW VARIABLES LIKE "auto_inc%"; ---error 167 +--error 1467 INSERT INTO t1 VALUES (NULL),(NULL); SELECT * FROM t1; DROP TABLE t1; @@ -403,6 +403,7 @@ CREATE TABLE t2 ( INSERT INTO t2 (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); +SELECT * FROM t2; SHOW CREATE TABLE t2; INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SHOW CREATE TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-dict.test b/mysql-test/suite/innodb/t/innodb-dict.test new file mode 100644 index 00000000000..25a284569db --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-dict.test @@ -0,0 +1,31 @@ +--source include/have_innodb.inc + +# +# Fix MySQL Bug#20755615: InnoDB compares column names case sensitively, +# while according to Storage Engine API column names should be compared +# case insensitively. This can cause FRM and InnoDB data dictionary to +# go out of sync: +# + +CREATE TABLE t1 (D INT) ENGINE=innodb; +INSERT INTO t1 VALUES (10); +ALTER TABLE t1 MODIFY COLUMN d INT; +ALTER TABLE t1 ADD INDEX my_d (d); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); +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; +EXPLAIN SELECT d FROM t1 WHERE d = 5; +EXPLAIN SELECT D FROM t1 WHERE D = 5; +ALTER TABLE t1 DROP INDEX my_d; +ALTER TABLE t1 MODIFY COLUMN D INT; +ALTER TABLE t1 ADD INDEX my_d (D); +EXPLAIN SELECT d FROM t1 WHERE d = 5; +EXPLAIN SELECT D FROM t1 WHERE D = 5; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-get-fk.test b/mysql-test/suite/innodb/t/innodb-get-fk.test new file mode 100644 index 00000000000..4245bef289f --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-get-fk.test @@ -0,0 +1,61 @@ +-- source include/have_innodb.inc +# need to restart server +-- source include/not_embedded.inc +CREATE SCHEMA `repro`; + +CREATE TABLE `repro`.`crew` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; + +CREATE TABLE `repro`.`pilot` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; + +CREATE TABLE `repro`.`crew_role_assigned` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`crew_id` int(11) NOT NULL, +`role_code` varchar(50) NOT NULL, +PRIMARY KEY (`id`), +KEY `fk_crewRoleAssigned_roleCode` (`role_code`), +CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB COMMENT="This is a comment about tables"; + +--echo # Restart mysqld --innodb_read_only_mode=1 +-- let $restart_parameters=--innodb-read-only-mode=1 +-- source include/restart_mysqld.inc + +SHOW CREATE TABLE `repro`.`crew_role_assigned`; + +-- source include/restart_mysqld.inc + +ALTER TABLE `repro`.`crew_role_assigned` COMMENT = "This is a new comment about tables"; +SHOW CREATE TABLE `repro`.`crew_role_assigned`; + +--echo # Restart mysqld --innodb_read_only_mode=1 +-- let $restart_parameters=--innodb-read-only-mode=1 +-- source include/restart_mysqld.inc + +# +# Below create table should contain also fk definitions +# +SHOW CREATE TABLE `repro`.`crew_role_assigned`; + +-- source include/restart_mysqld.inc + +DROP TABLE `repro`.`crew_role_assigned`; +DROP TABLE `repro`.`pilot`; +DROP TABLE `repro`.`crew`; +DROP SCHEMA `repro`; diff --git a/mysql-test/suite/innodb/t/innodb-stats-modified-counter.opt b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.opt new file mode 100644 index 00000000000..3e5b41c6db9 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.opt @@ -0,0 +1 @@ +--loose-innodb-sys-tablestats diff --git a/mysql-test/suite/innodb/t/innodb-stats-modified-counter.test b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.test new file mode 100644 index 00000000000..b3e45002768 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.test @@ -0,0 +1,28 @@ +-- source include/have_innodb.inc + +# +# MDEV-8297: information_schema.innodb_sys_tablestats.modified_counter doesn't change on UPDATE +# + +set global innodb_stats_auto_recalc=off; + +CREATE TABLE t1 (i int) ENGINE=InnoDB; + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +DELETE FROM t1 WHERE i = 1; + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +UPDATE t1 SET i = 4 WHERE i = 2; + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +DROP TABLE t1; + +set global innodb_stats_auto_recalc=default; diff --git a/mysql-test/suite/innodb/t/innodb_bug56947.test b/mysql-test/suite/innodb/t/innodb_bug56947.test index 4cefeb391cf..7883cafe291 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56947.test +++ b/mysql-test/suite/innodb/t/innodb_bug56947.test @@ -8,6 +8,7 @@ SET GLOBAL innodb_file_per_table=0; create table bug56947(a int not null) engine = innodb; SET DEBUG_DBUG='+d,ib_rebuild_cannot_rename'; +--replace_regex /"[^"]*"/"xxx"/ --error ER_GET_ERRNO alter table bug56947 add unique index (a); SET DEBUG_DBUG='-d,ib_rebuild_cannot_rename'; diff --git a/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt b/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt new file mode 100644 index 00000000000..9f30d81ef9c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt @@ -0,0 +1,29 @@ +--loose-innodb_trx +--loose-innodb_locks +--loose-innodb_lock_waits +--loose-innodb_cmp +--loose-innodb_cmp_reset +--loose-innodb_cmp_per_index +--loose-innodb_cmp_per_index_reset +--loose-innodb_cmpmem +--loose-innodb_cmpmem_reset +--loose-innodb_buffer_page +--loose-innodb_buffer_page_lru +--loose-innodb_buffer_stats +--loose-innodb_sys_tables +--loose-innodb_sys_tablestats +--loose-innodb_sys_indexes +--loose-innodb_sys_columns +--loose-innodb_sys_fields +--loose-innodb_sys_foreign +--loose-innodb_sys_foreign_cols +--loose-innodb_changed_pages +--loose-innodb_rseg +--loose-innodb_undo_logs +--loose-innodb_sys_stats +--loose-innodb_table_stats +--loose-innodb_index_stats +--loose-innodb_admin_command +--loose-innodb_buffer_pool_pages +--loose-innodb_buffer_pool_pages_index +--loose-innodb_buffer_pool_pages_blob diff --git a/mysql-test/suite/innodb/t/innodb_information_schema_tables.test b/mysql-test/suite/innodb/t/innodb_information_schema_tables.test new file mode 100644 index 00000000000..15b3bf4f561 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_information_schema_tables.test @@ -0,0 +1,64 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc + +# +# MDEV-7762 InnoDB: Failing assertion: block->page.buf_fix_count > 0 in buf0buf.ic line 730 +# +# Make sure that all supported information_schema tables are readable +# (actual result sets are not important). +# +CREATE TABLE t1 ENGINE=InnoDB AS SELECT * FROM mysql.help_topic; + +--disable_query_log +--disable_result_log +BEGIN; +SELECT * FROM t1 FOR UPDATE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; +SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_RESET; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET; +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_RSEG; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_UNDO_LOGS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLE_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEX_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_ADMIN_COMMAND; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_BLOB; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES; +COMMIT; +--enable_query_log +--enable_result_log +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index dd8412c6651..1cdce29cdf9 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -498,7 +498,7 @@ MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) DROP TABLE t1; CREATE TABLE t1(a TEXT) ENGINE = InnoDB; SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); -ERROR HY000: Incorrect arguments to AGAINST +ERROR HY000: Incorrect arguments to MATCH DROP TABLE t1; CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result index 3668df4c2f0..470d06c341d 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result +++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result @@ -1,4 +1,4 @@ -CREATE TABLE `CC` ( +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -8,9 +8,9 @@ KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.CC'. This is deprecated and will be disallowed in a future release. -INSERT INTO `CC` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release. +INSERT INTO `t2` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -20,13 +20,13 @@ KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.C'. This is deprecated and will be disallowed in a future release. -INSERT INTO `C` VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. +INSERT INTO t1 VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); SELECT `pk` -FROM C OUTR +FROM t1 OUTR WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR OUTR .`date_nokey` ) ; pk 9 @@ -34,10 +34,10 @@ pk 5 6 SELECT `pk` -FROM C +FROM t1 WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR '2009-11-25' ) ; pk 2 @@ -62,9 +62,9 @@ Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' -DROP TABLE CC; -DROP TABLE C; -CREATE TABLE `CC` ( +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -77,9 +77,9 @@ KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.CC'. This is deprecated and will be disallowed in a future release. -INSERT INTO `CC` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release. +INSERT INTO `t2` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -92,17 +92,17 @@ KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.C'. This is deprecated and will be disallowed in a future release. -INSERT INTO `C` VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. +INSERT INTO t1 VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); SELECT OUTR . `pk` AS X -FROM C AS OUTR +FROM t1 AS OUTR WHERE OUTR . `pk` IN ( SELECT INNR . `int_key` AS Y -FROM CC AS INNR +FROM t2 AS INNR WHERE INNR . `date_nokey` < INNR . `datetime_nokey` XOR OUTR . `date_nokey` BETWEEN '2004-07-10' AND '2009-11-25' ORDER BY INNR . `int_nokey` ) AND ( OUTR . `datetime_key` BETWEEN '2000-05-25' AND '2004-08-07' OR OUTR . `datetime_nokey` = '2007-10-24' ) ORDER BY OUTR . `int_key` , OUTR . `pk`; X 9 -DROP TABLE CC; -DROP TABLE C; +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test b/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test index aab93a72725..f8c6b9104fb 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test +++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test @@ -1,4 +1,4 @@ -# test for BUG#45221 "Query "SELECT pk FROM C WHERE pk IN (SELECT +# test for BUG#45221 "Query "SELECT pk FROM t1 WHERE pk IN (SELECT # int_key)" failing" --source include/have_debug.inc @@ -13,7 +13,7 @@ #/* Begin test case for query 0 */ -CREATE TABLE `CC` ( +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -22,8 +22,8 @@ CREATE TABLE `CC` ( KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; -INSERT INTO `CC` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +INSERT INTO `t2` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -32,29 +32,29 @@ CREATE TABLE `C` ( KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; -INSERT INTO `C` VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); +INSERT INTO t1 VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); SELECT `pk` -FROM C OUTR +FROM t1 OUTR WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR OUTR .`date_nokey` ) ; SELECT `pk` -FROM C +FROM t1 WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR '2009-11-25' ) ; -DROP TABLE CC; -DROP TABLE C; +DROP TABLE t2; +DROP TABLE t1; #/* End of test case for query 0 */ #/* Begin test case for query 1 */ -CREATE TABLE `CC` ( +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -66,8 +66,8 @@ CREATE TABLE `CC` ( KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; -INSERT INTO `CC` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +INSERT INTO `t2` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -79,19 +79,19 @@ CREATE TABLE `C` ( KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; -INSERT INTO `C` VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); +INSERT INTO t1 VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); SELECT OUTR . `pk` AS X -FROM C AS OUTR +FROM t1 AS OUTR WHERE OUTR . `pk` IN ( SELECT INNR . `int_key` AS Y -FROM CC AS INNR +FROM t2 AS INNR WHERE INNR . `date_nokey` < INNR . `datetime_nokey` XOR OUTR . `date_nokey` BETWEEN '2004-07-10' AND '2009-11-25' ORDER BY INNR . `int_nokey` ) AND ( OUTR . `datetime_key` BETWEEN '2000-05-25' AND '2004-08-07' OR OUTR . `datetime_nokey` = '2007-10-24' ) ORDER BY OUTR . `int_key` , OUTR . `pk`; -DROP TABLE CC; -DROP TABLE C; +DROP TABLE t2; +DROP TABLE t1; #/* End of test case for query 1 */ diff --git a/mysql-test/suite/parts/inc/partition_binary.inc b/mysql-test/suite/parts/inc/partition_binary.inc index 08db71b6483..21e9635c036 100644 --- a/mysql-test/suite/parts/inc/partition_binary.inc +++ b/mysql-test/suite/parts/inc/partition_binary.inc @@ -8,11 +8,13 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',255)), ('b'), (repeat('a',128)), (repeat('b',64)); +--sorted_result select hex(a) from t1; select a from t1 where substr(a,1,2)='b\0'; update t1 set a='cc' where substr(a,1,2)= 'b\0'; select a from t1 where substr(a,1,1)='c'; delete from t1 where substr(a,1,2)='cc'; +--sorted_result select hex(a) from t1; drop table t1; @@ -33,6 +35,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select hex(a) from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_char.inc b/mysql-test/suite/parts/inc/partition_char.inc index 0fa28370c83..d28ed3158c5 100644 --- a/mysql-test/suite/parts/inc/partition_char.inc +++ b/mysql-test/suite/parts/inc/partition_char.inc @@ -8,10 +8,12 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',255)), ('b'), (repeat('a',128)), (repeat('b',64)); +--sorted_result select * from t1; select * from t1 where a='b'; update t1 set a='bb' where a='b'; delete from t1 where a='bb'; +--sorted_result select * from t1; drop table t1; @@ -32,6 +34,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_enum.inc b/mysql-test/suite/parts/inc/partition_enum.inc index a545d7fd8c7..87e88983055 100644 --- a/mysql-test/suite/parts/inc/partition_enum.inc +++ b/mysql-test/suite/parts/inc/partition_enum.inc @@ -8,11 +8,13 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values ('A'),('D'),('L'),('G'); +--sorted_result select * from t1; select * from t1 where a='A'; update t1 set a='E' where a='L'; select * from t1; delete from t1 where a='E'; +--sorted_result select * from t1; drop table t1; @@ -37,6 +39,7 @@ commit; --enable_query_log insert into t2 values ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0'); select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_varbinary.inc b/mysql-test/suite/parts/inc/partition_varbinary.inc index 04ba66d87ea..292407cd525 100644 --- a/mysql-test/suite/parts/inc/partition_varbinary.inc +++ b/mysql-test/suite/parts/inc/partition_varbinary.inc @@ -8,10 +8,12 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',767)), ('b'), (repeat('a',500)), (repeat('b',64)); +--sorted_result select * from t1; select * from t1 where a='b'; update t1 set a='bb' where a='b'; delete from t1 where a='bb'; +--sorted_result select * from t1; drop table t1; @@ -32,6 +34,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_varchar.inc b/mysql-test/suite/parts/inc/partition_varchar.inc index 33c61034a52..257c122ddf2 100644 --- a/mysql-test/suite/parts/inc/partition_varchar.inc +++ b/mysql-test/suite/parts/inc/partition_varchar.inc @@ -8,10 +8,12 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',767)), ('b'), (repeat('a',500)), (repeat('b',64)); +--sorted_result select * from t1; select * from t1 where a='b'; update t1 set a='bb' where a='b'; delete from t1 where a='bb'; +--sorted_result select * from t1; drop table t1; @@ -32,6 +34,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/r/partition_char_innodb.result b/mysql-test/suite/parts/r/partition_char_innodb.result Binary files differindex 6f4a107f840..bb99e89b022 100644 --- a/mysql-test/suite/parts/r/partition_char_innodb.result +++ b/mysql-test/suite/parts/r/partition_char_innodb.result diff --git a/mysql-test/suite/parts/r/partition_char_myisam.result b/mysql-test/suite/parts/r/partition_char_myisam.result Binary files differindex 913a874b646..9936f3ec0f4 100644 --- a/mysql-test/suite/parts/r/partition_char_myisam.result +++ b/mysql-test/suite/parts/r/partition_char_myisam.result diff --git a/mysql-test/suite/perfschema/include/have_timer_cycle.inc b/mysql-test/suite/perfschema/include/have_timer_cycle.inc new file mode 100644 index 00000000000..b801ea256d6 --- /dev/null +++ b/mysql-test/suite/perfschema/include/have_timer_cycle.inc @@ -0,0 +1,4 @@ +if (!`SELECT count(*) FROM performance_schema.performance_timers WHERE timer_name='CYCLE' AND timer_frequency IS NOT NULL`) +{ + Skip Need performance timer CYCLE; +} diff --git a/mysql-test/suite/perfschema/r/misc.result b/mysql-test/suite/perfschema/r/misc.result index 433d793909f..2adf2cba851 100644 --- a/mysql-test/suite/perfschema/r/misc.result +++ b/mysql-test/suite/perfschema/r/misc.result @@ -107,3 +107,14 @@ select mysql_errno, returned_sqlstate, message_text, errors, warnings from performance_schema.events_statements_history_long where errors > 0; mysql_errno returned_sqlstate message_text errors warnings 1146 42S02 Table 'test.t1' doesn't exist 1 0 +use performance_schema; +truncate performance_schema.events_statements_history; +select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS A; +A +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +select _utf8mb4 'ваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑ' as B; +B +васвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвас +select count(*) from events_statements_history where sql_text like "%..."; +count(*) +2 diff --git a/mysql-test/suite/perfschema/r/privilege.result b/mysql-test/suite/perfschema/r/privilege.result index 49d72fc499f..c5942273057 100644 --- a/mysql-test/suite/perfschema/r/privilege.result +++ b/mysql-test/suite/perfschema/r/privilege.result @@ -554,7 +554,7 @@ ERROR 42000: DROP command denied to user 'pfs_user_4'@'localhost' for table 'eve # # Grant access to change tables with the root account GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4; -GRANT UPDATE ON performance_schema.setup_timers TO pfs_user_4; +GRANT UPDATE, SELECT ON performance_schema.setup_timers TO pfs_user_4; GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4; @@ -565,7 +565,7 @@ UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE 'wait/synch/mutex/%' OR name LIKE 'wait/synch/rwlock/%'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; -UPDATE performance_schema.setup_timers SET timer_name = 'TICK'; +UPDATE performance_schema.setup_timers SET timer_name = 'TICK' WHERE name <> "wait"; TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; TRUNCATE TABLE performance_schema.events_waits_current; @@ -576,6 +576,5 @@ flush privileges; UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle"; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; diff --git a/mysql-test/suite/perfschema/r/stage_mdl_global.result b/mysql-test/suite/perfschema/r/stage_mdl_global.result index 1a6f51a4acc..fdcaafa6201 100644 --- a/mysql-test/suite/perfschema/r/stage_mdl_global.result +++ b/mysql-test/suite/perfschema/r/stage_mdl_global.result @@ -6,6 +6,7 @@ user1 statement/sql/flush flush tables with read lock username event_name nesting_event_type username event_name nesting_event_type user1 stage/sql/init STATEMENT +user1 stage/sql/init STATEMENT user1 stage/sql/query end STATEMENT user1 stage/sql/closing tables STATEMENT user1 stage/sql/freeing items STATEMENT diff --git a/mysql-test/suite/perfschema/t/dml_performance_timers.test b/mysql-test/suite/perfschema/t/dml_performance_timers.test index 2ec37fbe7e9..587c54144aa 100644 --- a/mysql-test/suite/perfschema/t/dml_performance_timers.test +++ b/mysql-test/suite/perfschema/t/dml_performance_timers.test @@ -2,6 +2,7 @@ --source include/not_embedded.inc --source include/have_perfschema.inc +--source include/have_timer_cycle.inc --replace_column 2 <frequency> 3 <resolution> 4 <overhead> select * from performance_schema.performance_timers; diff --git a/mysql-test/suite/perfschema/t/dml_setup_timers.test b/mysql-test/suite/perfschema/t/dml_setup_timers.test index 641ac90b1c5..bd8822d2b19 100644 --- a/mysql-test/suite/perfschema/t/dml_setup_timers.test +++ b/mysql-test/suite/perfschema/t/dml_setup_timers.test @@ -2,6 +2,7 @@ --source include/not_embedded.inc --source include/have_perfschema.inc +--source include/have_timer_cycle.inc # Set to a known state update performance_schema.setup_timers diff --git a/mysql-test/suite/perfschema/t/misc.test b/mysql-test/suite/perfschema/t/misc.test index 00b175ec391..bf3e8afffdc 100644 --- a/mysql-test/suite/perfschema/t/misc.test +++ b/mysql-test/suite/perfschema/t/misc.test @@ -4,6 +4,7 @@ --source include/not_embedded.inc --source include/have_perfschema.inc --source include/have_innodb.inc +--source include/no_protocol.inc # # Bug#12790483 OBJECTS_SUMMARY_GLOBAL_BY_TYPE AND RENAME TABLE @@ -189,3 +190,20 @@ select mysql_errno, returned_sqlstate, message_text, errors, warnings --echo select mysql_errno, returned_sqlstate, message_text, errors, warnings from performance_schema.events_statements_history_long where errors > 0; + +# +# Bug#20519832 - TRUNCATED SQL_TEXT values are not suffixed with '...' +# +# Verify that truncated SQL statements are suffixed with '...' + +use performance_schema; +truncate performance_schema.events_statements_history; + +# Should truncate at 1024 bytes (1024 characters) +select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS A; + +# Should truncate at 1024 bytes (487 characters) + +select _utf8mb4 'ваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑ' as B; + +select count(*) from events_statements_history where sql_text like "%..."; diff --git a/mysql-test/suite/perfschema/t/privilege.test b/mysql-test/suite/perfschema/t/privilege.test index 1864a34bcfe..926d90d6edc 100644 --- a/mysql-test/suite/perfschema/t/privilege.test +++ b/mysql-test/suite/perfschema/t/privilege.test @@ -291,7 +291,7 @@ TRUNCATE TABLE performance_schema.events_waits_current; --echo # Grant access to change tables with the root account GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4; -GRANT UPDATE ON performance_schema.setup_timers TO pfs_user_4; +GRANT UPDATE, SELECT ON performance_schema.setup_timers TO pfs_user_4; GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4; GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4; @@ -308,7 +308,11 @@ WHERE name LIKE 'wait/synch/mutex/%' UPDATE performance_schema.setup_consumers SET enabled = 'YES'; -UPDATE performance_schema.setup_timers SET timer_name = 'TICK'; +# We do not touch "wait", to avoid restoring it at the end of the test, +# as its default value initialized at server startup is ambiguous: +# it can be CYCLE or NANOSECOND depending on platform + +UPDATE performance_schema.setup_timers SET timer_name = 'TICK' WHERE name <> "wait"; TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; @@ -324,8 +328,9 @@ flush privileges; UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +# Restore the default values for the timers that we changed. +# Note, we did not touch "wait", see above. UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle"; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; diff --git a/mysql-test/suite/plugins/r/feedback_plugin_install.result b/mysql-test/suite/plugins/r/feedback_plugin_install.result index 37d26b48501..c52fdb8f85b 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_install.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_install.result @@ -3,7 +3,8 @@ select plugin_status from information_schema.plugins where plugin_name='feedback plugin_status ACTIVE select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' + and variable_name not like '%debug%'; VARIABLE_NAME VARIABLE_VALUE FEEDBACK used 1 FEEDBACK version 1.1 diff --git a/mysql-test/suite/plugins/r/feedback_plugin_load.result b/mysql-test/suite/plugins/r/feedback_plugin_load.result index fa8724c321d..58507036af2 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_load.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_load.result @@ -6,7 +6,8 @@ SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback wher variable_value = @feedback_used + 1 1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; VARIABLE_NAME VARIABLE_VALUE FEEDBACK version 1.1 FEEDBACK_SEND_RETRY_WAIT 60 diff --git a/mysql-test/suite/plugins/r/feedback_plugin_send.result b/mysql-test/suite/plugins/r/feedback_plugin_send.result index c4ad3717a16..8f3f33076da 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_send.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_send.result @@ -6,7 +6,8 @@ SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback wher variable_value = @feedback_used + 1 1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; VARIABLE_NAME VARIABLE_VALUE FEEDBACK version 1.1 FEEDBACK_SEND_RETRY_WAIT 60 @@ -22,7 +23,6 @@ VARIABLE_VALUE>0 VARIABLE_NAME 1 Collation used latin1_swedish_ci 1 Collation used utf8_bin 1 Collation used utf8_general_ci -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' +set global sql_mode=ONLY_FULL_GROUP_BY; +6: feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent +6: feedback plugin: server replied 'ok' diff --git a/mysql-test/suite/plugins/t/feedback_plugin_install.test b/mysql-test/suite/plugins/t/feedback_plugin_install.test index 81343c436c3..559dcebfc05 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_install.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_install.test @@ -10,6 +10,8 @@ select plugin_status from information_schema.plugins where plugin_name='feedback --replace_result https http --sorted_result select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' + and variable_name not like '%debug%'; + uninstall plugin feedback; diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test index f2f8c1f97a7..8b4aee28362 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_load.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -24,7 +24,8 @@ SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback wher --replace_result https http --sorted_result select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; # Embedded server does not use the table mysqld.user and thus # does not automatically use latin1_bin on startup. Use it manually. diff --git a/mysql-test/suite/plugins/t/feedback_plugin_send.test b/mysql-test/suite/plugins/t/feedback_plugin_send.test index 31542c33482..b28f9d4cb38 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_send.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_send.test @@ -13,7 +13,11 @@ if (!$MTR_FEEDBACK_PLUGIN) { # Let's wait, and hope that mtr is started with --parallel and # is doing some work in other workers. # -sleep 310; + +sleep 100; +set global sql_mode=ONLY_FULL_GROUP_BY; +sleep 210; + # The test expects that the plugin will send a report at least 2 times, # now (5 min after loading) and on server shutdown which happens below. @@ -25,20 +29,15 @@ sleep 310; --let $shutdown_timeout= 60 source include/restart_mysqld.inc; -replace_result https http; +replace_result https http 2 6; perl; $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; open(LOG, '<', $log_error) or die "open(< $log_error): $!"; - # Get the first few rows (as there may be different number rows in the log) - $i= 0; - while ($_=<LOG>) - { - if (/feedback plugin:.*/) - { - print "$&\n"; - break if ($i++ >= 3); - } + %logg=(); + while ($_=<LOG>) { + $logg{$&}++ if /feedback plugin:.*/; } + print "$logg{$_}: $_\n" for sort keys %logg; close LOG; EOF diff --git a/mysql-test/suite/rpl/r/rpl_parallel2.result b/mysql-test/suite/rpl/r/rpl_parallel2.result index de90bcd158f..f79661ee6fb 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel2.result +++ b/mysql-test/suite/rpl/r/rpl_parallel2.result @@ -29,8 +29,104 @@ include/start_slave.inc SELECT * FROM t1 WHERE a >= 10 ORDER BY a; a b 10 0 +*** MDEV-7818: Deadlock occurring with parallel replication and FTWRL *** +CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,0), (2,0), (3,0); +include/stop_slave.inc +SET @old_dbug= @@SESSION.debug_dbug; +SET @commit_id= 4242; +SET SESSION debug_dbug="+d,binlog_force_commit_id"; +BEGIN; +UPDATE t2 SET b=b+1 WHERE a=2; +COMMIT; +BEGIN; +INSERT INTO t2 VALUES (4,10); +COMMIT; +SET SESSION debug_dbug= @old_dbug; +INSERT INTO t2 VALUES (5,0); +INSERT INTO t2 VALUES (6,0); +INSERT INTO t2 VALUES (7,0); +INSERT INTO t2 VALUES (8,0); +INSERT INTO t2 VALUES (9,0); +INSERT INTO t2 VALUES (10,0); +INSERT INTO t2 VALUES (11,0); +INSERT INTO t2 VALUES (12,0); +INSERT INTO t2 VALUES (13,0); +INSERT INTO t2 VALUES (14,0); +INSERT INTO t2 VALUES (15,0); +INSERT INTO t2 VALUES (16,0); +INSERT INTO t2 VALUES (17,0); +INSERT INTO t2 VALUES (18,0); +INSERT INTO t2 VALUES (19,0); +BEGIN; +SELECT * FROM t2 WHERE a=2 FOR UPDATE; +a b +2 0 +include/start_slave.inc +FLUSH TABLES WITH READ LOCK; +COMMIT; +STOP SLAVE; +SELECT * FROM t2 ORDER BY a; +a b +1 0 +2 1 +3 0 +4 10 +5 0 +6 0 +7 0 +8 0 +9 0 +10 0 +11 0 +12 0 +13 0 +14 0 +15 0 +16 0 +17 0 +18 0 +19 0 +UNLOCK TABLES; +SELECT "after UNLOCK TABLES" as state; +state +after UNLOCK TABLES +SELECT "after reap of STOP SLAVE" as state; +state +after reap of STOP SLAVE +include/wait_for_slave_to_stop.inc +include/start_slave.inc +SELECT * FROM t2 ORDER BY a; +a b +1 0 +2 1 +3 0 +4 10 +5 0 +6 0 +7 0 +8 0 +9 0 +10 0 +11 0 +12 0 +13 0 +14 0 +15 0 +16 0 +17 0 +18 0 +19 0 +*** MDEV-8318: Assertion `!pool->busy' failed in pool_mark_busy(rpl_parallel_thread_pool*) on concurrent FTWRL *** +LOCK TABLE t2 WRITE; +FLUSH TABLES WITH READ LOCK; +FLUSH TABLES WITH READ LOCK; +KILL QUERY CID; +ERROR 70100: Query execution was interrupted +UNLOCK TABLES; +UNLOCK TABLES; include/stop_slave.inc SET GLOBAL slave_parallel_threads=@old_parallel_threads; include/start_slave.inc -DROP TABLE t1; +DROP TABLE t1, t2; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result b/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result index 4f10ac50f9a..ebce070190d 100644 --- a/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result @@ -7,7 +7,8 @@ CREATE TABLE t1 (name varchar(64), age smallint(3))ENGINE=InnoDB; INSERT INTO t1 SET name='Andy', age=31; INSERT INTO t1 SET name='Jacob', age=2; INSERT INTO t1 SET name='Caleb', age=1; -ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY; +ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY, +ALGORITHM=COPY; SELECT * FROM t1 ORDER BY id; name age id Andy 31 00000001 diff --git a/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result b/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result index 4c1a50b2f33..b2fc0f715ea 100644 --- a/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result @@ -7,7 +7,8 @@ CREATE TABLE t1 (name varchar(64), age smallint(3))ENGINE=MyISAM; INSERT INTO t1 SET name='Andy', age=31; INSERT INTO t1 SET name='Jacob', age=2; INSERT INTO t1 SET name='Caleb', age=1; -ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY; +ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY, +ALGORITHM=COPY; SELECT * FROM t1 ORDER BY id; name age id Andy 31 00000001 diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result index 70148afcacb..1035df2456c 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result @@ -493,13 +493,13 @@ include/diff_tables.inc [master:t4, slave:t4] [expecting slave to stop] INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' include/rpl_reset.inc [expecting slave to stop] INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' include/rpl_reset.inc [expecting slave to replicate correctly] diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result index 89557b554d0..0c12eb544a5 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result @@ -496,13 +496,13 @@ include/diff_tables.inc [master:t4, slave:t4] [expecting slave to stop] INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' include/rpl_reset.inc [expecting slave to stop] INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' include/rpl_reset.inc [expecting slave to replicate correctly] diff --git a/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result b/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result index 523564a222e..d9ebb52493b 100644 --- a/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result @@ -25,10 +25,4 @@ INSERT INTO t1(c1) VALUES (NULL); UPDATE t1 SET c1= 0; include/diff_tables.inc [master:t1, slave:t1] DROP TABLE t1; -include/rpl_reset.inc -CREATE TABLE t1 (c1 int(11) NOT NULL, c2 int(11) NOT NULL, c3 int(11) DEFAULT '-1') ENGINE=InnoDB DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (1,2,NULL); -UPDATE t1 SET c1=1, c2=2, c3=-1 WHERE c1=1 AND c2=2 AND ISNULL(c3); -include/diff_tables.inc [master:test.t1, slave:test.t1] -DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result b/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result index 4dc7c0bc7a3..1ec98a81a8b 100644 --- a/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result @@ -34,10 +34,4 @@ INSERT INTO t1(c1) VALUES (NULL); UPDATE t1 SET c1= 0; include/diff_tables.inc [master:t1, slave:t1] DROP TABLE t1; -include/rpl_reset.inc -CREATE TABLE t1 (c1 int(11) NOT NULL, c2 int(11) NOT NULL, c3 int(11) DEFAULT '-1') ENGINE=MyISAM DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (1,2,NULL); -UPDATE t1 SET c1=1, c2=2, c3=-1 WHERE c1=1 AND c2=2 AND ISNULL(c3); -include/diff_tables.inc [master:test.t1, slave:test.t1] -DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_stop_slave.result b/mysql-test/suite/rpl/r/rpl_start_stop_slave.result index 7344acec4ec..b6cbdc9686b 100644 --- a/mysql-test/suite/rpl/r/rpl_start_stop_slave.result +++ b/mysql-test/suite/rpl/r/rpl_start_stop_slave.result @@ -8,4 +8,6 @@ set @time_after_kill := (select CURRENT_TIMESTAMP); [Time after the query] [Killing of the slave IO thread was successful] START SLAVE IO_THREAD; +create table t1 (a int primary key); +drop table t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test b/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test index e270d81a5e5..d3412c3cd52 100644 --- a/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test +++ b/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test @@ -3,6 +3,7 @@ # tables. Specifically when drop temporary tables and create temporary tables # are used. ################################################################################### +--source include/big_test.inc --source include/have_binlog_format_mixed.inc --source include/have_innodb.inc --source include/master-slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel2.test b/mysql-test/suite/rpl/t/rpl_parallel2.test index 47b0e87a6b6..3a9c801175f 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel2.test +++ b/mysql-test/suite/rpl/t/rpl_parallel2.test @@ -1,3 +1,5 @@ +--source include/have_debug.inc +--source include/have_innodb.inc --source include/have_binlog_format_statement.inc --let $rpl_topology=1->2 --source include/rpl_init.inc @@ -78,13 +80,148 @@ SET GLOBAL sql_slave_skip_counter= 1; SELECT * FROM t1 WHERE a >= 10 ORDER BY a; -# Clean up +--echo *** MDEV-7818: Deadlock occurring with parallel replication and FTWRL *** + +--connection server_1 +CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,0), (2,0), (3,0); +--save_master_pos + +--connection server_2 +--sync_with_master +--source include/stop_slave.inc + +--connection server_1 +# Create a group commit with two transactions, will be used to provoke the +# problematic thread interaction with FTWRL on the slave. +SET @old_dbug= @@SESSION.debug_dbug; +SET @commit_id= 4242; +SET SESSION debug_dbug="+d,binlog_force_commit_id"; + +BEGIN; +UPDATE t2 SET b=b+1 WHERE a=2; +COMMIT; + +BEGIN; +INSERT INTO t2 VALUES (4,10); +COMMIT; + +SET SESSION debug_dbug= @old_dbug; + +INSERT INTO t2 VALUES (5,0); +INSERT INTO t2 VALUES (6,0); +INSERT INTO t2 VALUES (7,0); +INSERT INTO t2 VALUES (8,0); +INSERT INTO t2 VALUES (9,0); +INSERT INTO t2 VALUES (10,0); +INSERT INTO t2 VALUES (11,0); +INSERT INTO t2 VALUES (12,0); +INSERT INTO t2 VALUES (13,0); +INSERT INTO t2 VALUES (14,0); +INSERT INTO t2 VALUES (15,0); +INSERT INTO t2 VALUES (16,0); +INSERT INTO t2 VALUES (17,0); +INSERT INTO t2 VALUES (18,0); +INSERT INTO t2 VALUES (19,0); +--save_master_pos + +--connection server_2 + +--connect (s1, 127.0.0.1, root,, test, $SLAVE_MYPORT,) +# Block one transaction on a row lock. +BEGIN; +SELECT * FROM t2 WHERE a=2 FOR UPDATE; + +--connection server_2 + +# Wait for slave thread of the other transaction to have the commit lock. +--source include/start_slave.inc +--let $wait_condition= SELECT COUNT(*) > 0 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +--connect (s2, 127.0.0.1, root,, test, $SLAVE_MYPORT,) +send FLUSH TABLES WITH READ LOCK; +# The bug was that at this point we were deadlocked. +# The FTWRL command would wait forever for T2 to commit. +# T2 would wait for T1 to commit first, but T1 is waiting for +# the global read lock to be released. + +--connection s1 +# Release the lock that blocs T1 from replicating. +COMMIT; + +--connection s1 +send STOP SLAVE; + +--connection s2 +reap; + +--connection server_1 +SELECT * FROM t2 ORDER BY a; + +--connection s2 +UNLOCK TABLES; + +SELECT "after UNLOCK TABLES" as state; + +--connection s1 +reap; + +SELECT "after reap of STOP SLAVE" as state; + +--connection server_2 +--source include/wait_for_slave_to_stop.inc +--source include/start_slave.inc +--sync_with_master + +SELECT * FROM t2 ORDER BY a; + + + +--echo *** MDEV-8318: Assertion `!pool->busy' failed in pool_mark_busy(rpl_parallel_thread_pool*) on concurrent FTWRL *** + +--connection server_1 +LOCK TABLE t2 WRITE; + + +--connect (m1,localhost,root,,test) +--connection m1 +--let $cid=`SELECT CONNECTION_ID()` +send FLUSH TABLES WITH READ LOCK; + +--connect (m2,localhost,root,,test) +# We cannot force the race with DEBUG_SYNC, because the race does not +# exist after fixing the bug. At best we could force a debug sync to +# time out, which is effectively just a sleep. +# So just put a small sleep here; it is enough to trigger the bug in +# most run before the bug fix, and the code should work correctly +# however the thread scheduling happens. +--sleep 0.1 +send FLUSH TABLES WITH READ LOCK; + +--connection server_1 +--replace_result $cid CID +eval KILL QUERY $cid; + +--connection m1 +--error ER_QUERY_INTERRUPTED +reap; + +--connection server_1 +UNLOCK TABLES; + +--connection m2 +reap; +UNLOCK TABLES; + + +# Clean up. --connection server_2 --source include/stop_slave.inc SET GLOBAL slave_parallel_threads=@old_parallel_threads; --source include/start_slave.inc --connection server_1 -DROP TABLE t1; +DROP TABLE t1, t2; --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_stop_slave.test b/mysql-test/suite/rpl/t/rpl_start_stop_slave.test index ab388f3eebc..a38ac8fc6a1 100644 --- a/mysql-test/suite/rpl/t/rpl_start_stop_slave.test +++ b/mysql-test/suite/rpl/t/rpl_start_stop_slave.test @@ -53,5 +53,12 @@ if(`select TIMESTAMPDIFF(SECOND,@time_after_kill, @time_before_kill) > 60`) START SLAVE IO_THREAD; +# Ensure that the slave io thread started properly +connection master; +create table t1 (a int primary key); +sync_slave_with_master; +connection master; +drop table t1; + # End of test --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test b/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test index 6e3dabf6e32..7770fc77748 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test +++ b/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test @@ -3,6 +3,7 @@ # tables. Specifically when drop temporary tables and create temporary tables # are used. ################################################################################### +--source include/big_test.inc --source include/have_binlog_format_statement.inc --source include/master-slave.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/rpl/t/semisync_future-7591.test b/mysql-test/suite/rpl/t/semisync_future-7591.test index 772a36b5632..daf3d2f8571 100644 --- a/mysql-test/suite/rpl/t/semisync_future-7591.test +++ b/mysql-test/suite/rpl/t/semisync_future-7591.test @@ -27,5 +27,7 @@ reset slave; set global rpl_semi_sync_slave_enabled = OFF; --connection master drop table t1; +--sync_slave_with_master +--connection master set global rpl_semi_sync_master_enabled = OFF; --source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/all_vars.result b/mysql-test/suite/sys_vars/r/all_vars.result index 63f3ce2e439..1bd4e394f6a 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -10,7 +10,5 @@ there should be *no* long test name listed below: select distinct variable_name as `there should be *no* variables listed below:` from t2 left join t1 on variable_name=test_name where test_name is null; there should be *no* variables listed below: -max_digest_length -pfs_max_digest_length drop table t1; drop table t2; diff --git a/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_basic.result b/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_basic.result new file mode 100644 index 00000000000..51c72cfe791 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_basic.result @@ -0,0 +1,20 @@ +SET @orig = @@global.innodb_buffer_pool_dump_pct; +SELECT @orig; +@orig +100 +SET GLOBAL innodb_buffer_pool_dump_pct=3, GLOBAL innodb_buffer_pool_dump_now = ON; +SET GLOBAL innodb_buffer_pool_dump_pct=0; +SELECT @@global.innodb_buffer_pool_dump_pct; +@@global.innodb_buffer_pool_dump_pct +1 +SHOW WARNINGS; +Level Code Message +Warning 1292 Truncated incorrect innodb_buffer_pool_dump_pct value: '0' +SET GLOBAL innodb_buffer_pool_dump_pct=101; +SELECT @@global.innodb_buffer_pool_dump_pct; +@@global.innodb_buffer_pool_dump_pct +100 +SHOW WARNINGS; +Level Code Message +Warning 1292 Truncated incorrect innodb_buffer_pool_dump_pct value: '101' +SET GLOBAL innodb_buffer_pool_dump_pct=@orig; diff --git a/mysql-test/suite/sys_vars/r/max_digest_length_basic.result b/mysql-test/suite/sys_vars/r/max_digest_length_basic.result new file mode 100644 index 00000000000..1a733273614 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/max_digest_length_basic.result @@ -0,0 +1,23 @@ +select @@global.max_digest_length; +@@global.max_digest_length +1024 +select @@session.max_digest_length; +ERROR HY000: Variable 'max_digest_length' is a GLOBAL variable +show global variables like 'max_digest_length'; +Variable_name Value +max_digest_length 1024 +show session variables like 'max_digest_length'; +Variable_name Value +max_digest_length 1024 +select * from information_schema.global_variables +where variable_name='max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +MAX_DIGEST_LENGTH 1024 +select * from information_schema.session_variables +where variable_name='max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +MAX_DIGEST_LENGTH 1024 +set global max_digest_length=1; +ERROR HY000: Variable 'max_digest_length' is a read only variable +set session max_digest_length=1; +ERROR HY000: Variable 'max_digest_length' is a read only variable diff --git a/mysql-test/suite/sys_vars/r/pfs_max_digest_length_basic.result b/mysql-test/suite/sys_vars/r/pfs_max_digest_length_basic.result new file mode 100644 index 00000000000..12e48ee50ba --- /dev/null +++ b/mysql-test/suite/sys_vars/r/pfs_max_digest_length_basic.result @@ -0,0 +1,23 @@ +select @@global.performance_schema_max_digest_length; +@@global.performance_schema_max_digest_length +1024 +select @@session.performance_schema_max_digest_length; +ERROR HY000: Variable 'performance_schema_max_digest_length' is a GLOBAL variable +show global variables like 'performance_schema_max_digest_length'; +Variable_name Value +performance_schema_max_digest_length 1024 +show session variables like 'performance_schema_max_digest_length'; +Variable_name Value +performance_schema_max_digest_length 1024 +select * from information_schema.global_variables +where variable_name='performance_schema_max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH 1024 +select * from information_schema.session_variables +where variable_name='performance_schema_max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH 1024 +set global performance_schema_max_digest_length=1; +ERROR HY000: Variable 'performance_schema_max_digest_length' is a read only variable +set session performance_schema_max_digest_length=1; +ERROR HY000: Variable 'performance_schema_max_digest_length' is a read only variable diff --git a/mysql-test/suite/sys_vars/t/innodb_buffer_pool_dump_pct_basic.test b/mysql-test/suite/sys_vars/t/innodb_buffer_pool_dump_pct_basic.test new file mode 100644 index 00000000000..d2f5cb4a0de --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_buffer_pool_dump_pct_basic.test @@ -0,0 +1,34 @@ +# +# Basic test for innodb_buffer_pool_dump_pct +# + +-- source include/have_innodb.inc + +# Check the default value +SET @orig = @@global.innodb_buffer_pool_dump_pct; +SELECT @orig; + +# Do the dump +SET GLOBAL innodb_buffer_pool_dump_pct=3, GLOBAL innodb_buffer_pool_dump_now = ON; + +# Wait for the dump to complete +let $wait_condition = + SELECT SUBSTR(variable_value, 1, 33) = 'Buffer pool(s) dump completed at ' + FROM information_schema.global_status + WHERE LOWER(variable_name) = 'innodb_buffer_pool_dump_status'; +-- source include/wait_condition.inc + +# Confirm that the dump file has been created +-- let $file = `SELECT CONCAT(@@datadir, @@global.innodb_buffer_pool_filename)` +-- file_exists $file + +--disable_warnings +SET GLOBAL innodb_buffer_pool_dump_pct=0; +SELECT @@global.innodb_buffer_pool_dump_pct; +SHOW WARNINGS; +SET GLOBAL innodb_buffer_pool_dump_pct=101; +SELECT @@global.innodb_buffer_pool_dump_pct; +SHOW WARNINGS; +--enable_warnings + +SET GLOBAL innodb_buffer_pool_dump_pct=@orig; diff --git a/mysql-test/suite/sys_vars/t/max_digest_length_basic.test b/mysql-test/suite/sys_vars/t/max_digest_length_basic.test new file mode 100644 index 00000000000..b5e1d834d7e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/max_digest_length_basic.test @@ -0,0 +1,46 @@ +# Copyright (C) 2010 Sun Microsystems, Inc. +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +--source include/not_embedded.inc + +# +# Only global +# + +select @@global.max_digest_length; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.max_digest_length; + +show global variables like 'max_digest_length'; + +show session variables like 'max_digest_length'; + +select * from information_schema.global_variables + where variable_name='max_digest_length'; + +select * from information_schema.session_variables + where variable_name='max_digest_length'; + +# +# Read-only +# + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set global max_digest_length=1; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set session max_digest_length=1; + diff --git a/mysql-test/suite/sys_vars/t/pfs_max_digest_length-master_basic.opt b/mysql-test/suite/sys_vars/t/pfs_max_digest_length-master_basic.opt new file mode 100644 index 00000000000..a98eb86d2e6 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/pfs_max_digest_length-master_basic.opt @@ -0,0 +1,2 @@ +--loose-enable-performance-schema +--loose-performance-schema-max_digest_length=1024 diff --git a/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test b/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test new file mode 100644 index 00000000000..5c89dbda07f --- /dev/null +++ b/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test @@ -0,0 +1,47 @@ +# Copyright (C) 2010 Sun Microsystems, Inc. +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +--source include/not_embedded.inc +--source include/have_perfschema.inc + +# +# Only global +# + +select @@global.performance_schema_max_digest_length; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.performance_schema_max_digest_length; + +show global variables like 'performance_schema_max_digest_length'; + +show session variables like 'performance_schema_max_digest_length'; + +select * from information_schema.global_variables + where variable_name='performance_schema_max_digest_length'; + +select * from information_schema.session_variables + where variable_name='performance_schema_max_digest_length'; + +# +# Read-only +# + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set global performance_schema_max_digest_length=1; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set session performance_schema_max_digest_length=1; + diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index 8631789f15f..0aaed59ed6c 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -322,3 +322,11 @@ drop table t1; create table t1 (a int, b int as (b is null) virtual); ERROR HY000: A computed column cannot be based on a computed column # end of 5.3 tests +create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v1` varchar(255) AS (c1) PERSISTENT, + `c1` varchar(50) COLLATE latin1_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci +drop table t1; diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index a4c1fc06ce9..12f46e9b002 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -283,3 +283,10 @@ drop table t1; create table t1 (a int, b int as (b is null) virtual); --echo # end of 5.3 tests + +# +# MDEV-7655 SHOW CREATE TABLE returns invalid DDL when using virtual columns along with a table collation +# +create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci; +show create table t1; +drop table t1; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 4d57a5a110a..e30ea1b240c 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -2055,6 +2055,14 @@ show create table t1; drop table t1; # +# MDEV-7050: MySQL#74603 - Assertion `comma_length > 0' failed in mysql_prepare_create_table +# +set @@session.collation_server=filename; +create table t1(a enum('','')); +drop table t1; +set @@session.collation_server=default; + +# # MDEV-4880 Attempt to create a table without columns produces ER_ILLEGAL_HA instead of ER_TABLE_MUST_HAVE_COLUMNS # --error ER_TABLE_MUST_HAVE_COLUMNS diff --git a/mysql-test/t/ctype_filename.test b/mysql-test/t/ctype_filename.test index 436ccfc4f2e..4c501a8b826 100644 --- a/mysql-test/t/ctype_filename.test +++ b/mysql-test/t/ctype_filename.test @@ -19,3 +19,6 @@ drop table com1; create table `clock$` (a int); drop table `clock$`; + +select convert(convert(',' using filename) using binary); + diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index fcd19c112e9..f8b08142e0f 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1650,6 +1650,22 @@ INSERT INTO t2 VALUES ('aaa'); SELECT (SELECT CONCAT(a),1 FROM t1) <=> (SELECT CONCAT(a),1 FROM t2); DROP TABLE t1, t2; +--echo # +--echo # MDEV-8630 Datetime value dropped in "INSERT ... SELECT ... ON DUPLICATE KEY" +--echo # +SET NAMES utf8; +CREATE TABLE t1 (id2 int, ts timestamp); +INSERT INTO t1 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24'); +CREATE TABLE t2 AS SELECT + COALESCE(ts, 0) AS c0, + GREATEST(COALESCE(ts, 0), COALESCE(ts, 0)) AS c1, + GREATEST(CASE WHEN 1 THEN ts ELSE 0 END, CASE WHEN 1 THEN ts ELSE 0 END) AS c2, + GREATEST(IFNULL(ts,0), IFNULL(ts,0)) AS c3, + GREATEST(IF(1,ts,0), IF(1,ts,0)) AS c4 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2, t1; --echo # --echo # End of 5.5 tests diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test index ac55fec3e2b..03e2345ba1c 100644 --- a/mysql-test/t/dyncol.test +++ b/mysql-test/t/dyncol.test @@ -884,5 +884,42 @@ SELECT COLUMN_JSON(COLUMN_CREATE('a',0,'b','1')); SELECT COLUMN_JSON(COLUMN_CREATE('a',1,'b','1')); --echo # +--echo # MDEV-8401: COLUMN_CREATE(name, value as DOUBLE) results in string +--echo # +SELECT COLUMN_JSON( + COLUMN_CREATE( + 'one', 123.456, + 'two', 123.456 as DOUBLE + ) +); + +--echo # +--echo # MDEV-8521: Drastic loss of precision in COLUMN_JSON() on DOUBLEs +--echo # + +select column_get(column_create('float', 1.23456789012345E+100 as double), 'float' as double); +select column_json(column_create('float', 1.23456789012345E+100 as double)); +select column_json(column_create('float', 1.23456789012345E+10 as double)); + +--echo # +--echo # MDEV-9147: Character set is ignored in Dynamic Column for saved string +--echo # +SET NAMES utf8; +SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET latin1), 1 AS CHAR CHARACTER SET utf8) AS a; +SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET utf8), 1 AS CHAR CHARACTER SET utf8) AS a; + +--echo # +--echo # MDEV-9167: COLUMN_CHECK fails on valid decimal data +--echo # + +SELECT COLUMN_CHECK(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)); + +SELECT COLUMN_CHECK(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)); + +SELECT COLUMN_JSON(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)); + +SELECT COLUMN_JSON(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)); + +--echo # --echo # end of 10.0 tests --echo # diff --git a/mysql-test/t/events_1.test b/mysql-test/t/events_1.test index eecd11da5f6..bf5a356cee3 100644 --- a/mysql-test/t/events_1.test +++ b/mysql-test/t/events_1.test @@ -125,8 +125,7 @@ drop event existant; create table t_event3 (a int, b float); drop event if exists event3; -create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20251010", interval 5 day) - comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); +create event event3 on schedule every 50 + 10 minute starts date_add(curdate(), interval 5 minute) ends date_add(curdate(), interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); let $wait_condition=SELECT count(*)=0 from t_event3; --source include/wait_condition.inc select count(*) from t_event3; diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 9b9e65d7b51..d5be354c852 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -317,3 +317,19 @@ SELECT * FROM ( SELECT t1.a FROM t1,t2 WHERE t2.a = t1.a ) AS t; set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-7215: EXPLAIN REPLACE produces an error: +--echo # Column count doesn't match value count +--echo # +create table t1 (a int); +insert into t1 values (1); +create table t2 (b int, c int); + +replace into t2 select 100, (select a from t1); +explain replace into t2 select 100, (select a from t1); +#analyze replace into t2 select 100, (select a from t1); + +drop table t1, t2; + +--echo # End of 10.1 tests diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 1be0351f9bc..01989ef5367 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -102,3 +102,11 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; + +# +# MDEV-9014 SHOW TRIGGERS not case sensitive +# +create table t1 (a int); +create trigger t1_bi before insert on t1 for each row set new.a= 1; +show triggers like '%T1%'; +drop table t1; diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index a24ba0de2d9..2ef469ba7c5 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -20,7 +20,7 @@ file_exists $MYSQLD_DATADIR/mysql_upgrade_info; # It should have created a file in the MySQL Servers datadir file_exists $MYSQLD_DATADIR/mysql_upgrade_info; ---echo Force should run it regardless of wether it's been run before +--echo Force should run it regardless of whether it has been run before --exec $MYSQL_UPGRADE --force 2>&1 # It should have created a file in the MySQL Servers datadir @@ -131,6 +131,14 @@ let $MYSQLD_DATADIR= `select @@datadir`; --remove_file $MYSQLD_DATADIR/mysql_upgrade_info --echo # +--echo # Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR +--echo # + +--echo Run mysql_upgrade with unauthorized access +--error 1 +--exec $MYSQL_UPGRADE --skip-verbose --user=root --password=wrong_password 2>&1 + +--echo # --echo # MDEV-4332 Increase username length from 16 characters --echo # MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names --echo # diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 677a66ab4c8..8a9fd4ba91d 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2497,3 +2497,18 @@ DROP DATABASE db_20772273; --exec $MYSQL_DUMP --user=foo 2>&1 > $MYSQLTEST_VARDIR/tmp/bug6056.out --exec $MYSQL_DUMP --help > $MYSQLTEST_VARDIR/tmp/bug6056.out + +--echo # +--echo # MDEV-9001 - [PATCH] Fix DB name quoting in mysqldump --routine +--echo # +CREATE DATABASE `a\"'``b`; +USE `a\"'``b`; +CREATE PROCEDURE p1() BEGIN END; +ALTER DATABASE `a\"'``b` COLLATE utf8_general_ci; +--let shell_ready_db_name="a\\\\\\"'`b" +if (`select convert(@@version_compile_os using latin1) IN ("Win32","Win64","Windows") = 0`) +{ + --let shell_ready_db_name=a\\\\\\"\\'\\`b +} +--exec $MYSQL_DUMP --routines --compact $shell_ready_db_name +DROP DATABASE `a\"'``b`; diff --git a/mysql-test/t/partition_myisam.test b/mysql-test/t/partition_myisam.test index bce0c6f009c..d07637057e0 100644 --- a/mysql-test/t/partition_myisam.test +++ b/mysql-test/t/partition_myisam.test @@ -123,6 +123,7 @@ CHECK TABLE t1; SELECT * FROM t1; --echo # Note that it is currently impossible to drop a partitioned table --echo # without the .par file +--replace_result "Not owner" "Operation not permitted" --error ER_GET_ERRNO DROP TABLE t1; --remove_file $MYSQLD_DATADIR/test/t1.frm diff --git a/mysql-test/t/partition_not_blackhole.test b/mysql-test/t/partition_not_blackhole.test index 3731d659ad0..fe7452432b2 100644 --- a/mysql-test/t/partition_not_blackhole.test +++ b/mysql-test/t/partition_not_blackhole.test @@ -19,6 +19,9 @@ SHOW TABLES; --replace_result $MYSQLD_DATADIR ./ --error ER_NOT_FORM_FILE SHOW CREATE TABLE t1; + +# The replace is needed for Solaris +--replace_result "Not owner" "Operation not permitted" --error ER_GET_ERRNO DROP TABLE t1; --list_files $MYSQLD_DATADIR/test t1* diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 7b68f42c4cb..5c6dfdc9dd1 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1678,6 +1678,18 @@ select count(*) from t1 ignore index (ix_fd) where fd <'😁'; drop table t1; set names default; +# +# Bug#17755540 VALGRIND ERROR WHEN SETTING UP ROW COMPARATORS +# +create table t2 (a int, b int, c int, d int, key x(a, b)); +insert into t2 values (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), + (6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); +insert into t2 select * from t2; +insert into t2 values (0, 0, 0, 0), (1, 1, 1, 1); +analyze table t2; +select a, b from t2 where (a, b) in ((0, 0), (1, 1)); +drop table t2; + --echo # --echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE --echo # diff --git a/mysql-test/t/show_row_order-9226.test b/mysql-test/t/show_row_order-9226.test new file mode 100644 index 00000000000..06df24061ec --- /dev/null +++ b/mysql-test/t/show_row_order-9226.test @@ -0,0 +1,73 @@ +# +# MDEV-9226 SHOW COLUMNS returns wrong column order for tables with large ENUMs +# +create table test_table ( + column_number_1 enum('1','2') not null, + column_number_2 enum('1','2','3','4','5','6','7','8','9','10','11','12') not null, + column_number_3 varchar(10) not null, + column_number_4 varchar(10) not null, + column_number_5 enum( +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa01', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa02', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa03', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa04', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa05', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa06', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa07', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa08', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa09', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa10', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa11', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa13', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa14', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa15', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa16', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa17', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa18', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa19', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa20', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa21', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa22', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa23', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa24', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa25', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa26', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa27', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa28', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa29', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa30', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa31', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa32', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa33', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa34', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa35', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa36', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa37', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa38', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa39', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa40', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa41', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa42', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa43', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa44', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa45', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa46', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa47', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa48', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa49', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa50', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa51', +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa52' + ) not null, + column_number_6 enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131') not null, + column_number_7 enum('1','2','3','4','5','6','7') not null, + column_number_8 enum('8') not null, + column_number_9 enum('9') not null, + column_number_10 varchar(10) not null, + column_number_11 enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49') not null +) default charset=utf8mb4; +# SHOW command must list columns in the table order +# (SELECT isn't required to do it, though) +show columns from test_table; +drop table test_table; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 25ca322ca0a..4cbaa9e27c8 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -232,4 +232,77 @@ SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1; DROP TABLE t1,t2; +--echo # +--echo # MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists +--echo # + +--disable_warnings +drop database if exists db1; +drop database if exists db1; +--enable_warnings + +create database db1; +create database db2; +use db1; +--echo # +--echo # First, run the original testcase: +--echo # +create table t1 (i int); +insert into t1 values (10),(20); +analyze table t1 persistent for all; +rename table t1 to db2.t1; + +--echo # Verify that stats in the old database are gone: +select * from mysql.column_stats where db_name='db1' and table_name='t1'; +select * from mysql.table_stats where db_name='db1' and table_name='t1'; + +--echo # Verify that stats are present in the new database: +select * from mysql.column_stats where db_name='db2' and table_name='t1'; +select * from mysql.table_stats where db_name='db2' and table_name='t1'; + + +--echo # +--echo # Now, try with more than one column and with indexes: +--echo # +use test; +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + + +use db1; +create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b)); +insert into t2 select a/10, a/2, a from test.t1; +analyze table t2 persistent for all; + +alter table t2 rename db2.t2; + +--echo # Verify that stats in the old database are gone: +select * from mysql.table_stats where db_name='db1' and table_name='t2'; +select * from mysql.column_stats where db_name='db1' and table_name='t2'; +select * from mysql.index_stats where db_name='db1' and table_name='t2'; + +--echo # Verify that stats are present in the new database: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +select * from mysql.index_stats where db_name='db2' and table_name='t2'; + +use db2; +--echo # +--echo # Now, rename within the same database and verify: +--echo # +rename table t2 to t3; +--echo # No stats under old name: +select * from mysql.table_stats where db_name='db2' and table_name='t2'; +select * from mysql.column_stats where db_name='db2' and table_name='t2'; +select * from mysql.index_stats where db_name='db2' and table_name='t2'; +--echo # Stats under the new name: +select * from mysql.table_stats where db_name='db2' and table_name='t3'; +select * from mysql.column_stats where db_name='db2' and table_name='t3'; +select * from mysql.index_stats where db_name='db2' and table_name='t3'; + +use test; +drop database db1; +drop database db2; +drop table t1; + set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test index 36e2c5a77bd..2c8dec307cc 100644 --- a/mysql-test/t/statistics.test +++ b/mysql-test/t/statistics.test @@ -701,3 +701,19 @@ drop table t1, t2; set use_stat_tables=@save_use_stat_tables; +--echo # +--echo # Bug MDEV-7383: min/max value for a column not utf8 compatible +--echo # + +create table t1 (a varchar(100)) engine=MyISAM; +insert into t1 values(unhex('D879626AF872675F73E662F8')); +analyze table t1 persistent for all; +show warnings; + +select db_name, table_name, column_name, + HEX(min_value), HEX(max_value), + nulls_ratio, avg_frequency, + hist_size, hist_type, HEX(histogram) + FROM mysql.column_stats; + +drop table t1; diff --git a/mysql-test/t/udf_notembedded.test b/mysql-test/t/udf_notembedded.test new file mode 100644 index 00000000000..bf54af7256c --- /dev/null +++ b/mysql-test/t/udf_notembedded.test @@ -0,0 +1,14 @@ +--source include/not_embedded.inc +--source include/have_udf.inc + +# +# MDEV-8644 Using a UDF in a virtual column causes a crash when stopping the server +# +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval create function sequence returns integer soname "$UDF_EXAMPLE_SO"; +create table t1 (n int key not null auto_increment, msg int as (sequence()) virtual); +select * from t1; +source include/restart_mysqld.inc; +drop table t1; +drop function sequence; + diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 05f9ce39bec..e5ef0b11127 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -560,6 +560,79 @@ ROLLBACK; DROP TABLE t1, t2; --echo # +--echo # MDEV-8938: Server Crash on Update with joins +--echo # + +CREATE TABLE `t1` ( + `name` varchar(255) NOT NULL, + `value` varchar(4095) DEFAULT NULL, + PRIMARY KEY (`name`) +); + +UPDATE `t1` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `t1` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain"; + +drop table t1; + +CREATE TABLE `t1` ( + `name` varchar(255) NOT NULL, + `value` varchar(4095) DEFAULT NULL, + PRIMARY KEY (`name`) +); + +create table t2 ( + `name` varchar(255) NOT NULL, + `value` varchar(4095) DEFAULT NULL, + PRIMARY KEY (`name`) +); + +UPDATE t1 +SET value = (SELECT value FROM t2 WHERE `name`= t1.name) +WHERE value is null ; + +drop table t1,t2; + +--echo # +--echo #MDEV-8701: Crash on derived query +--echo # + +CREATE TABLE t1 ( + data_exit_entry_id int(11) NOT NULL, + data_entry_id int(11) NOT NULL, + data_entry_exit_id int(11) NOT NULL, + data_exit_entry_quantity double NOT NULL +) DEFAULT CHARSET=utf8; + +CREATE TABLE t2 ( + data_entry_id int(11) NOT NULL, + data_entry_cost double NOT NULL, + data_entry_quantity double NOT NULL +) DEFAULT CHARSET=utf8; + +create algorithm=temptable view v1 as SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost + FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id; + +UPDATE t2 +SET data_entry_cost + = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost) + FROM + v1 AS query + WHERE data_entry_exit_id = t2.data_entry_id + ) + ); + +UPDATE t2 +SET data_entry_cost + = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost) + FROM + ( SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost + FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id) AS query + WHERE data_entry_exit_id = t2.data_entry_id + ) + ); + +drop view v1; +drop table t1, t2; +--echo # --echo # MDEV-4410: update does not want to use a covering index, but select uses it. --echo # create table t2(a int); diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 773df49de6f..d8e7ad051d7 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5445,6 +5445,41 @@ EXECUTE stmt; DROP TABLE t1, t2, t3; DROP VIEW v3; +--echo # +--echo # MDEV-8632: Segmentation fault on INSERT +--echo # +CREATE TABLE `t1` ( + `id` int(10) unsigned NOT NULL, + `r` float NOT NULL, + PRIMARY KEY (`id`) +) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +create view v1 as select id, if(r=r,1,2) as d from t1; +create view v2 as + select id, + d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p + from v1; +insert into t1 (id, r) +select id,p from +( + select id, + d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p + from ( + select id, if(r=r,1,2) as d + from t1 + ) a +) b +on duplicate key update r=p; +insert into t1 (id, r) +select id,p from v2 +on duplicate key update r=p; + +prepare stmt from "insert into t1 (id, r) select id,p from v2 on duplicate key update r=p"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1,v2; +drop table `t1`; --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- @@ -5602,6 +5637,38 @@ SELECT v1.*,v2.* FROM v1 LEFT JOIN v2 ON v1.idAlbum = v2.idAlbum WHERE v1.idAlbu drop view v1,v2; drop table t1,t2,t3,t4; +--echo # +--echo # MDEV-8913: Derived queries with same column names as final +--echo # projection causes issues when using Order By +--echo # +create table t1 (field int); +insert into t1 values (10),(5),(3),(8),(20); + +SELECT sq.f2 AS f1, sq.f1 AS f2 +FROM ( SELECT field AS f1, 1 AS f2 FROM t1) AS sq +ORDER BY sq.f1; + +create view v1 as SELECT field AS f1, 1 AS f2 FROM t1; + +SELECT sq.f2 AS f1, sq.f1 AS f2 +FROM v1 AS sq +ORDER BY sq.f1; + +drop view v1; + +create table t2 SELECT field AS f1, 1 AS f2 FROM t1; + +SELECT + sq.f2 AS f1, + sq.f1 AS f2 +FROM t2 AS sq +ORDER BY sq.f1; + +drop table t1, t2; + +--error ER_BAD_FIELD_ERROR +SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1; + --echo # ----------------------------------------------------------------- --echo # -- End of 10.0 tests. --echo # ----------------------------------------------------------------- |