From 26c9f1da82906532d5f132b5fa9f4ab9425d687e Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 15 Dec 2011 16:16:48 -0500 Subject: BUG#12969301 : mysql_plugin: enable is ignored if plugin exists This patch changes the mechanism by which the client enables a plugin. Instead of using INSERT IGNORE to reload a plugin library, it now uses REPLACE INTO. This allows users to load a library multiple times replacing the existing values in the mysql.plugin table. This allows users to replace the symbol reference to a different dl name in the table. Thus permitting enabling of multiple versions of the same library without first disabling the old version. A regression test was added to ensure this feature works. --- mysql-test/include/libdaemon_example.ini | 9 ++++ mysql-test/t/mysql_plugin.test | 77 +++++++++++++++++++++++++++++++- 2 files changed, 84 insertions(+), 2 deletions(-) create mode 100644 mysql-test/include/libdaemon_example.ini (limited to 'mysql-test') 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/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 -- cgit v1.2.1 From 86765941f73cdb93cbcde4b512616bb13806c968 Mon Sep 17 00:00:00 2001 From: Vasil Dimov Date: Mon, 20 Feb 2012 13:25:37 +0200 Subject: Adjust .result files of mysqld--help-notwin and mysqld--help-win mtr tests. This is a followup to vasil.dimov@oracle.com-20120217130947-03319op732dsf4m2 which added a deprecation notice to ignore-builtin-innodb --- mysql-test/r/mysqld--help-notwin.result | 3 ++- mysql-test/r/mysqld--help-win.result | 3 ++- 2 files changed, 4 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index 8dacb035f36..3df31eeebd5 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -175,7 +175,8 @@ The following options may be given as the first argument: GROUP_CONCAT() -?, --help Display this help and exit. --ignore-builtin-innodb - Disable initialization of builtin InnoDB plugin + DEPRECATED. This option will be removed in future + releases. Disable initialization of builtin InnoDB plugin --init-connect=name Command(s) that are executed for each new connection --init-file=name Read SQL commands from this file at startup --init-rpl-role=name diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result index d7a06c5b23e..cd7043eb59f 100644 --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -175,7 +175,8 @@ The following options may be given as the first argument: GROUP_CONCAT() -?, --help Display this help and exit. --ignore-builtin-innodb - Disable initialization of builtin InnoDB plugin + DEPRECATED. This option will be removed in future + releases. Disable initialization of builtin InnoDB plugin --init-connect=name Command(s) that are executed for each new connection --init-file=name Read SQL commands from this file at startup --init-rpl-role=name -- cgit v1.2.1 From 74374933c8db3c7f1de07ced89ce72dd705576b3 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Mon, 20 Feb 2012 22:59:11 +0100 Subject: Bug#11761296: 53775: QUERY ON PARTITIONED TABLE RETURNS CACHED RESULT FROM PREVIOUS TRANSACTION The current Query Cache API is not fully compatible with the partitioning engine. There is no good way to implement support for QC due to: 1) a static callback for ha_partition would need to have access to all partition names and call the underlying callback for each [sub]partition with the correct name. 2) pruning would be impossible, even if one used the ulonglong engine_data due to if engine_data is changed, the table is invalidated by the QC. So the only viable solution to avoid incorrect data is to not allow caching of queries using partitioned tables. (There are some extra changes, due to removal of \r as line break) --- mysql-test/include/query_cache.inc | 38 ++++--- mysql-test/r/cache_innodb.result | 7 +- mysql-test/r/partition_cache.result | 205 +++++++++++++++++++++++++++++++++++ mysql-test/t/cache_innodb-master.opt | 1 - mysql-test/t/partition_cache.test | 21 ++++ 5 files changed, 255 insertions(+), 17 deletions(-) create mode 100644 mysql-test/r/partition_cache.result delete mode 100644 mysql-test/t/cache_innodb-master.opt create mode 100644 mysql-test/t/partition_cache.test (limited to 'mysql-test') diff --git a/mysql-test/include/query_cache.inc b/mysql-test/include/query_cache.inc index 7ce97b42158..ecc5014880c 100644 --- a/mysql-test/include/query_cache.inc +++ b/mysql-test/include/query_cache.inc @@ -4,6 +4,9 @@ # $engine_type -- storage engine to be tested # $test_foreign_keys -- 0, skip foreign key tests # -- 1, do not skip foreign key tests +# $partitions_a -- partition by column 'a' +# $partitions_id -- partition by column 'id' +# $partitions_s1 -- partition by column 's1' # have to be set before sourcing this script. # # Last update: @@ -19,47 +22,61 @@ eval SET SESSION STORAGE_ENGINE = $engine_type; drop table if exists t1,t2,t3; --enable_warnings +set @save_query_cache_size = @@global.query_cache_size; +set GLOBAL query_cache_size = 1355776; + # # Without auto_commit. # flush status; set autocommit=0; -create table t1 (a int not null); +eval create table t1 (a int not null)$partitions_a; insert into t1 values (1),(2),(3); +--sorted_result select * from t1; show status like "Qcache_queries_in_cache"; drop table t1; commit; set autocommit=1; begin; -create table t1 (a int not null); +eval create table t1 (a int not null)$partitions_a; insert into t1 values (1),(2),(3); +--sorted_result select * from t1; show status like "Qcache_queries_in_cache"; drop table t1; commit; -create table t1 (a int not null); -create table t2 (a int not null); -create table t3 (a int not null); +eval create table t1 (a int not null)$partitions_a; +eval create table t2 (a int not null)$partitions_a; +eval create table t3 (a int not null)$partitions_a; insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1),(2); +--sorted_result select * from t1; +--sorted_result select * from t2; +--sorted_result select * from t3; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; begin; +--sorted_result select * from t1; +--sorted_result select * from t2; +--sorted_result select * from t3; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; insert into t1 values (3); insert into t2 values (3); insert into t1 values (4); +--sorted_result select * from t1; +--sorted_result select * from t2; +--sorted_result select * from t3; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; @@ -67,7 +84,7 @@ commit; show status like "Qcache_queries_in_cache"; drop table t3,t2,t1; -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)); +eval CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id))$partitions_id; select count(*) from t1; insert into t1 (id) values (0); select count(*) from t1; @@ -78,8 +95,6 @@ if ($test_foreign_keys) # # one statement roll back inside transation # -let $save_query_cache_size=`select @@global.query_cache_size`; -set GLOBAL query_cache_size=1355776; CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY a (a)); CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY b (b)); CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`)); @@ -95,9 +110,6 @@ insert into t3 VALUES ( NULL, 1, 1, 2 ); commit; select t1.* from t1, t2, t3 where t3.state & 1 = 0 and t3.t1_id = t1.id and t3.t2_id = t2.id and t1.id = 1 order by t1.a asc; drop table t3,t2,t1; ---disable_query_log -eval set GLOBAL query_cache_size=$save_query_cache_size; ---enable_query_log } # @@ -118,7 +130,7 @@ SET GLOBAL query_cache_size = 200000; flush status; SET @@autocommit=1; eval SET SESSION STORAGE_ENGINE = $engine_type; -CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1)); +eval CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1))$partitions_s1; INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10)); COMMIT; START TRANSACTION; @@ -176,8 +188,8 @@ show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; # Final cleanup -eval set GLOBAL query_cache_size=$save_query_cache_size; disconnect connection1; --source include/wait_until_disconnected.inc connection default; +set @@global.query_cache_size = @save_query_cache_size; drop table t2; diff --git a/mysql-test/r/cache_innodb.result b/mysql-test/r/cache_innodb.result index b59298727c5..03989c80b9d 100644 --- a/mysql-test/r/cache_innodb.result +++ b/mysql-test/r/cache_innodb.result @@ -1,5 +1,7 @@ SET SESSION STORAGE_ENGINE = InnoDB; drop table if exists t1,t2,t3; +set @save_query_cache_size = @@global.query_cache_size; +set GLOBAL query_cache_size = 1355776; flush status; set autocommit=0; create table t1 (a int not null); @@ -100,7 +102,7 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 drop table t3,t2,t1; -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)); +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)); select count(*) from t1; count(*) 0 @@ -109,7 +111,6 @@ select count(*) from t1; count(*) 1 drop table t1; -set GLOBAL query_cache_size=1355776; CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY a (a)); CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY b (b)); CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`)); @@ -218,5 +219,5 @@ Qcache_queries_in_cache 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 -set GLOBAL query_cache_size=1048576; +set @@global.query_cache_size = @save_query_cache_size; drop table t2; diff --git a/mysql-test/r/partition_cache.result b/mysql-test/r/partition_cache.result new file mode 100644 index 00000000000..e4e7cc8a4ac --- /dev/null +++ b/mysql-test/r/partition_cache.result @@ -0,0 +1,205 @@ +SET SESSION STORAGE_ENGINE = InnoDB; +drop table if exists t1,t2,t3; +set @save_query_cache_size = @@global.query_cache_size; +set GLOBAL query_cache_size = 1355776; +flush status; +set autocommit=0; +create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +insert into t1 values (1),(2),(3); +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t1; +commit; +set autocommit=1; +begin; +create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +insert into t1 values (1),(2),(3); +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t1; +commit; +create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +create table t2 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +create table t3 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +insert into t1 values (1),(2); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +select * from t1; +a +1 +2 +select * from t2; +a +1 +2 +select * from t3; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +begin; +select * from t1; +a +1 +2 +select * from t2; +a +1 +2 +select * from t3; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +insert into t1 values (3); +insert into t2 values (3); +insert into t1 values (4); +select * from t1; +a +1 +2 +3 +4 +select * from t2; +a +1 +2 +3 +select * from t3; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +commit; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t3,t2,t1; +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) PARTITION BY HASH (id) PARTITIONS 3; +select count(*) from t1; +count(*) +0 +insert into t1 (id) values (0); +select count(*) from t1; +count(*) +1 +drop table t1; +SET SESSION STORAGE_ENGINE = InnoDB; +SET @@autocommit=1; +connection default +SHOW VARIABLES LIKE 'have_query_cache'; +Variable_name Value +have_query_cache YES +SET GLOBAL query_cache_size = 200000; +flush status; +SET @@autocommit=1; +SET SESSION STORAGE_ENGINE = InnoDB; +CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1)) PARTITION BY KEY (s1) PARTITIONS 3; +INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10)); +COMMIT; +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +0 +UPDATE t2 SET s2 = 'w' WHERE s1 = 3; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +connection connection1 +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +0 +INSERT INTO t2 VALUES (5,'w'); +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +COMMIT; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +connection default +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +COMMIT; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +connection connection1 +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +INSERT INTO t2 VALUES (6,'w'); +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +3 +connection default +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +DELETE from t2 WHERE s1=3; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +COMMIT; +connection connection1 +COMMIT; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +set @@global.query_cache_size = @save_query_cache_size; +drop table t2; diff --git a/mysql-test/t/cache_innodb-master.opt b/mysql-test/t/cache_innodb-master.opt deleted file mode 100644 index 5f0ebff98f6..00000000000 --- a/mysql-test/t/cache_innodb-master.opt +++ /dev/null @@ -1 +0,0 @@ ---set-variable=query_cache_size=1M diff --git a/mysql-test/t/partition_cache.test b/mysql-test/t/partition_cache.test new file mode 100644 index 00000000000..b5d19796b7c --- /dev/null +++ b/mysql-test/t/partition_cache.test @@ -0,0 +1,21 @@ +# t/cache_innodb.test +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code t/innodb_cache.test --> include/query_cache.inc +# new wrapper t/cache_innodb.test +# + +--source include/have_query_cache.inc + +--source include/have_innodb.inc +--source include/have_partition.inc +let $engine_type= InnoDB; +# Using SELECT to get a space as first character. +let $partitions_a= `SELECT ' PARTITION BY KEY (a) PARTITIONS 3'`; +let $partitions_id= `SELECT ' PARTITION BY HASH (id) PARTITIONS 3'`; +let $partitions_s1= `SELECT ' PARTITION BY KEY (s1) PARTITIONS 3'`; +# partitioning does not support FOREIGN KEYs +let $test_foreign_keys= 0; + +--source include/query_cache.inc -- cgit v1.2.1 From c2d0fea84e25aca620e7e6922de234e38a24703a Mon Sep 17 00:00:00 2001 From: Vasil Dimov Date: Tue, 21 Feb 2012 17:57:07 +0200 Subject: Fix Bug#13639142 64128: INNODB ERROR IN SERVER LOG OF INNODB_BUG34300 Suppress innodb_bug34300 from failing if InnoDB prints: 120221 11:05:03 InnoDB: ERROR: the age of the last checkpoint is 9439048, InnoDB: which exceeds the log group capacity 9433498. by default the log capacity is 2 log files, 5 MB each. --- mysql-test/suite/innodb/t/innodb_bug34300.test | 1 + mysql-test/suite/innodb_plugin/t/innodb_bug34300.test | 1 + 2 files changed, 2 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/t/innodb_bug34300.test b/mysql-test/suite/innodb/t/innodb_bug34300.test index 6c516ccb73e..c49b69f29fc 100644 --- a/mysql-test/suite/innodb/t/innodb_bug34300.test +++ b/mysql-test/suite/innodb/t/innodb_bug34300.test @@ -8,6 +8,7 @@ -- disable_query_log -- disable_result_log call mtr.add_suppression("InnoDB: Warning: a long semaphore wait:"); +call mtr.add_suppression("the age of the last checkpoint is"); # set packet size and reconnect SET @@global.max_allowed_packet=16777216; diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug34300.test b/mysql-test/suite/innodb_plugin/t/innodb_bug34300.test index 8be53f0db30..6684ba692c8 100644 --- a/mysql-test/suite/innodb_plugin/t/innodb_bug34300.test +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug34300.test @@ -8,6 +8,7 @@ -- disable_query_log -- disable_result_log call mtr.add_suppression("InnoDB: Warning: a long semaphore wait:"); +call mtr.add_suppression("the age of the last checkpoint is"); # set packet size and reconnect let $max_packet=`select @@global.max_allowed_packet`; -- cgit v1.2.1 From 8325fe02b3f2cb94fb99ac72dd837d9db0c037ba Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Wed, 22 Feb 2012 23:13:36 +0100 Subject: Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST INNODB PARTITION STATISTICS Problem was the fix for bug#11756867; It always used the first partitions, and stopped after it checked 10 [sub]partitions. (or until it found a partition which would contain a match). This results in bad statistics for tables where the first 10 partitions don't represent the majority of the data (like when the first 10 partitions only contained a few rows in total). The solution was to take statisics from the partitions containing the most rows instead: Added an array of partition ids which is sorted by number of records in descending order. this array is used in records_in_range to cover as many records as possible in as few calls as possible. Also changed the limit of how many partitions to use for the statistics from a static max of 10 partitions, into a dynamic model: Maximum number of partitions is now log2(total number of partitions) taken from the ordered array. It will continue calling partitions records_in_range until it has checked: (total rows in matching partitions) * (maximum number of partitions) / (number of used partitions) Also reverted the changes for ha_partition::scan_time() and ha_partition::estimate_rows_upper_bound() to before the fix of bug#11756867. Since they are not as slow as records_in_range. --- mysql-test/r/partition_innodb.result | 29 +++++++++++++ mysql-test/r/partition_pruning.result | 76 +++++++++++++++++------------------ mysql-test/t/partition_innodb.test | 29 +++++++++++++ 3 files changed, 96 insertions(+), 38 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 5fcb0e796b1..12a935c1b2b 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -1,5 +1,34 @@ drop table if exists t1, t2; # +# Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST +# INNODB PARTITION STATISTICS +# +CREATE TABLE t1 +(a INT, +b varchar(64), +PRIMARY KEY (a), +KEY (b)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) SUBPARTITIONS 10 +(PARTITION pNeg VALUES LESS THAN (0), +PARTITION p0 VALUES LESS THAN (1000), +PARTITION pMAX VALUES LESS THAN MAXVALUE); +# Only one row in the first 10 subpartitions +INSERT INTO t1 VALUES (-1, 'Only negative pk value'); +INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), +(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), +(20, '0'), (21, '1'), (22, '2'), (23, '3'), +(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); +INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b b 67 NULL 18 Using where; Using index +DROP TABLE t1; +# # Bug#56287: crash when using Partition datetime in sub in query # CREATE TABLE t1 diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 01ae3876fd0..985675712ef 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -18,7 +18,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -# # # # # # # # # 9 # +# # # # # # # # # 10 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # @@ -105,7 +105,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -168,7 +168,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a <= 7; a -1 @@ -182,7 +182,7 @@ a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -424,7 +424,7 @@ a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -474,7 +474,7 @@ a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 6; a -1 @@ -487,7 +487,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -744,13 +744,13 @@ a 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index @@ -759,26 +759,26 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index @@ -790,16 +790,16 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; @@ -1076,7 +1076,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1104,10 +1104,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index @@ -1119,10 +1119,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index @@ -2537,18 +2537,18 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where explain partitions select * from t2 where b = 4; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 Using where explain extended select * from t2 where b = 6; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref b b 5 const 76 100.00 Using where +1 SIMPLE t2 ref b b 5 const 96 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6) explain partitions select * from t2 where b = 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 Using where explain extended select * from t2 where b in (1,3,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 51.65 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5)) explain partitions select * from t2 where b in (1,3,5); @@ -2556,7 +2556,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (2,4,6); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 31.65 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6)) explain partitions select * from t2 where b in (2,4,6); @@ -2564,7 +2564,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (7,8,9); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 19.12 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9)) explain partitions select * from t2 where b in (7,8,9); @@ -2572,7 +2572,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 29.23 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5) explain partitions select * from t2 where b > 5; @@ -2580,7 +2580,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 28.13 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8)) explain partitions select * from t2 where b > 5 and b < 8; @@ -2588,15 +2588,15 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where +1 SIMPLE t2 range b b 5 NULL 96 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7)) explain partitions select * from t2 where b > 5 and b < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 96 Using where explain extended select * from t2 where b > 0 and b < 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 53.19 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5)) explain partitions select * from t2 where b > 0 and b < 5; @@ -2630,10 +2630,10 @@ flush status; delete from t2 where b = 7; show status like 'Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 0 +Handler_read_rnd_next 1215 show status like 'Handler_read_key'; Variable_name Value -Handler_read_key 5 +Handler_read_key 0 flush status; delete from t2 where b > 5; show status like 'Handler_read_rnd_next'; diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index dc8bcbb4cb9..f2a1a5b0fe4 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -7,6 +7,35 @@ drop table if exists t1, t2; let $MYSQLD_DATADIR= `SELECT @@datadir`; +--echo # +--echo # Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST +--echo # INNODB PARTITION STATISTICS +--echo # + +CREATE TABLE t1 +(a INT, + b varchar(64), + PRIMARY KEY (a), + KEY (b)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) SUBPARTITIONS 10 +(PARTITION pNeg VALUES LESS THAN (0), + PARTITION p0 VALUES LESS THAN (1000), + PARTITION pMAX VALUES LESS THAN MAXVALUE); + +--echo # Only one row in the first 10 subpartitions +INSERT INTO t1 VALUES (-1, 'Only negative pk value'); + +INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), +(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), +(20, '0'), (21, '1'), (22, '2'), (23, '3'), +(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); +INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; +ANALYZE TABLE t1; +EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; +DROP TABLE t1; + --echo # --echo # Bug#56287: crash when using Partition datetime in sub in query --echo # -- cgit v1.2.1 From 6a0d03fce76616dd10e8d6bd83c19af26f8c5874 Mon Sep 17 00:00:00 2001 From: Chaithra Gopalareddy Date: Fri, 24 Feb 2012 11:53:36 +0530 Subject: Bug#13012483:EXPLAIN EXTENDED, PREPARED STATEMENT, CRASH IN CHECK_SIMPLE_EQUALITY PROBLEM: Crash in "check_simple_equality" when using a subquery with "IN" and "ALL" in prepare. ANALYSIS: Crash can be reproduced using a simplified query like this one: prepare s from "select 1 from g1 where 1 < all ( select @:=(1 in (select 1 from g1)) from g1)"; This bug is currently present only on 5.5.and 5.1. Its fixed as part of work log(#1110) in 5.6. We are taking one change to fix this in 5.5 and 5.1. Problem seems to be present because we are trying to evaluate "is_null" on an argument which is part of a subquery (In Item_is_not_null_test::update_used_tables()). But the condition to evaluate is only when we do not have a sub query present, which means to say that "with_subselect" is not set. With respect to the above query, we create an object of type "Item_in_optimizer" which by definition is always associated with a subquery. While in 5.6 we set "with_subselect" to true for "Item_in_optimizer" object, we do not do the same in 5.5. This results in the evaluation for "is_null" resulting in a coredump. So, we are now setting "with_subselect" to true for "Item_in_optimizer" in 5.1 and 5.5. mysql-test/r/func_in.result: Result file changes for the test case added mysql-test/t/func_in.test: Test case added for Bug#13012483 sql/item_cmpfunc.h: Changed Item_in_optimizer::Item_in_optimizer( ) to set "with_subselect" to true --- mysql-test/r/func_in.result | 10 ++++++++++ mysql-test/t/func_in.test | 13 +++++++++++++ 2 files changed, 23 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 0b6117581f3..7f0e607e789 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -776,4 +776,14 @@ SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1) 1 # +# +# Bug#13012483: EXPLAIN EXTENDED, PREPARED STATEMENT, CRASH IN CHECK_SIMPLE_EQUALITY +# +CREATE TABLE t1 (a INT); +PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; +EXECUTE s; +1 +DROP TABLE t1; +# End of test BUG#13012483 +# End of 5.1 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 08469b37967..dd4194e2a4e 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -562,4 +562,17 @@ SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); --echo # +--echo # +--echo # Bug#13012483: EXPLAIN EXTENDED, PREPARED STATEMENT, CRASH IN CHECK_SIMPLE_EQUALITY +--echo # + +CREATE TABLE t1 (a INT); +PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; +EXECUTE s; + +DROP TABLE t1; + +--echo # End of test BUG#13012483 + +--echo # --echo End of 5.1 tests -- cgit v1.2.1 From a8fabaecaa7275fc860b164ce0583dcae8d6767f Mon Sep 17 00:00:00 2001 From: Manish Kumar Date: Tue, 28 Feb 2012 16:25:13 +0530 Subject: BUG#13333431 - INCORRECT DEFAULT PORT IN 'SHOW SLAVE HOSTS' OUTPUT This is a post commit patch for failing test on windows. --- mysql-test/r/mysqld--help-win.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result index cd7043eb59f..bc917792239 100644 --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -923,7 +923,7 @@ relay-log-space-limit 0 replicate-same-server-id FALSE report-host (No default value) report-password (No default value) -report-port 3306 +report-port 0 report-user (No default value) rpl-recovery-rank 0 safe-user-create FALSE -- cgit v1.2.1 From bc1e5e0646fa77bbbee017000f865e042b99f207 Mon Sep 17 00:00:00 2001 From: Manish Kumar Date: Tue, 28 Feb 2012 19:43:09 +0530 Subject: BUG#13333431 - INCORRECT DEFAULT PORT IN 'SHOW SLAVE HOSTS' OUTPUT This is a post commit patch for failing test on windows. --- mysql-test/r/mysqld--help-win.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result index bc917792239..db7dd264b76 100644 --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -923,7 +923,7 @@ relay-log-space-limit 0 replicate-same-server-id FALSE report-host (No default value) report-password (No default value) -report-port 0 +report-port 0 report-user (No default value) rpl-recovery-rank 0 safe-user-create FALSE -- cgit v1.2.1 From c22c9270fb40a8a2df988084968c0e04dd07a4b8 Mon Sep 17 00:00:00 2001 From: Praveenkumar Hulakund Date: Wed, 29 Feb 2012 12:23:15 +0530 Subject: Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES IGNORED AND BREAKS REPLICATION Analysis: ======================== sql_mode "NO_BACKSLASH_ESCAPES": When user want to use backslash as character input, instead of escape character in a string literal then sql_mode can be set to "NO_BACKSLASH_ESCAPES". With this mode enabled, backslash becomes an ordinary character like any other. SQL_MODE set applies to the current client session. And while creating the stored procedure, MySQL stores the current sql_mode and always executes the stored procedure in sql_mode stored with the Procedure, regardless of the server SQL mode in effect when the routine is invoked. In the scenario (for which bug is reported), the routine is created with sql_mode=NO_BACKSLASH_ESCAPES. And routine is executed with the invoker sql_mode is "" (NOT SET) by executing statement "call testp('Axel\'s')". Since invoker sql_mode is "" (NOT_SET), the '\' in 'Axel\'s'(argument to function) is considered as escape character and column "a" (of table "t1") values are updated with "Axel's". The binary log generated for above update operation is as below, set sql_mode=XXXXXX (for no_backslash_escapes) update test.t1 set a= NAME_CONST('var',_latin1'Axel\'s' COLLATE 'latin1_swedish_ci'); While logging stored procedure statements, the local variables (params) used in statements are replaced with the NAME_CONST(var_name, var_value) (Internal function) (http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_name-const) On slave, these logs are applied. NAME_CONST is parsed to get the variable and its value. Since, stored procedure is created with sql_mode="NO_BACKSLASH_ESCAPES", the sql_mode is also logged in. So that at slave this sql_mode is set before executing the statements of routine. So at slave, sql_mode is set to "NO_BACKSLASH_ESCAPES" and then while parsing NAME_CONST of string variable, '\' is considered as NON ESCAPE character and parsing reported error for "'" (as we have only one "'" no backslash). At slave, parsing was proper with sql_mode "NO_BACKSLASH_ESCAPES". But above error reported while writing bin log, "'" (of Axel's) is escaped with "\" character. Actually, all special characters (n, r, ', ", \, 0...) are escaped while writing NAME_CONST for string variable(param, local variable) in bin log Airrespective of "NO_BACKSLASH_ESCAPES" sql_mode. So, basically, the problem is that logging string parameter does not take into account sql_mode value. Fix: ======================== So when sql_mode is set to "NO_BACKSLASH_ESCAPES", escaping characters as (n, r, ', ", \, 0...) should be avoided. To do so, added a check to not to escape such characters while writing NAME_CONST for string variables in bin log. And when sql_mode is set to NO_BACKSLASH_ESCAPES, quote character "'" is represented as ''. http://dev.mysql.com/doc/refman/5.6/en/string-literals.html (There are several ways to include quote characters within a string: ) mysql-test/r/sql_mode.result: Added test case for Bug#12601974. mysql-test/suite/binlog/r/binlog_sql_mode.result: Appended result of test cases added for Bug#12601974. mysql-test/suite/binlog/t/binlog_sql_mode.test: Added test case for Bug#12601974. mysql-test/t/sql_mode.test: Appended result of test cases added for Bug#12601974. --- mysql-test/r/sql_mode.result | 204 +++++++++++++++++++++++ mysql-test/suite/binlog/r/binlog_sql_mode.result | 111 ++++++++++++ mysql-test/suite/binlog/t/binlog_sql_mode.test | 96 +++++++++++ mysql-test/t/sql_mode.test | 154 +++++++++++++++++ 4 files changed, 565 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index 0b0d5a38d0b..fb192c25087 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -527,3 +527,207 @@ SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%P Host Db User Table_name Column_name Timestamp Column_priv DROP TABLE t1; DROP TABLE t2; + +# +# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +# IGNORED AND BREAKS REPLICATION +# +DROP TABLE IF EXISTS test_table; +DROP FUNCTION IF EXISTS test_function; +CREATE TABLE test_table (c1 CHAR(50)); +SET @org_mode=@@sql_mode; +SET @@sql_mode=''; +PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)'; +PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?'; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN +DECLARE char_val CHAR(50); +SELECT c1 INTO char_val FROM test_table WHERE c1=var; +RETURN char_val; +END +$ +SET @var1='abcd\'ef'; +SET @var2='abcd\"ef'; +SET @var3='abcd\bef'; +SET @var4='abcd\nef'; +SET @var5='abcd\ref'; +SET @var6='abcd\tef'; +SET @var7='abcd\\ef'; +SET @var8='abcd\%ef'; +SET @var9='abcd\_ef'; +SET @to_var1='wxyz\'ef'; +SET @to_var2='wxyz\"ef'; +SET @to_var3='wxyz\bef'; +SET @to_var4='wxyz\nef'; +SET @to_var5='wxyz\ref'; +SET @to_var6='wxyz\tef'; +SET @to_var7='wxyz\\ef'; +SET @to_var8='wxyz\%ef'; +SET @to_var9='wxyz\_ef'; +# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; +SELECT * FROM test_table; +c1 +wxyz'ef +wxyz"ef +wxyzef +wxyz +ef +wxyz ef +wxyz ef +wxyz\ef +wxyz\%ef +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +test_function(@to_var1) +wxyz'ef +SELECT test_function(@to_var2); +test_function(@to_var2) +wxyz"ef +SELECT test_function(@to_var3); +test_function(@to_var3) +wxyzef +SELECT test_function(@to_var4); +test_function(@to_var4) +wxyz +ef +SELECT test_function(@to_var5); +test_function(@to_var5) +wxyz ef +SELECT test_function(@to_var6); +test_function(@to_var6) +wxyz ef +SELECT test_function(@to_var7); +test_function(@to_var7) +wxyz\ef +SELECT test_function(@to_var8); +test_function(@to_var8) +wxyz\%ef +SELECT test_function(@to_var9); +test_function(@to_var9) +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DELETE FROM test_table; +DROP FUNCTION test_function; +SET @@sql_mode='NO_BACKSLASH_ESCAPES'; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN +DECLARE char_val CHAR(50); +SELECT c1 INTO char_val FROM test_table WHERE c1=var; +RETURN char_val; +END +$ +# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; +SELECT * FROM test_table; +c1 +wxyz'ef +wxyz"ef +wxyzef +wxyz +ef +wxyz ef +wxyz ef +wxyz\ef +wxyz\%ef +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +test_function(@to_var1) +wxyz'ef +SELECT test_function(@to_var2); +test_function(@to_var2) +wxyz"ef +SELECT test_function(@to_var3); +test_function(@to_var3) +wxyzef +SELECT test_function(@to_var4); +test_function(@to_var4) +wxyz +ef +SELECT test_function(@to_var5); +test_function(@to_var5) +wxyz ef +SELECT test_function(@to_var6); +test_function(@to_var6) +wxyz ef +SELECT test_function(@to_var7); +test_function(@to_var7) +wxyz\ef +SELECT test_function(@to_var8); +test_function(@to_var8) +wxyz\%ef +SELECT test_function(@to_var9); +test_function(@to_var9) +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DROP TABLE test_table; +DROP FUNCTION test_function; +SET @@sql_mode= @org_mode; + +#End of Test for Bug#12601974 diff --git a/mysql-test/suite/binlog/r/binlog_sql_mode.result b/mysql-test/suite/binlog/r/binlog_sql_mode.result index 4477c94a95e..1aea77c4a4b 100644 --- a/mysql-test/suite/binlog/r/binlog_sql_mode.result +++ b/mysql-test/suite/binlog/r/binlog_sql_mode.result @@ -38,3 +38,114 @@ DROP VIEW testView; DROP TABLE t1; SET @@global.sql_mode= @old_sql_mode; SET @@session.binlog_format=@old_binlog_format; + +# +# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +# IGNORED AND BREAKS REPLICATION +# +DROP DATABASE IF EXISTS mysqltest_db; +DROP TABLE IF EXISTS test_table; +CREATE DATABASE mysqltest_db; +USE mysqltest_db; +CREATE TABLE test_table (c1 CHAR(50)); +SET @org_mode=@@sql_mode; +SET @@sql_mode=''; +CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN +DECLARE var1 CHAR(50) DEFAULT param1; +DECLARE var2 CHAR(50) DEFAULT param2; +DECLARE var3 CHAR(50) DEFAULT 'abcd\bef'; +DECLARE var4 CHAR(50) DEFAULT 'abcd\nef'; +DECLARE var5 CHAR(50) DEFAULT 'abcd\ref'; +DECLARE var6 CHAR(50) DEFAULT 'abcd\tef'; +DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef'; +DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef'; +DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef'; +INSERT INTO test_table VALUES (var1); +INSERT INTO test_table VALUES (var2); +INSERT INTO test_table VALUES (var3); +INSERT INTO test_table VALUES (var4); +INSERT INTO test_table VALUES (var5); +INSERT INTO test_table VALUES (var6); +INSERT INTO test_table VALUES (var7); +INSERT INTO test_table VALUES (var8); +INSERT INTO test_table VALUES (var9); +END +$ +SET @@sql_mode='NO_BACKSLASH_ESCAPES'$ +CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN +DECLARE var1 CHAR(50) DEFAULT param1; +DECLARE var2 CHAR(50) DEFAULT param2; +DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef'; +DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef'; +DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref'; +DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef'; +DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef'; +DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef'; +DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef'; +INSERT INTO test_table VALUES (var1); +INSERT INTO test_table VALUES (var2); +INSERT INTO test_table VALUES (var3); +INSERT INTO test_table VALUES (var4); +INSERT INTO test_table VALUES (var5); +INSERT INTO test_table VALUES (var6); +INSERT INTO test_table VALUES (var7); +INSERT INTO test_table VALUES (var8); +INSERT INTO test_table VALUES (var9); +END +$ +SET @@sql_mode=''; +CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef'); +CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef'); +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +wxyz'ef +wxyz"ef +wxyz\bef +wxyz\nef +wxyz\ref +wxyz\tef +wxyz\\ef +wxyz\%ef +wxyz\_ef +"Dropping table test_table" +DROP TABLE test_table; +#"test_table" content after replaying the binlog +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +wxyz'ef +wxyz"ef +wxyz\bef +wxyz\nef +wxyz\ref +wxyz\tef +wxyz\\ef +wxyz\%ef +wxyz\_ef +#Clean up +DROP DATABASE mysqltest_db; +SET @@sql_mode= @org_mode; +use test; + +#End of Test for Bug#12601974 diff --git a/mysql-test/suite/binlog/t/binlog_sql_mode.test b/mysql-test/suite/binlog/t/binlog_sql_mode.test index ab4f6450543..167c8f5a96d 100644 --- a/mysql-test/suite/binlog/t/binlog_sql_mode.test +++ b/mysql-test/suite/binlog/t/binlog_sql_mode.test @@ -73,3 +73,99 @@ DROP TABLE t1; SET @@global.sql_mode= @old_sql_mode; SET @@session.binlog_format=@old_binlog_format; + +--echo +--echo # +--echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +--echo # IGNORED AND BREAKS REPLICATION +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db; +DROP TABLE IF EXISTS test_table; +--enable_warnings + +CREATE DATABASE mysqltest_db; +USE mysqltest_db; +CREATE TABLE test_table (c1 CHAR(50)); + +SET @org_mode=@@sql_mode; + +SET @@sql_mode=''; +DELIMITER $; +CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN + DECLARE var1 CHAR(50) DEFAULT param1; + DECLARE var2 CHAR(50) DEFAULT param2; + DECLARE var3 CHAR(50) DEFAULT 'abcd\bef'; + DECLARE var4 CHAR(50) DEFAULT 'abcd\nef'; + DECLARE var5 CHAR(50) DEFAULT 'abcd\ref'; + DECLARE var6 CHAR(50) DEFAULT 'abcd\tef'; + DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef'; + DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef'; + DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef'; + + INSERT INTO test_table VALUES (var1); + INSERT INTO test_table VALUES (var2); + INSERT INTO test_table VALUES (var3); + INSERT INTO test_table VALUES (var4); + INSERT INTO test_table VALUES (var5); + INSERT INTO test_table VALUES (var6); + INSERT INTO test_table VALUES (var7); + INSERT INTO test_table VALUES (var8); + INSERT INTO test_table VALUES (var9); +END +$ + +SET @@sql_mode='NO_BACKSLASH_ESCAPES'$ +CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN + DECLARE var1 CHAR(50) DEFAULT param1; + DECLARE var2 CHAR(50) DEFAULT param2; + DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef'; + DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef'; + DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref'; + DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef'; + DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef'; + DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef'; + DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef'; + + INSERT INTO test_table VALUES (var1); + INSERT INTO test_table VALUES (var2); + INSERT INTO test_table VALUES (var3); + INSERT INTO test_table VALUES (var4); + INSERT INTO test_table VALUES (var5); + INSERT INTO test_table VALUES (var6); + INSERT INTO test_table VALUES (var7); + INSERT INTO test_table VALUES (var8); + INSERT INTO test_table VALUES (var9); +END +$ + +DELIMITER ;$ +SET @@sql_mode=''; +CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef'); +CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef'); +SELECT * FROM test_table; + +let $MYSQLD_DATADIR= `select @@datadir`; +--exec $MYSQL_BINLOG --force-if-open -d mysqltest_db $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog + +--echo "Dropping table test_table" +DROP TABLE test_table; + +--exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog" + +--echo #"test_table" content after replaying the binlog +SELECT * FROM test_table; + +--echo #Clean up +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog +DROP DATABASE mysqltest_db; +SET @@sql_mode= @org_mode; +use test; + +--echo +--echo #End of Test for Bug#12601974 + + diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index 4a9f34443cb..526791d9a42 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -344,3 +344,157 @@ SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%P # Cleanup DROP TABLE t1; DROP TABLE t2; + + +--echo +--echo # +--echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +--echo # IGNORED AND BREAKS REPLICATION +--echo # + +--disable_warnings +DROP TABLE IF EXISTS test_table; +DROP FUNCTION IF EXISTS test_function; +--enable_warnings + +CREATE TABLE test_table (c1 CHAR(50)); + +SET @org_mode=@@sql_mode; + +SET @@sql_mode=''; + +PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)'; +PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?'; +DELIMITER $; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN + DECLARE char_val CHAR(50); + SELECT c1 INTO char_val FROM test_table WHERE c1=var; + RETURN char_val; +END +$ +DELIMITER ;$ + +SET @var1='abcd\'ef'; +SET @var2='abcd\"ef'; +SET @var3='abcd\bef'; +SET @var4='abcd\nef'; +SET @var5='abcd\ref'; +SET @var6='abcd\tef'; +SET @var7='abcd\\ef'; +SET @var8='abcd\%ef'; +SET @var9='abcd\_ef'; + +SET @to_var1='wxyz\'ef'; +SET @to_var2='wxyz\"ef'; +SET @to_var3='wxyz\bef'; +SET @to_var4='wxyz\nef'; +SET @to_var5='wxyz\ref'; +SET @to_var6='wxyz\tef'; +SET @to_var7='wxyz\\ef'; +SET @to_var8='wxyz\%ef'; +SET @to_var9='wxyz\_ef'; + +--echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; + +SELECT * FROM test_table; + +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; + +SELECT * FROM test_table; + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT + +--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +SELECT test_function(@to_var2); +SELECT test_function(@to_var3); +SELECT test_function(@to_var4); +SELECT test_function(@to_var5); +SELECT test_function(@to_var6); +SELECT test_function(@to_var7); +SELECT test_function(@to_var8); +SELECT test_function(@to_var9); + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DELETE FROM test_table; +DROP FUNCTION test_function; + +SET @@sql_mode='NO_BACKSLASH_ESCAPES'; +DELIMITER $; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN + DECLARE char_val CHAR(50); + SELECT c1 INTO char_val FROM test_table WHERE c1=var; + RETURN char_val; +END +$ +DELIMITER ;$ + +--echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; + +SELECT * FROM test_table; + +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; + +SELECT * FROM test_table; + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT + +--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +SELECT test_function(@to_var2); +SELECT test_function(@to_var3); +SELECT test_function(@to_var4); +SELECT test_function(@to_var5); +SELECT test_function(@to_var6); +SELECT test_function(@to_var7); +SELECT test_function(@to_var8); +SELECT test_function(@to_var9); + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING + +DROP TABLE test_table; +DROP FUNCTION test_function; +SET @@sql_mode= @org_mode; + +--echo +--echo #End of Test for Bug#12601974 -- cgit v1.2.1 From 4c74d96f51fe67d4ad29e4e430a905559859e35e Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 6 Mar 2012 13:31:18 +0200 Subject: Remove a duplicate mysql-test file innodb-index.inc. --- mysql-test/suite/innodb/include/innodb-index.inc | 26 ------------------------ 1 file changed, 26 deletions(-) delete mode 100644 mysql-test/suite/innodb/include/innodb-index.inc (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/include/innodb-index.inc b/mysql-test/suite/innodb/include/innodb-index.inc deleted file mode 100644 index 37de3162abe..00000000000 --- a/mysql-test/suite/innodb/include/innodb-index.inc +++ /dev/null @@ -1,26 +0,0 @@ ---eval create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=$charset -insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); -commit; ---error ER_DUP_ENTRY -alter table t1 add unique index (b); -insert into t1 values(8,9,'fff','fff'); -select * from t1; -show create table t1; -alter table t1 add index (b); -insert into t1 values(10,10,'kkk','iii'); -select * from t1; -select * from t1 force index(b) order by b; -explain select * from t1 force index(b) order by b; -show create table t1; -alter table t1 add unique index (c), add index (d); -insert into t1 values(11,11,'aaa','mmm'); -select * from t1; -select * from t1 force index(b) order by b; -select * from t1 force index(c) order by c; -select * from t1 force index(d) order by d; -explain select * from t1 force index(b) order by b; -explain select * from t1 force index(c) order by c; -explain select * from t1 force index(d) order by d; -show create table t1; -check table t1; -drop table t1; -- cgit v1.2.1 From a54e6e002d0297ad02461f933f35c9bcb147a1eb Mon Sep 17 00:00:00 2001 From: Manish Kumar Date: Fri, 9 Mar 2012 08:07:16 +0530 Subject: BUG#13812374 - RPL.RPL_REPORT_PORT FAILS OCCASIONALLY ON PB2 Fix - Changed the implementation of the condition check from the result file to using an assert. mysql-test/suite/rpl/r/rpl_report_port.result: Updated the result file. mysql-test/suite/rpl/t/rpl_report_port.test: Changed from the a condtional check to an assert. --- mysql-test/suite/rpl/r/rpl_report_port.result | 11 ++--------- mysql-test/suite/rpl/t/rpl_report_port.test | 28 +++++++++------------------ 2 files changed, 11 insertions(+), 28 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_report_port.result b/mysql-test/suite/rpl/r/rpl_report_port.result index d5a684241bc..ebdd3f889f3 100644 --- a/mysql-test/suite/rpl/r/rpl_report_port.result +++ b/mysql-test/suite/rpl/r/rpl_report_port.result @@ -1,15 +1,8 @@ include/master-slave.inc [connection master] -[The default value shown for the slave's port number is the actual port number of the slave] -SHOW SLAVE HOSTS; -Server_id Host Port Master_id -2 127.0.0.1 SLAVE_PORT 1 -[The default value shown for the slave's port number is the actual port number of the slave] +include/assert.inc [The default value shown for the slave's port number is the actual port number of the slave.] include/rpl_restart_server.inc [server_number=2 parameters: --report-port=9000] include/start_slave.inc [Slave restarted with the report-port set to some value] -[The value shown for the slave's port number is 9000 which is the value set for report-port] -SHOW SLAVE HOSTS; -Server_id Host Port Master_id -2 127.0.0.1 9000 1 +include/assert.inc [The value shown for the slave's port number is 9000 which is the value set for report-port.] include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_report_port.test b/mysql-test/suite/rpl/t/rpl_report_port.test index 11cb9684090..b1b71a2982c 100644 --- a/mysql-test/suite/rpl/t/rpl_report_port.test +++ b/mysql-test/suite/rpl/t/rpl_report_port.test @@ -21,16 +21,10 @@ source include/master-slave.inc; connection master; ---echo [The default value shown for the slave's port number is the actual port number of the slave] -source include/show_slave_hosts.inc; - -# SLAVE_PORT is the value of the port we should get. - -let $show_statement= SHOW SLAVE HOSTS; -let $field= Port; -let $condition= 'SLAVE_PORT'; -source include/wait_show_condition.inc; ---echo [The default value shown for the slave's port number is the actual port number of the slave] +--let $report_port= query_get_value(SHOW SLAVE HOSTS, Port, 1) +--let assert_text= The default value shown for the slave's port number is the actual port number of the slave. +--let assert_cond= $report_port = "$SLAVE_MYPORT" +--source include/assert.inc # Start the server with some value being passed to the report_port=