diff options
| author | unknown <msvensson@neptunus.(none)> | 2006-12-04 19:11:55 +0100 |
|---|---|---|
| committer | unknown <msvensson@neptunus.(none)> | 2006-12-04 19:11:55 +0100 |
| commit | dea988a04809c326a147935230f1ae5dfbc72fa2 (patch) | |
| tree | ac382a3e694ccbe7ed5a481331e35e4f055fdcae /mysql-test/t | |
| parent | 23b2d1d2c62b3a3fd94ea17e4728da82b0b2dc0a (diff) | |
| parent | a12fff7315effaecf741770665137e05f0a2e623 (diff) | |
| download | mariadb-git-dea988a04809c326a147935230f1ae5dfbc72fa2.tar.gz | |
Merge neptunus.(none):/home/msvensson/mysql/mysql-5.1
into neptunus.(none):/home/msvensson/mysql/mysql-5.1-maint
BitKeeper/etc/collapsed:
auto-union
BitKeeper/etc/ignore:
auto-union
Makefile.am:
Auto merged
client/mysql.cc:
Auto merged
client/mysqltest.c:
Auto merged
configure.in:
Auto merged
extra/yassl/taocrypt/include/algebra.hpp:
Auto merged
include/Makefile.am:
Auto merged
include/my_sys.h:
Auto merged
mysql-test/lib/mtr_io.pl:
Auto merged
mysql-test/lib/mtr_process.pl:
Auto merged
mysql-test/mysql-test-run.pl:
Auto merged
mysql-test/r/information_schema.result:
Auto merged
mysql-test/r/parser.result:
Auto merged
mysql-test/r/ps.result:
Auto merged
mysql-test/r/view_grant.result:
Auto merged
mysql-test/t/information_schema.test:
Auto merged
mysql-test/t/parser.test:
Auto merged
mysql-test/t/ps.test:
Auto merged
mysql-test/t/sp.test:
Auto merged
mysql-test/t/system_mysql_db_fix30020.test:
Auto merged
mysql-test/t/udf.test:
Auto merged
mysql-test/t/view_grant.test:
Auto merged
scripts/mysql_fix_privilege_tables.sql:
Auto merged
sql/ha_ndbcluster.cc:
Auto merged
sql/handler.cc:
Auto merged
sql/item.cc:
Auto merged
sql/item_create.cc:
Auto merged
sql/item_func.cc:
Auto merged
sql/item_func.h:
Auto merged
sql/item_timefunc.cc:
Auto merged
sql/mysql_priv.h:
Auto merged
sql/mysqld.cc:
Auto merged
sql/sp.cc:
Auto merged
sql/sql_base.cc:
Auto merged
sql/sql_handler.cc:
Auto merged
sql/sql_lex.cc:
Auto merged
sql/sql_lex.h:
Auto merged
sql/sql_parse.cc:
Auto merged
sql/sql_table.cc:
Auto merged
sql-common/my_time.c:
Auto merged
sql/sql_yacc.yy:
Auto merged
storage/myisam/myisampack.c:
Auto merged
mysql-test/r/sp.result:
Manual merge
mysql-test/r/udf.result:
Manual merge
mysql-test/t/events_bugs.test:
Manual merge
sql/share/errmsg.txt:
Manual merge
support-files/mysql.spec.sh:
Manual merge
Diffstat (limited to 'mysql-test/t')
52 files changed, 1051 insertions, 38 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 78bbd23adf1..406854ebaed 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -372,6 +372,103 @@ alter table t1 add unique ( a(1) ); drop table t1; # +# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table +# +# This problem happens if the data change is compatible. +# Changing to the same type is compatible for example. +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, key(a)); +show indexes from t1; +--echo "this used not to disable the index" +alter table t1 modify a int, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 add b char(10), disable keys; +show indexes from t1; + +alter table t1 add c decimal(10,2), enable keys; +show indexes from t1; + +--echo "this however did" +alter table t1 disable keys; +show indexes from t1; + +desc t1; + +alter table t1 add d decimal(15,5); +--echo "The key should still be disabled" +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique index" +create table t1(a int, b char(10), unique(a)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + +--echo "If no copy on noop change, this won't touch the data file" +--echo "Unique index, no change" +alter table t1 modify a int, disable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "Unique index, no change" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 modify a bigint; +show indexes from t1; + +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique and one non-unique index" +create table t1(a int, b char(10), unique(a), key(b)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + + +--echo "If no copy on noop change, this won't touch the data file" +--echo "The non-unique index will be disabled" +alter table t1 modify a int, disable keys; +show indexes from t1; +alter table t1 enable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "The non-unique index will be disabled" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +--echo "Change again the type, but leave the indexes as_is" +alter table t1 modify a int; +show indexes from t1; +--echo "Try the same. When data is no copied on similar tables, this is noop" +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + + +# # Bug#11493 - Alter table rename to default database does not work without # db name qualifying # @@ -402,6 +499,24 @@ alter table test.t1 rename test.t1; use test; drop table t1; +# +# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash +# +--disable_warnings +DROP TABLE IF EXISTS bug24219; +DROP TABLE IF EXISTS bug24219_2; +--enable_warnings + +CREATE TABLE bug24219 (a INT, INDEX(a)); + +SHOW INDEX FROM bug24219; + +ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; + +SHOW INDEX FROM bug24219_2; + +DROP TABLE bug24219_2; + # End of 4.1 tests # diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 35cda11646a..6c1229db83f 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -278,4 +278,13 @@ select * from t1 where bigint_col=17666000000000000000; select * from t1 where bigint_col='17666000000000000000'; drop table t1; +--echo +--echo bug 19955 -- mod is signed with bigint + +select cast(10000002383263201056 as unsigned) mod 50 as result; + +create table t1 (c1 bigint unsigned); +insert into t1 values (10000002383263201056); +select c1 mod 50 as result from t1; +drop table t1; diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index c19ebeb2e28..64bd69c1855 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -329,4 +329,12 @@ SELECT TIME_FORMAT("25:00:00", '%l %p'); # SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896); +# +# Bug #22029: str_to_date returning NULL +# + +select str_to_date('04 /30/2004', '%m /%d/%Y'); +select str_to_date('04/30 /2004', '%m /%d /%Y'); +select str_to_date('04/30/2004 ', '%m/%d/%Y '); + --echo "End of 4.1 tests" diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test index b5522394d2d..225ddbc3ee2 100644 --- a/mysql-test/t/default.test +++ b/mysql-test/t/default.test @@ -82,3 +82,61 @@ SELECT * from t2; drop table t1; drop table t2; + +# +# Bug#20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when specifying DEFAULT +# +# From the docs: +# If the column can take NULL as a value, the column is defined with an +# explicit DEFAULT NULL clause. This is the same as before 5.0.2. +# +# If the column cannot take NULL as the value, MySQL defines the column with +# no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE +# statement includes no value for the column, MySQL handles the column +# according to the SQL mode in effect at the time: +# +# * If strict SQL mode is not enabled, MySQL sets the column to the +# implicit default value for the column data type. +# +# * If strict mode is enabled, an error occurs for transactional tables and +# the statement is rolled back. For non-transactional tables, an error +# occurs, but if this happens for the second or subsequent row of a +# multiple-row statement, the preceding rows will have been inserted. +# +create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00'); +insert into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT); +insert into bug20691 (i) values (2); +desc bug20691; +insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT); +insert into bug20691 (i) values (4); +insert into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT); +SET sql_mode = 'ALLOW_INVALID_DATES'; +insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT); +SET sql_mode = 'STRICT_ALL_TABLES'; +--error 1364 +insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT); +select * from bug20691 order by i asc; +drop table bug20691; + +SET sql_mode = ''; +create table bug20691 ( + a set('one', 'two', 'three') not null, + b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null, + c time not null, + d date not null, + e int not null, + f long not null, + g blob not null, + h datetime not null, + i decimal not null, + x int); +insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 1); +insert into bug20691 (x) values (2); +insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 3); +insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4); +select * from bug20691 order by x asc; +drop table bug20691; + +### +--echo End of 5.0 tests. + diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index e48108af9ce..b541b6d8d33 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -28,7 +28,6 @@ rpl_ndb_ddl : BUG#18946 result file needs update + test needs to ch rpl_ndb_innodb2ndb : Bug #19710 Cluster replication to partition table fails on DELETE FROM statement rpl_ndb_myisam2ndb : Bug #19710 Cluster replication to partition table fails on DELETE FROM statement rpl_row_blob_innodb : BUG#18980 2006-04-10 kent Test fails randomly -rpl_sp : BUG#16456 2006-02-16 jmiller rpl_multi_engine : BUG#22583 2006-09-23 lars synchronization : Bug#24529 Test 'synchronization' fails on Mac pushbuild; Also on Linux 64 bit. diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index 81988818809..605eaa431d0 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -280,7 +280,7 @@ create event e22830_3 on schedule every 1 hour do alter event e22830_3 on schedu create event e22830_4 on schedule every 1 hour do alter event e22830_4 on schedule every (select f22830() from dual) hour; select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; set global event_scheduler=on; ---sleep 2.0 +--sleep 2.4 set global event_scheduler=off; select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; drop function f22830; diff --git a/mysql-test/t/fix_priv_tables.test b/mysql-test/t/fix_priv_tables.test new file mode 100644 index 00000000000..3a91f41dfcc --- /dev/null +++ b/mysql-test/t/fix_priv_tables.test @@ -0,0 +1,66 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + +# +# This is the test for mysql_fix_privilege_tables +# It checks that a system tables from mysql 4.1.23 +# can be upgraded to current system table format +# +# Note: If this test fails, don't be confused about the errors reported +# by mysql-test-run This shows warnings generated by +# mysql_fix_system_tables which should be ignored. +# Instead, concentrate on the errors in r/system_mysql_db.reject + +--disable_warnings +drop table if exists t1,t1aa,t2aa; +--enable_warnings + +# +# Bug #20589 Missing some table level privileges after upgrade +# +# Add some grants that should survive the "upgrade" + +--disable_warnings +DROP DATABASE IF EXISTS testdb; +--enable_warnings +CREATE DATABASE testdb; +CREATE TABLE testdb.t1 ( + c1 INT, + c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); + +CREATE VIEW testdb.v1 AS + SELECT * FROM testdb.t1; + +GRANT CREATE VIEW, SHOW VIEW ON testdb.v1 TO 'show_view_tbl'@'localhost'; +SHOW GRANTS FOR 'show_view_tbl'@'localhost'; +echo; + +# Some extra GRANTS for more complete test +GRANT SELECT(c1) on testdb.v1 to 'select_only_c1'@localhost; +SHOW GRANTS FOR 'select_only_c1'@'localhost'; +echo; + +-- disable_result_log +-- disable_query_log + +# Run the mysql_fix_privilege_tables.sql using "mysql --force" +--exec $MYSQL --force mysql < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/fix_priv_tables.log 2>&1 + +-- enable_query_log +-- enable_result_log + +echo "after fix privs"; + +SHOW GRANTS FOR 'show_view_tbl'@'localhost'; +echo; +SHOW GRANTS FOR 'select_only_c1'@'localhost'; +echo; + +DROP USER 'show_view_tbl'@'localhost'; +DROP USER 'select_only_c1'@'localhost'; + +DROP VIEW testdb.v1; +DROP TABLE testdb.t1; +DROP DATABASE testdb; + +# End of 4.1 tests diff --git a/mysql-test/t/flush2-master.opt b/mysql-test/t/flush2-master.opt new file mode 100644 index 00000000000..ccbd01c91d3 --- /dev/null +++ b/mysql-test/t/flush2-master.opt @@ -0,0 +1 @@ +--disable-log-bin diff --git a/mysql-test/t/flush2.test b/mysql-test/t/flush2.test new file mode 100644 index 00000000000..fc9e88e3141 --- /dev/null +++ b/mysql-test/t/flush2.test @@ -0,0 +1,9 @@ +# +# Bug#17733 Flushing logs causes daily server crash +# +flush logs; +set global expire_logs_days = 3; +show variables like 'log%'; +flush logs; +show variables like 'log%'; +set global expire_logs_days = 0; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 52a5512d070..5cac6c45f65 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -125,4 +125,11 @@ drop table t2; drop table t1; set global query_cache_size=default; +# +# Bug #21466: INET_ATON() returns signed, not unsigned +# + +create table t1 select INET_ATON('255.255.0.1') as `a`; +show create table t1; +drop table t1; --echo End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index a6cbca61b84..ebaa6d84bd3 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -565,6 +565,12 @@ union union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H); +# +# Bug #23653: crash if last_day('0000-00-00') +# + +select last_day('0000-00-00'); + --echo End of 4.1 tests explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1, @@ -580,6 +586,8 @@ select time_format('100:00:00', '%H %k %h %I %l'); # Bug #12562: Make SYSDATE behave like it does in Oracle: always the current # time, regardless of magic to make NOW() always the same for the # entirety of a statement. +SET GLOBAL log_bin_trust_function_creators = 1; + create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); delimiter //; @@ -609,6 +617,8 @@ drop trigger t_before; drop function t_slow_sysdate; drop table t1; +SET GLOBAL log_bin_trust_function_creators = 0; + create table t1 (a datetime, i int, b datetime); insert into t1 select sysdate(), sleep(1), sysdate() from dual; select a != b from t1; @@ -676,6 +686,12 @@ DROP TABLE t1,t2; # Restore timezone to default set time_zone= @@global.time_zone; + +# +# Bug #22229: bug in DATE_ADD() +# + +select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; --echo End of 5.0 tests # diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 7bba34be3ff..4e5a21c6789 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -393,7 +393,7 @@ drop table t1; --disable_warnings drop procedure if exists fn3; --enable_warnings -create function fn3 () returns point return GeomFromText("point(1 1)"); +create function fn3 () returns point deterministic return GeomFromText("point(1 1)"); show create function fn3; select astext(fn3()); drop function fn3; diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 99da1fa1ee7..a533af995dc 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -484,6 +484,8 @@ flush privileges; # BUG#13310 incorrect user parsing by SP # +SET GLOBAL log_bin_trust_function_creators = 1; + grant all privileges on test.* to `a@`@localhost; grant execute on * to `a@`@localhost; connect (bug13310,localhost,'a@',,test); @@ -494,11 +496,10 @@ insert into t2 values (1); drop function if exists f2; --enable_warnings delimiter //; -create function f2 () returns int begin declare v int; select s1 from t2 -into v; return v; end// +create function f2 () returns int +begin declare v int; select s1 from t2 into v; return v; end// delimiter ;// select f2(); - drop function f2; drop table t2; disconnect bug13310; @@ -506,3 +507,5 @@ disconnect bug13310; connection default; REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; drop user `a@`@localhost; + +SET GLOBAL log_bin_trust_function_creators = 0; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 6cf26acd40d..6d98e4c4aa9 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -721,7 +721,7 @@ create temporary table schemata(f1 char(10)); # Bug #10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA # delimiter |; ---error 1044 +--error ER_BAD_DB_ERROR CREATE PROCEDURE p1 () BEGIN SELECT 'foo' FROM DUAL; diff --git a/mysql-test/t/init_connect.test b/mysql-test/t/init_connect.test index 31a98df33df..cf98f608982 100644 --- a/mysql-test/t/init_connect.test +++ b/mysql-test/t/init_connect.test @@ -232,7 +232,9 @@ connection con0; disconnect con1; drop trigger trg1; -set global init_connect=default; +# Set init connect back to the value provided in init_connect-master.opt +# doesn't matter as server will be restarted +set global init_connect="set @a='a\\0c'"; revoke all privileges, grant option from mysqltest1@localhost; drop user mysqltest1@localhost; diff --git a/mysql-test/t/innodb_notembedded.test b/mysql-test/t/innodb_notembedded.test index 53332d9fda4..2731f8e33ed 100644 --- a/mysql-test/t/innodb_notembedded.test +++ b/mysql-test/t/innodb_notembedded.test @@ -13,6 +13,7 @@ connect (b,localhost,root,,); # BUG#11238 - in prelocking mode SELECT .. FOR UPDATE is changed to # non-blocking SELECT # +SET GLOBAL log_bin_trust_function_creators = 1; create table t1 (col1 integer primary key, col2 integer) engine=innodb; insert t1 values (1,100); delimiter |; @@ -38,3 +39,4 @@ drop table t1; drop function f1; disconnect a; disconnect b; +SET GLOBAL log_bin_trust_function_creators = 0; diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index 4001e0df4af..3044964ebc3 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -164,16 +164,19 @@ create table t1 (mytext text, FULLTEXT (mytext)); insert t1 values ('aaabbb'); check table t1; +set @my_key_cache_block_size= @@global.key_cache_block_size; set GLOBAL key_cache_block_size=2048; check table t1; - drop table t1; +# Restore the changed variable value +set global key_cache_block_size= @my_key_cache_block_size; # # Bug #19079: corrupted index when key_cache_block_size is not multiple of # myisam_block_size CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY); +SET @my_key_cache_block_size= @@global.key_cache_block_size; SET GLOBAL key_cache_block_size=1536; INSERT INTO t1 VALUES (1); SELECT @@key_cache_block_size; @@ -206,6 +209,8 @@ SELECT COUNT(*) FROM t1; SELECT @@key_cache_block_size; CHECK TABLE t1; DROP TABLE t1,t2; +# Restore changed variables +set global key_cache_block_size= @my_key_cache_block_size; # # Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index 2eb4e6cbbb2..bc599b20d36 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -69,5 +69,5 @@ explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; - +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 883315994fe..414027cb485 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -21,6 +21,7 @@ create database D1; disconnect sample; connection master; +drop user 'sample'@'localhost'; drop database if exists d1; disconnect master; connection default; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index cb4a5593de7..c9fec6deb35 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -241,4 +241,23 @@ drop table t17583; --error 1 --exec $MYSQL test -e "\r test cyrils_superlonghostnameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" 2>&1 + +# +# Bug #21412: mysql cmdline client allows backslash(es) +# as delimiter but can't recognize them +# + +# This should work just fine... +--exec echo "DELIMITER /" > $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec echo "SELECT 1/" >> $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1 + +# This should give an error... +--exec echo "DELIMITER \\" > $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1 + +# As should this... +--exec echo "DELIMITER \\\\" > $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1 + --echo End of 5.0 tests diff --git a/mysql-test/t/mysql_client_test-master.opt b/mysql-test/t/mysql_client_test-master.opt index 3711946168d..2dfcc4a2fb9 100644 --- a/mysql-test/t/mysql_client_test-master.opt +++ b/mysql-test/t/mysql_client_test-master.opt @@ -1 +1 @@ ---log --log-output=FILE +--log=$MYSQLTEST_VARDIR/log/master.log --log-output=FILE diff --git a/mysql-test/t/mysql_delimiter.sql b/mysql-test/t/mysql_delimiter.sql index fa80c980b29..db679c3b06b 100644 --- a/mysql-test/t/mysql_delimiter.sql +++ b/mysql-test/t/mysql_delimiter.sql @@ -49,3 +49,13 @@ delimiter ; # Reset delimiter # Bug #11523: \d works differently than delimiter # source t/mysql_delimiter_source.sql +delimiter ; # Reset delimiter + +# +# Bug #19799: delimiter command not working correctly when sourcing a sql file +# with Windows style line endings. +# +source t/mysql_delimiter_19799.sql +use test// +show tables// +delimiter ; # Reset delimiter diff --git a/mysql-test/t/mysql_delimiter_19799.sql b/mysql-test/t/mysql_delimiter_19799.sql new file mode 100755 index 00000000000..2a3d4378492 --- /dev/null +++ b/mysql-test/t/mysql_delimiter_19799.sql @@ -0,0 +1 @@ +delimiter // diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test new file mode 100644 index 00000000000..4200ba844f7 --- /dev/null +++ b/mysql-test/t/mysql_upgrade.test @@ -0,0 +1,20 @@ +# Only run test if "mysql_upgrade" is found +--require r/have_mysql_upgrade.result +--disable_query_log +select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade; +--enable_query_log + +# +# Basic test thta we can run mysql_upgrde and that it finds the +# expected binaries it uses. +# +--echo Run mysql_upgrade once +--exec $MYSQL_UPGRADE 2> $MYSQLTEST_VARDIR/log/mysql_upgrade.err + +--echo Run it again - should say already completed +--exec $MYSQL_UPGRADE 2> $MYSQLTEST_VARDIR/log/mysql_upgrade.err + +--echo Force should run it regardless of wheter it's been run before +--exec $MYSQL_UPGRADE --force 2> $MYSQLTEST_VARDIR/log/mysql_upgrade.err + + diff --git a/mysql-test/t/mysqladmin.test b/mysql-test/t/mysqladmin.test new file mode 100644 index 00000000000..7c016fd7416 --- /dev/null +++ b/mysql-test/t/mysqladmin.test @@ -0,0 +1,32 @@ +# +# Test "mysqladmin ping" +# + +--exec $MYSQLADMIN --no-defaults -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 + + +# +# Bug#10608 mysqladmin breaks on "database" variable in my.cnf +# + +# When mysqladmin finds database in .cnf file it shall fail +--write_file $MYSQLTEST_VARDIR/tmp/bug10608.cnf +[client] +database=db1 +EOF + +--replace_regex /\/.*mysqladmin/mysqladmin/ +--error 7 +--exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 + + +# When mysqladmin finds "loose-database" in .cnf file it shall print +# a warning and continue +--write_file $MYSQLTEST_VARDIR/tmp/bug10608.cnf +[client] +loose-database=db2 +EOF + +--replace_regex /Warning: .*mysqladmin/Warning: mysqladmin/ +--exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 + diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 05b15cdad17..b7ff8bbdfdb 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1343,7 +1343,7 @@ connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; create database mysqldump_myDB; use mysqldump_myDB; -create user myDB_User; +create user myDB_User@localhost; grant create, create view, select, insert on mysqldump_myDB.* to myDB_User@localhost; create table t1 (c1 int); insert into t1 values (3); @@ -1366,7 +1366,7 @@ drop view v1; drop table t1; drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; -drop user myDB_User; +drop user myDB_User@localhost; drop database mysqldump_myDB; flush privileges; @@ -1391,7 +1391,7 @@ drop view v1; drop table t1; drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; -drop user myDB_User; +drop user myDB_User@localhost; drop database mysqldump_myDB; use test; connection default; diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 3c20b38722f..c06d51d9d49 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -1522,6 +1522,46 @@ remove_file $MYSQLTEST_VARDIR/tmp/file2.tmp; --exec echo "copy_file from_file;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- +# test for chmod +# ---------------------------------------------------------------------------- +--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +file1 +EOF + +chmod 0000 $MYSQLTEST_VARDIR/tmp/file1.tmp; +# The below write fails, but --error is not implemented +# for write_file +#--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +#test should fail +#EOF + +chmod 0777 $MYSQLTEST_VARDIR/tmp/file1.tmp; +--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +test2 +EOF + +remove_file $MYSQLTEST_VARDIR/tmp/file1.tmp; + +--error 1 +--exec echo "chmod ;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod 0 from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod 08 from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod ABZD from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod 06789 from_file;" | $MYSQL_TEST 2>&1 + + +# ---------------------------------------------------------------------------- # test for perl # ---------------------------------------------------------------------------- --perl diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test index 39c8d8e2bf4..13b4338701e 100644 --- a/mysql-test/t/parser.test +++ b/mysql-test/t/parser.test @@ -493,3 +493,113 @@ select yearweek(); -- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT select yearweek(1, 2, 3); +# +# Bug#24736: UDF functions parsed as Stored Functions +# + +# Verify that the syntax for calling UDF : foo(expr AS param, ...) +# can not be used when calling native functions + +# Native function with 1 argument + +select abs(3); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 AS three); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 three); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 AS "three"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 "three"); + +# Native function with 2 arguments + +set @bar="bar"; +set @foobar="foobar"; + +select instr("foobar", "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" AS p1, "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" p1, "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" AS "p1", "bar"); +## String concatenation, valid syntax +select instr("foobar" "p1", "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr(@foobar "p1", "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", "bar" AS p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", "bar" p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", "bar" AS "p2"); +## String concatenation, valid syntax +select instr("foobar", "bar" "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", @bar "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" AS p1, "bar" AS p2); + +# Native function with 3 arguments + +select conv(255, 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 AS p1, 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 p1, 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 AS "p1", 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 "p1", 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 AS p2, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 p2, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 AS "p2", 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 "p2", 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 AS p3); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 p3); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 AS "p3"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 "p3"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 AS p1, 10 AS p2, 16 AS p3); + +# Native function with a variable number of arguments + +select atan(10); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS p1); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 p1); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS "p1"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 "p1"); + +select atan(10, 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS p1, 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 p1, 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS "p1", 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 "p1", 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 AS p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 AS "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS p1, 20 AS p2); + diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 0e96a7aad10..15b0a476279 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1587,8 +1587,8 @@ call proc_1(); call proc_1(); delimiter |; --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG -create function func_1() returns int begin reset query cache; return 1; end| -create function func_1() returns int begin call proc_1(); return 1; end| +create function func_1() returns int deterministic begin reset query cache; return 1; end| +create function func_1() returns int deterministic begin call proc_1(); return 1; end| delimiter ;| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG select func_1(), func_1(), func_1() from dual; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index cb4e5f97c3a..ad7fd90ed63 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -788,7 +788,9 @@ create procedure `p1`() begin select a, f1() from t1; end// +SET GLOBAL log_bin_trust_function_creators = 1; call p1()// +SET GLOBAL log_bin_trust_function_creators = 0; drop procedure p1// drop function f1// diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test index 97be9f9f7ca..802022d061b 100644 --- a/mysql-test/t/query_cache_notembedded.test +++ b/mysql-test/t/query_cache_notembedded.test @@ -183,6 +183,8 @@ drop table t1; # # bug#14767: INSERT in SF + concurrent SELECT with query cache # +SET GLOBAL log_bin_trust_function_creators = 1; + reset query cache; --disable_warnings drop function if exists f1; @@ -222,3 +224,4 @@ disconnect con2; connection default; set GLOBAL query_cache_size=0; +SET GLOBAL log_bin_trust_function_creators = 0; diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 175a5bba6fa..8e14b310f4c 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -101,6 +101,18 @@ drop table t1; --error 1290 insert into t1 values(1); +# +# BUG #22077 "DROP TEMPORARY TABLE fails with wrong error if read_only is set" +# +# check if DROP TEMPORARY on a non-existing temporary table returns the right +# error + +--error ER_BAD_TABLE_ERROR +drop temporary table ttt; + +# check if DROP TEMPORARY TABLE IF EXISTS produces a warning with read_only set +drop temporary table if exists ttt; + connection default; drop table t1,t2; drop user test@localhost; diff --git a/mysql-test/t/rpl_000015.test b/mysql-test/t/rpl_000015.test index cf93beb2bfe..ffe7ab87632 100644 --- a/mysql-test/t/rpl_000015.test +++ b/mysql-test/t/rpl_000015.test @@ -18,7 +18,7 @@ show slave status; change master to master_host='127.0.0.1'; # The following needs to be cleaned up when change master is fixed ---replace_result $MYSQL_TCP_PORT MASTER_PORT +--replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # show slave status; --replace_result $MASTER_MYPORT MASTER_PORT diff --git a/mysql-test/t/rpl_drop_db.test b/mysql-test/t/rpl_drop_db.test index ffdc605b402..7f4a7843c4a 100644 --- a/mysql-test/t/rpl_drop_db.test +++ b/mysql-test/t/rpl_drop_db.test @@ -46,17 +46,16 @@ show tables; use test; select * from t1; -system rm $MYSQLTEST_VARDIR/master-data/mysqltest1/f1.txt; -connection master; -DROP DATABASE mysqltest1; -sync_slave_with_master; - #cleanup connection slave; stop slave; -#system rm -rf $MYSQLTEST_VARDIR/master-data/mysqltest1; +drop database mysqltest1; connection master; +# Remove the "extra" file created above +--remove_file $MYSQLTEST_VARDIR/master-data/mysqltest1/f1.txt +drop database mysqltest1; + use test; drop table t1; diff --git a/mysql-test/t/rpl_ignore_revoke.test b/mysql-test/t/rpl_ignore_revoke.test index cdeb40df069..00171605a92 100644 --- a/mysql-test/t/rpl_ignore_revoke.test +++ b/mysql-test/t/rpl_ignore_revoke.test @@ -45,3 +45,7 @@ revoke select on *.* FROM 'user_foo'; connection master; delete from mysql.user where user="user_foo"; sync_slave_with_master; + +# Since changes to mysql.* are ignored, the revoke need to +# be done on slave as well +delete from mysql.user where user="user_foo"; diff --git a/mysql-test/t/rpl_init_slave.test b/mysql-test/t/rpl_init_slave.test index cefb04a7b75..139b4902e12 100644 --- a/mysql-test/t/rpl_init_slave.test +++ b/mysql-test/t/rpl_init_slave.test @@ -17,6 +17,8 @@ show variables like 'max_connections'; save_master_pos; connection slave; sync_with_master; +# Save variable value +set @my_global_init_connect= @@global.init_connect; set global init_connect="set @c=1"; show variables like 'init_connect'; connection master; @@ -25,4 +27,8 @@ connection slave; sync_with_master; stop slave; +# Restore changed global variable +set global init_connect= @my_global_init_connect; +set global max_connections= default; + # End of 4.1 tests diff --git a/mysql-test/t/rpl_sf.test b/mysql-test/t/rpl_sf.test new file mode 100644 index 00000000000..2b1e0350f1b --- /dev/null +++ b/mysql-test/t/rpl_sf.test @@ -0,0 +1,68 @@ +# Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR + +# save status + +let $oblf=`select @@SESSION.BINLOG_FORMAT`; +let $otfc=`select @@log_bin_trust_function_creators`; + +set global log_bin_trust_function_creators=0; + + + +# fail *on definition* + +set binlog_format=STATEMENT; + +delimiter |; +--error ER_BINLOG_UNSAFE_ROUTINE +create function fn16456() + returns int +begin + return unix_timestamp(); +end| +delimiter ;| + + + +# force in definition, so we can see whether we fail on call + +set global log_bin_trust_function_creators=1; + +delimiter |; +create function fn16456() + returns int +begin + return unix_timestamp(); +end| +delimiter ;| + +set global log_bin_trust_function_creators=0; + + + +# allow funcall in RBR + +set binlog_format=ROW; + +--replace_column 1 timestamp +select fn16456(); + + + +# fail funcall in SBR + +set binlog_format=STATEMENT; + +--error ER_BINLOG_ROW_RBR_TO_SBR +select fn16456(); + + + +# restore status + +drop function fn16456; + +--disable_query_log +eval set binlog_format=$oblf; +eval set global log_bin_trust_function_creators=$otfc; +--enable_query_log diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test index 22ab72df104..3a93a6608cd 100644 --- a/mysql-test/t/rpl_sp.test +++ b/mysql-test/t/rpl_sp.test @@ -8,6 +8,7 @@ # still accepted (this test also checks that the new name is # accepted). The old name could be removed in 5.1 or 6.0. +source include/have_binlog_format_mixed.inc; source include/master-slave.inc; # we need a db != test, where we don't have automatic grants @@ -319,7 +320,9 @@ select * from t2; # ********************** PART 3 : TRIGGERS *************** connection con1; ---error 1227 +# now fails due to missing trigger grant (err 1142 i/o 1227) due to new +# check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01 --azundris +--error ER_TABLEACCESS_DENIED_ERROR create trigger trg before insert on t1 for each row set new.a= 10; connection master; @@ -519,6 +522,33 @@ connection master; drop table t1; sync_slave_with_master; +# Restore log_bin_trust_function_creators to original value +set global log_bin_trust_function_creators=0; +connection master; +set global log_bin_trust_function_creators=0; --echo End of 5.0 tests + +# +# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS" +# +connection master; +reset master; +--disable_warnings +drop database if exists mysqltest; +drop database if exists mysqltest2; +--enable_warnings +create database mysqltest; +create database mysqltest2; +use mysqltest2; +create table t ( t integer ); +create procedure mysqltest.test() begin end; +insert into t values ( 1 ); +show binlog events in 'master-bin.000001' from 102; +--error ER_BAD_DB_ERROR +create procedure `\\`.test() begin end; +# Clean up +drop database mysqltest; +drop database mysqltest2; + --echo End of 5.1 tests diff --git a/mysql-test/t/rpl_sp_effects.test b/mysql-test/t/rpl_sp_effects.test index e1746682b76..027bfd69f36 100644 --- a/mysql-test/t/rpl_sp_effects.test +++ b/mysql-test/t/rpl_sp_effects.test @@ -20,6 +20,8 @@ drop view if exists v1; --enable_warnings create table t1 (a int); +SET GLOBAL log_bin_trust_function_creators = 1; + # 1. Test simple variables use. delimiter //; create procedure p1() @@ -205,3 +207,5 @@ drop function f1; drop function f2; drop procedure p1; sync_slave_with_master; + +SET GLOBAL log_bin_trust_function_creators = 0; diff --git a/mysql-test/t/rpl_timezone.test b/mysql-test/t/rpl_timezone.test index 5a6c1cd06f8..4b8c8152c82 100644 --- a/mysql-test/t/rpl_timezone.test +++ b/mysql-test/t/rpl_timezone.test @@ -17,6 +17,9 @@ source include/master-slave.inc; +# Save original timezone +set @my_time_zone= @@global.time_zone; + # Some preparations let $VERSION=`select version()`; set timestamp=100000000; # for fixed output of mysqlbinlog @@ -133,3 +136,7 @@ connection master; drop table t1, t2; sync_slave_with_master; + +# Restore original timezone +connection master; +set global time_zone= @my_time_zone; diff --git a/mysql-test/t/rpl_variables.test b/mysql-test/t/rpl_variables.test index b1744c57c9b..84dc3acfe6e 100644 --- a/mysql-test/t/rpl_variables.test +++ b/mysql-test/t/rpl_variables.test @@ -1,5 +1,8 @@ source include/master-slave.inc; +# Init for rstore of variable values +set @my_slave_net_timeout =@@global.slave_net_timeout; + set global slave_net_timeout=100; set global sql_slave_skip_counter=100; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 8f4763c636a..ba1180ffaff 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -359,7 +359,7 @@ end| --disable_warnings drop procedure if exists sub3| --enable_warnings -create function sub3(i int) returns int +create function sub3(i int) returns int deterministic return i+1| call sub1("sub1a", (select 7))| @@ -6707,6 +6707,83 @@ delimiter |; --echo End of 5.1 tests # +# BUG#23760: ROW_COUNT() and store procedure not owrking together +# +--disable_warnings +DROP TABLE IF EXISTS bug23760| +DROP TABLE IF EXISTS bug23760_log| +DROP PROCEDURE IF EXISTS bug23760_update_log| +DROP PROCEDURE IF EXISTS bug23760_test_row_count| +DROP FUNCTION IF EXISTS bug23760_rc_test| +--enable_warnings +CREATE TABLE bug23760 ( + id INT NOT NULL AUTO_INCREMENT , + num INT NOT NULL , + PRIMARY KEY ( id ) +)| + +CREATE TABLE bug23760_log ( + id INT NOT NULL AUTO_INCREMENT , + reason VARCHAR(50)NULL , + ammount INT NOT NULL , + PRIMARY KEY ( id ) +)| + +CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT) +BEGIN + INSERT INTO bug23760_log (reason, ammount) VALUES(r, a); +END| + +CREATE PROCEDURE bug23760_test_row_count() +BEGIN + UPDATE bug23760 SET num = num + 1; + CALL bug23760_update_log('Test is working', ROW_COUNT()); + UPDATE bug23760 SET num = num - 1; +END| + + +CREATE PROCEDURE bug23760_test_row_count2(level INT) +BEGIN + IF level THEN + UPDATE bug23760 SET num = num + 1; + CALL bug23760_update_log('Test2 is working', ROW_COUNT()); + CALL bug23760_test_row_count2(level - 1); + END IF; +END| + +CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var| + +INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)| +SELECT ROW_COUNT()| + +CALL bug23760_test_row_count()| +SELECT * FROM bug23760_log ORDER BY id| + +SET @save_max_sp_recursion= @@max_sp_recursion_depth| +SELECT @save_max_sp_recursion| +SET max_sp_recursion_depth= 5| +SELECT @@max_sp_recursion_depth| +CALL bug23760_test_row_count2(2)| +SELECT ROW_COUNT()| +SELECT * FROM bug23760_log ORDER BY id| +SELECT * FROM bug23760 ORDER by ID| +SET max_sp_recursion_depth= @save_max_sp_recursion| + +SELECT bug23760_rc_test(123)| +INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)| +SELECT bug23760_rc_test(ROW_COUNT())| + +DROP TABLE bug23760, bug23760_log| +DROP PROCEDURE bug23760_update_log| +DROP PROCEDURE bug23760_test_row_count| +DROP FUNCTION bug23760_rc_test| + +# +# NOTE: The delimiter is `|`, and not `;`. It is changed to `;` +# at the end of the file! +# + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/system_mysql_db_fix-master.opt b/mysql-test/t/system_mysql_db_fix30020-master.opt index 7e4fa9a3ee8..7e4fa9a3ee8 100644 --- a/mysql-test/t/system_mysql_db_fix-master.opt +++ b/mysql-test/t/system_mysql_db_fix30020-master.opt diff --git a/mysql-test/t/system_mysql_db_fix.test b/mysql-test/t/system_mysql_db_fix30020.test index cccf1d5767b..6188aebb9f5 100644 --- a/mysql-test/t/system_mysql_db_fix.test +++ b/mysql-test/t/system_mysql_db_fix30020.test @@ -10,9 +10,11 @@ # # This is the test for mysql_fix_privilege_tables +# It checks that a system tables from mysql 3.20 +# can be upgraded to current system table format # # Note: If this test fails, don't be confused about the errors reported -# by mysql-test-run; This shows warnings from generated by +# by mysql-test-run This shows warnings generated by # mysql_fix_system_tables which should be ignored. # Instead, concentrate on the errors in r/system_mysql_db.reject @@ -85,7 +87,7 @@ INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y',' INSERT INTO user VALUES ('localhost','', '','N','N','N','N','N','N','N','N','N'); # Call the "shell script" $MYSQL_FIX_SYSTEM_TABLES using system --- system $MYSQL_FIX_SYSTEM_TABLES --database=test > /dev/null +-- system $MYSQL_FIX_SYSTEM_TABLES --database=test > $MYSQLTEST_VARDIR/log/system_mysql_db_fix30020.log 2>&1 -- enable_query_log -- enable_result_log diff --git a/mysql-test/t/system_mysql_db_fix40123-master.opt b/mysql-test/t/system_mysql_db_fix40123-master.opt new file mode 100644 index 00000000000..7e4fa9a3ee8 --- /dev/null +++ b/mysql-test/t/system_mysql_db_fix40123-master.opt @@ -0,0 +1 @@ +--result-file=system_mysql_db diff --git a/mysql-test/t/system_mysql_db_fix40123.test b/mysql-test/t/system_mysql_db_fix40123.test new file mode 100644 index 00000000000..2d4fd1e920f --- /dev/null +++ b/mysql-test/t/system_mysql_db_fix40123.test @@ -0,0 +1,79 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + +# +# This is the test for mysql_fix_privilege_tables +# It checks that a system tables from mysql 4.1.23 +# can be upgraded to current system table format +# +# Note: If this test fails, don't be confused about the errors reported +# by mysql-test-run This shows warnings generated by +# mysql_fix_system_tables which should be ignored. +# Instead, concentrate on the errors in r/system_mysql_db.reject + +--disable_warnings +drop table if exists t1,t1aa,t2aa; +--enable_warnings + +-- disable_result_log +-- disable_query_log + +use test; + +# create system tables as in mysql-4.1.23 +# created by executing "./mysql_create_system_tables real ." + +set table_type=myisam; +CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); +INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); + +CREATE TABLE host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; + + +CREATE TABLE user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); + INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); + +CREATE TABLE func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; + + +CREATE TABLE tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; +CREATE TABLE columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp(14), Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; + +CREATE TABLE help_topic ( help_topic_id int unsigned not null, name varchar(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url varchar(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; +CREATE TABLE help_category ( help_category_id smallint unsigned not null, name varchar(64) not null, parent_category_id smallint unsigned null, url varchar(128) not null, primary key (help_category_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help categories'; +CREATE TABLE help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; +CREATE TABLE help_keyword ( help_keyword_id int unsigned not null, name varchar(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; + +CREATE TABLE time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; + +CREATE TABLE time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; + +CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; + +CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; + +CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; + + +# Run the mysql_fix_privilege_tables.sql using "mysql --force" +--exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/system_mysql_db_fix40123.log 2>&1 + +-- enable_query_log +-- enable_result_log + +# Dump the tables that should be compared +-- source include/system_db_struct.inc + +-- disable_query_log + +# Drop all tables created by this test +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, binlog_index; + +-- enable_query_log + +# check that we dropped all system tables +show tables; + +# End of 4.1 tests diff --git a/mysql-test/t/system_mysql_db_fix50030-master.opt b/mysql-test/t/system_mysql_db_fix50030-master.opt new file mode 100644 index 00000000000..7e4fa9a3ee8 --- /dev/null +++ b/mysql-test/t/system_mysql_db_fix50030-master.opt @@ -0,0 +1 @@ +--result-file=system_mysql_db diff --git a/mysql-test/t/system_mysql_db_fix50030.test b/mysql-test/t/system_mysql_db_fix50030.test new file mode 100644 index 00000000000..78ecf1e573c --- /dev/null +++ b/mysql-test/t/system_mysql_db_fix50030.test @@ -0,0 +1,82 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + +# +# This is the test for mysql_fix_privilege_tables +# It checks that a system tables from mysql 5.0.30 +# can be upgraded to current system table format +# +# Note: If this test fails, don't be confused about the errors reported +# by mysql-test-run This shows warnings generated by +# mysql_fix_system_tables which should be ignored. +# Instead, concentrate on the errors in r/system_mysql_db.reject + +--disable_warnings +drop table if exists t1,t1aa,t2aa; +--enable_warnings + +-- disable_result_log +-- disable_query_log + +use test; + +# create system tables as in mysql-5.0.30 +# created by executing "./mysql_create_system_tables real ." + +set table_type=myisam; +CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); +INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); + +CREATE TABLE host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; + +CREATE TABLE user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); +INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0, 0); + +CREATE TABLE func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; + +CREATE TABLE tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; + +CREATE TABLE columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp(14), Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; + +CREATE TABLE help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url char(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; +CREATE TABLE help_category ( help_category_id smallint unsigned not null, name char(64) not null, parent_category_id smallint unsigned null, url char(128) not null, primary key (help_category_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help categories'; +CREATE TABLE help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; +CREATE TABLE help_keyword ( help_keyword_id int unsigned not null, name char(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; + +CREATE TABLE time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; + +CREATE TABLE time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; + +CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; + +CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; + +CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; + +CREATE TABLE proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob DEFAULT '' NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob DEFAULT '' NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; + +CREATE TABLE procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; + + +# Run the mysql_fix_privilege_tables.sql using "mysql --force" +--exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/system_mysql_db_fix50030.log 2>&1 + +-- enable_query_log +-- enable_result_log + +# Dump the tables that should be compared +-- source include/system_db_struct.inc + +-- disable_query_log + +# Drop all tables created by this test +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, binlog_index; + +-- enable_query_log + +# check that we dropped all system tables +show tables; + +# End of 4.1 tests diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test index 862b9cc58d1..5e1f74d388f 100644 --- a/mysql-test/t/timezone2.test +++ b/mysql-test/t/timezone2.test @@ -228,6 +228,8 @@ drop table t1; # Test for bug #11081 "Using a CONVERT_TZ function in a stored function # or trigger fails". # +SET GLOBAL log_bin_trust_function_creators = 1; + create table t1 (ldt datetime, udt datetime); create function f1(i datetime) returns datetime return convert_tz(i, 'UTC', 'Europe/Moscow'); @@ -241,4 +243,6 @@ select ldt, f1(udt) as ldt2 from t1; drop table t1; drop function f1; +SET GLOBAL log_bin_trust_function_creators = 0; + # End of 5.0 tests diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 01aac24ded4..b0580faea3d 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -149,9 +149,9 @@ EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; SELECT a,c FROM v1; ---error ER_PARSE_ERROR +--error ER_WRONG_PARAMETERS_TO_STORED_FCT SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; ---error ER_PARSE_ERROR +--error ER_WRONG_PARAMETERS_TO_STORED_FCT SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; --error ER_PARSE_ERROR SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; @@ -174,6 +174,22 @@ DROP FUNCTION fn; --echo End of 5.0 tests. # +# Bug#24736: UDF functions parsed as Stored Functions +# + +select myfunc_double(3); +select myfunc_double(3 AS three); +select myfunc_double(abs(3)); +select myfunc_double(abs(3) AS named_param); +select abs(myfunc_double(3)); +select abs(myfunc_double(3 AS three)); + +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select myfunc_double(abs(3 AS wrong)); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(myfunc_double(3) AS wrong); + +# # BUG#18239: Possible to overload internal functions with stored functions # @@ -223,4 +239,41 @@ DROP FUNCTION lookup; DROP FUNCTION reverse_lookup; DROP FUNCTION avgcost; - +# +# Bug#18761: constant expression as UDF parameters not passed in as constant +# +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; + +select + is_const(3) as const, + is_const(3.14) as const, + is_const('fnord') as const, + is_const(2+3) as const, + is_const(rand()) as 'nc rand()', + is_const(sin(3.14)) as const, + is_const(upper('test')) as const; + +create table bug18761 (n int); +insert into bug18761 values (null),(2); +select + is_const(3) as const, + is_const(3.14) as const, + is_const('fnord') as const, + is_const(2+3) as const, + is_const(2+n) as 'nc 2+n ', + is_const(sin(n)) as 'nc sin(n)', + is_const(sin(3.14)) as const, + is_const(upper('test')) as const, + is_const(rand()) as 'nc rand()', + is_const(n) as 'nc n ', + is_const(is_const(n)) as 'nc ic?(n)', + is_const(is_const('c')) as const +from + bug18761; +drop table bug18761; + +--error 1241 +select is_const((1,2,3)); + +drop function if exists is_const; diff --git a/mysql-test/t/varbinary.test b/mysql-test/t/varbinary.test index 5fbd116d7b8..0e45bfb5e1b 100644 --- a/mysql-test/t/varbinary.test +++ b/mysql-test/t/varbinary.test @@ -37,3 +37,46 @@ select x,xx from t1; drop table t1; # End of 4.1 tests + +# +# Bug #19371 VARBINARY() have trailing zeros after upgrade from 4.1 +# + +# Test with a saved table from 4.1 +copy_file std_data/bug19371.frm $MYSQLTEST_VARDIR/master-data/test/t1.frm; +chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.frm; +copy_file std_data/bug19371.MYD $MYSQLTEST_VARDIR/master-data/test/t1.MYD; +chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.MYD; +copy_file std_data/bug19371.MYI $MYSQLTEST_VARDIR/master-data/test/t1.MYI; +chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.MYI; + +# Everything _looks_ fine +show create table t1; + +# But the length of the varbinary columns are too long +select length(a), length(b) from t1; + +# Run CHECK TABLE, it should indicate table need a REPAIR TABLE +CHECK TABLE t1 FOR UPGRADE; + +# Run REPAIR TABLE to alter the table and repair +# the varbinary fields +REPAIR TABLE t1; + +# Now check it's back to normal +show create table t1; +select length(a), length(b) from t1; +insert into t1 values("ccc", "ddd"); +select length(a), length(b) from t1; +select hex(a), hex(b) from t1; +select concat("'", a, "'"), concat("'", b, "'") from t1; + +drop table t1; + +# Check that the fix does not affect table created with current version +create table t1(a varbinary(255)); +insert into t1 values("aaa "); +select length(a) from t1; +alter table t1 modify a varchar(255); +select length(a) from t1; + diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 5c920a2fc4c..9c06e79e2ca 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -733,13 +733,13 @@ CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; -GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly; -GRANT SELECT ON mysqltest1.v_ts TO readonly; -GRANT INSERT ON mysqltest1.v_ti TO readonly; -GRANT UPDATE ON mysqltest1.v_tu TO readonly; -GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly; -GRANT DELETE ON mysqltest1.v_td TO readonly; -GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly; +GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost; +GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost; +GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost; +GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost; +GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost; +GRANT DELETE ON mysqltest1.v_td TO readonly@localhost; +GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost; CONNECT (n1,localhost,readonly,,); CONNECTION n1; @@ -797,7 +797,7 @@ DROP VIEW mysqltest1.v_ti; DROP VIEW mysqltest1.v_ts; DROP VIEW mysqltest1.v_t1; DROP TABLE mysqltest1.t1; -DROP USER readonly@localhost; +DROP USER readonly@localhost; DROP DATABASE mysqltest1; # |