From af2e74f8cbbe23552648a7acae197e5a15048706 Mon Sep 17 00:00:00 2001 From: V Narayanan Date: Tue, 18 Nov 2008 11:31:03 +0530 Subject: Bug#39616: Missing quotes from .CSV crashes server When a CSV file contained comma separated elements that were not enclosed in quotes, it was causing the mysql server to crash. The old algorithm that parsed the content of a row in mysql 5.0 was assuming that the values of the fields in a .CSV file will be enclosed in quotes and will be separated by commas. This was causing the old algorithm to fail when the content of the file resembled the following 3,"sans quotes" The CSV engine that is part of mysql 5.0 was expecting the above to be "3","sans quotes" The above is just one example of where the engine was failing for what would be recognized as a valid .CSV file content otherwise. The proposed fix changes the old algorithm being used to parse rows from the .CSV file to handle two separate cases 1) When the current field of the row is enclosed in quotes 2) When the current field of the row is not enclosed in quotes --- mysql-test/r/csv.result | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index dca4e349c8a..ac1aee6cc9a 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5071,4 +5071,19 @@ select * from t1; a foo drop table t1; +create table bug39616_1(id int NOT NULL, d varchar(50) NOT NULL) ENGINE=csv; +select * from bug39616_1; +id d +1 integer sans quotes +1 string sans quotes +1 string end quotes" +1 quotes"in between" strings +1 Integer with quote and string with no quote +1 escape sequence + " \ \a within quotes +drop table bug39616_1; +create table bug39616_1(id int NOT NULL, d varchar(50) NOT NULL) ENGINE=csv; +select * from bug39616_1; +id d +drop table bug39616_1; End of 5.0 tests -- cgit v1.2.1 From d1e5808d7ed09b23a9b3b1720e43541efb66a08c Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Thu, 20 Nov 2008 15:39:39 +0100 Subject: Bug#39591: Crash if table comment is longer than 62 characters It was possible to crash a mysqld build with EXTRA_DEBUG using CREATE TABLE ... COMMENT with a specially-crafted UTF-8 string. This CS removes the check that caused it since it no longer applies in current servers anyway, and adds comments instead to avoid future confusion. --- mysql-test/r/strict.result | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 34869862a63..c31b5ea2189 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1348,6 +1348,13 @@ t1 CREATE TABLE `t1` ( `i` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*' drop table t1; +CREATE TABLE t3 (f1 INT) COMMENT 'כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן'; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `f1` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='כקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחןכקבהחן' +DROP TABLE t3; set sql_mode= 'traditional'; create table t1(col1 tinyint, col2 tinyint unsigned, col3 smallint, col4 smallint unsigned, -- cgit v1.2.1 From 85d4cbae24d0a6d86911a1845d0c3fe24f48c36e Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Fri, 28 Nov 2008 18:17:13 +0400 Subject: BUG#37245 - Full text search problem Certain boolean mode queries with truncation operator did not return matching records and calculate relevancy incorrectly. --- mysql-test/r/fulltext.result | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index e73f8af405c..6821691c9d0 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -497,3 +497,12 @@ WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref b b 5 const 4 Using where DROP TABLE t1; +CREATE TABLE t1(a CHAR(10)); +INSERT INTO t1 VALUES('aaa15'); +SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; +MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) +1 +SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1; +MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) +2 +DROP TABLE t1; -- cgit v1.2.1 From 674f88380ce50bcbbdbfbc47d39208385534aeb8 Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Fri, 28 Nov 2008 16:45:34 +0100 Subject: - Fix for Bug#40179 Test main.query_cache failing randomly on Pushbuild, test weakness - disable the test suffering from Bug#41098 Query Cache returns wrong result with concurrent insert - additional improvements Details: - Move the test for the historic bug Bug 28249 Query Cache returns wrong result with concurrent insert / certain lock into its own testscript query_cache_28249.test. - query_cache.test: - replace error numbers with error names - remove trailing spaces, replace tabs with spaces - reset of @@global.log_bin_trust_function_creators to its original value at the end of the test - query_cache_28249.test: - replace sleep 5 with poll routine - avoid random differences caused by concurrent_inserts effects - improved comments and formatting --- mysql-test/r/query_cache.result | 38 ++------------------- mysql-test/r/query_cache_28249.result | 62 +++++++++++++++++++++++++++++++++++ 2 files changed, 64 insertions(+), 36 deletions(-) create mode 100644 mysql-test/r/query_cache_28249.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 34c86d6c8c6..3c8ff8faddc 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1116,12 +1116,13 @@ create procedure `p1`() begin select a, f1() from t1; end// +SET @log_bin_trust_function_creators = @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; call p1()// a f1() 1 2 2 2 -SET GLOBAL log_bin_trust_function_creators = 0; +SET GLOBAL log_bin_trust_function_creators = @log_bin_trust_function_creators; drop procedure p1// drop function f1// drop table t1// @@ -1615,41 +1616,6 @@ id DROP PROCEDURE proc29856; DROP TABLE t1; SET GLOBAL query_cache_size= default; -Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock -set GLOBAL query_cache_type=1; -set GLOBAL query_cache_limit=10000; -set GLOBAL query_cache_min_res_unit=0; -set GLOBAL query_cache_size= 100000; -flush tables; -drop table if exists t1, t2; -create table t1 (a int); -create table t2 (a int); -insert into t1 values (1),(2),(3); -Locking table T2 with a write lock. -lock table t2 write; -Select blocked by write lock. -select *, (select count(*) from t2) from t1;; -Sleeing is ok, because selecting should be done very fast. -Inserting into table T1. -insert into t1 values (4); -Unlocking the tables. -unlock tables; -Collecting result from previously blocked select. -Next select should contain 4 rows, as the insert is long finished. -select *, (select count(*) from t2) from t1; -a (select count(*) from t2) -1 0 -2 0 -3 0 -4 0 -reset query cache; -select *, (select count(*) from t2) from t1; -a (select count(*) from t2) -1 0 -2 0 -3 0 -4 0 -drop table t1,t2; # # Bug#25132 disabled query cache: Qcache_free_blocks = 1 # diff --git a/mysql-test/r/query_cache_28249.result b/mysql-test/r/query_cache_28249.result new file mode 100644 index 00000000000..aed84911032 --- /dev/null +++ b/mysql-test/r/query_cache_28249.result @@ -0,0 +1,62 @@ +SET @query_cache_type= @@global.query_cache_type; +SET @query_cache_limit= @@global.query_cache_limit; +SET @query_cache_min_res_unit= @@global.query_cache_min_res_unit; +SET @query_cache_size= @@global.query_cache_size; +# Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock +# Establish connections user1,user2,user3 (user=root) +# Switch to connection user1 +SET GLOBAL query_cache_type=1; +SET GLOBAL query_cache_limit=10000; +SET GLOBAL query_cache_min_res_unit=0; +SET GLOBAL query_cache_size= 100000; +FLUSH TABLES; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Switch to connection user2 +LOCK TABLE t2 WRITE; +# Switch to connection user1 +# "send" the next select, "reap" the result later. +# The select will be blocked by the write lock on the t1. +SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; +# Switch to connection user3 +# Poll till the select of connection user1 is blocked by the write lock on t1. +SELECT user,command,state,info FROM information_schema.processlist +WHERE state = 'Locked' + AND info = 'SELECT *, (SELECT COUNT(*) FROM t2) FROM t1'; +user command state info +root Query Locked SELECT *, (SELECT COUNT(*) FROM t2) FROM t1 +INSERT INTO t1 VALUES (4); +# Switch to connection user2 +UNLOCK TABLES; +# Switch to connection user1 +# Collecting ("reap") the result from the previously blocked select. +# The printing of the result (varies between 3 and 4 rows) set has to be suppressed. +# Switch to connection user3 +# The next select enforces that effects of "concurrent_inserts" like the +# record with a = 4 is missing in result sets can no more happen. +SELECT 1 FROM t1 WHERE a = 4; +1 +1 +# Switch to connection user1 +# The next result set must contain 4 rows. +SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; +a (SELECT COUNT(*) FROM t2) +1 0 +2 0 +3 0 +4 0 +RESET QUERY CACHE; +SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; +a (SELECT COUNT(*) FROM t2) +1 0 +2 0 +3 0 +4 0 +DROP TABLE t1,t2; +# Switch to connection default + close connections user1,user2,user3 +SET GLOBAL query_cache_type= @query_cache_type; +SET GLOBAL query_cache_limit= @query_cache_limit; +SET GLOBAL query_cache_min_res_unit= @query_cache_min_res_unit; +SET GLOBAL query_cache_size= @query_cache_size; -- cgit v1.2.1 From f486bfbbdb8eeaca11428c7822472150f11185cc Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Sat, 29 Nov 2008 15:36:17 +0200 Subject: Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested column When the storage engine uses secondary keys clustered with the primary key MySQL was adding the primary key parts to each secondary key. In doing so it was not checking whether the index was on full columns and this resulted in the secondary keys being added to the list of covering keys even if they have partial columns. Fixed by not adding a primary key part to the list of columns that can be used for index read of the secondary keys when the primary key part is a partial key part. --- mysql-test/r/innodb_mysql.result | 84 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 84 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 2c14b1f2385..a348c63ec81 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1677,3 +1677,87 @@ select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); DROP TABLE t1; +CREATE TABLE foo (a int, b int, c char(10), +PRIMARY KEY (c(3)), +KEY b (b) +) engine=innodb; +CREATE TABLE foo2 (a int, b int, c char(10), +PRIMARY KEY (c), +KEY b (b) +) engine=innodb; +CREATE TABLE bar (a int, b int, c char(10), +PRIMARY KEY (c(3)), +KEY b (b) +) engine=myisam; +INSERT INTO foo VALUES +(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'), +(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe'); +INSERT INTO bar SELECT * FROM foo; +INSERT INTO foo2 SELECT * FROM foo; +EXPLAIN SELECT c FROM bar WHERE b>2;; +id 1 +select_type SIMPLE +table bar +type ALL +possible_keys b +key NULL +key_len NULL +ref NULL +rows 6 +Extra Using where +EXPLAIN SELECT c FROM foo WHERE b>2;; +id 1 +select_type SIMPLE +table foo +type ALL +possible_keys b +key NULL +key_len NULL +ref NULL +rows 6 +Extra Using where +EXPLAIN SELECT c FROM foo2 WHERE b>2;; +id 1 +select_type SIMPLE +table foo2 +type range +possible_keys b +key b +key_len 5 +ref NULL +rows 3 +Extra Using where; Using index +EXPLAIN SELECT c FROM bar WHERE c>2;; +id 1 +select_type SIMPLE +table bar +type ALL +possible_keys PRIMARY +key NULL +key_len NULL +ref NULL +rows 6 +Extra Using where +EXPLAIN SELECT c FROM foo WHERE c>2;; +id 1 +select_type SIMPLE +table foo +type ALL +possible_keys PRIMARY +key NULL +key_len NULL +ref NULL +rows 6 +Extra Using where +EXPLAIN SELECT c FROM foo2 WHERE c>2;; +id 1 +select_type SIMPLE +table foo2 +type index +possible_keys PRIMARY +key b +key_len 5 +ref NULL +rows 6 +Extra Using where; Using index +DROP TABLE foo, bar, foo2; -- cgit v1.2.1 From 8f39d2584ba0ba33998461aa93a714f8cadf498d Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Tue, 2 Dec 2008 11:18:01 +0100 Subject: Bug#40389: REORGANIZE PARTITION crashes when only using one partition The non documented command 'ALTER PARTITION t REORGANIZE PARTITION' (without any partitions!) which only make sense for nativly partitioned engines, such as NDB, crashes the server if there was no change of number of partitions. The problem was wrong usage of fast_end_partition function, which led to usage of a non initialized variable. --- mysql-test/r/partition_mgm.result | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/partition_mgm.result b/mysql-test/r/partition_mgm.result index 9ef220028b3..eeb9be949a0 100644 --- a/mysql-test/r/partition_mgm.result +++ b/mysql-test/r/partition_mgm.result @@ -1,4 +1,11 @@ DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT PRIMARY KEY) +ENGINE MYISAM +PARTITION BY HASH (a) +PARTITIONS 1; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +ALTER TABLE t1 REORGANIZE PARTITION; +DROP TABLE t1; create table t1 (a int) partition by range (a) subpartition by key (a) -- cgit v1.2.1 From 5f7869a22a4068ae052680630a6d062208ec27a6 Mon Sep 17 00:00:00 2001 From: Alexey Kopytov Date: Wed, 3 Dec 2008 19:15:39 +0300 Subject: Fix for bug #27483: Casting 'scientific notation type' to 'unsigned bigint' fails on windows. Visual Studio does not take into account some x86 hardware limitations which leads to incorrect results when converting large DOUBLE values to BIGINT UNSIGNED ones. Fixed by adding a workaround for double->ulonglong conversion on Windows. --- mysql-test/r/type_float.result | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index f1075604ca9..e7f17bd75a7 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -392,4 +392,17 @@ f1 + 0e0 1.0000000150475e+30 -1.0000000150475e+30 drop table t1; +create table t1(d double, u bigint unsigned); +insert into t1(d) values (9.2233720368547777e+18), +(9.223372036854779e18), +(9.22337203685479e18), +(1.84e19); +update t1 set u = d; +select * from t1; +d u +9.22337203685478e+18 9223372036854775808 +9.22337203685478e+18 9223372036854779904 +9.22337203685479e+18 9223372036854790144 +1.84e+19 18400000000000000000 +drop table t1; End of 5.0 tests -- cgit v1.2.1 From 5726574b0c11b90047e326f8b36350c82485519f Mon Sep 17 00:00:00 2001 From: Luis Soares Date: Thu, 4 Dec 2008 01:01:03 +0000 Subject: BUG#38826 Race in MYSQL_LOG::purge_logs is impossible to debug in production BUG#39325 Server crash inside MYSQL_LOG::purge_first_log halts replicaiton The patch reverses the order of the purging and updating events for log and relay-log.info/index files respectively. This solves the problem of having holes caused by crashes happening between updating info/index files and purging logs. NOTE: This is a combined patch for BUG#38826 and BUG#39325. This patch is based on bugteam tree and takes into account reviewers suggestions. --- mysql-test/r/binlog_index.result | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/binlog_index.result b/mysql-test/r/binlog_index.result index 82fc26092f4..b307b97e460 100644 --- a/mysql-test/r/binlog_index.result +++ b/mysql-test/r/binlog_index.result @@ -21,6 +21,7 @@ flush logs; *** must be a warning master-bin.000001 was not found *** Warnings: Warning 1477 Being purged log MYSQLTEST_VARDIR/log/master-bin.000001 was not found +Warning 1477 Being purged log MYSQLTEST_VARDIR/log/master-bin.000001 was not found *** must show one record, of the active binlog, left in the index file after PURGE *** show binary logs; Log_name File_size -- cgit v1.2.1 From cea55f3dc68953050dacd5366a7003e2ab29cdcf Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Thu, 4 Dec 2008 18:36:45 +0200 Subject: Bug #33420 Test 'rpl_packet' fails randomly with changed "Exec_Master_Log_Pos" Bug #41173 rpl_packet fails sporadically on pushbuild: query 'DROP TABLE t1' failed The both issues appeared to be a race between the SQL thread executing CREATE table t1 and the IO thread that is expected to stop at the consequent big size event. The two events need serialization which is implemented. The early bug required back-porting a part fixes for bug#38350 exclusively for 5.0 version. --- mysql-test/r/rpl_packet.result | 40 +++++----------------------------------- 1 file changed, 5 insertions(+), 35 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_packet.result b/mysql-test/r/rpl_packet.result index 88c63994fff..f84ce18810e 100644 --- a/mysql-test/r/rpl_packet.result +++ b/mysql-test/r/rpl_packet.result @@ -23,39 +23,9 @@ SET @@global.max_allowed_packet=4096; SET @@global.net_buffer_length=4096; STOP SLAVE; START SLAVE; -CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM; +CREATE TABLE `t1` (`f1` LONGTEXT) ENGINE=MyISAM; INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048'); -show slave status; -Slave_IO_State # -Master_Host 127.0.0.1 -Master_User root -Master_Port MASTER_MYPORT -Connect_Retry 1 -Master_Log_File master-bin.000001 -Read_Master_Log_Pos 2138 -Relay_Log_File # -Relay_Log_Pos # -Relay_Master_Log_File master-bin.000001 -Slave_IO_Running No -Slave_SQL_Running # -Replicate_Do_DB -Replicate_Ignore_DB -Replicate_Do_Table -Replicate_Ignore_Table -Replicate_Wild_Do_Table -Replicate_Wild_Ignore_Table -Last_Errno 0 -Last_Error -Skip_Counter 0 -Exec_Master_Log_Pos 2138 -Relay_Log_Space # -Until_Condition None -Until_Log_File -Until_Log_Pos 0 -Master_SSL_Allowed No -Master_SSL_CA_File -Master_SSL_CA_Path -Master_SSL_Cert -Master_SSL_Cipher -Master_SSL_Key -Seconds_Behind_Master # +Slave_IO_Running = No (expect No) +==== clean up ==== +DROP TABLE t1; +DROP TABLE t1; -- cgit v1.2.1 From b7139581325fe41ed584dfa653a2a66d211d4d67 Mon Sep 17 00:00:00 2001 From: Alexey Kopytov Date: Tue, 9 Dec 2008 11:05:36 +0300 Subject: Fixed type_float failures in --ps-protocol mode introduced by the test case for bug #27483. The reason for the failures was bug #21205 (fixed in 6.0 by dtoa, but still present in 5.0/5.1). --- mysql-test/r/type_float.result | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index e7f17bd75a7..d86c515062a 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -398,11 +398,11 @@ insert into t1(d) values (9.2233720368547777e+18), (9.22337203685479e18), (1.84e19); update t1 set u = d; -select * from t1; -d u -9.22337203685478e+18 9223372036854775808 -9.22337203685478e+18 9223372036854779904 -9.22337203685479e+18 9223372036854790144 -1.84e+19 18400000000000000000 +select u from t1; +u +9223372036854775808 +9223372036854779904 +9223372036854790144 +18400000000000000000 drop table t1; End of 5.0 tests -- cgit v1.2.1 From b3012d08673df1e7685c227067b619b4ccaa52ac Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 9 Dec 2008 13:04:28 +0400 Subject: Bug#40949 Debug version of MySQL server crashes when run OPTIMIZE on compressed table. reset diagnostics area state after error message is sent --- mysql-test/r/myisampack.result | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/myisampack.result b/mysql-test/r/myisampack.result index 5f39d318234..14b6283bf8f 100644 --- a/mysql-test/r/myisampack.result +++ b/mysql-test/r/myisampack.result @@ -27,3 +27,25 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +drop table if exists t1; +create table t1(f1 int, f2 varchar(255)); +insert into t1 values(1, 'foo'), (2, 'bar'); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +flush tables; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize error Table 'test.t1' is read only +Warnings: +Error 1036 Table 't1' is read only +drop table t1; -- cgit v1.2.1 From eb46763654e3f432d2e654681e9c079cbc3d4af0 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 9 Dec 2008 13:53:23 +0400 Subject: Bug#35796 SHOW CREATE TABLE and default value for BIT field show default value for BIT field in printable format --- mysql-test/r/type_bit.result | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index 68c0e1635a3..2a83d9b4c62 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -708,4 +708,19 @@ HEX(b1) HEX(b2) i2 1 0 100 1 0 200 DROP TABLE t1, t2; +CREATE TABLE IF NOT EXISTS t1 ( +f1 bit(2) NOT NULL default b'10', +f2 bit(14) NOT NULL default b'11110000111100' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` bit(2) NOT NULL default b'10', + `f2` bit(14) NOT NULL default b'11110000111100' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci +DROP TABLE t1; +CREATE TABLE IF NOT EXISTS t1 ( +f1 bit(2) NOT NULL default b'' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; +ERROR 42000: Invalid default value for 'f1' End of 5.0 tests -- cgit v1.2.1 From 0661c210d3813a73d678f7e9d8347762b8a984a5 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 9 Dec 2008 14:00:43 +0400 Subject: bug#35558 Wrong server metadata blows up the client the problem: FORMAT func max_length value was calculated incorrectly the fix: correct calculation of max_length --- mysql-test/r/func_str.result | 12 +++++++++--- 1 file changed, 9 insertions(+), 3 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index d0809eca65b..c121c8937d7 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -717,8 +717,6 @@ insert(_latin2'abcd',2,3,_latin2'ef'), replace(_latin2'abcd',_latin2'b',_latin2'B'), encode('abcd','ab') ; -Warnings: -Warning 1265 Data truncated for column 'format(130,10)' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -727,7 +725,7 @@ t1 CREATE TABLE `t1` ( `conv(130,16,10)` varchar(64) default NULL, `hex(130)` varchar(6) NOT NULL default '', `char(130)` varbinary(4) NOT NULL default '', - `format(130,10)` varchar(4) NOT NULL default '', + `format(130,10)` varchar(16) NOT NULL default '', `left(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '', `right(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '', `lcase(_latin2'a')` varchar(1) character set latin2 NOT NULL default '', @@ -2175,4 +2173,12 @@ SELECT HEX(c1) from v1; HEX(c1) 414243 DROP VIEW v1; +create table t1(a float); +insert into t1 values (1.33); +select format(a, 2) from t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def format(a, 2) 253 20 4 Y 0 2 8 +format(a, 2) +1.33 +drop table t1; End of 5.0 tests -- cgit v1.2.1 From d2b5e0bb940dbcf4dae0000a745cab58a351408e Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 9 Dec 2008 16:38:52 +0400 Subject: Bug#31291 ALTER TABLE CONVERT TO CHARACTER SET does not change some data types added ability for TINY[MEDIUM] text fields to be converted to greater subtype during alter if necessary(altered charset) --- mysql-test/r/alter_table.result | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 95c652055ec..d81086682f1 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -915,3 +915,19 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +create table t1 (a tinytext character set latin1); +alter table t1 convert to character set utf8; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +drop table t1; +create table t1 (a mediumtext character set latin1); +alter table t1 convert to character set utf8; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +drop table t1; -- cgit v1.2.1 From c5c64a30d44f720ff3c9a6bfcdbf11a0bcc70797 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 9 Dec 2008 16:59:47 +0400 Subject: Bug#31399 Wrong query result when doing join buffering over BIT fields if table has bit fields then uneven bits(if exist) are stored into null bits place. So we need to copy null bits in case of uneven bit field presence. --- mysql-test/r/type_bit.result | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index 2a83d9b4c62..63dec0297d0 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -723,4 +723,30 @@ CREATE TABLE IF NOT EXISTS t1 ( f1 bit(2) NOT NULL default b'' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; ERROR 42000: Invalid default value for 'f1' +create table t1bit7 (a1 bit(7) not null) engine=MyISAM; +create table t2bit7 (b1 bit(7)) engine=MyISAM; +insert into t1bit7 values (b'1100000'); +insert into t1bit7 values (b'1100001'); +insert into t1bit7 values (b'1100010'); +insert into t2bit7 values (b'1100001'); +insert into t2bit7 values (b'1100010'); +insert into t2bit7 values (b'1100110'); +select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; +bin(a1) +1100001 +1100010 +drop table t1bit7, t2bit7; +create table t1bit7 (a1 bit(15) not null) engine=MyISAM; +create table t2bit7 (b1 bit(15)) engine=MyISAM; +insert into t1bit7 values (b'110000011111111'); +insert into t1bit7 values (b'110000111111111'); +insert into t1bit7 values (b'110001011111111'); +insert into t2bit7 values (b'110000111111111'); +insert into t2bit7 values (b'110001011111111'); +insert into t2bit7 values (b'110011011111111'); +select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; +bin(a1) +110000111111111 +110001011111111 +drop table t1bit7, t2bit7; End of 5.0 tests -- cgit v1.2.1 From 71296ae2e2c3b6f87ad392aa3f45b9c53a41b2f2 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 9 Dec 2008 19:46:03 +0200 Subject: Bug #37936: ASSERT_COLUMN_MARKED_FOR_WRITE in Field_datetime::store , Field_varstring::store The code that temporary saved the bitmaps of the read set and the write set so that it can set it to all columns for debug purposes was not expecting that the table->read_set and table->write_set can be the same. And was always saving both in sequence. As a result the original value was never restored. Fixed by saving & restoring the original value only once if the two sets are the same (in a special set of functions). --- mysql-test/r/select.result | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 4b2464af688..313bc9b9630 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4358,3 +4358,29 @@ a 4 5 DROP TABLE t1; +CREATE TABLE A (date_key date); +CREATE TABLE C ( +pk int, +int_nokey int, +int_key int, +date_key date NOT NULL, +date_nokey date, +varchar_key varchar(1) +); +INSERT INTO C VALUES +(1,1,1,'0000-00-00',NULL,NULL), +(1,1,1,'0000-00-00',NULL,NULL); +SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C); +1 +SELECT COUNT(DISTINCT 1) FROM C +WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk; +COUNT(DISTINCT 1) +SELECT date_nokey FROM C +WHERE int_key IN (SELECT 1 FROM A) +HAVING date_nokey = '10:41:7' +ORDER BY date_key; +date_nokey +Warnings: +Warning 1292 Incorrect date value: '10:41:7' for column 'date_nokey' at row 1 +DROP TABLE A,C; +End of 5.1 tests -- cgit v1.2.1 From d506265f2ccbaea41f268aa58f0272273537e443 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 9 Dec 2008 20:35:02 +0200 Subject: backported the fix for bug #34773 to 5.0 --- mysql-test/r/explain.result | 48 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 48 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index a4c8432d2a4..3aa189f4a9d 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -107,3 +107,51 @@ X X X X X X X X X X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF) DROP TABLE t2; DROP TABLE t1; +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN EXTENDED SELECT 1 +FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +EXPLAIN EXTENDED SELECT 1 +FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +prepare s1 from +'EXPLAIN EXTENDED SELECT 1 + FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1'; +execute s1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +prepare s1 from +'EXPLAIN EXTENDED SELECT 1 + FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1'; +execute s1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +execute s1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` +DROP TABLE t1,t2; -- cgit v1.2.1 From f5a850109afea58f5b04af3d4b3703466a5a122c Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 10 Dec 2008 13:05:57 +0400 Subject: Bug#38227 EXTRACTVALUE doesn't work with DTD declarations Problem: XML syntax parser allowed to use quoted strings as attribute names, and tried to put them into parser state stack instead of identifiers. After that parser failed, if quoted string contained some slash characters. Fix: - Disallowing quoted strings in regular tags. - Allowing quoted string in DOCTYPE declararion, but don't push it into parse state stack (just skip it). --- mysql-test/r/xml.result | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result index 56c884343e3..41c0d6bee21 100644 --- a/mysql-test/r/xml.result +++ b/mysql-test/r/xml.result @@ -1029,4 +1029,28 @@ SELECT 1 FROM t1 ORDER BY(UPDATEXML(a, '1', '1')); 1 1 DROP TABLE t1; +SET @xml= +' + + + Title - document with document declaration + + Hi, Im a webpage with document a declaration +'; +SELECT ExtractValue(@xml, 'html/head/title'); +ExtractValue(@xml, 'html/head/title') + Title - document with document declaration +SELECT ExtractValue(@xml, 'html/body'); +ExtractValue(@xml, 'html/body') + Hi, Im a webpage with document a declaration +SELECT ExtractValue('CharData', '/xml'); +ExtractValue('CharData', '/xml') +NULL +Warnings: +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 11: STRING unexpected ('>' wanted)' +SELECT ExtractValue('CharData', '/xml'); +ExtractValue('CharData', '/xml') +NULL +Warnings: +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 17: STRING unexpected ('>' wanted)' End of 5.1 tests -- cgit v1.2.1 From 33bac53782bda23d4ff9a063c049e06c22bd3c4f Mon Sep 17 00:00:00 2001 From: Alexey Kopytov Date: Wed, 10 Dec 2008 16:07:32 +0300 Subject: Fix for a test failure on Solaris/x86/gcc introduced by the patch for bug #27483. Removed values with more than 15 significant digits from the test case. Results of reading/printing such values using system library functions depend on implementation and thus are not portable. --- mysql-test/r/type_float.result | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index d86c515062a..8caabbff047 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -393,15 +393,11 @@ f1 + 0e0 -1.0000000150475e+30 drop table t1; create table t1(d double, u bigint unsigned); -insert into t1(d) values (9.2233720368547777e+18), -(9.223372036854779e18), -(9.22337203685479e18), +insert into t1(d) values (9.22337203685479e18), (1.84e19); update t1 set u = d; select u from t1; u -9223372036854775808 -9223372036854779904 9223372036854790144 18400000000000000000 drop table t1; -- cgit v1.2.1 From 5fdd9c99c246c27f92f85e9919dea2ed537d460d Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Wed, 10 Dec 2008 18:38:27 +0400 Subject: Bug#35275 INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS omits KEY_BLOCK_SIZE Added KEY_BLOCK_SIZE option to I_S.TABLES.CREATE_OPTIONS field --- mysql-test/r/information_schema.result | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 85af461eb14..4fd0bdde063 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1707,4 +1707,9 @@ where a.VARIABLE_NAME = b.VARIABLE_NAME; a.VARIABLE_VALUE - b.VARIABLE_VALUE 2 drop table t0; +CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1; +SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; +CREATE_OPTIONS +KEY_BLOCK_SIZE=1 +DROP TABLE t1; End of 5.1 tests. -- cgit v1.2.1 From cf9126a034cd69b1271519b130d2218fd4d08a5a Mon Sep 17 00:00:00 2001 From: Chad MILLER Date: Thu, 11 Dec 2008 12:26:03 -0500 Subject: Bug#33812: mysql client incorrectly parsing DELIMITER Fix parsing of mysql client commands, especially in relation to single-line comments when --comments was specified. This is a little tricky, because we need to allow single-line comments in the middle of statements, but we don't want to allow client commands in the middle of statements. So in comment-preservation mode, we go ahead and send single-line comments to the server immediately when we encounter them on their own. This is still slightly flawed, in that it does not handle a single-line comment with leading spaces, followed by a client-side command when --comment has been enabled. But this isn't a new problem, and it is quite an edge condition. Fixing it would require a more extensive overall of how the mysql client parses commands. --- mysql-test/r/mysql.result | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index eded1a3fc3b..95bdcab6ba1 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -180,4 +180,10 @@ ERROR at line 1: DELIMITER cannot contain a backslash character 1 This is a file starting with UTF8 BOM 0xEFBBBF This is a file starting with UTF8 BOM 0xEFBBBF +delimiter +1 +2 +2 +2 +2 End of 5.0 tests -- cgit v1.2.1 From ce8ad64dd2ac2f28557020f5de07c56cc21ecaac Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Fri, 12 Dec 2008 00:57:32 +0400 Subject: Bug #40761: Assert on sum function on IF(..., CAST(longtext AS UNSIGNED), signed_val) (was: LEFT JOIN on inline view crashes server) Select from a LONGTEXT column wrapped with an expression like "IF(..., CAST(longtext_column AS UNSIGNED), smth_signed)" failed an assertion or crashed the server. IFNULL function was affected too. LONGTEXT column item has a maximum length of 32^2-1 bytes, at the same time this is a maximum possible length of any MySQL item. CAST(longtext_column AS UNSIGNED) returns some unsigned numeric result of length 32^2-1, so the result of IF/IFNULL function of this number and some other signed number will have text length of (32^2-1)+1=32^2 (one byte for the minus sign) - there is integer overflow, and the length is equal to zero. That caused assert/crash. The bug has been fixed by the same solution as in the CASE function implementation. --- mysql-test/r/func_if.result | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 7ffc957e285..16d6b435dc7 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -176,4 +176,13 @@ IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((R DROP TABLE t1; +CREATE TABLE t1 (c LONGTEXT); +INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890'); +SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; +MAX(IF(1, CAST(c AS UNSIGNED), 0)) +12345678901234567890 +SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te; +MAX(IFNULL(CAST(c AS UNSIGNED), 0)) +12345678901234567890 +DROP TABLE t1; End of 5.0 tests -- cgit v1.2.1 From 03f9b2cea60842e8b8e7e7e65bfdf96ca7e3fef8 Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Fri, 12 Dec 2008 14:59:10 +0400 Subject: rollback of bug #40761 fix --- mysql-test/r/func_if.result | 9 --------- 1 file changed, 9 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 16d6b435dc7..7ffc957e285 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -176,13 +176,4 @@ IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((R DROP TABLE t1; -CREATE TABLE t1 (c LONGTEXT); -INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890'); -SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; -MAX(IF(1, CAST(c AS UNSIGNED), 0)) -12345678901234567890 -SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te; -MAX(IFNULL(CAST(c AS UNSIGNED), 0)) -12345678901234567890 -DROP TABLE t1; End of 5.0 tests -- cgit v1.2.1 From 2b761ffde93fe951eb884c796d8f8232e78723f0 Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Fri, 12 Dec 2008 14:52:20 +0200 Subject: Bug #41383 Test commit1_innodb fails with binlog-format=row The test explicitly warned on existence of a bug in its 27th part. The expected values of prepare and commit counters changed, corrected, by fixes to bug#40221. Notice, that binlog does not have to register for a statement with the statement binlog-format because the statement rollback does not need to do anything in that mode. It's not so with the ROW format which was bug#40221 concern. Fixed with correcting the expected values of the mentioned counters and explained that with comments in the test. --- mysql-test/r/commit_1innodb.result | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index a2c1eb9a82e..766b920f630 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -770,8 +770,11 @@ call p_verify_status_increment(0, 0, 0, 0); SUCCESS insert t1 set a=4; -# Sic: a bug. Binlog did not register itself this time. -call p_verify_status_increment(1, 0, 1, 0); +# Binlog does not register itself this time for other than the 1st +# statement of the transaction with MIXED/STATEMENT binlog_format. +# It needs registering with the ROW format. Therefore 1,0,2,2 are +# the correct arguments to this test after bug#40221 fixed. +call p_verify_status_increment(1, 0, 2, 2); SUCCESS release savepoint a; -- cgit v1.2.1 From c1bf0475cf23777a5a5c25e0b4ece4dfc3e4e398 Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Fri, 12 Dec 2008 17:16:25 +0400 Subject: Bug #40761: Assert on sum function on IF(..., CAST(longtext AS UNSIGNED), signed_val) (was: LEFT JOIN on inline view crashes server) Select from a LONGTEXT column wrapped with an expression like "IF(..., CAST(longtext_column AS UNSIGNED), smth_signed)" failed an assertion or crashed the server. IFNULL function was affected too. LONGTEXT column item has a maximum length of 32^2-1 bytes, at the same time this is a maximum possible length of any MySQL item. CAST(longtext_column AS UNSIGNED) returns some unsigned numeric result of length 32^2-1, so the result of IF/IFNULL function of this number and some other signed number will have text length of (32^2-1)+1=32^2 (one byte for the minus sign) - there is integer overflow, and the length is equal to zero. That caused assert/crash. CAST AS UNSIGNED function has been modified to limit maximal length of resulting number to 67 (maximal length of DECIMAL and two characters for minus sign and dot). --- mysql-test/r/func_if.result | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 7ffc957e285..16d6b435dc7 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -176,4 +176,13 @@ IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((R DROP TABLE t1; +CREATE TABLE t1 (c LONGTEXT); +INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890'); +SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; +MAX(IF(1, CAST(c AS UNSIGNED), 0)) +12345678901234567890 +SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te; +MAX(IFNULL(CAST(c AS UNSIGNED), 0)) +12345678901234567890 +DROP TABLE t1; End of 5.0 tests -- cgit v1.2.1 From d083ceabe840e9049ae340ddedb7343ad38b1589 Mon Sep 17 00:00:00 2001 From: Andrei Elkin Date: Fri, 12 Dec 2008 21:15:56 +0200 Subject: Bug #41003 log_bin_trust_function_creators_func test explicitly warns on a bug in it The test 1. did not verify that CREATE FUNCTION shall fails in a case of active binlog and @@log_bin_trust_function_creators is zero if there is no DETERMINISTIC qualifier and super user privilege; 2. contained an explit warning on that CREATE FUNCTION actually succeeded whereas it was supposed to fail; 3. did not demand the bin-log be set ON even though it has contained the opt file explictily setting the name for the binlog file. Fixed 1-3 with modifying the test accordingly. --- .../r/log_bin_trust_function_creators_func.result | 25 ++++++++++++++++------ 1 file changed, 19 insertions(+), 6 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/log_bin_trust_function_creators_func.result b/mysql-test/r/log_bin_trust_function_creators_func.result index e109b53a8e7..50de0466a0d 100644 --- a/mysql-test/r/log_bin_trust_function_creators_func.result +++ b/mysql-test/r/log_bin_trust_function_creators_func.result @@ -10,10 +10,7 @@ create table t2 (a INT); SELECT @@log_bin_trust_function_creators; @@log_bin_trust_function_creators 0 -SELECT @@sql_log_bin; -@@sql_log_bin -1 -## Creating new function f1 ## +## Creating new function f1 fails because no DETERMINISTIC ### CREATE FUNCTION f1(a INT) RETURNS INT BEGIN IF (a < 3) THEN @@ -21,8 +18,24 @@ INSERT INTO t2 VALUES (a); END IF; RETURN 1; END| -'Bug: Create Function should give error here because non-super user'; -'is creating function here'; +ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) +## Creating new function f1 fails because non-super user ## +CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC +BEGIN +IF (a < 3) THEN +INSERT INTO t2 VALUES (a); +END IF; +RETURN 1; +END| +ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) +## Creating new function f1 succeeds ## +CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC +BEGIN +IF (a < 3) THEN +INSERT INTO t2 VALUES (a); +END IF; +RETURN 1; +END| ## Creating new table t1 ## CREATE TABLE t1 (a INT); ## Inserting some records in t1 ## -- cgit v1.2.1 From 46c19805381f3e44dbc87b5f9fe27501d8f72c6c Mon Sep 17 00:00:00 2001 From: Timothy Smith Date: Sun, 14 Dec 2008 11:24:16 -0700 Subject: Completing snapshot innodb-5.1-ss2438, part 1. Fixes Bug #35537: Innodb doesn't increment handler_update and handler_delete Detailed revision comments: r2388 | vasil | 2008-03-27 14:02:34 +0200 (Thu, 27 Mar 2008) | 7 lines branches/5.1: Swap the order in which mysql_thd, mysql_query_str and *mysql_query_str are checked for non-NULL. Suggested by: Marko r2421 | calvin | 2008-04-24 15:32:30 +0300 (Thu, 24 Apr 2008) | 6 lines branches/5.1: Fix bug#35537 - Innodb doesn't increment handler_update and handler_delete Add the calls to ha_statistic_increment() in ha_innobase::delete_row() and ha_innobase::update_row(). --- mysql-test/r/innodb.result | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 5f8c59b9a0c..7806262f052 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -3295,3 +3295,11 @@ info: Records: 5 Duplicates: 0 Warnings: 0 TRUNCATE TABLE t1; affected rows: 0 DROP TABLE t1; +Variable_name Value +Handler_update 0 +Variable_name Value +Handler_delete 0 +Variable_name Value +Handler_update 1 +Variable_name Value +Handler_delete 1 -- cgit v1.2.1 From fef8e27a79f7edbbfcfde2130fbe8fc290de6361 Mon Sep 17 00:00:00 2001 From: Timothy Smith Date: Sun, 14 Dec 2008 12:29:59 -0700 Subject: Apply InnoDB snapshot innodb-5.1-ss2637, part 2. Fixes Bug #38839: auto increment does not work properly with InnoDB after update Detailed revision comments: r2609 | sunny | 2008-08-24 01:19:05 +0300 (Sun, 24 Aug 2008) | 12 lines branches/5.1: Fix for MySQL Bug#38839. Reset the statement level last value field in prebuilt. This field tracks the last value in an autoincrement interval. We use this value to check whether we need to update a table's AUTOINC counter, if the value written to a table is less than this value then we avoid updating the table's AUTOINC value in order to reduce mutex contention. If it's not reset (e.g., after a DELETE statement) then there is the possibility of missing updates to the table's AUTOINC counter resulting in a subsequent duplicate row error message under certain conditions (see the test case for details). Bug #38839 - auto increment does not work properly with InnoDB after update --- mysql-test/r/innodb-autoinc.result | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index e000f910772..70cdc67f77e 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -169,3 +169,30 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL, 1); +DELETE FROM t1 WHERE c1 = 1; +INSERT INTO t1 VALUES (2,1); +INSERT INTO t1 VALUES (NULL,8); +SELECT * FROM t1; +c1 c2 +2 1 +3 8 +DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL, 1); +DELETE FROM t1 WHERE c1 = 1; +INSERT INTO t1 VALUES (2,1), (NULL, 8); +INSERT INTO t1 VALUES (NULL,9); +SELECT * FROM t1; +c1 c2 +2 1 +3 8 +5 9 +DROP TABLE t1; -- cgit v1.2.1 From fe769c968eac80cae884e3b59b531df5f46ed346 Mon Sep 17 00:00:00 2001 From: Timothy Smith Date: Sun, 14 Dec 2008 13:00:37 -0700 Subject: Apply InnoDB snapshot innodb-5.1-ss2858, part 2. Fixes Bug#38231: Innodb crash in lock_reset_all_on_table() on TRUNCATE + LOCK / UNLOCK branches/5.1: Fix Bug#38231 Innodb crash in lock_reset_all_on_table() on TRUNCATE + LOCK / UNLOCK In TRUNCATE TABLE and discard tablespace: do not remove table-level S and X locks and do not assert on such locks not being wait locks. Leave such locks alone. Approved by: Heikki (rb://14) --- mysql-test/r/innodb_bug38231.result | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) create mode 100644 mysql-test/r/innodb_bug38231.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_bug38231.result b/mysql-test/r/innodb_bug38231.result new file mode 100644 index 00000000000..b5d1d91ddc0 --- /dev/null +++ b/mysql-test/r/innodb_bug38231.result @@ -0,0 +1,22 @@ +SET storage_engine=InnoDB; +INSERT INTO bug38231 VALUES (1), (10), (300); +SET autocommit=0; +SELECT * FROM bug38231 FOR UPDATE; +a +1 +10 +300 +TRUNCATE TABLE bug38231; +COMMIT; +DROP TABLE bug38231; +SET storage_engine=InnoDB; +INSERT INTO bug38231 VALUES (1), (10), (300); +SET autocommit=0; +SELECT * FROM bug38231 FOR UPDATE; +a +1 +10 +300 +TRUNCATE TABLE bug38231; +COMMIT; +DROP TABLE bug38231; -- cgit v1.2.1 From b3d77069e920a600d6efe4e06041d57732fa83e0 Mon Sep 17 00:00:00 2001 From: Timothy Smith Date: Sun, 14 Dec 2008 13:31:13 -0700 Subject: Apply InnoDB snapshot innodb-5.1-ss2858, part 4. Fixes Bug #39438: Testcase for Bug#39436 crashes on 5.1 in fil_space_get_latch Detailed revision comments: r2719 | vasil | 2008-10-03 18:17:28 +0300 (Fri, 03 Oct 2008) | 49 lines branches/5.1: Fix Bug#39438 Testcase for Bug#39436 crashes on 5.1 in fil_space_get_latch In ha_innobase::info() - do not try to get the free space for a tablespace which has been discarded with ALTER TABLE ... DISCARD TABLESPACE or if the .ibd file is missing for some other reason. ibd_file_missing and tablespace_discarded are manipulated only in row_discard_tablespace_for_mysql() and in row_import_tablespace_for_mysql() and the manipulation is protected/surrounded by row_mysql_lock_data_dictionary()/row_mysql_unlock_data_dictionary() thus we do the same in ha_innobase::info() when checking the values of those members to avoid race conditions. I have tested the code-path with UNIV_DEBUG and UNIV_SYNC_DEBUG. rb://20 Reviewed by: Inaam, Calvin Approved by: Heikki --- mysql-test/r/innodb_bug39438.result | 1 + 1 file changed, 1 insertion(+) create mode 100644 mysql-test/r/innodb_bug39438.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_bug39438.result b/mysql-test/r/innodb_bug39438.result new file mode 100644 index 00000000000..195775f74c8 --- /dev/null +++ b/mysql-test/r/innodb_bug39438.result @@ -0,0 +1 @@ +SET storage_engine=InnoDB; -- cgit v1.2.1 From 220ee82cb7248dd9381805f0de3c737f97f3e577 Mon Sep 17 00:00:00 2001 From: Timothy Smith Date: Sun, 14 Dec 2008 13:42:37 -0700 Subject: Apply InnoDB snapshot innodb-5.1-2858, part 7. A follow-up fix for Bug 38839, which exposed a pre-existing bug in the autoinc handling. Detailed revision comments: r2722 | sunny | 2008-10-04 02:48:04 +0300 (Sat, 04 Oct 2008) | 18 lines branches/5.1: This bug has always existed but was masked by other errors. The fix for bug# 38839 triggered this bug. When the offset and increment are > 1 we need to calculate the next value taking into consideration the two variables. Previously we simply assumed they were 1 particularly offset was never used. MySQL does its own calculation and that's probably why it seemed to work in the past. We would return what we thought was the correct next value and then MySQL would recalculate the actual value from that and return it to the caller (e.g., handler::write_row()). Several new tests have been added that try and catch some edge cases. The tests exposed a wrap around error in MySQL next value calculation which was filed as bug 39828. The tests will need to be updated once MySQL fix that bug. One good side effect of this fix is that dict_table_t size has been reduced by 8 bytes because we have moved the autoinc_increment field to the row_prebuilt_t structure. See review-board for a detailed discussion. rb://3 --- mysql-test/r/innodb-autoinc.result | 385 +++++++++++++++++++++++++++++++++++++ 1 file changed, 385 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index 70cdc67f77e..589bf2f30b0 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -196,3 +196,388 @@ c1 c2 3 8 5 9 DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(5),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +c1 +5 +10 +110 +250 +310 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +c1 +5 +10 +110 +250 +310 +400 +410 +1000 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(0); +SELECT * FROM t1; +c1 +1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +c1 +-1 +1 +2 +10 +110 +250 +410 +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +Got one of the listed errors +SELECT * FROM t1; +c1 +-1 +1 +2 +10 +110 +250 +410 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(-1); +SELECT * FROM t1; +c1 +-1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +c1 +-2 +-1 +1 +2 +10 +250 +310 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +c1 +-2 +-1 +1 +2 +10 +250 +310 +400 +410 +1000 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(-1); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t1; +c1 +1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +INSERT INTO t1 VALUES (-2); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (250); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +c1 +1 +2 +10 +110 +210 +250 +310 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +c1 +1 +2 +10 +110 +210 +250 +310 +400 +1000 +1010 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(-1); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t1; +c1 +1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +INSERT INTO t1 VALUES (-2),(NULL),(2),(NULL); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +c1 +1 +2 +10 +110 +210 +250 +410 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +Got one of the listed errors +SELECT * FROM t1; +c1 +1 +2 +10 +110 +210 +250 +410 +1000 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (9223372036854775794); +SELECT * FROM t1; +c1 +1 +9223372036854775794 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 10 +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1; +c1 +1 +9223372036854775794 +9223372036854775796 +9223372036854775798 +9223372036854775800 +9223372036854775802 +9223372036854775804 +9223372036854775806 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551603); +SELECT * FROM t1; +c1 +1 +18446744073709551603 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 10 +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1; +c1 +0 +1 +18446744073709551603 +18446744073709551604 +18446744073709551606 +18446744073709551608 +18446744073709551610 +18446744073709551612 +18446744073709551614 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551603); +SELECT * FROM t1; +c1 +1 +18446744073709551603 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 5 +auto_increment_offset 7 +INSERT INTO t1 VALUES (NULL),(NULL), (NULL); +Got one of the listed errors +SELECT * FROM t1; +c1 +1 +18446744073709551603 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES(-9223372036854775806); +INSERT INTO t1 VALUES(-9223372036854775807); +INSERT INTO t1 VALUES(-9223372036854775808); +SELECT * FROM t1; +c1 +-9223372036854775808 +-9223372036854775807 +-9223372036854775806 +1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 3 +auto_increment_offset 3 +INSERT INTO t1 VALUES (NULL),(NULL), (NULL); +SELECT * FROM t1; +c1 +-9223372036854775808 +-9223372036854775807 +-9223372036854775806 +1 +3 +6 +9 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551610); +SELECT * FROM t1; +c1 +1 +18446744073709551610 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; +Warnings: +Warning 1292 Truncated incorrect auto_increment_increment value: '1152921504606846976' +Warning 1292 Truncated incorrect auto_increment_offset value: '1152921504606846976' +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 65535 +auto_increment_offset 65535 +INSERT INTO t1 VALUES (NULL),(NULL), (NULL); +SELECT * FROM t1; +c1 +1 +65534 +65535 +18446744073709551610 +18446744073709551615 +DROP TABLE t1; -- cgit v1.2.1 From e4dedf7ed88b9a4746167e6e0d3b509ec2d69eeb Mon Sep 17 00:00:00 2001 From: Timothy Smith Date: Sun, 14 Dec 2008 14:26:31 -0700 Subject: Apply InnoDB snapshot innodb-5.1-ss2858, part 16. Fix merge mistake which resulted in a duplicate test case for bug 38231. --- mysql-test/r/innodb_bug38231.result | 11 ----------- 1 file changed, 11 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_bug38231.result b/mysql-test/r/innodb_bug38231.result index b5d1d91ddc0..2f909779755 100644 --- a/mysql-test/r/innodb_bug38231.result +++ b/mysql-test/r/innodb_bug38231.result @@ -9,14 +9,3 @@ a TRUNCATE TABLE bug38231; COMMIT; DROP TABLE bug38231; -SET storage_engine=InnoDB; -INSERT INTO bug38231 VALUES (1), (10), (300); -SET autocommit=0; -SELECT * FROM bug38231 FOR UPDATE; -a -1 -10 -300 -TRUNCATE TABLE bug38231; -COMMIT; -DROP TABLE bug38231; -- cgit v1.2.1 From bd2629b439cac0a338c93ff93867109a8027d63b Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Tue, 16 Dec 2008 12:44:18 +0100 Subject: post push fix for bug#40595 Addition of hander function was_semi_consistent_read --- .../r/partition_innodb_semi_consistent.result | 128 +++++++++++++++++++++ 1 file changed, 128 insertions(+) create mode 100644 mysql-test/r/partition_innodb_semi_consistent.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/partition_innodb_semi_consistent.result b/mysql-test/r/partition_innodb_semi_consistent.result new file mode 100644 index 00000000000..1bb39af043a --- /dev/null +++ b/mysql-test/r/partition_innodb_semi_consistent.result @@ -0,0 +1,128 @@ +drop table if exists t1; +set binlog_format=mixed; +set session transaction isolation level read committed; +create table t1(a int not null) +engine=innodb +DEFAULT CHARSET=latin1 +PARTITION BY RANGE(a) +(PARTITION p0 VALUES LESS THAN (20), +PARTITION p1 VALUES LESS THAN MAXVALUE); +insert into t1 values (1),(2),(3),(4),(5),(6),(7); +set autocommit=0; +select * from t1 where a=3 lock in share mode; +a +3 +set binlog_format=mixed; +set session transaction isolation level read committed; +set autocommit=0; +update t1 set a=10 where a=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +commit; +update t1 set a=10 where a=5; +select * from t1 where a=2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1 where a=2 limit 1 for update; +a +2 +update t1 set a=11 where a=6; +update t1 set a=12 where a=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +update t1 set a=13 where a=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +commit; +update t1 set a=14 where a=1; +commit; +select * from t1; +a +10 +11 +14 +2 +3 +4 +7 +drop table t1; +SET SESSION AUTOCOMMIT = 0; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +set binlog_format=mixed; +# Switch to connection con1 +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (300), +PARTITION p1 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1,2); +# 1. test for locking: +BEGIN; +UPDATE t1 SET b = 12 WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +1 12 +# Switch to connection con2 +UPDATE t1 SET b = 21 WHERE a = 1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# Switch to connection con1 +SELECT * FROM t1; +a b +1 12 +ROLLBACK; +# 2. test for serialized update: +CREATE TABLE t2 (a INT); +TRUNCATE t1; +INSERT INTO t1 VALUES (1,'init'); +CREATE PROCEDURE p1() +BEGIN +UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; +INSERT INTO t2 VALUES (); +END| +BEGIN; +UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +1 init+con1 +# Switch to connection con2 +CALL p1;; +# Switch to connection con1 +SELECT * FROM t1; +a b +1 init+con1 +COMMIT; +SELECT * FROM t1; +a b +1 init+con1 +# Switch to connection con2 +SELECT * FROM t1; +a b +1 init+con1+con2 +# Switch to connection con1 +# 3. test for updated key column: +TRUNCATE t1; +TRUNCATE t2; +INSERT INTO t1 VALUES (1,'init'); +BEGIN; +UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +2 init+con1 +# Switch to connection con2 +CALL p1;; +# Switch to connection con1 +SELECT * FROM t1; +a b +2 init+con1 +COMMIT; +SELECT * FROM t1; +a b +2 init+con1 +# Switch to connection con2 +SELECT * FROM t1; +a b +2 init+con1 +DROP PROCEDURE p1; +DROP TABLE t1, t2; -- cgit v1.2.1 From ed1049f9dc24fec8fa5412addeef9fa4cfbe0646 Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Tue, 16 Dec 2008 19:09:09 +0100 Subject: Fix for Bug#39854 events_scheduling fails sporadically on pushbuild Detail: - Choose the solution that was already in place (before last fix) for 6.0 - minor cleanup in comments --- mysql-test/r/events_scheduling.result | 12 ------------ 1 file changed, 12 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/events_scheduling.result b/mysql-test/r/events_scheduling.result index 63140bffaa4..7dfd10a53f8 100644 --- a/mysql-test/r/events_scheduling.result +++ b/mysql-test/r/events_scheduling.result @@ -62,18 +62,6 @@ CREATE EVENT event_4 ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND ON COMPLETION PRESERVE DO INSERT INTO table_4 VALUES (1); -SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1; -IF(SUM(a) >= 4, 'OK', 'ERROR') -OK -SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_2; -IF(SUM(a) >= 4, 'OK', 'ERROR') -OK -SELECT IF(SUM(a) >= 1, 'OK', 'ERROR') FROM table_3; -IF(SUM(a) >= 1, 'OK', 'ERROR') -OK -SELECT IF(SUM(a) >= 1, 'OK', 'ERROR') FROM table_4; -IF(SUM(a) >= 1, 'OK', 'ERROR') -OK SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='event_2'; -- cgit v1.2.1 From aae00014e2308678d313df1120790bde5870a053 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Wed, 17 Dec 2008 17:23:21 +0400 Subject: Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines(for 5.1) --- mysql-test/r/myisampack.result | 2 +- mysql-test/r/status2.result | 73 ++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 74 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/status2.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/myisampack.result b/mysql-test/r/myisampack.result index 14b6283bf8f..7ed9b86d887 100644 --- a/mysql-test/r/myisampack.result +++ b/mysql-test/r/myisampack.result @@ -28,7 +28,7 @@ Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; drop table if exists t1; -create table t1(f1 int, f2 varchar(255)); +create table t1(f1 int, f2 char(255)); insert into t1 values(1, 'foo'), (2, 'bar'); insert into t1 select * from t1; insert into t1 select * from t1; diff --git a/mysql-test/r/status2.result b/mysql-test/r/status2.result new file mode 100644 index 00000000000..146fb2adbaf --- /dev/null +++ b/mysql-test/r/status2.result @@ -0,0 +1,73 @@ +# +# Bug#24289 Status Variable "Questions" gets wrong values with Stored Routines +# +FLUSH STATUS; +CREATE FUNCTION testQuestion() RETURNS INTEGER +BEGIN +DECLARE foo INTEGER; +DECLARE bar INTEGER; +SET foo=1; +SET bar=2; +RETURN foo; +END $$ +CREATE PROCEDURE testQuestion2() +BEGIN +SELECT 1; +END $$ +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND +DO INSERT INTO t1 VALUES(1); +Assert Questions == 7 +SHOW STATUS LIKE 'Questions'; +Variable_name Value +Questions 7 +SELECT testQuestion(); +testQuestion() +1 +Assert Questions == 9 +SHOW STATUS LIKE 'Questions'; +Variable_name Value +Questions 9 +CALL testQuestion2(); +1 +1 +Assert Questions == 11 +SHOW STATUS LIKE 'Questions'; +Variable_name Value +Questions 11 +SELECT 1; +1 +1 +Assert Questions == 13 +SHOW STATUS LIKE 'Questions'; +Variable_name Value +Questions 13 +SELECT 1; +1 +1 +Assert Questions == 14 +SHOW STATUS LIKE 'Questions'; +Variable_name Value +Questions 14 +CREATE TRIGGER trigg1 AFTER INSERT ON t1 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES (1); +END; +$$ +Assert Questions == 16 +SHOW STATUS LIKE 'Questions'; +Variable_name Value +Questions 16 +INSERT INTO t1 VALUES (1); +Assert Questions == 18 +SHOW STATUS LIKE 'Questions'; +Variable_name Value +Questions 18 +DROP PROCEDURE testQuestion2; +DROP TRIGGER trigg1; +DROP FUNCTION testQuestion; +DROP EVENT ev1; +DROP TABLE t1,t2; +End of 6.0 tests -- cgit v1.2.1 From 7df22ce11ed38ab89e854c8d0a8040f0f37d64f9 Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Wed, 17 Dec 2008 17:24:34 +0400 Subject: BUG#39746 - Debug flag breaks struct definition (server crash) Altering a table with fulltext index[es] which use pluggable fulltext parser may cause server crash in debug builds. The problem was that ALTER TABLE code wrongly assigned fulltext parser name. Also fixed that altering a table with fulltext index[es] leave stale fulltext parser locks, which prevent fulltext parsers from being uninstalled after ALTER TABLE. --- mysql-test/r/fulltext_plugin.result | 5 +++++ mysql-test/r/have_simple_parser.require | 2 ++ 2 files changed, 7 insertions(+) create mode 100644 mysql-test/r/fulltext_plugin.result create mode 100644 mysql-test/r/have_simple_parser.require (limited to 'mysql-test/r') diff --git a/mysql-test/r/fulltext_plugin.result b/mysql-test/r/fulltext_plugin.result new file mode 100644 index 00000000000..69ebbe07e9e --- /dev/null +++ b/mysql-test/r/fulltext_plugin.result @@ -0,0 +1,5 @@ +INSTALL PLUGIN simple_parser SONAME 'mypluglib.so'; +CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a) WITH PARSER simple_parser); +ALTER TABLE t1 ADD FULLTEXT(b) WITH PARSER simple_parser; +DROP TABLE t1; +UNINSTALL PLUGIN simple_parser; diff --git a/mysql-test/r/have_simple_parser.require b/mysql-test/r/have_simple_parser.require new file mode 100644 index 00000000000..0e023bd6983 --- /dev/null +++ b/mysql-test/r/have_simple_parser.require @@ -0,0 +1,2 @@ +have_simple_parser +1 -- cgit v1.2.1 From 0737be6a5a854879d8d754a49eac29ee067aaa20 Mon Sep 17 00:00:00 2001 From: Horst Hunger Date: Wed, 17 Dec 2008 14:38:02 +0100 Subject: Final fix for bug#36773: Moved patch in 5.1 from rpl to bug team tree. --- mysql-test/r/rpl_init_slave_func.result | 67 ++++++++++++++++----------------- 1 file changed, 33 insertions(+), 34 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_init_slave_func.result b/mysql-test/r/rpl_init_slave_func.result index 3861f40e7d9..0d1f4d483d8 100644 --- a/mysql-test/r/rpl_init_slave_func.result +++ b/mysql-test/r/rpl_init_slave_func.result @@ -4,44 +4,43 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -'#--------------------FN_DYNVARS_037_01-------------------------#' -SET @@global.init_slave = "SET @a = 10"; -'connect (con1,localhost,root,,)' -'connection con1' -SELECT @@global.init_slave; -@@global.init_slave -SET @a = 10 -'connection master' -'#--------------------FN_DYNVARS_037_02-------------------------#' -'check if value in slave opt file is executed' -'connection slave' -show variables like 'init_slave'; -Variable_name Value -init_slave set global max_connections=500 -show variables like 'max_connections'; -Variable_name Value -max_connections 500 -reset master; -'check if value in slave opt file doesnt apply to master' -'connection master' -show variables like 'init_slave'; -Variable_name Value -init_slave SET @a = 10 -show variables like 'max_connections'; -Variable_name Value -max_connections 151 -'connection slave' -'try creating a temporary variable in init_slave' +connection slave +SET @start_max_connections= @@global.max_connections; +SET @start_init_slave= @@global.init_slave; +SET @@global.init_slave = 'SET @@global.max_connections = @@global.max_connections + 1'; +DROP TABLE IF EXISTS t1; +CREATE TEMPORARY TABLE t1 AS SELECT @@global.init_slave AS my_column; +DESCRIBE t1; +Field Type Null Key Default Extra +my_column longtext NO NULL +DROP TABLE t1; +SELECT @@global.init_slave = 'SET @@global.max_connections = @@global.max_connections + 1'; +@@global.init_slave = 'SET @@global.max_connections = @@global.max_connections + 1' +1 +Expect 1 +SELECT @@global.max_connections= @start_max_connections; +@@global.max_connections= @start_max_connections +1 +Expect 1 +STOP SLAVE; +RESET MASTER; +RESET SLAVE; +START SLAVE; +SELECT @@global.max_connections = @start_max_connections + 1; +@@global.max_connections = @start_max_connections + 1 +1 +Expect 1 SET @@global.init_slave = "SET @a=5"; -stop slave; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -start slave; +STOP SLAVE; +RESET MASTER; +RESET SLAVE; +START SLAVE; SHOW VARIABLES LIKE 'init_slave'; Variable_name Value init_slave SET @a=5 SELECT @a; @a NULL -'Bug#35365 SET statement in init_slave not execute if slave is restarted' -set global max_connections= default; +Expect NULL +SET @@global.max_connections= @start_max_connections; +SET @@global.init_slave= @start_init_slave; -- cgit v1.2.1 From fcba66f10c0c4a577577dbe5c0f7550ffb88002c Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Wed, 17 Dec 2008 19:45:34 +0400 Subject: Bug#29263 disabled storage engines omitted in SHOW ENGINES Static disabled plugins|engines and dynamic plugins which installed but disabled are not visible in I_S PLUGINS|ENGINES tables because they are not stored into global plugin array. The fix: add such plugins|engines to plugin array with PLUGIN_IS_DISABLED status. I_S.ENGINES 'Transactions', 'XA', 'Savepoints' fields have NULL value in this case. --- mysql-test/r/warnings_engine_disabled.result | 10 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/warnings_engine_disabled.result b/mysql-test/r/warnings_engine_disabled.result index aa6170a60a7..e5d35fdaa5f 100644 --- a/mysql-test/r/warnings_engine_disabled.result +++ b/mysql-test/r/warnings_engine_disabled.result @@ -1,7 +1,15 @@ create table t1 (id int) engine=NDB; Warnings: +Warning 1286 Unknown table engine 'NDB' Warning 1266 Using storage engine MyISAM for table 't1' alter table t1 engine=NDB; Warnings: -Warning 1266 Using storage engine MyISAM for table 't1' +Warning 1286 Unknown table engine 'NDB' drop table t1; +SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='ndbcluster'; +ENGINE SUPPORT +ndbcluster NO +SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE +PLUGIN_NAME='ndbcluster'; +PLUGIN_NAME PLUGIN_STATUS +ndbcluster DISABLED -- cgit v1.2.1 From 8d8983127534fe7962ac5de383f32bce2efd1261 Mon Sep 17 00:00:00 2001 From: Timothy Smith Date: Fri, 19 Dec 2008 01:28:51 +0100 Subject: Disable part of innodb-autoinc.test, because the MySQL server asserts when compiled --with-debug, due to bug 39828, "autoinc wraps around when offset and increment > 1". This change should be reverted when that bug is fixed (and a a few other minor changes to the test as described in comments). --- mysql-test/r/innodb-autoinc.result | 12 +++++------- 1 file changed, 5 insertions(+), 7 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index 589bf2f30b0..1e4b088c6cd 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -471,10 +471,9 @@ SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 2 auto_increment_offset 10 -INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); SELECT * FROM t1; c1 -0 1 18446744073709551603 18446744073709551604 @@ -505,12 +504,13 @@ SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 5 auto_increment_offset 7 -INSERT INTO t1 VALUES (NULL),(NULL), (NULL); -Got one of the listed errors +INSERT INTO t1 VALUES (NULL),(NULL); SELECT * FROM t1; c1 1 18446744073709551603 +18446744073709551607 +18446744073709551612 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; @@ -572,12 +572,10 @@ SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 65535 auto_increment_offset 65535 -INSERT INTO t1 VALUES (NULL),(NULL), (NULL); +INSERT INTO t1 VALUES (NULL); SELECT * FROM t1; c1 1 -65534 -65535 18446744073709551610 18446744073709551615 DROP TABLE t1; -- cgit v1.2.1 From 05ae989e76aaab49537ac8b83ed8358be0476991 Mon Sep 17 00:00:00 2001 From: Sergey Petrunia Date: Fri, 19 Dec 2008 16:38:39 +0300 Subject: BUG#40974: Incorrect query results when using clause evaluated using range check - QUICK_INDEX_MERGE_SELECT deinitializes its rnd_pos() scan when it reaches EOF, but we need to make the deinitialization in QUICK_INDEX_MERGE_SELECT destructor also. This is because certain execution strategies can stop scanning without reaching EOF, then then try to do a full table scan on this table. Failure to deinitialize caused the full scan to use (already empty) table->sort and produce zero records. --- mysql-test/r/index_merge.result | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index ed397e8fdc8..f3fce29c910 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -527,4 +527,32 @@ b a y z DROP TABLE t1; +# +# BUG#40974: Incorrect query results when using clause evaluated using range check +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int); +insert into t1 values (1),(2); +create table t2(a int, b int); +insert into t2 values (1,1), (2, 1000); +create table t3 (a int, b int, filler char(100), key(a), key(b)); +insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C; +insert into t3 values (1,1,'data'); +insert into t3 values (1,1,'data'); +The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3) +explain select * from t1 +where exists (select 1 from t2, t3 +where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) +select * from t1 +where exists (select 1 from t2, t3 +where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); +a +1 +2 +drop table t0, t1, t2, t3; End of 5.0 tests -- cgit v1.2.1 From 26e804d0a78c31c9206b920245821cad42b0724f Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 23 Dec 2008 18:08:04 +0400 Subject: Bug#37575 UCASE fails on monthname The MONTHNAME/DAYNAME functions returns binary string, so the LOWER/UPPER functions are not effective on the result of MONTHNAME/DAYNAME call. Character set of the MONTHNAME/DAYNAME function result has been changed to connection character set. --- mysql-test/r/ctype_ucs.result | 53 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/r/func_time.result | 15 ++++++++++++ 2 files changed, 68 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 89e8f1f6221..8c0f1a108a6 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -1111,4 +1111,57 @@ set names latin1; select hex(char(0x41 using ucs2)); hex(char(0x41 using ucs2)) 0041 +SET character_set_connection=ucs2; +SELECT CHARSET(DAYNAME(19700101)); +CHARSET(DAYNAME(19700101)) +ucs2 +SELECT CHARSET(MONTHNAME(19700101)); +CHARSET(MONTHNAME(19700101)) +ucs2 +SELECT LOWER(DAYNAME(19700101)); +LOWER(DAYNAME(19700101)) +thursday +SELECT LOWER(MONTHNAME(19700101)); +LOWER(MONTHNAME(19700101)) +january +SELECT UPPER(DAYNAME(19700101)); +UPPER(DAYNAME(19700101)) +THURSDAY +SELECT UPPER(MONTHNAME(19700101)); +UPPER(MONTHNAME(19700101)) +JANUARY +SELECT HEX(MONTHNAME(19700101)); +HEX(MONTHNAME(19700101)) +004A0061006E0075006100720079 +SELECT HEX(DAYNAME(19700101)); +HEX(DAYNAME(19700101)) +00540068007500720073006400610079 +SET LC_TIME_NAMES=ru_RU; +SET NAMES utf8; +SET character_set_connection=ucs2; +SELECT CHARSET(DAYNAME(19700101)); +CHARSET(DAYNAME(19700101)) +ucs2 +SELECT CHARSET(MONTHNAME(19700101)); +CHARSET(MONTHNAME(19700101)) +ucs2 +SELECT LOWER(DAYNAME(19700101)); +LOWER(DAYNAME(19700101)) +четверг +SELECT LOWER(MONTHNAME(19700101)); +LOWER(MONTHNAME(19700101)) +января +SELECT UPPER(DAYNAME(19700101)); +UPPER(DAYNAME(19700101)) +ЧЕТВЕРГ +SELECT UPPER(MONTHNAME(19700101)); +UPPER(MONTHNAME(19700101)) +ЯНВАРЯ +SELECT HEX(MONTHNAME(19700101)); +HEX(MONTHNAME(19700101)) +042F043D043204300440044F +SELECT HEX(DAYNAME(19700101)); +HEX(DAYNAME(19700101)) +0427043504420432043504400433 +SET character_set_connection=latin1; End of 5.0 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index d397947d7ca..b3505795494 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -592,6 +592,21 @@ unix_timestamp('1970-01-01 03:00:01') select unix_timestamp('2038-01-19 07:14:07'); unix_timestamp('2038-01-19 07:14:07') 0 +SELECT CHARSET(DAYNAME(19700101)); +CHARSET(DAYNAME(19700101)) +latin1 +SELECT CHARSET(MONTHNAME(19700101)); +CHARSET(MONTHNAME(19700101)) +latin1 +SELECT LOWER(DAYNAME(19700101)); +LOWER(DAYNAME(19700101)) +thursday +SELECT LOWER(MONTHNAME(19700101)); +LOWER(MONTHNAME(19700101)) +january +SELECT COERCIBILITY(MONTHNAME('1970-01-01')),COERCIBILITY(DAYNAME('1970-01-01')); +COERCIBILITY(MONTHNAME('1970-01-01')) COERCIBILITY(DAYNAME('1970-01-01')) +4 4 CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time); INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08"); SELECT * from t1; -- cgit v1.2.1 From c61c1a0d60d004914cab1417868ce7a268141b10 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 23 Dec 2008 19:33:46 +0400 Subject: Bug#40104 regression with table names? On Winodws FN_DEVCHAR is ':' symbol. There is a check in mysql_create_table_no_lock() func on FN_DEVCHAR presence but this code is obsolete and unnecessary. So the fix is to remove unnecessary code. --- mysql-test/r/create.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 947890d2085..67f81cb4fa5 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1890,5 +1890,7 @@ c1 c2 DROP TABLE t1; # -- End of Bug#34274 +create table `me:i`(id int); +drop table `me:i`; End of 5.1 tests -- cgit v1.2.1 From 59543e9f80a1947aefa069730be388d8fbcb9ec7 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Wed, 24 Dec 2008 19:01:41 +0400 Subject: Bug#25830 SHOW TABLE STATUS behaves differently depending on table name replace wild_case_compare with my_wildcmp which is multibyte safe function --- mysql-test/r/lowercase_utf8.result | 9 +++++++++ 1 file changed, 9 insertions(+) create mode 100644 mysql-test/r/lowercase_utf8.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/lowercase_utf8.result b/mysql-test/r/lowercase_utf8.result new file mode 100644 index 00000000000..043226e1f64 --- /dev/null +++ b/mysql-test/r/lowercase_utf8.result @@ -0,0 +1,9 @@ +set names utf8; +create table `Ö` (id int); +show tables from test like 'Ö'; +Tables_in_test (Ö) +ö +show tables from test like 'ö'; +Tables_in_test (ö) +ö +drop table `Ö`; -- cgit v1.2.1 From 026e9c36766ba81d728e189555b8a15f0d4c52ba Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Wed, 24 Dec 2008 19:14:59 +0400 Subject: Bug#41456 SET PASSWORD hates CURRENT_USER() init user->user struct with thd->security_ctx->priv_user context if user->user is not initializied --- mysql-test/r/grant.result | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index d56020c3090..ee328d461ac 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1151,4 +1151,9 @@ drop user 'greg'@'localhost'; drop view v1; drop table test; drop function test_function; +SELECT CURRENT_USER(); +CURRENT_USER() +root@localhost +SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin"); +SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); End of 5.0 tests -- cgit v1.2.1 From a31795b82d9b2f40cb2b489c99d6d03e036f039f Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Wed, 24 Dec 2008 19:24:11 +0400 Subject: Bug#40953 SELECT query throws "ERROR 1062 (23000): Duplicate entry..." error Table could be marked dependent because it is either 1) an inner table of an outer join, or 2) it is a part of STRAIGHT_JOIN. In case of STRAIGHT_JOIN table->maybe_null should not be assigned. The fix is to set st_table::maybe_null to 'true' only for those tables which are used in outer join. --- mysql-test/r/select.result | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index c2cd2129a32..67ce231a157 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4355,4 +4355,37 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 6 DROP TABLE t1, t2; +CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', +f2 int(11) NOT NULL default '0', +f3 bigint(20) NOT NULL default '0', +f4 varchar(255) NOT NULL default '', +PRIMARY KEY (f1), +KEY key1 (f4), +KEY key2 (f2)); +CREATE TABLE t2 (f1 int(11) NOT NULL default '0', +f2 enum('A1','A2','A3') NOT NULL default 'A1', +f3 int(11) NOT NULL default '0', +PRIMARY KEY (f1), +KEY key1 (f3)); +CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', +f2 datetime NOT NULL default '1980-01-01 00:00:00', +PRIMARY KEY (f1)); +insert into t1 values (1, 1, 1, 'abc'); +insert into t1 values (2, 1, 2, 'def'); +insert into t1 values (3, 1, 2, 'def'); +insert into t2 values (1, 'A1', 1); +insert into t3 values (1, '1980-01-01'); +SELECT a.f3, cr.f4, count(*) count +FROM t2 a +STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 +LEFT JOIN +(t1 cr2 +JOIN t3 ae2 ON cr2.f3 = ae2.f1 +) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND +cr.f4 = cr2.f4 +GROUP BY a.f3, cr.f4; +f3 f4 count +1 abc 1 +1 def 2 +drop table t1, t2, t3; End of 5.0 tests -- cgit v1.2.1 From 7ac7726509e282d600643998ad8edc5bab5be35f Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Sun, 28 Dec 2008 12:33:49 +0100 Subject: Bug#40972: some sql execution lead the whole databse crashing Problem was an errornous date that lead to end partition was before the start, leading to a crash. Solution was to check greater or equal instead of only equal between start and end partition. NOTE: partitioning pruning handles incorrect dates differently than index lookup, which can give different results in a partitioned table versus a non partitioned table for queries having 'bad' dates in the where clause. --- mysql-test/r/partition_pruning.result | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index b153ad9ad47..26ddc92e97b 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -1,4 +1,16 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +CREATE TABLE t1 +(a INT NOT NULL AUTO_INCREMENT, +b DATETIME, +PRIMARY KEY (a,b), +KEY (b)) +PARTITION BY RANGE (to_days(b)) +(PARTITION p0 VALUES LESS THAN (733681) COMMENT = 'LESS THAN 2008-10-01', +PARTITION p1 VALUES LESS THAN (733712) COMMENT = 'LESS THAN 2008-11-01', +PARTITION pX VALUES LESS THAN MAXVALUE); +SELECT a,b FROM t1 WHERE b >= '2008-12-01' AND b < '2009-12-00'; +a b +DROP TABLE t1; create table t1 ( a int not null) partition by hash(a) partitions 2; insert into t1 values (1),(2),(3); explain select * from t1 where a=5 and a=6; -- cgit v1.2.1 From f67ce4761768a3ea05f8c4c11704fd5cd2065d3a Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Mon, 29 Dec 2008 16:06:53 +0400 Subject: Bug#41131 "Questions" fails to increment - ignores statements instead stored procs(5.0 ver) Added global status variable 'Queries' which represents total amount of queries executed by server including statements executed by SPs. note: It's old behaviour of 'Questions' variable. --- mysql-test/r/status.result | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 36857e22aaf..971e399a3b6 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -91,3 +91,28 @@ SHOW SESSION STATUS LIKE 'Last_query_cost'; Variable_name Value Last_query_cost 4.805836 DROP TABLE t1; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +DECLARE foo INTEGER; +DECLARE bar INTEGER; +SET foo=1; +SET bar=2; +RETURN foo; +END $$ +CREATE PROCEDURE p1() +BEGIN +SELECT 1; +END $$ +SELECT f1(); +f1() +1 +CALL p1(); +1 +1 +SELECT 9; +9 +9 +DROP PROCEDURE p1; +DROP FUNCTION f1; -- cgit v1.2.1 From c9245922d6dc5a7dedca24c160c0ae791d55aa69 Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Mon, 29 Dec 2008 16:50:51 +0400 Subject: Bug#41441 repair csv table crashes debug server The problem: data file can not be deleted on win because there is another opened instance of this file. Data file might be opened twice, on table opening stage and during write_row execution. We need to close both instances to satisfy Win. --- mysql-test/r/csv.result | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index 8bc6114dbc2..9deaf55c535 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5394,4 +5394,17 @@ select * from t1; ERROR HY000: File './test/t1.CSV' not found (Errcode: 2) unlock tables; drop table t1; +create table t1(a enum ('a') not null) engine=csv; +insert into t1 values (2); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select * from t1 limit 1; +ERROR HY000: Table 't1' is marked as crashed and should be repaired +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair Warning Data truncated for column 'a' at row 1 +test.t1 repair status OK +select * from t1 limit 1; +a +drop table t1; End of 5.1 tests -- cgit v1.2.1 From 33cbf93cedf153f502f8bd2980c71d2cc0025eda Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Wed, 31 Dec 2008 15:55:04 +0400 Subject: Bug #41363: crash of mysqld on windows with aggregate in case Execution of queries containing the CASE function of aggregate function like in "SELECT ... CASE ARGV(...) WHEN ..." crashed the server. The CASE function caches pointers to concrete comparison functions for an each pair of types of CASE-WHERE clause parameters, i.e. for the "CASE INT_RESULT WHERE REAL_RESULT THEN ... WHERE DECIMAL_RESULT ... END" function call it caches comparisons for INT_RESULT with REAL_RESULT and for INT_RESULT with DECIMAL_RESULT. Usually a result type is known after a call to the fix_fields function, however, the setup_copy_fields function call may wrap aggregate items with Item_copy_string that has STRING_RESULT result type, so setup_copy_fields may change argument result types of the CASE function after call to Item_func_case::fix_fields/fix_length_and_dec. Then the Item_func_case::find_item function tries to use comparison function for unexpected pair of the STRING_RESULT and some other type - that caused an assertion failure of server crash. The Item_func_case::fix_length_and_dec function has been modified to take into account possible STRING_RESULT result type in the presence of aggregate arguments of the CASE function. --- mysql-test/r/func_in.result | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index e761fb851fb..1e967b668c5 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -575,4 +575,16 @@ id select * from t1 where NOT id in (null, 1); id drop table t1; +CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER); +INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1); +SELECT CASE AVG (c0) WHEN c1 * c2 THEN 1 END FROM t1; +CASE AVG (c0) WHEN c1 * c2 THEN 1 END +1 +SELECT CASE c1 * c2 WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1; +CASE c1 * c2 WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END +2 +SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1; +CASE c1 WHEN c1 + 1 THEN 1 END ABS(AVG(c0)) +NULL 1.0000 +DROP TABLE t1; End of 5.1 tests -- cgit v1.2.1 From e56284128f628691dff3541d326b819b87eda571 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Mon, 5 Jan 2009 12:37:56 +0200 Subject: Reverted the fix for bug #25830 because of omissions and non-complete test case. --- mysql-test/r/lowercase_utf8.result | 9 --------- 1 file changed, 9 deletions(-) delete mode 100644 mysql-test/r/lowercase_utf8.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/lowercase_utf8.result b/mysql-test/r/lowercase_utf8.result deleted file mode 100644 index 043226e1f64..00000000000 --- a/mysql-test/r/lowercase_utf8.result +++ /dev/null @@ -1,9 +0,0 @@ -set names utf8; -create table `Ö` (id int); -show tables from test like 'Ö'; -Tables_in_test (Ö) -ö -show tables from test like 'ö'; -Tables_in_test (ö) -ö -drop table `Ö`; -- cgit v1.2.1 From dc5a0f4481e09fa7423231c154e1bb1a8f8c55d1 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Wed, 7 Jan 2009 10:11:37 -0200 Subject: Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table The problem is that INSERT INTO .. SELECT FROM .. and CREATE TABLE .. SELECT FROM a temporary table could inadvertently overwrite the locking type of the temporary table. The lock type of temporary tables should be a write lock by default. The solution is to reset the lock type of temporary tables back to its default value after they are used in a statement. --- mysql-test/r/innodb_mysql.result | 21 +++++++++++++++++++++ mysql-test/r/temp_table.result | 16 ++++++++++++++++ 2 files changed, 37 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 075b4f84f2d..a1116a78bda 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1767,4 +1767,25 @@ ref NULL rows 6 Extra Using where; Using index DROP TABLE foo, bar, foo2; +DROP TABLE IF EXISTS t1,t3,t2; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS VARCHAR(250) +BEGIN +return 'hhhhhhh' ; +END| +CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB; +BEGIN WORK; +CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB; +CREATE TEMPORARY TABLE t3 LIKE t2; +INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL); +SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl'); +PREPARE stmt1 FROM @stmt; +SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2'); +PREPARE stmt3 FROM @stmt; +EXECUTE stmt1; +COMMIT; +DEALLOCATE PREPARE stmt1; +DEALLOCATE PREPARE stmt3; +DROP TABLE t1,t3,t2; +DROP FUNCTION f1; End of 5.1 tests diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 6df09463d02..ba6b9f81a2d 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -194,4 +194,20 @@ DELETE FROM t1; SELECT * FROM t1; a b DROP TABLE t1; +DROP TABLE IF EXISTS t1,t2; +DROP FUNCTION IF EXISTS f1; +CREATE TEMPORARY TABLE t1 (a INT); +CREATE TEMPORARY TABLE t2 LIKE t1; +CREATE FUNCTION f1() RETURNS INT +BEGIN +return 1; +END| +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t1 SELECT f1(); +CREATE TABLE t3 SELECT * FROM t1; +INSERT INTO t1 SELECT f1(); +UPDATE t1,t2 SET t1.a = t2.a; +INSERT INTO t2 SELECT f1(); +DROP TABLE t1,t2,t3; +DROP FUNCTION f1; End of 5.1 tests -- cgit v1.2.1 From 17bbe30cb0770459a03bd9ae78095e8fc8e7234f Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Thu, 8 Jan 2009 10:25:31 +0100 Subject: Bug#41470: DATE_FORMAT() crashes the complete server with a valid date Passing dubious "year zero" in non-zero date (not "0000-00-00") could lead to negative value for year internally, while variable was unsigned. This led to Really Bad Things further down the line. Now doing calculations with signed type for year internally. --- mysql-test/r/date_formats.result | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 6833a7f1594..43f656e3c99 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -593,3 +593,13 @@ select str_to_date('04/30/2004 ', '%m/%d/%Y '); str_to_date('04/30/2004 ', '%m/%d/%Y ') 2004-04-30 "End of 4.1 tests" +SELECT DATE_FORMAT("0000-01-01",'%W %d %M %Y') as valid_date; +valid_date +Sunday 01 January 0000 +SELECT DATE_FORMAT("0000-02-28",'%W %d %M %Y') as valid_date; +valid_date +Tuesday 28 February 0000 +SELECT DATE_FORMAT("2009-01-01",'%W %d %M %Y') as valid_date; +valid_date +Thursday 01 January 2009 +"End of 5.0 tests" -- cgit v1.2.1 From 577e390eceb06a6c35198d0026c11b47a3d33232 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Fri, 9 Jan 2009 08:20:32 -0200 Subject: Bug#37016: TRUNCATE TABLE removes some rows but not all The special TRUNCATE TABLE (DDL) transaction wasn't being properly rolled back if a error occurred during row by row deletion. The error can be caused by a foreign key restriction imposed by InnoDB SE and would cause the server to erroneously issue a implicit commit. The solution is to rollback the transaction if a truncation via row by row deletion fails, otherwise commit. All effects of a TRUNCATE ABLE operation are rolled back if a row by row deletion fails. --- mysql-test/r/commit_1innodb.result | 6 ++-- mysql-test/r/innodb_mysql.result | 58 ++++++++++++++++++++++++++++++++++++++ 2 files changed, 61 insertions(+), 3 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 766b920f630..72e6b01bb03 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -683,10 +683,10 @@ SUCCESS # 23. DDL: RENAME TEMPORARY TABLE, does not start a transaction # No test because of Bug#8729 "rename table fails on temporary table" -# 24. DDL: TRUNCATE TEMPORARY TABLE, does not start a transaction +# 24. DDL: TRUNCATE TEMPORARY TABLE truncate table t2; -call p_verify_status_increment(2, 0, 2, 0); +call p_verify_status_increment(4, 0, 4, 0); SUCCESS commit; @@ -853,7 +853,7 @@ call p_verify_status_increment(1, 0, 1, 0); SUCCESS truncate table t3; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(4, 4, 2, 2); SUCCESS create view v1 as select * from t2; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index a1116a78bda..6fcc9415d12 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1788,4 +1788,62 @@ DEALLOCATE PREPARE stmt1; DEALLOCATE PREPARE stmt3; DROP TABLE t1,t3,t2; DROP FUNCTION f1; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TABLE t2 (id INT PRIMARY KEY, +t1_id INT, INDEX par_ind (t1_id), +FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (3,2); +SET AUTOCOMMIT = 0; +START TRANSACTION; +TRUNCATE TABLE t1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)) +SELECT * FROM t1; +id +1 +2 +COMMIT; +SELECT * FROM t1; +id +1 +2 +START TRANSACTION; +TRUNCATE TABLE t1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)) +SELECT * FROM t1; +id +1 +2 +ROLLBACK; +SELECT * FROM t1; +id +1 +2 +SET AUTOCOMMIT = 1; +START TRANSACTION; +SELECT * FROM t1; +id +1 +2 +COMMIT; +TRUNCATE TABLE t1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)) +SELECT * FROM t1; +id +1 +2 +DELETE FROM t2 WHERE id = 3; +START TRANSACTION; +SELECT * FROM t1; +id +1 +2 +TRUNCATE TABLE t1; +ROLLBACK; +SELECT * FROM t1; +id +TRUNCATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; End of 5.1 tests -- cgit v1.2.1 From 7c3ae5164738c931938195fd2184a1fff754be1c Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 9 Jan 2009 13:50:18 +0200 Subject: Bug #41437: Value stored in 'case' lacks charset, causes segfault When substituting system constant functions with a constant result the server was not expecting that the function may return NULL. Fixed by checking for NULL and returning Item_null (in the relevant collation) if the result of the system constant function was NULL. --- mysql-test/r/mysql.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 95bdcab6ba1..9bad3b9f791 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -186,4 +186,6 @@ delimiter 2 2 2 +@z:='1' @z=database() +1 NULL End of 5.0 tests -- cgit v1.2.1 From 53ba196e11ef7b7d49e679224469eb40ecbe81e6 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 9 Jan 2009 14:04:47 +0200 Subject: Bug #41543: Assertion `m_status == DA_ERROR' failed in Diagnostics_area::sql_errno No need to mask the error code returned by getting the next row to end of file when doing filesort. --- mysql-test/r/innodb_mysql_rbk.result | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) create mode 100644 mysql-test/r/innodb_mysql_rbk.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_mysql_rbk.result b/mysql-test/r/innodb_mysql_rbk.result new file mode 100644 index 00000000000..21ac4295325 --- /dev/null +++ b/mysql-test/r/innodb_mysql_rbk.result @@ -0,0 +1,21 @@ +CREATE TABLE t1(a INT, b INT NOT NULL, PRIMARY KEY (a)) ENGINE=innodb +DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +START TRANSACTION; +SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; +a b +3 3 +START TRANSACTION; +UPDATE t1 SET b=b+12 WHERE a > 2 ORDER BY a; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +ROLLBACK; +START TRANSACTION; +SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; +a b +3 3 +START TRANSACTION; +UPDATE t1 SET b=10 WHERE a > 1 ORDER BY a; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1 WHERE b = 10; +a b +DROP TABLE t1; -- cgit v1.2.1 From c6d81f84675a70f5e5ea1b8e6218788395fa19a0 Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Fri, 9 Jan 2009 15:10:03 +0100 Subject: 1. Fix for Bug#41111 events_bugs fails sporadically on pushbuild 2. Avoid bad effects of bug 41925 Warning 1366 Incorrect string value: ... for column processlist.info 3. Add poll routines which ensure that subtests meet stable scenarios. This does not change the sense of the subtests. --- mysql-test/r/events_bugs.result | 27 +++++++++++++-------------- 1 file changed, 13 insertions(+), 14 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index c698531dfec..f0fa1bc3efa 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -3,6 +3,8 @@ drop database if exists mysqltest_db1; drop database if exists mysqltest_db2; create database events_test; use events_test; +set @concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert = 0; select * from information_schema.global_variables where variable_name like 'event_scheduler'; VARIABLE_NAME VARIABLE_VALUE EVENT_SCHEDULER ON @@ -60,7 +62,7 @@ select get_lock('test_bug16407', 60); end| "Now if everything is fine the event has compiled and is locked" select /*1*/ user, host, db, info from information_schema.processlist -where info = 'select get_lock(\'test_bug16407\', 60)'; +where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; user host db info root localhost events_test select get_lock('test_bug16407', 60) select release_lock('test_bug16407'); @@ -84,7 +86,7 @@ get_lock('ee_16407_2', 60) set global event_scheduler= 1; "Another sql_mode test" set sql_mode="traditional"; -create table events_smode_test(ev_name char(10), a date) engine=myisam; +create table events_smode_test(ev_name char(10), a date); "This should never insert something" create event ee_16407_2 on schedule every 60 second do begin @@ -92,7 +94,7 @@ select get_lock('ee_16407_2', 60) /*ee_16407_2*/; select release_lock('ee_16407_2'); insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); end| -insert into events_smode_test values ('test','1980-19-02')| +insert into events_test.events_smode_test values ('test','1980-19-02')| ERROR 22007: Incorrect date value: '1980-19-02' for column 'a' at row 1 "This is ok" create event ee_16407_3 on schedule every 60 second do @@ -116,7 +118,7 @@ events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_4 select /*2*/ user, host, db, info from information_schema.processlist -where info = 'select get_lock(\'ee_16407_2\', 60)'; +where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; user host db info root localhost events_test select get_lock('ee_16407_2', 60) root localhost events_test select get_lock('ee_16407_2', 60) @@ -125,10 +127,10 @@ select release_lock('ee_16407_2'); release_lock('ee_16407_2') 1 select /*3*/ user, host, db, info from information_schema.processlist -where info = 'select get_lock(\'ee_16407_2\', 60)'; +where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; user host db info set global event_scheduler= off; -select * from events_smode_test order by ev_name, a; +select * from events_test.events_smode_test order by ev_name, a; ev_name a ee_16407_3 1980-02-19 ee_16407_3 1980-02-29 @@ -143,7 +145,7 @@ drop event ee_16407_2; drop event ee_16407_3; drop event ee_16407_4; "And now one last test regarding sql_mode and call of SP from an event" -delete from events_smode_test; +delete from events_test.events_smode_test; set sql_mode='ansi'; select get_lock('ee_16407_5', 60); get_lock('ee_16407_5', 60) @@ -166,10 +168,8 @@ call events_test.ee_16407_6_pendant(); end| "Should have 2 locked processes" select /*4*/ user, host, db, info from information_schema.processlist -where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') -order by info; +where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; user host db info -event_scheduler localhost NULL NULL root localhost events_test select get_lock('ee_16407_5', 60) root localhost events_test select get_lock('ee_16407_5', 60) select release_lock('ee_16407_5'); @@ -177,11 +177,9 @@ release_lock('ee_16407_5') 1 "Should have 0 processes locked" select /*5*/ user, host, db, info from information_schema.processlist -where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') -order by info; +where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; user host db info -event_scheduler localhost NULL NULL -select * from events_smode_test order by ev_name, a; +select * from events_test.events_smode_test order by ev_name, a; ev_name a ee_16407_6 2004-02-29 "And here we check one more time before we drop the events" @@ -740,3 +738,4 @@ name drop event e1; DROP DATABASE events_test; SET GLOBAL event_scheduler = 'ON'; +SET @@global.concurrent_insert = @concurrent_insert; -- cgit v1.2.1 From a7d03bf4edbaff35f3d9447d7e3ad0c29fb7dd62 Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Mon, 12 Jan 2009 06:32:49 +0100 Subject: Bug#31177: Server variables can't be set to their current values Bounds-checks and blocksize corrections were applied to user-input, but constants in the server were trusted implicitly. If these values did not actually meet the requirements, the user could not set change a variable, then set it back to the (wonky) factory default or maximum by explicitly specifying it (SET = vs SET =DEFAULT). Now checks also apply to the server's presets. Wonky values and maxima get corrected at startup. Consequently all non-offsetted values the user sees are valid, and users can set the variable to that exact value if they so desire. --- mysql-test/r/read_buffer_size_basic.result | 4 ++++ mysql-test/r/read_rnd_buffer_size_basic.result | 4 ++++ 2 files changed, 8 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/read_buffer_size_basic.result b/mysql-test/r/read_buffer_size_basic.result index 799f7b56235..3c007dc1515 100644 --- a/mysql-test/r/read_buffer_size_basic.result +++ b/mysql-test/r/read_buffer_size_basic.result @@ -86,6 +86,8 @@ SELECT @@global.read_buffer_size= 8200 OR @@global.read_buffer_size= 8228 ; @@global.read_buffer_size= 8200 OR @@global.read_buffer_size= 8228 1 SET @@global.read_buffer_size = 2147479553; +Warnings: +Warning 1292 Truncated incorrect read_buffer_size value: '2147479553' SELECT @@global.read_buffer_size; @@global.read_buffer_size 2147479552 @@ -114,6 +116,8 @@ SELECT @@session.read_buffer_size= 8200 OR @@session.read_buffer_size= 8228 ; SET @@session.read_buffer_size = 65530.34.; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.' at line 1 SET @@session.read_buffer_size = 2147479553; +Warnings: +Warning 1292 Truncated incorrect read_buffer_size value: '2147479553' SELECT @@session.read_buffer_size; @@session.read_buffer_size 2147479552 diff --git a/mysql-test/r/read_rnd_buffer_size_basic.result b/mysql-test/r/read_rnd_buffer_size_basic.result index c51b0591054..c6440ac032e 100644 --- a/mysql-test/r/read_rnd_buffer_size_basic.result +++ b/mysql-test/r/read_rnd_buffer_size_basic.result @@ -88,6 +88,8 @@ SELECT @@global.read_rnd_buffer_size= 8200 OR @@global.read_rnd_buffer_size= 822 @@global.read_rnd_buffer_size= 8200 OR @@global.read_rnd_buffer_size= 8228 1 SET @@global.read_rnd_buffer_size = 2147479553; +Warnings: +Warning 1292 Truncated incorrect read_rnd_buffer_size value: '2147479553' SELECT @@global.read_rnd_buffer_size; @@global.read_rnd_buffer_size 2147479552 @@ -116,6 +118,8 @@ SELECT @@session.read_rnd_buffer_size= 8200 OR @@session.read_rnd_buffer_size= 8 SET @@session.read_rnd_buffer_size = 65530.34.; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.' at line 1 SET @@session.read_rnd_buffer_size = 2147479553; +Warnings: +Warning 1292 Truncated incorrect read_rnd_buffer_size value: '2147479553' SELECT @@session.read_rnd_buffer_size; @@session.read_rnd_buffer_size 2147479552 -- cgit v1.2.1 From a13eda38ae78c42730a406f808cb36fd1b61bc57 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Mon, 12 Jan 2009 10:48:33 -0200 Subject: Post-merge fix for bug 37016: Update test case for row-based logging. --- mysql-test/r/commit_1innodb.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 72e6b01bb03..eddd31c9374 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -853,7 +853,7 @@ call p_verify_status_increment(1, 0, 1, 0); SUCCESS truncate table t3; -call p_verify_status_increment(4, 4, 2, 2); +call p_verify_status_increment(4, 4, 4, 4); SUCCESS create view v1 as select * from t2; -- cgit v1.2.1 From d657438ea041cec292f366f0ad4427b4027da4c7 Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Tue, 13 Jan 2009 14:09:24 +0100 Subject: Fix for Bug#40377 sporadic pushbuild failure in log_state: result mismatch + add workaround for bug 38124 + messages into the protocol when sessions are switched + replace error numbers by error names + reset of system variables to initial values per subtest + remove a file created by this test + minor improvements in structure and formatting --- mysql-test/r/log_state.result | 81 +++++++++++++++++++++++++------------------ 1 file changed, 48 insertions(+), 33 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 63903a034d2..5c3e3d789a1 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -1,3 +1,7 @@ +SET @old_general_log= @@global.general_log; +SET @old_general_log_file= @@global.general_log_file; +SET @old_slow_query_log= @@global.slow_query_log; +SET @old_slow_query_log_file= @@global.slow_query_log_file; set global general_log= OFF; truncate table mysql.general_log; truncate table mysql.slow_log; @@ -33,20 +37,26 @@ general_log ON log ON log_slow_queries OFF slow_query_log OFF -set session long_query_time=1; -select sleep(2); -sleep(2) +# Establish connection con1 (user=root) +# Switch to connection con1 +set @long_query_time = ; +set session long_query_time = @long_query_time; +select sleep(@long_query_time + 1); +sleep(@long_query_time + 1) 0 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text +# Switch to connection default set global slow_query_log= ON; -set session long_query_time=1; -select sleep(2); -sleep(2) +# Switch to connection con1 +set session long_query_time = @long_query_time; +select sleep(@long_query_time + 1); +sleep(@long_query_time + 1) 0 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text -TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 select sleep(2) +TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 select sleep(@long_query_time + 1) +# Switch to connection default show global variables where Variable_name = 'log' or Variable_name = 'log_slow_queries' or Variable_name = 'general_log' or Variable_name = 'slow_query_log'; @@ -92,8 +102,8 @@ slow_query_log_file # show variables like 'log_output'; Variable_name Value log_output FILE,TABLE -set global general_log_file='/not exiting path/log.master'; -ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not exiting path/log.master' +set global general_log_file='/not existing path/log.master'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master' set global general_log_file='MYSQLTEST_VARDIR'; ERROR 42000: Variable 'general_log_file' can't be set to the value of 'MYSQLTEST_VARDIR' set global general_log_file=''; @@ -153,8 +163,10 @@ select * from mysql.general_log; event_time user_host thread_id server_id command_type argument TIMESTAMP USER_HOST # 1 Query drop table t1 TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log -SET @old_general_log_state = @@global.general_log; -SET @old_slow_log_state = @@global.slow_query_log; +SET @@global.general_log = @old_general_log; +SET @@global.general_log_file = @old_general_log_file; +SET @@global.slow_query_log = @old_slow_query_log; +SET @@global.slow_query_log_file = @old_slow_query_log_file; SET GLOBAL general_log = ON; SET GLOBAL slow_query_log = ON; FLUSH TABLES WITH READ LOCK; @@ -173,10 +185,9 @@ SET GLOBAL READ_ONLY = ON; SET GLOBAL general_log = ON; SET GLOBAL slow_query_log = ON; SET GLOBAL READ_ONLY = OFF; -SET GLOBAL general_log = @old_general_log_state; -SET GLOBAL slow_query_log = @old_slow_log_state; -SET @old_general_log_state = @@global.general_log; -SET @old_slow_log_state = @@global.slow_query_log; +SET GLOBAL general_log = @old_general_log; +SET GLOBAL slow_query_log = @old_slow_query_log; +SET GLOBAL general_log = ON; SHOW VARIABLES LIKE 'general_log'; Variable_name Value general_log ON @@ -239,29 +250,24 @@ log_slow_queries ON SELECT @@slow_query_log, @@log_slow_queries; @@slow_query_log @@log_slow_queries 1 1 -SET GLOBAL general_log = @old_general_log_state; -SET GLOBAL slow_query_log = @old_slow_log_state; -set @old_general_log_file= @@global.general_log_file; -set @old_slow_query_log_file= @@global.slow_query_log_file; -set global general_log_file= concat('/not exiting path/log.maste', 'r'); -ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not exiting path/log.master' -set global general_log_file= NULL; +SET GLOBAL general_log = @old_general_log; +SET GLOBAL slow_query_log = @old_slow_query_log; +SET GLOBAL general_log_file= CONCAT('/not existing path/log.maste', 'r'); +ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master' +SET GLOBAL general_log_file= NULL; ERROR 42000: Variable 'general_log_file' can't be set to the value of 'NULL' -set global slow_query_log_file= concat('/not exiting path/log.maste', 'r'); -ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of '/not exiting path/log.master' -set global slow_query_log_file= NULL; +SET GLOBAL slow_query_log_file= CONCAT('/not existing path/log.maste', 'r'); +ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of '/not existing path/log.master' +SET GLOBAL slow_query_log_file= NULL; ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of 'NULL' -set global general_log_file= @old_general_log_file; -set global slow_query_log_file= @old_slow_query_log_file; +SET GLOBAL general_log_file= @old_general_log_file; +SET GLOBAL slow_query_log_file= @old_slow_query_log_file; # -- # -- Bug#32748: Inconsistent handling of assignments to -# -- general_log_file/slow_query_log_file. +# -- general_log_file/slow_query_log_file. # -- -SET @general_log_file_saved = @@global.general_log_file; -SET @slow_query_log_file_saved = @@global.slow_query_log_file; - SET GLOBAL general_log_file = 'bug32748.query.log'; SET GLOBAL slow_query_log_file = 'bug32748.slow.log'; @@ -270,8 +276,8 @@ Variable_name Value general_log_file bug32748.query.log slow_query_log_file bug32748.slow.log -SET GLOBAL general_log_file = @general_log_file_saved; -SET GLOBAL slow_query_log_file = @slow_query_log_file_saved; +SET GLOBAL general_log_file = @old_general_log_file; +SET GLOBAL slow_query_log_file = @old_slow_query_log_file; # -- End of Bug#32748. deprecated: @@ -298,4 +304,13 @@ SET GLOBAL general_log_file = @my_glf; SET GLOBAL slow_query_log_file = @my_sqlf; SET GLOBAL general_log = DEFAULT; SET GLOBAL slow_query_log = DEFAULT; +SET @@global.general_log = @old_general_log; +SET @@global.general_log_file = @old_general_log_file; +SET @@global.slow_query_log = @old_slow_query_log; +SET @@global.slow_query_log_file = @old_slow_query_log_file; End of 5.1 tests +# Close connection con1 +SET global general_log = @old_general_log; +SET global general_log_file = @old_general_log_file; +SET global slow_query_log = @old_slow_query_log; +SET global slow_query_log_file = @old_slow_query_log_file; -- cgit v1.2.1 From 50ad9d1fca2d3ea7f61a50f92b6952dab712209c Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Tue, 13 Jan 2009 15:04:28 +0100 Subject: Merge of fix for bug 41776 type_date.test may fail if run around midnight. into GCA tree. --- mysql-test/r/type_date.result | 13 ++++++++----- 1 file changed, 8 insertions(+), 5 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 7cb71831bce..f96e07b0c5e 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -65,12 +65,15 @@ CREATE TABLE t1(AFIELD INT); INSERT INTO t1 VALUES(1); CREATE TABLE t2(GMT VARCHAR(32)); INSERT INTO t2 VALUES('GMT-0800'); -SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) FROM t1, t2 GROUP BY t1.AFIELD; -DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) +SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) +FROM t1, t2 GROUP BY t1.AFIELD; +DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) Wed, 06 March 2002 10:11:12 GMT-0800 INSERT INTO t1 VALUES(1); -SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)), DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) FROM t1,t2 GROUP BY t1.AFIELD; -DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) +SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)), +DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) +FROM t1,t2 GROUP BY t1.AFIELD; +DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) Wed, 06 March 2002 10:11:12 GMT-0800 Wed, 06 March 2002 10:11:12 GMT-0800 drop table t1,t2; CREATE TABLE t1 (f1 time default NULL, f2 time default NULL); @@ -155,7 +158,7 @@ str_to_date( '', a ) 0000-00-00 00:00:00 NULL DROP TABLE t1; -CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b)); +CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b)); INSERT INTO t1 VALUES (DATE(NOW()), 1); SELECT COUNT(*) FROM t1 WHERE a = NOW(); COUNT(*) -- cgit v1.2.1 From 347762946e84c24dba28a4f8302159c2e35d59ec Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Wed, 14 Jan 2009 18:50:51 +0400 Subject: Fix for bug#33094: Error in upgrading from 5.0 to 5.1 when table contains triggers and #41385: Crash when attempting to repair a #mysql50# upgraded table with triggers. Problem: 1. trigger code didn't assume a table name may have a "#mysql50#" prefix, that may lead to a failing ASSERT(). 2. "ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME" failed for databases with "#mysql50#" prefix if any trigger. 3. mysqlcheck --fix-table-name didn't use UTF8 as a default character set that resulted in (parsing) errors for tables with non-latin symbols in their names and definitions of triggers. Fix: 1. properly handle table/database names with "#mysql50#" prefix. 2. handle --default-character-set mysqlcheck option; if mysqlcheck is launched with --fix-table-name or --fix-db-name set default character set to UTF8 if no --default-character-set option given. Note: if given --fix-table-name or --fix-db-name option, without --default-character-set mysqlcheck option default character set is UTF8. --- mysql-test/r/mysqlcheck.result | 55 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 55 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index e222d66c5b6..313cba51ec9 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -130,3 +130,58 @@ v1 v-1 drop view v1, `v-1`; drop table t1; +SET NAMES utf8; +CREATE TABLE `#mysql50#@` (a INT); +SHOW TABLES; +Tables_in_test +#mysql50#@ +SET NAMES DEFAULT; +mysqlcheck --fix-table-names --databases test +SET NAMES utf8; +SHOW TABLES; +Tables_in_test +@ +DROP TABLE `@`; +CREATE TABLE `я` (a INT); +SET NAMES DEFAULT; +mysqlcheck --default-character-set="latin1" --databases test +test.? +Error : Table 'test.?' doesn't exist +error : Corrupt +mysqlcheck --default-character-set="utf8" --databases test +test.я OK +SET NAMES utf8; +DROP TABLE `я`; +SET NAMES DEFAULT; +CREATE DATABASE `#mysql50#a@b`; +USE `#mysql50#a@b`; +CREATE TABLE `#mysql50#c@d` (a INT); +CREATE TABLE t1 (a INT); +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS +WHERE TRIGGER_SCHEMA="#mysql50#a@b" ORDER BY trigger_name; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +NULL #mysql50#a@b tr1 INSERT NULL #mysql50#a@b #mysql50#c@d 0 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +NULL #mysql50#a@b tr2 INSERT NULL #mysql50#a@b t1 0 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +Warnings: +Warning 1603 Triggers for table `#mysql50#a@b`.`#mysql50#c@d` have no creation context +Warning 1603 Triggers for table `#mysql50#a@b`.`t1` have no creation context +mysqlcheck --fix-db-names --fix-table-names --all-databases +USE `a@b`; +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS +WHERE TRIGGER_SCHEMA="a@b" ORDER BY trigger_name; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +NULL a@b tr1 INSERT NULL a@b c@d 0 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci +NULL a@b tr2 INSERT NULL a@b t1 0 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci +INSERT INTO `c@d` VALUES (2), (1); +SELECT * FROM `c@d`; +a +20 +10 +INSERT INTO t1 VALUES (3), (5); +SELECT * FROM t1; +a +300 +500 +DROP DATABASE `a@b`; +USE test; +End of 5.1 tests -- cgit v1.2.1 From 2dba01b2fa6240cf339076e352de922c6efaf934 Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Thu, 15 Jan 2009 00:54:25 +0400 Subject: bug#33094: Error in upgrading from 5.0 to 5.1 when table contains triggers Post-fix test failure: fixed mysqlcheck.test on Windows platforms. --- mysql-test/r/mysqlcheck.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 313cba51ec9..2febfcf7eb4 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -146,7 +146,7 @@ CREATE TABLE `я` (a INT); SET NAMES DEFAULT; mysqlcheck --default-character-set="latin1" --databases test test.? -Error : Table 'test.?' doesn't exist +Error : Table doesn't exist error : Corrupt mysqlcheck --default-character-set="utf8" --databases test test.я OK -- cgit v1.2.1