diff options
Diffstat (limited to 'mysql-test')
74 files changed, 2262 insertions, 1253 deletions
diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index f528f1c473e..b3623402065 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -24,6 +24,4 @@ sys_vars.wait_timeout_func # Bug#11750645 2010-04-26 alik wai sys_vars.ndb_log_update_as_write_basic sys_vars.have_ndbcluster_basic sys_vars.ndb_log_updated_only_basic -sys_vars.query_cache_size_basic_64 # Bug#11748572 - 36747: ALLOCATING A LARGE QUERY CACHE IS NOT DETERMINISTIC -sys_vars.query_cache_size_basic_32 # Bug#11748572 - 36747: ALLOCATING A LARGE QUERY CACHE IS NOT DETERMINISTIC sys_vars.rpl_init_slave_func # Bug#12535301 2011-05-09 andrei sys_vars.rpl_init_slave_func mismatches in daily-5.5 diff --git a/mysql-test/extra/rpl_tests/rpl_extra_col_master.test b/mysql-test/extra/rpl_tests/rpl_extra_col_master.test index 513bc949c5d..235eb858d34 100644 --- a/mysql-test/extra/rpl_tests/rpl_extra_col_master.test +++ b/mysql-test/extra/rpl_tests/rpl_extra_col_master.test @@ -59,6 +59,8 @@ #VARCHAR(M) # +--let $_saved_conn= $CURRENT_CONNECTION + let $binformat = `SHOW VARIABLES LIKE '%binlog_format%'`; --echo --echo *********************************************************** @@ -1017,3 +1019,5 @@ SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1; connection master; DROP TABLE t5; sync_slave_with_master; + +--connection $_saved_conn diff --git a/mysql-test/extra/rpl_tests/rpl_start_stop_slave.test b/mysql-test/extra/rpl_tests/rpl_start_stop_slave.test index de71bcdc93f..f828c79bc6a 100644 --- a/mysql-test/extra/rpl_tests/rpl_start_stop_slave.test +++ b/mysql-test/extra/rpl_tests/rpl_start_stop_slave.test @@ -180,3 +180,36 @@ UNLOCK TABLES; --connection master DROP TABLE t1; sync_slave_with_master; + +# +# bug#3593869-64035 uninitialized event_coordinates instance crashes server +# Testing how out of valid range position value is handled with an error. +# + +--connection master +RESET MASTER; +let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1); +let $master_pos= `SELECT $master_pos + 1`; + +--connection slave +--source include/stop_slave.inc +--replace_regex /[0-9]+/MASTER_POS/ +eval CHANGE MASTER TO master_log_pos=$master_pos; + +START SLAVE; +# ER_MASTER_FATAL_ERROR_READING_BINLOG 1236 +--let $slave_param=Last_IO_Errno +--let $slave_param_value=1236 +--source include/wait_for_slave_param.inc + +--let $slave_field_result_replace= / at [0-9]*/ at XXX/ +--let $status_items= Last_IO_Errno, Last_IO_Error +--source include/show_slave_status.inc + +--source include/stop_slave.inc +RESET SLAVE; + +--connection master +RESET MASTER; + +# Slave is stopped by bug#3593869-64035 tests. diff --git a/mysql-test/include/libdaemon_example.ini b/mysql-test/include/libdaemon_example.ini new file mode 100644 index 00000000000..7e7df5bd2a2 --- /dev/null +++ b/mysql-test/include/libdaemon_example.ini @@ -0,0 +1,9 @@ +# +# Plugin configuration file. Place the following on a separate line: +# +# library binary file name (without .so or .dll) +# component_name +# [component_name] - additional components in plugin +# +liblibdaemon_example +daemon_example diff --git a/mysql-test/include/show_slave_status.inc b/mysql-test/include/show_slave_status.inc index d04c6354386..41b5ee17ecd 100644 --- a/mysql-test/include/show_slave_status.inc +++ b/mysql-test/include/show_slave_status.inc @@ -55,6 +55,18 @@ # comma-separated list. Example: # # --let $status_items= Master_Log_File, Relay_Master_Log_File +# +# $slave_field_result_replace +# If set, one or more regex patterns for replacing variable +# text in the error message. Syntax as --replace-regex +# +# $slave_sql_mode +# If set, change the slave sql mode during this macro, reverting +# to the previous on exit. Default sql_mode is NO_BACKSLASH_ESCAPES +# to allow replace '\' by '/' making paths OS independent. Example: +# +# --let $slave_sql_mode= NO_BACKSLASH_ESCAPES +# --let $_show_slave_status_items=$status_items @@ -64,6 +76,17 @@ if (!$status_items) } +--let $_slave_sql_mode= NO_BACKSLASH_ESCAPES +if ($slave_sql_mode) +{ + --let $_slave_sql_mode= $slave_sql_mode +} +--let $_previous_slave_sql_mode = `SELECT @@sql_mode` +--disable_query_log +eval SET sql_mode= '$_slave_sql_mode'; +--enable_query_log + + while ($_show_slave_status_items) { --let $_show_slave_status_name= `SELECT SUBSTRING_INDEX('$_show_slave_status_items', ',', 1)` @@ -71,6 +94,13 @@ while ($_show_slave_status_items) --replace_regex /\.[\\\/]master/master/ --let $_show_slave_status_value= query_get_value(SHOW SLAVE STATUS, $_show_slave_status_name, 1) + --let $_slave_field_result_replace= /[\\]/\// $slave_field_result_replace + --replace_regex $_slave_field_result_replace --let $_show_slave_status_value= `SELECT REPLACE("$_show_slave_status_value", '$MYSQL_TEST_DIR', 'MYSQL_TEST_DIR')` --echo $_show_slave_status_name = '$_show_slave_status_value' } + + +--disable_query_log +eval SET sql_mode= '$_previous_slave_sql_mode'; +--enable_query_log diff --git a/mysql-test/include/wait_for_ndb_to_binlog.inc b/mysql-test/include/wait_for_ndb_to_binlog.inc index 77da6d62154..c1f94802724 100644 --- a/mysql-test/include/wait_for_ndb_to_binlog.inc +++ b/mysql-test/include/wait_for_ndb_to_binlog.inc @@ -1,13 +1,11 @@ # ==== Purpose ==== # -# Several test primitives from mysql-test/extra/rpl_tests -# shared for test cases for MyISAM, InnoDB, NDB and other -# engines. But for NDB all events will be added by NDB -# injector and now there are no way to detect the state of -# NDB injector therefore this primitive waits 5 sec -# if engine type is NDB. -# In future that should be fixed by waiting of proper -# state of NDB injector. +# Several test primitives from mysql-test/extra/rpl_tests +# are shared for test cases for MyISAM, InnoDB, NDB and +# other engines. +# For NDB engine all events will be added by NDB injector +# so tests only can continue after injector is ready, +# this test waits for proper injector thread state. # # ==== Usage ==== # @@ -17,25 +15,12 @@ # ==== Parameters ===== # # $engine_type -# Type of engine. If type is NDB then it waits $wait_time sec -# -# $wait_time -# Test will wait $wait_time seconds - -let $_wait_time= 5; - -if (!$wait_time) { - let $_wait_time= $wait_time; -} +# Type of engine. If type is NDB then it waits for injector +# thread proper state. if (`SELECT UPPER(LEFT('$engine_type',3)) = 'NDB'`) { - while (!$_wait_time) { - let $_wait_time_internal= 10; - while (!$_wait_time_internal) { - sleep 0.1; - dec $_wait_time_internal; - } - dec $_wait_time; - } + let $show_statement= SHOW PROCESSLIST; + let $field= State; + let $condition= = 'Waiting for event from ndbcluster'; + source include/wait_show_condition.inc; } - diff --git a/mysql-test/include/wait_for_slave_io_error.inc b/mysql-test/include/wait_for_slave_io_error.inc index e1609113b8f..4177bf4b858 100644 --- a/mysql-test/include/wait_for_slave_io_error.inc +++ b/mysql-test/include/wait_for_slave_io_error.inc @@ -33,6 +33,10 @@ # (e.g., temporary connection error) and does not cause the slave # IO thread to stop. # +# $slave_io_error_replace +# If set, one or more regex patterns for replacing variable +# text in the error message. Syntax as --replace-regex +# # $slave_timeout # See include/wait_for_slave_param.inc # @@ -72,6 +76,7 @@ if ($show_slave_io_error) { --let $_wait_for_slave_io_error_old_status_items= $status_items --let $status_items= Last_IO_Error + --let $slave_field_result_replace= $slave_io_error_replace --source include/show_slave_status.inc --let $status_items= $_wait_for_slave_io_error_old_status_items } diff --git a/mysql-test/lib/My/Test.pm b/mysql-test/lib/My/Test.pm index 2d2bac3be03..4d017b3b42c 100644 --- a/mysql-test/lib/My/Test.pm +++ b/mysql-test/lib/My/Test.pm @@ -111,7 +111,7 @@ sub read_test { $serialized =~ s/\\([0-9a-fA-F]{2})/chr(hex($1))/eg; my $test= Storable::thaw($serialized); use Data::Dumper; - die "wrong class (hack attempt?): ".ref($test)."\n".Dumper(\$test) + die "wrong class (hack attempt?): ".ref($test)."\n".Dumper(\$test, $serialized) unless ref($test) eq 'My::Test'; resfile_from_test($test) if $::opt_resfile; return $test; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index b2895f82beb..a22271bb15f 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3665,7 +3665,7 @@ sub do_before_run_mysqltest($) # Remove old files produced by mysqltest die "unsupported result file name $resfile, stoping" unless - $resfile =~ /^(.*?)((?:,\w+)*)\.(rdiff|result)$/; + $resfile =~ /^(.*?)((?:,\w+)*)\.(rdiff|result|result~)$/; my ($base_file, $suites, $ext)= ($1, $2, $3); # if the result file is a diff, make a proper result file if ($ext eq 'rdiff') { diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 3ff3cd741e1..505d655b6bb 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -438,3 +438,67 @@ Level Code Message Note 1003 select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 0) and ('0' = `test`.`t1`.`b`)) DROP TABLE t1; # End of 5.1 tests +# +# Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON +# +CREATE TABLE t_bigint(id BIGINT); +INSERT INTO t_bigint VALUES (1), (2); +SELECT id, id >= 1.1 FROM t_bigint; +id id >= 1.1 +1 0 +2 1 +SELECT id, 1.1 <= id FROM t_bigint; +id 1.1 <= id +1 0 +2 1 +SELECT id, id = 1.1 FROM t_bigint; +id id = 1.1 +1 0 +2 0 +SELECT id, 1.1 = id FROM t_bigint; +id 1.1 = id +1 0 +2 0 +SELECT * from t_bigint WHERE id = 1.1; +id +SELECT * from t_bigint WHERE id = 1.1e0; +id +SELECT * from t_bigint WHERE id = '1.1'; +id +SELECT * from t_bigint WHERE id = '1.1e0'; +id +SELECT * from t_bigint WHERE id IN (1.1, 2.2); +id +SELECT * from t_bigint WHERE id IN (1.1e0, 2.2e0); +id +SELECT * from t_bigint WHERE id IN ('1.1', '2.2'); +id +SELECT * from t_bigint WHERE id IN ('1.1e0', '2.2e0'); +id +SELECT * from t_bigint WHERE id BETWEEN 1.1 AND 1.9; +id +SELECT * from t_bigint WHERE id BETWEEN 1.1e0 AND 1.9e0; +id +SELECT * from t_bigint WHERE id BETWEEN '1.1' AND '1.9'; +id +SELECT * from t_bigint WHERE id BETWEEN '1.1e0' AND '1.9e0'; +id +DROP TABLE t_bigint; +# +# Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2 +# +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9; +a +SELECT * FROM t1 WHERE coalesce(a)=0.9; +a +SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9); +a +SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9; +a +SELECT * FROM t1 WHERE a=0.9; +a +SELECT * FROM t1 WHERE a IN (0.8,0.9); +a +DROP TABLE t1; diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index b8ef2a50733..1cb4b48ed69 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -1416,7 +1416,7 @@ create table t1 as select concat(unix_timestamp(20090224)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(17) NOT NULL DEFAULT '' + `c1` varbinary(17) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(time_to_sec('10:11:12'))); diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index cb700f2feb8..3d695a87701 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -1808,7 +1808,7 @@ create table t1 as select concat(unix_timestamp(20090224)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) CHARACTER SET cp1251 NOT NULL DEFAULT '' + `c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(time_to_sec('10:11:12'))); diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index 6c494b83668..e2169e4e4e2 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -1835,7 +1835,7 @@ create table t1 as select concat(unix_timestamp(20090224)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) NOT NULL DEFAULT '' + `c1` varchar(17) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(time_to_sec('10:11:12'))); diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 76c5bc40a82..b482e3d542c 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -2718,7 +2718,7 @@ create table t1 as select concat(unix_timestamp(20090224)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) CHARACTER SET ucs2 NOT NULL DEFAULT '' + `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(time_to_sec('10:11:12'))); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 51924e06309..4dd9d787528 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1931,6 +1931,30 @@ D120 SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)); HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)) D120D18E +# +# Bug#11752408 - 43593: DUMP/BACKUP/RESTORE/UPGRADE TOOLS FAILS BECAUSE OF UTF8_GENERAL_CI +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci); +INSERT INTO t1 VALUES ('a'),('r'),('s'),(_latin1 0xDF),(_latin1 0xF7),('t'),('z'); +SELECT * FROM t1 ORDER BY a; +a +a +r +s +t +z +ß +÷ +SELECT a, COUNT(*) FROM t1 GROUP BY a; +a COUNT(*) +a 1 +r 1 +s 1 +t 1 +z 1 +ß 1 +÷ 1 +DROP TABLE t1; End of 5.1 tests Start of 5.4 tests SET NAMES utf8mb3; @@ -3550,7 +3574,7 @@ create table t1 as select concat(unix_timestamp(20090224)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) CHARACTER SET utf8 NOT NULL DEFAULT '' + `c1` varchar(17) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(time_to_sec('10:11:12'))); diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 650f103710d..2c20b77a7c4 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -469,6 +469,8 @@ SELECT HEX(a) FROM t2 WHERE a IN HEX(a) 7FFFFFFFFFFFFFFE 7FFFFFFFFFFFFFFF +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'abc' CREATE TABLE t3 (a BIGINT UNSIGNED); INSERT INTO t3 VALUES (9223372036854775551); SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); @@ -777,18 +779,3 @@ SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); 1 # End of 5.1 tests -create table t1 (a bigint, b int); -insert t1 values (1,1),(2,2),(3,3); -select * from t1 where a in ('2.1'); -a b -2 2 -select * from t1 where b in ('2.1'); -a b -select * from t1 where a='2.1'; -a b -2 2 -select * from t1 where b='2.1'; -a b -select * from t1 where IF(1,a,a)='2.1'; -a b -drop table t1; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index f07aa9ceeed..205197bc7f6 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2829,5 +2829,41 @@ SELECT ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)); ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)) 0 # +# Bug#13359121 LARGE NUMBERS, /STRINGS/DTOA.C:662: +# BALLOC: ASSERTION `K <= 15' FAILED. +# Bug#12985021 SIMPLE QUERY WITH DECIMAL NUMBERS TAKE AN +# EXTRAORDINARY LONG TIME TO EXECUTE +SELECT @tmp_max:= @@global.max_allowed_packet; +@tmp_max:= @@global.max_allowed_packet +1048576 +SET @@global.max_allowed_packet=1024*1024*1024; +SELECT @@global.max_allowed_packet; +@@global.max_allowed_packet +1073741824 +do +format(rpad('111111111.1', +1111111, +'999999999999999999999999999999999999999999'),0,'be_BY') +; +DO +round( +concat( ( +coalesce( ( +linefromwkb('2147483648', +-b'1111111111111111111111111111111111111111111')), +( convert('[.DC2.]',decimal(30,30)) ), +bit_count('') +) ), +( lpad( ( elt('01','}:K5')), +sha1('P'), +( ( select '-9223372036854775808.1' > all (select ''))) +) +) +) +); +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '[.DC2.]' +SET @@global.max_allowed_packet:= @tmp_max; +# # End of 5.5 tests # diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 94353dc1833..d4a071d2d63 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1436,6 +1436,256 @@ NULL DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); Warnings: Warning 1292 Truncated incorrect date value: '0000-00-00' +# +# BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY +# SIMILAR TO '2009-10-00' +# +SELECT +DATE('20091000'), +STR_TO_DATE('200910','%Y%m'), +LAST_DAY('2009-10-00'), +LAST_DAY(DATE('2009-10-00')), +LAST_DAY(DATE'2009-10-00'), +LAST_DAY(STR_TO_DATE('200910','%Y%m')), +WEEK('2009-10-00'), +WEEK(DATE('2009-10-00')), +WEEK(DATE'2009-10-00'), +WEEK(STR_TO_DATE('200910','%Y%m')), +WEEKOFYEAR('2009-10-00'), +WEEKOFYEAR(DATE('2009-10-00')), +WEEKOFYEAR(DATE'2009-10-00'), +WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')), +DAYOFYEAR('2009-10-00'), +DAYOFYEAR(DATE('2009-10-00')), +DAYOFYEAR(DATE'2009-10-00'), +DAYOFYEAR(STR_TO_DATE('200910','%Y%m')), +WEEKDAY('2009-10-00'), +WEEKDAY(DATE('2009-10-00')), +WEEKDAY(DATE'2009-10-00'), +WEEKDAY(STR_TO_DATE('200910','%Y%m')), +TO_DAYs('2009-10-00'), +TO_DAYs(DATE('2009-10-00')), +TO_DAYs(DATE'2009-10-00'), +TO_DAYs(STR_TO_DATE('200910','%Y%m')); +DATE('20091000') 2009-10-00 +STR_TO_DATE('200910','%Y%m') 2009-10-00 +LAST_DAY('2009-10-00') NULL +LAST_DAY(DATE('2009-10-00')) NULL +LAST_DAY(DATE'2009-10-00') NULL +LAST_DAY(STR_TO_DATE('200910','%Y%m')) 2009-10-31 +WEEK('2009-10-00') NULL +WEEK(DATE('2009-10-00')) NULL +WEEK(DATE'2009-10-00') NULL +WEEK(STR_TO_DATE('200910','%Y%m')) NULL +WEEKOFYEAR('2009-10-00') NULL +WEEKOFYEAR(DATE('2009-10-00')) NULL +WEEKOFYEAR(DATE'2009-10-00') NULL +WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL +DAYOFYEAR('2009-10-00') NULL +DAYOFYEAR(DATE('2009-10-00')) NULL +DAYOFYEAR(DATE'2009-10-00') NULL +DAYOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL +WEEKDAY('2009-10-00') NULL +WEEKDAY(DATE('2009-10-00')) NULL +WEEKDAY(DATE'2009-10-00') NULL +WEEKDAY(STR_TO_DATE('200910','%Y%m')) NULL +TO_DAYs('2009-10-00') NULL +TO_DAYs(DATE('2009-10-00')) NULL +TO_DAYs(DATE'2009-10-00') NULL +TO_DAYs(STR_TO_DATE('200910','%Y%m')) NULL +Warnings: +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '200910' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '200910' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '200910' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '200910' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '200910' for function str_to_date +SELECT +DATE('00000100'), +STR_TO_DATE('000001','%Y%m'), +LAST_DAY('0000-01-00'), +LAST_DAY(DATE('0000-01-00')), +LAST_DAY(DATE'0000-01-00'), +LAST_DAY(STR_TO_DATE('000001','%Y%m')), +WEEK('0000-01-00'), +WEEK(DATE('0000-01-00')), +WEEK(DATE'0000-01-00'), +WEEK(STR_TO_DATE('000001','%Y%m')), +WEEKOFYEAR('0000-01-00'), +WEEKOFYEAR(DATE('0000-01-00')), +WEEKOFYEAR(DATE'0000-01-00'), +WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')), +DAYOFYEAR('0000-01-00'), +DAYOFYEAR(DATE('0000-01-00')), +DAYOFYEAR(DATE'0000-01-00'), +DAYOFYEAR(STR_TO_DATE('000001','%Y%m')), +WEEKDAY('0000-01-00'), +WEEKDAY(DATE('0000-01-00')), +WEEKDAY(DATE'0000-01-00'), +WEEKDAY(STR_TO_DATE('000001','%Y%m')), +TO_DAYs('0000-01-00'), +TO_DAYs(DATE('0000-01-00')), +TO_DAYs(DATE'0000-01-00'), +TO_DAYs(STR_TO_DATE('000001','%Y%m')); +DATE('00000100') 0000-01-00 +STR_TO_DATE('000001','%Y%m') 0000-01-00 +LAST_DAY('0000-01-00') NULL +LAST_DAY(DATE('0000-01-00')) NULL +LAST_DAY(DATE'0000-01-00') NULL +LAST_DAY(STR_TO_DATE('000001','%Y%m')) 0000-01-31 +WEEK('0000-01-00') NULL +WEEK(DATE('0000-01-00')) NULL +WEEK(DATE'0000-01-00') NULL +WEEK(STR_TO_DATE('000001','%Y%m')) NULL +WEEKOFYEAR('0000-01-00') NULL +WEEKOFYEAR(DATE('0000-01-00')) NULL +WEEKOFYEAR(DATE'0000-01-00') NULL +WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL +DAYOFYEAR('0000-01-00') NULL +DAYOFYEAR(DATE('0000-01-00')) NULL +DAYOFYEAR(DATE'0000-01-00') NULL +DAYOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL +WEEKDAY('0000-01-00') NULL +WEEKDAY(DATE('0000-01-00')) NULL +WEEKDAY(DATE'0000-01-00') NULL +WEEKDAY(STR_TO_DATE('000001','%Y%m')) NULL +TO_DAYs('0000-01-00') NULL +TO_DAYs(DATE('0000-01-00')) NULL +TO_DAYs(DATE'0000-01-00') NULL +TO_DAYs(STR_TO_DATE('000001','%Y%m')) NULL +Warnings: +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '000001' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '000001' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '000001' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '000001' for function str_to_date +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Truncated incorrect date value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1411 +Message Incorrect datetime value: '000001' for function str_to_date End of 5.1 tests # # Bug#57039: constant subtime expression returns incorrect result. @@ -1465,6 +1715,21 @@ NULL 1 NULL SET storage_engine=NULL; ERROR 42000: Variable 'storage_engine' can't be set to the value of 'NULL' # +# BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIMESTAMP +# Part1 (5.5) +SET time_zone='+03:00'; +CREATE TABLE t1 (a DATETIME NOT NULL); +INSERT INTO t1 VALUES ('2009-09-20 07:32:39.06'); +INSERT INTO t1 VALUES ('0000-00-00 00:00:00.00'); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT CAST(UNIX_TIMESTAMP(a) AS DECIMAL(25,3)) AS c1 FROM v1 ORDER BY 1; +c1 +NULL +1253421159.000 +DROP VIEW v1; +DROP TABLE t1; +SET time_zone=DEFAULT; +# # Bug #59686 crash in String::copy() with time data type # SELECT min(timestampadd(month, 1>'', from_days('%Z'))); diff --git a/mysql-test/r/mysql_plugin.result b/mysql-test/r/mysql_plugin.result index 93567e28c3d..636b039047e 100644 --- a/mysql-test/r/mysql_plugin.result +++ b/mysql-test/r/mysql_plugin.result @@ -23,12 +23,24 @@ wacky libdaemon_example.so wicky libdaemon_example.so wonky libdaemon_example.so # +# Ensure the plugin is loaded. +# +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; +name dl +daemon_example libdaemon_example.so +# +# Ensure the plugin is replaced. +# +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; +name dl +daemon_example liblibdaemon_example.so +# # Disable the plugin... # # # Ensure the plugin isn't loaded. # -SELECT * FROM mysql.plugin WHERE dl like 'libdaemon%' ORDER BY name; +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; name dl # # Attempt to load non-existant plugin diff --git a/mysql-test/r/mysqld--help,win.rdiff b/mysql-test/r/mysqld--help,win.rdiff index 676393720a7..957292b265f 100644 --- a/mysql-test/r/mysqld--help,win.rdiff +++ b/mysql-test/r/mysqld--help,win.rdiff @@ -42,9 +42,9 @@ --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) + --standalone Dummy option to start as a standalone program (NT). - -s, --symbolic-links - Enable symbolic link support. - --sync-binlog=# Synchronously flush binary log to disk after every #th + --stored-program-cache=# + The soft upper limit for number of cached stored routines + for one connection. @@ -807,8 +815,8 @@ size, MySQL will automatically convert it to an on-disk MyISAM or Aria table diff --git a/mysql-test/r/mysqld--help-win.result.THIS b/mysql-test/r/mysqld--help-win.result.THIS deleted file mode 100644 index 1edd8b36430..00000000000 --- a/mysql-test/r/mysqld--help-win.result.THIS +++ /dev/null @@ -1,1098 +0,0 @@ -The following options may be given as the first argument: ---print-defaults Print the program argument list and exit. ---no-defaults Don't read default options from any option file. ---defaults-file=# Only read default options from the given file #. ---defaults-extra-file=# Read this file after the global files are read. - - --allow-suspicious-udfs - Allows use of UDFs consisting of only one symbol xxx() - without corresponding xxx_init() or xxx_deinit(). That - also means that one can load any function from any - library, for example exit() from libc.so - -a, --ansi Use ANSI SQL syntax instead of MySQL syntax. This mode - will also set transaction isolation level 'serializable'. - --auto-increment-increment[=#] - Auto-increment columns are incremented by this - --auto-increment-offset[=#] - Offset added to Auto-increment columns. Used when - auto-increment-increment != 1 - --autocommit Set default value for autocommit (0 or 1) - (Defaults to on; use --skip-autocommit to disable.) - --automatic-sp-privileges - Creating and dropping stored procedures alters ACLs - (Defaults to on; use --skip-automatic-sp-privileges to disable.) - --back-log=# The number of outstanding connection requests MySQL can - have. This comes into play when the main MySQL thread - gets very many connection requests in a very short time - -b, --basedir=name Path to installation directory. All paths are usually - resolved relative to this - --big-tables Allow big result sets by saving all temporary sets on - file (Solves most 'table full' errors) - --bind-address=name IP address to bind to. - --binlog-annotate-row-events - Tells the master to annotate RBR events with the - statement that caused these events - --binlog-cache-size=# - The size of the transactional cache for updates to - transactional engines for the binary log. If you often - use transactions containing many statements, you can - increase this to get more performance - --binlog-checksum=name - Type of BINLOG_CHECKSUM_ALG. Include checksum for log - events in the binary log. Possible values are NONE and - CRC32; default is NONE. - --binlog-direct-non-transactional-updates - Causes updates to non-transactional engines using - statement format to be written directly to binary log. - Before using this option make sure that there are no - dependencies between transactional and non-transactional - tables such as in the statement INSERT INTO t_myisam - SELECT * FROM t_innodb; otherwise, slaves may diverge - from the master. - --binlog-do-db=name Tells the master it should log updates for the specified - database, and exclude all others not explicitly - mentioned. - --binlog-format=name - What form of binary logging the master will use: either - ROW for row-based binary logging, STATEMENT for - statement-based binary logging, or MIXED. MIXED is - statement-based binary logging except for those - statements where only row-based is correct: those which - involve user-defined functions (i.e. UDFs) or the UUID() - function; for those, row-based binary logging is - automatically used. If NDBCLUSTER is enabled and - binlog-format is MIXED, the format switches to row-based - and back implicitly per each query accessing an - NDBCLUSTER table - --binlog-ignore-db=name - Tells the master that updates to the given database - should not be logged to the binary log. - --binlog-optimize-thread-scheduling - Run fast part of group commit in a single thread, to - optimize kernel thread scheduling. On by default. Disable - to run each transaction in group commit in its own - thread, which can be slower at very high concurrency. - This option is mostly for testing one algorithm versus - the other, and it should not normally be necessary to - change it. - (Defaults to on; use --skip-binlog-optimize-thread-scheduling to disable.) - --binlog-row-event-max-size=# - The maximum size of a row-based binary log event in - bytes. Rows will be grouped into events smaller than this - size if possible. The value has to be a multiple of 256. - --binlog-stmt-cache-size=# - The size of the statement cache for updates to - non-transactional engines for the binary log. If you - often use statements updating a great number of rows, you - can increase this to get more performance - --bootstrap Used by mysql installation scripts. - --bulk-insert-buffer-size=# - Size of tree cache used in bulk insert optimisation. Note - that this is a limit per thread! - --character-set-client-handshake - Don't ignore client side character set value sent during - handshake. - (Defaults to on; use --skip-character-set-client-handshake to disable.) - --character-set-filesystem=name - Set the filesystem character set. - -C, --character-set-server=name - Set the default character set. - --character-sets-dir=name - Directory where character sets are - -r, --chroot=name Chroot mysqld daemon during startup. - --collation-server=name - Set the default collation. - --completion-type=name - The transaction completion type, one of NO_CHAIN, CHAIN, - RELEASE - --concurrent-insert[=name] - Use concurrent insert with MyISAM. Possible values are - NEVER, AUTO, ALWAYS - --connect-timeout=# The number of seconds the mysqld server is waiting for a - connect packet before responding with 'Bad handshake' - --console Write error output on screen; don't remove the console - window on windows. - --core-file Write core on errors. - -h, --datadir=name Path to the database root directory - --date-format=name The DATE format (ignored) - --datetime-format=name - The DATETIME format (ignored) - --deadlock-search-depth-long=# - Long search depth for the two-step deadlock detection - --deadlock-search-depth-short=# - Short search depth for the two-step deadlock detection - --deadlock-timeout-long=# - Long timeout for the two-step deadlock detection (in - microseconds) - --deadlock-timeout-short=# - Short timeout for the two-step deadlock detection (in - microseconds) - --default-storage-engine=name - The default storage engine for new tables - --default-time-zone=name - Set the default time zone. - --default-week-format=# - The default week format used by WEEK() functions - --delay-key-write[=name] - Type of DELAY_KEY_WRITE - --delayed-insert-limit=# - After inserting delayed_insert_limit rows, the INSERT - DELAYED handler will check if there are any SELECT - statements pending. If so, it allows these to execute - before continuing - --delayed-insert-timeout=# - How long a INSERT DELAYED thread should wait for INSERT - statements before terminating - --delayed-queue-size=# - What size queue (in rows) should be allocated for - handling INSERT DELAYED. If the queue becomes full, any - client that does INSERT DELAYED will wait until there is - room in the queue again - --div-precision-increment=# - Precision of the result of '/' operator will be increased - on that value - --engine-condition-pushdown - Push supported query conditions to the storage engine. - Deprecated, use --optimizer-switch instead. - (Defaults to on; use --skip-engine-condition-pushdown to disable.) - --event-scheduler[=name] - Enable the event scheduler. Possible values are ON, OFF, - and DISABLED (keep the event scheduler completely - deactivated, it cannot be activated run-time) - --expire-logs-days=# - If non-zero, binary logs will be purged after - expire_logs_days days; possible purges happen at startup - and at binary log rotation - --external-locking Use system (external) locking (disabled by default). - With this option enabled you can run myisamchk to test - (not repair) tables while the MySQL server is running. - Disable with --skip-external-locking. - --extra-max-connections=# - The number of connections on extra-port - --extra-port=# Extra port number to use for tcp connections in a - one-thread-per-connection manner. 0 means don't use - another port - --flush Flush MyISAM tables to disk between SQL commands - --flush-time=# A dedicated thread is created to flush all tables at the - given interval - --ft-boolean-syntax=name - List of operators for MATCH ... AGAINST ( ... IN BOOLEAN - MODE) - --ft-max-word-len=# The maximum length of the word to be included in a - FULLTEXT index. Note: FULLTEXT indexes must be rebuilt - after changing this variable - --ft-min-word-len=# The minimum length of the word to be included in a - FULLTEXT index. Note: FULLTEXT indexes must be rebuilt - after changing this variable - --ft-query-expansion-limit=# - Number of best matches to use for query expansion - --ft-stopword-file=name - Use stopwords from this file instead of built-in list - --gdb Set up signals usable for debugging. Deprecated, use - --general-log Log connections and queries to a table or log file. - Defaults logging to a file 'hostname'.log or a table - mysql.general_logif --log-output=TABLE is used - --general-log-file=name - Log connections and queries to given file - --group-concat-max-len=# - The maximum length of the result of function - GROUP_CONCAT() - -?, --help Display this help and exit. - --ignore-builtin-innodb - Disable initialization of builtin InnoDB plugin - --init-connect=name Command(s) that are executed for each new connection - (unless the user has SUPER privilege) - --init-file=name Read SQL commands from this file at startup - --init-rpl-role=name - Set the replication role. - --init-slave=name Command(s) that are executed by a slave server each time - the SQL thread starts - --interactive-timeout=# - The number of seconds the server waits for activity on an - interactive connection before closing it - --join-buffer-size=# - The size of the buffer that is used for joins - --join-buffer-space-limit=# - The limit of the space for all join buffers used by a - query - --join-cache-level=# - Controls what join operations can be executed with join - buffers. Odd numbers are used for plain join buffers - while even numbers are used for linked buffers - --keep-files-on-create - Don't overwrite stale .MYD and .MYI even if no directory - is specified - --key-buffer-size=# The size of the buffer used for index blocks for MyISAM - tables. Increase this to get better index handling (for - all reads and multiple writes) to as much as you can - afford - --key-cache-age-threshold=# - This characterizes the number of hits a hot block has to - be untouched until it is considered aged enough to be - downgraded to a warm block. This specifies the percentage - ratio of that number of hits to the total number of - blocks in key cache - --key-cache-block-size=# - The default size of key cache blocks - --key-cache-division-limit=# - The minimum percentage of warm blocks in key cache - --key-cache-segments=# - The number of segments in a key cache - -L, --language=name Client error messages in given language. May be given as - a full path. Deprecated. Use --lc-messages-dir instead. - --lc-messages=name Set the language used for the error messages. - -L, --lc-messages-dir=name - Directory where error messages are - --lc-time-names=name - Set the language used for the month names and the days of - the week. - --local-infile Enable LOAD DATA LOCAL INFILE - (Defaults to on; use --skip-local-infile to disable.) - --lock-wait-timeout=# - Timeout in seconds to wait for a lock before returning an - error. - -l, --log[=name] Log connections and queries to file (deprecated option, - use --general-log/--general-log-file instead). - --log-basename=name Basename for all log files and the .pid file. This sets - all log file names at once (in 'datadir') and is normally - the only option you need for specifying log files. Sets - names for --log-bin, --log-bin-index, --relay-log, - --relay-log-index, --general-log-file, - --log-slow-query-log-file, --log-error-file, and - --pid-file - --log-bin[=name] Log update queries in binary format. Optional argument - should be name for binary log. If not given - 'datadir'/'log-basename'-bin or 'datadir'/mysql-bin will - be used (the later if --log-basename is not specified). - We strongly recommend to use either --log-basename or - specify a filename to ensure that replication doesn't - stop if the real hostname of the computer changes. - --log-bin-index=name - File that holds the names for last binary log files. - --log-bin-trust-function-creators - If set to FALSE (the default), then when --log-bin is - used, creation of a stored function (or trigger) is - allowed only to users having the SUPER privilege and only - if this stored function (trigger) may not break binary - logging. Note that if ALL connections to this server - ALWAYS use row-based binary logging, the security issues - do not exist and the binary logging cannot break, so you - can safely set this to TRUE - --log-error[=name] Log errors to file (instead of stdout). If file name is - not specified then 'datadir'/'log-basename'.err or the - 'pid-file' path with extension .err is used - --log-isam[=name] Log all MyISAM changes to file. - --log-output=name Syntax: log-output=value[,value...], where "value" could - be TABLE, FILE or NONE - --log-queries-not-using-indexes - Log queries that are executed without benefit of any - index to the slow log if it is open - --log-short-format Don't log extra information to update and slow-query - logs. - --log-slave-updates Tells the slave to log the updates from the slave thread - to the binary log. You will need to turn it on if you - plan to daisy-chain the slaves - --log-slow-admin-statements - Log slow OPTIMIZE, ANALYZE, ALTER and other - administrative statements to the slow log if it is open. - --log-slow-filter=name - Log only certain types of queries. Multiple flags can be - specified, separated by commas. Valid values are admin, - slave, filesort, filesort_on_disk, full_join, full_scan, - query_cache, query_cache_miss, tmp_table, - tmp_table_on_disk - --log-slow-queries[=name] - Enable logging of slow queries (longer than - --long-query-time) to log file or table. Optional - argument is a file name for the slow log. If not given, - 'log-basename'-slow.log will be used. Use - --log-output=TABLE if you want to have the log in the - table mysql.slow_log. Deprecated option, use - --slow-query-log/--slow-query-log-file instead. - --log-slow-rate-limit=# - Write to slow log every #th slow query. Set to 1 to log - everything. Increase it to reduce the size of the slow or - the performance impact of slow logging - --log-slow-slave-statements - Log slow statements executed by slave thread to the slow - log if it is open. - --log-slow-verbosity=name - log-slow-verbosity=[value[,value ...]] where value is one - of 'innodb', 'query_plan' - --log-tc=name Path to transaction coordinator log (used for - transactions that affect more than one storage engine, - when binary log is disabled). - --log-tc-size=# Size of transaction coordinator log. - -W, --log-warnings[=#] - Log some not critical warnings to the general log - file.Value can be between 0 and 11. Higher values mean - more verbosity - --long-query-time=# Log all queries that have taken more than long_query_time - seconds to execute to file. The argument will be treated - as a decimal value with microsecond precision - --low-priority-updates - INSERT/DELETE/UPDATE has lower priority than selects - --lower-case-table-names[=#] - If set to 1 table names are stored in lowercase on disk - and table names will be case-insensitive. Should be set - to 2 if you are using a case insensitive file system - --master-info-file=name - The location and name of the file that remembers the - master and where the I/O replication thread is in the - master's binlogs. Defaults to master.info - --master-retry-count=# - The number of tries the slave will make to connect to the - master before giving up. - --master-verify-checksum - Force checksum verification of logged events in the - binary log before sending them to slaves or printing them - in the output of SHOW BINLOG EVENTS - --max-allowed-packet=# - Max packet length to send to or receive from the server - --max-binlog-cache-size=# - Sets the total size of the transactional cache - --max-binlog-size=# Binary log will be rotated automatically when the size - exceeds this value. Will also apply to relay logs if - max_relay_log_size is 0 - --max-binlog-stmt-cache-size=# - Sets the total size of the statement cache - --max-connect-errors=# - If there is more than this number of interrupted - connections from a host this host will be blocked from - further connections - --max-connections=# The number of simultaneous clients allowed - --max-delayed-threads=# - Don't start more than this number of threads to handle - INSERT DELAYED statements. If set to zero INSERT DELAYED - will be not used - --max-error-count=# Max number of errors/warnings to store for a statement - --max-heap-table-size=# - Don't allow creation of heap tables bigger than this - --max-join-size=# Joins that are probably going to read more than - max_join_size records return an error - --max-length-for-sort-data=# - Max number of bytes in sorted records - --max-long-data-size=# - The maximum BLOB length to send to server from - mysql_send_long_data API. Deprecated option; use - max_allowed_packet instead. - --max-prepared-stmt-count=# - Maximum number of prepared statements in the server - --max-relay-log-size=# - If non-zero: relay log will be rotated automatically when - the size exceeds this value; if zero: when the size - exceeds max_binlog_size - --max-seeks-for-key=# - Limit assumed max number of seeks when looking up rows - based on a key - --max-sort-length=# The number of bytes to use when sorting BLOB or TEXT - values (only the first max_sort_length bytes of each - value are used; the rest are ignored) - --max-sp-recursion-depth[=#] - Maximum stored procedure recursion depth - --max-tmp-tables=# Maximum number of temporary tables a client can keep open - at a time - --max-user-connections=# - The maximum number of active connections for a single - user (0 = no limit) - --max-write-lock-count=# - After this many write locks, allow some read locks to run - in between - --memlock Lock mysqld in memory. - --metadata-locks-cache-size=# - Size of unused metadata locks cache - --min-examined-row-limit=# - Don't write queries to slow log that examine fewer rows - than that - --mrr-buffer-size=# Size of buffer to use when using MRR with range access - --multi-range-count=# - Ignored. Use mrr_buffer_size instead - --myisam-block-size=# - Block size to be used for MyISAM index pages - --myisam-data-pointer-size=# - Default pointer size to be used for MyISAM tables - --myisam-max-sort-file-size=# - Don't use the fast sort index method to created index if - the temporary file would get bigger than this - --myisam-mmap-size=# - Restricts the total memory used for memory mapping of - MySQL tables - --myisam-recover-options[=name] - Syntax: myisam-recover-options[=option[,option...]], - where option can be DEFAULT, BACKUP, BACKUP_ALL, FORCE, - QUICK, or OFF - --myisam-repair-threads=# - If larger than 1, when repairing a MyISAM table all - indexes will be created in parallel, with one thread per - index. The value of 1 disables parallel repair - --myisam-sort-buffer-size=# - The buffer that is allocated when sorting the index when - doing a REPAIR or when creating indexes with CREATE INDEX - or ALTER TABLE - --myisam-stats-method=name - Specifies how MyISAM index statistics collection code - should treat NULLs. Possible values of name are - NULLS_UNEQUAL (default behavior for 4.1 and later), - NULLS_EQUAL (emulate 4.0 behavior), and NULLS_IGNORED - --myisam-use-mmap Use memory mapping for reading and writing MyISAM tables - --named-pipe Enable the named pipe (NT) - --net-buffer-length=# - Buffer length for TCP/IP and socket communication - --net-read-timeout=# - Number of seconds to wait for more data from a connection - before aborting the read - --net-retry-count=# If a read on a communication port is interrupted, retry - this many times before giving up - --net-write-timeout=# - Number of seconds to wait for a block to be written to a - connection before aborting the write - --old Use compatible behavior - --old-alter-table Use old, non-optimized alter table - --old-passwords Use old password encryption method (needed for 4.0 and - older clients) - --old-style-user-limits - Enable old-style user limits (before 5.0.3, user - resources were counted per each user+host vs. per - account). - --one-thread (Deprecated): Only use one thread (for debugging under - Linux). Use thread-handling=no-threads instead. - --open-files-limit=# - If this is not 0, then mysqld will use this value to - reserve file descriptors to use with setrlimit(). If this - value is 0 then mysqld will reserve max_connections*5 or - max_connections + table_cache*2 (whichever is larger) - number of file descriptors - --optimizer-prune-level=# - Controls the heuristic(s) applied during query - optimization to prune less-promising partial plans from - the optimizer search space. Meaning: 0 - do not apply any - heuristic, thus perform exhaustive search; 1 - prune - plans based on number of retrieved rows - --optimizer-search-depth=# - Maximum depth of search performed by the query optimizer. - Values larger than the number of relations in a query - result in better query plans, but take longer to compile - a query. Values smaller than the number of tables in a - relation result in faster optimization, but may produce - very bad query plans. If set to 0, the system will - automatically pick a reasonable value; if set to 63, the - optimizer will switch to the original find_best search. - NOTE: The value 63 and its associated behaviour is - deprecated - --optimizer-switch=name - optimizer_switch=option=val[,option=val...], where option - is one of {derived_merge, derived_with_keys, firstmatch, - in_to_exists, engine_condition_pushdown, - index_condition_pushdown, index_merge, - index_merge_intersection, index_merge_sort_intersection, - index_merge_sort_union, index_merge_union, - join_cache_bka, join_cache_hashed, - join_cache_incremental, loosescan, materialization, mrr, - mrr_cost_based, mrr_sort_keys, optimize_join_buffer_size, - outer_join_with_cache, partial_match_rowid_merge, - partial_match_table_scan, semijoin, semijoin_with_cache, - subquery_cache, table_elimination } and val is one of - {on, off, default} - --performance-schema - Enable the performance schema. - --performance-schema-events-waits-history-long-size=# - Number of rows in EVENTS_WAITS_HISTORY_LONG. - --performance-schema-events-waits-history-size=# - Number of rows per thread in EVENTS_WAITS_HISTORY. - --performance-schema-max-cond-classes=# - Maximum number of condition instruments. - --performance-schema-max-cond-instances=# - Maximum number of instrumented condition objects. - --performance-schema-max-file-classes=# - Maximum number of file instruments. - --performance-schema-max-file-handles=# - Maximum number of opened instrumented files. - --performance-schema-max-file-instances=# - Maximum number of instrumented files. - --performance-schema-max-mutex-classes=# - Maximum number of mutex instruments. - --performance-schema-max-mutex-instances=# - Maximum number of instrumented MUTEX objects. - --performance-schema-max-rwlock-classes=# - Maximum number of rwlock instruments. - --performance-schema-max-rwlock-instances=# - Maximum number of instrumented RWLOCK objects. - --performance-schema-max-table-handles=# - Maximum number of opened instrumented tables. - --performance-schema-max-table-instances=# - Maximum number of instrumented tables. - --performance-schema-max-thread-classes=# - Maximum number of thread instruments. - --performance-schema-max-thread-instances=# - Maximum number of instrumented threads. - --pid-file=name Pid file used by safe_mysqld - --plugin-dir=name Directory for plugins - --plugin-load=name Semicolon-separated list of plugins to load, where each - plugin is specified as ether a plugin_name=library_file - pair or only a library_file. If the latter case, all - plugins from a given library_file will be loaded. - --plugin-maturity=name - The lowest desirable plugin maturity (unknown, - experimental, alpha, beta, gamma, or stable). Plugins - less mature than that will not be installed or loaded. - -P, --port=# Port number to use for connection or 0 to default to, - my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default - (3306), whatever comes first - --port-open-timeout=# - Maximum time in seconds to wait for the port to become - free. (Default: No wait). - --preload-buffer-size=# - The size of the buffer that is allocated when preloading - indexes - --profiling-history-size=# - Limit of query profiling memory - --progress-report-time=# - Seconds between sending progress reports to the client - for time-consuming statements. Set to 0 to disable - progress reporting. - --query-alloc-block-size=# - Allocation block size for query parsing and execution - --query-cache-limit=# - Don't cache results that are bigger than this - --query-cache-min-res-unit=# - The minimum size for blocks allocated by the query cache - --query-cache-size=# - The memory allocated to store results from old queries - --query-cache-strip-comments - Strip all comments from a query before storing it in the - query cache - --query-cache-type=name - OFF = Don't cache or retrieve results. ON = Cache all - results except SELECT SQL_NO_CACHE ... queries. DEMAND = - Cache only SELECT SQL_CACHE ... queries - --query-cache-wlock-invalidate - Invalidate queries in query cache on LOCK for write - --query-prealloc-size=# - Persistent buffer for query parsing and execution - --range-alloc-block-size=# - Allocation block size for storing ranges during - optimization - --read-buffer-size=# - Each thread that does a sequential scan allocates a - buffer of this size for each table it scans. If you do - many sequential scans, you may want to increase this - value - --read-only Make all non-temporary tables read-only, with the - exception for replication (slave) threads and users with - the SUPER privilege - --read-rnd-buffer-size=# - When reading rows in sorted order after a sort, the rows - are read through this buffer to avoid a disk seeks - --relay-log=name The location and name to use for relay logs - --relay-log-index=name - The location and name to use for the file that keeps a - list of the last relay logs - --relay-log-info-file=name - The location and name of the file that remembers where - the SQL replication thread is in the relay logs - --relay-log-purge if disabled - do not purge relay logs. if enabled - purge - them as soon as they are no more needed - (Defaults to on; use --skip-relay-log-purge to disable.) - --relay-log-recovery - Enables automatic relay log recovery right after the - database startup, which means that the IO Thread starts - re-fetching from the master right after the last - transaction processed - --relay-log-space-limit=# - Maximum space to use for all relay logs - --replicate-annotate-row-events - Tells the slave to write annotate rows events recieved - from the master to its own binary log. Ignored if - log_slave_updates is not set - --replicate-do-db=name - Tells the slave thread to restrict replication to the - specified database. To specify more than one database, - use the directive multiple times, once for each database. - Note that this will only work if you do not use - cross-database queries such as UPDATE some_db.some_table - SET foo='bar' while having selected a different or no - database. If you need cross database updates to work, - make sure you have 3.23.28 or later, and use - replicate-wild-do-table=db_name.%. - --replicate-do-table=name - Tells the slave thread to restrict replication to the - specified table. To specify more than one table, use the - directive multiple times, once for each table. This will - work for cross-database updates, in contrast to - replicate-do-db. - --replicate-events-marked-for-skip=name - Whether the slave should replicate events that were - created with @@skip_replication=1 on the master. Default - REPLICATE (no events are skipped). Other values are - FILTER_ON_SLAVE (events will be sent by the master but - ignored by the slave) and FILTER_ON_MASTER (events marked - with @@skip_replication=1 will be filtered on the master - and never be sent to the slave). - --replicate-ignore-db=name - Tells the slave thread to not replicate to the specified - database. To specify more than one database to ignore, - use the directive multiple times, once for each database. - This option will not work if you use cross database - updates. If you need cross database updates to work, make - sure you have 3.23.28 or later, and use - replicate-wild-ignore-table=db_name.%. - --replicate-ignore-table=name - Tells the slave thread to not replicate to the specified - table. To specify more than one table to ignore, use the - directive multiple times, once for each table. This will - work for cross-database updates, in contrast to - replicate-ignore-db. - --replicate-rewrite-db=name - Updates to a database with a different name than the - original. Example: - replicate-rewrite-db=master_db_name->slave_db_name. - --replicate-same-server-id - In replication, if set to 1, do not skip events having - our server id. Default value is 0 (to break infinite - loops in circular replication). Can't be set to 1 if - --log-slave-updates is used. - --replicate-wild-do-table=name - Tells the slave thread to restrict replication to the - tables that match the specified wildcard pattern. To - specify more than one table, use the directive multiple - times, once for each table. This will work for - cross-database updates. Example: - replicate-wild-do-table=foo%.bar% will replicate only - updates to tables in all databases that start with foo - and whose table names start with bar. - --replicate-wild-ignore-table=name - Tells the slave thread to not replicate to the tables - that match the given wildcard pattern. To specify more - than one table to ignore, use the directive multiple - times, once for each table. This will work for - cross-database updates. Example: - replicate-wild-ignore-table=foo%.bar% will not do updates - to tables in databases that start with foo and whose - table names start with bar. - --report-host=name Hostname or IP of the slave to be reported to the master - during slave registration. Will appear in the output of - SHOW SLAVE HOSTS. Leave unset if you do not want the - slave to register itself with the master. Note that it is - not sufficient for the master to simply read the IP of - the slave off the socket once the slave connects. Due to - NAT and other routing issues, that IP may not be valid - for connecting to the slave from the master or other - hosts - --report-password=name - The account password of the slave to be reported to the - master during slave registration - --report-port=# Port for connecting to slave reported to the master - during slave registration. Set it only if the slave is - listening on a non-default port or if you have a special - tunnel from the master or other clients to the slave. If - not sure, leave this option unset - --report-user=name The account user name of the slave to be reported to the - master during slave registration - --rowid-merge-buff-size=# - The size of the buffers used [NOT] IN evaluation via - partial matching - --rpl-recovery-rank=# - Unused, will be removed - --safe-mode Skip some optimize stages (for testing). - --safe-user-create Don't allow new user creation by the user who has no - write privileges to the mysql.user table. - --secure-auth Disallow authentication for accounts that have old - (pre-4.1) passwords - --secure-file-priv=name - Limit LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() to - files within specified directory - --server-id=# Uniquely identifies the server instance in the community - of replication partners - --shared-memory Enable the shared memory - --shared-memory-base-name=name - Base name of shared memory - --show-slave-auth-info - Show user and password in SHOW SLAVE HOSTS on this - master. - --skip-bdb Deprecated option; Exist only for compatiblity with old - my.cnf files - --skip-grant-tables Start without grant tables. This gives all users FULL - ACCESS to all tables. - --skip-host-cache Don't cache host names. - --skip-name-resolve Don't resolve hostnames. All hostnames are IP's or - 'localhost'. - --skip-networking Don't allow connection with TCP/IP - --skip-show-database - Don't allow 'SHOW DATABASE' commands - --skip-slave-start If set, slave is not autostarted. - --skip-thread-priority - Don't give threads different priorities. This option is - deprecated because it has no effect; the implied behavior - is already the default. - --slave-compressed-protocol - Use compression on master/slave protocol - --slave-exec-mode=name - Modes for how replication events should be executed. - Legal values are STRICT (default) and IDEMPOTENT. In - IDEMPOTENT mode, replication will not stop for operations - that are idempotent. In STRICT mode, replication will - stop on any unexpected difference between the master and - the slave - --slave-load-tmpdir=name - The location where the slave should put its temporary - files when replicating a LOAD DATA INFILE command - --slave-net-timeout=# - Number of seconds to wait for more data from a - master/slave connection before aborting the read - --slave-skip-errors=name - Tells the slave thread to continue replication when a - query event returns an error from the provided list - --slave-sql-verify-checksum - Force checksum verification of replication events after - reading them from relay log. Note: Events are always - checksum-verified by slave on receiving them from the - network before writing them to the relay log - (Defaults to on; use --skip-slave-sql-verify-checksum to disable.) - --slave-transaction-retries=# - Number of times the slave SQL thread will retry a - transaction in case it failed with a deadlock or elapsed - lock wait timeout, before giving up and stopping - --slave-type-conversions=name - Set of slave type conversions that are enabled. Legal - values are: ALL_LOSSY to enable lossy conversions and - ALL_NON_LOSSY to enable non-lossy conversions. If the - variable is assigned the empty set, no conversions are - allowed and it is expected that the types match exactly. - --slow-launch-time=# - If creating the thread takes longer than this value (in - seconds), the Slow_launch_threads counter will be - incremented - --slow-query-log Log slow queries to a table or log file. Defaults logging - to a file 'hostname'-slow.log or a table mysql.slow_log - if --log-output=TABLE is used. Must be enabled to - activate other slow log options - --slow-query-log-file=name - Log slow queries to given log file. Defaults logging to - 'hostname'-slow.log. Must be enabled to activate other - slow log options - --slow-start-timeout=# - Maximum number of milliseconds that the service control - manager should wait before trying to kill the windows - service during startup(Default: 15000). - --socket=name Socket file to use for connection - --sort-buffer-size=# - Each thread that needs to do a sort allocates a buffer of - this size - --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual - for the complete list of valid sql modes - --stack-trace Print a symbolic stack trace on failure - (Defaults to on; use --skip-stack-trace to disable.) - --standalone Dummy option to start as a standalone program (NT). - -s, --symbolic-links - Enable symbolic link support. - --sync-binlog=# Synchronously flush binary log to disk after every #th - event. Use 0 (default) to disable synchronous flushing - --sync-frm Sync .frm files to disk on creation - (Defaults to on; use --skip-sync-frm to disable.) - --sync-master-info=# - Synchronously flush master info to disk after every #th - event. Use 0 (default) to disable synchronous flushing - --sync-relay-log=# Synchronously flush relay log to disk after every #th - event. Use 0 (default) to disable synchronous flushing - --sync-relay-log-info=# - Synchronously flush relay log info to disk after every - #th transaction. Use 0 (default) to disable synchronous - flushing - --sysdate-is-now Non-default option to alias SYSDATE() to NOW() to make it - safe-replicable. Since 5.0, SYSDATE() returns a `dynamic' - value different for different invocations, even within - the same statement. - --table-cache=# Deprecated; use --table-open-cache instead. - --table-definition-cache=# - The number of cached table definitions - --table-open-cache=# - The number of cached open tables - --tc-heuristic-recover=name - Decision to use in heuristic recover process. Possible - values are COMMIT or ROLLBACK. - --thread-cache-size=# - How many threads we should keep in a cache for reuse - --thread-stack=# The stack size for each thread - --time-format=name The TIME format (ignored) - --timed-mutexes Specify whether to time mutexes (only InnoDB mutexes are - currently supported) - --tmp-table-size=# If an internal in-memory temporary table exceeds this - size, MySQL will automatically convert it to an on-disk - MyISAM or Aria table - -t, --tmpdir=name Path for temporary files. Several paths may be specified, - separated by a semicolon (;), in this case they are used - in a round-robin fashion - --transaction-alloc-block-size=# - Allocation block size for transactions to be stored in - binary log - --transaction-isolation=name - Default transaction isolation level. - --transaction-prealloc-size=# - Persistent buffer for transactions to be stored in binary - log - --updatable-views-with-limit=name - YES = Don't issue an error message (warning only) if a - VIEW without presence of a key of the underlying table is - used in queries with a LIMIT clause for updating. NO = - Prohibit update of a VIEW, which does not contain a key - of the underlying table and the query uses a LIMIT clause - (usually get from GUI tools) - -u, --user=name Run mysqld daemon as user. - --userstat Enables statistics gathering for USER_STATISTICS, - CLIENT_STATISTICS, INDEX_STATISTICS and TABLE_STATISTICS - tables in the INFORMATION_SCHEMA - -v, --verbose Used with --help option for detailed help. - -V, --version Output version information and exit. - --wait-timeout=# The number of seconds the server waits for activity on a - connection before closing it - -Variables (--variable-name=value) -allow-suspicious-udfs FALSE -auto-increment-increment 1 -auto-increment-offset 1 -autocommit TRUE -automatic-sp-privileges TRUE -back-log 50 -big-tables FALSE -bind-address (No default value) -binlog-annotate-row-events FALSE -binlog-cache-size 32768 -binlog-checksum NONE -binlog-direct-non-transactional-updates FALSE -binlog-format STATEMENT -binlog-optimize-thread-scheduling TRUE -binlog-row-event-max-size 1024 -binlog-stmt-cache-size 32768 -bulk-insert-buffer-size 8388608 -character-set-client-handshake TRUE -character-set-filesystem binary -character-set-server latin1 -character-sets-dir MYSQL_CHARSETSDIR/ -chroot (No default value) -collation-server latin1_swedish_ci -completion-type NO_CHAIN -concurrent-insert AUTO -connect-timeout 10 -console FALSE -date-format %Y-%m-%d -datetime-format %Y-%m-%d %H:%i:%s -deadlock-search-depth-long 15 -deadlock-search-depth-short 4 -deadlock-timeout-long 50000000 -deadlock-timeout-short 10000 -default-storage-engine myisam -default-time-zone (No default value) -default-week-format 0 -delay-key-write ON -delayed-insert-limit 100 -delayed-insert-timeout 300 -delayed-queue-size 1000 -div-precision-increment 4 -engine-condition-pushdown FALSE -event-scheduler OFF -expire-logs-days 0 -external-locking FALSE -extra-max-connections 1 -extra-port 0 -flush FALSE -flush-time 0 -ft-boolean-syntax + -><()~*:""&| -ft-max-word-len 84 -ft-min-word-len 4 -ft-query-expansion-limit 20 -ft-stopword-file (No default value) -gdb FALSE -general-log FALSE -group-concat-max-len 1024 -help TRUE -ignore-builtin-innodb FALSE -init-connect -init-file (No default value) -init-rpl-role MASTER -init-slave -interactive-timeout 28800 -join-buffer-size 131072 -join-buffer-space-limit 2097152 -join-cache-level 2 -keep-files-on-create FALSE -key-buffer-size 134217728 -key-cache-age-threshold 300 -key-cache-block-size 1024 -key-cache-division-limit 100 -key-cache-segments 0 -language MYSQL_SHAREDIR/ -lc-messages en_US -lc-messages-dir MYSQL_SHAREDIR/ -lc-time-names en_US -local-infile TRUE -lock-wait-timeout 31536000 -log-bin (No default value) -log-bin-index (No default value) -log-bin-trust-function-creators FALSE -log-error -log-isam myisam.log -log-output FILE -log-queries-not-using-indexes FALSE -log-short-format FALSE -log-slave-updates FALSE -log-slow-admin-statements FALSE -log-slow-filter admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk -log-slow-rate-limit 1 -log-slow-slave-statements FALSE -log-slow-verbosity -log-tc tc.log -log-tc-size 24576 -log-warnings 1 -long-query-time 10 -low-priority-updates FALSE -lower-case-table-names 1 -master-info-file master.info -master-retry-count 86400 -master-verify-checksum FALSE -max-allowed-packet 1048576 -max-binlog-cache-size 18446744073709547520 -max-binlog-size 1073741824 -max-binlog-stmt-cache-size 18446744073709547520 -max-connect-errors 10 -max-connections 151 -max-delayed-threads 20 -max-error-count 64 -max-heap-table-size 16777216 -max-join-size 18446744073709551615 -max-length-for-sort-data 1024 -max-long-data-size 1048576 -max-prepared-stmt-count 16382 -max-relay-log-size 0 -max-seeks-for-key 18446744073709551615 -max-sort-length 1024 -max-sp-recursion-depth 0 -max-tmp-tables 32 -max-user-connections 0 -max-write-lock-count 18446744073709551615 -memlock FALSE -metadata-locks-cache-size 1024 -min-examined-row-limit 0 -mrr-buffer-size 262144 -multi-range-count 256 -myisam-block-size 1024 -myisam-data-pointer-size 6 -myisam-max-sort-file-size 9223372036853727232 -myisam-mmap-size 18446744073709551615 -myisam-recover-options DEFAULT -myisam-repair-threads 1 -myisam-sort-buffer-size 8388608 -myisam-stats-method nulls_unequal -myisam-use-mmap FALSE -named-pipe FALSE -net-buffer-length 16384 -net-read-timeout 30 -net-retry-count 10 -net-write-timeout 60 -old FALSE -old-alter-table FALSE -old-passwords FALSE -old-style-user-limits FALSE -optimizer-prune-level 1 -optimizer-search-depth 62 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on -performance-schema FALSE -performance-schema-events-waits-history-long-size 10000 -performance-schema-events-waits-history-size 10 -performance-schema-max-cond-classes 80 -performance-schema-max-cond-instances 1000 -performance-schema-max-file-classes 50 -performance-schema-max-file-handles 32768 -performance-schema-max-file-instances 10000 -performance-schema-max-mutex-classes 200 -performance-schema-max-mutex-instances 1000000 -performance-schema-max-rwlock-classes 30 -performance-schema-max-rwlock-instances 1000000 -performance-schema-max-table-handles 100000 -performance-schema-max-table-instances 50000 -performance-schema-max-thread-classes 50 -performance-schema-max-thread-instances 1000 -plugin-load (No default value) -plugin-maturity unknown -port 3306 -port-open-timeout 0 -preload-buffer-size 32768 -profiling-history-size 15 -progress-report-time 56 -query-alloc-block-size 8192 -query-cache-limit 1048576 -query-cache-min-res-unit 4096 -query-cache-size 0 -query-cache-strip-comments FALSE -query-cache-type ON -query-cache-wlock-invalidate FALSE -query-prealloc-size 8192 -range-alloc-block-size 4096 -read-buffer-size 131072 -read-only FALSE -read-rnd-buffer-size 262144 -relay-log (No default value) -relay-log-index (No default value) -relay-log-info-file relay-log.info -relay-log-purge TRUE -relay-log-recovery FALSE -relay-log-space-limit 0 -replicate-annotate-row-events FALSE -replicate-events-marked-for-skip replicate -replicate-same-server-id FALSE -report-host (No default value) -report-password (No default value) -report-port 3306 -report-user (No default value) -rowid-merge-buff-size 8388608 -rpl-recovery-rank 0 -safe-user-create FALSE -secure-auth FALSE -secure-file-priv (No default value) -server-id 0 -shared-memory FALSE -shared-memory-base-name MYSQL -show-slave-auth-info FALSE -skip-grant-tables TRUE -skip-name-resolve FALSE -skip-networking FALSE -skip-show-database FALSE -skip-slave-start FALSE -slave-compressed-protocol FALSE -slave-exec-mode STRICT -slave-net-timeout 3600 -slave-skip-errors (No default value) -slave-sql-verify-checksum TRUE -slave-transaction-retries 10 -slave-type-conversions -slow-launch-time 2 -slow-query-log FALSE -slow-start-timeout 15000 -sort-buffer-size 2097152 -sql-mode -stack-trace TRUE -symbolic-links FALSE -sync-binlog 0 -sync-frm FALSE -sync-master-info 0 -sync-relay-log 0 -sync-relay-log-info 0 -sysdate-is-now FALSE -table-cache 400 -table-definition-cache 400 -table-open-cache 400 -tc-heuristic-recover COMMIT -thread-cache-size 0 -thread-stack 294912 -time-format %H:%i:%s -timed-mutexes FALSE -tmp-table-size 16777216 -transaction-alloc-block-size 8192 -transaction-isolation REPEATABLE-READ -transaction-prealloc-size 4096 -updatable-views-with-limit YES -userstat FALSE -verbose TRUE -wait-timeout 28800 - -To see what values a running MySQL server is using, type -'mysqladmin variables' instead of 'mysqld --verbose --help'. diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index cc226069eb7..f9b64e88f68 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -775,6 +775,9 @@ The following options may be given as the first argument: for the complete list of valid sql modes --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) + --stored-program-cache=# + The soft upper limit for number of cached stored routines + for one connection. -s, --symbolic-links Enable symbolic link support. --sync-binlog=# Synchronously flush binary log to disk after every #th @@ -1059,6 +1062,7 @@ slow-query-log FALSE sort-buffer-size 2097152 sql-mode stack-trace TRUE +stored-program-cache 256 symbolic-links FALSE sync-binlog 0 sync-frm FALSE diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 3db422657e3..8025f7a74b2 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -4713,6 +4713,445 @@ ALTER DATABASE `test-database` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; DROP DATABASE `test-database`; USE test; # +# BUG#11760384 : 52792: mysqldump in XML mode does not dump routines. +# +CREATE DATABASE BUG52792; +USE BUG52792; +CREATE TABLE t1 (c1 INT, c2 VARCHAR(20)); +CREATE TABLE t2 (c1 INT); +INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); +INSERT INTO t2 VALUES (1),(2),(3); +# Stored Procedures. +CREATE PROCEDURE simpleproc1 (OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t1; +END// +CREATE PROCEDURE simpleproc2 (OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t2; +END// +# Events. +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE BUG52792; +CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE BUG52792; +# Functions. +CREATE FUNCTION `hello1` (s CHAR(20)) +RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT('Hello, ' ,s ,'!'); +CREATE FUNCTION `hello2` (s CHAR(20)) +RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT(']]>, ' , s ,'!'); +# Triggers. +CREATE TRIGGER trig1 BEFORE INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1); +END; +| +CREATE TRIGGER trig2 AFTER INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1, ']]>'); +INSERT INTO t2 VALUES(2, '<![CDATA]]>'); +INSERT INTO t2 VALUES(3, '<![CDATA['); +INSERT INTO t2 VALUES(4, '< > & \ " _'); +END; +| +# Views +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t2; + +# Dumping BUG52792 database in xml format. + +# Running 'replace_regex on timestamp' +<?xml version="1.0"?> +<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +<database name="BUG52792"> + <table_structure name="t1"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <field Field="c2" Type="varchar(20)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="t1" Engine="MyISAM" Version="10" Row_format="Dynamic" Rows="3" Avg_row_length="20" Data_length="60" Max_data_length="281474976710655" Index_length="1024" Data_free="0" Create_time="--TIME--" Update_time="--TIME--" Collation="latin1_swedish_ci" Create_options="" Comment="" /> + </table_structure> + <table_data name="t1"> + <row> + <field name="c1">1</field> + <field name="c2">aaa</field> + </row> + <row> + <field name="c1">2</field> + <field name="c2">bbb</field> + </row> + <row> + <field name="c1">3</field> + <field name="c2">ccc</field> + </row> + </table_data> + <table_structure name="t2"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="t2" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="3" Avg_row_length="7" Data_length="21" Max_data_length="1970324836974591" Index_length="1024" Data_free="0" Create_time="--TIME--" Update_time="--TIME--" Collation="latin1_swedish_ci" Create_options="" Comment="" /> + </table_structure> + <table_data name="t2"> + <row> + <field name="c1">1</field> + </row> + <row> + <field name="c1">2</field> + </row> + <row> + <field name="c1">3</field> + </row> + </table_data> + <triggers name="t2"> + <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER trig1 BEFORE INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1); +END +]]> + </trigger> + <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER trig2 AFTER INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1, ']]]]><![CDATA[>'); +INSERT INTO t2 VALUES(2, '<![CDATA]]]]><![CDATA[>'); +INSERT INTO t2 VALUES(3, '<![CDATA['); +INSERT INTO t2 VALUES(4, '< > & \ " _'); +END +]]> + </trigger> + </triggers> + <table_structure name="v1"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <field Field="c2" Type="varchar(20)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="v1" Comment="VIEW" /> + </table_structure> + <table_structure name="v2"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="v2" Comment="VIEW" /> + </table_structure> + <events> + <event Event="e1" sql_mode="" time_zone="SYSTEM" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '--TIME--' ON COMPLETION NOT PRESERVE ENABLE DO DROP DATABASE BUG52792 +]]> + </event> + <event Event="e2" sql_mode="" time_zone="SYSTEM" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '--TIME--' ON COMPLETION NOT PRESERVE ENABLE DO DROP DATABASE BUG52792 +]]> + </event> + </events> + <routines> + <routine Function="hello1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` FUNCTION `hello1`(s CHAR(20)) RETURNS char(50) CHARSET latin1 + DETERMINISTIC +RETURN CONCAT('Hello, ' ,s ,'!') +]]> + </routine> + <routine Function="hello2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` FUNCTION `hello2`(s CHAR(20)) RETURNS char(50) CHARSET latin1 + DETERMINISTIC +RETURN CONCAT(']]]]><![CDATA[>, ' , s ,'!') +]]> + </routine> + <routine Procedure="simpleproc1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc1`(OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t1; +END +]]> + </routine> + <routine Procedure="simpleproc2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`(OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t2; +END +]]> + </routine> + </routines> +</database> +</mysqldump> + +# Dumping BUG52792 database in xml format with comments. + +# Running 'replace_regex on timestamp' +<?xml version="1.0"?> +<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +<database name="BUG52792"> +<!-- +- +- Table structure for table `t1` +- + + --> + <table_structure name="t1"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <field Field="c2" Type="varchar(20)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="t1" Engine="MyISAM" Version="10" Row_format="Dynamic" Rows="3" Avg_row_length="20" Data_length="60" Max_data_length="281474976710655" Index_length="1024" Data_free="0" Create_time="--TIME--" Update_time="--TIME--" Collation="latin1_swedish_ci" Create_options="" Comment="" /> + </table_structure> +<!-- +- +- Dumping data for table `t1` +- + --> + <table_data name="t1"> + <row> + <field name="c1">1</field> + <field name="c2">aaa</field> + </row> + <row> + <field name="c1">2</field> + <field name="c2">bbb</field> + </row> + <row> + <field name="c1">3</field> + <field name="c2">ccc</field> + </row> + </table_data> +<!-- +- +- Table structure for table `t2` +- + + --> + <table_structure name="t2"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="t2" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="3" Avg_row_length="7" Data_length="21" Max_data_length="1970324836974591" Index_length="1024" Data_free="0" Create_time="--TIME--" Update_time="--TIME--" Collation="latin1_swedish_ci" Create_options="" Comment="" /> + </table_structure> +<!-- +- +- Dumping data for table `t2` +- + --> + <table_data name="t2"> + <row> + <field name="c1">1</field> + </row> + <row> + <field name="c1">2</field> + </row> + <row> + <field name="c1">3</field> + </row> + </table_data> + <triggers name="t2"> + <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER trig1 BEFORE INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1); +END +]]> + </trigger> + <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER trig2 AFTER INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1, ']]]]><![CDATA[>'); +INSERT INTO t2 VALUES(2, '<![CDATA]]]]><![CDATA[>'); +INSERT INTO t2 VALUES(3, '<![CDATA['); +INSERT INTO t2 VALUES(4, '< > & \ " _'); +END +]]> + </trigger> + </triggers> +<!-- +- +- Table structure for table `v1` +- + + --> + <table_structure name="v1"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <field Field="c2" Type="varchar(20)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="v1" Comment="VIEW" /> + </table_structure> +<!-- +- +- Table structure for table `v2` +- + + --> + <table_structure name="v2"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="v2" Comment="VIEW" /> + </table_structure> +<!-- +- +- Dumping events for database 'BUG52792' +- + --> + <events> + <event Event="e1" sql_mode="" time_zone="SYSTEM" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '--TIME--' ON COMPLETION NOT PRESERVE ENABLE DO DROP DATABASE BUG52792 +]]> + </event> + <event Event="e2" sql_mode="" time_zone="SYSTEM" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '--TIME--' ON COMPLETION NOT PRESERVE ENABLE DO DROP DATABASE BUG52792 +]]> + </event> + </events> +<!-- +- +- Dumping routines for database 'BUG52792' +- + --> + <routines> + <routine Function="hello1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` FUNCTION `hello1`(s CHAR(20)) RETURNS char(50) CHARSET latin1 + DETERMINISTIC +RETURN CONCAT('Hello, ' ,s ,'!') +]]> + </routine> + <routine Function="hello2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` FUNCTION `hello2`(s CHAR(20)) RETURNS char(50) CHARSET latin1 + DETERMINISTIC +RETURN CONCAT(']]]]><![CDATA[>, ' , s ,'!') +]]> + </routine> + <routine Procedure="simpleproc1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc1`(OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t1; +END +]]> + </routine> + <routine Procedure="simpleproc2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`(OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t2; +END +]]> + </routine> + </routines> +</database> +</mysqldump> + +# Test to check 'Insufficient privileges' error. + +GRANT ALL PRIVILEGES ON BUG52792.* TO user1; +# Running 'replace_regex on timestamp' +<?xml version="1.0"?> +<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +<database name="BUG52792"> + <table_structure name="t1"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <field Field="c2" Type="varchar(20)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="t1" Engine="MyISAM" Version="10" Row_format="Dynamic" Rows="3" Avg_row_length="20" Data_length="60" Max_data_length="281474976710655" Index_length="1024" Data_free="0" Create_time="--TIME--" Update_time="--TIME--" Collation="latin1_swedish_ci" Create_options="" Comment="" /> + </table_structure> + <table_data name="t1"> + <row> + <field name="c1">1</field> + <field name="c2">aaa</field> + </row> + <row> + <field name="c1">2</field> + <field name="c2">bbb</field> + </row> + <row> + <field name="c1">3</field> + <field name="c2">ccc</field> + </row> + </table_data> + <table_structure name="t2"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="t2" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="3" Avg_row_length="7" Data_length="21" Max_data_length="1970324836974591" Index_length="1024" Data_free="0" Create_time="--TIME--" Update_time="--TIME--" Collation="latin1_swedish_ci" Create_options="" Comment="" /> + </table_structure> + <table_data name="t2"> + <row> + <field name="c1">1</field> + </row> + <row> + <field name="c1">2</field> + </row> + <row> + <field name="c1">3</field> + </row> + </table_data> + <triggers name="t2"> + <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER trig1 BEFORE INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1); +END +]]> + </trigger> + <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER trig2 AFTER INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1, ']]]]><![CDATA[>'); +INSERT INTO t2 VALUES(2, '<![CDATA]]]]><![CDATA[>'); +INSERT INTO t2 VALUES(3, '<![CDATA['); +INSERT INTO t2 VALUES(4, '< > & \ " _'); +END +]]> + </trigger> + </triggers> + <table_structure name="v1"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <field Field="c2" Type="varchar(20)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="v1" Comment="VIEW" /> + </table_structure> + <table_structure name="v2"> + <field Field="c1" Type="int(11)" Null="YES" Key="" Extra="" Comment="" /> + <options Name="v2" Comment="VIEW" /> + </table_structure> + <events> + <event Event="e1" sql_mode="" time_zone="SYSTEM" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '--TIME--' ON COMPLETION NOT PRESERVE ENABLE DO DROP DATABASE BUG52792 +]]> + </event> + <event Event="e2" sql_mode="" time_zone="SYSTEM" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '--TIME--' ON COMPLETION NOT PRESERVE ENABLE DO DROP DATABASE BUG52792 +]]> + </event> + </events> + <routines> +<!-- +- insufficient privileges to SHOW CREATE FUNCTION `hello1` + --> +<!-- - does user1 have permissions on mysql.proc? + + --> +DROP USER user1; +DROP DATABASE BUG52792; +# UTF-8 +CREATE DATABASE BUG52792; +USE BUG52792; +SET NAMES utf8; +CREATE FUNCTION `straße` ( c1 CHAR(20)) +RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT(']]>, ', s, '!'); +<?xml version="1.0"?> +<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +<database name="BUG52792"> + <routines> + <routine Function="straße" sql_mode="" character_set_client="utf8" collation_connection="utf8_general_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` FUNCTION `straße`( c1 CHAR(20)) RETURNS char(50) CHARSET latin1 + DETERMINISTIC +RETURN CONCAT(']]]]><![CDATA[>, ', s, '!') +]]> + </routine> + </routines> +</database> +</mysqldump> +DROP DATABASE BUG52792; +USE test; +# # End of 5.1 tests # # diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index f679e0a9f3d..d7bc193a15c 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -1793,7 +1793,7 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `const10` decimal(22,6) DEFAULT NULL, `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, @@ -1823,7 +1823,7 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 N 32769 6 63 +def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 74853f46ae5..ce9409762e5 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -1776,7 +1776,7 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `const10` decimal(22,6) DEFAULT NULL, `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, @@ -1806,7 +1806,7 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 N 32769 6 63 +def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 500ff119ba2..d5fc8f7e8da 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -1777,7 +1777,7 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `const10` decimal(22,6) DEFAULT NULL, `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, @@ -1807,7 +1807,7 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 N 32769 6 63 +def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index e7dea3d51ba..bb74363f68c 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -1713,7 +1713,7 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `const10` decimal(22,6) DEFAULT NULL, `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, @@ -1743,7 +1743,7 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 N 32769 6 63 +def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 @@ -5067,7 +5067,7 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `const10` decimal(22,6) DEFAULT NULL, `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, @@ -5097,7 +5097,7 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 N 32769 6 63 +def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 70ab2207e89..3b7d07d525e 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1768,6 +1768,123 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; pk i4 pk i4 DROP TABLE t1; +# +# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND +# WITH/WITHOUT INDEX RANGE SCAN +# +create table t1 (id int unsigned not null auto_increment primary key); +insert into t1 values (null); +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +create table t2 ( +id int unsigned not null auto_increment, +val decimal(5,3) not null, +primary key (id,val), +unique key (val,id), +unique key (id)); +insert into t2 select null,id*0.0009 from t1; +select count(val) from t2 ignore index (val) where val > 0.1155; +count(val) +128 +select count(val) from t2 force index (val) where val > 0.1155; +count(val) +128 +drop table t2, t1; +# +# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG +# RESULTS WITH DECIMAL CONVERSION +# +create table t1 (a int,b int,c int,primary key (a,c)); +insert into t1 values (1,1,2),(1,1,3),(1,1,4); +select convert(3, signed integer) > 2.9; +convert(3, signed integer) > 2.9 +1 +select * from t1 force index (primary) where a=1 and c>= 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 ignore index (primary) where a=1 and c>= 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 force index (primary) where a=1 and c> 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 ignore index (primary) where a=1 and c> 2.9; +a b c +1 1 3 +1 1 4 +drop table t1; +# +# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG +# RESULT AFTER MYSQL 5.1. +# +CREATE TABLE t1( +F1 CHAR(5) NOT NULL, +F2 CHAR(5) NOT NULL, +F3 CHAR(5) NOT NULL, +PRIMARY KEY(F1), +INDEX IDX_F2(F2) +); +INSERT INTO t1 VALUES +('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), +('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); +SELECT * FROM t1 WHERE F1 = 'A '; +F1 F2 F3 +A A A +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; +F1 F2 F3 +A A A +SELECT * FROM t1 WHERE F1 >= 'A '; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F1 > 'A '; +F1 F2 F3 +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND +'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +DROP TABLE t1; End of 5.1 tests # # LP Bug #533117: Wrong use_count in SEL_ARG trees diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index f52bdbdc587..354a4758e7c 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -1770,6 +1770,123 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; pk i4 pk i4 DROP TABLE t1; +# +# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND +# WITH/WITHOUT INDEX RANGE SCAN +# +create table t1 (id int unsigned not null auto_increment primary key); +insert into t1 values (null); +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +create table t2 ( +id int unsigned not null auto_increment, +val decimal(5,3) not null, +primary key (id,val), +unique key (val,id), +unique key (id)); +insert into t2 select null,id*0.0009 from t1; +select count(val) from t2 ignore index (val) where val > 0.1155; +count(val) +128 +select count(val) from t2 force index (val) where val > 0.1155; +count(val) +128 +drop table t2, t1; +# +# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG +# RESULTS WITH DECIMAL CONVERSION +# +create table t1 (a int,b int,c int,primary key (a,c)); +insert into t1 values (1,1,2),(1,1,3),(1,1,4); +select convert(3, signed integer) > 2.9; +convert(3, signed integer) > 2.9 +1 +select * from t1 force index (primary) where a=1 and c>= 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 ignore index (primary) where a=1 and c>= 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 force index (primary) where a=1 and c> 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 ignore index (primary) where a=1 and c> 2.9; +a b c +1 1 3 +1 1 4 +drop table t1; +# +# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG +# RESULT AFTER MYSQL 5.1. +# +CREATE TABLE t1( +F1 CHAR(5) NOT NULL, +F2 CHAR(5) NOT NULL, +F3 CHAR(5) NOT NULL, +PRIMARY KEY(F1), +INDEX IDX_F2(F2) +); +INSERT INTO t1 VALUES +('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), +('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); +SELECT * FROM t1 WHERE F1 = 'A '; +F1 F2 F3 +A A A +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; +F1 F2 F3 +A A A +SELECT * FROM t1 WHERE F1 >= 'A '; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F1 > 'A '; +F1 F2 F3 +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND +'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +DROP TABLE t1; End of 5.1 tests # # LP Bug #533117: Wrong use_count in SEL_ARG trees diff --git a/mysql-test/r/xa.result b/mysql-test/r/xa.result index cc40b96c43f..3799e9417af 100644 --- a/mysql-test/r/xa.result +++ b/mysql-test/r/xa.result @@ -206,8 +206,8 @@ DROP TABLE t1; # FAILED # DROP TABLE IF EXISTS t1, t2; -CREATE TABLE t1 (a INT) engine=innodb; -CREATE TABLE t2 (a INT) engine=innodb; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (a INT) ENGINE=InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1); # Connection con2 @@ -217,12 +217,14 @@ INSERT INTO t2 SELECT a FROM t1; # Connection default # Waiting until INSERT ... is blocked DELETE FROM t1; -COMMIT; # Connection con2 # Reaping: INSERT INTO t2 SELECT a FROM t1 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction XA COMMIT 'xid1'; ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected +# Connection default +COMMIT; +# Connection con2 XA START 'xid1'; XA END 'xid1'; XA PREPARE 'xid1'; diff --git a/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result index 458805a9157..87ffb6cdd9f 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result +++ b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result @@ -54,13 +54,13 @@ USE test; # SET @old_log_warnings = @@log_warnings; DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(10)); +CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(15)); SET GLOBAL LOG_WARNINGS = 0; -INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); +INSERT INTO t1 VALUES(UUID(), 'timestamp'); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. SET GLOBAL LOG_WARNINGS = 1; -INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); +INSERT INTO t1 VALUES(UUID(), 'timestamp'); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test index 78a16945791..8b48c1723fd 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test +++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test @@ -101,16 +101,24 @@ eval USE $old_db; --echo # Bug#46265: Can not disable warning about unsafe statements for binary logging --echo # +let BINLOG_COUNTER1= `select CONVERT(NOW(),UNSIGNED) as timestmap from dual`; + SET @old_log_warnings = @@log_warnings; --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings -CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(10)); +CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(15)); + SET GLOBAL LOG_WARNINGS = 0; -INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); +# Replacing the result file content here. +# Instead of writing $BINLOG_COUNTER1 value to result file, +# writing a fixed string timestamp to it. +--replace_result $BINLOG_COUNTER1 timestamp +eval INSERT INTO t1 VALUES(UUID(), '$BINLOG_COUNTER1'); SET GLOBAL LOG_WARNINGS = 1; -INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); +--replace_result $BINLOG_COUNTER1 timestamp +eval INSERT INTO t1 VALUES(UUID(), '$BINLOG_COUNTER1'); DROP TABLE t1; SET GLOBAL log_warnings = @old_log_warnings; @@ -134,7 +142,9 @@ perl; use Cwd; my $log_error= $ENV{'LOG_ERROR'} or die "LOG_ERROR not set"; open(FILE, "$log_error") or die("Unable to open '$log_error' from directory " . cwd() . "\n"); - my $count = () = grep(/Bug#46265/g,<FILE>); + my $binlog_counter= $ENV{'BINLOG_COUNTER1'} or die "BINLOG_COUNTER1 not set"; + my $count = () = grep(/$binlog_counter/g,<FILE>); + # Grep the timestamp value from the error file. print "Occurrences: $count\n"; close(FILE); EOF @@ -156,7 +166,6 @@ delete from t1 where n2=1 + rand()*0; drop table t1; # bug#50192: diplaying the unsafe warning comes out to the user warning stack - -- disable_warnings DROP TABLE IF EXISTS t1, t2; -- enable_warnings diff --git a/mysql-test/suite/innodb/r/innodb_bug12400341.result b/mysql-test/suite/innodb/r/innodb_bug12400341.result new file mode 100644 index 00000000000..551e94f5e77 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug12400341.result @@ -0,0 +1,25 @@ +call mtr.add_suppression("InnoDB: Warning: cannot find a free slot for an undo log. Do you have too"); +show variables like "max_connections"; +Variable_name Value +max_connections 64 +show variables like "innodb_thread_concurrency"; +Variable_name Value +innodb_thread_concurrency 0 +show variables like "innodb_file_per_table"; +Variable_name Value +innodb_file_per_table ON +drop database if exists mysqltest; +create database mysqltest; +CREATE TABLE mysqltest.transtable (id int unsigned NOT NULL PRIMARY KEY, val int DEFAULT 0) ENGINE=InnoDB; +select count(*) from information_schema.processlist; +count(*) +33 +CREATE TABLE mysqltest.testtable (id int unsigned not null primary key) ENGINE=InnoDB; +ERROR HY000: Can't create table 'mysqltest.testtable' (errno: 177) +select count(*) from information_schema.processlist; +count(*) +33 +select count(*) from information_schema.processlist; +count(*) +33 +drop database mysqltest; diff --git a/mysql-test/suite/innodb/t/innodb-lock.test b/mysql-test/suite/innodb/t/innodb-lock.test index 2c2516557ef..e3026ac505b 100644 --- a/mysql-test/suite/innodb/t/innodb-lock.test +++ b/mysql-test/suite/innodb/t/innodb-lock.test @@ -1,8 +1,4 @@ --source include/have_innodb.inc -if (`select plugin_auth_version <= "1.1.8-20.1" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not supported by XtraDB 1.1.8-20.1 or earlier -} # # Check and select innodb lock type diff --git a/mysql-test/suite/innodb/t/innodb_bug12400341-master.opt b/mysql-test/suite/innodb/t/innodb_bug12400341-master.opt new file mode 100644 index 00000000000..13f480704f5 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug12400341-master.opt @@ -0,0 +1 @@ +--max_connections=64 --innodb_thread_concurrency=0 --innodb_file_per_table --innodb_rollback_segments=2 diff --git a/mysql-test/suite/innodb/t/innodb_bug12400341.test b/mysql-test/suite/innodb/t/innodb_bug12400341.test new file mode 100644 index 00000000000..2ab1be81f6d --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug12400341.test @@ -0,0 +1,103 @@ +# Test for bug #12400341: INNODB CAN LEAVE ORPHAN IBD FILES AROUND + +-- source include/have_innodb.inc + +if (`select count(*)=0 from information_schema.global_variables where variable_name = 'INNODB_TRX_RSEG_N_SLOTS_DEBUG'`) +{ + --skip Test requires InnoDB built with UNIV_DEBUG definition. +} + +call mtr.add_suppression("InnoDB: Warning: cannot find a free slot for an undo log. Do you have too"); + +--disable_query_log +set @old_innodb_trx_rseg_n_slots_debug = @@innodb_trx_rseg_n_slots_debug; +set global innodb_trx_rseg_n_slots_debug = 32; +--enable_query_log + +show variables like "max_connections"; +show variables like "innodb_thread_concurrency"; +show variables like "innodb_file_per_table"; + +--disable_warnings +drop database if exists mysqltest; +--enable_warnings + +create database mysqltest; +CREATE TABLE mysqltest.transtable (id int unsigned NOT NULL PRIMARY KEY, val int DEFAULT 0) ENGINE=InnoDB; + +--disable_query_log +# +# Insert in 1 transaction which needs over 1 page undo record to avoid the insert_undo cached, +# because the cached insert_undo can be reused at "CREATE TABLE" statement later. +# +START TRANSACTION; +let $c = 1024; +while ($c) +{ + eval INSERT INTO mysqltest.transtable (id) VALUES ($c); + dec $c; +} +COMMIT; + +let $c = 32; +while ($c) +{ + # if failed at here, it might be shortage of file descriptors limit. + connect (con$c,localhost,root,,); + dec $c; +} +--enable_query_log + +select count(*) from information_schema.processlist; + +# +# fill the all undo slots +# +--disable_query_log +let $c = 32; +while ($c) +{ + connection con$c; + START TRANSACTION; + eval UPDATE mysqltest.transtable SET val = 1 WHERE id = 33 - $c; + dec $c; +} +--enable_query_log + +connection default; + +--error ER_CANT_CREATE_TABLE +CREATE TABLE mysqltest.testtable (id int unsigned not null primary key) ENGINE=InnoDB; + +select count(*) from information_schema.processlist; + +--disable_query_log +let $c = 32; +while ($c) +{ + connection con$c; + ROLLBACK; + dec $c; +} +--enable_query_log + +connection default; +select count(*) from information_schema.processlist; + +--disable_query_log +let $c = 32; +while ($c) +{ + disconnect con$c; + dec $c; +} +--enable_query_log + +# +# If the isolated .ibd file remained, the drop database should fail. +# +drop database mysqltest; + +--disable_query_log +set global innodb_trx_rseg_n_slots_debug = @old_innodb_trx_rseg_n_slots_debug; +--enable_query_log diff --git a/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test b/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test index c9a0237fa5f..4263e839c85 100644 --- a/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test +++ b/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test @@ -1,8 +1,4 @@ --source include/have_innodb.inc -if (`select plugin_auth_version <= "1.1.8-20.1" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not supported by XtraDB 1.1.8-20.1 or earlier -} let $per_table=`select @@innodb_file_per_table`; let $format=`select @@innodb_file_format`; diff --git a/mysql-test/suite/maria/r/ps_maria.result b/mysql-test/suite/maria/r/ps_maria.result index 1f26f03f479..6c1b40302ec 100644 --- a/mysql-test/suite/maria/r/ps_maria.result +++ b/mysql-test/suite/maria/r/ps_maria.result @@ -1793,7 +1793,7 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) NOT NULL DEFAULT '0.000000', + `const10` decimal(22,6) DEFAULT NULL, `param10` decimal(65,30) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, @@ -1823,7 +1823,7 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 N 32769 6 63 +def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 diff --git a/mysql-test/suite/perfschema/r/all_instances.result b/mysql-test/suite/perfschema/r/all_instances.result index 59b1950cb88..599356fa6ea 100644 --- a/mysql-test/suite/perfschema/r/all_instances.result +++ b/mysql-test/suite/perfschema/r/all_instances.result @@ -71,6 +71,7 @@ wait/synch/mutex/sql/LOG::LOCK_log wait/synch/mutex/sql/LOG_INFO::lock wait/synch/mutex/sql/Master_info::data_lock wait/synch/mutex/sql/Master_info::run_lock +wait/synch/mutex/sql/Master_info::sleep_lock wait/synch/mutex/sql/MDL_map::mutex wait/synch/mutex/sql/MDL_wait::LOCK_wait_status wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index @@ -79,6 +80,7 @@ wait/synch/mutex/sql/Query_cache::structure_guard_mutex wait/synch/mutex/sql/Relay_log_info::data_lock wait/synch/mutex/sql/Relay_log_info::log_space_lock wait/synch/mutex/sql/Relay_log_info::run_lock +wait/synch/mutex/sql/Relay_log_info::sleep_lock wait/synch/mutex/sql/Slave_reporting_capability::err_lock wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data wait/synch/mutex/sql/THD::LOCK_thd_data @@ -120,6 +122,7 @@ wait/synch/cond/sql/COND_thread_cache wait/synch/cond/sql/COND_thread_count wait/synch/cond/sql/Event_scheduler::COND_state wait/synch/cond/sql/Master_info::data_cond +wait/synch/cond/sql/Master_info::sleep_cond wait/synch/cond/sql/Master_info::start_cond wait/synch/cond/sql/Master_info::stop_cond wait/synch/cond/sql/MDL_context::COND_wait_status @@ -130,6 +133,7 @@ wait/synch/cond/sql/MYSQL_RELAY_LOG::update_cond wait/synch/cond/sql/Query_cache::COND_cache_status_changed wait/synch/cond/sql/Relay_log_info::data_cond wait/synch/cond/sql/Relay_log_info::log_space_cond +wait/synch/cond/sql/Relay_log_info::sleep_cond wait/synch/cond/sql/Relay_log_info::start_cond wait/synch/cond/sql/Relay_log_info::stop_cond wait/synch/cond/sql/THD::COND_wakeup_ready diff --git a/mysql-test/suite/rpl/r/rpl_cant_read_event_incident.result b/mysql-test/suite/rpl/r/rpl_cant_read_event_incident.result index bcae1c2c62b..c132f80f58e 100644 --- a/mysql-test/suite/rpl/r/rpl_cant_read_event_incident.result +++ b/mysql-test/suite/rpl/r/rpl_cant_read_event_incident.result @@ -11,7 +11,7 @@ reset slave; start slave; include/wait_for_slave_param.inc [Last_IO_Errno] Last_IO_Errno = '1236' -Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the last event was read from 'master-bin.000001' at 316, the last byte read was read from 'master-bin.000001' at 335.'' +Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the start event position from '.' at XXX, the last event was read from 'master-bin.000001' at XXX, the last byte read was read from 'master-bin.000001' at XXX.'' reset master; stop slave; reset slave; diff --git a/mysql-test/suite/rpl/r/rpl_checksum.result b/mysql-test/suite/rpl/r/rpl_checksum.result index 3297e8c2754..feabfc19631 100644 --- a/mysql-test/suite/rpl/r/rpl_checksum.result +++ b/mysql-test/suite/rpl/r/rpl_checksum.result @@ -71,7 +71,7 @@ insert into t1 values (1) /* will not be applied on slave due to simulation */; set @@global.debug_dbug='d,simulate_slave_unaware_checksum'; start slave; include/wait_for_slave_io_error.inc [errno=1236] -Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the last event was read from 'master-bin.000010' at 245, the last byte read was read from 'master-bin.000010' at 245.'' +Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the start event position from 'master-bin.000009' at 245, the last event was read from 'master-bin.000010' at 245, the last byte read was read from 'master-bin.000010' at 245.'' select count(*) as zero from t1; zero 0 diff --git a/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result b/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result index 29b5b768b66..cc9d1f99f7c 100644 --- a/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result +++ b/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result @@ -270,6 +270,7 @@ Heartbeat event received include/rpl_reset.inc include/stop_slave.inc include/rpl_change_topology.inc [new topology=1->2->1] +CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD=1; include/start_slave.inc CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(10)); INSERT INTO t1 VALUES(1, 'on master'); diff --git a/mysql-test/suite/rpl/r/rpl_log_pos.result b/mysql-test/suite/rpl/r/rpl_log_pos.result index a7687dd3e73..941dba2f5af 100644 --- a/mysql-test/suite/rpl/r/rpl_log_pos.result +++ b/mysql-test/suite/rpl/r/rpl_log_pos.result @@ -11,6 +11,7 @@ include/stop_slave.inc change master to master_log_pos=MASTER_LOG_POS; start slave; include/wait_for_slave_io_error.inc [errno=1236] +Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the start event position from 'master-bin.000001' at XXX, the last event was read from 'master-bin.000001' at XXX, the last byte read was read from 'master-bin.000001' at XXX.'' include/stop_slave_sql.inc show master status; File Position Binlog_Do_DB Binlog_Ignore_DB diff --git a/mysql-test/suite/rpl/r/rpl_manual_change_index_file.result b/mysql-test/suite/rpl/r/rpl_manual_change_index_file.result index a2dc5d402a7..efe9bf076f0 100644 --- a/mysql-test/suite/rpl/r/rpl_manual_change_index_file.result +++ b/mysql-test/suite/rpl/r/rpl_manual_change_index_file.result @@ -5,6 +5,7 @@ CREATE TABLE t1(c1 INT); FLUSH LOGS; call mtr.add_suppression('Got fatal error 1236 from master when reading data from binary log: .*could not find next log'); include/wait_for_slave_io_error.inc [errno=1236] +Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the start event position from 'master-bin.000001' at XXX, the last event was read from 'master-bin.000002' at XXX, the last byte read was read from 'master-bin.000002' at XXX.'' CREATE TABLE t2(c1 INT); FLUSH LOGS; CREATE TABLE t3(c1 INT); diff --git a/mysql-test/suite/rpl/r/rpl_packet.result b/mysql-test/suite/rpl/r/rpl_packet.result index 27f85cac7de..4b2a525de27 100644 --- a/mysql-test/suite/rpl/r/rpl_packet.result +++ b/mysql-test/suite/rpl/r/rpl_packet.result @@ -37,6 +37,7 @@ DROP TABLE t1; CREATE TABLE t1 (f1 int PRIMARY KEY, f2 LONGTEXT, f3 LONGTEXT) ENGINE=MyISAM; INSERT INTO t1(f1, f2, f3) VALUES(1, REPEAT('a', @@global.max_allowed_packet), REPEAT('b', @@global.max_allowed_packet)); include/wait_for_slave_io_error.inc [errno=1236] +Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the start event position from '.' at XXX, the last event was read from 'master-bin.000001' at XXX, the last byte read was read from 'master-bin.000001' at XXX.'' STOP SLAVE; RESET SLAVE; RESET MASTER; diff --git a/mysql-test/suite/rpl/r/rpl_start_stop_slave.result b/mysql-test/suite/rpl/r/rpl_start_stop_slave.result new file mode 100644 index 00000000000..43a7712bdf1 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_stop_slave.result @@ -0,0 +1,10 @@ +include/master-slave.inc +[connection master] +set @time_before_kill := (select CURRENT_TIMESTAMP); +[Time before the query] +[Connection ID of the slave I/O thread found] +kill <connection_id>; +set @time_after_kill := (select CURRENT_TIMESTAMP); +[Time after the query] +[Killing of the slave IO thread was successful] +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_stm_start_stop_slave.result b/mysql-test/suite/rpl/r/rpl_stm_start_stop_slave.result index 57206873e2f..8bf903b0713 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_start_stop_slave.result +++ b/mysql-test/suite/rpl/r/rpl_stm_start_stop_slave.result @@ -69,6 +69,16 @@ include/wait_for_slave_to_stop.inc include/start_slave.inc # Clean up DROP TABLE t1; +RESET MASTER; +include/stop_slave.inc +CHANGE MASTER TO master_log_pos=MASTER_POS; +START SLAVE; +include/wait_for_slave_param.inc [Last_IO_Errno] +Last_IO_Errno = '1236' +Last_IO_Error = 'Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the start event position from 'master-bin.000001' at XXX, the last event was read from 'master-bin.000001' at XXX, the last byte read was read from 'master-bin.000001' at XXX.'' +include/stop_slave.inc +RESET SLAVE; +RESET MASTER; SET @@global.innodb_flush_log_at_trx_commit= @old_innodb_flush_log_at_trx_commit; call mtr.add_suppression("Slave SQL.*Request to stop slave SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group"); SET @@global.innodb_flush_log_at_trx_commit= @old_innodb_flush_log_at_trx_commit; diff --git a/mysql-test/suite/rpl/t/rpl_cant_read_event_incident.test b/mysql-test/suite/rpl/t/rpl_cant_read_event_incident.test index 7d05ce251f2..4825090d6cd 100644 --- a/mysql-test/suite/rpl/t/rpl_cant_read_event_incident.test +++ b/mysql-test/suite/rpl/t/rpl_cant_read_event_incident.test @@ -14,11 +14,6 @@ --source include/master-slave.inc --source include/have_binlog_format_mixed.inc -# -# Bug#13050593 swallows `\' from Last_IO_Error -# todo: uncomment the filter once the bug is fixed. -# ---source include/not_windows.inc --connection slave # Make sure the slave is stopped while we are messing with master. @@ -56,6 +51,7 @@ start slave; --let $slave_param_value=1236 --source include/wait_for_slave_param.inc +--let $slave_field_result_replace= / at [0-9]*/ at XXX/ --let $status_items= Last_IO_Errno, Last_IO_Error --source include/show_slave_status.inc diff --git a/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test b/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test index b9a170c5bc1..78489449ab8 100644 --- a/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test +++ b/mysql-test/suite/rpl/t/rpl_heartbeat_basic.test @@ -480,6 +480,12 @@ let $status_var_comparsion= >; --connection master #--replace_result $SLAVE_MYPORT SLAVE_PORT $slave_binlog SLAVE_BINLOG #eval CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=$SLAVE_MYPORT, MASTER_USER='root', MASTER_CONNECT_RETRY=$connect_retry, MASTER_HEARTBEAT_PERIOD=1, MASTER_LOG_FILE='$slave_binlog'; + +# BUG#12403008 RPL_HEARTBEAT_BASIC FAILS SPORADICALLY ON PUSHBUILD +# MASTER_HEARTBEAT_PERIOD had the default value (slave_net_timeout/2) +# so wait on "Heartbeat event received on master", that only waits for +# 1 minute, sometimes timeout before heartbeat arrives. +CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD=1; --source include/start_slave.inc # Insert data on master and on slave and make sure that it replicated for both directions diff --git a/mysql-test/suite/rpl/t/rpl_log_pos.test b/mysql-test/suite/rpl/t/rpl_log_pos.test index 43f54bd3705..1270933ede1 100644 --- a/mysql-test/suite/rpl/t/rpl_log_pos.test +++ b/mysql-test/suite/rpl/t/rpl_log_pos.test @@ -30,12 +30,9 @@ let $wrong_log_pos= `SELECT $read_pos+2`; eval change master to master_log_pos=$wrong_log_pos; start slave; let $slave_io_errno= 1236; -# -# Win and Unix path is printed differently: BUG#13055685. So -# show_slave_io_error is made 0 until the bug fixes provide necessary -# facilities -# -let $show_slave_io_error= 0; +--let $show_slave_io_error= 1 +# Mask line numbers +--let $slave_io_error_replace= / at [0-9]*/ at XXX/ source include/wait_for_slave_io_error.inc; source include/stop_slave_sql.inc; --enable_warnings diff --git a/mysql-test/suite/rpl/t/rpl_manual_change_index_file.test b/mysql-test/suite/rpl/t/rpl_manual_change_index_file.test index 9b648de8486..981cecb66ad 100644 --- a/mysql-test/suite/rpl/t/rpl_manual_change_index_file.test +++ b/mysql-test/suite/rpl/t/rpl_manual_change_index_file.test @@ -60,12 +60,9 @@ call mtr.add_suppression('Got fatal error 1236 from master when reading data fro connection slave; # 1236 = ER_MASTER_FATAL_ERROR_READING_BINLOG --let $slave_io_errno= 1236 -# -# Win and Unix path is printed differently: BUG#13055685. So -# show_slave_io_error is made 0 until the bug fixes provide necessary -# facilities -# ---let $show_slave_io_error= 0 +--let $show_slave_io_error= 1 +# Mask line numbers +--let $slave_io_error_replace= / at [0-9]*/ at XXX/ --source include/wait_for_slave_io_error.inc connection master; diff --git a/mysql-test/suite/rpl/t/rpl_packet.test b/mysql-test/suite/rpl/t/rpl_packet.test index 2b8a1fd8310..4f296fed68e 100644 --- a/mysql-test/suite/rpl/t/rpl_packet.test +++ b/mysql-test/suite/rpl/t/rpl_packet.test @@ -125,12 +125,9 @@ connection slave; # The slave I/O thread must stop after receiving # 1236=ER_MASTER_FATAL_ERROR_READING_BINLOG error message from master. --let $slave_io_errno= 1236 -# -# Win and Unix path is printed differently: BUG#13055685. So -# show_slave_io_error is made 0 until the bug fixes provide necessary -# facilities -# ---let $show_slave_io_error= 0 + +# Mask line numbers +--let $slave_io_error_replace= / at [0-9]*/ at XXX/ --source include/wait_for_slave_io_error.inc # Remove the bad binlog and clear error status on slave. diff --git a/mysql-test/suite/rpl/t/rpl_start_stop_slave.test b/mysql-test/suite/rpl/t/rpl_start_stop_slave.test new file mode 100644 index 00000000000..69d39a066b1 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_stop_slave.test @@ -0,0 +1,44 @@ +# +#BUG#11752315 : STOP SLAVE UNABLE TO COMPLETE WHEN SLAVE THREAD IS TRYING +# TO RECONNECT TO +# +# ==== Purpose ==== +# +#Tests that the slave does not go to a sleep for a long duration after the +#master is killed and we do a START_SLAVE and STOP_SLAVE. +# +# ==== Method ==== +# +#This is a new functionality of having an interruptable sleep of the slave. +#We find the thread id for the slave thread. On finding the thread ID of the +#slave thread we kill the slave thread. A successful kill in less than 60 sec +#should serve the purpose of checking the functionality. +# + +--source include/have_log_bin.inc +--source include/master-slave.inc + +connection slave; +--let $connection_id=`SELECT id FROM information_schema.processlist where state LIKE 'Waiting for master to send event'` + +set @time_before_kill := (select CURRENT_TIMESTAMP); + +--echo [Time before the query] +--echo [Connection ID of the slave I/O thread found] + +--replace_regex /kill [0-9]*/kill <connection_id>/ +--eval kill $connection_id + +set @time_after_kill := (select CURRENT_TIMESTAMP); + +--echo [Time after the query] + +if(`select TIMESTAMPDIFF(SECOND,@time_after_kill, @time_before_kill) > 60`) +{ +--echo # assert : The difference between the timestamps 'time_after_kill' and 'time_before_kill' should be less than 60sec. +--die +} + +--echo [Killing of the slave IO thread was successful] +# End of test +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_stm_start_stop_slave.test b/mysql-test/suite/rpl/t/rpl_stm_start_stop_slave.test index ada080e9071..dfe2e49bb90 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_start_stop_slave.test +++ b/mysql-test/suite/rpl/t/rpl_stm_start_stop_slave.test @@ -22,5 +22,5 @@ SET @@global.innodb_flush_log_at_trx_commit= @old_innodb_flush_log_at_trx_commit call mtr.add_suppression("Slave SQL.*Request to stop slave SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group"); --connection master SET @@global.innodb_flush_log_at_trx_commit= @old_innodb_flush_log_at_trx_commit; - +--let $rpl_only_running_threads= 1 --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 50720052513..1bd4e394f6a 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -3,7 +3,6 @@ create table t2 (variable_name text); load data infile "MYSQLTEST_VARDIR/tmp/sys_vars.all_vars.txt" into table t1; insert into t2 select variable_name from information_schema.global_variables; insert into t2 select variable_name from information_schema.session_variables; -delete from t2 where variable_name='innodb_change_buffering_debug'; update t2 set variable_name= replace(variable_name, "PERFORMANCE_SCHEMA_", "PFS_"); select distinct variable_name as `there should be *no* long test name listed below:` from t2 where length(variable_name) > 50; diff --git a/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result b/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result index c04e5dac199..ef8ec0cd9b8 100644 --- a/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result +++ b/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result @@ -1,20 +1,20 @@ select @@global.character_sets_dir; @@global.character_sets_dir -MYSQL_CHARSETSDIR/ +MYSQL_CHARSETSDIR select @@session.character_sets_dir; ERROR HY000: Variable 'character_sets_dir' is a GLOBAL variable show global variables like 'character_sets_dir'; Variable_name Value -character_sets_dir MYSQL_CHARSETSDIR/ +character_sets_dir MYSQL_CHARSETSDIR show session variables like 'character_sets_dir'; Variable_name Value -character_sets_dir MYSQL_CHARSETSDIR/ +character_sets_dir MYSQL_CHARSETSDIR select * from information_schema.global_variables where variable_name='character_sets_dir'; VARIABLE_NAME VARIABLE_VALUE -CHARACTER_SETS_DIR MYSQL_CHARSETSDIR/ +CHARACTER_SETS_DIR MYSQL_CHARSETSDIR select * from information_schema.session_variables where variable_name='character_sets_dir'; VARIABLE_NAME VARIABLE_VALUE -CHARACTER_SETS_DIR MYSQL_CHARSETSDIR/ +CHARACTER_SETS_DIR MYSQL_CHARSETSDIR set global character_sets_dir="foo"; ERROR HY000: Variable 'character_sets_dir' is a read only variable set session character_sets_dir="foo"; diff --git a/mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result b/mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result new file mode 100644 index 00000000000..2b74f891050 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result @@ -0,0 +1,64 @@ +SET @start_global_value = @@global.innodb_change_buffering_debug; +SELECT @start_global_value; +@start_global_value +0 +select @@global.innodb_change_buffering_debug in (0, 1); +@@global.innodb_change_buffering_debug in (0, 1) +1 +select @@global.innodb_change_buffering_debug; +@@global.innodb_change_buffering_debug +0 +select @@session.innodb_change_buffering_debug; +ERROR HY000: Variable 'innodb_change_buffering_debug' is a GLOBAL variable +show global variables like 'innodb_change_buffering_debug'; +Variable_name Value +innodb_change_buffering_debug 0 +show session variables like 'innodb_change_buffering_debug'; +Variable_name Value +innodb_change_buffering_debug 0 +select * from information_schema.global_variables where variable_name='innodb_change_buffering_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_CHANGE_BUFFERING_DEBUG 0 +select * from information_schema.session_variables where variable_name='innodb_change_buffering_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_CHANGE_BUFFERING_DEBUG 0 +set global innodb_change_buffering_debug=1; +select @@global.innodb_change_buffering_debug; +@@global.innodb_change_buffering_debug +1 +select * from information_schema.global_variables where variable_name='innodb_change_buffering_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_CHANGE_BUFFERING_DEBUG 1 +select * from information_schema.session_variables where variable_name='innodb_change_buffering_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_CHANGE_BUFFERING_DEBUG 1 +set @@global.innodb_change_buffering_debug=0; +select @@global.innodb_change_buffering_debug; +@@global.innodb_change_buffering_debug +0 +select * from information_schema.global_variables where variable_name='innodb_change_buffering_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_CHANGE_BUFFERING_DEBUG 0 +select * from information_schema.session_variables where variable_name='innodb_change_buffering_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_CHANGE_BUFFERING_DEBUG 0 +set session innodb_change_buffering_debug='some'; +ERROR HY000: Variable 'innodb_change_buffering_debug' is a GLOBAL variable and should be set with SET GLOBAL +set @@session.innodb_change_buffering_debug='some'; +ERROR HY000: Variable 'innodb_change_buffering_debug' is a GLOBAL variable and should be set with SET GLOBAL +set global innodb_change_buffering_debug=1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_change_buffering_debug' +set global innodb_change_buffering_debug='foo'; +ERROR 42000: Incorrect argument type to variable 'innodb_change_buffering_debug' +set global innodb_change_buffering_debug=-2; +Warnings: +Warning 1292 Truncated incorrect innodb_change_buffering_debug value: '-2' +set global innodb_change_buffering_debug=1e1; +ERROR 42000: Incorrect argument type to variable 'innodb_change_buffering_debug' +set global innodb_change_buffering_debug=2; +Warnings: +Warning 1292 Truncated incorrect innodb_change_buffering_debug value: '2' +SET @@global.innodb_change_buffering_debug = @start_global_value; +SELECT @@global.innodb_change_buffering_debug; +@@global.innodb_change_buffering_debug +0 diff --git a/mysql-test/suite/sys_vars/r/innodb_trx_rseg_n_slots_debug_basic.result b/mysql-test/suite/sys_vars/r/innodb_trx_rseg_n_slots_debug_basic.result new file mode 100644 index 00000000000..7e253c869e3 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_trx_rseg_n_slots_debug_basic.result @@ -0,0 +1,65 @@ +SET @start_global_value = @@global.innodb_trx_rseg_n_slots_debug; +SELECT @start_global_value; +@start_global_value +0 +select @@global.innodb_trx_rseg_n_slots_debug between 0 and 1024; +@@global.innodb_trx_rseg_n_slots_debug between 0 and 1024 +1 +select @@global.innodb_trx_rseg_n_slots_debug; +@@global.innodb_trx_rseg_n_slots_debug +0 +select @@session.innodb_trx_rseg_n_slots_debug; +ERROR HY000: Variable 'innodb_trx_rseg_n_slots_debug' is a GLOBAL variable +show global variables like 'innodb_trx_rseg_n_slots_debug'; +Variable_name Value +innodb_trx_rseg_n_slots_debug 0 +show session variables like 'innodb_trx_rseg_n_slots_debug'; +Variable_name Value +innodb_trx_rseg_n_slots_debug 0 +select * from information_schema.global_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_TRX_RSEG_N_SLOTS_DEBUG 0 +select * from information_schema.session_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_TRX_RSEG_N_SLOTS_DEBUG 0 +set global innodb_trx_rseg_n_slots_debug=1; +select @@global.innodb_trx_rseg_n_slots_debug; +@@global.innodb_trx_rseg_n_slots_debug +1 +select * from information_schema.global_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_TRX_RSEG_N_SLOTS_DEBUG 1 +select * from information_schema.session_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_TRX_RSEG_N_SLOTS_DEBUG 1 +set @@global.innodb_trx_rseg_n_slots_debug=0; +select @@global.innodb_trx_rseg_n_slots_debug; +@@global.innodb_trx_rseg_n_slots_debug +0 +select * from information_schema.global_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_TRX_RSEG_N_SLOTS_DEBUG 0 +select * from information_schema.session_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_TRX_RSEG_N_SLOTS_DEBUG 0 +set session innodb_trx_rseg_n_slots_debug='some'; +ERROR HY000: Variable 'innodb_trx_rseg_n_slots_debug' is a GLOBAL variable and should be set with SET GLOBAL +set @@session.innodb_trx_rseg_n_slots_debug='some'; +ERROR HY000: Variable 'innodb_trx_rseg_n_slots_debug' is a GLOBAL variable and should be set with SET GLOBAL +set global innodb_trx_rseg_n_slots_debug=1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_trx_rseg_n_slots_debug' +set global innodb_trx_rseg_n_slots_debug='foo'; +ERROR 42000: Incorrect argument type to variable 'innodb_trx_rseg_n_slots_debug' +set global innodb_trx_rseg_n_slots_debug=-2; +Warnings: +Warning 1292 Truncated incorrect innodb_trx_rseg_n_slots_debug value: '-2' +set global innodb_trx_rseg_n_slots_debug=1e1; +ERROR 42000: Incorrect argument type to variable 'innodb_trx_rseg_n_slots_debug' +set global innodb_trx_rseg_n_slots_debug=1024; +set global innodb_trx_rseg_n_slots_debug=1025; +Warnings: +Warning 1292 Truncated incorrect innodb_trx_rseg_n_slots_debug value: '1025' +SET @@global.innodb_trx_rseg_n_slots_debug = @start_global_value; +SELECT @@global.innodb_trx_rseg_n_slots_debug; +@@global.innodb_trx_rseg_n_slots_debug +0 diff --git a/mysql-test/suite/sys_vars/r/plugin_dir_basic.result b/mysql-test/suite/sys_vars/r/plugin_dir_basic.result index a5f36de73fa..e2c03bcad6a 100644 --- a/mysql-test/suite/sys_vars/r/plugin_dir_basic.result +++ b/mysql-test/suite/sys_vars/r/plugin_dir_basic.result @@ -1,20 +1,20 @@ select @@global.plugin_dir; @@global.plugin_dir -MYSQL_TMP_DIR +MYSQL_TMP_DIR/ select @@session.plugin_dir; ERROR HY000: Variable 'plugin_dir' is a GLOBAL variable show global variables like 'plugin_dir'; Variable_name Value -plugin_dir MYSQL_TMP_DIR +plugin_dir MYSQL_TMP_DIR/ show session variables like 'plugin_dir'; Variable_name Value -plugin_dir MYSQL_TMP_DIR +plugin_dir MYSQL_TMP_DIR/ select * from information_schema.global_variables where variable_name='plugin_dir'; VARIABLE_NAME VARIABLE_VALUE -PLUGIN_DIR MYSQL_TMP_DIR +PLUGIN_DIR MYSQL_TMP_DIR/ select * from information_schema.session_variables where variable_name='plugin_dir'; VARIABLE_NAME VARIABLE_VALUE -PLUGIN_DIR MYSQL_TMP_DIR +PLUGIN_DIR MYSQL_TMP_DIR/ set global plugin_dir=1; ERROR HY000: Variable 'plugin_dir' is a read only variable set session plugin_dir=1; diff --git a/mysql-test/suite/sys_vars/r/stored_program_cache_basic.result b/mysql-test/suite/sys_vars/r/stored_program_cache_basic.result new file mode 100644 index 00000000000..f1638520f72 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/stored_program_cache_basic.result @@ -0,0 +1,59 @@ +# Saving initial value of stored_program_cache in a temporary variable +SET @start_value = @@global.stored_program_cache; +SELECT @start_value; +@start_value +256 +# Display the DEFAULT value of stored_program_cache +SET @@global.stored_program_cache = DEFAULT; +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 +# Verify default value of variable +SELECT @@global.stored_program_cache = 256; +@@global.stored_program_cache = 256 +1 +# Change the value of stored_program_cache to a valid value +SET @@global.stored_program_cache = 512; +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +512 +# Change the value of stored_program_cache to invalid value +SET @@global.stored_program_cache = -1; +Warnings: +Warning 1292 Truncated incorrect stored_program_cache value: '-1' +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 +SET @@global.stored_program_cache =100000000000; +Warnings: +Warning 1292 Truncated incorrect stored_program_cache value: '100000000000' +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +524288 +SET @@global.stored_program_cache = 0; +Warnings: +Warning 1292 Truncated incorrect stored_program_cache value: '0' +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 +SET @@global.stored_program_cache = 10000.01; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +SET @@global.stored_program_cache = ON; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +SET @@global.stored_program_cache= 'test'; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +SET @@global.stored_program_cache = ''; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +# Test if accessing session stored_program_cache gives error +SET @@session.stored_program_cache = 0; +ERROR HY000: Variable 'stored_program_cache' is a GLOBAL variable and should be set with SET GLOBAL +# Check if accessing variable without SCOPE points to same global variable +SET @@global.stored_program_cache = 512; +SELECT @@stored_program_cache = @@global.stored_program_cache; +@@stored_program_cache = @@global.stored_program_cache +1 +# Restore initial value +SET @@global.stored_program_cache = @start_value; +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 diff --git a/mysql-test/suite/sys_vars/t/all_vars.test b/mysql-test/suite/sys_vars/t/all_vars.test index 8f59af0f47d..7b9f69bd7d1 100644 --- a/mysql-test/suite/sys_vars/t/all_vars.test +++ b/mysql-test/suite/sys_vars/t/all_vars.test @@ -47,9 +47,6 @@ eval load data infile "$MYSQLTEST_VARDIR/tmp/sys_vars.all_vars.txt" into table t insert into t2 select variable_name from information_schema.global_variables; insert into t2 select variable_name from information_schema.session_variables; -# This is only present in debug builds. -delete from t2 where variable_name='innodb_change_buffering_debug'; - # Performance schema variables are too long for files named # 'mysql-test/suite/sys_vars/t/' ... # ... 'performance_schema_events_waits_history_long_size_basic-master.opt' diff --git a/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test b/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test index 0bbfd04133d..bf21db1c7dc 100644 --- a/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test +++ b/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test @@ -2,17 +2,26 @@ # # show the global and session values; # ---replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR + +# workaround to adjust for the directory separators being different in +# different OSes : slash on unixes and backslash or slash on windows. +# TODO: fix with a proper comparison in mysqltest +let $rcd= `SELECT REPLACE('$MYSQL_CHARSETSDIR', '\\\\\', '.')`; +let $rcd= `SELECT REPLACE('$rcd', '/', '.')`; +let $regex_charsetdir= `SELECT '/$rcd[\\\\\/\\\\\]/MYSQL_CHARSETSDIR/'`; + +--replace_regex $regex_charsetdir select @@global.character_sets_dir; + --error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@session.character_sets_dir; ---replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR +--replace_regex $regex_charsetdir show global variables like 'character_sets_dir'; ---replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR +--replace_regex $regex_charsetdir show session variables like 'character_sets_dir'; ---replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR +--replace_regex $regex_charsetdir select * from information_schema.global_variables where variable_name='character_sets_dir'; ---replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR +--replace_regex $regex_charsetdir select * from information_schema.session_variables where variable_name='character_sets_dir'; # diff --git a/mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test b/mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test new file mode 100644 index 00000000000..ec1065a538e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test @@ -0,0 +1,52 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +SET @start_global_value = @@global.innodb_change_buffering_debug; +SELECT @start_global_value; + +# +# exists as global only +# +select @@global.innodb_change_buffering_debug in (0, 1); +select @@global.innodb_change_buffering_debug; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.innodb_change_buffering_debug; +show global variables like 'innodb_change_buffering_debug'; +show session variables like 'innodb_change_buffering_debug'; +select * from information_schema.global_variables where variable_name='innodb_change_buffering_debug'; +select * from information_schema.session_variables where variable_name='innodb_change_buffering_debug'; + +# +# show that it's writable +# +set global innodb_change_buffering_debug=1; +select @@global.innodb_change_buffering_debug; +select * from information_schema.global_variables where variable_name='innodb_change_buffering_debug'; +select * from information_schema.session_variables where variable_name='innodb_change_buffering_debug'; +set @@global.innodb_change_buffering_debug=0; +select @@global.innodb_change_buffering_debug; +select * from information_schema.global_variables where variable_name='innodb_change_buffering_debug'; +select * from information_schema.session_variables where variable_name='innodb_change_buffering_debug'; +--error ER_GLOBAL_VARIABLE +set session innodb_change_buffering_debug='some'; +--error ER_GLOBAL_VARIABLE +set @@session.innodb_change_buffering_debug='some'; + +# +# incorrect types +# +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_change_buffering_debug=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_change_buffering_debug='foo'; +set global innodb_change_buffering_debug=-2; +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_change_buffering_debug=1e1; +set global innodb_change_buffering_debug=2; + +# +# Cleanup +# + +SET @@global.innodb_change_buffering_debug = @start_global_value; +SELECT @@global.innodb_change_buffering_debug; diff --git a/mysql-test/suite/sys_vars/t/innodb_trx_rseg_n_slots_debug_basic.test b/mysql-test/suite/sys_vars/t/innodb_trx_rseg_n_slots_debug_basic.test new file mode 100644 index 00000000000..11c6dd7820b --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_trx_rseg_n_slots_debug_basic.test @@ -0,0 +1,58 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +if (`select plugin_auth_version <= "1.1.8-24.1" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not supported by XtraDB 1.1.8-24.1 or earlier +} + +SET @start_global_value = @@global.innodb_trx_rseg_n_slots_debug; +SELECT @start_global_value; + +# +# exists as global only +# +select @@global.innodb_trx_rseg_n_slots_debug between 0 and 1024; +select @@global.innodb_trx_rseg_n_slots_debug; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.innodb_trx_rseg_n_slots_debug; +show global variables like 'innodb_trx_rseg_n_slots_debug'; +show session variables like 'innodb_trx_rseg_n_slots_debug'; +select * from information_schema.global_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +select * from information_schema.session_variables where variable_name='innodb_trx_rseg_n_slots_debug'; + +# +# show that it's writable +# +set global innodb_trx_rseg_n_slots_debug=1; +select @@global.innodb_trx_rseg_n_slots_debug; +select * from information_schema.global_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +select * from information_schema.session_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +set @@global.innodb_trx_rseg_n_slots_debug=0; +select @@global.innodb_trx_rseg_n_slots_debug; +select * from information_schema.global_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +select * from information_schema.session_variables where variable_name='innodb_trx_rseg_n_slots_debug'; +--error ER_GLOBAL_VARIABLE +set session innodb_trx_rseg_n_slots_debug='some'; +--error ER_GLOBAL_VARIABLE +set @@session.innodb_trx_rseg_n_slots_debug='some'; + +# +# incorrect types +# +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_trx_rseg_n_slots_debug=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_trx_rseg_n_slots_debug='foo'; +set global innodb_trx_rseg_n_slots_debug=-2; +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_trx_rseg_n_slots_debug=1e1; +set global innodb_trx_rseg_n_slots_debug=1024; +set global innodb_trx_rseg_n_slots_debug=1025; + +# +# Cleanup +# + +SET @@global.innodb_trx_rseg_n_slots_debug = @start_global_value; +SELECT @@global.innodb_trx_rseg_n_slots_debug; diff --git a/mysql-test/suite/sys_vars/t/stored_program_cache_basic.test b/mysql-test/suite/sys_vars/t/stored_program_cache_basic.test new file mode 100644 index 00000000000..9a9ba2ebf81 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/stored_program_cache_basic.test @@ -0,0 +1,59 @@ +# Variable Name: stored_program_cache +# Scope: GLOBAL +# Access Type: Dynamic +# Data Type: numeric +# Default Value: 256 +# Range: 256-524288 + +--source include/load_sysvars.inc + +--echo # Saving initial value of stored_program_cache in a temporary variable +SET @start_value = @@global.stored_program_cache; +SELECT @start_value; + +--echo # Display the DEFAULT value of stored_program_cache +SET @@global.stored_program_cache = DEFAULT; +SELECT @@global.stored_program_cache; + +--echo # Verify default value of variable +SELECT @@global.stored_program_cache = 256; + +--echo # Change the value of stored_program_cache to a valid value +SET @@global.stored_program_cache = 512; +SELECT @@global.stored_program_cache; + +--echo # Change the value of stored_program_cache to invalid value +SET @@global.stored_program_cache = -1; +SELECT @@global.stored_program_cache; + +SET @@global.stored_program_cache =100000000000; +SELECT @@global.stored_program_cache; + +SET @@global.stored_program_cache = 0; +SELECT @@global.stored_program_cache; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache = 10000.01; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache = ON; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache= 'test'; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache = ''; + +--echo # Test if accessing session stored_program_cache gives error + +--Error ER_GLOBAL_VARIABLE +SET @@session.stored_program_cache = 0; + +--echo # Check if accessing variable without SCOPE points to same global variable + +SET @@global.stored_program_cache = 512; +SELECT @@stored_program_cache = @@global.stored_program_cache; + +--echo # Restore initial value + +SET @@global.stored_program_cache = @start_value; +SELECT @@global.stored_program_cache; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index e19bba971f9..41f33b8a7f2 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -361,4 +361,51 @@ DROP TABLE t1; --echo # End of 5.1 tests +--echo # +--echo # Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON +--echo # + +CREATE TABLE t_bigint(id BIGINT); +INSERT INTO t_bigint VALUES (1), (2); + +SELECT id, id >= 1.1 FROM t_bigint; +SELECT id, 1.1 <= id FROM t_bigint; + +SELECT id, id = 1.1 FROM t_bigint; +SELECT id, 1.1 = id FROM t_bigint; + +SELECT * from t_bigint WHERE id = 1.1; +SELECT * from t_bigint WHERE id = 1.1e0; +SELECT * from t_bigint WHERE id = '1.1'; +SELECT * from t_bigint WHERE id = '1.1e0'; + +SELECT * from t_bigint WHERE id IN (1.1, 2.2); +SELECT * from t_bigint WHERE id IN (1.1e0, 2.2e0); +SELECT * from t_bigint WHERE id IN ('1.1', '2.2'); +SELECT * from t_bigint WHERE id IN ('1.1e0', '2.2e0'); + +SELECT * from t_bigint WHERE id BETWEEN 1.1 AND 1.9; +SELECT * from t_bigint WHERE id BETWEEN 1.1e0 AND 1.9e0; +SELECT * from t_bigint WHERE id BETWEEN '1.1' AND '1.9'; +SELECT * from t_bigint WHERE id BETWEEN '1.1e0' AND '1.9e0'; +DROP TABLE t_bigint; + +--echo # +--echo # Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2 +--echo # + +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (1); + +# a. These queries correctly return 0 rows: +SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9; +SELECT * FROM t1 WHERE coalesce(a)=0.9; +SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9); + +# b. These queries mistakenely returned 1 row: +SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9; +SELECT * FROM t1 WHERE a=0.9; +SELECT * FROM t1 WHERE a IN (0.8,0.9); + +DROP TABLE t1; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index b123902f605..210589adc81 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1464,6 +1464,14 @@ SELECT HEX(LPAD(_utf8 0xD18F, 3, 0x20)); SELECT HEX(INSERT(_utf8 0xD18F, 2, 1, 0x20)); SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)); +--echo # +--echo # Bug#11752408 - 43593: DUMP/BACKUP/RESTORE/UPGRADE TOOLS FAILS BECAUSE OF UTF8_GENERAL_CI +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci); +INSERT INTO t1 VALUES ('a'),('r'),('s'),(_latin1 0xDF),(_latin1 0xF7),('t'),('z'); +SELECT * FROM t1 ORDER BY a; +SELECT a, COUNT(*) FROM t1 GROUP BY a; +DROP TABLE t1; --echo End of 5.1 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index febec62f037..9e6628a1972 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -564,22 +564,3 @@ SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); --echo End of 5.1 tests -# -# lp:817966 int_column IN (string_constant) -# -# rather illogically, when BIGINT field is compared to a string, -# the string is converted to an integer, not to a double. -# When some other integer field (not BIGINT) is compared to a string, -# or when the BIGINT is not a field, but an expression, both -# operands are compared as doubles. The latter behavior is correct, -# according to the manual. -# -create table t1 (a bigint, b int); -insert t1 values (1,1),(2,2),(3,3); -select * from t1 where a in ('2.1'); -select * from t1 where b in ('2.1'); -select * from t1 where a='2.1'; -select * from t1 where b='2.1'; -select * from t1 where IF(1,a,a)='2.1'; -drop table t1; - diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 208b18b3f85..bdb206be07b 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1463,6 +1463,45 @@ SELECT stddev_samp(rpad(1.0,2048,1)); SELECT ((127.1) not in ((rpad(1.0,2048,1)),(''),(-1.1))); SELECT ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)); +--echo # +--echo # Bug#13359121 LARGE NUMBERS, /STRINGS/DTOA.C:662: +--echo # BALLOC: ASSERTION `K <= 15' FAILED. +--echo # Bug#12985021 SIMPLE QUERY WITH DECIMAL NUMBERS TAKE AN +--echo # EXTRAORDINARY LONG TIME TO EXECUTE + +SELECT @tmp_max:= @@global.max_allowed_packet; +SET @@global.max_allowed_packet=1024*1024*1024; +# switching connection to allow the new max_allowed_packet take effect +--connect (newconn, localhost, root,,) + +SELECT @@global.max_allowed_packet; + +do +format(rpad('111111111.1', + 1111111, + '999999999999999999999999999999999999999999'),0,'be_BY') +; + +DO +round( + concat( ( + coalesce( ( + linefromwkb('2147483648', + -b'1111111111111111111111111111111111111111111')), + ( convert('[.DC2.]',decimal(30,30)) ), + bit_count('') + ) ), + ( lpad( ( elt('01','}:K5')), + sha1('P'), + ( ( select '-9223372036854775808.1' > all (select ''))) + ) + ) + ) +); + +--connection default +SET @@global.max_allowed_packet:= @tmp_max; +--disconnect newconn --echo # --echo # End of 5.5 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 7d0e5ae819a..42705975b88 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -927,6 +927,67 @@ SELECT WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1); DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); +--echo # +--echo # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY +--echo # SIMILAR TO '2009-10-00' +--echo # + +query_vertical SELECT + DATE('20091000'), + STR_TO_DATE('200910','%Y%m'), + LAST_DAY('2009-10-00'), + LAST_DAY(DATE('2009-10-00')), + LAST_DAY(DATE'2009-10-00'), + LAST_DAY(STR_TO_DATE('200910','%Y%m')), + WEEK('2009-10-00'), + WEEK(DATE('2009-10-00')), + WEEK(DATE'2009-10-00'), + WEEK(STR_TO_DATE('200910','%Y%m')), + WEEKOFYEAR('2009-10-00'), + WEEKOFYEAR(DATE('2009-10-00')), + WEEKOFYEAR(DATE'2009-10-00'), + WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')), + DAYOFYEAR('2009-10-00'), + DAYOFYEAR(DATE('2009-10-00')), + DAYOFYEAR(DATE'2009-10-00'), + DAYOFYEAR(STR_TO_DATE('200910','%Y%m')), + WEEKDAY('2009-10-00'), + WEEKDAY(DATE('2009-10-00')), + WEEKDAY(DATE'2009-10-00'), + WEEKDAY(STR_TO_DATE('200910','%Y%m')), + TO_DAYs('2009-10-00'), + TO_DAYs(DATE('2009-10-00')), + TO_DAYs(DATE'2009-10-00'), + TO_DAYs(STR_TO_DATE('200910','%Y%m')); + +query_vertical SELECT + DATE('00000100'), + STR_TO_DATE('000001','%Y%m'), + LAST_DAY('0000-01-00'), + LAST_DAY(DATE('0000-01-00')), + LAST_DAY(DATE'0000-01-00'), + LAST_DAY(STR_TO_DATE('000001','%Y%m')), + WEEK('0000-01-00'), + WEEK(DATE('0000-01-00')), + WEEK(DATE'0000-01-00'), + WEEK(STR_TO_DATE('000001','%Y%m')), + WEEKOFYEAR('0000-01-00'), + WEEKOFYEAR(DATE('0000-01-00')), + WEEKOFYEAR(DATE'0000-01-00'), + WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')), + DAYOFYEAR('0000-01-00'), + DAYOFYEAR(DATE('0000-01-00')), + DAYOFYEAR(DATE'0000-01-00'), + DAYOFYEAR(STR_TO_DATE('000001','%Y%m')), + WEEKDAY('0000-01-00'), + WEEKDAY(DATE('0000-01-00')), + WEEKDAY(DATE'0000-01-00'), + WEEKDAY(STR_TO_DATE('000001','%Y%m')), + TO_DAYs('0000-01-00'), + TO_DAYs(DATE('0000-01-00')), + TO_DAYs(DATE'0000-01-00'), + TO_DAYs(STR_TO_DATE('000001','%Y%m')); + --echo End of 5.1 tests --echo # @@ -952,6 +1013,21 @@ SELECT MONTHNAME(0), MONTHNAME(0) IS NULL, MONTHNAME(0) + 1; --error ER_WRONG_VALUE_FOR_VAR SET storage_engine=NULL; + +--echo # +--echo # BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIMESTAMP +--echo # Part1 (5.5) +SET time_zone='+03:00'; +CREATE TABLE t1 (a DATETIME NOT NULL); +INSERT INTO t1 VALUES ('2009-09-20 07:32:39.06'); +INSERT INTO t1 VALUES ('0000-00-00 00:00:00.00'); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT CAST(UNIX_TIMESTAMP(a) AS DECIMAL(25,3)) AS c1 FROM v1 ORDER BY 1; +DROP VIEW v1; +DROP TABLE t1; +SET time_zone=DEFAULT; + + --echo # --echo # Bug #59686 crash in String::copy() with time data type --echo # diff --git a/mysql-test/t/mysql_plugin.test b/mysql-test/t/mysql_plugin.test index c5968df85f8..71617b86330 100644 --- a/mysql-test/t/mysql_plugin.test +++ b/mysql-test/t/mysql_plugin.test @@ -155,6 +155,74 @@ eval INSERT INTO mysql.plugin VALUES ('wonky', '$DAEMONEXAMPLE'); --replace_regex /\.dll/.so/ SELECT * FROM mysql.plugin WHERE dl like 'libdaemon%' ORDER BY name; +# MTR will remove this file later, but this might be too late. +--error 0,1 +--remove_file $expect_file +--write_file $expect_file +wait +EOF +--shutdown_server 10 +--source include/wait_until_disconnected.inc + +# +# Disable the plugin - to remove winky, wonky entries +# +--exec $MYSQL_PLUGIN_CMD DISABLE daemon_example + +# +# Enable the plugin again +# +--exec $MYSQL_PLUGIN_CMD ENABLE daemon_example + +# +# Restart the server +# +--append_file $expect_file +restart +EOF +--enable_reconnect +--source include/wait_until_connected_again.inc + +--echo # +--echo # Ensure the plugin is loaded. +--echo # +--replace_regex /\.dll/.so/ +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; + +# MTR will remove this file later, but this might be too late. +--error 0,1 +--remove_file $expect_file +--write_file $expect_file +wait +EOF +--shutdown_server 10 +--source include/wait_until_disconnected.inc + +# To test the case where the same plugin is reloaded with a different soname, +# we must copy the example daemon to a new location renaming it. + +let $DAEMON_RELOAD = lib$DAEMONEXAMPLE; +--copy_file $PLUGIN_DIR/$DAEMONEXAMPLE $PLUGIN_DIR/$DAEMON_RELOAD +--copy_file include/libdaemon_example.ini $PLUGIN_DIR/libdaemon_example.ini + +# Now reload it and see that it is a different name. +--exec $MYSQL_PLUGIN_CMD ENABLE libdaemon_example + +# +# Restart the server +# +--append_file $expect_file +restart +EOF +--enable_reconnect +--source include/wait_until_connected_again.inc + +--echo # +--echo # Ensure the plugin is replaced. +--echo # +--replace_regex /\.dll/.so/ +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; + --echo # --echo # Disable the plugin... --echo # @@ -170,7 +238,12 @@ EOF # # Disable the plugin # ---exec $MYSQL_PLUGIN_CMD DISABLE daemon_example +--exec $MYSQL_PLUGIN_CMD DISABLE libdaemon_example + +# Remove files for last test case. + +--remove_file $PLUGIN_DIR/$DAEMON_RELOAD +--remove_file $DAEMONEXAMPLE_DIR/libdaemon_example.ini # # Restart the server @@ -184,7 +257,7 @@ EOF --echo # --echo # Ensure the plugin isn't loaded. --echo # -SELECT * FROM mysql.plugin WHERE dl like 'libdaemon%' ORDER BY name; +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; # # Stop the server for error conditions diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 424dd35a819..1d18ef06db4 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2213,6 +2213,118 @@ DROP DATABASE `test-database`; USE test; --echo # +--echo # BUG#11760384 : 52792: mysqldump in XML mode does not dump routines. +--echo # +CREATE DATABASE BUG52792; +USE BUG52792; +CREATE TABLE t1 (c1 INT, c2 VARCHAR(20)); +CREATE TABLE t2 (c1 INT); +INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); +INSERT INTO t2 VALUES (1),(2),(3); + +--echo # Stored Procedures. + +DELIMITER //; +CREATE PROCEDURE simpleproc1 (OUT param1 INT) +BEGIN + SELECT COUNT(*) INTO param1 FROM t1; +END// +DELIMITER ;// + +DELIMITER //; +CREATE PROCEDURE simpleproc2 (OUT param1 INT) +BEGIN + SELECT COUNT(*) INTO param1 FROM t2; +END// +DELIMITER ;// + +--echo # Events. + +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE BUG52792; +CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE BUG52792; + +--echo # Functions. + +CREATE FUNCTION `hello1` (s CHAR(20)) + RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT('Hello, ' ,s ,'!'); + +CREATE FUNCTION `hello2` (s CHAR(20)) + RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT(']]>, ' , s ,'!'); + +--echo # Triggers. + +DELIMITER |; +CREATE TRIGGER trig1 BEFORE INSERT ON t2 + FOR EACH ROW BEGIN + INSERT INTO t2 VALUES(1); +END; +| +DELIMITER ;| + +DELIMITER |; +CREATE TRIGGER trig2 AFTER INSERT ON t2 + FOR EACH ROW BEGIN + INSERT INTO t2 VALUES(1, ']]>'); + INSERT INTO t2 VALUES(2, '<![CDATA]]>'); + INSERT INTO t2 VALUES(3, '<![CDATA['); + INSERT INTO t2 VALUES(4, '< > & \ " _'); +END; +| +DELIMITER ;| + +--echo # Views + +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t2; +--echo +--echo # Dumping BUG52792 database in xml format. +--echo +--echo # Running 'replace_regex on timestamp' +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/--TIME--/ +--exec $MYSQL_DUMP --user=root --compact -R -E --triggers -X BUG52792 +--echo +--echo # Dumping BUG52792 database in xml format with comments. +--echo +--echo # Running 'replace_regex on timestamp' +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/--TIME--/ +--exec $MYSQL_DUMP --comments --user=root -R -E --triggers -X BUG52792 + +--echo +--echo # Test to check 'Insufficient privileges' error. +--echo + +GRANT ALL PRIVILEGES ON BUG52792.* TO user1; + +connect (conn_1, localhost, user1, , BUG52792, $MASTER_MYPORT, $MASTER_MYSOCK); +connection conn_1; + +--echo # Running 'replace_regex on timestamp' +--replace_regex /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/--TIME--/ +--error 2 +--exec $MYSQL_DUMP --user=user1 -R -E --triggers -X BUG52792 + +connection default; +disconnect conn_1; + +DROP USER user1; +DROP DATABASE BUG52792; +--echo # UTF-8 +CREATE DATABASE BUG52792; +USE BUG52792; +SET NAMES utf8; +CREATE FUNCTION `straße` ( c1 CHAR(20)) + RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT(']]>, ', s, '!'); + +--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --default-character-set=utf8 --compatible=mysql323 -R -X BUG52792 + +DROP DATABASE BUG52792; + +USE test; + +--echo # --echo # End of 5.1 tests --echo # diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index e4e26da7031..6ac883ffcc1 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1404,6 +1404,82 @@ SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; DROP TABLE t1; +--echo # +--echo # BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND +--echo # WITH/WITHOUT INDEX RANGE SCAN +--echo # + +create table t1 (id int unsigned not null auto_increment primary key); +insert into t1 values (null); +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +create table t2 ( + id int unsigned not null auto_increment, + val decimal(5,3) not null, + primary key (id,val), + unique key (val,id), + unique key (id)); +--disable_warnings +insert into t2 select null,id*0.0009 from t1; +--enable_warnings + +select count(val) from t2 ignore index (val) where val > 0.1155; +select count(val) from t2 force index (val) where val > 0.1155; + +drop table t2, t1; + +--echo # +--echo # BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG +--echo # RESULTS WITH DECIMAL CONVERSION +--echo # + +create table t1 (a int,b int,c int,primary key (a,c)); +insert into t1 values (1,1,2),(1,1,3),(1,1,4); +# show that the integer 3 is bigger than the decimal 2.9, +# which should also apply to comparing "c" with 2.9 +# when c is 3. +select convert(3, signed integer) > 2.9; +select * from t1 force index (primary) where a=1 and c>= 2.9; +select * from t1 ignore index (primary) where a=1 and c>= 2.9; +select * from t1 force index (primary) where a=1 and c> 2.9; +select * from t1 ignore index (primary) where a=1 and c> 2.9; +drop table t1; + +--echo # +--echo # BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG +--echo # RESULT AFTER MYSQL 5.1. +--echo # + +CREATE TABLE t1( + F1 CHAR(5) NOT NULL, + F2 CHAR(5) NOT NULL, + F3 CHAR(5) NOT NULL, + PRIMARY KEY(F1), + INDEX IDX_F2(F2) +); + +INSERT INTO t1 VALUES +('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), +('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); + +SELECT * FROM t1 WHERE F1 = 'A '; +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; +SELECT * FROM t1 WHERE F1 >= 'A '; +SELECT * FROM t1 WHERE F1 > 'A '; +SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; +SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; +SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND +'AAAAA'; + +DROP TABLE t1; + --echo End of 5.1 tests --echo # diff --git a/mysql-test/t/xa.test b/mysql-test/t/xa.test index 78f41763e20..ab589ae6727 100644 --- a/mysql-test/t/xa.test +++ b/mysql-test/t/xa.test @@ -338,8 +338,8 @@ DROP TABLE t1; DROP TABLE IF EXISTS t1, t2; --enable_warnings -CREATE TABLE t1 (a INT) engine=innodb; -CREATE TABLE t2 (a INT) engine=innodb; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (a INT) ENGINE=InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1); @@ -360,7 +360,6 @@ let $wait_condition= --source include/wait_condition.inc --sleep 0.1 DELETE FROM t1; -COMMIT; --echo # Connection con2 --connection con2 @@ -369,6 +368,14 @@ COMMIT; --reap --error ER_XA_RBDEADLOCK XA COMMIT 'xid1'; + +--echo # Connection default +connection default; + +COMMIT; + +--echo # Connection con2 +connection con2; # This caused the assert to be triggered XA START 'xid1'; |