diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2022-10-25 11:26:37 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2022-10-25 11:26:37 +0300 |
commit | 9a0b9e336081619d88211d17895e3e7f8030ab3f (patch) | |
tree | dff672703c5309b1e34227330ea027ab13e6c29a | |
parent | 2f7a0072b6f343051fc2dbd77ea46519617b94ab (diff) | |
parent | 667d3fbbb51044b20d23150992adbbad1f04aad8 (diff) | |
download | mariadb-git-9a0b9e336081619d88211d17895e3e7f8030ab3f.tar.gz |
Merge 10.4 into 10.5
87 files changed, 1754 insertions, 240 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index e742519fa02..ee1870ce1d4 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -6952,7 +6952,12 @@ int main(int argc, char **argv) if (flush_logs || opt_delete_master_logs) { if (mysql_refresh(mysql, REFRESH_LOG)) + { + fprintf(stderr, + "Flush logs or delete master logs failure in server \n"); + first_error= EX_MYSQLERR; goto err; + } verbose_msg("-- main : logs flushed successfully!\n"); } diff --git a/debian/rules b/debian/rules index 57636eb028a..71e7c88be40 100755 --- a/debian/rules +++ b/debian/rules @@ -1,8 +1,11 @@ #!/usr/bin/make -f -# Enable Debian Hardening -# https://wiki.debian.org/Hardening -export DEB_BUILD_MAINT_OPTIONS = hardening=+all +export DH_VERBOSE=1 +export DEB_BUILD_HARDENING=1 + +# enable Debian Hardening +# see: https://wiki.debian.org/Hardening +export DEB_BUILD_MAINT_OPTIONS = hardening=+all optimize=-lto DPKG_EXPORT_BUILDFLAGS = 1 # Include all defaults, including buildflags.mk include /usr/share/dpkg/default.mk diff --git a/include/my_base.h b/include/my_base.h index c29e9de6e0d..2a420591873 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -276,8 +276,8 @@ enum ha_base_keytype { #define HA_GENERATED_KEY 8192U /* Automatically generated key */ /* The combination of the above can be used for key type comparison. */ -#define HA_KEYFLAG_MASK (HA_NOSAME | HA_PACK_KEY | HA_AUTO_KEY | \ - HA_BINARY_PACK_KEY | HA_FULLTEXT | HA_UNIQUE_CHECK | \ +#define HA_KEYFLAG_MASK (HA_NOSAME | HA_AUTO_KEY | \ + HA_FULLTEXT | HA_UNIQUE_CHECK | \ HA_SPATIAL | HA_NULL_ARE_EQUAL | HA_GENERATED_KEY) /* diff --git a/include/mysql/service_encryption.h b/include/mysql/service_encryption.h index a4e908f9aff..69d205a27e8 100644 --- a/include/mysql/service_encryption.h +++ b/include/mysql/service_encryption.h @@ -34,12 +34,13 @@ extern "C" { #ifndef __cplusplus #define inline __inline #endif -#elif defined(__FreeBSD__) || defined(__OpenBSD__) || defined(__NetBSD__) || defined(__DragonFly__) -#include <stdlib.h> #else +#include <stdlib.h> +#ifdef HAVE_ALLOCA_H #include <alloca.h> #endif #endif +#endif /* returned from encryption_key_get_latest_version() */ #define ENCRYPTION_KEY_VERSION_INVALID (~(unsigned int)0) diff --git a/mysql-test/main/alter_table_upgrade_aria.result b/mysql-test/main/alter_table_upgrade_aria.result new file mode 100644 index 00000000000..10afd128649 --- /dev/null +++ b/mysql-test/main/alter_table_upgrade_aria.result @@ -0,0 +1,53 @@ +# +# Start of 10.4 tests +# +# +# MDEV-29481 mariadb-upgrade prints confusing statement +# +SET @debug_key_flags=NULL; +SET default_storage_engine=ARIA; +CREATE PROCEDURE debug_show_key_flags() +BEGIN +IF @debug_key_flags IS TRUE +THEN +FLUSH TABLES; +-- Wrap SET into EXECUTE IMMEDIATE to avoid +-- parse time "Unknown system variable" errors in release builds. +EXECUTE IMMEDIATE "SET debug_dbug='+d,key'"; +SELECT * FROM t1 LIMIT 0; +EXECUTE IMMEDIATE "SET debug_dbug=''"; +END IF; +END; +$$ +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` double(18,7) DEFAULT NULL, + KEY `d` (`d`) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +CHECK TABLE t1 FOR UPGRADE; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CALL debug_show_key_flags(); +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=INSTANT; +CALL debug_show_key_flags(); +DROP TABLE t1; +CALL debug_show_key_flags(); +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=NOCOPY; +CALL debug_show_key_flags(); +DROP TABLE t1; +CALL debug_show_key_flags(); +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +CALL debug_show_key_flags(); +DROP TABLE t1; +CALL debug_show_key_flags(); +ALTER TABLE t1 FORCE; +CALL debug_show_key_flags(); +DROP TABLE t1; +DROP PROCEDURE debug_show_key_flags; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/alter_table_upgrade_aria.test b/mysql-test/main/alter_table_upgrade_aria.test new file mode 100644 index 00000000000..5b87095f515 --- /dev/null +++ b/mysql-test/main/alter_table_upgrade_aria.test @@ -0,0 +1,21 @@ +--source include/have_aria.inc + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-29481 mariadb-upgrade prints confusing statement +--echo # + +let $table= std_data/mysql_upgrade/mdev29481_100104_aria; +let $EXT_DAT= MAD; +let $EXT_IDX= MAI; +SET @debug_key_flags=NULL; +SET default_storage_engine=ARIA; +--source alter_table_upgrade_mdev29481_myisam_aria.inc + + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/alter_table_upgrade_mdev29481_myisam_aria.inc b/mysql-test/main/alter_table_upgrade_mdev29481_myisam_aria.inc new file mode 100644 index 00000000000..0a8759eccdd --- /dev/null +++ b/mysql-test/main/alter_table_upgrade_mdev29481_myisam_aria.inc @@ -0,0 +1,59 @@ +let $datadir=`select @@datadir`; + +DELIMITER $$; +CREATE PROCEDURE debug_show_key_flags() +BEGIN + IF @debug_key_flags IS TRUE + THEN + FLUSH TABLES; + -- Wrap SET into EXECUTE IMMEDIATE to avoid + -- parse time "Unknown system variable" errors in release builds. + EXECUTE IMMEDIATE "SET debug_dbug='+d,key'"; + SELECT * FROM t1 LIMIT 0; + EXECUTE IMMEDIATE "SET debug_dbug=''"; + END IF; +END; +$$ +DELIMITER ;$$ + + +copy_file $table.frm $datadir/test/t1.frm; +copy_file $table.$EXT_DAT $datadir/test/t1.$EXT_DAT; +copy_file $table.$EXT_IDX $datadir/test/t1.$EXT_IDX; +SHOW CREATE TABLE t1; +CHECK TABLE t1 FOR UPGRADE; +DROP TABLE t1; + +copy_file $table.frm $datadir/test/t1.frm; +copy_file $table.$EXT_DAT $datadir/test/t1.$EXT_DAT; +copy_file $table.$EXT_IDX $datadir/test/t1.$EXT_IDX; +CALL debug_show_key_flags(); +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=INSTANT; +CALL debug_show_key_flags(); +DROP TABLE t1; + +copy_file $table.frm $datadir/test/t1.frm; +copy_file $table.$EXT_DAT $datadir/test/t1.$EXT_DAT; +copy_file $table.$EXT_IDX $datadir/test/t1.$EXT_IDX; +CALL debug_show_key_flags(); +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=NOCOPY; +CALL debug_show_key_flags(); +DROP TABLE t1; + +copy_file $table.frm $datadir/test/t1.frm; +copy_file $table.$EXT_DAT $datadir/test/t1.$EXT_DAT; +copy_file $table.$EXT_IDX $datadir/test/t1.$EXT_IDX; +CALL debug_show_key_flags(); +REPAIR TABLE t1; +CALL debug_show_key_flags(); +DROP TABLE t1; + +copy_file $table.frm $datadir/test/t1.frm; +copy_file $table.$EXT_DAT $datadir/test/t1.$EXT_DAT; +copy_file $table.$EXT_IDX $datadir/test/t1.$EXT_IDX; +CALL debug_show_key_flags(); +ALTER TABLE t1 FORCE; +CALL debug_show_key_flags(); +DROP TABLE t1; + +DROP PROCEDURE debug_show_key_flags; diff --git a/mysql-test/main/alter_table_upgrade_myisam.result b/mysql-test/main/alter_table_upgrade_myisam.result new file mode 100644 index 00000000000..06bd1b70a0a --- /dev/null +++ b/mysql-test/main/alter_table_upgrade_myisam.result @@ -0,0 +1,52 @@ +# +# Start of 10.4 tests +# +# +# MDEV-29481 mariadb-upgrade prints confusing statement +# +SET @debug_key_flags=NULL; +CREATE PROCEDURE debug_show_key_flags() +BEGIN +IF @debug_key_flags IS TRUE +THEN +FLUSH TABLES; +-- Wrap SET into EXECUTE IMMEDIATE to avoid +-- parse time "Unknown system variable" errors in release builds. +EXECUTE IMMEDIATE "SET debug_dbug='+d,key'"; +SELECT * FROM t1 LIMIT 0; +EXECUTE IMMEDIATE "SET debug_dbug=''"; +END IF; +END; +$$ +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` double(18,7) DEFAULT NULL, + KEY `d` (`d`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CHECK TABLE t1 FOR UPGRADE; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CALL debug_show_key_flags(); +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=INSTANT; +CALL debug_show_key_flags(); +DROP TABLE t1; +CALL debug_show_key_flags(); +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=NOCOPY; +CALL debug_show_key_flags(); +DROP TABLE t1; +CALL debug_show_key_flags(); +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +CALL debug_show_key_flags(); +DROP TABLE t1; +CALL debug_show_key_flags(); +ALTER TABLE t1 FORCE; +CALL debug_show_key_flags(); +DROP TABLE t1; +DROP PROCEDURE debug_show_key_flags; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/alter_table_upgrade_myisam.test b/mysql-test/main/alter_table_upgrade_myisam.test new file mode 100644 index 00000000000..251ca3e3555 --- /dev/null +++ b/mysql-test/main/alter_table_upgrade_myisam.test @@ -0,0 +1,17 @@ +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-29481 mariadb-upgrade prints confusing statement +--echo # + +let $table= std_data/mysql_upgrade/mdev29481_100104_myisam; +let $EXT_DAT= MYD; +let $EXT_IDX= MYI; +SET @debug_key_flags=NULL; +--source alter_table_upgrade_mdev29481_myisam_aria.inc + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/alter_table_upgrade_myisam_debug.result b/mysql-test/main/alter_table_upgrade_myisam_debug.result new file mode 100644 index 00000000000..ed135585d9d --- /dev/null +++ b/mysql-test/main/alter_table_upgrade_myisam_debug.result @@ -0,0 +1,92 @@ +# +# Start of 10.4 tests +# +# +# MDEV-29481 mariadb-upgrade prints confusing statement +# +SET @debug_key_flags=TRUE; +CREATE PROCEDURE debug_show_key_flags() +BEGIN +IF @debug_key_flags IS TRUE +THEN +FLUSH TABLES; +-- Wrap SET into EXECUTE IMMEDIATE to avoid +-- parse time "Unknown system variable" errors in release builds. +EXECUTE IMMEDIATE "SET debug_dbug='+d,key'"; +SELECT * FROM t1 LIMIT 0; +EXECUTE IMMEDIATE "SET debug_dbug=''"; +END IF; +END; +$$ +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` double(18,7) DEFAULT NULL, + KEY `d` (`d`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CHECK TABLE t1 FOR UPGRADE; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000068 (HA_NULL_PART_KEY|HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=INSTANT; +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000068 (HA_NULL_PART_KEY|HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +DROP TABLE t1; +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000068 (HA_NULL_PART_KEY|HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +ALTER TABLE t1 MODIFY d DOUBLE DEFAULT 10, ALGORITHM=NOCOPY; +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000068 (HA_NULL_PART_KEY|HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +DROP TABLE t1; +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000068 (HA_NULL_PART_KEY|HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000068 (HA_NULL_PART_KEY|HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +DROP TABLE t1; +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000068 (HA_NULL_PART_KEY|HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +ALTER TABLE t1 FORCE; +CALL debug_show_key_flags(); +d +Warnings: +Note 1105 DBUG: ha_myisam::open: name=`d` flags=00000048 (HA_NULL_PART_KEY|HA_VAR_LENGTH_KEY) +Note 1105 DBUG: seg[0].type=6 DOUBLE +Note 1105 DBUG: seg[0].flag=00000850 (HA_CAN_MEMCMP|HA_SWAP_KEY|HA_NULL_PART) +DROP TABLE t1; +DROP PROCEDURE debug_show_key_flags; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/alter_table_upgrade_myisam_debug.test b/mysql-test/main/alter_table_upgrade_myisam_debug.test new file mode 100644 index 00000000000..8e26bcd2453 --- /dev/null +++ b/mysql-test/main/alter_table_upgrade_myisam_debug.test @@ -0,0 +1,19 @@ +--source include/have_debug.inc + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-29481 mariadb-upgrade prints confusing statement +--echo # + +let $table= std_data/mysql_upgrade/mdev29481_100104_myisam; +let $EXT_DAT= MYD; +let $EXT_IDX= MYI; +SET @debug_key_flags=TRUE; +--source alter_table_upgrade_mdev29481_myisam_aria.inc + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/ctype_ucs.result b/mysql-test/main/ctype_ucs.result index 804e8ff4c68..ce0d695797b 100644 --- a/mysql-test/main/ctype_ucs.result +++ b/mysql-test/main/ctype_ucs.result @@ -6398,6 +6398,17 @@ DEALLOCATE PREPARE stmt; # End of 10.2 tests # # +# Start of 10.3 tests +# +# +# MDEV-14983 Wrong error message with SET sql_mode=sha2(ucs2_value) +# +SET sql_mode=sha2(CONVERT('a' USING ucs2),0); +ERROR 42000: Variable 'sql_mode' can't be set to the value of '022a6979e6dab7aa5ae4c3e5e45f7e977112a7e63593820dbec1ec738a24f93c' +# +# End of 10.3 tests +# +# # Start of 10.4 tests # # diff --git a/mysql-test/main/ctype_ucs.test b/mysql-test/main/ctype_ucs.test index 8f566fe831e..77a717c11a9 100644 --- a/mysql-test/main/ctype_ucs.test +++ b/mysql-test/main/ctype_ucs.test @@ -1096,6 +1096,21 @@ DEALLOCATE PREPARE stmt; --echo # --echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-14983 Wrong error message with SET sql_mode=sha2(ucs2_value) +--echo # + +--error ER_WRONG_VALUE_FOR_VAR +SET sql_mode=sha2(CONVERT('a' USING ucs2),0); + +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # --echo # Start of 10.4 tests --echo # diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index b6310f1a09f..3576c1fa356 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1316,3 +1316,19 @@ a a 4 4 6 6 drop table t1,t2,t3; +# +# MDEV-16549: Server crashes in Item_field::fix_fields on query with +# view and subquery, Assertion `context' failed, Assertion `field' failed +# +CREATE TABLE t1 (a DECIMAL, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE VIEW v1 AS SELECT * FROM ( SELECT * FROM t1 WHERE a <> RAND() ) sq; +SELECT * FROM v1 WHERE b > 0; +a b +1 1 +2 2 +DROP VIEW v1; +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index 799de90d30e..67ea3c0d39a 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1124,3 +1124,23 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; + + +--echo # +--echo # MDEV-16549: Server crashes in Item_field::fix_fields on query with +--echo # view and subquery, Assertion `context' failed, Assertion `field' failed +--echo # + +CREATE TABLE t1 (a DECIMAL, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); # optional +CREATE VIEW v1 AS SELECT * FROM ( SELECT * FROM t1 WHERE a <> RAND() ) sq; + +SELECT * FROM v1 WHERE b > 0; + +# Cleanup +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/explain.result b/mysql-test/main/explain.result index bc3c53d01d3..8a9fd3c5390 100644 --- a/mysql-test/main/explain.result +++ b/mysql-test/main/explain.result @@ -413,3 +413,49 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 system NULL NULL NULL NULL 1 drop table t1, t2; # End of 10.1 tests +# +# End of 10.2 test +# +# +# MDEV-25564: Server crashed on running some EXPLAIN statements +# +EXPLAIN (SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1249 Select 3 was reduced during optimization +# +# MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN +SELECT * +FROM t1 +WHERE +a IN (SELECT a FROM t1 +UNION +SELECT a FROM t1 ORDER BY (SELECT a)) +UNION +SELECT * FROM t1 ORDER BY (SELECT a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +5 UNION t1 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union1,5> ALL NULL NULL NULL NULL NULL Using filesort +6 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +drop table t1; +explain +VALUES ( (VALUES (2))) UNION VALUES ( (SELECT 3)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +5 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1249 Select 4 was reduced during optimization diff --git a/mysql-test/main/explain.test b/mysql-test/main/explain.test index 0fa4a5af215..fc789601788 100644 --- a/mysql-test/main/explain.test +++ b/mysql-test/main/explain.test @@ -339,3 +339,34 @@ explain replace into t2 select 100, (select a from t1); drop table t1, t2; --echo # End of 10.1 tests + +--echo # +--echo # End of 10.2 test +--echo # + +--echo # +--echo # MDEV-25564: Server crashed on running some EXPLAIN statements +--echo # + +EXPLAIN (SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); + +--echo # +--echo # MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); + +EXPLAIN +SELECT * +FROM t1 +WHERE + a IN (SELECT a FROM t1 + UNION + SELECT a FROM t1 ORDER BY (SELECT a)) +UNION + SELECT * FROM t1 ORDER BY (SELECT a); +drop table t1; + +explain +VALUES ( (VALUES (2))) UNION VALUES ( (SELECT 3)); diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 20e9d95eb70..23ecbcdd2c8 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -2549,5 +2549,9 @@ Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' DROP TABLE t1; # +# MDEV-29678 Valgrind/MSAN uninitialised value errors upon PS with ALTER under ONLY_FULL_GROUP_BY +# +SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time'; +# # End of 10.3 tests # diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index 9663a48fe57..b5e9b5d93df 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -1785,5 +1785,11 @@ SELECT DISTINCT a IN ( COLLATION (AVG ('x'))) FROM t1 ; DROP TABLE t1; --echo # +--echo # MDEV-29678 Valgrind/MSAN uninitialised value errors upon PS with ALTER under ONLY_FULL_GROUP_BY +--echo # + +SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time'; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result index 2f2b01116d2..eccefb80cc9 100644 --- a/mysql-test/main/grant2.result +++ b/mysql-test/main/grant2.result @@ -707,6 +707,7 @@ a # SHOW COLUMNS FROM t1; Field Type Null Key Default Extra +a int(11) YES NULL SHOW KEYS FROM t3; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t3 0 PRIMARY 1 a A 0 NULL NULL BTREE diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result index 3947fd72bd4..a638671ca80 100644 --- a/mysql-test/main/grant5.result +++ b/mysql-test/main/grant5.result @@ -246,6 +246,60 @@ connection default; disconnect con1; drop database db1; drop user foo@localhost; +# +# MDEV-28455: CREATE TEMPORARY TABLES privilege +# is insufficient for SHOW COLUMNS +# +create database db; +create user foo@localhost; +create user bar@localhost; +create user buz@localhost; +grant create temporary tables on db.* to foo@localhost; +grant create temporary tables on db.* to bar@localhost; +connect con1,localhost,foo,,db; +create temporary table tmp (a int, key(a)); +show tables; +Tables_in_db +show full tables; +Tables_in_db Table_type +show table status; +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 Max_index_length Temporary +show index in tmp; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +tmp 1 a 1 a A NULL NULL NULL YES BTREE +show columns in tmp; +Field Type Null Key Default Extra +a int(11) YES MUL NULL +show full columns in tmp; +Field Type Collation Null Key Default Extra Privileges Comment +a int(11) NULL YES MUL NULL select,insert,update,references +# we don't expect to show temporary tables in information_schema.columns +select * from information_schema.columns where table_schema='db'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION +disconnect con1; +connect con1,localhost,bar,,db; +show full columns in tmp; +ERROR 42000: SELECT command denied to user 'bar'@'localhost' for table `db`.`tmp` +disconnect con1; +connection default; +grant select on db.* to bar@localhost; +connect con1,localhost,bar,,db; +show grants for current_user; +Grants for bar@localhost +GRANT USAGE ON *.* TO `bar`@`localhost` +GRANT SELECT, CREATE TEMPORARY TABLES ON `db`.* TO `bar`@`localhost` +show full columns in tmp; +ERROR 42S02: Table 'db.tmp' doesn't exist +disconnect con1; +connect con1,localhost,buz,,; +show columns in db.tmp; +ERROR 42000: SELECT command denied to user 'buz'@'localhost' for table `db`.`tmp` +disconnect con1; +connection default; +drop database db; +drop user foo@localhost; +drop user bar@localhost; +drop user buz@localhost; # End of 10.3 tests create user u1@h identified with 'mysql_native_password' using 'pwd'; ERROR HY000: Password hash should be a 41-digit hexadecimal number diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test index 3a972efa0fc..4c95b30f0ab 100644 --- a/mysql-test/main/grant5.test +++ b/mysql-test/main/grant5.test @@ -209,6 +209,58 @@ show create view t_v; --disconnect con1 drop database db1; drop user foo@localhost; +--echo # +--echo # MDEV-28455: CREATE TEMPORARY TABLES privilege +--echo # is insufficient for SHOW COLUMNS +--echo # + +create database db; +create user foo@localhost; +create user bar@localhost; +create user buz@localhost; +grant create temporary tables on db.* to foo@localhost; +grant create temporary tables on db.* to bar@localhost; + +--connect (con1,localhost,foo,,db) +create temporary table tmp (a int, key(a)); +show tables; +show full tables; +show table status; +show index in tmp; +show columns in tmp; +show full columns in tmp; +--echo # we don't expect to show temporary tables in information_schema.columns +select * from information_schema.columns where table_schema='db'; +--disconnect con1 + +--connect (con1,localhost,bar,,db) +# User doesn't have `select` privilege on table +--error ER_TABLEACCESS_DENIED_ERROR +show full columns in tmp; + +--disconnect con1 + +--connection default +grant select on db.* to bar@localhost; + +--connect (con1,localhost,bar,,db) +# Table doesn't exist for this session +show grants for current_user; +--error ER_NO_SUCH_TABLE +show full columns in tmp; +--disconnect con1 + +--connect (con1,localhost,buz,,) +--error ER_TABLEACCESS_DENIED_ERROR +show columns in db.tmp; +--disconnect con1 + +--connection default +# Cleanup +drop database db; +drop user foo@localhost; +drop user bar@localhost; +drop user buz@localhost; --echo # End of 10.3 tests diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index e2155b522a3..f1c1b1b8084 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -947,44 +947,6 @@ disconnect con1; connection default; drop table mysql.global_priv; rename table mysql.global_priv_bak to mysql.global_priv; -# -# MDEV-28727 ALTER TABLE ALGORITHM=NOCOPY does not work after upgrade -# -create or replace table pet4 ( -build_time double(18, 7) default null, -key idx1 (build_time) -) engine innodb; -check table pet4; -Table Op Msg_type Msg_text -test.pet4 check error Table rebuild required. Please do "ALTER TABLE `pet4` FORCE" or dump/reload to fix it! -check table pet4 for upgrade; -Table Op Msg_type Msg_text -test.pet4 check error Table rebuild required. Please do "ALTER TABLE `pet4` FORCE" or dump/reload to fix it! -alter table pet4 add i1 int, algorithm=nocopy; -ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE -# Running mysqlcheck -test.pet4 -error : Table rebuild required. Please do "ALTER TABLE `pet4` FORCE" or dump/reload to fix it! - -Repairing tables -check table pet4; -Table Op Msg_type Msg_text -test.pet4 check status OK -alter table pet4 add i1 int, algorithm=nocopy; -create or replace table pet4 ( -build_time double(18, 7) default null, -key idx1 (build_time) -) engine innodb; -alter table pet4 add i1 int, algorithm=nocopy; -ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE -# Running mysql_upgrade -test.pet4 -error : Table rebuild required. Please do "ALTER TABLE `pet4` FORCE" or dump/reload to fix it! -check table pet4; -Table Op Msg_type Msg_text -test.pet4 check status OK -alter table pet4 add i1 int, algorithm=nocopy; -drop table pet4; # End of 10.4 tests # # Check that mysql_upgrade can be run on mysqldump diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test index 3d7f9d152c2..fa79869fb26 100644 --- a/mysql-test/main/mysql_upgrade.test +++ b/mysql-test/main/mysql_upgrade.test @@ -472,47 +472,6 @@ drop table mysql.global_priv; rename table mysql.global_priv_bak to mysql.global_priv; --remove_file $MYSQLD_DATADIR/mysql_upgrade_info ---echo # ---echo # MDEV-28727 ALTER TABLE ALGORITHM=NOCOPY does not work after upgrade ---echo # -create or replace table pet4 ( - build_time double(18, 7) default null, - key idx1 (build_time) -) engine innodb; - ---remove_file $MYSQLD_DATADIR/test/pet4.frm ---copy_file std_data/mdev-28727-pet4.frm $MYSQLD_DATADIR/test/pet4.frm - -check table pet4; -check table pet4 for upgrade; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter table pet4 add i1 int, algorithm=nocopy; - ---echo # Running mysqlcheck ---exec $MYSQL_CHECK --auto-repair --databases test 2>&1 -check table pet4; -alter table pet4 add i1 int, algorithm=nocopy; - -create or replace table pet4 ( - build_time double(18, 7) default null, - key idx1 (build_time) -) engine innodb; - ---remove_file $MYSQLD_DATADIR/test/pet4.frm ---copy_file std_data/mdev-28727-pet4.frm $MYSQLD_DATADIR/test/pet4.frm - ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter table pet4 add i1 int, algorithm=nocopy; - ---echo # Running mysql_upgrade ---exec $MYSQL_UPGRADE --silent 2>&1 -file_exists $MYSQLD_DATADIR/mysql_upgrade_info; -check table pet4; -alter table pet4 add i1 int, algorithm=nocopy; - ---remove_file $MYSQLD_DATADIR/mysql_upgrade_info -drop table pet4; - --echo # End of 10.4 tests # diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index ddd20cfb5f5..2be21fe261d 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5621,6 +5621,104 @@ a.a a.b DEALLOCATE PREPARE stmt; DROP PROCEDURE p1; # +# MDEV-16128: Server crash in Item_func::print_op on 2nd execution of PS +# +CREATE TABLE t1 (a varchar(10)); +CREATE TABLE t2 (b varchar(10) CHARACTER SET utf8 ); +CREATE TABLE t3 (c varchar(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES ('b'); +INSERT INTO t2 VALUES ('b'); +INSERT INTO t3 VALUES ('b'); +PREPARE stmt FROM "SELECT t1.* FROM (t1 JOIN t2 ON (t2.b = t1.a)) WHERE (EXISTS (SELECT 1 FROM t3 WHERE t3.c = t1.a))"; +EXECUTE stmt; +a +b +# Without the patch second execution of the prepared statement +# would lead to server crash. +EXECUTE stmt; +a +b +# Clean up +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2, t3; +CREATE TABLE t1 (a varchar(10)); +CREATE TABLE t2 (b varchar(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES ('b'); +INSERT INTO t2 VALUES ('b'); +PREPARE stmt FROM 'SELECT STRAIGHT_JOIN 1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a)'; +EXECUTE stmt; +1 +1 +# Without the patch second execution of the prepared statement +# would lead to server crash. +EXECUTE stmt; +1 +1 +# Clean up +DEALLOCATE PREPARE stmt; +# Check that EXECUTE USING is run correctly +PREPARE stmt FROM 'SELECT 300 FROM t1 WHERE EXISTS (SELECT 100 FROM t2 WHERE t2.b = ?)'; +EXECUTE stmt USING 'b'; +300 +300 +EXECUTE stmt USING 'b'; +300 +300 +EXECUTE stmt USING 'd'; +300 +EXECUTE stmt USING 'd'; +300 +EXECUTE stmt USING _binary 'b'; +300 +300 +EXECUTE stmt USING _binary 'b'; +300 +300 +EXECUTE stmt USING _binary 'B'; +300 +300 +EXECUTE stmt USING 'B'; +300 +300 +EXECUTE stmt USING _binary 'd'; +300 +EXECUTE stmt USING _binary 'd'; +300 +EXECUTE stmt USING _ucs2 'b'; +300 +300 +EXECUTE stmt USING _ucs2 'b'; +300 +300 +EXECUTE stmt USING _ucs2 'd'; +300 +EXECUTE stmt USING _ucs2 'd'; +300 +EXECUTE stmt USING _latin1 'b'; +300 +300 +EXECUTE stmt USING _latin1 'b'; +300 +300 +EXECUTE stmt USING _latin1 'd'; +300 +EXECUTE stmt USING _latin1 'd'; +300 +CREATE TABLE t3 (c VARCHAR(10) CHARACTER SET ucs2); +INSERT INTO t3 VALUES ('b'); +PREPARE stmt FROM 'SELECT 300 FROM t1 WHERE EXISTS (SELECT 100 FROM t3 WHERE t3.c = ?)'; +EXECUTE stmt USING 'b'; +300 +300 +EXECUTE stmt USING 'b'; +300 +300 +EXECUTE stmt USING 'd'; +300 +EXECUTE stmt USING 'd'; +300 +DROP TABLE t1, t2, t3; +# # MDEV-19263: Server crashes in mysql_handle_single_derived # upon 2nd execution of PS # diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 062d729b2cc..97624925519 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -5039,6 +5039,77 @@ DEALLOCATE PREPARE stmt; DROP PROCEDURE p1; --echo # +--echo # MDEV-16128: Server crash in Item_func::print_op on 2nd execution of PS +--echo # + +CREATE TABLE t1 (a varchar(10)); +CREATE TABLE t2 (b varchar(10) CHARACTER SET utf8 ); +CREATE TABLE t3 (c varchar(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES ('b'); +INSERT INTO t2 VALUES ('b'); +INSERT INTO t3 VALUES ('b'); + +PREPARE stmt FROM "SELECT t1.* FROM (t1 JOIN t2 ON (t2.b = t1.a)) WHERE (EXISTS (SELECT 1 FROM t3 WHERE t3.c = t1.a))"; +EXECUTE stmt; +--echo # Without the patch second execution of the prepared statement +--echo # would lead to server crash. +EXECUTE stmt; +--echo # Clean up +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2, t3; + +CREATE TABLE t1 (a varchar(10)); +CREATE TABLE t2 (b varchar(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES ('b'); +INSERT INTO t2 VALUES ('b'); +PREPARE stmt FROM 'SELECT STRAIGHT_JOIN 1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a)'; +EXECUTE stmt; +--echo # Without the patch second execution of the prepared statement +--echo # would lead to server crash. +EXECUTE stmt; + +--echo # Clean up +DEALLOCATE PREPARE stmt; + +--echo # Check that EXECUTE USING is run correctly +PREPARE stmt FROM 'SELECT 300 FROM t1 WHERE EXISTS (SELECT 100 FROM t2 WHERE t2.b = ?)'; +EXECUTE stmt USING 'b'; +EXECUTE stmt USING 'b'; + +EXECUTE stmt USING 'd'; +EXECUTE stmt USING 'd'; + +EXECUTE stmt USING _binary 'b'; +EXECUTE stmt USING _binary 'b'; + +EXECUTE stmt USING _binary 'B'; +EXECUTE stmt USING 'B'; + +EXECUTE stmt USING _binary 'd'; +EXECUTE stmt USING _binary 'd'; + +EXECUTE stmt USING _ucs2 'b'; +EXECUTE stmt USING _ucs2 'b'; + +EXECUTE stmt USING _ucs2 'd'; +EXECUTE stmt USING _ucs2 'd'; + +EXECUTE stmt USING _latin1 'b'; +EXECUTE stmt USING _latin1 'b'; + +EXECUTE stmt USING _latin1 'd'; +EXECUTE stmt USING _latin1 'd'; + +CREATE TABLE t3 (c VARCHAR(10) CHARACTER SET ucs2); +INSERT INTO t3 VALUES ('b'); +PREPARE stmt FROM 'SELECT 300 FROM t1 WHERE EXISTS (SELECT 100 FROM t3 WHERE t3.c = ?)'; +EXECUTE stmt USING 'b'; +EXECUTE stmt USING 'b'; +EXECUTE stmt USING 'd'; +EXECUTE stmt USING 'd'; +DROP TABLE t1, t2, t3; + +--echo # --echo # MDEV-19263: Server crashes in mysql_handle_single_derived --echo # upon 2nd execution of PS --echo # diff --git a/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.MAD b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.MAD Binary files differnew file mode 100644 index 00000000000..3dcc005ec0d --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.MAD diff --git a/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.MAI b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.MAI Binary files differnew file mode 100644 index 00000000000..d11da43bf2b --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.MAI diff --git a/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.frm b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.frm Binary files differnew file mode 100644 index 00000000000..81d7672c405 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_aria.frm diff --git a/mysql-test/std_data/mdev-28727-pet4.frm b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_innodb.frm Binary files differindex 3ff86d1dca1..3ff86d1dca1 100644 --- a/mysql-test/std_data/mdev-28727-pet4.frm +++ b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_innodb.frm diff --git a/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.MYD b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.MYD new file mode 100644 index 00000000000..e69de29bb2d --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.MYD diff --git a/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.MYI b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.MYI Binary files differnew file mode 100644 index 00000000000..d36f27ac0cf --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.MYI diff --git a/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.frm b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.frm Binary files differnew file mode 100644 index 00000000000..afffafabbb1 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev29481_100104_myisam.frm diff --git a/mysql-test/suite/encryption/r/filekeys_secret_openssl_rand_128bits.result b/mysql-test/suite/encryption/r/filekeys_secret_openssl_rand_128bits.result new file mode 100644 index 00000000000..880245c7a09 --- /dev/null +++ b/mysql-test/suite/encryption/r/filekeys_secret_openssl_rand_128bits.result @@ -0,0 +1,17 @@ +create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) NOT NULL, + `b` char(200) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `encrypted`=yes `encryption_key_id`=1 +insert t1 values (12345, repeat('1234567890', 20)); +alter table t1 encryption_key_id=2; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) NOT NULL, + `b` char(200) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `encrypted`=yes `encryption_key_id`=2 +drop table t1; +# Test checks if opening an too large secret does not crash the server. diff --git a/mysql-test/suite/encryption/r/filekeys_secret_too_long.result b/mysql-test/suite/encryption/r/filekeys_secret_too_long.result new file mode 100644 index 00000000000..bd11e8d925e --- /dev/null +++ b/mysql-test/suite/encryption/r/filekeys_secret_too_long.result @@ -0,0 +1,10 @@ +call mtr.add_suppression("the filekey is too long"); +call mtr.add_suppression("Plugin 'file_key_management' init function returned error"); +call mtr.add_suppression("Plugin 'file_key_management' registration.*failed"); +FOUND 1 /the filekey is too long/ in mysqld.1.err +create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; +ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") +select plugin_status from information_schema.plugins +where plugin_name = 'file_key_management'; +plugin_status +# Test checks if opening an too large secret does not crash the server. diff --git a/mysql-test/suite/encryption/t/filekeys-data-too-long.key b/mysql-test/suite/encryption/t/filekeys-data-too-long.key new file mode 100644 index 00000000000..ba1624fb324 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys-data-too-long.key @@ -0,0 +1,4 @@ +secretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecret +secretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecret +secretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecret + diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.enc b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.enc new file mode 100644 index 00000000000..3257ff7d6de --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.enc @@ -0,0 +1,4 @@ +Salted__40-6L sK?p\am8N?q n<*g(|F/! + kok6y7t67D#g洄ʗԣiyu*i#ƈ82#6 .C8;7Bԣ +0 / +w0w"xԱQu04xkj{W3C5՜ᔪP$=Ҳ
\ No newline at end of file diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.key b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.key new file mode 100644 index 00000000000..bba639aeaac --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.key @@ -0,0 +1 @@ +c9518399cbec2b5edf773e06d1b934b90ec0f46ae455b8f1e001b5629ef31a513b83e676bf654c08ba98659461410e5e040e46237a7d50b40bd9bb90576f841275506e61523e5e9a0beb7641127ed2d946395b6fee7ff5263a9019cbe71bd907bf1ac6365940fa391086830a4e6c1d2972b99505467ef31cfb46d0cb7ab8f4f1 diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.opt b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.opt new file mode 100644 index 00000000000..9dee47bb96f --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.opt @@ -0,0 +1,3 @@ +--loose-file-key-management-filekey=FILE:$MTR_SUITE_DIR/t/filekeys_secret_openssl_rand_128bits.key +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys_secret_openssl_rand_128bits.enc + diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.test b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.test new file mode 100644 index 00000000000..60718d21a10 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.test @@ -0,0 +1,13 @@ +-- source include/have_innodb.inc +-- source filekeys_plugin.inc + +create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; +show create table t1; +insert t1 values (12345, repeat('1234567890', 20)); + +alter table t1 encryption_key_id=2; +show create table t1; + +drop table t1; + +--echo # Test checks if opening an too large secret does not crash the server. diff --git a/mysql-test/suite/encryption/t/filekeys_secret_too_long.opt b/mysql-test/suite/encryption/t/filekeys_secret_too_long.opt new file mode 100644 index 00000000000..c3f95019f2a --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_too_long.opt @@ -0,0 +1,3 @@ +--loose-file-key-management-filekey=FILE:$MTR_SUITE_DIR/t/filekeys-data-too-long.key +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-data.enc + diff --git a/mysql-test/suite/encryption/t/filekeys_secret_too_long.test b/mysql-test/suite/encryption/t/filekeys_secret_too_long.test new file mode 100644 index 00000000000..0032e94de37 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_too_long.test @@ -0,0 +1,4 @@ +let SEARCH_PATTERN=the filekey is too long; +source filekeys_badtest.inc; + +--echo # Test checks if opening an too large secret does not crash the server. diff --git a/mysql-test/suite/innodb/r/alter_table_upgrade.result b/mysql-test/suite/innodb/r/alter_table_upgrade.result new file mode 100644 index 00000000000..eebabd561ca --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_table_upgrade.result @@ -0,0 +1,36 @@ +# +# Start of 10.4 tests +# +# +# MDEV-29481 mariadb-upgrade prints confusing statement +# +CREATE TABLE pet4 ( +build_time double(18,7) DEFAULT NULL, +KEY idx1 (build_time)) ENGINE=InnoDB; +FLUSH TABLES; +SHOW CREATE TABLE pet4; +Table Create Table +pet4 CREATE TABLE `pet4` ( + `build_time` double(18,7) DEFAULT NULL, + KEY `idx1` (`build_time`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci +CHECK TABLE pet4 FOR UPGRADE; +Table Op Msg_type Msg_text +test.pet4 check status OK +ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=INSTANT; +DROP TABLE pet4; +CREATE TABLE pet4 ( +build_time double(18,7) DEFAULT NULL, +KEY idx1 (build_time)) ENGINE=InnoDB; +FLUSH TABLES; +SHOW CREATE TABLE pet4; +Table Create Table +pet4 CREATE TABLE `pet4` ( + `build_time` double(18,7) DEFAULT NULL, + KEY `idx1` (`build_time`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci +ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=NOCOPY; +DROP TABLE pet4; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/innodb/t/alter_table_upgrade.test b/mysql-test/suite/innodb/t/alter_table_upgrade.test new file mode 100644 index 00000000000..cd058aeee3c --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_table_upgrade.test @@ -0,0 +1,36 @@ +--source include/have_innodb.inc + +let $datadir=`select @@datadir`; + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-29481 mariadb-upgrade prints confusing statement +--echo # + +CREATE TABLE pet4 ( + build_time double(18,7) DEFAULT NULL, + KEY idx1 (build_time)) ENGINE=InnoDB; +FLUSH TABLES; +remove_file $datadir/test/pet4.frm; +copy_file std_data/mysql_upgrade/mdev29481_100104_innodb.frm $datadir/test/pet4.frm; +SHOW CREATE TABLE pet4; +CHECK TABLE pet4 FOR UPGRADE; +ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=INSTANT; +DROP TABLE pet4; + +CREATE TABLE pet4 ( + build_time double(18,7) DEFAULT NULL, + KEY idx1 (build_time)) ENGINE=InnoDB; +FLUSH TABLES; +remove_file $datadir/test/pet4.frm; +copy_file std_data/mysql_upgrade/mdev29481_100104_innodb.frm $datadir/test/pet4.frm; +SHOW CREATE TABLE pet4; +ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=NOCOPY; +DROP TABLE pet4; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index 1c9a0f7918f..2de08657a6e 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -772,4 +772,15 @@ UNLOCK TABLES; ALTER TABLE t1 DISCARD TABLESPACE; ALTER TABLE t2 IMPORT TABLESPACE; DROP TABLE t2, t1; +# +# MDEV-29778 Having Unique index interference with MATCH +# from a FULLTEXT +# +CREATE TABLE t1(f1 VARCHAR(100), FULLTEXT(f1), +UNIQUE INDEX(f1))ENGINE=InnoDB; +INSERT INTO t1 VALUES("test"); +SELECT f1, MATCH(f1) AGAINST ("test" IN BOOLEAN MODE) FROM t1; +f1 MATCH(f1) AGAINST ("test" IN BOOLEAN MODE) +test 0.000000001885928302414186 +DROP TABLE t1; # End of 10.3 tests diff --git a/mysql-test/suite/innodb_fts/t/fulltext.test b/mysql-test/suite/innodb_fts/t/fulltext.test index a45ca5a8d0d..3ddc1856b7f 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext.test +++ b/mysql-test/suite/innodb_fts/t/fulltext.test @@ -792,4 +792,14 @@ ALTER TABLE t2 IMPORT TABLESPACE; --enable_warnings DROP TABLE t2, t1; +--echo # +--echo # MDEV-29778 Having Unique index interference with MATCH +--echo # from a FULLTEXT +--echo # +CREATE TABLE t1(f1 VARCHAR(100), FULLTEXT(f1), + UNIQUE INDEX(f1))ENGINE=InnoDB; +INSERT INTO t1 VALUES("test"); +SELECT f1, MATCH(f1) AGAINST ("test" IN BOOLEAN MODE) FROM t1; +DROP TABLE t1; + --echo # End of 10.3 tests diff --git a/mysql-test/suite/roles/role_grant_propagate-29458.result b/mysql-test/suite/roles/role_grant_propagate.result index 88d3c0e38fb..7804b7b7a3c 100644 --- a/mysql-test/suite/roles/role_grant_propagate-29458.result +++ b/mysql-test/suite/roles/role_grant_propagate.result @@ -1,3 +1,6 @@ +# +# MDEV-29458 Role grant commands do not propagate all grants +# create user foo; create database some_db; create table some_db.t1 (a int, b int, secret int); @@ -134,3 +137,31 @@ grant select(user) on mysql.user to test_role2; drop role test_role1, test_role2; create role test_role1; drop role test_role1; +# +# MDEV-29851 Cached role privileges are not invalidated when needed +# +create role admin; +create role student; +create database crm; +grant create on crm.* to admin; +grant select on crm.* to student; +create user intern@localhost; +grant student to intern@localhost; +set default role student for intern@localhost; +connect con1, localhost, intern; +use crm; +disconnect con1; +connection default; +grant admin to student; +connect con1, localhost, intern; +use crm; +create table t1 (a int); +disconnect con1; +connection default; +drop user intern@localhost; +drop role student; +drop role admin; +drop database crm; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/roles/role_grant_propagate-29458.test b/mysql-test/suite/roles/role_grant_propagate.test index 1b0906dce25..bf20bc00809 100644 --- a/mysql-test/suite/roles/role_grant_propagate-29458.test +++ b/mysql-test/suite/roles/role_grant_propagate.test @@ -1,5 +1,9 @@ --source include/not_embedded.inc +--echo # +--echo # MDEV-29458 Role grant commands do not propagate all grants +--echo # + create user foo; create database some_db; create table some_db.t1 (a int, b int, secret int); @@ -161,3 +165,37 @@ drop role test_role1, test_role2; create role test_role1; drop role test_role1; + +--echo # +--echo # MDEV-29851 Cached role privileges are not invalidated when needed +--echo # +create role admin; +create role student; +create database crm; +grant create on crm.* to admin; +grant select on crm.* to student; +create user intern@localhost; +grant student to intern@localhost; +set default role student for intern@localhost; + +connect con1, localhost, intern; +use crm; +disconnect con1; + +connection default; +grant admin to student; + +connect con1, localhost, intern; +use crm; +create table t1 (a int); +disconnect con1; + +connection default; +drop user intern@localhost; +drop role student; +drop role admin; +drop database crm; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/sql_sequence/default.result b/mysql-test/suite/sql_sequence/default.result index cc22fb42464..eecef1d3527 100644 --- a/mysql-test/suite/sql_sequence/default.result +++ b/mysql-test/suite/sql_sequence/default.result @@ -193,3 +193,105 @@ INSERT INTO t1 () values (); EXECUTE stmt; DROP TABLE t1; DROP SEQUENCE s; +# +# MDEV-29540 Incorrect sequence values in INSERT SELECT +# +CREATE SEQUENCE s1; +CREATE TABLE t1 ( +a BIGINT UNSIGNED NOT NULL PRIMARY KEY +DEFAULT (NEXT VALUE FOR s1), +b CHAR(1) NOT NULL +); +INSERT INTO t1 (b) VALUES ('a'); +INSERT INTO t1 (b) VALUES ('b'), ('c'); +INSERT INTO t1 (b) VALUES ('d'); +INSERT INTO t1 (b) SELECT c FROM ( +SELECT 'e' as c +UNION +SELECT 'f' + UNION +SELECT 'g' +) der; +SELECT a, b FROM t1; +a b +1 a +2 b +3 c +4 d +5 e +6 f +7 g +ALTER SEQUENCE s1 RESTART; +INSERT INTO t1 (b) SELECT c FROM ( +SELECT 'a' as c +UNION +SELECT 'b' + UNION +SELECT 'c' + UNION +SELECT 'd' + UNION +SELECT 'e' + UNION +SELECT 'f' + UNION +SELECT 'g' +) der; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ALTER SEQUENCE s1 RESTART; +INSERT IGNORE INTO t1 (b) SELECT c FROM ( +SELECT 'a' as c +UNION +SELECT 'b' + UNION +SELECT 'c' + UNION +SELECT 'd' + UNION +SELECT 'e' + UNION +SELECT 'f' + UNION +SELECT 'g' +) der; +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +Warning 1062 Duplicate entry '2' for key 'PRIMARY' +Warning 1062 Duplicate entry '3' for key 'PRIMARY' +Warning 1062 Duplicate entry '4' for key 'PRIMARY' +Warning 1062 Duplicate entry '5' for key 'PRIMARY' +Warning 1062 Duplicate entry '6' for key 'PRIMARY' +Warning 1062 Duplicate entry '7' for key 'PRIMARY' +SELECT a, b FROM t1; +a b +1 a +2 b +3 c +4 d +5 e +6 f +7 g +INSERT IGNORE INTO t1 (b) SELECT c FROM ( +SELECT 'h' as c +UNION +SELECT 'i' + UNION +SELECT 'j' +) der; +SELECT a, b FROM t1; +a b +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 h +9 i +10 j +DROP TABLE t1; +DROP SEQUENCE s1; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/sql_sequence/default.test b/mysql-test/suite/sql_sequence/default.test index e7c13211013..28eb71e39cc 100644 --- a/mysql-test/suite/sql_sequence/default.test +++ b/mysql-test/suite/sql_sequence/default.test @@ -135,3 +135,83 @@ EXECUTE stmt; # Cleanup DROP TABLE t1; DROP SEQUENCE s; + +--echo # +--echo # MDEV-29540 Incorrect sequence values in INSERT SELECT +--echo # + +CREATE SEQUENCE s1; +CREATE TABLE t1 ( + a BIGINT UNSIGNED NOT NULL PRIMARY KEY + DEFAULT (NEXT VALUE FOR s1), + b CHAR(1) NOT NULL +); + +INSERT INTO t1 (b) VALUES ('a'); +INSERT INTO t1 (b) VALUES ('b'), ('c'); +INSERT INTO t1 (b) VALUES ('d'); +INSERT INTO t1 (b) SELECT c FROM ( + SELECT 'e' as c + UNION + SELECT 'f' + UNION + SELECT 'g' +) der; + +SELECT a, b FROM t1; + +ALTER SEQUENCE s1 RESTART; + +--error ER_DUP_ENTRY +INSERT INTO t1 (b) SELECT c FROM ( + SELECT 'a' as c + UNION + SELECT 'b' + UNION + SELECT 'c' + UNION + SELECT 'd' + UNION + SELECT 'e' + UNION + SELECT 'f' + UNION + SELECT 'g' +) der; + +ALTER SEQUENCE s1 RESTART; + +INSERT IGNORE INTO t1 (b) SELECT c FROM ( + SELECT 'a' as c + UNION + SELECT 'b' + UNION + SELECT 'c' + UNION + SELECT 'd' + UNION + SELECT 'e' + UNION + SELECT 'f' + UNION + SELECT 'g' +) der; + +SELECT a, b FROM t1; + +INSERT IGNORE INTO t1 (b) SELECT c FROM ( + SELECT 'h' as c + UNION + SELECT 'i' + UNION + SELECT 'j' +) der; + +SELECT a, b FROM t1; + +DROP TABLE t1; +DROP SEQUENCE s1; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/versioning/r/misc.result b/mysql-test/suite/versioning/r/misc.result new file mode 100644 index 00000000000..398e3b8be70 --- /dev/null +++ b/mysql-test/suite/versioning/r/misc.result @@ -0,0 +1,27 @@ +set time_zone='+00:00'; +# +# MDEV-29750 triggers can modify history +# +set sql_mode='', timestamp=unix_timestamp('2010-10-10 10:10:10'); +create table t (a int, b int as (a+1), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; +insert into t values (1,1, '2022-01-01','2023-01-01'),(2,2, '2022-02-02','2023-02-02'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't' has been ignored +Warning 1906 The value specified for generated column 's' in table 't' has been ignored +Warning 1906 The value specified for generated column 'e' in table 't' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't' has been ignored +Warning 1906 The value specified for generated column 's' in table 't' has been ignored +Warning 1906 The value specified for generated column 'e' in table 't' has been ignored +create trigger tr before insert on t for each row set new.b=1, new.s = '2022-03-03', new.e = '2023-03-03'; +insert into t (a) values (3),(4); +select * from t for system_time all; +a b s e +1 2 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +2 3 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +3 4 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +4 5 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +drop table t; +set sql_mode=default, timestamp=default; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 91b3e93bc86..e99afef738d 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -1537,6 +1537,107 @@ create trigger tr before insert on t for each row update tcount set c = c + 1; insert into t select * from t; drop table tcount, t; # +# MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref and Assertion `table_ref->table || table_ref->view' in Field_iterator_table_ref::set_field_iterator +# +create table t1 (i int); +create table t2 (i int); +alter table t1 partition by system_time +interval (select i from t2) day (partition p1 history, partition pn current); +ERROR 42000: INTERVAL does not support subqueries or stored functions +drop table t1; +create table t1 (id int) with system versioning +partition by system_time +interval (select i from t2) day (partition p1 history, partition pn current); +ERROR 42000: INTERVAL does not support subqueries or stored functions +create table t1 (id int) with system versioning +partition by system_time +interval "hello" day (partition p1 history, partition pn current); +ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL' +create table t1 (id int) with system versioning +partition by system_time +interval 3.893 day (partition p1 history, partition pn current); +drop table t1, t2; +create table t1 (id int) with system versioning +partition by system_time interval "3-11" year_month (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3-11' YEAR_MONTH +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "3 11" day_hour (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3 11' DAY_HOUR +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "3 11:12" day_minute (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3 11:12' DAY_MINUTE +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "3 11:12:13" day_second (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3 11:12:13' DAY_SECOND +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "11:12" hour_minute (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '11:12' HOUR_MINUTE +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "11:12:13" hour_second (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '11:12:13' HOUR_SECOND +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "12:13" minute_second (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '12:13' MINUTE_SECOND +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "12:13.123" minute_microsecond (partition p1 history, partition pn current); +ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL' +# # End of 10.3 tests # # diff --git a/mysql-test/suite/versioning/t/misc.test b/mysql-test/suite/versioning/t/misc.test new file mode 100644 index 00000000000..dce1e0deced --- /dev/null +++ b/mysql-test/suite/versioning/t/misc.test @@ -0,0 +1,20 @@ +# +# simple tests that don't need to be run in multiple various combinations +# +set time_zone='+00:00'; + +--echo # +--echo # MDEV-29750 triggers can modify history +--echo # +set sql_mode='', timestamp=unix_timestamp('2010-10-10 10:10:10'); +create table t (a int, b int as (a+1), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; +insert into t values (1,1, '2022-01-01','2023-01-01'),(2,2, '2022-02-02','2023-02-02'); +create trigger tr before insert on t for each row set new.b=1, new.s = '2022-03-03', new.e = '2023-03-03'; +insert into t (a) values (3),(4); +select * from t for system_time all; +drop table t; +set sql_mode=default, timestamp=default; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index 80ac70bbc09..3b8aa854625 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -1386,6 +1386,80 @@ insert into t select * from t; drop table tcount, t; --echo # +--echo # MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref and Assertion `table_ref->table || table_ref->view' in Field_iterator_table_ref::set_field_iterator +--echo # +create table t1 (i int); +create table t2 (i int); + +--error ER_SUBQUERIES_NOT_SUPPORTED +alter table t1 partition by system_time + interval (select i from t2) day (partition p1 history, partition pn current); + +drop table t1; + +--error ER_SUBQUERIES_NOT_SUPPORTED +create table t1 (id int) with system versioning + partition by system_time + interval (select i from t2) day (partition p1 history, partition pn current); + +--error ER_PART_WRONG_VALUE +create table t1 (id int) with system versioning + partition by system_time + interval "hello" day (partition p1 history, partition pn current); + +create table t1 (id int) with system versioning + partition by system_time + interval 3.893 day (partition p1 history, partition pn current); + +drop table t1, t2; + +create table t1 (id int) with system versioning + partition by system_time interval "3-11" year_month (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "3 11" day_hour (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "3 11:12" day_minute (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "3 11:12:13" day_second (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "11:12" hour_minute (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "11:12:13" hour_second (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "12:13" minute_second (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +--error ER_PART_WRONG_VALUE +create table t1 (id int) with system versioning + partition by system_time interval "12:13.123" minute_microsecond (partition p1 history, partition pn current); + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysys/my_pthread.c b/mysys/my_pthread.c index c1567f2cd03..36f5081b072 100644 --- a/mysys/my_pthread.c +++ b/mysys/my_pthread.c @@ -261,7 +261,6 @@ int sigwait(sigset_t *setp, int *sigp) pthread_attr_init(&thr_attr); pthread_attr_setscope(&thr_attr,PTHREAD_SCOPE_PROCESS); pthread_attr_setdetachstate(&thr_attr,PTHREAD_CREATE_DETACHED); - pthread_attr_setstacksize(&thr_attr,8196); pthread_create(&sigwait_thread_id, &thr_attr, sigwait_thread, setp); pthread_attr_destroy(&thr_attr); } diff --git a/mysys/thr_alarm.c b/mysys/thr_alarm.c index 92a471075c6..90429f72823 100644 --- a/mysys/thr_alarm.c +++ b/mysys/thr_alarm.c @@ -106,7 +106,6 @@ void init_thr_alarm(uint max_alarms) pthread_attr_init(&thr_attr); pthread_attr_setscope(&thr_attr,PTHREAD_SCOPE_PROCESS); pthread_attr_setdetachstate(&thr_attr,PTHREAD_CREATE_DETACHED); - pthread_attr_setstacksize(&thr_attr,8196); mysql_thread_create(key_thread_alarm, &alarm_thread, &thr_attr, alarm_handler, NULL); pthread_attr_destroy(&thr_attr); diff --git a/plugin/cracklib_password_check/cracklib_password_check.c b/plugin/cracklib_password_check/cracklib_password_check.c index 20294b070e7..470e6e5280f 100644 --- a/plugin/cracklib_password_check/cracklib_password_check.c +++ b/plugin/cracklib_password_check/cracklib_password_check.c @@ -13,10 +13,10 @@ along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ +#include <my_global.h> #include <mysql/plugin_password_validation.h> #include <crack.h> #include <string.h> -#include <alloca.h> #include <mysqld_error.h> static char *dictionary; diff --git a/plugin/file_key_management/parser.cc b/plugin/file_key_management/parser.cc index 818c026495f..57e0139a57d 100644 --- a/plugin/file_key_management/parser.cc +++ b/plugin/file_key_management/parser.cc @@ -162,19 +162,28 @@ bool Parser::read_filekey(const char *filekey, char *secret) int f= open(filekey, O_RDONLY|O_BINARY); if (f == -1) { - my_error(EE_FILENOTFOUND,ME_ERROR_LOG, filekey, errno); + my_error(EE_FILENOTFOUND, ME_ERROR_LOG, filekey, errno); return 1; } - int len= read(f, secret, MAX_SECRET_SIZE); + int len= read(f, secret, MAX_SECRET_SIZE + 1); if (len <= 0) { - my_error(EE_READ,ME_ERROR_LOG, filekey, errno); + my_error(EE_READ, ME_ERROR_LOG, filekey, errno); close(f); return 1; } close(f); + while (secret[len - 1] == '\r' || secret[len - 1] == '\n') len--; + if (len > MAX_SECRET_SIZE) + { + my_printf_error(EE_READ, + "Cannot read %s, the filekey is too long, " + "max secret size is %dB ", + ME_ERROR_LOG, filekey, MAX_SECRET_SIZE); + return 1; + } secret[len]= '\0'; return 0; } diff --git a/sql/handler.cc b/sql/handler.cc index 85600d02b63..a5b3a9c0af5 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -4607,32 +4607,17 @@ int handler::ha_check(THD *thd, HA_CHECK_OPT *check_opt) DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK); - const ulong v= table->s->mysql_version; - - if ((v >= MYSQL_VERSION_ID) && + if ((table->s->mysql_version >= MYSQL_VERSION_ID) && (check_opt->sql_flags & TT_FOR_UPGRADE)) return 0; - if (v < MYSQL_VERSION_ID) + if (table->s->mysql_version < MYSQL_VERSION_ID) { if (unlikely((error= check_old_types()))) return error; error= ha_check_for_upgrade(check_opt); if (unlikely(error && (error != HA_ADMIN_NEEDS_CHECK))) return error; - if (table->s->table_category == TABLE_CATEGORY_USER && - (v < 100142 || - (v >= 100200 && v < 100228) || - (v >= 100300 && v < 100319) || - (v >= 100400 && v < 100409))) - { - for (const KEY *key= table->key_info, - *end= table->key_info + table->s->keys; key < end; key++) - { - if (key->flags & HA_BINARY_PACK_KEY && key->flags & HA_VAR_LENGTH_KEY) - return HA_ADMIN_NEEDS_UPGRADE; - } - } if (unlikely(!error && (check_opt->sql_flags & TT_FOR_UPGRADE))) return 0; } diff --git a/sql/item.cc b/sql/item.cc index 9da8996157a..8aec7eb3a78 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -9838,6 +9838,8 @@ bool Item_trigger_field::set_value(THD *thd, sp_rcontext * /*ctx*/, Item **it) if (!item || fix_fields_if_needed(thd, NULL)) return true; + if (field->vers_sys_field()) + return false; // NOTE: field->table->copy_blobs should be false here, but let's // remember the value at runtime to avoid subtle bugs. @@ -10669,6 +10671,20 @@ void view_error_processor(THD *thd, void *data) ((TABLE_LIST *)data)->hide_view_error(thd); } +/** + Name resolution context with resolution in only one table +*/ + +Name_resolution_context::Name_resolution_context(TABLE_LIST *table): + outer_context(0), table_list(0), select_lex(0), + error_processor_data(0), + security_ctx(0) +{ + resolve_in_select_list= FALSE; + error_processor= &dummy_error_processor; + // resolve only in this table + first_name_resolution_table= last_name_resolution_table= table; +} st_select_lex *Item_ident::get_depended_from() const { diff --git a/sql/item.h b/sql/item.h index c3237ec8762..b145ec7793d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -244,6 +244,8 @@ struct Name_resolution_context: Sql_alloc security_ctx(0) {} + Name_resolution_context(TABLE_LIST *table); + void init() { resolve_in_select_list= FALSE; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 429d2ad4dc7..5f2c127e35b 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -420,9 +420,18 @@ bool Item_func::setup_args_and_comparator(THD *thd, Arg_comparator *cmp) if (args[0]->cmp_type() == STRING_RESULT && args[1]->cmp_type() == STRING_RESULT) { + Query_arena *arena, backup; + arena= thd->activate_stmt_arena_if_needed(&backup); + DTCollation tmp; - if (agg_arg_charsets_for_comparison(tmp, args, 2)) - return true; + bool ret= agg_arg_charsets_for_comparison(tmp, args, 2); + + if (arena) + thd->restore_active_arena(arena, &backup); + + if (ret) + return ret; + cmp->m_compare_collation= tmp.collation; } // Convert constants when compared to int/year field diff --git a/sql/privilege.h b/sql/privilege.h index 3e4c2526c6c..98168c0e62e 100644 --- a/sql/privilege.h +++ b/sql/privilege.h @@ -293,7 +293,7 @@ constexpr privilege_t SHOW_CREATE_TABLE_ACLS= existing temporary tables (CREATE_ACL is necessary for ALTER ... RENAME). */ constexpr privilege_t TMP_TABLE_ACLS= - COL_DML_ACLS | ALL_TABLE_DDL_ACLS; + COL_DML_ACLS | ALL_TABLE_DDL_ACLS | REFERENCES_ACL; diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index efed04fd76e..f51e16e4706 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -4096,7 +4096,7 @@ bool change_password(THD *thd, LEX_USER *user) if (update_user_table_password(thd, tables.user_table(), *acl_user)) goto end; - acl_cache->clear(1); // Clear locked hostname cache + hostname_cache_refresh(); // Clear locked hostname cache mysql_mutex_unlock(&acl_cache->lock); result= acl_cache_is_locked= 0; if (mysql_bin_log.is_open()) @@ -4254,7 +4254,6 @@ int acl_set_default_role(THD *thd, const char *host, const char *user, goto end; } - acl_cache->clear(1); mysql_mutex_unlock(&acl_cache->lock); result= 0; if (mysql_bin_log.is_open()) @@ -7733,7 +7732,10 @@ bool mysql_grant_role(THD *thd, List <LEX_USER> &list, bool revoke) a role */ if (role_as_user) + { propagate_role_grants(role_as_user, PRIVS_TO_MERGE::ALL); + acl_cache->clear(1); + } } mysql_mutex_unlock(&acl_cache->lock); @@ -14132,11 +14134,11 @@ static bool acl_check_ssl(THD *thd, const ACL_USER *acl_user) if (global_system_variables.log_warnings) sql_print_information("X509 issuer mismatch: should be '%s' " "but is '%s'", acl_user->x509_issuer, ptr); - free(ptr); + OPENSSL_free(ptr); X509_free(cert); return 1; } - free(ptr); + OPENSSL_free(ptr); } /* X509 subject is specified, we check it .. */ if (acl_user->x509_subject[0]) @@ -14149,11 +14151,11 @@ static bool acl_check_ssl(THD *thd, const ACL_USER *acl_user) if (global_system_variables.log_warnings) sql_print_information("X509 subject mismatch: should be '%s' but is '%s'", acl_user->x509_subject, ptr); - free(ptr); + OPENSSL_free(ptr); X509_free(cert); return 1; } - free(ptr); + OPENSSL_free(ptr); } X509_free(cert); return 0; diff --git a/sql/sql_class.h b/sql/sql_class.h index 273b53e4227..08e83adb85f 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5838,7 +5838,7 @@ class select_insert :public select_result_interceptor { int prepare(List<Item> &list, SELECT_LEX_UNIT *u); virtual int prepare2(JOIN *join); virtual int send_data(List<Item> &items); - virtual void store_values(List<Item> &values); + virtual bool store_values(List<Item> &values, bool ignore_errors); virtual bool can_rollback_data() { return 0; } bool prepare_eof(); bool send_ok_packet(); @@ -5879,7 +5879,8 @@ public: {} int prepare(List<Item> &list, SELECT_LEX_UNIT *u); - void store_values(List<Item> &values); + int binlog_show_create_table(TABLE **tables, uint count); + bool store_values(List<Item> &values, bool ignore_errors); bool send_eof(); virtual void abort_result_set(); virtual bool can_rollback_data() { return 1; } diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 580ff3fcfb5..539e427ee2f 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -662,7 +662,11 @@ int Explain_node::print_explain_for_children(Explain_query *query, for (int i= 0; i < (int) children.elements(); i++) { Explain_node *node= query->get_node(children.at(i)); - if (node->print_explain(query, output, explain_flags, is_analyze)) + /* + Note: node may not be present because for certain kinds of subqueries, + the optimizer is not able to see that they were eliminated. + */ + if (node && node->print_explain(query, output, explain_flags, is_analyze)) return 1; } return 0; @@ -706,8 +710,15 @@ void Explain_node::print_explain_json_for_children(Explain_query *query, for (int i= 0; i < (int) children.elements(); i++) { Explain_node *node= query->get_node(children.at(i)); - /* Derived tables are printed inside Explain_table_access objects */ + /* + Note: node may not be present because for certain kinds of subqueries, + the optimizer is not able to see that they were eliminated. + */ + if (!node) + continue; + + /* Derived tables are printed inside Explain_table_access objects */ if (!is_connection_printable_in_json(node->connection_type)) continue; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 8668b5d0dd2..a2c31f5684c 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -95,8 +95,8 @@ pthread_handler_t handle_delayed_insert(void *arg); static void unlink_blobs(TABLE *table); #endif static bool check_view_insertability(THD *thd, TABLE_LIST *view); -static int binlog_show_create_table(THD *thd, TABLE *table, - Table_specification_st *create_info); +static int binlog_show_create_table_(THD *thd, TABLE *table, + Table_specification_st *create_info); /* Check that insert/update fields are from the same single table of a view. @@ -4083,9 +4083,7 @@ int select_insert::send_data(List<Item> &values) bool error=0; thd->count_cuted_fields= CHECK_FIELD_WARN; // Calculate cuted fields - store_values(values); - if (table->default_field && - unlikely(table->update_default_fields(info.ignore))) + if (store_values(values, info.ignore)) DBUG_RETURN(1); thd->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL; if (unlikely(thd->is_error())) @@ -4143,18 +4141,19 @@ int select_insert::send_data(List<Item> &values) } -void select_insert::store_values(List<Item> &values) +bool select_insert::store_values(List<Item> &values, bool ignore_errors) { DBUG_ENTER("select_insert::store_values"); + bool error; if (fields->elements) - fill_record_n_invoke_before_triggers(thd, table, *fields, values, 1, - TRG_EVENT_INSERT); + error= fill_record_n_invoke_before_triggers(thd, table, *fields, values, + ignore_errors, TRG_EVENT_INSERT); else - fill_record_n_invoke_before_triggers(thd, table, table->field_to_fill(), - values, 1, TRG_EVENT_INSERT); + error= fill_record_n_invoke_before_triggers(thd, table, table->field_to_fill(), + values, ignore_errors, TRG_EVENT_INSERT); - DBUG_VOID_RETURN; + DBUG_RETURN(error); } bool select_insert::prepare_eof() @@ -4682,7 +4681,7 @@ select_create::prepare(List<Item> &_values, SELECT_LEX_UNIT *u) TABLE const *const table = *tables; if (thd->is_current_stmt_binlog_format_row() && !table->s->tmp_table) - return binlog_show_create_table(thd, *tables, ptr->create_info); + return binlog_show_create_table_(thd, *tables, ptr->create_info); return 0; } select_create *ptr; @@ -4796,8 +4795,8 @@ select_create::prepare(List<Item> &_values, SELECT_LEX_UNIT *u) } -static int binlog_show_create_table(THD *thd, TABLE *table, - Table_specification_st *create_info) +static int binlog_show_create_table_(THD *thd, TABLE *table, + Table_specification_st *create_info) { /* Note 1: In RBR mode, we generate a CREATE TABLE statement for the @@ -4890,7 +4889,7 @@ bool binlog_create_table(THD *thd, TABLE *table, bool replace) HA_CREATE_USED_DEFAULT_CHARSET); /* Ensure we write all engine options to binary log */ create_info.used_fields|= HA_CREATE_PRINT_ALL_OPTIONS; - result= binlog_show_create_table(thd, table, &create_info) != 0; + result= binlog_show_create_table_(thd, table, &create_info) != 0; thd->variables.option_bits= save_option_bits; return result; } @@ -4932,10 +4931,10 @@ bool binlog_drop_table(THD *thd, TABLE *table) } -void select_create::store_values(List<Item> &values) +bool select_create::store_values(List<Item> &values, bool ignore_errors) { - fill_record_n_invoke_before_triggers(thd, table, field, values, 1, - TRG_EVENT_INSERT); + return fill_record_n_invoke_before_triggers(thd, table, field, values, + ignore_errors, TRG_EVENT_INSERT); } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 35ed1af7bc1..4d5d417aabb 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2937,6 +2937,7 @@ void st_select_lex::init_query() item_list.empty(); min_max_opt_list.empty(); join= 0; + cur_pos_in_select_list= UNDEF_POS; having= prep_having= where= prep_where= 0; cond_pushed_into_where= cond_pushed_into_having= 0; attach_to_conds.empty(); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 5c864894b97..faaab33a589 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -7103,13 +7103,13 @@ static bool check_show_access(THD *thd, TABLE_LIST *table) @brief Check if the requested privileges exists in either User-, Host- or Db-tables. @param thd Thread context - @param want_access Privileges requested + @param requirements Privileges requested @param tables List of tables to be compared against - @param no_errors Don't report error to the client (using my_error() call). @param any_combination_of_privileges_will_do TRUE if any privileges on any column combination is enough. @param number Only the first 'number' tables in the linked list are relevant. + @param no_errors Don't report error to the client (using my_error() call). The suppled table list contains cached privileges. This functions calls the help functions check_access and check_grant to verify the first three steps @@ -7136,7 +7136,7 @@ static bool check_show_access(THD *thd, TABLE_LIST *table) bool check_table_access(THD *thd, privilege_t requirements, TABLE_LIST *tables, - bool any_combination_of_privileges_will_do, + bool any_combination_of_privileges_will_do, uint number, bool no_errors) { TABLE_LIST *org_tables= tables; diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 6cf99144a56..d8a441f2e10 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -6020,6 +6020,15 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables, show_table->use_all_columns(); // Required for default restore_record(show_table, s->default_values); +#ifndef NO_EMBEDDED_ACCESS_CHECKS + check_access(thd, SELECT_ACL, db_name->str, + &tables->grant.privilege, 0, 0, MY_TEST(tables->schema_table)); + if (is_temporary_table(tables)) + { + tables->grant.privilege|= TMP_TABLE_ACLS; + } +#endif + for (; (field= *ptr) ; ptr++) { if(field->invisible > INVISIBLE_USER) @@ -6039,14 +6048,13 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables, restore_record(table, s->default_values); #ifndef NO_EMBEDDED_ACCESS_CHECKS - ulonglong col_access; - check_access(thd,SELECT_ACL, db_name->str, - &tables->grant.privilege, 0, 0, MY_TEST(tables->schema_table)); - col_access= get_column_grant(thd, &tables->grant, - db_name->str, table_name->str, - field->field_name.str) & COL_ACLS; - if (!tables->schema_table && !col_access) + ulonglong col_access= + get_column_grant(thd, &tables->grant, db_name->str, table_name->str, + field->field_name.str) & COL_ACLS; + + if (!col_access && !tables->schema_table) continue; + char *end= tmp; for (uint bitnr=0; col_access ; col_access>>=1,bitnr++) { diff --git a/sql/sql_time.cc b/sql/sql_time.cc index 8bb96dfa776..12e4460ed25 100644 --- a/sql/sql_time.cc +++ b/sql/sql_time.cc @@ -68,62 +68,71 @@ int append_interval(String *str, interval_type int_type, const INTERVAL &interva size_t len; switch (int_type) { case INTERVAL_YEAR: - len= my_snprintf(buf,sizeof(buf),"%u", interval.year); + len= my_snprintf(buf,sizeof(buf),"%lu", interval.year); break; case INTERVAL_QUARTER: case INTERVAL_MONTH: - len= my_snprintf(buf,sizeof(buf),"%u", interval.month); + len= my_snprintf(buf,sizeof(buf),"%lu", interval.month); int_type=INTERVAL_MONTH; break; case INTERVAL_WEEK: case INTERVAL_DAY: - len= my_snprintf(buf,sizeof(buf),"%u", interval.day); + len= my_snprintf(buf,sizeof(buf),"%lu", interval.day); int_type=INTERVAL_DAY; break; case INTERVAL_HOUR: - len= my_snprintf(buf,sizeof(buf),"%u", interval.hour); + len= my_snprintf(buf,sizeof(buf),"%lu", interval.hour); break; case INTERVAL_MINUTE: - len= my_snprintf(buf,sizeof(buf),"%u", interval.minute); + len= my_snprintf(buf,sizeof(buf),"%llu", interval.minute); break; case INTERVAL_SECOND: - len= my_snprintf(buf,sizeof(buf),"%u", interval.second); + len= my_snprintf(buf,sizeof(buf),"%llu", interval.second); break; case INTERVAL_MICROSECOND: - len= my_snprintf(buf,sizeof(buf),"%u", interval.second_part); + len= my_snprintf(buf,sizeof(buf),"%llu", interval.second_part); break; case INTERVAL_YEAR_MONTH: - len= my_snprintf(buf,sizeof(buf),"%u-%02u", interval.day, interval.month); + len= my_snprintf(buf,sizeof(buf),"'%lu-%02lu'", + interval.year, interval.month); break; case INTERVAL_DAY_HOUR: - len= my_snprintf(buf,sizeof(buf),"%u %u", interval.day, interval.hour); + len= my_snprintf(buf,sizeof(buf),"'%lu %lu'", interval.day, interval.hour); break; case INTERVAL_DAY_MINUTE: - len= my_snprintf(buf,sizeof(buf),"%u %u:%02u", interval.day, interval.hour, interval.minute); + len= my_snprintf(buf,sizeof(buf),"'%lu %lu:%02llu'", + interval.day, interval.hour, interval.minute); break; case INTERVAL_DAY_SECOND: - len= my_snprintf(buf,sizeof(buf),"%u %u:%02u:%02u", interval.day, interval.hour, interval.minute, interval.second); + len= my_snprintf(buf,sizeof(buf),"'%lu %lu:%02llu:%02llu'", + interval.day, interval.hour, interval.minute, interval.second); break; case INTERVAL_HOUR_MINUTE: - len= my_snprintf(buf,sizeof(buf),"%u:%02u", interval.hour, interval.minute); + len= my_snprintf(buf,sizeof(buf),"'%lu:%02llu'", interval.hour, interval.minute); break; case INTERVAL_HOUR_SECOND: - len= my_snprintf(buf,sizeof(buf),"%u:%02u:%02u", interval.hour, interval.minute, interval.second); + len= my_snprintf(buf,sizeof(buf),"'%lu:%02llu:%02llu'", + interval.hour, interval.minute, interval.second); break; case INTERVAL_MINUTE_SECOND: - len= my_snprintf(buf,sizeof(buf),"%u:%02u", interval.minute, interval.second); + len= my_snprintf(buf,sizeof(buf),"'%llu:%02llu'", interval.minute, interval.second); break; case INTERVAL_DAY_MICROSECOND: - len= my_snprintf(buf,sizeof(buf),"%u %u:%02u:%02u.%06u", interval.day, interval.hour, interval.minute, interval.second, interval.second_part); + len= my_snprintf(buf,sizeof(buf),"'%lu %lu:%02llu:%02llu.%06llu'", + interval.day, interval.hour, interval.minute, + interval.second, interval.second_part); break; case INTERVAL_HOUR_MICROSECOND: - len= my_snprintf(buf,sizeof(buf),"%u:%02u:%02u.%06u", interval.hour, interval.minute, interval.second, interval.second_part); + len= my_snprintf(buf,sizeof(buf),"'%lu:%02llu:%02llu.%06llu'", + interval.hour, interval.minute, interval.second, + interval.second_part); break; case INTERVAL_MINUTE_MICROSECOND: - len= my_snprintf(buf,sizeof(buf),"%u:%02u.%06u", interval.minute, interval.second, interval.second_part); + len= my_snprintf(buf,sizeof(buf),"'%llu:%02llu.%06llu'", + interval.minute, interval.second, interval.second_part); break; case INTERVAL_SECOND_MICROSECOND: - len= my_snprintf(buf,sizeof(buf),"%u.%06u", interval.second, interval.second_part); + len= my_snprintf(buf,sizeof(buf),"%llu.%06llu", interval.second, interval.second_part); break; default: DBUG_ASSERT(0); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 4298c6b1171..0c1d0e13382 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1181,19 +1181,26 @@ err: bool mariadb_view_version_get(TABLE_SHARE *share) { DBUG_ASSERT(share->is_view); + DBUG_ASSERT(share->tabledef_version.length == 0); if (!(share->tabledef_version.str= (uchar*) alloc_root(&share->mem_root, MICROSECOND_TIMESTAMP_BUFFER_SIZE))) return TRUE; - share->tabledef_version.length= 0; // safety if the drfinition file is brocken DBUG_ASSERT(share->view_def != NULL); if (share->view_def->parse((uchar *) &share->tabledef_version, NULL, view_timestamp_parameters, 1, &file_parser_dummy_hook)) + { + // safety if the definition file is brocken + share->tabledef_version.length= 0; + my_error(ER_TABLE_CORRUPT, MYF(0), + share->db.str, share->table_name.str); return TRUE; + } DBUG_ASSERT(share->tabledef_version.length == MICROSECOND_TIMESTAMP_BUFFER_SIZE-1); + return FALSE; } @@ -1258,10 +1265,7 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, mysql_handle_single_derived(thd->lex, table, DT_REINIT); DEBUG_SYNC(thd, "after_cached_view_opened"); - if (!share->tabledef_version.length) - { - mariadb_view_version_get(share); - } + DBUG_ASSERT(share->tabledef_version.length); DBUG_RETURN(0); } @@ -1315,15 +1319,7 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, required_view_parameters, &file_parser_dummy_hook))) goto end; - if (!share->tabledef_version.length) - { - share->tabledef_version.str= (const uchar *) - memdup_root(&share->mem_root, - (const void *) - table->hr_timestamp.str, - (share->tabledef_version.length= - table->hr_timestamp.length)); - } + DBUG_ASSERT(share->tabledef_version.length); if (!table->tabledef_version.length) { table->set_view_def_version(&table->hr_timestamp); diff --git a/sql/sql_view.h b/sql/sql_view.h index c1e5dc49da3..0713b951de7 100644 --- a/sql/sql_view.h +++ b/sql/sql_view.h @@ -64,4 +64,6 @@ extern const LEX_CSTRING view_type; void make_valid_column_names(List<Item> &item_list); +bool mariadb_view_version_get(TABLE_SHARE *share); + #endif /* SQL_VIEW_INCLUDED */ diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 9e172646af9..57e67bed3cb 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -5391,11 +5391,12 @@ opt_part_option: opt_versioning_rotation: /* empty */ {} - | INTERVAL_SYM expr interval opt_versioning_interval_start + | { Lex->clause_that_disallows_subselect= "INTERVAL"; } + INTERVAL_SYM expr interval opt_versioning_interval_start { partition_info *part_info= Lex->part_info; const char *table_name= Lex->create_last_non_select_table->table_name.str; - if (unlikely(part_info->vers_set_interval(thd, $2, $3, $4, table_name))) + if (unlikely(part_info->vers_set_interval(thd, $3, $4, $5, table_name))) MYSQL_YYABORT; } | LIMIT ulonglong_num diff --git a/sql/table.cc b/sql/table.cc index 299bafcd0db..c99608c6cde 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -660,7 +660,11 @@ enum open_frm_error open_table_def(THD *thd, TABLE_SHARE *share, uint flags) if (!share->view_def) share->error= OPEN_FRM_ERROR_ALREADY_ISSUED; else + { share->error= OPEN_FRM_OK; + if (mariadb_view_version_get(share)) + share->error= OPEN_FRM_ERROR_ALREADY_ISSUED; + } } else share->error= OPEN_FRM_NOT_A_TABLE; @@ -8809,7 +8813,7 @@ int TABLE::update_virtual_field(Field *vf, bool ignore_warnings) Counting_error_handler count_errors; Suppress_warnings_error_handler warning_handler; in_use->push_internal_handler(&count_errors); - bool abort_on_warning; + bool abort_on_warning= ignore_warnings; if (ignore_warnings) { abort_on_warning= in_use->abort_on_warning; @@ -9576,7 +9580,8 @@ bool TABLE_LIST::change_refs_to_fields() Item **materialized_items= (Item **)thd->calloc(sizeof(void *) * table->s->fields); - if (!materialized_items) + Name_resolution_context *ctx= new Name_resolution_context(this); + if (!materialized_items || !ctx) return TRUE; while ((ref= (Item_direct_ref*)li++)) @@ -9592,7 +9597,8 @@ bool TABLE_LIST::change_refs_to_fields() DBUG_ASSERT(!field_it.end_of_fields()); if (!materialized_items[idx]) { - materialized_items[idx]= new (thd->mem_root) Item_field(thd, table->field[idx]); + materialized_items[idx]= + new (thd->mem_root) Item_field(thd, ctx, table->field[idx]); if (!materialized_items[idx]) return TRUE; } diff --git a/storage/connect/CMakeLists.txt b/storage/connect/CMakeLists.txt index 088ecfbf3b2..1c87305dc70 100644 --- a/storage/connect/CMakeLists.txt +++ b/storage/connect/CMakeLists.txt @@ -69,6 +69,7 @@ IF(UNIX) DISABLE_WARNING("format-truncation") DISABLE_WARNING("implicit-fallthrough") DISABLE_WARNING("type-limits") + DISABLE_WARNING("deprecated-declarations") endif(NOT WITH_WARNINGS) add_definitions( -DUNIX -DLINUX -DUBUNTU ) diff --git a/storage/connect/mysql-test/connect/r/odbc_postgresql.result b/storage/connect/mysql-test/connect/r/odbc_postgresql.result index fd23197c37f..6bd8d75a601 100644 --- a/storage/connect/mysql-test/connect/r/odbc_postgresql.result +++ b/storage/connect/mysql-test/connect/r/odbc_postgresql.result @@ -2,7 +2,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `Name` varchar(256) NOT NULL, `Description` varchar(256) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='ODBC' `CATFUNC`='Sources' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `TABLE_TYPE`='ODBC' `CATFUNC`='Sources' SET NAMES utf8; # # Checking CATFUNC=Tables @@ -15,6 +15,7 @@ Table_Cat Table_Schema Table_Name Table_Type Remark mtr public t1 TABLE mtr public t2 TABLE mtr public v1 VIEW +mtr schema1 space_in_column_name TABLE mtr schema1 t1 TABLE mtr schema1 t2 TABLE mtr schema1 t3 TABLE @@ -27,6 +28,7 @@ Table_Cat Table_Schema Table_Name Table_Type Remark mtr public t1 TABLE mtr public t2 TABLE mtr public v1 VIEW +mtr schema1 space_in_column_name TABLE mtr schema1 t1 TABLE mtr schema1 t2 TABLE mtr schema1 t3 TABLE @@ -39,6 +41,7 @@ Table_Cat Table_Schema Table_Name Table_Type Remark mtr public t1 TABLE mtr public t2 TABLE mtr public v1 VIEW +mtr schema1 space_in_column_name TABLE mtr schema1 t1 TABLE mtr schema1 t2 TABLE mtr schema1 t3 TABLE @@ -102,6 +105,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu mtr public t1 a 4 int4 10 4 0 10 0 mtr public t2 a 4 int4 10 4 0 10 0 mtr public v1 a 4 int4 10 4 0 10 1 +mtr schema1 space_in_column_name my space column 1 bpchar 20 80 NULL NULL 0 mtr schema1 t1 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t2 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t3 a 1 bpchar 10 40 NULL NULL 0 @@ -115,6 +119,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu mtr public t1 a 4 int4 10 4 0 10 0 mtr public t2 a 4 int4 10 4 0 10 0 mtr public v1 a 4 int4 10 4 0 10 1 +mtr schema1 space_in_column_name my space column 1 bpchar 20 80 NULL NULL 0 mtr schema1 t1 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t2 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t3 a 1 bpchar 10 40 NULL NULL 0 @@ -157,7 +162,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' SELECT * FROM t1; a 10 @@ -168,7 +173,7 @@ SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(10) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a 10 @@ -189,7 +194,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='public.t1' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='public.t1' SELECT * FROM t1; a 10 @@ -202,7 +207,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=utf8mb3 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t1' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t1' `DATA_CHARSET`='utf8' SELECT * FROM t1; a aaa @@ -213,8 +218,8 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` char(10) CHARACTER SET utf8mb3 NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 + `a` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a aaa @@ -237,7 +242,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=utf8mb3 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.v1' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.v1' `DATA_CHARSET`='utf8' SELECT * FROM t1; a aaa @@ -248,8 +253,8 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` char(10) CHARACTER SET utf8mb3 DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 + `a` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a aaa @@ -272,7 +277,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=utf8mb3 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t2' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t2' `DATA_CHARSET`='utf8' SELECT * FROM t1; a xxx @@ -283,8 +288,8 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` char(10) CHARACTER SET utf8mb3 NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 + `a` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a xxx @@ -306,3 +311,11 @@ DELETE FROM t1 WHERE a='20'; Warnings: Note 1105 schema1.t3: 0 affected rows DROP TABLE t1; +# +# MDEV-29687 ODBC tables do not quote identifier names correctly +# +CREATE TABLE pg_in_maria ENGINE=CONNECT TABNAME='schema1.space_in_column_name' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' quoted=1; +SELECT * from pg_in_maria; +my space column +My value +DROP TABLE pg_in_maria; diff --git a/storage/connect/mysql-test/connect/t/odbc_postgresql.sql b/storage/connect/mysql-test/connect/t/odbc_postgresql.sql index a795817a4d3..9b22c69af13 100644 --- a/storage/connect/mysql-test/connect/t/odbc_postgresql.sql +++ b/storage/connect/mysql-test/connect/t/odbc_postgresql.sql @@ -2,7 +2,7 @@ -- The SQL script to create PostgreSQL data for odbc_postgresql.test -- -- Run this script as a admin user: --- psql -U postgres < odbc_postgresql.sql +-- sudo -u postgres psql < storage/connect/mysql-test/connect/t/odbc_postgresql.sql SET NAMES 'UTF8'; @@ -11,7 +11,7 @@ DROP USER IF EXISTS mtr; CREATE USER mtr WITH PASSWORD 'mtr'; CREATE DATABASE mtr OWNER=mtr ENCODING='UTF8'; -GRANT ALL ON DATABASE mtr TO mtr; +GRANT ALL PRIVILEGES ON DATABASE mtr TO mtr; \c mtr SET role mtr; CREATE TABLE t1 (a INT NOT NULL); @@ -27,4 +27,6 @@ CREATE TABLE schema1.t2 (a CHAR(10) NOT NULL); INSERT INTO schema1.t2 VALUES ('xxx'),('yyy'),('zzz'),('ÄÖÜ'); CREATE TABLE schema1.t3 (a CHAR(10) NOT NULL, b CHAR(10) NOT NULL); INSERT INTO schema1.t3 VALUES ('xxx', 'aaa'),('yyy', 'bbb'),('zzz', 'ccc'),('ÄÖÜ', 'яяя'); - +CREATE TABLE schema1.space_in_column_name ("my space column" CHAR(20) NOT NULL); +INSERT INTO schema1.space_in_column_name VALUES ('My value'); +\dt schema1.* diff --git a/storage/connect/mysql-test/connect/t/odbc_postgresql.test b/storage/connect/mysql-test/connect/t/odbc_postgresql.test index 86597423d04..ec98453d630 100644 --- a/storage/connect/mysql-test/connect/t/odbc_postgresql.test +++ b/storage/connect/mysql-test/connect/t/odbc_postgresql.test @@ -5,10 +5,10 @@ # To configure your system to be able to run this test, # follow through the following steps: # -# 1. Install and configure PostgreSQL database to stat on the system startup +# 1. Install and configure PostgreSQL database to start on the system startup # # 2. Create user, database, schema and tables to be used by mtr: -# psql -U postgres < odbc_postgresql.sql +# sudo -u postgres psql < storage/connect/mysql-test/connect/t/odbc_postgresql.sql # # 3. Install PostgreSQL ODBC Driver. # - On CentOS, Fedora: @@ -18,18 +18,23 @@ # # 4. Create a data source with the name "ConnectEnginePostgresql" # - On Windows: use odbcadm.exe -# - On Linux: put these lines into /etc/odbc.ini +# - On Linux: put these lines into /etc/odbc.ini or in ~/.odbc.ini # #[ConnectEnginePostgresql] #Description=PostgreSQL DSN for ConnectSE -#Driver=PostgreSQL (should the path to the driver so file) +#Driver=PostgreSQL (should the path to the driver so file, on linux: /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so) #Database=mtr #Servername=localhost #Port=5432 # # 5. Allow user "mtr" to connect to the database "mtr" -# Add this line into the begginning of pg_hba.conf -# (usually /var/lib/pgsql/data/pg_hba.conf on Linux): +# Find `pg_hba.conf` file: +# Run `SHOW hba_file;` or `locate pg_hba.conf` to find right location +# (usually /var/lib/pgsql/data/pg_hba.conf or /etc/postgresql/[version]/main/pg_hba.conf on Linux) +# Add this line into the beginning of pg_hba.conf: +# For unix socket connection (connect with `psql -U mtr`) +#local mtr mtr password +# For TCP/IP connection (connect with `psql -U mtr -h 127.0.0.1`) #host mtr mtr 127.0.0.1/32 password # # 6. Restart the server: @@ -211,3 +216,10 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(6), b VARCHAR(6), PRIMARY KEY(a, b)) ENGINE=CONNECT TABNAME='schema1.t3' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr'; DELETE FROM t1 WHERE a='20'; DROP TABLE t1; + +--echo # +--echo # MDEV-29687 ODBC tables do not quote identifier names correctly +--echo # +CREATE TABLE pg_in_maria ENGINE=CONNECT TABNAME='schema1.space_in_column_name' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' quoted=1; +SELECT * from pg_in_maria; +DROP TABLE pg_in_maria; diff --git a/storage/connect/odbconn.cpp b/storage/connect/odbconn.cpp index eaa5d1748c2..b0026860054 100644 --- a/storage/connect/odbconn.cpp +++ b/storage/connect/odbconn.cpp @@ -997,6 +997,11 @@ ODBConn::ODBConn(PGLOBAL g, TDBODBC *tdbp) m_Full = false; m_UseCnc = false; m_IDQuoteChar[0] = '"'; + if (tdbp) + { + if (tdbp->Quoted && tdbp->Quote) + m_IDQuoteChar[0] = *tdbp->Quote; + } m_IDQuoteChar[1] = 0; //*m_ErrMsg = '\0'; } // end of ODBConn @@ -1179,6 +1184,7 @@ int ODBConn::Open(PCSZ ConnectString, POPARM sop, DWORD options) // Verify support for required functionality and cache info // VerifyConnect(); Deprecated GetConnectInfo(); + // Still we want to use the set QChar } catch(DBX *xp) { snprintf(g->Message, sizeof(g->Message), "%s: %s", xp->m_Msg, xp->GetErrorMessage(0)); Close(); diff --git a/storage/connect/tabext.cpp b/storage/connect/tabext.cpp index 44a996243db..dcd93539f19 100644 --- a/storage/connect/tabext.cpp +++ b/storage/connect/tabext.cpp @@ -159,6 +159,9 @@ bool EXTDEF::DefineAM(PGLOBAL g, LPCSTR am, int poff) Maxerr = GetIntCatInfo("Maxerr", 0); Maxres = GetIntCatInfo("Maxres", 0); Quoted = GetIntCatInfo("Quoted", 0); + Qchar = GetStringCatInfo(g,"Qchar", NULL); + if (Qchar && !Quoted) + Quoted = 1; Options = 0; Cto = 0; Qto = 0; @@ -198,6 +201,7 @@ TDBEXT::TDBEXT(EXTDEF *tdp) : TDB(tdp) Cto = tdp->Cto; Qto = tdp->Qto; Quoted = MY_MAX(0, tdp->GetQuoted()); + Quote = tdp->GetQchar(); Rows = tdp->GetElemt(); Memory = tdp->Memory; Scrollable = tdp->Scrollable; @@ -214,12 +218,12 @@ TDBEXT::TDBEXT(EXTDEF *tdp) : TDB(tdp) Cto = 0; Qto = 0; Quoted = 0; + Quote = NULL; Rows = 0; Memory = 0; Scrollable = false; } // endif tdp - Quote = NULL; Query = NULL; Count = NULL; //Where = NULL; @@ -252,6 +256,7 @@ TDBEXT::TDBEXT(PTDBEXT tdbp) : TDB(tdbp) Cto = tdbp->Cto; Qto = tdbp->Qto; Quoted = tdbp->Quoted; + Quote = tdbp->Quote; Rows = tdbp->Rows; Memory = tdbp->Memory; Scrollable = tdbp->Scrollable; @@ -389,6 +394,8 @@ bool TDBEXT::MakeSQL(PGLOBAL g, bool cnt) int len; bool first = true; PCOL colp; + char *res= NULL, *my_schema_table= NULL; + size_t my_len= 0; if (Srcdef) return MakeSrcdef(g); @@ -458,10 +465,37 @@ bool TDBEXT::MakeSQL(PGLOBAL g, bool cnt) Decode(TableName, buf, sizeof(buf)); if (Quote) { - // Put table name between identifier quotes in case in contains blanks - Query->Append(Quote); - Query->Append(buf); - Query->Append(Quote); + // Tabname can have both database and table identifiers, we need to parse + if (res= strstr(buf, ".")) + { + // Parse schema + my_len= res - buf + 1; + my_schema_table= (char *) malloc(my_len); + memcpy(my_schema_table, buf, my_len - 1); + my_schema_table[my_len] = 0; + Query->Append(Quote); + Query->Append(my_schema_table); + Query->Append(Quote); + free(my_schema_table); + Query->Append("."); + // Parse table + my_len= strlen(buf) - my_len + 1; + my_schema_table= (char *) malloc(my_len); + memcpy(my_schema_table, ++res, my_len); + my_schema_table[my_len] = 0; + Query->Append(Quote); + Query->Append(my_schema_table); + Query->Append(Quote); + free(my_schema_table); + } + else + { + // Put table name between identifier quotes in case in contains blanks + Query->Append(Quote); + Query->Append(buf); + Query->Append(Quote); + } + } else Query->Append(buf); diff --git a/storage/connect/tabext.h b/storage/connect/tabext.h index 5fef1b9ece0..8a0d6c784a5 100644 --- a/storage/connect/tabext.h +++ b/storage/connect/tabext.h @@ -68,6 +68,7 @@ public: inline PSZ GetSrcdef(void) { return Srcdef; } inline char GetSep(void) { return (Sep) ? *Sep : 0; } inline int GetQuoted(void) { return Quoted; } + inline PSZ GetQchar(void) { return Qchar; } inline int GetOptions(void) { return Options; } // Methods diff --git a/storage/innobase/data/data0data.cc b/storage/innobase/data/data0data.cc index 14a0b3e19fd..0e485e58215 100644 --- a/storage/innobase/data/data0data.cc +++ b/storage/innobase/data/data0data.cc @@ -1,7 +1,7 @@ /***************************************************************************** Copyright (c) 1994, 2016, Oracle and/or its affiliates. All Rights Reserved. -Copyright (c) 2017, 2020, MariaDB Corporation. +Copyright (c) 2017, 2022, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -579,7 +579,6 @@ dtuple_convert_big_rec( big_rec_t* vector; dfield_t* dfield; ulint size; - ulint n_fields; ulint local_prefix_len; if (!dict_index_is_clust(index)) { @@ -615,7 +614,7 @@ dtuple_convert_big_rec( a variable-length field that yields the biggest savings when stored externally */ - n_fields = 0; + ut_d(ulint n_fields = 0); uint16_t longest_i; ulint longest; @@ -731,9 +730,8 @@ ext_write: dfield_set_data(dfield, data, local_len); dfield_set_ext(dfield); - n_fields++; (*n_ext)++; - ut_ad(n_fields < dtuple_get_n_fields(entry)); + ut_ad(++n_fields < dtuple_get_n_fields(entry)); if (upd && !upd->is_modified(longest_i)) { diff --git a/storage/innobase/gis/gis0rtree.cc b/storage/innobase/gis/gis0rtree.cc index b529d5ebef5..a4fbf70e7a1 100644 --- a/storage/innobase/gis/gis0rtree.cc +++ b/storage/innobase/gis/gis0rtree.cc @@ -902,7 +902,7 @@ rtr_page_split_and_insert( lock_prdt_t new_prdt; rec_t* first_rec = NULL; int first_rec_group = 1; - ulint n_iterations = 0; + IF_DBUG(bool iterated = false,); if (!*heap) { *heap = mem_heap_create(1024); @@ -1119,7 +1119,7 @@ func_start: the page, and it'll need the second round split in this case. We test this scenario here*/ DBUG_EXECUTE_IF("rtr_page_need_second_split", - if (n_iterations == 0) { + if (!iterated) { rec = NULL; goto after_insert; } ); @@ -1183,7 +1183,7 @@ after_insert: parent. */ rtr_clean_rtr_info(cursor->rtr_info, true); cursor->rtr_info = NULL; - n_iterations++; + IF_DBUG(iterated=true,); rec_t* i_rec = page_rec_get_next(page_get_infimum_rec( buf_block_get_frame(block))); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index fb8b0180c3d..5af52a3240d 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -7227,6 +7227,11 @@ ha_innobase::build_template( m_prebuilt->versioned_write = table->versioned_write(VERS_TRX_ID); m_prebuilt->need_to_access_clustered = (index == clust_index); + if (m_prebuilt->in_fts_query) { + /* Do clustered index lookup to fetch the FTS_DOC_ID */ + m_prebuilt->need_to_access_clustered = true; + } + /* Either m_prebuilt->index should be a secondary index, or it should be the clustered index. */ ut_ad(dict_index_is_clust(index) == (index == clust_index)); diff --git a/storage/innobase/include/ut0lst.h b/storage/innobase/include/ut0lst.h index 9a5f3059826..7b7ed7b8e80 100644 --- a/storage/innobase/include/ut0lst.h +++ b/storage/innobase/include/ut0lst.h @@ -1,7 +1,7 @@ /***************************************************************************** Copyright (c) 1995, 2015, Oracle and/or its affiliates. All Rights Reserved. -Copyright (c) 2019, MariaDB Corporation. +Copyright (c) 2019, 2022, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -25,8 +25,7 @@ Created 9/10/1995 Heikki Tuuri Rewritten by Sunny Bains Dec 2011. ***********************************************************************/ -#ifndef ut0lst_h -#define ut0lst_h +#pragma once /* Do not include univ.i because univ.i includes this. */ @@ -474,17 +473,17 @@ template <typename List, class Functor> void ut_list_validate(const List& list, Functor& functor) { ut_list_map(list, functor); - +#ifdef UNIV_DEBUG /* Validate the list backwards. */ - ulint count = 0; + ulint count = list.count; for (typename List::elem_type* elem = list.end; elem != 0; elem = (elem->*list.node).prev) { - ++count; + --count; } - - ut_a(count == list.count); + ut_ad(!count); +#endif } /** Check the consistency of a doubly linked list. @@ -494,23 +493,24 @@ template <typename List, class Functor> inline void ut_list_validate(const List& list, const Functor& functor) { ut_list_map(list, functor); - +#ifdef UNIV_DEBUG /* Validate the list backwards. */ - ulint count = 0; + ulint count = list.count; for (typename List::elem_type* elem = list.end; elem != 0; elem = (elem->*list.node).prev) { - ++count; + --count; } - ut_a(count == list.count); + ut_ad(!count); +#endif } template <typename List> inline void ut_list_validate(const List& list) { - ut_list_validate(list, NullValidate()); + ut_d(ut_list_validate(list, NullValidate())); } #ifdef UNIV_DEBUG @@ -561,8 +561,3 @@ ut_list_move_to_front( ut_list_prepend(list, elem); } } - -#ifdef UNIV_DEBUG -#endif - -#endif /* ut0lst.h */ diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index c746a48d7b6..5c1b8c86f15 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -21286,6 +21286,104 @@ static void test_explain_meta() mct_close_log(); } +static void test_mdev_16128() +{ + int rc, res; + MYSQL_STMT *stmt; + MYSQL_BIND bind, bind_res; + char bind_arg_1[]="d", bind_arg_2[]="b"; + ulong length= 0; + const char *query= + "SELECT 300 FROM t1 WHERE EXISTS (SELECT 100 FROM t2 WHERE t2.b = ?)"; + + myheader("test_mdev_16128"); + + rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1"); + myquery(rc); + + rc= mysql_query(mysql, "DROP TABLE IF EXISTS t2"); + myquery(rc); + + rc= mysql_query(mysql, "CREATE TABLE t1 (a VARCHAR(10))"); + myquery(rc); + + rc= mysql_query(mysql, "CREATE TABLE t2 (b VARCHAR(10) CHARACTER SET utf8)"); + myquery(rc); + + rc= mysql_query(mysql, "INSERT INTO t1 VALUES('b')"); + myquery(rc); + + rc= mysql_query(mysql, "INSERT INTO t2 VALUES('d')"); + myquery(rc); + + stmt= mysql_stmt_init(mysql); + check_stmt(stmt); + + rc= mysql_stmt_prepare(stmt, query, strlen(query)); + check_execute(stmt, rc); + + memset(&bind, 0, sizeof(bind)); + bind.buffer_type= MYSQL_TYPE_STRING; + bind.buffer_length= strlen(bind_arg_1); + bind.buffer= bind_arg_1; + + rc= mysql_stmt_bind_param(stmt, &bind); + check_execute(stmt, rc); + + memset(&bind_res, 0, sizeof(bind_res)); + bind_res.buffer_type= MYSQL_TYPE_LONG; + bind_res.buffer= &res; + bind_res.is_null= NULL; + bind_res.length= &length; + + rc= mysql_stmt_bind_result(stmt, &bind_res); + check_execute(stmt, rc); + + rc= mysql_stmt_execute(stmt); + check_execute(stmt, rc); + + rc= mysql_stmt_store_result(stmt); + check_execute(stmt, rc); + + rc= mysql_stmt_fetch(stmt); + + /** + It's expected that the query + SELECT 300 FROM t1 WHERE EXISTS (SELECT 100 FROM t2 WHERE t2.b = ?)" + executed in PS-mode and bound with the value 'd' returns exactly + one row containing the value (300). + */ + check_execute(stmt, rc); + DIE_UNLESS(bind_res.buffer_type == MYSQL_TYPE_LONG); + DIE_UNLESS(res == 300); + + memset(&bind, 0, sizeof(bind)); + bind.buffer_type= MYSQL_TYPE_STRING; + bind.buffer_length= strlen(bind_arg_2); + bind.buffer= bind_arg_2; + + rc= mysql_stmt_bind_param(stmt, &bind); + check_execute(stmt, rc); + + rc= mysql_stmt_execute(stmt); + check_execute(stmt, rc); + + rc= mysql_stmt_store_result(stmt); + check_execute(stmt, rc); + + rc= mysql_stmt_fetch(stmt); + /** + It's expected that the query + SELECT 300 FROM t1 WHERE EXISTS (SELECT 100 FROM t2 WHERE t2.b = ?)" + executed in PS-mode and bound with the value 'd' returns empty result set. + */ + DIE_UNLESS(rc == MYSQL_NO_DATA); + + mysql_stmt_close(stmt); + + rc= mysql_query(mysql, "DROP TABLE t1, t2"); + myquery(rc); +} #ifndef EMBEDDED_LIBRARY #define MDEV19838_MAX_PARAM_COUNT 32 @@ -21784,6 +21882,7 @@ static struct my_tests_st my_tests[]= { #ifndef EMBEDDED_LIBRARY { "test_mdev19838", test_mdev19838 }, #endif + { "test_mdev_16128", test_mdev_16128 }, { "test_mdev18408", test_mdev18408 }, { "test_mdev20261", test_mdev20261 }, { 0, 0 } |