diff options
Diffstat (limited to 'mysql-test/r')
40 files changed, 1061 insertions, 94 deletions
diff --git a/mysql-test/r/almost_full.result b/mysql-test/r/almost_full.result new file mode 100644 index 00000000000..eb28f12fa51 --- /dev/null +++ b/mysql-test/r/almost_full.result @@ -0,0 +1,29 @@ +drop table if exists t1; +set global myisam_data_pointer_size=2; +CREATE TABLE t1 (a int auto_increment primary key not null, b longtext) ENGINE=MyISAM; +DELETE FROM t1 WHERE a=1 or a=5; +INSERT INTO t1 SET b=repeat('a',600); +ERROR HY000: The table 't1' is full +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +UPDATE t1 SET b=repeat('a', 800) where a=10; +ERROR HY000: The table 't1' is full +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +INSERT INTO t1 SET b=repeat('a',400); +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +DELETE FROM t1 WHERE a=2 or a=6; +UPDATE t1 SET b=repeat('a', 600) where a=11; +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +drop table t1; +set global myisam_data_pointer_size=default; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index a0a8455b496..59b1bfa183d 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -811,6 +811,12 @@ quote(name) ???????? ???????????????? drop table bug20536; +CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci); +INSERT INTO t1 VALUES('abcd'); +SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE); +a +abcd +DROP TABLE t1; End of 4.1 tests CREATE TABLE t1 (a varchar(64) character set ucs2, b decimal(10,3)); INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0); diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index 1cbc37cb5b5..83609856393 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -610,7 +610,6 @@ id ev_nm ev_cnt 6 ev_sched_1823 6 DROP TABLE event_log; SET GLOBAL event_scheduler = OFF; -DROP DATABASE events_test; SET GLOBAL event_scheduler= ON; CREATE EVENT bug28641 ON SCHEDULE AT '2038.01.18 03:00:00' DO BEGIN @@ -618,3 +617,105 @@ SELECT 1; END;| SET GLOBAL event_scheduler= OFF; DROP EVENT bug28641; + +##################################################################### +# +# BUG#31111: --read-only crashes MySQL (events fail to load). +# +##################################################################### + +DROP USER mysqltest_u1@localhost; +DROP EVENT IF EXISTS e1; +DROP EVENT IF EXISTS e2; + +GRANT EVENT ON *.* TO mysqltest_u1@localhost; + +SET GLOBAL READ_ONLY = 1; + +# +# Connection: u1_con (mysqltest_u1@localhost/events_test). +# + +CREATE EVENT e1 ON SCHEDULE AT '2020-01-01 00:00:00' DO SET @a = 1; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement + +ALTER EVENT e1 COMMENT 'comment'; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement + +DROP EVENT e1; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement + +# +# Connection: root_con (root@localhost/events_test). +# + +CREATE EVENT e1 ON SCHEDULE AT '2020-01-01 00:00:00' DO SET @a = 1; + +ALTER EVENT e1 COMMENT 'comment'; + +DROP EVENT e1; + +SET GLOBAL READ_ONLY = 0; + +# +# Connection: u1_con (mysqltest_u1@localhost/test). +# + +CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND DO SET @a = 1; +CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO SET @a = 1; + +SELECT +event_name, +last_executed IS NULL, +definer +FROM INFORMATION_SCHEMA.EVENTS +WHERE event_schema = 'events_test'; +event_name last_executed IS NULL definer +e1 1 mysqltest_u1@localhost +e2 1 mysqltest_u1@localhost + +# +# Connection: root_con (root@localhost/events_test). +# + +SET GLOBAL READ_ONLY = 1; + +SET GLOBAL EVENT_SCHEDULER = ON; + +# Waiting for the event scheduler to execute and drop event e1... + +# Waiting for the event scheduler to execute and update event e2... + +SET GLOBAL EVENT_SCHEDULER = OFF; + +SELECT +event_name, +last_executed IS NULL, +definer +FROM INFORMATION_SCHEMA.EVENTS +WHERE event_schema = 'events_test'; +event_name last_executed IS NULL definer +e2 0 mysqltest_u1@localhost + +DROP EVENT e1; +ERROR HY000: Unknown event 'e1' + +# Cleanup. + +DROP EVENT e2; + +SET GLOBAL READ_ONLY = 0; + +# +# Connection: default +# + +DROP USER mysqltest_u1@localhost; + +##################################################################### +# +# End of BUG#31111. +# +##################################################################### + +DROP DATABASE events_test; diff --git a/mysql-test/r/federated_server.result b/mysql-test/r/federated_server.result index 0905aabb075..32717b4f0e3 100644 --- a/mysql-test/r/federated_server.result +++ b/mysql-test/r/federated_server.result @@ -253,6 +253,14 @@ drop user guest_usage@localhost; drop user guest_select@localhost; drop table federated.t1; drop server 's1'; +create server 's1' foreign data wrapper 'mysql' options (port 3306); +alter server 's1' options +(host 'localhost', database '', user '', +password '', socket '', owner '', port 3306); +alter server 's1' options +(host 'localhost', database 'database1', user '', +password '', socket '', owner '', port 3306); +drop server 's1'; # End of 5.1 tests use test; create procedure p1 () @@ -262,7 +270,7 @@ DECLARE e INT DEFAULT 0; DECLARE i INT; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e = e + 1; SET i = sleep(5); -WHILE v < 20000 do +WHILE v < 10000 do CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test'); diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 96ebb9bf254..c3fa427127b 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -476,6 +476,12 @@ ALTER TABLE t1 DISABLE KEYS; SELECT * FROM t1 WHERE MATCH(a) AGAINST('test'); ERROR HY000: Can't find FULLTEXT index matching the column list DROP TABLE t1; +CREATE TABLE t1(a TEXT); +INSERT INTO t1 VALUES(' aaaaa aaaa'); +SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE); +a + aaaaa aaaa +DROP TABLE t1; CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)); INSERT INTO t1 VALUES('Offside'),('City Of God'); SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE); diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 4e25ada43a0..4d402d7d0e9 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -529,7 +529,7 @@ Db char(64) NO PRI User char(16) NO PRI Table_name char(64) NO PRI Grantor char(77) NO MUL -Timestamp timestamp NO CURRENT_TIMESTAMP +Timestamp timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') NO Column_priv set('Select','Insert','Update','References') NO use test; diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 02b1459afd0..a3744b36e87 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2299,8 +2299,7 @@ Handler_read_next 0 FLUSH STATUS; DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1) > 10000; -Warnings: -Error 1242 Subquery returns more than 1 row +ERROR 21000: Subquery returns more than 1 row SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 diff --git a/mysql-test/r/handler_innodb.result b/mysql-test/r/handler_innodb.result index e9e5c7dbdd5..d9a1a0aa12b 100644 --- a/mysql-test/r/handler_innodb.result +++ b/mysql-test/r/handler_innodb.result @@ -575,3 +575,65 @@ ERROR 42S02: Table 'test.t1' doesn't exist handler t1 close; handler t2 close; drop table t2; +drop table if exists t1; +create temporary table t1 (a int, b char(1), key a(a), key b(a,b)); +insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), +(5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); +select a,b from t1; +a b +0 a +1 b +2 c +3 d +4 e +5 f +6 g +7 h +8 i +9 j +handler t1 open as a1; +handler a1 read a first; +a b +0 a +handler a1 read a next; +a b +1 b +handler a1 read a next; +a b +2 c +select a,b from t1; +ERROR HY000: Can't reopen table: 'a1' +handler a1 read a prev; +a b +1 b +handler a1 read a prev; +a b +0 a +handler a1 read a=(6) where b="g"; +a b +6 g +handler a1 close; +select a,b from t1; +a b +0 a +1 b +2 c +3 d +4 e +5 f +6 g +7 h +8 i +9 j +handler t1 open as a2; +handler a2 read a first; +a b +0 a +handler a2 read a last; +a b +9 j +handler a2 read a prev; +a b +8 i +handler a2 close; +drop table t1; diff --git a/mysql-test/r/handler_myisam.result b/mysql-test/r/handler_myisam.result index 715e5ab03d6..b42fdc864fe 100644 --- a/mysql-test/r/handler_myisam.result +++ b/mysql-test/r/handler_myisam.result @@ -575,3 +575,65 @@ ERROR 42S02: Table 'test.t1' doesn't exist handler t1 close; handler t2 close; drop table t2; +drop table if exists t1; +create temporary table t1 (a int, b char(1), key a(a), key b(a,b)); +insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), +(5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); +select a,b from t1; +a b +0 a +1 b +2 c +3 d +4 e +5 f +6 g +7 h +8 i +9 j +handler t1 open as a1; +handler a1 read a first; +a b +0 a +handler a1 read a next; +a b +1 b +handler a1 read a next; +a b +2 c +select a,b from t1; +ERROR HY000: Can't reopen table: 'a1' +handler a1 read a prev; +a b +1 b +handler a1 read a prev; +a b +0 a +handler a1 read a=(6) where b="g"; +a b +6 g +handler a1 close; +select a,b from t1; +a b +0 a +1 b +2 c +3 d +4 e +5 f +6 g +7 h +8 i +9 j +handler t1 open as a2; +handler a2 read a first; +a b +0 a +handler a2 read a last; +a b +9 j +handler a2 read a prev; +a b +8 i +handler a2 close; +drop table t1; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 007bd112ccc..4df3b56da86 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1141,11 +1141,13 @@ DROP FUNCTION func2; select column_type, group_concat(table_schema, '.', table_name), count(*) as num from information_schema.columns where table_schema='information_schema' and -(column_type = 'varchar(7)' or column_type = 'varchar(20)') +(column_type = 'varchar(7)' or column_type = 'varchar(20)' + or column_type = 'varchar(27)') group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num +varchar(27) information_schema.COLUMNS 1 varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 -varchar(20) information_schema.COLUMNS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 6 +varchar(20) information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 5 create table t1(f1 char(1) not null, f2 char(9) not null) default character set utf8; select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 44d874ef018..3f9e9b404f0 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1288,6 +1288,40 @@ a b 3 2 1 1 DROP TABLE t1; + +# +# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table. +# + +# - prepare; + +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1(c INT) +ENGINE = InnoDB +ROW_FORMAT = COMPACT; + +# - initial check; + +SELECT table_schema, table_name, row_format +FROM INFORMATION_SCHEMA.TABLES +WHERE table_schema = DATABASE() AND table_name = 't1'; +table_schema table_name row_format +test t1 Compact + +# - change ROW_FORMAT and check; + +ALTER TABLE t1 ROW_FORMAT = REDUNDANT; + +SELECT table_schema, table_name, row_format +FROM INFORMATION_SCHEMA.TABLES +WHERE table_schema = DATABASE() AND table_name = 't1'; +table_schema table_name row_format +test t1 Redundant + +# - that's it, cleanup. + +DROP TABLE t1; End of 5.0 tests CREATE TABLE `t2` ( `k` int(11) NOT NULL auto_increment, @@ -1426,6 +1460,7 @@ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) ENGINE = InnoDB; INSERT INTO t1 VALUES (1,2); +# 1. test for locking: BEGIN; UPDATE t1 SET b = 12 WHERE a = 1; affected rows: 1 diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index a2bed75a709..3db5e926d30 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -530,3 +530,32 @@ ORDER BY c.b, c.d a b c d e f g h i j a b c d 2 2 1 2004-11-30 12:00:00 1 0 0 0 0 0 2 3388000 -553000 NULL DROP TABLE t1, t2; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT); +INSERT INTO t1 VALUES (), (), (); +SELECT 1 AS c1 +FROM t1 +ORDER BY ( +SELECT 1 AS c2 +FROM t1 +GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC +LIMIT 1); +c1 +1 +1 +1 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); +INSERT INTO t1 (a, b) +VALUES +(1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE +(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +SELECT 1 as RES FROM t1 AS t1_outer WHERE +(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +RES +DROP TABLE t1; diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 1bc98eb38fb..a31f2047670 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -50,7 +50,7 @@ general_log CREATE TABLE `general_log` ( ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show fields from mysql.general_log; Field Type Null Key Default Extra -event_time timestamp NO CURRENT_TIMESTAMP +event_time timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP user_host mediumtext NO NULL thread_id int(11) NO NULL server_id int(11) NO NULL @@ -73,7 +73,7 @@ slow_log CREATE TABLE `slow_log` ( ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show fields from mysql.slow_log; Field Type Null Key Default Extra -start_time timestamp NO CURRENT_TIMESTAMP +start_time timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP user_host mediumtext NO NULL query_time time NO NULL lock_time time NO NULL diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result index 3653461c6b8..c37dc41c495 100644 --- a/mysql-test/r/lowercase_view.result +++ b/mysql-test/r/lowercase_view.result @@ -119,7 +119,7 @@ create table t1Aa (col1 int); create view v1Aa as select col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; select Aaa.col1 from t1Aa as AaA; col1 @@ -128,6 +128,23 @@ drop view v1AA; create view v1Aa as select AaA.col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; drop table t1Aa; +CREATE TABLE t1 (a int, b int); +select X.a from t1 AS X group by X.b having (X.a = 1); +a +select X.a from t1 AS X group by X.b having (x.a = 1); +a +select X.a from t1 AS X group by X.b having (x.b = 1); +a +CREATE OR REPLACE VIEW v1 AS +select X.a from t1 AS X group by X.b having (X.a = 1); +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `x`.`a` AS `a` from `t1` `x` group by `x`.`b` having (`x`.`a` = 1) latin1 latin1_swedish_ci +SELECT * FROM v1; +a +DROP VIEW v1; +DROP TABLE t1; +End of 5.0 tests. diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 4a776b6a253..76c6ae7d034 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -21,7 +21,7 @@ def test t1 t1 g g 5 4 0 Y 32768 3 63 def test t1 t1 h h 246 7 0 Y 0 4 63 def test t1 t1 i i 13 4 0 Y 32864 0 63 def test t1 t1 j j 10 10 0 Y 128 0 63 -def test t1 t1 k k 7 19 0 N 1249 0 63 +def test t1 t1 k k 7 19 0 N 9441 0 63 def test t1 t1 l l 12 19 0 Y 128 0 63 def test t1 t1 m m 254 1 0 Y 256 0 8 def test t1 t1 n n 254 3 0 Y 2048 0 8 diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 73661897ee1..3125660643c 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1794,6 +1794,31 @@ SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; a 1 DROP TABLE t1; +CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2)) ENGINE=MYISAM; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 10 Fixed 0 # # # 1024 # # # # # # # +INSERT INTO t1 VALUES (1,1); +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # +ALTER TABLE t1 DISABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # +ALTER TABLE t1 ENABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # +ALTER TABLE t1 DISABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # +ALTER TABLE t1 ENABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # +DROP TABLE t1; End of 5.0 tests create table t1 (a int not null, key `a` (a) key_block_size=1024); show create table t1; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index f10ae4b4df5..1deb9401aa1 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -328,4 +328,27 @@ drop table t1; drop table t1; End of 5.0 tests flush logs; +BUG#31611: Security risk with BINLOG statement +SET BINLOG_FORMAT=ROW; +CREATE DATABASE mysqltest1; +CREATE USER untrusted@localhost; +GRANT SELECT ON mysqltest1.* TO untrusted@localhost; +SHOW GRANTS FOR untrusted@localhost; +Grants for untrusted@localhost +GRANT USAGE ON *.* TO 'untrusted'@'localhost' +GRANT SELECT ON `mysqltest1`.* TO 'untrusted'@'localhost' +USE mysqltest1; +CREATE TABLE t1 (a INT, b CHAR(64)); +flush logs; +INSERT INTO t1 VALUES (1,USER()); +flush logs; +mysqlbinlog var/log/master-bin.000017 > var/tmp/bug31611.sql +mysql mysqltest1 -uuntrusted < var/tmp/bug31611.sql +INSERT INTO t1 VALUES (1,USER()); +ERROR 42000: INSERT command denied to user 'untrusted'@'localhost' for table 't1' +SELECT * FROM t1; +a b +1 root@localhost +DROP DATABASE mysqltest1; +DROP USER untrusted@localhost; End of 5.1 tests diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index e39e84fdd69..d646226daf8 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1,4 +1,32 @@ drop table if exists t1; +CREATE TABLE t1 ( +d DATE NOT NULL +) +PARTITION BY RANGE( YEAR(d) ) ( +PARTITION p0 VALUES LESS THAN (1960), +PARTITION p1 VALUES LESS THAN (1970), +PARTITION p2 VALUES LESS THAN (1980), +PARTITION p3 VALUES LESS THAN (1990) +); +ALTER TABLE t1 ADD PARTITION ( +PARTITION `p5` VALUES LESS THAN (2010) +COMMENT 'APSTART \' APEND' +); +SELECT * FROM t1 LIMIT 1; +d +DROP TABLE t1; +create table t1 (id int auto_increment, s1 int, primary key (id)); +insert into t1 values (null,1); +insert into t1 values (null,6); +select * from t1; +id s1 +1 1 +2 6 +alter table t1 partition by range (id) ( +partition p0 values less than (3), +partition p1 values less than maxvalue +); +drop table t1; create table t1 (a int) partition by key(a) partitions 0.2+e1; @@ -33,11 +61,6 @@ call pz(); call pz(); drop procedure pz; drop table t1; -create table t1 (a int) -engine = csv -partition by list (a) -(partition p0 values in (null)); -ERROR HY000: Engine cannot be used in partitioned tables create table t1 (a bigint) partition by range (a) (partition p0 values less than (0xFFFFFFFFFFFFFFFF), @@ -68,11 +91,6 @@ a 18446744073709551614 drop table t1; create table t1 (a int) -engine = csv -partition by list (a) -(partition p0 values in (null)); -ERROR HY000: Engine cannot be used in partitioned tables -create table t1 (a int) partition by key(a) (partition p0 engine = MEMORY); drop table t1; @@ -319,18 +337,6 @@ analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK drop table t1; -CREATE TABLE `t1` ( -`id` int(11) default NULL -) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ; -SELECT * FROM t1; -id -drop table t1; -CREATE TABLE `t1` ( -`id` int(11) default NULL -) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ; -SELECT * FROM t1; -id -drop table t1; create table t1 (a int) partition by range (a) @@ -709,7 +715,7 @@ partition by range (a) alter table t1 add partition (partition p1 values in (2)); ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition alter table t1 add partition (partition p1); -ERROR HY000: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition +ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition drop table t1; create table t1 (a int) partition by list (a) @@ -717,7 +723,7 @@ partition by list (a) alter table t1 add partition (partition p1 values less than (2)); ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition alter table t1 add partition (partition p1); -ERROR HY000: LIST PARTITIONING requires definition of VALUES IN for each partition +ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition drop table t1; create table t1 (a int) partition by hash (a) @@ -1050,18 +1056,6 @@ OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note The storage engine for the table doesn't support optimize drop table t1; -create database db99; -use db99; -create table t1 (a int not null) -engine=archive -partition by list (a) -(partition p0 values in (1), partition p1 values in (2)); -insert into t1 values (1), (2); -create index inx on t1 (a); -alter table t1 add partition (partition p2 values in (3)); -alter table t1 drop partition p2; -use test; -drop database db99; drop procedure if exists mysqltest_1; create table t1 (a int) partition by list (a) @@ -1291,18 +1285,9 @@ t1 CREATE TABLE `t1` ( `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (b) (PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (20) ENGINE = MyISAM) */ drop table t1, t2; -create table t1 (int_column int, char_column char(5)) -PARTITION BY RANGE (int_column) subpartition by key (char_column) -(PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); -Warnings: -Warning 1286 Unknown table engine 'InnoDB' -alter table t1 PARTITION BY RANGE (int_column) subpartition by key (char_column) -(PARTITION p1 VALUES LESS THAN (5) ENGINE = myisam); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `int_column` int(11) DEFAULT NULL, - `char_column` char(5) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (int_column) SUBPARTITION BY KEY (char_column) (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ +create table t1 +(s1 timestamp on update current_timestamp, s2 int) +partition by key(s1) partitions 3; +insert into t1 values (null,null); drop table t1; End of 5.1 tests diff --git a/mysql-test/r/partition_archive.result b/mysql-test/r/partition_archive.result new file mode 100644 index 00000000000..dd063f6224c --- /dev/null +++ b/mysql-test/r/partition_archive.result @@ -0,0 +1,13 @@ +drop database if exists db99; +create database db99; +use db99; +create table t1 (a int not null) +engine=archive +partition by list (a) +(partition p0 values in (1), partition p1 values in (2)); +insert into t1 values (1), (2); +create index inx on t1 (a); +alter table t1 add partition (partition p2 values in (3)); +alter table t1 drop partition p2; +use test; +drop database db99; diff --git a/mysql-test/r/partition_blackhole.result b/mysql-test/r/partition_blackhole.result new file mode 100644 index 00000000000..225cc1886fd --- /dev/null +++ b/mysql-test/r/partition_blackhole.result @@ -0,0 +1,7 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE `t1` ( +`id` int(11) default NULL +) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ; +SELECT * FROM t1; +id +DROP TABLE t1; diff --git a/mysql-test/r/partition_csv.result b/mysql-test/r/partition_csv.result new file mode 100644 index 00000000000..b5e1dcd9541 --- /dev/null +++ b/mysql-test/r/partition_csv.result @@ -0,0 +1,15 @@ +drop table if exists t1; +create table t1 (a int) +engine = csv +partition by list (a) +(partition p0 values in (null)); +ERROR HY000: Engine cannot be used in partitioned tables +USE mysql; +SET GLOBAL general_log = 0; +ALTER TABLE general_log ENGINE = MyISAM; +ALTER TABLE general_log PARTITION BY RANGE (TO_DAYS(event_time)) +(PARTITION p0 VALUES LESS THAN (733144), +PARTITION p1 VALUES LESS THAN (3000000)); +ERROR HY000: Incorrect usage of PARTITION and log table +ALTER TABLE general_log ENGINE = CSV; +SET GLOBAL general_log = 1; diff --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result new file mode 100644 index 00000000000..c6506178b03 --- /dev/null +++ b/mysql-test/r/partition_datatype.result @@ -0,0 +1,282 @@ +drop table if exists t1; +create table t1 (a tinyint not null) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a smallint not null) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a mediumint not null) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a int not null) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a bigint not null) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a float not null) partition by key (a); +insert into t1 values (2.1); +select * from t1 where a = 2.1; +a +drop table t1; +create table t1 (a double not null) partition by key (a); +insert into t1 values (2.1); +select * from t1 where a = 2.1; +a +2.1 +drop table t1; +create table t1 (a decimal not null) partition by key (a); +insert into t1 values (2.1); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +select * from t1 where a = 2.1; +a +drop table t1; +create table t1 (a date not null) partition by key (a); +insert into t1 values ('2001-01-01'); +select * from t1 where a = '2001-01-01'; +a +2001-01-01 +drop table t1; +create table t1 (a datetime not null) partition by key (a); +insert into t1 values ('2001-01-01 01:02:03'); +select * from t1 where a = '2001-01-01 01:02:03'; +a +2001-01-01 01:02:03 +drop table t1; +create table t1 (a timestamp not null) partition by key (a); +insert into t1 values ('2001-01-01 01:02:03'); +select * from t1 where a = '2001-01-01 01:02:03'; +a +2001-01-01 01:02:03 +drop table t1; +create table t1 (a time not null) partition by key (a); +insert into t1 values ('01:02:03'); +select * from t1 where a = '01:02:03'; +a +01:02:03 +drop table t1; +create table t1 (a year not null) partition by key (a); +insert into t1 values ('2001'); +select * from t1 where a = '2001'; +a +2001 +drop table t1; +create table t1 (a varchar(10) character set utf8 not null) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a varchar(300) character set utf8 not null) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a varchar(10) character set latin1 not null) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a varchar(300) character set latin1 not null) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a char(10) character set utf8 not null) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a char(10) character set latin1 not null) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a enum('y','n') not null) partition by key (a); +insert into t1 values ('y'); +select * from t1 where a = 'y'; +a +y +drop table t1; +create table t1 (a set('y','n') not null) partition by key (a); +insert into t1 values ('y'); +select * from t1 where a = 'y'; +a +y +drop table t1; +create table t1 (a tinyint) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a smallint) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a mediumint) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a int) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a bigint) partition by key (a); +insert into t1 values (2); +select * from t1 where a = 2; +a +2 +drop table t1; +create table t1 (a float) partition by key (a); +insert into t1 values (2.1); +select * from t1 where a = 2.1; +a +drop table t1; +create table t1 (a double) partition by key (a); +insert into t1 values (2.1); +select * from t1 where a = 2.1; +a +2.1 +drop table t1; +create table t1 (a decimal) partition by key (a); +insert into t1 values (2.1); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +select * from t1 where a = 2.1; +a +drop table t1; +create table t1 (a date) partition by key (a); +insert into t1 values ('2001-01-01'); +select * from t1 where a = '2001-01-01'; +a +2001-01-01 +drop table t1; +create table t1 (a datetime) partition by key (a); +insert into t1 values ('2001-01-01 01:02:03'); +select * from t1 where a = '2001-01-01 01:02:03'; +a +2001-01-01 01:02:03 +drop table t1; +create table t1 (a timestamp null) partition by key (a); +insert into t1 values ('2001-01-01 01:02:03'); +select * from t1 where a = '2001-01-01 01:02:03'; +a +2001-01-01 01:02:03 +drop table t1; +create table t1 (a time) partition by key (a); +insert into t1 values ('01:02:03'); +select * from t1 where a = '01:02:03'; +a +01:02:03 +drop table t1; +create table t1 (a year) partition by key (a); +insert into t1 values ('2001'); +select * from t1 where a = '2001'; +a +2001 +drop table t1; +create table t1 (a varchar(10) character set utf8) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a varchar(300) character set utf8) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a varchar(10) character set latin1) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a varchar(300) character set latin1) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a char(10) character set utf8) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a char(10) character set latin1) partition by key (a); +insert into t1 values ('abc'); +select * from t1 where a = 'abc'; +a +abc +drop table t1; +create table t1 (a enum('y','n')) partition by key (a); +insert into t1 values ('y'); +select * from t1 where a = 'y'; +a +y +drop table t1; +create table t1 (a set('y','n')) partition by key (a); +insert into t1 values ('y'); +select * from t1 where a = 'y'; +a +y +drop table t1; +create table t1 (a varchar(65531)) partition by key (a); +insert into t1 values ('bbbb'); +insert into t1 values ('aaaa'); +select * from t1 where a = 'aaa%'; +a +select * from t1 where a like 'aaa%'; +a +aaaa +drop table t1; +create table t1 (a varchar(65532)) partition by key (a); +insert into t1 values ('bbbb'); +insert into t1 values ('aaaa'); +select * from t1 where a = 'aaa%'; +a +select * from t1 where a like 'aaa%'; +a +aaaa +drop table t1; +create table t1 (a varchar(65533) not null) partition by key (a); +insert into t1 values ('aaaa'); +select * from t1 where a = 'aaa%'; +a +drop table t1; +create table t1 (a varchar(65533)) partition by key (a); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +create table t1 (a varchar(65534) not null) partition by key (a); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +create table t1 (a varchar(65535)) partition by key (a); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index 7952c8df609..46532cb32ab 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -1,4 +1,13 @@ drop table if exists t1; +CREATE TABLE t1 ( +a int +) +PARTITION BY RANGE (a) +( +PARTITION p0 VALUES LESS THAN (1), +PARTITION p1 VALU ES LESS THAN (2) +); +ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition partition by list (a) partitions 3 (partition x1 values in (1,2,9,4) tablespace ts1, @@ -351,7 +360,7 @@ partition by range (a) partitions 2 (partition x1 values less than (4), partition x2); -ERROR HY000: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition +ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition CREATE TABLE t1 ( a int not null, b int not null, @@ -531,7 +540,7 @@ partition by list (a) partitions 2 (partition x1 values in (4), partition x2); -ERROR HY000: LIST PARTITIONING requires definition of VALUES IN for each partition +ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition CREATE TABLE t1 ( a int not null, b int not null, @@ -551,7 +560,7 @@ partition by list (a) partitions 2 (partition x1 values in (4,6), partition x2); -ERROR HY000: LIST PARTITIONING requires definition of VALUES IN for each partition +ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition CREATE TABLE t1 ( a int not null, b int not null, diff --git a/mysql-test/r/partition_hash.result b/mysql-test/r/partition_hash.result index 9a82a36d902..72f036be099 100644 --- a/mysql-test/r/partition_hash.result +++ b/mysql-test/r/partition_hash.result @@ -183,3 +183,6 @@ c1 c2 c3 182 abc 2002-11-09 184 abc 2002-11-22 drop table t1; +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1; +INSERT DELAYED INTO t1 VALUES (1); +DROP TABLE t1; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 5b755b6bfd5..f80e0001ea8 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -136,3 +136,16 @@ SELECT COUNT(*) FROM t1; COUNT(*) 2 DROP TABLE t1; +create table t1 (int_column int, char_column char(5)) +PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 +(PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); +alter table t1 PARTITION BY RANGE (int_column) +subpartition by key (char_column) subpartitions 2 +(PARTITION p1 VALUES LESS THAN (5) ENGINE = myisam); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `int_column` int(11) DEFAULT NULL, + `char_column` char(5) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (int_column) SUBPARTITION BY KEY (char_column) SUBPARTITIONS 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ +drop table t1; diff --git a/mysql-test/r/partition_mgm.result b/mysql-test/r/partition_mgm.result index 04ac603fea7..9ef220028b3 100644 --- a/mysql-test/r/partition_mgm.result +++ b/mysql-test/r/partition_mgm.result @@ -17,12 +17,6 @@ t1 CREATE TABLE `t1` ( `f_date` date DEFAULT NULL, `f_varchar` varchar(30) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) PARTITIONS 2 */ -hello/master-data/test/t1#P#p0.MYD -hello/master-data/test/t1#P#p0.MYI -hello/master-data/test/t1#P#p1.MYD -hello/master-data/test/t1#P#p1.MYI -hello/master-data/test/t1.frm -hello/master-data/test/t1.par ALTER TABLE t1 COALESCE PARTITION 1; SHOW CREATE TABLE t1; Table Create Table @@ -30,10 +24,6 @@ t1 CREATE TABLE `t1` ( `f_date` date DEFAULT NULL, `f_varchar` varchar(30) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) PARTITIONS 1 */ -hello/master-data/test/t1#P#p0.MYD -hello/master-data/test/t1#P#p0.MYI -hello/master-data/test/t1.frm -hello/master-data/test/t1.par drop table t1; create table t1 (a int) partition by list (a) diff --git a/mysql-test/r/partition_symlink.result b/mysql-test/r/partition_symlink.result new file mode 100644 index 00000000000..20e841d2e0e --- /dev/null +++ b/mysql-test/r/partition_symlink.result @@ -0,0 +1,83 @@ +DROP TABLE IF EXISTS t1; +DROP DATABASE IF EXISTS mysqltest2; +# Creating two non colliding tables mysqltest2.t1 and test.t1 +# test.t1 have partitions in mysqltest2-directory! +# user root: +CREATE USER mysqltest_1@localhost; +CREATE DATABASE mysqltest2; +USE mysqltest2; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0); +# user mysqltest_1: +USE test; +CREATE TABLE t1 (a INT) +PARTITION BY LIST (a) ( +PARTITION p0 VALUES IN (0) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2', +PARTITION p1 VALUES IN (1) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test', +PARTITION p2 VALUES IN (2) +); +# without the patch for bug#32091 this would create +# files mysqltest2/t1.MYD + .MYI and possible overwrite +# the mysqltest2.t1 table (depending on bug#32111) +ALTER TABLE t1 REMOVE PARTITIONING; +INSERT INTO t1 VALUES (1); +SELECT * FROM t1; +a +1 +# user root: +USE mysqltest2; +FLUSH TABLES; +# if the patch works, this should be different +# and before the patch they were the same! +SELECT * FROM t1; +a +0 +USE test; +SELECT * FROM t1; +a +1 +DROP TABLE t1; +DROP DATABASE mysqltest2; +# test that symlinks can not overwrite files when CREATE TABLE +# user root: +CREATE DATABASE mysqltest2; +USE mysqltest2; +CREATE TABLE t1 (a INT) +PARTITION BY LIST (a) ( +PARTITION p0 VALUES IN (0) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2', +PARTITION p1 VALUES IN (1) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' + ); +# user mysqltest_1: +USE test; +CREATE TABLE t1 (a INT) +PARTITION BY LIST (a) ( +PARTITION p0 VALUES IN (0) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2', +PARTITION p1 VALUES IN (1) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' + ); +Got one of the listed errors +CREATE TABLE t1 (a INT) +PARTITION BY LIST (a) ( +PARTITION p0 VALUES IN (0) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test', +PARTITION p1 VALUES IN (1) +DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' + ); +Got one of the listed errors +# user root (cleanup): +DROP DATABASE mysqltest2; +USE test; +DROP USER mysqltest_1@localhost; diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index 8628acecf55..e4af1535775 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -17,3 +17,13 @@ UNINSTALL PLUGIN EXAMPLE; ERROR 42000: PLUGIN EXAMPLE does not exist UNINSTALL PLUGIN non_exist; ERROR 42000: PLUGIN non_exist does not exist +# +# Bug#32034: check_func_enum() does not check correct values but set it +# to impossible int val +# +INSTALL PLUGIN example SONAME 'ha_example.so'; +SET GLOBAL example_enum_var= e1; +SET GLOBAL example_enum_var= e2; +SET GLOBAL example_enum_var= impossible; +ERROR 42000: Variable 'enum_var' can't be set to the value of 'impossible' +UNINSTALL PLUGIN example; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index edac68a88d6..9d0906ad5e7 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -2680,4 +2680,21 @@ t1 CREATE TABLE `t1` ( KEY `c` (`c`(10)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +drop table if exists t1, t2; +create table t1 (a int, b int); +create table t2 like t1; +insert into t1 (a, b) values (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +insert into t2 select a, max(b) from t1 group by a; +prepare stmt from "delete from t2 where (select (select max(b) from t1 group +by a having a < 2) x from t1) > 10000"; +delete from t2 where (select (select max(b) from t1 group +by a having a < 2) x from t1) > 10000; +ERROR 21000: Subquery returns more than 1 row +execute stmt; +ERROR 21000: Subquery returns more than 1 row +execute stmt; +ERROR 21000: Subquery returns more than 1 row +deallocate prepare stmt; +drop table t1, t2; End of 5.1 tests. diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 6f0bad71f23..eb8ef7a85c3 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 85a109b0630..15650efa41d 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 980b89ad504..486f770220e 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -64,7 +64,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index e95f8766a24..0229b0ece08 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -106,7 +106,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 @@ -3128,7 +3128,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 745819107f0..719d0926022 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4233,6 +4233,80 @@ str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00' select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL 0 +CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, +c22 INT DEFAULT NULL, +KEY(c21, c22)); +CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, +c32 INT DEFAULT NULL, +c33 INT NOT NULL, +c34 INT UNSIGNED DEFAULT 0, +KEY (c33, c34, c32)); +INSERT INTO t1 values (),(),(),(),(); +INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; +INSERT INTO t3 VALUES (1, 1, 1, 0), +(2, 2, 0, 0), +(3, 3, 1, 0), +(4, 4, 0, 0), +(5, 5, 1, 0); +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND +t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND +t3.c33 = 1 AND t2.c22 in (1, 3) +ORDER BY c32; +c32 +1 +1 +3 +3 +5 +5 +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND +t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND +t3.c33 = 1 AND t2.c22 in (1, 3) +ORDER BY c32 DESC; +c32 +5 +5 +3 +3 +1 +1 +DROP TABLE t1, t2, t3; + +# +# Bug#30736: Row Size Too Large Error Creating a Table and +# Inserting Data. +# +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; + +CREATE TABLE t1( +c1 DECIMAL(10, 2), +c2 FLOAT); + +INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5); + +CREATE TABLE t2( +c3 DECIMAL(10, 2)) +SELECT +c1 * c2 AS c3 +FROM t1; + +SELECT * FROM t1; +c1 c2 +0.00 1 +2.00 3 +4.00 5 + +SELECT * FROM t2; +c3 +0.00 +6.00 +20.00 + +DROP TABLE t1; +DROP TABLE t2; + End of 5.0 tests create table t1(a INT, KEY (a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 04a5253e6a8..ff32d2a1512 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -979,7 +979,7 @@ def COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 192 0 Y 0 0 33 def COLUMNS COLLATION_NAME COLLATION_NAME 253 192 0 Y 0 0 33 def COLUMNS COLUMN_TYPE COLUMN_TYPE 252 589815 7 N 17 0 33 def COLUMNS COLUMN_KEY COLUMN_KEY 253 9 3 N 1 0 33 -def COLUMNS EXTRA EXTRA 253 60 0 N 1 0 33 +def COLUMNS EXTRA EXTRA 253 81 0 N 1 0 33 def COLUMNS PRIVILEGES PRIVILEGES 253 240 31 N 1 0 33 def COLUMNS COLUMN_COMMENT COLUMN_COMMENT 253 765 0 N 1 0 33 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT @@ -998,7 +998,7 @@ def COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 def COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 def COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 def COLUMNS COLUMN_DEFAULT Default 252 589815 0 Y 16 0 33 -def COLUMNS EXTRA Extra 253 60 0 N 1 0 33 +def COLUMNS EXTRA Extra 253 81 0 N 1 0 33 Field Type Null Key Default Extra c int(11) NO PRI NULL ---------------------------------------------------------------- diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 1b14d75cd9c..b81f8ea64c9 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1428,7 +1428,6 @@ create function bug20701() returns varchar(25) binary return "test"; ERROR 42000: This version of MySQL doesn't yet support 'return value collation' create function bug20701() returns varchar(25) return "test"; drop function bug20701; -End of 5.1 tests create procedure proc_26503_error_1() begin retry: @@ -1523,3 +1522,60 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT ..inexistent(); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.inexistent()' at line 1 USE test; +create function f1() returns int +begin +set @test = 1, password = password('foo'); +return 1; +end| +ERROR HY000: Not allowed to set autocommit from a stored function or trigger +create trigger t1 +before insert on t2 for each row set password = password('foo');| +ERROR HY000: Not allowed to set autocommit from a stored function or trigger +drop function if exists f1; +drop function if exists f2; +drop table if exists t1, t2; +create function f1() returns int +begin +drop temporary table t1; +return 1; +end| +create temporary table t1 as select f1(); +ERROR HY000: Can't reopen table: 't1' +create function f2() returns int +begin +create temporary table t2 as select f1(); +return 1; +end| +create temporary table t1 as select f2(); +ERROR HY000: Can't reopen table: 't1' +drop function f1; +drop function f2; +create function f1() returns int +begin +drop temporary table t2,t1; +return 1; +end| +create function f2() returns int +begin +create temporary table t2 as select f1(); +return 1; +end| +create temporary table t1 as select f2(); +ERROR HY000: Can't reopen table: 't2' +drop function f1; +drop function f2; +create temporary table t2(a int); +select * from t2; +a +create function f2() returns int +begin +drop temporary table t2; +return 1; +end| +select f2(); +f2() +1 +drop function f2; +drop table t2; +ERROR 42S02: Unknown table 't2' +End of 5.1 tests diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index aba60f757c6..4eece9d5b5a 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -99,6 +99,12 @@ t1 CREATE TABLE `t1` ( `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +CREATE TABLE t1(a INT) +DATA DIRECTORY='TEST_DIR/master-data/mysql' +INDEX DIRECTORY='TEST_DIR/master-data/mysql'; +RENAME TABLE t1 TO user; +ERROR HY000: Can't create/write to file 'TEST_DIR/master-data/mysql/user.MYI' (Errcode: 17) +DROP TABLE t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 149f0d2dca5..6e08067d8a4 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -57,7 +57,7 @@ ushort smallint(5) unsigned zerofill NULL NO MUL 00000 # umedium mediumint(8) unsigned NULL NO MUL 0 # ulong int(11) unsigned NULL NO MUL 0 # ulonglong bigint(13) unsigned NULL NO MUL 0 # -time_stamp timestamp NULL NO CURRENT_TIMESTAMP # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field date NULL YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # @@ -225,7 +225,7 @@ ushort smallint(5) unsigned zerofill NULL NO 00000 # umedium mediumint(8) unsigned NULL NO MUL 0 # ulong int(11) unsigned NULL NO MUL 0 # ulonglong bigint(13) unsigned NULL NO MUL 0 # -time_stamp timestamp NULL NO CURRENT_TIMESTAMP # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field char(10) latin1_swedish_ci YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 7caf7e78fe9..596dcbf432c 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -251,7 +251,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO 2003-01-01 00:00:00 +t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; create table t1 (t1 timestamp default now() on update now(), t2 datetime); @@ -276,7 +276,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; create table t1 (t1 timestamp, t2 datetime, t3 timestamp); @@ -302,7 +302,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; @@ -328,7 +328,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL truncate table t1; insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index da27a71c1a1..cb5afcf5f17 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -11,7 +11,7 @@ RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; select myfunc_double(); -ERROR HY000: myfunc_double must have at least one argument +ERROR HY000: Can't initialize function 'myfunc_double'; myfunc_double must have at least one argument select myfunc_double(1); myfunc_double(1) 49.00 @@ -24,26 +24,26 @@ select myfunc_int(); myfunc_int() 0 select lookup(); -ERROR HY000: Wrong arguments to lookup; Use the source +ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup; Use the source select lookup("127.0.0.1"); lookup("127.0.0.1") 127.0.0.1 select lookup(127,0,0,1); -ERROR HY000: Wrong arguments to lookup; Use the source +ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup; Use the source select lookup("localhost"); lookup("localhost") 127.0.0.1 select reverse_lookup(); -ERROR HY000: Wrong number of arguments to reverse_lookup; Use the source +ERROR HY000: Can't initialize function 'reverse_lookup'; Wrong number of arguments to reverse_lookup; Use the source select reverse_lookup("127.0.0.1"); select reverse_lookup(127,0,0,1); select reverse_lookup("localhost"); reverse_lookup("localhost") NULL select avgcost(); -ERROR HY000: wrong number of arguments: AVGCOST() requires two arguments +ERROR HY000: Can't initialize function 'avgcost'; wrong number of arguments: AVGCOST() requires two arguments select avgcost(100,23.76); -ERROR HY000: wrong argument type: AVGCOST() requires an INT and a REAL +ERROR HY000: Can't initialize function 'avgcost'; wrong argument type: AVGCOST() requires an INT and a REAL create table t1(sum int, price float(24)); insert into t1 values(100, 50.00), (100, 100.00); select avgcost(sum, price) from t1; |