From 02f305516f3663533ca9abd68e73a64f7da51455 Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Mon, 18 Aug 2008 13:05:51 +0200 Subject: Bug#35981: ALTER EVENT causes the server to change the PRESERVE option. If [NOT] PRESERVE was not given, parser always defaulted to NOT PRESERVE, making it impossible for the "not given = no change" rule to work in ALTER EVENT. Leaving out the PRESERVE-clause defaults to NOT PRESERVE on CREATE now, and to "no change" in ALTER. mysql-test/r/events_2.result: show that giving no PRESERVE-clause to ALTER EVENT results in no change. show that giving no PRESERVE-clause to CREATE EVENT defaults to NOT PRESERVE as per the docs. Show specifically that this is also handled correctly when trying to ALTER EVENTs into the past. mysql-test/t/events_2.test: show that giving no PRESERVE-clause to ALTER EVENT results in no change. show that giving no PRESERVE-clause to CREATE EVENT defaults to NOT PRESERVE as per the docs. Show specifically that this is also handled correctly when trying to ALTER EVENTs into the past. sql/event_db_repository.cc: If ALTER EVENT was given no PRESERVE-clause (meaning "no change"), we don't know the previous PRESERVE-setting by the time we check the parse-data. If ALTER EVENT was given dates that are in the past, we don't know how to react, lacking the PRESERVE-setting. Heal this by running the check later when we have actually read the previous EVENT-data. sql/event_parse_data.cc: Change default for ON COMPLETION to indicate, "not specified." Also defer throwing errors when ALTER EVENT is given dates in the past but not PRESERVE-clause until we know the previous PRESERVE-value. sql/event_parse_data.h: Add third state for ON COMPLETION [NOT] PRESERVE (preserve, don't, not specified). Make check_dates() public so we can defer this check until deeper in the callstack where we have all the required data. sql/sql_yacc.yy: If CREATE EVENT is not given ON COMPLETION [NOT] PRESERVE, we default to NOT, as per the docs. --- mysql-test/t/events_2.test | 102 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 102 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/events_2.test b/mysql-test/t/events_2.test index 58a6fffe6f5..a50255e9f8b 100644 --- a/mysql-test/t/events_2.test +++ b/mysql-test/t/events_2.test @@ -411,6 +411,108 @@ create event очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66 on schedule every 2 year do select 1; +# +# Bug#35981: ALTER EVENT causes the server to change the PRESERVE option. +# + +create event event_35981 on schedule every 6 month on completion preserve +disable +do + select 1; + +echo The following SELECTs should all give 1; + +# show current ON_COMPLETION +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'PRESERVE'; + +# show ON_COMPLETION remains "PRESERVE" when not given in ALTER EVENT +alter event event_35981 enable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'PRESERVE'; + +# show we can change ON_COMPLETION +alter event event_35981 on completion not preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'NOT PRESERVE'; + +# show ON_COMPLETION remains "NOT PRESERVE" when not given in ALTER EVENT +alter event event_35981 disable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'NOT PRESERVE'; + +# show we can change ON_COMPLETION +alter event event_35981 on completion preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'PRESERVE'; + + +drop event event_35981; + +create event event_35981 on schedule every 6 month disable +do + select 1; + +# show that the defaults for CREATE EVENT are still correct (NOT PRESERVE) +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'NOT PRESERVE'; + +drop event event_35981; + + +# show that backdating doesn't break + +create event event_35981 on schedule every 1 hour starts current_timestamp + on completion not preserve +do + select 1; + +# should fail thanks to above's NOT PRESERVE +--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; + +drop event event_35981; + +create event event_35981 on schedule every 1 hour starts current_timestamp + on completion not preserve +do + select 1; + +# succeed with warning +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; + +drop event event_35981; + + + +create event event_35981 on schedule every 1 hour starts current_timestamp + on completion preserve +do + select 1; + +# this should succeed thanks to above PRESERVE! give a warning though. +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; + +# this should fail, as the event would have passed already +--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion not preserve; + +# should succeed giving a warning +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; + +drop event event_35981; + # # End of tests # -- cgit v1.2.1 From 47c3b2e12bac01a3322f0ebc0ede10ae8bc976f3 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Mon, 1 Sep 2008 13:25:19 +0200 Subject: Bug#38120: main.partition fails sporadically sporadic failures due to full disk. Fix by truncating general_log before altering it. (if running the full main-test, it can be big). mysql-test/r/partition.result: Bug#38120: main.partition fails sporadically updated result file mysql-test/t/partition.test: Bug#38120: main.partition fails sporadically Truncating general_log before altering it. (if running the full main-test, it can be big when altering it). --- mysql-test/t/partition.test | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 5270eced05f..64d8f7096d0 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1589,13 +1589,15 @@ drop table t; # USE mysql; +TRUNCATE TABLE general_log; +SET @old_general_log_state = @@global.general_log; SET GLOBAL general_log = 0; ALTER TABLE general_log ENGINE = MyISAM; --error ER_WRONG_USAGE ALTER TABLE general_log PARTITION BY RANGE (TO_DAYS(event_time)) (PARTITION p0 VALUES LESS THAN (733144), PARTITION p1 VALUES LESS THAN (3000000)); ALTER TABLE general_log ENGINE = CSV; -SET GLOBAL general_log = default; +SET GLOBAL general_log = @old_general_log_state; use test; # -- cgit v1.2.1 From 94fad7c0c242a15dd90be9fe061d0661a0fedd16 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 15 Sep 2008 15:29:31 +0500 Subject: Bug#35720 ucs2 + pad_char_to_full_length = failure Problem: with @@sql_mode=pad_char_to_full_length a CHAR column returned additional garbage after trailing space characters due to incorrect my_charpos() call. Fix: call my_charpos() with correct arguments. --- mysql-test/t/ctype_ucs.test | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index db609777c8d..cb371bc0ca6 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -678,6 +678,17 @@ select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062); select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); drop table t1; +# +# Bug#35720 ucs2 + pad_char_to_full_length = failure +# +CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET UCS2); +INSERT INTO t1 VALUES ('a'); +SET @@sql_mode=pad_char_to_full_length; +SELECT HEX(s1) FROM t1; +SET @@sql_mode=default; +SELECT HEX(s1) FROM t1; +DROP TABLE t1; + set collation_connection=ucs2_general_ci; --source include/ctype_regex.inc set names latin1; -- cgit v1.2.1 From ebd3a6e452c13510d13f82b8fe83850ae41f1b5c Mon Sep 17 00:00:00 2001 From: Patrick Crews Date: Mon, 15 Sep 2008 15:34:39 -0400 Subject: Bug#37938 Test "mysqldump" lacks various INSERT statements / values Moved fix for this bug to 5.0 as other mysqldump bugs seem tied to concurrent_insert being on Setting concurrent_insert off during this test as INSERTs weren't being completely processed before the calls to mysqldump, resulting in failing tests. Altered .test file to turn concurrent_insert off during the test and to restore it to whatever the value was at the start of the test when complete. Re-recorded .result file to account for changes to variables in the test. --- mysql-test/t/mysqldump.test | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 88053ae95fb..2f11685385f 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -5,6 +5,14 @@ # Binlog is required --source include/have_log_bin.inc + +--echo Bug#37938 - Test "mysqldump" lacks various insert statements +--echo Turn off concurrent inserts to avoid random errors +--echo NOTE: We reset the variable back to saved value at the end of test +SET @OLD_CONCURRENT_INSERT = @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT = 0; + + --disable_warnings DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa, t3; drop database if exists mysqldump_test_db; @@ -1594,6 +1602,10 @@ DROP TABLE t1,t2; --replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// / on [0-9 :-]+/ on DATE/ --exec $MYSQL_DUMP test +# We reset concurrent_inserts value to whatever it was at the start of the test +SET @@GLOBAL.CONCURRENT_INSERT = @OLD_CONCURRENT_INSERT; + + --echo # --echo # End of 5.0 tests --echo # -- cgit v1.2.1 From d714d29058fcb6320f385c87a8f0809c73edf7df Mon Sep 17 00:00:00 2001 From: Narayanan V Date: Tue, 16 Sep 2008 18:37:59 +0530 Subject: Bug#38338: REPLACE causes last_insert_id() to return an incorrect value Fix the write_record function to record auto increment values in a consistent way. mysql-test/r/auto_increment.result: Updated the test result file with the output of the new test case added to verify this bug. mysql-test/t/auto_increment.test: Added a new test case to verify this bug. sql/sql_insert.cc: The algorithm for the write_record function in sql_insert.cc is (more emphasis given to the parts that deal with the autogenerated values) 1) If a write fails 1.1) save the autogenerated value to avoid thd->insert_id_for_cur_row to become 0. 1.2) 2) record the first successful insert id. explanation of the failure -------------------------- As long as 1.1) was executed 2) worked fine. 1.1) was always executed when REPLACE worked with the last row update optimization, but in cases where 1.1) was not executed 2) would fail and would result in the autogenerated value not being saved. solution -------- repeat a check for thd->insert_id_for_cur_row being zero similar to 1.1) before 2) and ensure that the correct value is saved. --- mysql-test/t/auto_increment.test | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index ff92c743960..47458c1f054 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -314,5 +314,15 @@ insert into t1 values(null,0,0,null); # this will delete two rows replace into t1 values(null,1,0,null); select last_insert_id(); +drop table t1; +# Test of REPLACE when it does a INSERT+DELETE for all the conflicting rows +# (i.e.) when there are three rows conflicting in unique key columns with +# a row that is being inserted, all the three rows will be deleted and then +# the new rows will be inserted. +create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e)); +insert into t1 values(null,1,1,1,now()); +insert into t1 values(null,0,0,0,null); +replace into t1 values(null,1,0,2,null); +select last_insert_id(); drop table t1; -- cgit v1.2.1 From 635887dcee35a63fc67f81104decba54ecfc40fb Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Tue, 16 Sep 2008 19:05:30 +0200 Subject: Fix for Bug#38184 : main.federated fails sporadically Details: - backport of some improvements which prevent sporadic failures from 5.1 to 5.0 - @@GLOBAL.CONCURRENT_INSERT= 0 also for slave server - --sorted_result before all selects which have result sets with more than one row - Replace error numbers by error names --- mysql-test/t/federated.test | 105 ++++++++++++++++++++++++++++++++++---------- 1 file changed, 83 insertions(+), 22 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 2d98c51a548..8bbb87ccd9c 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1,6 +1,22 @@ -source include/federated.inc; +# Note: This test is tricky. It reuses the prerequisites generated for +# replication tests (master+slave server and connections) for its +# own purposes. But the replication feature itself is stopped. +# + + +--source include/federated.inc + +connection default; + +# Disable concurrent inserts to avoid test failures when reading +# data from concurrent connections (insert might return before +# the data is actually in the table). +SET @OLD_MASTER_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; connection slave; +SET @OLD_SLAVE_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, @@ -11,7 +27,7 @@ CREATE TABLE federated.t1 ( connection master; DROP TABLE IF EXISTS federated.t1; # test too many items (malformed) in the comment string url ---error 1432 +--error ER_FOREIGN_DATA_STRING_INVALID_CANT_CREATE CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' @@ -20,7 +36,7 @@ CREATE TABLE federated.t1 ( CONNECTION='mysql://root@127.0.0.1:@/too/many/items/federated/t1'; # test not enough items (malformed) in the comment string url ---error 1432 +--error ER_FOREIGN_DATA_STRING_INVALID_CANT_CREATE CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' @@ -36,7 +52,7 @@ eval CREATE TABLE federated.t1 ( ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t3'; ---error 1431 +--error ER_FOREIGN_DATA_SOURCE_DOESNT_EXIST SELECT * FROM federated.t1; DROP TABLE federated.t1; @@ -48,7 +64,7 @@ eval CREATE TABLE federated.t1 ( ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://user:pass@127.0.0.1:$SLAVE_MYPORT/federated/t1'; ---error 1429 +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE SELECT * FROM federated.t1; DROP TABLE federated.t1; @@ -64,6 +80,7 @@ eval CREATE TABLE federated.t1 ( INSERT INTO federated.t1 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t1 (id, name) VALUES (2, 'fee'); +--sorted_result SELECT * FROM federated.t1; DELETE FROM federated.t1; DROP TABLE federated.t1; @@ -84,7 +101,7 @@ eval SHOW CREATE TABLE federated.t2; INSERT INTO federated.t2 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t2 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t2; +SELECT * FROM federated.t2 ORDER BY id, name; DROP TABLE federated.t2; connection slave; @@ -111,7 +128,7 @@ eval CREATE TABLE federated.t1 ( INSERT INTO federated.t1 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t1 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t1; +SELECT * FROM federated.t1 ORDER BY id,name; DELETE FROM federated.t1; DROP TABLE IF EXISTS federated.t1; @@ -126,7 +143,7 @@ eval CREATE TABLE federated.`t1%` ( INSERT INTO federated.`t1%` (id, name) VALUES (1, 'foo'); INSERT INTO federated.`t1%` (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.`t1%`; +SELECT * FROM federated.`t1%` ORDER BY id, name; DELETE FROM federated.`t1%`; DROP TABLE IF EXISTS federated.`t1%`; @@ -166,12 +183,14 @@ INSERT INTO federated.t1 (name, other) VALUES ('Ninth Name', 99999); INSERT INTO federated.t1 (name, other) VALUES ('Tenth Name', 101010); # basic select +--sorted_result SELECT * FROM federated.t1; # with PRIMARY KEY index_read_idx SELECT * FROM federated.t1 WHERE id = 5; SELECT * FROM federated.t1 WHERE name = 'Sixth Name'; SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name'; SELECT * FROM federated.t1 WHERE name = 'Sixth Name' AND other = 44444; +--sorted_result SELECT * FROM federated.t1 WHERE name like '%th%'; UPDATE federated.t1 SET name = '3rd name' WHERE id = 3; SELECT * FROM federated.t1 WHERE name = '3rd name'; @@ -243,6 +262,7 @@ INSERT INTO federated.t1 (name, other, created) VALUES ('Tenth Name', 101010, '2005-03-12 12:00:01'); # basic select +--sorted_result SELECT * FROM federated.t1; # with PRIMARY KEY index_read_idx SELECT * FROM federated.t1 WHERE id = 5; @@ -251,6 +271,7 @@ SELECT * FROM federated.t1 WHERE id = 5; SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name'; # with regular key index_read -> index_read_idx SELECT * FROM federated.t1 WHERE other = 44444; +--sorted_result SELECT * FROM federated.t1 WHERE name like '%th%'; # update - update_row, index_read_idx UPDATE federated.t1 SET name = '3rd name' WHERE id = 3; @@ -303,9 +324,12 @@ INSERT INTO federated.t1 (name, other) VALUES ('Eigth Name', 88888); INSERT INTO federated.t1 (name, other) VALUES ('Ninth Name', 99999); INSERT INTO federated.t1 (other) VALUES ('fee fie foe fum'); +--sorted_result SELECT * FROM federated.t1 WHERE other IS NULL; +--sorted_result SELECT * FROM federated.t1 WHERE name IS NULL; SELECT * FROM federated.t1 WHERE name IS NULL and other IS NULL; +--sorted_result SELECT * FROM federated.t1 WHERE name IS NULL or other IS NULL; UPDATE federated.t1 @@ -316,6 +340,7 @@ UPDATE federated.t1 SET other = 'two two two two' WHERE name = 'Second Name'; UPDATE federated.t1 SET other = 'seven seven' WHERE name like 'Sev%'; UPDATE federated.t1 SET name = 'Tenth Name' WHERE other like 'fee fie%'; SELECT * FROM federated.t1 WHERE name IS NULL OR other IS NULL ; +--sorted_result SELECT * FROM federated.t1; # test multi-keys @@ -386,6 +411,7 @@ INSERT INTO federated.t1 (name, bincol, floatval, other) VALUES ('second', 0x66, 22.22, 2222); INSERT INTO federated.t1 (name, bincol, floatval, other) VALUES ('third', 'g', 22.22, 2222); +--sorted_result SELECT * FROM federated.t1; SELECT * FROM federated.t1 WHERE name = 'second'; SELECT * FROM federated.t1 WHERE bincol= 'f'; @@ -394,6 +420,7 @@ SELECT * FROM federated.t1 WHERE bincol= 0x67; SELECT * FROM federated.t1 WHERE bincol= 'g'; SELECT * FROM federated.t1 WHERE floatval=11.11; SELECT * FROM federated.t1 WHERE name='third'; +--sorted_result SELECT * FROM federated.t1 WHERE other=2222; SELECT * FROM federated.t1 WHERE name='third' and other=2222; @@ -467,32 +494,47 @@ SELECT * FROM federated.t1 WHERE id = 5 SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' AND col3 = 5 AND col4 = 55555; +--sorted_result SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'five 5 five five 5') OR (col2 = 'three Three' AND col3 = 33); SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'Two two') OR (col2 = 444 AND col3 = 4444444); +--sorted_result SELECT * FROM federated.t1 WHERE id = 1 OR col1 = 10 OR col2 = 'Two two' OR col3 = 33 OR col4 = 4444444; +--sorted_result SELECT * FROM federated.t1 WHERE id > 5; +--sorted_result SELECT * FROM federated.t1 WHERE id >= 5; +--sorted_result SELECT * FROM federated.t1 WHERE id < 5; +--sorted_result SELECT * FROM federated.t1 WHERE id <= 5; +--sorted_result SELECT * FROM federated.t1 WHERE id != 5; +--sorted_result SELECT * FROM federated.t1 WHERE id > 3 AND id < 7; +--sorted_result SELECT * FROM federated.t1 WHERE id > 3 AND id <= 7; +--sorted_result SELECT * FROM federated.t1 WHERE id >= 3 AND id <= 7; SELECT * FROM federated.t1 WHERE id < 3 AND id <= 7; SELECT * FROM federated.t1 WHERE id < 3 AND id > 7; +--sorted_result SELECT * FROM federated.t1 WHERE id < 3 OR id > 7; SELECT * FROM federated.t1 WHERE col2 = 'three Three'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 > 'one'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 LIKE 's%'; SELECT * FROM federated.t1 WHERE col2 LIKE 'si%'; SELECT * FROM federated.t1 WHERE col2 LIKE 'se%'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 NOT LIKE 'e%'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 <> 'one One'; # more multi-column indexes, in the primary key @@ -546,13 +588,19 @@ SELECT * FROM federated.t1 WHERE col3 = 'bababababa'; SELECT * FROM federated.t1 WHERE col1 = 'gggg' AND col2 = 'ggggggggggggggggggg'; SELECT * FROM federated.t1 WHERE col1 = 'gggg' AND col3 = 'gagagagaga'; SELECT * FROM federated.t1 WHERE col1 = 'ffff' AND col4 = 'fcfcfcfcfcfcfcfc'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 > 'bbbb'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 >= 'bbbb'; SELECT * FROM federated.t1 WHERE col1 < 'bbbb'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 <= 'bbbb'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 <> 'bbbb'; SELECT * FROM federated.t1 WHERE col1 LIKE 'b%'; +--sorted_result SELECT * FROM federated.t1 WHERE col4 LIKE '%b%'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 NOT LIKE 'c%'; SELECT * FROM federated.t1 WHERE col4 NOT LIKE '%c%'; connection slave; @@ -583,11 +631,13 @@ INSERT INTO federated.t1 VALUES ('ccd', '112', 'zzzz'); # let's see what the foreign database says connection slave; +--sorted_result SELECT col3 FROM federated.t1 WHERE ( (col1 = 'aaa' AND col2 >= '111') OR col1 > 'aaa') AND (col1 < 'ccc' OR ( col1 = 'ccc' AND col2 <= '111')); connection master; +--sorted_result SELECT col3 FROM federated.t1 WHERE ( (col1 = 'aaa' AND col2 >= '111') OR col1 > 'aaa') AND (col1 < 'ccc' OR ( col1 = 'ccc' AND col2 <= '111')); @@ -622,6 +672,7 @@ INSERT INTO federated.t1 (name, floatval, other) VALUES ('foo', 33.33333332, NULL); INSERT INTO federated.t1 (name, floatval, other) VALUES (0, 00.3333, NULL); +--sorted_result SELECT * FROM federated.t1; SELECT count(*) FROM federated.t1 WHERE id IS NULL @@ -651,7 +702,8 @@ eval CREATE TABLE federated.t1 ( INSERT INTO federated.t1 VALUES (1, " MySQL supports a number of column types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these column types, and then provides a more detailed description of the properties of the types in each category, and a summary of the column type storage requirements. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values."); INSERT INTO federated.t1 VALUES (2, "All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE."); INSERT INTO federated.t1 VALUES (3, " A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. "); -INSERT INTO federated.t1 VALUES(4, "Die bersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest fr jemanden, der seine Zielsprache ernst nimmt:"); +INSERT INTO federated.t1 VALUES(4, "Die Übersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest für jemanden, der seine Zielsprache ernst nimmt:"); +--sorted_result SELECT * FROM federated.t1; connection slave; @@ -1010,6 +1062,7 @@ eval CREATE TABLE federated.t1 ( INSERT INTO federated.t1 (code, fileguts, creation_date) VALUES ('ASDFWERQWETWETAWETA', '*()w*09*$()*#)(*09*^90*d)(*s()d8g)(s*ned)(*)(s*d)(*hn(d*)(*sbn)D((#$*(#*%%&#&^$#&#&#&#&^&#*&*#$*&^*(&#(&Q*&&(*!&!(*&*(#&*(%&#*###[[', '2003-03-03 03:03:03'); INSERT INTO federated.t1 (code, fileguts, creation_date) VALUES ('DEUEUEUEUEUEUEUEUEU', '*()w*09*$()*#)(*09*^90*d)(*s()d8g)(s*ned)(*)(s*d)(*hn(d*)(*sbn)D((#$*(#*%%&#&^$#&#&#&#&^&#*&*#$*&^*(&#(&Q*&&(*!&!(*&*(#&*(%&#*###[[', '2004-04-04 04:04:04'); INSERT INTO federated.t1 (code, fileguts, creation_date) VALUES ('DEUEUEUEUEUEUEUEUEU', 'jimbob', '2004-04-04 04:04:04'); +--sorted_result SELECT * FROM federated.t1; # test blob indexes SELECT * FROM federated.t1 WHERE fileguts = 'jimbob'; @@ -1030,6 +1083,7 @@ CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; INSERT INTO federated.t1 VALUES (0x00); INSERT INTO federated.t1 VALUES (0x0001); INSERT INTO federated.t1 VALUES (0x0100); +--sorted_result SELECT HEX(a) FROM federated.t1; # # simple tests for cyrillic, given to me by @@ -1044,20 +1098,20 @@ SELECT HEX(a) FROM federated.t1; # CREATE TABLE federated.t1 (a char(20)) charset=cp1251; # # # connection master; -# INSERT INTO federated.t1 values (_cp1251'--1'); -# INSERT INTO federated.t1 values (_cp1251'--2'); +# INSERT INTO federated.t1 values (_cp1251'À-ÁÂÃ-1'); +# INSERT INTO federated.t1 values (_cp1251'Á-ÂÃÄ-2'); # SELECT * FROM federated.t1; # SET names cp1251; -# INSERT INTO federated.t1 values ('--3'); -# INSERT INTO federated.t1 values ('-Ũ-4'); +# INSERT INTO federated.t1 values ('Â-ÃÄÅ-3'); +# INSERT INTO federated.t1 values ('Ã-ŨÆ-4'); # SELECT * FROM federated.t1; # SELECT hex(a) from federated.t1; # SELECT hex(a) from federated.t1 ORDER BY a desc; -# UPDATE federated.t1 SET a='--1' WHERE a='--1'; +# UPDATE federated.t1 SET a='À-ÁÂÃ-1íîâûé' WHERE a='À-ÁÂÃ-1'; # SELECT * FROM federated.t1; -# DELETE FROM federated.t1 WHERE a='-Ũ-4'; +# DELETE FROM federated.t1 WHERE a='Ã-ŨÆ-4'; # SELECT * FROM federated.t1; -# DELETE FROM federated.t1 WHERE a>'-'; +# DELETE FROM federated.t1 WHERE a>'Â-'; # SELECT * FROM federated.t1; # SET names default; # DROP TABLE IF EXISTS federated.t1; @@ -1108,6 +1162,7 @@ INSERT INTO federated.t1 (name, country_id, other) VALUES ('Monty', 4, 33333); INSERT INTO federated.t1 (name, country_id, other) VALUES ('Sanja', 5, 33333); #inner join +--sorted_result SELECT federated.t1.name AS name, federated.t1.country_id AS country_id, federated.t1.other AS other, federated.countries.country AS country FROM federated.t1, federated.countries WHERE @@ -1181,7 +1236,7 @@ INSERT INTO federated.alter_me (id, name) VALUES (2, 'David'); SELECT * FROM federated.alter_me; ---error 1031 +--error ER_ILLEGAL_HA ALTER TABLE federated.alter_me MODIFY COLUMN id int(16) NOT NULL; SELECT * FROM federated.alter_me; @@ -1452,15 +1507,15 @@ insert into federated.t2 values (13, 17), (19, 23); # Each of three statements should correctly set values for all three fields # insert insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11); -select * from federated.t1; +select * from federated.t1 order by a; delete from federated.t1; # insert ... select insert into federated.t1 (a, b) select * from federated.t2; -select * from federated.t1; +select * from federated.t1 order by a; delete from federated.t1; # load load data infile '../std_data_ln/loaddata5.dat' into table federated.t1 fields terminated by '' enclosed by '' ignore 1 lines (a, b); -select * from federated.t1; +select * from federated.t1 order by a; drop tables federated.t1, federated.t2; connection slave; @@ -1769,7 +1824,13 @@ DROP TABLE t1; connection master; DROP TABLE t1; - -source include/federated_cleanup.inc; +connection default; --echo End of 5.0 tests + +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; +connection slave; +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_SLAVE_CONCURRENT_INSERT; + +connection default; +source include/federated_cleanup.inc; -- cgit v1.2.1 From 2f082d9d427059a2a7cbcaf1d575cdd25c1376c2 Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Wed, 17 Sep 2008 08:34:00 +0200 Subject: Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE and SELECT INTO OUTFILE. It is now. mysql-test/r/loaddata.result: Show that SQL-mode NO_BACKSLASH_ESCAPES is heeded in INFILE/OUTFILE, and that dump/restore cycles work! mysql-test/t/loaddata.test: Show that SQL-mode NO_BACKSLASH_ESCAPES is heeded in INFILE/OUTFILE, and that dump/restore cycles work! sql/sql_class.cc: Add function to enquire whether ESCAPED BY was given. When doing SELECT...OUTFILE, use ESCAPED BY if specifically given; otherwise use sensible default value depending on SQL-mode features NO_BACKSLASH_ESCAPES. sql/sql_class.h: Add function to enquire whether ESCAPED BY was given. sql/sql_load.cc: When doing LOAD DATA INFILE, use ESCAPED BY if specifically given; otherwise use sensible default value depending on SQL-mode features NO_BACKSLASH_ESCAPES. --- mysql-test/t/loaddata.test | 181 ++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 180 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index e9da2bbadc5..38e392a56e7 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -318,4 +318,183 @@ DROP VIEW v3; ########################################################################### -# End of 5.0 tests + +# +# Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with +# LOAD DATA INFILE +# + +# - For each plain "SELECT id,...", the 1st pair ("before" SELECT...OUTFILE, +# LOAD...INFILE) and the 2nd pair of lines ("after") in the result should +# look the same, otherwise we broke the dumpe/restore cycle! +# +# - the \r is always { '\\', 'r' } in memory, but on-disk format changes +# +# - the \t is { '\t' } or { '\\', 't' } in memory depending on whether \ +# is magic (that is, NO_BACKSLASH_ESCAPES is not set) at INSERT-time. +# on-disk format varies. +# +# - while INFILE/OUTFILE behaviour changes according to NO_BACKSLASH_ESCAPES, +# we can override these defaults using ESCAPED BY '...' +# 1: NO_BACKSLASH_ESCAPES default, \ on-disk: \,t,x,\r +# 2: NO_BACKSLASH_ESCAPES override, \\ on-disk: \,\,t,x,\,\,r +# 3: !NO_BACKSLASH_ESCAPES default, \\ on-disk: tab,\,\,r +# 3: !NO_BACKSLASH_ESCAPES override, \ on-disk: tab,\,r + +--echo Bug#37114 + +SET SESSION character_set_client=latin1; +SET SESSION character_set_server=latin1; +SET SESSION character_set_connection=latin1; +SET @OLD_SQL_MODE=@@SESSION.SQL_MODE; + +# 0. test LOAD DATA INFILE first; if that works, all issues in +# SELECT INTO OUTFILE / LOAD DATA INFILE cycles below are +# arguably in the saving. + +--echo test LOAD DATA INFILE + +--let $file=$MYSQLTEST_VARDIR/tmp/bug37114.txt +--let $file2=$MYSQLTEST_VARDIR/tmp/bug37114_out.txt + +--write_file $file +1 \aa +EOF + +CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; + +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' ' +SELECT * FROM t1; + +# show we can write this with OUTFILE, forcing the parameters for now +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file2' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 +--diff_files $file $file2 +--remove_file $file2 + +# now show the OUTFILE defaults are correct with NO_BACKSLASH_ESCAPES +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file2' FIELDS TERMINATED BY ' ' FROM t1 +--diff_files $file $file2 +--remove_file $file2 + +INSERT INTO t1 (id, val1) VALUES (1, '\aa'); +SELECT * FROM t1; + +SET sql_mode=''; +INSERT INTO t1 (id, val1) VALUES (1, '\aa'); +SELECT * FROM t1; + +DROP TABLE t1; + +--remove_file $file + + + +--echo test SELECT INTO OUTFILE + +CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4)); +CREATE TABLE t2 LIKE t1; + +# 1. with NO_BACKSLASH_ESCAPES on + +SET sql_mode = ''; +INSERT INTO t1 (id, val1) VALUES (5, '\ttab'); +INSERT INTO t1 (id, val1) VALUES (4, '\\r'); +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; +INSERT INTO t1 (id, val1) VALUES (3, '\tx'); + +--echo 1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS TERMINATED BY ' ' + +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION + SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; + +TRUNCATE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + + + +--echo 1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS ESCAPED BY '\' TERMINATED BY ' ' + +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION + SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; + +TRUNCATE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + + + +# 2. with NO_BACKSLASH_ESCAPES off + +SET sql_mode = ''; + +--echo 2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS TERMINATED BY ' ' + +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION + SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; + +TRUNCATE t2; + +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + +SET sql_mode = ''; + + + +--echo 2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS ESCAPED BY '' TERMINATED BY ' ' + +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION + SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; + +TRUNCATE t2; + +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + +# clean up +set session sql_mode=@OLD_SQL_MODE; +DROP TABLE t1,t2; + + + +--echo End of 5.0 tests -- cgit v1.2.1 From e9cb71fc3a2f4e83720f1bab4f470e10ca878eb6 Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Thu, 18 Sep 2008 13:38:44 +0500 Subject: Bug#26020: User-Defined Variables are not consistent with columns data types The "SELECT @lastId, @lastId := Id FROM t" query returns different result sets depending on the type of the Id column (INT or BIGINT). Note: this fix doesn't cover the case when a select query references an user variable and stored function that updates a value of that variable, in this case a result is indeterminate. The server uses incorrect assumption about a constantness of an user variable value as a select list item: The server caches a last query number where that variable was changed and compares this number with a current query number. If these numbers are different, the server guesses, that the variable is not updating in the current query, so a respective select list item is a constant. However, in some common cases the server updates cached query number too late. The server has been modified to memorize user variable assignments during the parse phase to take them into account on the next (query preparation) phase independently of the order of user variable references/assignments in a select item list. mysql-test/r/user_var.result: Added test case for bug #26020. mysql-test/t/user_var.test: Added test case for bug #26020. sql/item_func.cc: An update of entry and update_query_id variables has been moved from Item_func_set_user_var::fix_fields() to a separate method, Item_func_set_user_var::set_entry(). sql/item_func.h: 1. The Item_func_set_user_var::set_entry() method has been added to update Item_func_set_user_var::entry. 2. The Item_func_set_user_var::entry_thd field has beend added to update Item_func_set_user_var::entry only when needed. sql/sql_base.cc: Fix: setup_fiedls() calls Item_func_set_user_var::set_entry() for all items from the thd->lex->set_var_list before the first call of ::fix_fields(). sql/sql_lex.cc: The lex_start function has been modified to reset the st_lex::set_var_list list. sql/sql_lex.h: New st_lex::set_var_list field has been added to memorize all user variable assignments in the current select query. sql/sql_yacc.yy: The variable_aux rule has been modified to memorize in-query user variable assignments in the st_lex::set_var_list list. --- mysql-test/t/user_var.test | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index f2699ab03d3..5d916e410e3 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -263,4 +263,26 @@ from t1 order by score desc; --enable_result_log drop table t1; +# +# Bug#26020: User-Defined Variables are not consistent with columns data types +# + +create table t1(b bigint); +insert into t1 (b) values (10), (30), (10); +set @var := 0; +select if(b=@var, 999, b) , @var := b from t1 order by b; +drop table t1; + +create temporary table t1 (id int); +insert into t1 values (2), (3), (3), (4); +set @lastid=-1; +select @lastid != id, @lastid, @lastid := id from t1; +drop table t1; + +create temporary table t1 (id bigint); +insert into t1 values (2), (3), (3), (4); +set @lastid=-1; +select @lastid != id, @lastid, @lastid := id from t1; +drop table t1; + --echo End of 5.1 tests -- cgit v1.2.1 From 654db75dfbedf64435fb7fdb00500396e0ee92f1 Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Thu, 18 Sep 2008 11:24:50 +0200 Subject: Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE tweaked test to make embedded server happy --- mysql-test/t/loaddata.test | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 38e392a56e7..cd22b700430 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -357,16 +357,17 @@ SET @OLD_SQL_MODE=@@SESSION.SQL_MODE; --let $file=$MYSQLTEST_VARDIR/tmp/bug37114.txt --let $file2=$MYSQLTEST_VARDIR/tmp/bug37114_out.txt ---write_file $file -1 \aa -EOF +SET sql_mode = ''; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT '1 \\\\aa\n' INTO DUMPFILE '$file' CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; SET sql_mode = 'NO_BACKSLASH_ESCAPES'; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---eval LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' ' +--eval LOAD DATA INFILE '$file' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' ' SELECT * FROM t1; # show we can write this with OUTFILE, forcing the parameters for now -- cgit v1.2.1 From 71fe19017206da71c053cbe794a382eb94f30741 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Thu, 18 Sep 2008 15:55:36 +0300 Subject: Bug #39353: Multiple conditions on timestamp column crashes server The fix for bug 31887 was incomplete : it assumes that all the field types returned by the IS_NUM macro are descendants of Item_num and tries to zero-fill the values before doing constant substitution with such fields when they are compared to constant string values. The only exception to this is Field_timestamp : it's in the IS_NUM macro, but is not a descendant of Field_num. Fixed by excluding timestamp fields (Field_timestamp) when zero-filling when converting the constant to compare with to a string. Note that this will not exclude the timestamp columns from const propagation. mysql-test/r/compare.result: Bug #39353: test case mysql-test/t/compare.test: Bug #39353: test case sql/item.cc: Bug #39353: don't zero-fill timestamp fields when const propagating to a string : they'll be converted to a string in a date/time format and not as an integer. --- mysql-test/t/compare.test | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/compare.test b/mysql-test/t/compare.test index 8863ed825c2..103244eb2f7 100644 --- a/mysql-test/t/compare.test +++ b/mysql-test/t/compare.test @@ -76,4 +76,13 @@ FROM t2 ORDER BY a; DROP TABLE t1,t2; +# +# Bug #39353: Multiple conditions on timestamp column crashes server +# + +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (NOW()),(NOW()),(NOW()); +SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00'; +DROP TABLE t1; + --echo End of 5.0 tests -- cgit v1.2.1 From 3e1d88d188b13a9b7ad2e40b5fee264d44c618e2 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Thu, 18 Sep 2008 22:49:34 +0300 Subject: Bug#30573: Ordered range scan over partitioned tables returns some rows twice and Bug#33555: Group By Query does not correctly aggregate partitions Backport of bug-33257 which is the same bug. read_range_*() calls was not passed to the partition handlers, but was translated to index_read/next family calls. Resulting in duplicates rows and wrong aggregations. mysql-test/r/partition_range.result: Bug#30573: Ordered range scan over partitioned tables returns some rows twice Updated result file mysql-test/t/partition_range.test: Bug#30573: Ordered range scan over partitioned tables returns some rows twice Re-enabled the test sql/ha_partition.cc: Bug#30573: Ordered range scan over partitioned tables returns some rows twice backport of bug-33257, correct handling of read_range_* calls, without converting them to index_read/next calls sql/ha_partition.h: Bug#30573: Ordered range scan over partitioned tables returns some rows twice backport of bug-33257, correct handling of read_range_* calls, without converting them to index_read/next calls --- mysql-test/t/partition_range.test | 40 +++++++++++++++++++-------------------- 1 file changed, 20 insertions(+), 20 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test index bc4231d1d71..c02d9049f2e 100644 --- a/mysql-test/t/partition_range.test +++ b/mysql-test/t/partition_range.test @@ -807,24 +807,24 @@ DROP TABLE t1; # # BUG#30573: get wrong result with "group by" on PARTITIONed table # -#create table t1 (a int); -#insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -#CREATE TABLE t2 ( -# defid int(10) unsigned NOT NULL, -# day int(10) unsigned NOT NULL, -# count int(10) unsigned NOT NULL, -# filler char(200), -# KEY (defid,day) -#) -#PARTITION BY RANGE (day) ( -# PARTITION p7 VALUES LESS THAN (20070401) , -# PARTITION p8 VALUES LESS THAN (20070501)); - -#insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; -#insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; -#insert into t2 values(52, 20070321, 123, 'filler') ; -#insert into t2 values(52, 20070322, 456, 'filler') ; - -#select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; -#drop table t1, t2; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( + defid int(10) unsigned NOT NULL, + day int(10) unsigned NOT NULL, + count int(10) unsigned NOT NULL, + filler char(200), + KEY (defid,day) +) +PARTITION BY RANGE (day) ( + PARTITION p7 VALUES LESS THAN (20070401) , + PARTITION p8 VALUES LESS THAN (20070501)); + +insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; +insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; +insert into t2 values(52, 20070321, 123, 'filler') ; +insert into t2 values(52, 20070322, 456, 'filler') ; + +select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; +drop table t1, t2; -- cgit v1.2.1 From 598a7542959a7bffc40ca6b031a8dec95c79beb8 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 19 Sep 2008 16:24:32 +0300 Subject: fixed a problem with the push of bug #31434 --- mysql-test/t/mysqldump-max.test | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/mysqldump-max.test b/mysql-test/t/mysqldump-max.test index 1876d759372..1e8b9647503 100644 --- a/mysql-test/t/mysqldump-max.test +++ b/mysql-test/t/mysqldump-max.test @@ -1114,9 +1114,9 @@ CREATE VIEW v1 AS SELECT * FROM t1; INSERT INTO t1 VALUES(); SELECT COUNT(*) FROM v1; ---exec $MYSQL_DUMP --allow-keywords --single-transaction --quick --verbose test --result-file $MYSQL_TEST_DIR/var/tmp/bug31434.sql ---exec $MYSQL test < $MYSQL_TEST_DIR/var/tmp/bug31434.sql ---remove_file $MYSQL_TEST_DIR/var/tmp/bug31434.sql +--exec $MYSQL_DUMP --allow-keywords --single-transaction --quick --verbose test --result-file $MYSQLTEST_VARDIR/tmp/bug31434.sql +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug31434.sql +--remove_file $MYSQLTEST_VARDIR/tmp/bug31434.sql SELECT COUNT(*) FROM v1; -- cgit v1.2.1 From 404e4b802cc15197028f29a38f522e2711366c02 Mon Sep 17 00:00:00 2001 From: Patrick Crews Date: Sat, 20 Sep 2008 02:21:28 -0400 Subject: Bug#38311 Some tests use 'rm' which is not portable Substituted use of MTR's remove_file function in the tests Started with 5.0 tree and will clean up any offenders discovered during upmerge. --- mysql-test/t/binlog_killed_simulate.test | 4 ++-- mysql-test/t/binlog_start_comment.test | 2 +- mysql-test/t/ctype_big5.test | 3 ++- mysql-test/t/distinct.test | 4 ++-- mysql-test/t/ndb_autodiscover.test | 32 ++++++++++++++++---------------- mysql-test/t/ndb_loaddatalocal.test | 8 ++++---- mysql-test/t/ndb_restore_print.test | 20 ++++++++++++-------- mysql-test/t/outfile.test | 2 +- mysql-test/t/rpl_EE_error.test | 4 +++- mysql-test/t/rpl_loaddatalocal.test | 4 ++-- mysql-test/t/symlink.test | 3 ++- 11 files changed, 47 insertions(+), 39 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/binlog_killed_simulate.test b/mysql-test/t/binlog_killed_simulate.test index 670cd756803..3f50ac350d5 100644 --- a/mysql-test/t/binlog_killed_simulate.test +++ b/mysql-test/t/binlog_killed_simulate.test @@ -30,7 +30,7 @@ let $error_code= `select @a like "%#%error_code=0%" /* must return 1 */`; eval select $error_code /* must return 1 as query completed before got killed*/; # cleanup for the sub-case -system rm $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog; +remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog; # @@ -58,7 +58,7 @@ let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`; eval select $error_code /* must return 0 to mean the killed query is in */; # cleanup for the sub-case -system rm $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog; +remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog; drop table t1,t2; diff --git a/mysql-test/t/binlog_start_comment.test b/mysql-test/t/binlog_start_comment.test index 84889167cd2..2834c6d27e3 100644 --- a/mysql-test/t/binlog_start_comment.test +++ b/mysql-test/t/binlog_start_comment.test @@ -21,4 +21,4 @@ select * from t2; # clean up drop table t1,t2; -#--system rm $MYSQLTEST_VARDIR/tmp/binlog_start_comment.binlog +#--remove_file $MYSQLTEST_VARDIR/tmp/binlog_start_comment.binlog diff --git a/mysql-test/t/ctype_big5.test b/mysql-test/t/ctype_big5.test index 0ed21091110..5a8a13f2bad 100644 --- a/mysql-test/t/ctype_big5.test +++ b/mysql-test/t/ctype_big5.test @@ -79,7 +79,8 @@ delete from t1; --eval select hex(load_file('$MYSQLTEST_VARDIR/master-data/test/t1.txt')); load data infile 't1.txt' into table t1; select hex(a) from t1; ---exec rm $MYSQLTEST_VARDIR/master-data/test/t1.txt +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.txt + drop table t1; --echo End of 5.0 tests diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 7310f98cd16..19147a7d9cf 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -492,12 +492,12 @@ DROP TABLE t1; #SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE #'../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE'; #LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2; -#--exec rm $MYSQL_TEST_DIR/var/tmp/data1.tmp +#--remove_file $MYSQL_TEST_DIR/var/tmp/data1.tmp # #SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE #'../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE'; #LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2; -#--exec rm $MYSQL_TEST_DIR/var/tmp/data2.tmp +#--remove_file $MYSQL_TEST_DIR/var/tmp/data2.tmp # #SELECT @v19, @v20; #SELECT * FROM t2; diff --git a/mysql-test/t/ndb_autodiscover.test b/mysql-test/t/ndb_autodiscover.test index 049dcd9755e..11bb0b1fb7f 100644 --- a/mysql-test/t/ndb_autodiscover.test +++ b/mysql-test/t/ndb_autodiscover.test @@ -24,7 +24,7 @@ create table t1( insert into t1 values(1, "Autodiscover"); flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t1.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm ; select * from t1; show status like 'handler_discover%'; @@ -33,13 +33,13 @@ show status like 'handler_discover%'; # flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t1.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm ; insert into t1 values (2, "Auto 2"); show status like 'handler_discover%'; insert into t1 values (3, "Discover 3"); show status like 'handler_discover%'; flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t1.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm ; select * from t1 order by id; show status like 'handler_discover%'; @@ -48,7 +48,7 @@ show status like 'handler_discover%'; # flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t1.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm ; update t1 set name="Autodiscover" where id = 2; show status like 'handler_discover%'; select * from t1 order by id; @@ -59,7 +59,7 @@ show status like 'handler_discover%'; # flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t1.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm ; delete from t1 where id = 3; select * from t1 order by id; show status like 'handler_discover%'; @@ -111,7 +111,7 @@ show status like 'handler_discover%'; flush tables; # Remove the frm file from disk -system rm $MYSQLTEST_VARDIR/master-data/test/t3.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t3.frm ; --error 1050 create table t3( @@ -168,14 +168,14 @@ show status like 'handler_discover%'; # Remove the frm file from disk flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t7.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm ; show tables from test; show status like 'handler_discover%'; # Remove the frm file from disk again flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t7.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm ; --replace_column 7 # 8 # 9 # 12 # 13 # 15 # 18 # show table status; @@ -290,8 +290,8 @@ insert into t9 values (9); system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t3 >> $NDB_TOOLS_OUTPUT ; system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t5 >> $NDB_TOOLS_OUTPUT ; # Remove t6, t7 from disk -system rm $MYSQLTEST_VARDIR/master-data/test/t6.frm > /dev/null ; -system rm $MYSQLTEST_VARDIR/master-data/test/t7.frm > /dev/null ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t6.frm > /dev/null ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm > /dev/null ; SHOW TABLES; @@ -332,8 +332,8 @@ insert into t9 values (9); system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t3 > /dev/null ; system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t5 > /dev/null ; # Remove t6, t7 from disk -system rm $MYSQLTEST_VARDIR/master-data/test/t6.frm > /dev/null ; -system rm $MYSQLTEST_VARDIR/master-data/test/t7.frm > /dev/null ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t6.frm > /dev/null ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm > /dev/null ; SHOW TABLES LIKE 't6'; @@ -375,9 +375,9 @@ insert into t3 values (3, "ndb table 3"); insert into t4 values (4); # Remove t1, t2, t3 from disk -system rm $MYSQLTEST_VARDIR/master-data/test/t1.frm > /dev/null ; -system rm $MYSQLTEST_VARDIR/master-data/test/t2.frm > /dev/null ; -system rm $MYSQLTEST_VARDIR/master-data/test/t3.frm > /dev/null ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm > /dev/null ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t2.frm > /dev/null ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t3.frm > /dev/null ; flush tables; # Select from the table which only exists in NDB. @@ -531,7 +531,7 @@ CREATE TABLE t9 ( insert t9 values(1, 2), (2,3), (3, 4), (4, 5); #Don't drop the table, instead remove the frm file -system rm $MYSQLTEST_VARDIR/master-data/test/t9.frm ; +remove_file $MYSQLTEST_VARDIR/master-data/test/t9.frm ; # Now leave test case, when ndb_autodiscover2 will run, this # MySQL Server will have been restarted because it has a diff --git a/mysql-test/t/ndb_loaddatalocal.test b/mysql-test/t/ndb_loaddatalocal.test index 47054ecfbf5..f8930255a2a 100644 --- a/mysql-test/t/ndb_loaddatalocal.test +++ b/mysql-test/t/ndb_loaddatalocal.test @@ -25,7 +25,7 @@ create table t1(a int) engine=ndb; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval load data local infile '$MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile' into table t1; select count(*) from t1; -system rm $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile ; +remove_file $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile ; drop table t1; create table t1(a int) engine=myisam; @@ -37,7 +37,7 @@ drop table t1; create table t1(a int primary key) engine=ndb; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval load data local infile '$MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile' into table t1; -system rm $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile; +remove_file $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile; select * from t1 order by a; drop table t1; @@ -50,7 +50,7 @@ drop table t1; create table t1(a int primary key) engine=ndb; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval load data local infile '$MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile' into table t1; -system rm $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile; +remove_file $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile; select * from t1 order by a; drop table t1; @@ -63,7 +63,7 @@ drop table t1; create table t1(a int primary key) engine=ndb; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval load data local infile '$MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile' into table t1; -system rm $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile; +remove_file $MYSQLTEST_VARDIR/master-data/ndb_loaddatalocal.select_outfile; select * from t1 order by a; drop table t1; diff --git a/mysql-test/t/ndb_restore_print.test b/mysql-test/t/ndb_restore_print.test index 6dbbfdf5933..9a880f8968c 100644 --- a/mysql-test/t/ndb_restore_print.test +++ b/mysql-test/t/ndb_restore_print.test @@ -134,10 +134,14 @@ insert into t4 values (1,31),(2,32),(3,33),(4,34),(5,35); --let ndb_restore_filter=test t1 --source include/ndb_backup_print.inc ---exec rm -f $MYSQLTEST_VARDIR/tmp/t1.txt ---exec rm -f $MYSQLTEST_VARDIR/tmp/t2.txt ---exec rm -f $MYSQLTEST_VARDIR/tmp/t3.txt ---exec rm -f $MYSQLTEST_VARDIR/tmp/t4.txt +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/t1.txt +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/t2.txt +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/t3.txt +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/t4.txt --let ndb_restore_opts=--verbose=0 --print_data --hex --tab $MYSQLTEST_VARDIR/tmp --append --let ndb_restore_filter=test @@ -156,10 +160,10 @@ insert into t4 values (1,31),(2,32),(3,33),(4,34),(5,35); --source include/show_msg.inc --exec sort $MYSQLTEST_VARDIR/tmp/t4.txt ---exec rm -f $MYSQLTEST_VARDIR/tmp/t1.txt ---exec rm -f $MYSQLTEST_VARDIR/tmp/t2.txt ---exec rm -f $MYSQLTEST_VARDIR/tmp/t3.txt ---exec rm -f $MYSQLTEST_VARDIR/tmp/t4.txt +--remove_file $MYSQLTEST_VARDIR/tmp/t1.txt +--remove_file $MYSQLTEST_VARDIR/tmp/t2.txt +--remove_file $MYSQLTEST_VARDIR/tmp/t3.txt +--remove_file $MYSQLTEST_VARDIR/tmp/t4.txt # now test some other datatypes drop table t1; diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test index 2b80b0b9d93..941bbe228d4 100644 --- a/mysql-test/t/outfile.test +++ b/mysql-test/t/outfile.test @@ -125,7 +125,7 @@ from information_schema.schemata where schema_name like 'mysqltest'; connection default; ---exec rm $MYSQLTEST_VARDIR/tmp/outfile-test.4 +--remove_file $MYSQLTEST_VARDIR/tmp/outfile-test.4 use test; revoke all privileges on *.* from user_1@localhost; drop user user_1@localhost; diff --git a/mysql-test/t/rpl_EE_error.test b/mysql-test/t/rpl_EE_error.test index 640a2b1a88c..75665d0ceea 100644 --- a/mysql-test/t/rpl_EE_error.test +++ b/mysql-test/t/rpl_EE_error.test @@ -6,9 +6,11 @@ source include/master-slave.inc; + create table t1 (a int) engine=myisam; flush tables; -system rm $MYSQLTEST_VARDIR/master-data/test/t1.MYI ; +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.MYI + drop table if exists t1; save_master_pos; connection slave; diff --git a/mysql-test/t/rpl_loaddatalocal.test b/mysql-test/t/rpl_loaddatalocal.test index af4fd0106bd..d3149bea427 100644 --- a/mysql-test/t/rpl_loaddatalocal.test +++ b/mysql-test/t/rpl_loaddatalocal.test @@ -25,7 +25,7 @@ eval select * into outfile '$MYSQLTEST_VARDIR/master-data/rpl_loaddatalocal.sele truncate table t1; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval load data local infile '$MYSQLTEST_VARDIR/master-data/rpl_loaddatalocal.select_outfile' into table t1; -system rm $MYSQLTEST_VARDIR/master-data/rpl_loaddatalocal.select_outfile ; +--remove_file $MYSQLTEST_VARDIR/master-data/rpl_loaddatalocal.select_outfile save_master_pos; connection slave; sync_with_master; @@ -52,7 +52,7 @@ drop table t1; create table t1(a int primary key); --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval load data local infile '$MYSQLTEST_VARDIR/master-data/rpl_loaddatalocal.select_outfile' into table t1; -system rm $MYSQLTEST_VARDIR/master-data/rpl_loaddatalocal.select_outfile ; +remove_file $MYSQLTEST_VARDIR/master-data/rpl_loaddatalocal.select_outfile ; select * from t1; save_master_pos; connection slave; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 9478abe1224..8ce1bca1ec1 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -185,7 +185,8 @@ drop table t1; # # Protect ourselves from data left in tmp/ by a previos possibly failed # test ---system rm -f $MYSQLTEST_VARDIR/tmp/t1.* +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/t1.* --disable_query_log eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'"; --enable_query_log -- cgit v1.2.1 From f0352e346a1a7dbf056ac87239ed9b407d70886b Mon Sep 17 00:00:00 2001 From: Kristofer Pettersson Date: Sat, 20 Sep 2008 10:51:03 +0200 Subject: Bug#38469 invalid memory read and/or crash with utf8 text field, stored procedure, uservar A stored procedure involving substrings could crash the server on certain platforms because of invalid memory reads. During storing the new blob-field value, the cached value's address range overlapped that of the new field value. This caused problems when the cached value storage was reallocated to provide access for a new characater set representation. The patch checks the address ranges, and if they overlap, the new field value is copied to a new storage before it is converted to the new character set. mysql-test/r/sp.result: Added result set mysql-test/t/sp.test: Added test case sql/field.cc: The source and destination address ranges of a character conversion must not overlap or the 'from' address will be invalidated as the temporary value- object is re-allocated to fit the new character set. sql/field.h: Added comments --- mysql-test/t/sp.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 87ab1d2f0d9..21ca2528e4f 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -7818,6 +7818,24 @@ drop function f1; drop view v1; drop table t1; +# +# Bug#38469 invalid memory read and/or crash with utf8 text field, stored procedure, uservar +# +delimiter $; +--disable_warnings +drop procedure if exists `p2` $ +--enable_warnings +create procedure `p2`(in `a` text charset utf8) +begin + declare `pos` int default 1; + declare `str` text charset utf8; + set `str` := `a`; + select substr(`str`, `pos`+ 1 ) into `str`; +end $ +delimiter ;$ +call `p2`('s s s s s s'); +drop procedure `p2`; + --echo # ------------------------------------------------------------------ --echo # -- End of 5.0 tests --echo # ------------------------------------------------------------------ -- cgit v1.2.1 From 392ff10eac0759dff8822b13afbf493bc0b01925 Mon Sep 17 00:00:00 2001 From: Chad MILLER Date: Wed, 24 Sep 2008 08:59:56 -0400 Subject: Bug#35754: mysql_install_db does not work if no hostname is set Machines with hostname set to "localhost" cause uniqueness errors in the SQL bootstrap data. Now, insert zero lines for cases where the (lowercased) hostname is the same as an already-inserted 'localhost' name. Also, fix a few tests that expect certain local accounts to have a certain host name. --- mysql-test/t/join.test | 10 ++++++---- mysql-test/t/rpl_grant.test | 8 ++++---- 2 files changed, 10 insertions(+), 8 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 5b599c3dad7..55048a86f14 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -546,10 +546,12 @@ select * from v1a join v1b on t1.b = t2.b; # # Bug #17523 natural join and information_schema # -# We mask out the Privileges column because it differs with embedded server ---replace_column 31 # -select * from information_schema.statistics join information_schema.columns - using(table_name,column_name) where table_name='user'; +# Omit columns.PRIVILIGES as it may vary with embedded server. +# Omit columns.ORDINAL_POSITION as it may vary with hostname='localhost'. +select + statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.CARDINALITY, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, + columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT + from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; drop table t1; drop table t2; diff --git a/mysql-test/t/rpl_grant.test b/mysql-test/t/rpl_grant.test index 71e36342584..dbb5c624f2b 100644 --- a/mysql-test/t/rpl_grant.test +++ b/mysql-test/t/rpl_grant.test @@ -10,11 +10,11 @@ CREATE USER dummy@localhost; CREATE USER dummy1@localhost, dummy2@localhost; SELECT user, host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); sync_slave_with_master; --echo **** On Slave **** SELECT user,host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); --echo **** On Master **** connection master; @@ -31,11 +31,11 @@ DROP USER nonexisting@localhost, dummy@localhost; DROP USER dummy1@localhost, dummy2@localhost; SELECT user, host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); sync_slave_with_master; --echo **** On Slave **** SELECT user,host FROM mysql.user WHERE user != 'root'; # root host non-determ -SELECT COUNT(*) FROM mysql.user; +SELECT COUNT(*) FROM mysql.user WHERE user != 'root' or (host != 'localhost' and host != @hostname); --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # -- cgit v1.2.1 From 0406d409ea8550073465c1744425c599d0107553 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Mon, 29 Sep 2008 10:53:40 -0300 Subject: Bug#34306: Can't make copy of log tables when server binary log is enabled The problem is that when statement-based replication was enabled, statements such as INSERT INTO .. SELECT FROM .. and CREATE TABLE .. SELECT FROM need to grab a read lock on the source table that does not permit concurrent inserts, which would in turn be denied if the source table is a log table because log tables can't be locked exclusively. The solution is to not take such a lock when the source table is a log table as it is unsafe to replicate log tables under statement based replication. Furthermore, the read lock that does not permits concurrent inserts is now only taken if statement-based replication is enabled and if the source table is not a log table. include/thr_lock.h: Introduce yet another lock type that my get upgraded depending on the binary log format. This is not a optimal solution but can be easily improved later. mysql-test/r/log_tables.result: Add test case result for Bug#34306 mysql-test/suite/binlog/r/binlog_stm_row.result: Add test case result for Bug#34306 mysql-test/suite/binlog/t/binlog_stm_row.test: Add test case for Bug#34306 mysql-test/t/log_tables.test: Add test case for Bug#34306 sql/lock.cc: Assert that TL_READ_DEFAULT is not a real lock type. sql/mysql_priv.h: Export new function. sql/mysqld.cc: Remove using_update_log. sql/sql_base.cc: Introduce function that returns the appropriate read lock type depending on how the statement is going to be replicated. It will only take a TL_READ_NO_INSERT log if the binary is enabled and the binary log format is statement-based and the table is not a log table. sql/sql_parse.cc: Remove using_update_log. sql/sql_update.cc: Use new function to choose read lock type. sql/sql_yacc.yy: The lock type is now decided at open_tables time. This old behavior was actually misleading as the binary log format can be dynamically switched and this would not change for statements that have already been parsed when the binary log format is changed (ie: prepared statements). --- mysql-test/t/log_tables.test | 46 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 46 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 3047d16d3b6..34086336fa8 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -936,6 +936,52 @@ select command_type, argument from mysql.general_log where thread_id = @thread_i deallocate prepare long_query; set global general_log = @old_general_log_state; +# +# Bug#34306: Can't make copy of log tables when server binary log is enabled +# + +--disable_warnings +DROP TABLE IF EXISTS log_count; +DROP TABLE IF EXISTS slow_log_copy; +DROP TABLE IF EXISTS general_log_copy; +--enable_warnings + +CREATE TABLE log_count (count BIGINT(21)); + +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; + +SET GLOBAL general_log = ON; +SET GLOBAL slow_query_log = ON; + +CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log)); +DROP TABLE slow_log_copy; + +CREATE TABLE general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log)); +DROP TABLE general_log_copy; + +SET GLOBAL general_log = OFF; +SET GLOBAL slow_query_log = OFF; + +CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log)); +DROP TABLE slow_log_copy; + +CREATE TABLE general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log)); +DROP TABLE general_log_copy; + +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; + +DROP TABLE log_count; + # # Bug #31700: thd->examined_row_count not incremented for 'const' type queries # -- cgit v1.2.1 From ce64a16b75746848c8d5a89cfb726a38691c9d6f Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Mon, 29 Sep 2008 19:11:34 +0500 Subject: Bug#37949 Crash if argument to SP is a subquery that returns more than one row JOIN for the subselect wasn't cleaned if we came upon an error during sub_select() execution. That leads to the assertion failure in close_thread_tables() part of the 6.0 code backported per-file comments: mysql-test/r/sp-error.result Bug#37949 Crash if argument to SP is a subquery that returns more than one row test result mysql-test/t/sp-error.test Bug#37949 Crash if argument to SP is a subquery that returns more than one row test case sql/sp_head.cc Bug#37949 Crash if argument to SP is a subquery that returns more than one row lex->unit.cleanup() call added if not substatement --- mysql-test/t/sp-error.test | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index c9b2b1dbd0e..c839b1e4374 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -2173,6 +2173,14 @@ begin end loop; end| +CREATE TABLE t1 (a INT)| +INSERT INTO t1 VALUES (1),(2)| +CREATE PROCEDURE p1(a INT) BEGIN END| +--error ER_SUBQUERY_NO_1_ROW +CALL p1((SELECT * FROM t1))| +DROP PROCEDURE IF EXISTS p1| +DROP TABLE t1| + delimiter ;| # -- cgit v1.2.1 From 006b940a27206ce530ae68dc7b5915b86720086e Mon Sep 17 00:00:00 2001 From: Patrick Crews Date: Tue, 30 Sep 2008 20:54:06 -0400 Subject: Bug#38311 Some tests use 'rm' which is not portable Repush of change to fix tests on Pushbuild. --- mysql-test/t/distinct.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 0b09502e342..ff232a61621 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -490,6 +490,7 @@ default NULL); SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE '../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE'; LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2; +--error 0,1 --remove_file $MYSQL_TEST_DIR/var/tmp/data1.tmp SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE -- cgit v1.2.1 From ab5b7ceb971896ee73fa6187e894cb6ef2a36595 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Wed, 1 Oct 2008 12:45:02 +0300 Subject: fixed a wrong directory in distinct.test --- mysql-test/t/distinct.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index ff232a61621..2cbd34c0d2c 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -491,12 +491,12 @@ SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE '../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE'; LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2; --error 0,1 ---remove_file $MYSQL_TEST_DIR/var/tmp/data1.tmp +--remove_file $MYSQLTEST_VARDIR/tmp/data1.tmp SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE '../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE'; LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2; ---remove_file $MYSQL_TEST_DIR/var/tmp/data2.tmp +--remove_file $MYSQLTEST_VARDIR/tmp/data2.tmp SELECT @v19, @v20; SELECT * FROM t2; -- cgit v1.2.1 From d3e317d16befe9e6d7ade3e0527dca10609577ad Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Wed, 1 Oct 2008 14:48:47 +0500 Subject: Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. Problem: we replace all references to local variables in stored procedures with NAME_CONST(name, value) logging to the binary log. However, if the value's collation differs we might get an 'illegal mix of collation' error as we don't pass the collation to the function. Fix: pass the value's collation to NAME_CONST(). Note: actually we should pass to NAME_CONST() the value's derivation as well. It's impossible without the parser modifying. Now we always set the derivation to DERIVATION_IMPLICIT, the same as local variables have. mysql-test/r/binlog.result: Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - test result. mysql-test/r/ctype_cp932_binlog.result: Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - results adjusted. mysql-test/r/rpl_sp.result: Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - results adjusted. mysql-test/t/binlog.test: Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - test case. sql/item.cc: Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - allow NAME_CONST() to get _charset'foo' COLLATE 'bar' strings (see Item_func_set_collation). sql/sp_head.cc: Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - pass the value's collation to NAME_CONST(). --- mysql-test/t/binlog.test | 38 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/binlog.test b/mysql-test/t/binlog.test index b35c81b3b18..b9893e02e14 100644 --- a/mysql-test/t/binlog.test +++ b/mysql-test/t/binlog.test @@ -123,4 +123,42 @@ drop table t1; --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/ show binlog events from 0; + +# +# Bug #39182: Binary log producing incompatible character set query from +# stored procedure. +# +CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +USE bug39182; +CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci) + DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +DELIMITER //; + +CREATE PROCEDURE p1() +BEGIN + DECLARE s1 VARCHAR(255); + SET s1= "test"; + CREATE TEMPORARY TABLE tmp1 + SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%"); + SELECT + COLLATION(NAME_CONST('s1', _utf8'test')) c1, + COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2, + COLLATION(s1) c3, + COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1, + COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2, + COERCIBILITY(s1) d3; + DROP TEMPORARY TABLE tmp1; +END// + +DELIMITER ;// + +CALL p1(); +SHOW BINLOG EVENTS FROM 1285; + +DROP PROCEDURE p1; +DROP TABLE t1; +DROP DATABASE bug39182; +USE test; + --echo End of 5.0 tests -- cgit v1.2.1 From 652565d3622d2c6a81313aa6faf5e44fd2c9d57b Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Wed, 1 Oct 2008 15:53:11 +0300 Subject: fixed a failure in symlink.test caused by replacing rm with remove_file --- mysql-test/t/symlink.test | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 4ea41046c2f..af1867b5b66 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -186,7 +186,11 @@ drop table t1; # Protect ourselves from data left in tmp/ by a previos possibly failed # test --error 0,1 ---remove_file $MYSQLTEST_VARDIR/tmp/t1.* +--remove_file $MYSQLTEST_VARDIR/tmp/t1.frm +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/t1.MYD +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/t1.MYI --disable_query_log eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'"; --enable_query_log -- cgit v1.2.1 From eb3c08069db60d61f41dacb10fd6b73635fec236 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Wed, 1 Oct 2008 18:50:55 +0300 Subject: Bug#37943: Reproducible mysqld crash/sigsegv in sel_trees_can_be_ored When analyzing the possible index use cases the server was re-using an internal structure. This is wrong, as this internal structure gets updated during the analysis. Fixed by making a copy of the internal structure for every place it needs to be used. Also stopped the generation of empty SEL_TREE structures that unnecessary complicate the analysis. mysql-test/r/index_merge.result: Bug#37943: test case mysql-test/t/index_merge.test: Bug#37943: test case sql/opt_range.cc: Bug#37943: - Make copy constructors for SEL_TREE and sub-structures and use them when OR-ing trees. - don't generate empty SEL_TREEs. Return NULL instead. --- mysql-test/t/index_merge.test | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/index_merge.test b/mysql-test/t/index_merge.test index 8c19ab4d7d6..970b9a87454 100644 --- a/mysql-test/t/index_merge.test +++ b/mysql-test/t/index_merge.test @@ -463,3 +463,18 @@ select * from t2 where a=4 or b=4; drop table t1, t2; +# +# Bug #37943: Reproducible mysqld crash/sigsegv in sel_trees_can_be_ored +# + +CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'), + KEY b(b), KEY a(a)); +INSERT INTO t1 VALUES ('y',''), ('z',''); + +#should not crash +SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR + (a='pure-S') OR (a='DE80337a') OR (a='DE80799'); + +DROP TABLE t1; + +--echo End of 5.0 tests -- cgit v1.2.1 From 7e60f71001595df62b92a089869dd67fcc15a1ee Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Thu, 2 Oct 2008 14:37:07 +0500 Subject: Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS The problem: I_S views table does not check the presence of SHOW_VIEW_ACL|SELECT_ACL privileges for a view. It leads to discrepancy between SHOW CREATE VIEW and I_S.VIEWS. The fix: added appropriate check. mysql-test/r/information_schema_db.result: test result mysql-test/t/information_schema_db.test: test case sql/sql_show.cc: The problem: I_S views table does not check the presence of SHOW_VIEW_ACL|SELECT_ACL privileges for a view. It leads to discrepancy between SHOW CREATE VIEW and I_S.VIEWS. The fix: added appropriate check. --- mysql-test/t/information_schema_db.test | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 666f331c7b9..6353e94fd51 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -82,6 +82,7 @@ drop function func2; drop database `inf%`; drop procedure mbase.p1; drop database mbase; +disconnect user1; # # Bug#18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views @@ -210,3 +211,32 @@ drop view testdb_1.v1, v2, testdb_1.v3, v4; drop database testdb_1; drop user testdb_1@localhost; drop user testdb_2@localhost; + +# +# Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS +# +create database testdb_1; +create table testdb_1.t1 (a int); +create view testdb_1.v1 as select * from testdb_1.t1; + +grant show view on testdb_1.* to mysqltest_1@localhost; +grant select on testdb_1.v1 to mysqltest_1@localhost; + +connect (user1,localhost,mysqltest_1,,test); +connection user1; +select table_schema, table_name, view_definition from information_schema.views +where table_name='v1'; +show create view testdb_1.v1; + +connection default; +revoke select on testdb_1.v1 from mysqltest_1@localhost; +connection user1; +select table_schema, table_name, view_definition from information_schema.views +where table_name='v1'; +--error ER_TABLEACCESS_DENIED_ERROR +show create view testdb_1.v1; + +connection default; +drop user mysqltest_1@localhost; +drop database testdb_1; +disconnect user1; -- cgit v1.2.1 From 342235aec8db2fd4e9010a0688ba6f104fdf8253 Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Thu, 2 Oct 2008 13:04:29 +0200 Subject: Fix for Bug#38762 main.federated_bug_25714 fails sporadically --- mysql-test/t/federated_bug_25714.test | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/federated_bug_25714.test b/mysql-test/t/federated_bug_25714.test index 9c185181511..6d112cae5b8 100644 --- a/mysql-test/t/federated_bug_25714.test +++ b/mysql-test/t/federated_bug_25714.test @@ -1,8 +1,16 @@ --source include/have_bug25714.inc source include/federated.inc; +connection master; +# Disable concurrent inserts to avoid test failures when reading +# data from concurrent connections (insert might return before +# the data is actually in the table). +SET @OLD_MASTER_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; connection slave; +SET @OLD_SLAVE_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; --disable_warnings DROP TABLE IF EXISTS federated.bug_13118_table; --enable_warnings @@ -39,9 +47,12 @@ SELECT LAST_INSERT_ID(); SELECT * from federated.t1; DROP TABLE federated.t1; +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; connection slave; DROP TABLE federated.t1; +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_SLAVE_CONCURRENT_INSERT; source include/federated_cleanup.inc; + -- cgit v1.2.1 From d51e2c0760f5dca2aff03aa16b77bf70e0b06dbc Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Thu, 2 Oct 2008 16:57:52 +0500 Subject: Bug#35924 DEFINER should be stored 'quoted' in I_S The '@' symbol can not be used in the host name according to rfc952. The fix: added function check_host_name(LEX_STRING *str) which checks that all symbols in host name string are valid and host name length is not more than max host name length (just moved check_string_length() function from the parser into check_host_name()). mysql-test/r/create.result: test result mysql-test/t/create.test: test case sql/mysql_priv.h: added function check_host_name(LEX_STRING *str) sql/sql_parse.cc: added function check_host_name(LEX_STRING *str) which checks that all symbols in host name string are valid and host name length is not more than max host name length(HOSTNAME_LENGTH). sql/sql_yacc.yy: using newly added function check_host_name() --- mysql-test/t/create.test | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 61ee40477ee..a837653d618 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1171,6 +1171,11 @@ CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY; SHOW INDEX FROM t1; DROP TABLE t1; +# +# Bug#35924 DEFINER should be stored 'quoted' in I_S +# +--error ER_UNKNOWN_ERROR +create user mysqltest_1@'test@test'; # # Bug#38821: Assert table->auto_increment_field_not_null failed in open_table() -- cgit v1.2.1 From a8f52c1bff7bcbb580fa38bb98cc350fc88fef47 Mon Sep 17 00:00:00 2001 From: Matthias Leich Date: Thu, 2 Oct 2008 15:41:59 +0200 Subject: Fix for Bug#36874 : main.slow_launch_time_func test fails --- mysql-test/t/slow_launch_time_func.test | 129 +++++++++++++++----------------- 1 file changed, 62 insertions(+), 67 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/slow_launch_time_func.test b/mysql-test/t/slow_launch_time_func.test index fe8d1ba4c02..c9a7d28bb8a 100644 --- a/mysql-test/t/slow_launch_time_func.test +++ b/mysql-test/t/slow_launch_time_func.test @@ -1,6 +1,6 @@ -############# mysql-test\t\SLOW_LAUNCH_time_func.test ########################## +############# mysql-test\t\slow_launch_time_func.test ########################## # # -# Variable Name: slow_launch_time # +# Variable Name: slow_launch_time # # Scope: SESSION # # Access Type: Dynamic # # Data Type: NUMERIC # @@ -9,14 +9,20 @@ # # # # # Creation Date: 2008-03-02 # -# Author: Sharique Abdullah # +# Author: Sharique Abdullah # +# # +# Last change: 2008-09-09 mleich Reimplementation of this test # +# - Fix Bug#36874 : main.slow_launch_time_func test fails # +# randomly # +# - Fix other failures and streamline the test # # # # Description: Test Cases of Dynamic System Variable "slow_launch_time " # # that checks behavior of this variable in the following ways # # * Functionality based on different values # # # -#Reference: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html# -# option_mysqld_slow_launch_time # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # +# option_mysqld_slow_launch_time # # # ################################################################################ @@ -28,82 +34,71 @@ SET @global_slow_launch_time = @@GLOBAL.slow_launch_time; +--echo ** Connection default ** +connection default; --echo '#--------------------FN_DYNVARS_124_01-------------------------#' -##################################### -# Increase number of connection # -##################################### - ---echo ** Connection default ** -connection default; +######################################################################## +# Reveal that a new connect gets counted as "slow launched thread" if # +# @@GLOBAL.slow_launch_time = 0. # +# The value of slow_launch_threads must be increased by 1. # +######################################################################## SET @@GLOBAL.slow_launch_time=0; SELECT @@GLOBAL.slow_launch_time; ---echo ** Connecting conn5 using username 'root' ** -CONNECT (conn5,localhost,root,,); ---echo ** Connecting conn6 using username 'root' ** -CONNECT (conn6,localhost,root,,); ---echo ** Connecting conn7 using username 'root' ** -CONNECT (conn7,localhost,root,,); ---echo ** Connecting conn8 using username 'root' ** -CONNECT (conn8,localhost,root,,); ---echo ** Connecting conn9 using username 'root' ** -CONNECT (conn9,localhost,root,,); ---echo ** Connecting conn10 using username 'root' ** -CONNECT (conn10,localhost,root,,); ---echo ** Connecting conn11 using username 'root' ** -CONNECT (conn11,localhost,root,,); ---echo ** Connecting conn12 using username 'root' ** -CONNECT (conn12,localhost,root,,); ---echo ** Connecting conn13 using username 'root' ** -CONNECT (conn13,localhost,root,,); ---echo ** Connecting conn14 using username 'root' ** -CONNECT (conn14,localhost,root,,); ---echo ** Connecting conn15 using username 'root' ** -CONNECT (conn15,localhost,root,,); ---echo ** Connecting conn16 using username 'root' ** -CONNECT (conn16,localhost,root,,); +let $value_before= + query_get_value(show status like 'slow_launch_threads', Value, 1); +--echo ** Connecting conn1 using username 'root' ** +CONNECT (conn1,localhost,root,,); +let $value_after= + query_get_value(show status like 'slow_launch_threads', Value, 1); +if (!`SELECT $value_after = $value_before + 1`) +{ + --echo ERROR: Subtest FN_DYNVARS_124_01 failed + --echo A new connect should be counted as 'slow_launch_thread' if + --echo @@GLOBAL.slow_launch_time=0 + SELECT @@GLOBAL.slow_launch_time; + echo Number of slow_launch_threads before new connect: $value_before; + echo Number of slow_launch_threads after new connect: $value_after; +} +--echo ** Switch to connection default and disconnect conn1 ** +connection default; +disconnect conn1; + +--echo '#--------------------FN_DYNVARS_124_02-------------------------#' +######################################################################## +# Reveal that a new connect gets not counted as "slow launched thread" # +# if @@GLOBAL.slow_launch_time is sufficient big. # +# The value of slow_launch_threads must not change. # +######################################################################## -# -# Checking status of slow_launch_threads -# +SET @@GLOBAL.slow_launch_time= 1000; +SELECT @@GLOBAL.slow_launch_time; -show status like 'slow_launch_threads'; ---echo 12 Expected +let $value_before= + query_get_value(show status like 'slow_launch_threads', Value, 1); +--echo ** Connecting conn2 using username 'root' ** +CONNECT (conn2,localhost,root,,); +let $value_after= + query_get_value(show status like 'slow_launch_threads', Value, 1); +if (!`SELECT $value_after = $value_before`) +{ + --echo ERROR: Subtest FN_DYNVARS_124_02 failed + --echo A new connect must not be counted as 'slow_launch_thread' if + --echo @@GLOBAL.slow_launch_time is sufficient big. + SELECT @@GLOBAL.slow_launch_time; + echo Number of slow_launch_threads before new connect: $value_before; + echo Number of slow_launch_threads after new connect: $value_after; +} # # Cleanup # ---echo ** Connection default ** +--echo ** Switch to connection default and disconnect conn2 ** connection default; - ---echo ** Disconnecting conn5 ** -disconnect conn5; ---echo ** Disconnecting conn6 ** -disconnect conn6; ---echo ** Disconnecting conn7 ** -disconnect conn7; ---echo ** Disconnecting conn8 ** -disconnect conn8; ---echo ** Disconnecting conn9 ** -disconnect conn9; ---echo ** Disconnecting conn10 ** -disconnect conn10; ---echo ** Disconnecting conn11 ** -disconnect conn11; ---echo ** Disconnecting conn12 ** -disconnect conn12; ---echo ** Disconnecting conn13 ** -disconnect conn13; ---echo ** Disconnecting conn14 ** -disconnect conn14; ---echo ** Disconnecting conn15 ** -disconnect conn15; ---echo ** Disconnecting conn16 ** -disconnect conn16; - +disconnect conn2; SET @@GLOBAL.slow_launch_time = @global_slow_launch_time; -- cgit v1.2.1 From a18639b63426092f6df98f6c67ab1139fe50e3c8 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Thu, 2 Oct 2008 17:44:49 +0300 Subject: Bug #37348: Crash in or immediately after JOIN::make_sum_func_list The optimizer pulls up aggregate functions which should be aggregated in an outer select. At some point it may substitute such a function for a field in the temporary table. The setup_copy_fields function doesn't take this into account and may overrun the copy_field buffer. Fixed by filtering out the fields referenced through the specialized reference for aggregates (Item_aggregate_ref). Added an assertion to make sure bugs that cause similar discrepancy don't go undetected. mysql-test/r/func_group.result: Bug #37348: test case mysql-test/t/func_group.test: Bug #37348: test case sql/item.cc: Bug #37348: Added a way to distinguish Item_aggregate_ref from the other types of refs sql/item.h: Bug #37348: Added a way to distinguish Item_aggregate_ref from the other types of refs sql/sql_select.cc: Bug #37348: - Don't consider copying field references seen through Item_aggregate_ref - check for discrepancies between the number of expected fields that need copying and the actual fields copied. --- mysql-test/t/func_group.test | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index b6143bc0c78..4eedd433d34 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -933,5 +933,45 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; DROP TABLE t1; +# +# Bug #37348: Crash in or immediately after JOIN::make_sum_func_list +# + +CREATE TABLE derived1 (a bigint(21)); +INSERT INTO derived1 VALUES (2); + + +CREATE TABLE D ( + pk int(11) NOT NULL AUTO_INCREMENT, + int_nokey int(11) DEFAULT NULL, + int_key int(11) DEFAULT NULL, + filler blob, + PRIMARY KEY (pk), + KEY int_key (int_key) +); + +INSERT INTO D VALUES + (39,40,4,repeat(' X', 42)), + (43,56,4,repeat(' X', 42)), + (47,12,4,repeat(' X', 42)), + (71,28,4,repeat(' X', 42)), + (76,54,4,repeat(' X', 42)), + (83,45,4,repeat(' X', 42)), + (105,53,12,NULL); + +SELECT + (SELECT COUNT( int_nokey ) + FROM derived1 AS X + WHERE + X.int_nokey < 61 + GROUP BY pk + LIMIT 1) +FROM D AS X +WHERE X.int_key < 13 +GROUP BY int_nokey LIMIT 1; + +DROP TABLE derived1; +DROP TABLE D; + ### --echo End of 5.0 tests -- cgit v1.2.1 From 30d46cac5addbed32a0a1a1f88dcbfe39025896c Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Fri, 3 Oct 2008 08:40:45 -0300 Subject: Bug#37481: status.test fails randomly The problem was that the test was trying to obtain a lock on a table in one connection without ensuring that a insert which was executed in another connection had released the lock on the same table. The solution is to add a dummy select query after the insert to ensure that the table is unlocked and closed by the time it tries to lock it again. This is enough to prevent test failures described in the bug report. As an extra safety measure, concurrent inserts are disabled. Remove comments that calculated the Table_locks_immediate. This value is not tested anymore and it's calculation did not reflect the actual value. mysql-test/r/status.result: Update test case result. mysql-test/t/status.test: Issue a dummy select to ensure that tables are unlocked after a insert and disable concurrent inserts as a extra-safety. --- mysql-test/t/status.test | 41 +++++++++++++++++++++-------------------- 1 file changed, 21 insertions(+), 20 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 8bd9ee26b26..bc241423417 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -4,6 +4,11 @@ # embedded server causes different stat -- source include/not_embedded.inc +# Disable concurrent inserts to avoid sporadic test failures as it might +# affect the the value of variables used throughout the test case. +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 0; + # PS causes different statistics --disable_ps_protocol @@ -12,54 +17,45 @@ connect (con2,localhost,root,,); flush status; -# Logging to the general query log table (--log-output=table --log) increments -# Table_locks_immediate with each query, so here Immediate becomes 1 show status like 'Table_lock%'; -# ++Immediate = 2 select * from information_schema.session_status where variable_name like 'Table_lock%'; connection con1; -# ++Immediate = 3 -SET SQL_LOG_BIN=0; -set @old_general_log = @@global.general_log; +--echo # Switched to connection: con1 +set sql_log_bin=0; +set @old_general_log = @@global.general_log; set global general_log = 'OFF'; --disable_warnings -# ++Immediate = 4 drop table if exists t1; --enable_warnings -# ++Immediate = 5 create table t1(n int) engine=myisam; -# Immediate + 2 = 7 insert into t1 values(1); +# Execute dummy select in order to ensure that tables used in the +# previous statement are unlocked and closed. +select 1; connection con2; -# Immediate + 2 = 9 +--echo # Switched to connection: con2 lock tables t1 read; -# ++Immediate = 10 unlock tables; -# Immediate + 2 = 12 lock tables t1 read; connection con1; -# ++Immediate = 13 +--echo # Switched to connection: con1 let $ID= `select connection_id()`; -# ++Immediate = 14 (Not +2, because this increments Table_locks_waited) ---send -update t1 set n = 3; +--send update t1 set n = 3 connection con2; +--echo # Switched to connection: con2 # wait for the other query to start executing let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST where ID = $ID and STATE = "Locked"; -# Immediate = 14 + $wait_condition_reps ($wait_timeout is 0, so no extra select -# is done inside wait_condition.inc) --source include/wait_condition.inc -# ++Immediate = 15 + $wait_condition_reps unlock tables; connection con1; +--echo # Switched to connection: con1 reap; -# ++Immediate = 16 + $wait_condition_reps show status like 'Table_locks_waited'; drop table t1; set global general_log = @old_general_log; @@ -67,6 +63,7 @@ set global general_log = @old_general_log; disconnect con2; disconnect con1; connection default; +--echo # Switched to connection: default # End of 4.1 tests @@ -295,3 +292,7 @@ drop database db37908; drop procedure proc37908; drop function func37908; # End of 5.1 tests + +# Restore global concurrent_insert value. Keep in the end of the test file. +--connection default +set @@global.concurrent_insert= @old_concurrent_insert; -- cgit v1.2.1 From 19256b96e8a90112195f1882cf6003a61b09cae1 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 3 Oct 2008 15:24:19 +0300 Subject: Bug #39812: Make statement replication default for 5.1 (to match 5.0) Make STMT replication default for 5.1. Add a default of MIXED into the config files Fix the tests that needed MIXED replication mode. mysql-test/include/mix1.inc: Bug #39812: Fix the tests that needed MIXED replication mode. mysql-test/r/innodb-semi-consistent.result: Bug #39812: Fix the tests that needed MIXED replication mode. mysql-test/r/innodb.result: Bug #39812: Fix the tests that needed MIXED replication mode. mysql-test/r/innodb_mysql.result: Bug #39812: Fix the tests that needed MIXED replication mode. mysql-test/r/tx_isolation_func.result: Bug #39812: Fix the tests that needed MIXED replication mode. mysql-test/t/innodb-semi-consistent.test: Bug #39812: Fix the tests that needed MIXED replication mode. mysql-test/t/innodb.test: Bug #39812: Fix the tests that needed MIXED replication mode. mysql-test/t/tx_isolation_func.test: Bug #39812: Fix the tests that needed MIXED replication mode. sql/mysqld.cc: Bug #39812: Make STMT replication default for 5.1. support-files/my-huge.cnf.sh: Bug #39812: Add a default of MIXED into the config files support-files/my-innodb-heavy-4G.cnf.sh: Bug #39812: Add a default of MIXED into the config files support-files/my-large.cnf.sh: Bug #39812: Add a default of MIXED into the config files support-files/my-medium.cnf.sh: Bug #39812: Add a default of MIXED into the config files support-files/my-small.cnf.sh: Bug #39812: Add a default of MIXED into the config files --- mysql-test/t/innodb-semi-consistent.test | 2 ++ mysql-test/t/innodb.test | 17 +++++++++++++++++ mysql-test/t/tx_isolation_func.test | 2 ++ 3 files changed, 21 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/innodb-semi-consistent.test b/mysql-test/t/innodb-semi-consistent.test index c33126b93ff..a3496625e95 100644 --- a/mysql-test/t/innodb-semi-consistent.test +++ b/mysql-test/t/innodb-semi-consistent.test @@ -10,6 +10,7 @@ drop table if exists t1; connect (a,localhost,root,,); connect (b,localhost,root,,); connection a; +set binlog_format=mixed; set session transaction isolation level read committed; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2),(3),(4),(5),(6),(7); @@ -17,6 +18,7 @@ set autocommit=0; # this should lock the entire table select * from t1 where a=3 lock in share mode; connection b; +set binlog_format=mixed; set session transaction isolation level read committed; set autocommit=0; -- error ER_LOCK_WAIT_TIMEOUT diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 1073f5535df..339be87419a 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -701,6 +701,7 @@ insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); select id, code, name from t1 order by id; COMMIT; +SET binlog_format='MIXED'; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); @@ -2000,10 +2001,12 @@ connection a; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); commit; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t1 set b = 5 where b = 1; connection b; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; # @@ -2071,6 +2074,7 @@ commit; set autocommit = 0; select * from t2 for update; connection b; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t1 select * from t2; @@ -2127,46 +2131,55 @@ commit; set autocommit = 0; select * from t2 for update; connection b; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; --send insert into t1 select * from t2; connection c; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; --send update t3 set b = (select b from t2 where a = d); connection d; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; --send create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2; connection e; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; --send insert into t5 (select * from t2 lock in share mode); connection f; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; --send update t6 set e = (select b from t2 where a = d lock in share mode); connection g; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; --send create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode; connection h; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; --send insert into t8 (select * from t2 for update); connection i; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; --send update t9 set e = (select b from t2 where a = d for update); connection j; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; --send @@ -2380,6 +2393,7 @@ DROP TABLE t1; CONNECT (c1,localhost,root,,); CONNECT (c2,localhost,root,,); CONNECTION c1; +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; DROP TABLE IF EXISTS t1, t2; @@ -2387,6 +2401,7 @@ CREATE TABLE t1 ( a int ) ENGINE=InnoDB; CREATE TABLE t2 LIKE t1; SELECT * FROM t2; CONNECTION c2; +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (1); @@ -2398,10 +2413,12 @@ DISCONNECT c2; CONNECT (c1,localhost,root,,); CONNECT (c2,localhost,root,,); CONNECTION c1; +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; SELECT * FROM t2; CONNECTION c2; +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (2); diff --git a/mysql-test/t/tx_isolation_func.test b/mysql-test/t/tx_isolation_func.test index 3a4167dc368..1fd2e323db8 100644 --- a/mysql-test/t/tx_isolation_func.test +++ b/mysql-test/t/tx_isolation_func.test @@ -75,10 +75,12 @@ INSERT INTO t1 VALUES(24, 24); --echo ** Connection con0 ** connection con0; SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +set binlog_format=mixed; --echo ** Connection con1 ** connection con1; SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +set binlog_format=mixed; # # Testing WHERE on keys using IN clause -- cgit v1.2.1 From 87a77eecacd00402c7313b7484f9b95913c6406f Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Mon, 6 Oct 2008 11:05:34 +0500 Subject: Bug#38083 Error-causing row inserted into partitioned table despite error problems are located in the sql_partition.cc where functions calculation partition_id don't expect error returned from item->val_int(). Fixed by adding checks to these functions. Note - it tries to fix more problems than just the reported bug. per-file comments: modified: mysql-test/r/partition.result Bug#38083 Error-causing row inserted into partitioned table despite error test result mysql-test/t/partition.test Bug#38083 Error-causing row inserted into partitioned table despite error test case sql/opt_range.cc Bug#38083 Error-causing row inserted into partitioned table despite error get_part_id() call fixed sql/partition_info.h Bug#38083 Error-causing row inserted into partitioned table despite error get_subpart_id_func interface changed. sql/sql_partition.cc Bug#38083 Error-causing row inserted into partitioned table despite error various functions calculationg partition_id and subpart_id didn't expect an error returned from item->val_int(). Error checks added. --- mysql-test/t/partition.test | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 5270eced05f..7e357e0bc31 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1791,4 +1791,27 @@ select count(*) from t1, t2 where t1.createdDate = t2.createdDate; drop table t1, t2; +# +# Bug #38083 Error-causing row inserted into partitioned table despite error +# +SET @orig_sql_mode = @@SQL_MODE; +SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; +CREATE TABLE t1 (c1 INT) + PARTITION BY LIST(1 DIV c1) ( + PARTITION p0 VALUES IN (NULL), + PARTITION p1 VALUES IN (1) + ); + +-- error ER_DIVISION_BY_ZERO +INSERT INTO t1 VALUES (0); +SELECT * FROM t1; +TRUNCATE t1; +-- error ER_DIVISION_BY_ZERO +INSERT INTO t1 VALUES (NULL), (0), (1), (2); +SELECT * FROM t1; +DROP TABLE t1; +SET SQL_MODE= @orig_sql_mode; + + + --echo End of 5.1 tests -- cgit v1.2.1 From 07953513edc3e5956d485bec0e6067f5e864a7db Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Mon, 6 Oct 2008 11:29:42 +0200 Subject: WL#4403 deprecate @log and @slow_log_queries variables Adds --general_log_file, --slow_query_log_file command- line options to match system variables of the same names. Deprecates --log, --log-slow-queries command-line options and log, log_slow_queries system-variables for v7.0; they are superseded by general_log/general_log_file and slow_query_log/slow_query_log_file, respectively. mysql-test/r/log_basic.result: Change deprecated system variable "log" to general log. mysql-test/r/log_state.result: Show that all log-related server variables that should throw deprecation warnings do, and the others don't. mysql-test/t/log_basic.test: Change deprecated system variable "log" to general log. mysql-test/t/log_state.test: Show that all log-related server variables that should throw deprecation warnings do, and the others don't. sql/mysqld.cc: Add command-line options --general_log_file and --slow_query_log_file to match server options of the same name. Deprecated --log and --log-slow-queries command-line options; they are superseded by --general-log/ --general-log-file and --slow-query-log/--slow-query-log-file, respectively sql/set_var.cc: Deprecate system-variables log in favour of general_log, log_slow_queries in favour of slow_query_log for 7.0, both for value- and DEFAULT-setting. --- mysql-test/t/log_basic.test | 4 ++-- mysql-test/t/log_state.test | 26 ++++++++++++++++++++++++++ 2 files changed, 28 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/log_basic.test b/mysql-test/t/log_basic.test index 16d7a4bfe7f..b9a64f8981b 100644 --- a/mysql-test/t/log_basic.test +++ b/mysql-test/t/log_basic.test @@ -38,9 +38,9 @@ SELECT @@global.log AS INIT_VALUE; SELECT @@log AS INIT_VALUE; -SET @@global.log = ON; +SET @@global.general_log = ON; -SET global log = 0; +SET global general_log = 0; --echo 'Bug# 34832: log is a system but it is not accessible using SET @@global.log;' --echo 'SET GLOBAL log; and SELECT @@global.log. SHOW VARIABLES shows the value of log.' diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index 2fd2cabc97c..977b74aa1e3 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -259,6 +259,32 @@ SET GLOBAL slow_query_log_file = @slow_query_log_file_saved; ########################################################################### + + +## WL#4403 - deprecate @log and @slow_log_queries variables + +## these are all deprecated -- show for command-line as well! +--echo deprecated: +SET GLOBAL log = 0; +SET GLOBAL log_slow_queries = 0; +SET GLOBAL log = DEFAULT; +SET GLOBAL log_slow_queries = DEFAULT; + +## these are NOT deprecated +--echo not deprecated: +SELECT @@global.general_log_file INTO @my_glf; +SELECT @@global.slow_query_log_file INTO @my_sqlf; +SET GLOBAL general_log = 0; +SET GLOBAL slow_query_log = 0; +SET GLOBAL general_log_file = 'WL4403_G.log'; +SET GLOBAL slow_query_log_file = 'WL4403_SQ.log'; +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; + + + --echo End of 5.1 tests --enable_ps_protocol -- cgit v1.2.1 From 468cacec4fd850baafa287a00bf9d68914ab59d1 Mon Sep 17 00:00:00 2001 From: Chad MILLER Date: Mon, 6 Oct 2008 08:18:13 -0400 Subject: Fix for test for b-g#35754 which fails based on hostname ?= "localhost". --- mysql-test/t/join.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 55048a86f14..6c4de33950e 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -547,9 +547,9 @@ select * from v1a join v1b on t1.b = t2.b; # Bug #17523 natural join and information_schema # # Omit columns.PRIVILIGES as it may vary with embedded server. -# Omit columns.ORDINAL_POSITION as it may vary with hostname='localhost'. +# Omit columns.ORDINAL_POSITION and statistics.CARDINALITY as it may vary with hostname='localhost'. select - statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.CARDINALITY, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, + statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; -- cgit v1.2.1 From afbb52c43c0180917db6f7718232d40031f47fa4 Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Mon, 6 Oct 2008 18:14:20 +0500 Subject: Bug#38005 Partitions: error with insert select. MyISAM blocks index usage for bulk insert into zero-records tables. See ha_myisam::start_bulk_insert() lines from ... if (file->state->records == 0 ... ... That causes problems for partition engine when some partitions have records some not as the engine uses same access method for all partitions. Now partition engine doesn't call index_first/index_last for empty tables. per-file comments: mysql-test/r/partition.result Bug#38005 Partitions: error with insert select. test result mysql-test/t/partition.test Bug#38005 Partitions: error with insert select. test case sql/ha_partition.cc Bug#38005 Partitions: error with insert select. ha_engine::index_first and ha_engine::index_last not called for empty tables. --- mysql-test/t/partition.test | 49 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 49 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 5270eced05f..970eb3e6f35 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1791,4 +1791,53 @@ select count(*) from t1, t2 where t1.createdDate = t2.createdDate; drop table t1, t2; +# +# Bug #38005 Partitions: error with insert select +# + +create table t1 (s1 int) partition by hash(s1) partitions 2; +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +drop table t1; + +create table t1 (s1 int) partition by range(s1) + (partition pa1 values less than (10), + partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +drop table t1; + +create table t1 (s1 int) partition by range(s1) + (partition pa1 values less than (10), + partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (20); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +drop table t1; + +create table t1 (s1 int) partition by range(s1) + (partition pa1 values less than (10), + partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +insert into t1 select s1 from t1 where s1=3; +select count(*) from t1; +drop table t1; + --echo End of 5.1 tests -- cgit v1.2.1 From 84c1fffabbf89a7c1ed3a768c81dc9060516255c Mon Sep 17 00:00:00 2001 From: Guilhem Bichot Date: Mon, 6 Oct 2008 16:06:59 +0200 Subject: Fix for BUG#31612 "Trigger fired multiple times leads to gaps in auto_increment sequence". The bug was that if a trigger fired multiple times inside a top statement (for example top-statement is a multi-row INSERT, and trigger is ON INSERT), and that trigger inserted into an auto_increment column, then gaps could be observed in the auto_increment sequence, even if there were no other users of the database (no concurrency). It was wrong usage of THD::auto_inc_intervals_in_cur_stmt_for_binlog. Note that the fix changes "class handler", I'll tell the Storage Engine API team. mysql-test/r/trigger-trans.result: result; before the bugfix, the sequence was 1,2,4,6,8,10,12... mysql-test/t/trigger-trans.test: test for BUG#31612 sql/handler.cc: See revision comment of handler.h. As THD::auto_inc_intervals_in_cur_stmt_for_binlog is cumulative over all trigger invokations by the top statement, the second invokation of the trigger arrived in handler::update_auto_increment() with already one interval in THD::auto_inc_intervals_in_cur_stmt_for_binlog. The method thus believed it had already reserved one interval for that invokation, thus reserved a twice larger interval (heuristic when we don't know how large the interval should be: we grow by powers of two). InnoDB thus increased its internal per-table auto_increment counter by 2 while only one row was to be inserted. Hence a gap in the sequence. The fix is to use the new handler::auto_inc_intervals_count. Note that the trigger's statement knows how many rows it is going to insert, but provides estimation_rows_to_insert == 0 (see comments in sql_insert.cc why triggers don't call handler::ha_start_bulk_insert()). * removing white space at end of line * we don't need to maintain THD::auto_inc_intervals_in_cur_stmt_for_binlog if no binlogging or if row-based binlogging. Using auto_inc_intervals_count in the heuristic makes the heuristic independent of binary logging, which is good. sql/handler.h: THD::auto_inc_intervals_in_cur_stmt_for_binlog served - for binlogging - as a heuristic when we have no estimation of how many records the statement will insert. But the first goal needs to be cumulative over all statements which form a binlog event, while the second one needs to be attached to each statement. THD::auto_inc_intervals_in_cur_stmt_for_binlog is cumulative, leading to BUG#31612. So we introduce handler::auto_inc_intervals_count for the second goal. See the revision comment of handler.cc. A smaller issue was that, even when the binlog event was only one statement (no triggers, no stored functions), THD::auto_inc_intervals_in_cur_stmt.nb_elements() could be lower than the number of reserved intervals (fooling the heuristic), because its append() method collapses two contiguous intervals in one. Note that as auto_inc_intervals_count is in class 'handler' and not in class 'THD', it does not need to be handled in THD::reset|restore_sub_statement_state(). sql/log.cc: Comment is wrong: if auto_increment is second, in handler::update_auto_increment() 'append' is false and so auto_inc_intervals_in_cur_stmt_for_binlog is empty, we do not come here. sql/sql_class.h: comment --- mysql-test/t/trigger-trans.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/trigger-trans.test b/mysql-test/t/trigger-trans.test index 5db5b982773..ae223b2666e 100644 --- a/mysql-test/t/trigger-trans.test +++ b/mysql-test/t/trigger-trans.test @@ -162,3 +162,16 @@ DROP TABLE t2, t1; --echo End of 5.0 tests + +--echo BUG#31612 +--echo Trigger fired multiple times leads to gaps in auto_increment sequence +create table t1 (a int, val char(1)) engine=InnoDB; +create table t2 (b int auto_increment primary key, + val char(1)) engine=InnoDB; +create trigger t1_after_insert after + insert on t1 for each row insert into t2 set val=NEW.val; +insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'), + (123, 'd'), (123, 'e'), (123, 'f'), (123, 'g'); +insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'), + (654, 'd'), (654, 'e'), (654, 'f'), (654, 'g'); +select * from t2 order by b; -- cgit v1.2.1 From 4d7ad72e52db47b6d8364c34dbfb206c40cb4aad Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 7 Oct 2008 19:54:12 +0300 Subject: fixed test suite failures in 5.1-bugteam --- mysql-test/t/trigger-trans.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/trigger-trans.test b/mysql-test/t/trigger-trans.test index ae223b2666e..4d6e82dedcb 100644 --- a/mysql-test/t/trigger-trans.test +++ b/mysql-test/t/trigger-trans.test @@ -175,3 +175,5 @@ insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'), insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'), (654, 'd'), (654, 'e'), (654, 'f'), (654, 'g'); select * from t2 order by b; +drop trigger t1_after_insert; +drop table t1,t2; -- cgit v1.2.1 From f48b42e77657dd2e27380201631fd0f137863b85 Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Wed, 8 Oct 2008 02:34:00 +0500 Subject: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' Concurrent execution of 1) multitable update with a NATURAL/USING join and 2) a such query as "FLUSH TABLES WITH READ LOCK" or "ALTER TABLE" of updating table led to a server crash. The mysql_multi_update_prepare() function call is optimized to lock updating tables only, so it postpones locking to the last, and if locking fails, it does cleanup of modified syntax structures and repeats a query analysis. However, that cleanup procedure was incomplete for NATURAL/USING join syntax data: 1) some Field_item items pointed into freed table structures, and 2) the TABLE_LIST::join_columns fields was not reset. Major change: short-living Field *Natural_join_column::table_field has been replaced with long-living Item*. mysql-test/r/lock_multi.result: Added test case for bug #38691. mysql-test/t/lock_multi.test: Added test case for bug #38691. sql/item.cc: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' The Item_field constructor has been modified to allocate and copy original database/table/field names always (not during PS preparation/1st execution only), because an initialization of Item_field items with a pointer to short-living Field structures is a common practice. sql/sql_base.cc: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' 1) Type adjustment for Natural_join_column::table_field (Field to Item_field); 2) The setup_natural_join_row_types function has been updated to take into account new first_natural_join_processing flag to skip unnecessary reinitialization of Natural_join_column::join_columns during table reopening after lock_tables() failure (like the 'first_execution' flag for PS). sql/sql_lex.cc: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' Initialization of the new st_select_lex::first_natural_join_processing flag has been added. sql/sql_lex.h: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' The st_select_lex::first_natural_join_processing flag has been added to skip unnecessary rebuilding of NATURAL/USING JOIN structures during table reopening after lock_tables failure. sql/sql_update.cc: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' Extra cleanup calls have been added to reset Natural_join_column::table_field items. sql/table.cc: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' Type adjustment for Natural_join_column::table_field (Field to Item_field). sql/table.h: Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while ``FLUSH TABLES WITH READ LOCK'' Type of the Natural_join_column::table_field field has been changed from Field that points into short-living TABLE memory to long-living Item_field that can be linked to (fixed) reopened table. --- mysql-test/t/lock_multi.test | 119 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 119 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 649c1a4efbd..fe22efeb8c2 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -281,4 +281,123 @@ unlock tables; connection default; drop table t1; +# +# Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while +# ``FLUSH TABLES WITH READ LOCK'' +# + +--connection default +CREATE TABLE t1 ( + a int(11) unsigned default NULL, + b varchar(255) default NULL, + UNIQUE KEY a (a), + KEY b (b) +); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); +CREATE TABLE t2 SELECT * FROM t1; +CREATE TABLE t3 SELECT * FROM t1; + +--echo # test altering of columns that multiupdate doesn't use + +--echo # normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) + SET a = NULL WHERE t1.b <> t2.b; + +--connection locker + ALTER TABLE t2 ADD COLUMN (c INT); + ALTER TABLE t2 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) + SET a = NULL WHERE t1.b <> t2.b'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t2 ADD COLUMN (c INT); + ALTER TABLE t2 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + + +--echo # test altering of columns that multiupdate uses + +--echo # normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,1060 + ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t2 SET a=b; + +--connection writer +--send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b + +--connection locker +--error 0,1091 + ALTER TABLE t2 DROP COLUMN a; + +--connection writer +--error 0,1054 +--reap +} +--enable_query_log + +--echo # PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,1060 + ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t2 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b'; +--send EXECUTE stmt + +--connection locker +--error 0,1091 + ALTER TABLE t2 DROP COLUMN a; + +--connection writer +--error 0,1054 +--reap + +} +--enable_query_log +--connection default +DROP TABLE t1, t2, t3; + # End of 5.0 tests -- cgit v1.2.1 From a83f5b18efaa54df3b56c86c1ab9244280447f24 Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Thu, 9 Oct 2008 20:24:31 +0500 Subject: Bug#38499: flush tables and multitable table update with derived table cause crash When a multi-UPDATE command fails to lock some table, and subsequently succeeds, the tables need to be reopened if they were altered. But the reopening procedure failed for derived tables. Extra cleanup has been added. mysql-test/r/lock_multi.result: Added test case for bug #38499. mysql-test/t/lock_multi.test: Added test case for bug #38499. sql/sql_union.cc: Bug#38499: flush tables and multitable table update with derived table cause crash Obsolete assertion has been removed. sql/sql_update.cc: Bug#38499: flush tables and multitable table update with derived table cause crash Extra cleanup for derived tables has been added: 1) unit.cleanup(), 2) unit->reinit_exec_mechanism(). --- mysql-test/t/lock_multi.test | 202 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 202 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index fe22efeb8c2..089a60edb3d 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -400,4 +400,206 @@ while ($i) { --connection default DROP TABLE t1, t2, t3; +# +# Bug#38499: flush tables and multitable table update with derived table cause +# crash +# + +CREATE TABLE t1( a INT, b INT ); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); + +--echo # 1. test regular tables +--echo # 1.1. test altering of columns that multiupdate doesn't use +--echo # 1.1.1. normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # 1.1.2. PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + +--echo # 1.2. test altering of columns that multiupdate uses +--echo # 1.2.1. normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,1060 + ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t1 SET a=b; + +--connection writer +--send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; + +--connection locker +--error 0,1091 + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,1054 # unknown column error +--reap +} +--enable_query_log + +--echo # 1.2.2. PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,1060 + ALTER TABLE t1 ADD COLUMN a INT; + UPDATE t1 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; +--send EXECUTE stmt + +--connection locker +--error 0,1091 + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,1054 # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log +--connection default +ALTER TABLE t1 ADD COLUMN a INT; + +--echo # 2. test UNIONs +--echo # 2.1. test altering of columns that multiupdate doesn't use +--echo # 2.1.1. normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # 2.1.2. PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + +--echo # 2.2. test altering of columns that multiupdate uses +--echo # 2.2.1. normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,1060 + ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t1 SET a=b; + +--connection writer +--send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; + +--connection locker +--error 0,1091 + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,1054 # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log + +--echo # 2.2.2. PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,1060 + ALTER TABLE t1 ADD COLUMN a INT; + UPDATE t1 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; +--send EXECUTE stmt + +--connection locker +--error 0,1091 + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,1054 # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log +--connection default +DROP TABLE t1; + # End of 5.0 tests -- cgit v1.2.1