From 1ef22e28ad7744d436c694e970420c426fc7095a Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 16 Mar 2023 17:24:12 +0200 Subject: MDEV-26258 Various crashes/asserts/corruptions when Aria encryption is enabled/used, but the encryption plugin is not loaded The reason for the MDEV reported failures is that the tests are enabling encryption for Aria but not providing any encryption keys. Fixed by checking if encryption keys exists before creating the table. Other things: - maria.encrypt_wrong-key changed as we now get the error on CREATE instead during insert. --- include/handler_ername.h | 1 + include/my_base.h | 6 ++- include/my_handler_errors.h | 3 +- mysql-test/include/wait_until_connected_again.inc | 6 ++- mysql-test/suite/maria/encrypt-no-key.result | 36 ++++++++++----- mysql-test/suite/maria/encrypt-no-key.test | 55 ++++++++++++++++++++--- mysql-test/suite/maria/encrypt-wrong-key.result | 12 ++--- mysql-test/suite/maria/encrypt-wrong-key.test | 7 +-- storage/maria/ha_maria.cc | 3 +- storage/maria/ma_create.c | 2 + storage/maria/ma_crypt.c | 35 ++++++++++++--- storage/maria/ma_crypt.h | 3 +- storage/maria/ma_delete_table.c | 7 +-- storage/maria/ma_open.c | 3 +- 14 files changed, 135 insertions(+), 44 deletions(-) diff --git a/include/handler_ername.h b/include/handler_ername.h index fe55062e6fb..20e62487c2c 100644 --- a/include/handler_ername.h +++ b/include/handler_ername.h @@ -79,3 +79,4 @@ { "HA_ERR_ABORTED_BY_USER", HA_ERR_ABORTED_BY_USER, "" }, { "HA_ERR_DISK_FULL", HA_ERR_DISK_FULL, "" }, { "HA_ERR_INCOMPATIBLE_DEFINITION", HA_ERR_INCOMPATIBLE_DEFINITION, "" }, +{ "HA_ERR_NO_ENCRYPTION", HA_ERR_NO_ENCRYPTION, "" }, diff --git a/include/my_base.h b/include/my_base.h index 32ef6f7fe85..cc3d57cb0b1 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -48,6 +48,7 @@ #define HA_OPEN_NO_PSI_CALL 1024U /* Don't call/connect PSI */ #define HA_OPEN_MERGE_TABLE 2048U #define HA_OPEN_FOR_CREATE 4096U +#define HA_OPEN_FOR_DROP (1U << 13) /* Open part of drop */ /* Allow opening even if table is incompatible as this is for ALTER TABLE which @@ -516,14 +517,15 @@ enum ha_base_keytype { #define HA_ERR_DISK_FULL 189 #define HA_ERR_INCOMPATIBLE_DEFINITION 190 #define HA_ERR_FTS_TOO_MANY_WORDS_IN_PHRASE 191 /* Too many words in a phrase */ -#define HA_ERR_DECRYPTION_FAILED 192 /* Table encrypted but decypt failed */ +#define HA_ERR_DECRYPTION_FAILED 192 /* Table encrypted but decrypt failed */ #define HA_ERR_FK_DEPTH_EXCEEDED 193 /* FK cascade depth exceeded */ #define HA_ERR_TABLESPACE_MISSING 194 /* Missing Tablespace */ #define HA_ERR_SEQUENCE_INVALID_DATA 195 #define HA_ERR_SEQUENCE_RUN_OUT 196 #define HA_ERR_COMMIT_ERROR 197 #define HA_ERR_PARTITION_LIST 198 -#define HA_ERR_LAST 198 /* Copy of last error nr * */ +#define HA_ERR_NO_ENCRYPTION 199 +#define HA_ERR_LAST 199 /* Copy of last error nr * */ /* Number of different errors */ #define HA_ERR_ERRORS (HA_ERR_LAST - HA_ERR_FIRST + 1) diff --git a/include/my_handler_errors.h b/include/my_handler_errors.h index 4c3a02dd745..7eab185dd98 100644 --- a/include/my_handler_errors.h +++ b/include/my_handler_errors.h @@ -109,7 +109,8 @@ static const char *handler_error_messages[]= "Sequence has been run out", "Sequence values are conflicting", "Error during commit", - "Cannot select partitions" + "Cannot select partitions", + "Cannot initialize encryption. Check that all encryption parameters have been set" }; #endif /* MYSYS_MY_HANDLER_ERRORS_INCLUDED */ diff --git a/mysql-test/include/wait_until_connected_again.inc b/mysql-test/include/wait_until_connected_again.inc index 15a1e5bf847..f644ec8645f 100644 --- a/mysql-test/include/wait_until_connected_again.inc +++ b/mysql-test/include/wait_until_connected_again.inc @@ -11,7 +11,7 @@ let $counter= 5000; let $mysql_errno= 9999; while ($mysql_errno) { - --error 0,ER_ACCESS_DENIED_ERROR,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,ER_LOCK_WAIT_TIMEOUT,2002,2006,2013 + --error 0,ER_ACCESS_DENIED_ERROR,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,ER_LOCK_WAIT_TIMEOUT,2002,2006,2013,HA_ERR_NO_ENCRYPTION show status; dec $counter; @@ -30,6 +30,10 @@ while ($mysql_errno) { let $mysql_errno=0; } + if ($mysql_errno == 199) + { + let $mysql_errno=0; + } --sleep 0.1 } --enable_query_log diff --git a/mysql-test/suite/maria/encrypt-no-key.result b/mysql-test/suite/maria/encrypt-no-key.result index 6745670dfac..32a280d8cbc 100644 --- a/mysql-test/suite/maria/encrypt-no-key.result +++ b/mysql-test/suite/maria/encrypt-no-key.result @@ -1,15 +1,27 @@ -call mtr.add_suppression('Unknown key id 1. Can''t continue'); +call mtr.add_suppression("Initialization of encryption failed.*"); set global aria_encrypt_tables= 1; create table t1 (pk int primary key, a int, key(a)) engine=aria transactional=1; -alter table t1 disable keys; -insert into t1 values (1,1); -alter table t1 enable keys; -ERROR HY000: Unknown key id 1. Can't continue! -repair table t1 use_frm; -Table Op Msg_type Msg_text -test.t1 repair warning Number of rows changed from 0 to 1 -test.t1 repair Error Unknown key id 1. Can't continue! -test.t1 repair Error Unknown key id 1. Can't continue! -test.t1 repair status OK -drop table t1; +ERROR HY000: Initialization of encryption failed for ./test/t1 set global aria_encrypt_tables= default; +# +# MDEV-26258 Various crashes/asserts/corruptions when Aria encryption is +# enabled/used, but the encryption plugin is not loaded +# +SET GLOBAL aria_encrypt_tables=ON; +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; +ERROR HY000: Initialization of encryption failed for ./test/t1 +# Restart with encryption enabled +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; +INSERT INTO t1 VALUES (4,0); +LOAD INDEX INTO CACHE t1 IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +LOAD INDEX INTO CACHE t1; +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +SELECT * FROM t1; +ERROR HY000: Initialization of encryption failed for ./test/t1.MAD +DROP TABLE t1; +Warnings: +Warning 199 Initialization of encryption failed for ./test/t1.MAD +Cleanup diff --git a/mysql-test/suite/maria/encrypt-no-key.test b/mysql-test/suite/maria/encrypt-no-key.test index 2d586c50695..b3f2f4b4bf3 100644 --- a/mysql-test/suite/maria/encrypt-no-key.test +++ b/mysql-test/suite/maria/encrypt-no-key.test @@ -1,14 +1,55 @@ # # MDEV-18496 Crash when Aria encryption is enabled but plugin not available # -call mtr.add_suppression('Unknown key id 1. Can''t continue'); +call mtr.add_suppression("Initialization of encryption failed.*"); set global aria_encrypt_tables= 1; +--error HA_ERR_NO_ENCRYPTION create table t1 (pk int primary key, a int, key(a)) engine=aria transactional=1; -alter table t1 disable keys; -insert into t1 values (1,1); -error 192; -alter table t1 enable keys; -repair table t1 use_frm; -drop table t1; set global aria_encrypt_tables= default; + +--echo # +--echo # MDEV-26258 Various crashes/asserts/corruptions when Aria encryption is +--echo # enabled/used, but the encryption plugin is not loaded +--echo # + +SET GLOBAL aria_encrypt_tables=ON; + +--write_file $MYSQLTEST_VARDIR/keys1.txt +1;770A8A65DA156D24EE2A093277530142 +EOF + +--replace_result \\ / +--error HA_ERR_NO_ENCRYPTION +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; + +--echo # Restart with encryption enabled + +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc +--exec echo "restart:--aria-encrypt-tables=1 --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQLTEST_VARDIR/keys1.txt" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc + +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; +INSERT INTO t1 VALUES (4,0); +LOAD INDEX INTO CACHE t1 IGNORE LEAVES; +LOAD INDEX INTO CACHE t1; + +# Restart without encryption. Above table should be unreadable + +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc +--exec echo "restart:--aria-encrypt-tables=0" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc + +--replace_result \\ / +--error HA_ERR_NO_ENCRYPTION +SELECT * FROM t1; +DROP TABLE t1; + +--echo Cleanup +--remove_file $MYSQLTEST_VARDIR/keys1.txt diff --git a/mysql-test/suite/maria/encrypt-wrong-key.result b/mysql-test/suite/maria/encrypt-wrong-key.result index bc22481296d..4ff057957da 100644 --- a/mysql-test/suite/maria/encrypt-wrong-key.result +++ b/mysql-test/suite/maria/encrypt-wrong-key.result @@ -1,16 +1,16 @@ call mtr.add_suppression("file_key_management"); call mtr.add_suppression("System key id 1 is missing"); call mtr.add_suppression("Unknown key id 1"); -call mtr.add_suppression("Failed to decrypt"); +call mtr.add_suppression("Initialization of encryption failed.*"); CREATE TABLE t1 (i INT, KEY(i)) ENGINE=Aria; INSERT INTO t1 VALUES (1); repair table t1; Table Op Msg_type Msg_text -test.t1 repair info Wrong CRC on datapage at 1 -test.t1 repair warning Number of rows changed from 1 to 0 -test.t1 repair status OK +test.t1 repair Error Initialization of encryption failed for ./test/t1.MAD +test.t1 repair error Corrupt INSERT INTO t1 VALUES (2); +ERROR HY000: Initialization of encryption failed for ./test/t1.MAD select * from t1; -ERROR HY000: failed to decrypt './test/t1' rc: -1 dstlen: 0 size: 8172 - +i +1 drop table t1; diff --git a/mysql-test/suite/maria/encrypt-wrong-key.test b/mysql-test/suite/maria/encrypt-wrong-key.test index ca65e1018d0..ac060c4e9dd 100644 --- a/mysql-test/suite/maria/encrypt-wrong-key.test +++ b/mysql-test/suite/maria/encrypt-wrong-key.test @@ -7,7 +7,7 @@ call mtr.add_suppression("file_key_management"); call mtr.add_suppression("System key id 1 is missing"); call mtr.add_suppression("Unknown key id 1"); -call mtr.add_suppression("Failed to decrypt"); +call mtr.add_suppression("Initialization of encryption failed.*"); --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --shutdown_server @@ -36,8 +36,11 @@ EOF --enable_reconnect --source include/wait_until_connected_again.inc +--replace_result \\ / repair table t1; +--replace_result \\ / +--error HA_ERR_NO_ENCRYPTION INSERT INTO t1 VALUES (2); --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect @@ -48,8 +51,6 @@ INSERT INTO t1 VALUES (2); --enable_reconnect --source include/wait_until_connected_again.inc ---replace_result \\ / ---error 192 select * from t1; drop table t1; --remove_file $MYSQLTEST_VARDIR/keys1.txt diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 2d6c8f85fe4..f25ba2aa0b3 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -292,7 +292,8 @@ static MYSQL_SYSVAR_BOOL(used_for_temp_tables, "Whether temporary tables should be MyISAM or Aria", 0, 0, 1); -static MYSQL_SYSVAR_BOOL(encrypt_tables, maria_encrypt_tables, PLUGIN_VAR_OPCMDARG, +static MYSQL_SYSVAR_BOOL(encrypt_tables, maria_encrypt_tables, + PLUGIN_VAR_OPCMDARG, "Encrypt tables (only for tables with ROW_FORMAT=PAGE (default) " "and not FIXED/DYNAMIC)", 0, 0, 0); diff --git a/storage/maria/ma_create.c b/storage/maria/ma_create.c index 8ef3249c2e4..3352a494d16 100644 --- a/storage/maria/ma_create.c +++ b/storage/maria/ma_create.c @@ -1062,6 +1062,8 @@ int maria_create(const char *name, enum data_file_type datafile_type, if (encrypted) { + DBUG_ASSERT(share.data_file_name.length == 0); + share.data_file_name.str= (char*) name; /* For error reporting */ if (ma_crypt_create(&share) || ma_crypt_write(&share, file)) goto err; diff --git a/storage/maria/ma_crypt.c b/storage/maria/ma_crypt.c index 33cf72b0b1e..8cc85d87d28 100644 --- a/storage/maria/ma_crypt.c +++ b/storage/maria/ma_crypt.c @@ -100,6 +100,7 @@ static void crypt_data_scheme_locker(struct st_encryption_scheme *scheme, int ma_crypt_create(MARIA_SHARE* share) { + uint key_version; MARIA_CRYPT_DATA *crypt_data= (MARIA_CRYPT_DATA*)my_malloc(sizeof(MARIA_CRYPT_DATA), MYF(MY_ZEROFILL)); crypt_data->scheme.type= CRYPT_SCHEME_1; @@ -110,6 +111,16 @@ ma_crypt_create(MARIA_SHARE* share) my_random_bytes((uchar*)&crypt_data->space, sizeof(crypt_data->space)); share->crypt_data= crypt_data; share->crypt_page_header_space= CRYPT_SCHEME_1_KEY_VERSION_SIZE; + + key_version = encryption_key_get_latest_version(crypt_data->scheme.key_id); + if (unlikely(key_version == ENCRYPTION_KEY_VERSION_INVALID)) + { + my_errno= HA_ERR_NO_ENCRYPTION; + my_printf_error(HA_ERR_NO_ENCRYPTION, + "Initialization of encryption failed for %s", MYF(0), + share->data_file_name.str); + return 1; + } return 0; } @@ -145,7 +156,7 @@ ma_crypt_write(MARIA_SHARE* share, File file) } uchar* -ma_crypt_read(MARIA_SHARE* share, uchar *buff) +ma_crypt_read(MARIA_SHARE* share, uchar *buff, my_bool silent) { uchar type= buff[0]; uchar iv_length= buff[1]; @@ -155,9 +166,9 @@ ma_crypt_read(MARIA_SHARE* share, uchar *buff) iv_length != sizeof(((MARIA_CRYPT_DATA*)1)->scheme.iv) + 4) { my_printf_error(HA_ERR_UNSUPPORTED, - "Unsupported crypt scheme! type: %d iv_length: %d\n", - MYF(ME_FATAL|ME_ERROR_LOG), - type, iv_length); + "Unsupported crypt scheme type: %d iv_length: %d\n", + MYF(ME_ERROR_LOG | (silent ? ME_WARNING : ME_FATAL)), + type, iv_length); return 0; } @@ -166,6 +177,7 @@ ma_crypt_read(MARIA_SHARE* share, uchar *buff) /* opening a table */ MARIA_CRYPT_DATA *crypt_data= (MARIA_CRYPT_DATA*)my_malloc(sizeof(MARIA_CRYPT_DATA), MYF(MY_ZEROFILL)); + uint key_version; crypt_data->scheme.type= type; mysql_mutex_init(key_CRYPT_DATA_lock, &crypt_data->lock, @@ -175,6 +187,17 @@ ma_crypt_read(MARIA_SHARE* share, uchar *buff) crypt_data->space= uint4korr(buff + 2); memcpy(crypt_data->scheme.iv, buff + 6, sizeof(crypt_data->scheme.iv)); share->crypt_data= crypt_data; + + key_version= encryption_key_get_latest_version(crypt_data->scheme.key_id); + if (unlikely(key_version == ENCRYPTION_KEY_VERSION_INVALID)) + { + my_errno= HA_ERR_NO_ENCRYPTION; + my_printf_error(HA_ERR_NO_ENCRYPTION, + "Initialization of encryption failed for %s", + MYF(ME_ERROR_LOG | (silent ? ME_WARNING : ME_FATAL)), + share->data_file_name.str); + return 0; + } } share->crypt_page_header_space= CRYPT_SCHEME_1_KEY_VERSION_SIZE; @@ -462,7 +485,7 @@ static int ma_encrypt(MARIA_SHARE *share, MARIA_CRYPT_DATA *crypt_data, uint32 dstlen= 0; /* Must be set because of error message */ *key_version = encryption_key_get_latest_version(crypt_data->scheme.key_id); - if (*key_version == ENCRYPTION_KEY_VERSION_INVALID) + if (unlikely(*key_version == ENCRYPTION_KEY_VERSION_INVALID)) { /* We use this error for both encryption and decryption, as in normal @@ -470,7 +493,7 @@ static int ma_encrypt(MARIA_SHARE *share, MARIA_CRYPT_DATA *crypt_data, */ my_errno= HA_ERR_DECRYPTION_FAILED; my_printf_error(HA_ERR_DECRYPTION_FAILED, - "Unknown key id %u. Can't continue!", + "Unknown encryption key id %u. Can't continue!", MYF(ME_FATAL|ME_ERROR_LOG), crypt_data->scheme.key_id); return 1; diff --git a/storage/maria/ma_crypt.h b/storage/maria/ma_crypt.h index 811b319bc0c..acaf36ee831 100644 --- a/storage/maria/ma_crypt.h +++ b/storage/maria/ma_crypt.h @@ -26,7 +26,8 @@ uint ma_crypt_get_index_page_header_space(struct st_maria_share *); uint ma_crypt_get_file_length(); /* bytes needed in file */ int ma_crypt_create(struct st_maria_share *); /* create encryption data */ int ma_crypt_write(struct st_maria_share *, File); /* write encryption data */ -uchar* ma_crypt_read(struct st_maria_share *, uchar *buff); /* read crypt data*/ +uchar* ma_crypt_read(struct st_maria_share *, uchar *buff, + my_bool silent); /* read crypt data*/ void ma_crypt_set_data_pagecache_callbacks(struct st_pagecache_file *file, struct st_maria_share *share); diff --git a/storage/maria/ma_delete_table.c b/storage/maria/ma_delete_table.c index 0c78476ad44..250ea9dff89 100644 --- a/storage/maria/ma_delete_table.c +++ b/storage/maria/ma_delete_table.c @@ -43,12 +43,13 @@ int maria_delete_table(const char *name) 'open_for_repair' to be able to open even a crashed table. */ my_errno= 0; - if (!(info= maria_open(name, O_RDONLY, HA_OPEN_FOR_REPAIR))) + if (!(info= maria_open(name, O_RDONLY, (HA_OPEN_FOR_DROP | HA_OPEN_FOR_REPAIR)))) { sync_dir= 0; /* Ignore not found errors and wrong symlink errors */ - if (my_errno != ENOENT && my_errno != HA_WRONG_CREATE_OPTION) - got_error= my_errno;; + if (my_errno != ENOENT && my_errno != HA_WRONG_CREATE_OPTION && + my_errno != HA_ERR_NO_ENCRYPTION) + got_error= my_errno; } else { diff --git a/storage/maria/ma_open.c b/storage/maria/ma_open.c index 06183c72895..7b59351e24b 100644 --- a/storage/maria/ma_open.c +++ b/storage/maria/ma_open.c @@ -863,7 +863,8 @@ MARIA_HA *maria_open(const char *name, int mode, uint open_flags) if (MY_TEST(share->base.extra_options & MA_EXTRA_OPTIONS_ENCRYPTED)) { - if (!(disk_pos= ma_crypt_read(share, disk_pos))) + if (!(disk_pos= ma_crypt_read(share, disk_pos, + MY_TEST(open_flags & HA_OPEN_FOR_DROP)))) goto err; } -- cgit v1.2.1 From 4cb0d43ac63761174a39cea892c176b9cfa6edfc Mon Sep 17 00:00:00 2001 From: Monty Date: Fri, 17 Mar 2023 12:02:04 +0200 Subject: MDEV-28054 Various crashes upon INSERT/UPDATE after changing Aria settings The cause of the crash was that test was setting aria_sort_buffer_size to MAX_LONG_LONG, which caused an overflow in my_malloc() when trying to allocate the buffer + 8 bytes. Fixed by reducing max size of sort_buffer for Aria and MyISAM Other things: - Added code in maria_repair_parallell() to not allocate a big sort buffer for small files. - Updated size of minumim sort buffer in Aria --- mysql-test/suite/maria/maria.result | 17 +---- mysql-test/suite/maria/maria.test | 18 ++--- mysql-test/suite/maria/repair-big-sort.result | 81 ++++++++++++++++++++++ mysql-test/suite/maria/repair-big-sort.test | 15 ++++ .../sys_vars/r/aria_sort_buffer_size_basic.result | 8 +-- mysql-test/suite/sys_vars/r/sysvars_aria.result | 4 +- .../sys_vars/r/sysvars_server_embedded,32bit.rdiff | 8 +-- .../sys_vars/r/sysvars_server_embedded.result | 6 +- .../sys_vars/r/sysvars_server_notembedded.result | 6 +- storage/maria/ha_maria.cc | 2 +- storage/maria/ma_check.c | 22 ++++++ storage/maria/ma_sort.c | 26 ++++--- storage/maria/maria_chk.c | 3 +- storage/maria/maria_def.h | 3 +- storage/myisam/ha_myisam.cc | 2 +- 15 files changed, 159 insertions(+), 62 deletions(-) create mode 100644 mysql-test/suite/maria/repair-big-sort.result create mode 100644 mysql-test/suite/maria/repair-big-sort.test diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 925c5121bfb..f8911bdde2b 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -2814,7 +2814,7 @@ DROP TABLE t1; # cardinalities=1 # SET aria_repair_threads=2; -SET aria_sort_buffer_size=8192; +SET aria_sort_buffer_size=16384; CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); Warnings: Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release @@ -2839,19 +2839,8 @@ SET aria_repair_threads=@@global.aria_repair_threads; # low myisam_sort_buffer_size # CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); -INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), -(6,'0'),(7,'0'); -INSERT INTO t1 SELECT a+10,b FROM t1; -INSERT INTO t1 SELECT a+20,b FROM t1; -INSERT INTO t1 SELECT a+40,b FROM t1; -INSERT INTO t1 SELECT a+80,b FROM t1; -INSERT INTO t1 SELECT a+160,b FROM t1; -INSERT INTO t1 SELECT a+320,b FROM t1; -INSERT INTO t1 SELECT a+640,b FROM t1; -INSERT INTO t1 SELECT a+1280,b FROM t1; -INSERT INTO t1 SELECT a+2560,b FROM t1; -INSERT INTO t1 SELECT a+5120,b FROM t1; -SET aria_sort_buffer_size=4096; +INSERT INTO t1 select seq,'0' from seq_1_to_65536; +SET aria_sort_buffer_size=16384; REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair error aria_sort_buffer_size is too small. X diff --git a/mysql-test/suite/maria/maria.test b/mysql-test/suite/maria/maria.test index a326bb12d2a..f7a5b5c35b2 100644 --- a/mysql-test/suite/maria/maria.test +++ b/mysql-test/suite/maria/maria.test @@ -5,6 +5,7 @@ -- source include/have_maria.inc -- source include/have_partition.inc +-- source include/have_sequence.inc call mtr.add_suppression("Can't find record in '.*'"); @@ -2034,7 +2035,7 @@ DROP TABLE t1; --echo # cardinalities=1 --echo # SET aria_repair_threads=2; -SET aria_sort_buffer_size=8192; +SET aria_sort_buffer_size=16384; CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0),(1),(2),(3); --replace_regex /Current aria_sort_buffer_size.*/X/ @@ -2050,19 +2051,8 @@ SET aria_repair_threads=@@global.aria_repair_threads; --echo # low myisam_sort_buffer_size --echo # CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); -INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), - (6,'0'),(7,'0'); -INSERT INTO t1 SELECT a+10,b FROM t1; -INSERT INTO t1 SELECT a+20,b FROM t1; -INSERT INTO t1 SELECT a+40,b FROM t1; -INSERT INTO t1 SELECT a+80,b FROM t1; -INSERT INTO t1 SELECT a+160,b FROM t1; -INSERT INTO t1 SELECT a+320,b FROM t1; -INSERT INTO t1 SELECT a+640,b FROM t1; -INSERT INTO t1 SELECT a+1280,b FROM t1; -INSERT INTO t1 SELECT a+2560,b FROM t1; -INSERT INTO t1 SELECT a+5120,b FROM t1; -SET aria_sort_buffer_size=4096; +INSERT INTO t1 select seq,'0' from seq_1_to_65536; +SET aria_sort_buffer_size=16384; --replace_regex /Current aria_sort_buffer_size.*/X/ REPAIR TABLE t1; CHECK TABLE t1; diff --git a/mysql-test/suite/maria/repair-big-sort.result b/mysql-test/suite/maria/repair-big-sort.result new file mode 100644 index 00000000000..d1dfe3f7c2f --- /dev/null +++ b/mysql-test/suite/maria/repair-big-sort.result @@ -0,0 +1,81 @@ +SET sql_mode=''; +CREATE TEMPORARY TABLE t1 (a tinyINT,b CHAR(1)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,1),(3,3),(2,2); +SET SESSION tmp_table_size=True; +Warnings: +Warning 1292 Truncated incorrect tmp_table_size value: '1' +CREATE TABLE t2 (c INT, d DATE) ENGINE=InnoDB PARTITION BY RANGE (YEAR (d)) SUBPARTITION BY HASH (TO_DAYS (d)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION s4, SUBPARTITION s5)); +SET SESSION aria_sort_buffer_size=CAST(-1 AS UNSIGNED INT); +Warnings: +Note 1105 Cast to unsigned converted negative integer to it's positive complement +Note 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 1292 Truncated incorrect aria_sort_buffer_size value: '18446744073709551615' +INSERT INTO t1 SELECT '', SEQ FROM seq_1_to_258; +Warnings: +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 1 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 2 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 3 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 4 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 5 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 6 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 7 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 8 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 9 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 10 +Warning 1265 Data truncated for column 'b' at row 10 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 11 +Warning 1265 Data truncated for column 'b' at row 11 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 12 +Warning 1265 Data truncated for column 'b' at row 12 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 13 +Warning 1265 Data truncated for column 'b' at row 13 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 14 +Warning 1265 Data truncated for column 'b' at row 14 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 15 +Warning 1265 Data truncated for column 'b' at row 15 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 16 +Warning 1265 Data truncated for column 'b' at row 16 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 17 +Warning 1265 Data truncated for column 'b' at row 17 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 18 +Warning 1265 Data truncated for column 'b' at row 18 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 19 +Warning 1265 Data truncated for column 'b' at row 19 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 20 +Warning 1265 Data truncated for column 'b' at row 20 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 21 +Warning 1265 Data truncated for column 'b' at row 21 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 22 +Warning 1265 Data truncated for column 'b' at row 22 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 23 +Warning 1265 Data truncated for column 'b' at row 23 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 24 +Warning 1265 Data truncated for column 'b' at row 24 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 25 +Warning 1265 Data truncated for column 'b' at row 25 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 26 +Warning 1265 Data truncated for column 'b' at row 26 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 27 +Warning 1265 Data truncated for column 'b' at row 27 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 28 +Warning 1265 Data truncated for column 'b' at row 28 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 29 +Warning 1265 Data truncated for column 'b' at row 29 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 30 +Warning 1265 Data truncated for column 'b' at row 30 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 31 +Warning 1265 Data truncated for column 'b' at row 31 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 32 +Warning 1265 Data truncated for column 'b' at row 32 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 33 +Warning 1265 Data truncated for column 'b' at row 33 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 34 +Warning 1265 Data truncated for column 'b' at row 34 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 35 +Warning 1265 Data truncated for column 'b' at row 35 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 36 +Warning 1265 Data truncated for column 'b' at row 36 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 37 +SET SESSION aria_repair_threads=4; +UPDATE t1 SET a=( (SELECT MAX(a) FROM t1)); +drop table t1,t2; diff --git a/mysql-test/suite/maria/repair-big-sort.test b/mysql-test/suite/maria/repair-big-sort.test new file mode 100644 index 00000000000..f2f4f84c29b --- /dev/null +++ b/mysql-test/suite/maria/repair-big-sort.test @@ -0,0 +1,15 @@ + +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_sequence.inc + +SET sql_mode=''; +CREATE TEMPORARY TABLE t1 (a tinyINT,b CHAR(1)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,1),(3,3),(2,2); +SET SESSION tmp_table_size=True; +CREATE TABLE t2 (c INT, d DATE) ENGINE=InnoDB PARTITION BY RANGE (YEAR (d)) SUBPARTITION BY HASH (TO_DAYS (d)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION s4, SUBPARTITION s5)); +SET SESSION aria_sort_buffer_size=CAST(-1 AS UNSIGNED INT); +INSERT INTO t1 SELECT '', SEQ FROM seq_1_to_258; +SET SESSION aria_repair_threads=4; +UPDATE t1 SET a=( (SELECT MAX(a) FROM t1)); +drop table t1,t2; diff --git a/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result b/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result index 56522566ec9..6da4c9bfe97 100644 --- a/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result +++ b/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result @@ -22,13 +22,13 @@ Warnings: Warning 1292 Truncated incorrect aria_sort_buffer_size value: '10' select @@global.aria_sort_buffer_size; @@global.aria_sort_buffer_size -4096 +16376 set session aria_sort_buffer_size=10; Warnings: Warning 1292 Truncated incorrect aria_sort_buffer_size value: '10' select @@session.aria_sort_buffer_size; @@session.aria_sort_buffer_size -4096 +16376 set global aria_sort_buffer_size=1.1; ERROR 42000: Incorrect argument type to variable 'aria_sort_buffer_size' set session aria_sort_buffer_size=1e1; @@ -40,9 +40,9 @@ Warnings: Warning 1292 Truncated incorrect aria_sort_buffer_size value: '0' select @@global.aria_sort_buffer_size; @@global.aria_sort_buffer_size -4096 +16376 set session aria_sort_buffer_size=cast(-1 as unsigned int); select @@session.aria_sort_buffer_size; @@session.aria_sort_buffer_size -18446744073709551615 +1152921504606846975 SET @@global.aria_sort_buffer_size = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/sysvars_aria.result b/mysql-test/suite/sys_vars/r/sysvars_aria.result index 9f5585668b6..3e9130db9db 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_aria.result +++ b/mysql-test/suite/sys_vars/r/sysvars_aria.result @@ -223,8 +223,8 @@ DEFAULT_VALUE 268434432 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. -NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MIN_VALUE 16376 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index e0aad7fa6d0..6fa25546ad1 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@ -97,9 +97,9 @@ @@ -207,7 +207,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. - NUMERIC_MIN_VALUE 4096 + NUMERIC_MIN_VALUE 16376 -NUMERIC_MAX_VALUE 18446744073709551615 -+NUMERIC_MAX_VALUE 4294967295 ++NUMERIC_MAX_VALUE 268435455 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -684,8 +684,8 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE NUMERIC_MIN_VALUE 4096 --NUMERIC_MAX_VALUE 18446744073709551615 -+NUMERIC_MAX_VALUE 4294967295 +-NUMERIC_MAX_VALUE 1152921504606846975 ++NUMERIC_MAX_VALUE 268435455 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 1ed5b5ce3bd..c03181d0c63 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -216,8 +216,8 @@ VARIABLE_NAME ARIA_SORT_BUFFER_SIZE VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. -NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MIN_VALUE 16376 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -2127,7 +2127,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 125d629ac87..48b481b200c 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -216,8 +216,8 @@ VARIABLE_NAME ARIA_SORT_BUFFER_SIZE VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. -NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MIN_VALUE 16376 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -2287,7 +2287,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index f25ba2aa0b3..6639fc39caf 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -269,7 +269,7 @@ static MYSQL_THDVAR_ULONG(repair_threads, PLUGIN_VAR_RQCMDARG, static MYSQL_THDVAR_ULONGLONG(sort_buffer_size, PLUGIN_VAR_RQCMDARG, "The buffer that is allocated when sorting the index when doing a " "REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE.", NULL, NULL, - SORT_BUFFER_INIT, MIN_SORT_BUFFER, SIZE_T_MAX, 1); + SORT_BUFFER_INIT, MARIA_MIN_SORT_MEMORY, SIZE_T_MAX/16, 1); static MYSQL_THDVAR_ENUM(stats_method, PLUGIN_VAR_RQCMDARG, "Specifies how Aria index statistics collection code should treat " diff --git a/storage/maria/ma_check.c b/storage/maria/ma_check.c index fec443dfc56..6835af928ee 100644 --- a/storage/maria/ma_check.c +++ b/storage/maria/ma_check.c @@ -3903,6 +3903,16 @@ int maria_repair_by_sort(HA_CHECK *param, register MARIA_HA *info, { sort_param.key_read= sort_key_read; sort_param.key_write= sort_key_write; + + /* + Limit usage of sort memory + We assume we don't need more memory than data file length * 2 + (There is a pointer overhead for each key, but this is hard to + estimae as we cannot be sure how many records we have in file to + be repaired). + */ + set_if_smaller(param->sort_buffer_length, sort_info.filelength*2); + set_if_bigger(param->sort_buffer_length, MARIA_MIN_SORT_MEMORY); } if (sort_info.new_info->s->data_file_type == BLOCK_RECORD) @@ -4505,6 +4515,16 @@ int maria_repair_parallel(HA_CHECK *param, register MARIA_HA *info, DBUG_PRINT("io_cache_share", ("thread: %u read_cache: %p", i, &sort_param[i].read_cache)); + /* + Limit usage of sort memory + We assume we don't need more memory than data file length * 2 + (There is a pointer overhead for each key, but this is hard to + estimae as we cannot be sure how many records we have in file to + be repaired). + */ + set_if_smaller(param->sort_buffer_length, sort_info.filelength*2); + set_if_bigger(param->sort_buffer_length, MARIA_MIN_SORT_MEMORY); + /* two approaches: the same amount of memory for each thread or the memory for the same number of keys for each thread... @@ -4517,6 +4537,8 @@ int maria_repair_parallel(HA_CHECK *param, register MARIA_HA *info, #else param->sort_buffer_length*sort_param[i].key_length/total_key_length; #endif + set_if_bigger(sort_param[i].sortbuff_size, MARIA_MIN_SORT_MEMORY); + if (mysql_thread_create(key_thread_find_all_keys, &sort_param[i].thr, &thr_attr, _ma_thr_find_all_keys, (void *) (sort_param+i))) diff --git a/storage/maria/ma_sort.c b/storage/maria/ma_sort.c index 4dc6472bd15..bd57cdb7727 100644 --- a/storage/maria/ma_sort.c +++ b/storage/maria/ma_sort.c @@ -29,12 +29,10 @@ /* static variables */ -#undef MIN_SORT_MEMORY #undef DISK_BUFFER_SIZE #define MERGEBUFF 15 #define MERGEBUFF2 31 -#define MIN_SORT_MEMORY (4096-MALLOC_OVERHEAD) #define DISK_BUFFER_SIZE (IO_SIZE*128) /* How many keys we can keep in memory */ @@ -145,11 +143,11 @@ int _ma_create_index_by_sort(MARIA_SORT_PARAM *info, my_bool no_messages, sort_keys= (uchar **) NULL; error= 1; maxbuffer=1; - memavl=MY_MAX(sortbuff_size,MIN_SORT_MEMORY); + memavl=MY_MAX(sortbuff_size,MARIA_MIN_SORT_MEMORY); records= info->sort_info->max_records; sort_length= info->key_length; - while (memavl >= MIN_SORT_MEMORY) + while (memavl >= MARIA_MIN_SORT_MEMORY) { /* Check if we can fit all keys into memory */ if (((ulonglong) (records + 1) * @@ -208,10 +206,10 @@ int _ma_create_index_by_sort(MARIA_SORT_PARAM *info, my_bool no_messages, break; } old_memavl=memavl; - if ((memavl=memavl/4*3) < MIN_SORT_MEMORY && old_memavl > MIN_SORT_MEMORY) - memavl=MIN_SORT_MEMORY; + if ((memavl=memavl/4*3) < MARIA_MIN_SORT_MEMORY && old_memavl > MARIA_MIN_SORT_MEMORY) + memavl=MARIA_MIN_SORT_MEMORY; } - if (memavl < MIN_SORT_MEMORY) + if (memavl < MARIA_MIN_SORT_MEMORY) { /* purecov: begin inspected */ _ma_check_print_error(info->sort_info->param, @@ -387,12 +385,12 @@ static my_bool _ma_thr_find_all_keys_exec(MARIA_SORT_PARAM* sort_param) bzero((char*) &sort_param->unique, sizeof(sort_param->unique)); sortbuff_size= sort_param->sortbuff_size; - memavl= MY_MAX(sortbuff_size, MIN_SORT_MEMORY); + memavl= MY_MAX(sortbuff_size, MARIA_MIN_SORT_MEMORY); idx= (ha_keys) sort_param->sort_info->max_records; sort_length= sort_param->key_length; maxbuffer= 1; - while (memavl >= MIN_SORT_MEMORY) + while (memavl >= MARIA_MIN_SORT_MEMORY) { if ((my_off_t) (idx+1)*(sort_length+sizeof(char*)) <= (my_off_t) memavl) keys= idx+1; @@ -442,11 +440,11 @@ static my_bool _ma_thr_find_all_keys_exec(MARIA_SORT_PARAM* sort_param) break; } old_memavl= memavl; - if ((memavl= memavl/4*3) < MIN_SORT_MEMORY && - old_memavl > MIN_SORT_MEMORY) - memavl= MIN_SORT_MEMORY; + if ((memavl= memavl/4*3) < MARIA_MIN_SORT_MEMORY && + old_memavl > MARIA_MIN_SORT_MEMORY) + memavl= MARIA_MIN_SORT_MEMORY; } - if (memavl < MIN_SORT_MEMORY) + if (memavl < MARIA_MIN_SORT_MEMORY) { /* purecov: begin inspected */ _ma_check_print_error(sort_param->sort_info->param, @@ -626,7 +624,7 @@ int _ma_thr_write_keys(MARIA_SORT_PARAM *sort_param) if (!mergebuf) { length=(size_t)param->sort_buffer_length; - while (length >= MIN_SORT_MEMORY) + while (length >= MARIA_MIN_SORT_MEMORY) { if ((mergebuf= my_malloc((size_t) length, MYF(0)))) break; diff --git a/storage/maria/maria_chk.c b/storage/maria/maria_chk.c index 2f130de1c7a..921751030a8 100644 --- a/storage/maria/maria_chk.c +++ b/storage/maria/maria_chk.c @@ -424,7 +424,8 @@ static struct my_option my_long_options[] = "Size of sort buffer. Used by --recover", &check_param.sort_buffer_length, &check_param.sort_buffer_length, 0, GET_ULL, REQUIRED_ARG, - SORT_BUFFER_INIT, MIN_SORT_BUFFER, SIZE_T_MAX, MALLOC_OVERHEAD, 1L, 0}, + SORT_BUFFER_INIT, MARIA_MIN_SORT_MEMORY, SIZE_T_MAX/10, MALLOC_OVERHEAD, + 1L, 0}, { "sort_key_blocks", OPT_SORT_KEY_BLOCKS, "Internal buffer for sorting keys; Don't touch :)", &check_param.sort_key_blocks, diff --git a/storage/maria/maria_def.h b/storage/maria/maria_def.h index 80c57ad9a0e..f4799eef379 100644 --- a/storage/maria/maria_def.h +++ b/storage/maria/maria_def.h @@ -45,6 +45,8 @@ #define MARIA_MAX_TREE_LEVELS 32 #define MARIA_MAX_RECORD_ON_STACK 16384 +#define MARIA_MIN_SORT_MEMORY (16384-MALLOC_OVERHEAD) + /* maria_open() flag, specific for maria_pack */ #define HA_OPEN_IGNORE_MOVED_STATE (1U << 30) @@ -1273,7 +1275,6 @@ typedef struct st_maria_block_info #define PAGE_BUFFER_INIT MY_ALIGN_DOWN(1024L*1024L*256L-MALLOC_OVERHEAD, 8192) #define READ_BUFFER_INIT MY_ALIGN_DOWN(1024L*256L-MALLOC_OVERHEAD, 1024) #define SORT_BUFFER_INIT MY_ALIGN_DOWN(1024L*1024L*256L-MALLOC_OVERHEAD, 1024) -#define MIN_SORT_BUFFER 4096U #define fast_ma_writeinfo(INFO) if (!(INFO)->s->tot_locks) (void) _ma_writeinfo((INFO),0) #define fast_ma_readinfo(INFO) ((INFO)->lock_type == F_UNLCK) && _ma_readinfo((INFO),F_RDLCK,1) diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index 4fef809f4f0..ddab8bcf741 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -80,7 +80,7 @@ static MYSQL_THDVAR_ULONG(repair_threads, PLUGIN_VAR_RQCMDARG, static MYSQL_THDVAR_ULONGLONG(sort_buffer_size, PLUGIN_VAR_RQCMDARG, "The buffer that is allocated when sorting the index when doing " "a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE", NULL, NULL, - SORT_BUFFER_INIT, MIN_SORT_BUFFER, SIZE_T_MAX, 1); + SORT_BUFFER_INIT, MIN_SORT_BUFFER, SIZE_T_MAX/16, 1); static MYSQL_SYSVAR_BOOL(use_mmap, opt_myisam_use_mmap, PLUGIN_VAR_NOCMDARG, "Use memory mapping for reading and writing MyISAM tables", NULL, NULL, FALSE); -- cgit v1.2.1 From 4f7317579e700a8ac4375b3b85e5fb1a91a1a20f Mon Sep 17 00:00:00 2001 From: Monty Date: Sat, 29 Apr 2023 20:39:50 +0300 Subject: Fixed "Trying to lock uninitialized mutex' in parallel replication The problem was that mutex_init() was called after the worker was put into the domain_hash, which allowed other threads to access it before mutex was initialized. --- sql/rpl_parallel.cc | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) diff --git a/sql/rpl_parallel.cc b/sql/rpl_parallel.cc index 746c923ba44..96b319c5f4a 100644 --- a/sql/rpl_parallel.cc +++ b/sql/rpl_parallel.cc @@ -2317,9 +2317,7 @@ rpl_parallel::find(uint32 domain_id) mysql_cond_init(key_COND_parallel_entry, &e->COND_parallel_entry, NULL); if (my_hash_insert(&domain_hash, (uchar *)e)) { - mysql_cond_destroy(&e->COND_parallel_entry); - mysql_mutex_destroy(&e->LOCK_parallel_entry); - my_free(e); + free_rpl_parallel_entry(e); return NULL; } } -- cgit v1.2.1 From 7f96dd50e25abc2a9a75d96cc1c711124b6be765 Mon Sep 17 00:00:00 2001 From: Monty Date: Tue, 2 May 2023 22:30:57 +0300 Subject: MDEV-6768 Wrong result with aggregate with join with no result set When a query does implicit grouping and join operation produces an empty result set, a NULL-complemented row combination is generated. However, constant table fields still show non-NULL values. What happens in the is that end_send_group() is called with a const row but without any rows matching the WHERE clause. This last part is shown by 'join->first_record' not being set. This causes item->no_rows_in_result() to be called for all items to reset all sum functions to their initial state. However fields are not set to NULL. The used fix is to produce NULL-complemented records for constant tables as well. Also, reset the constant table's records back in case we're in a subquery which may get re-executed. An alternative fix would have item->no_rows_in_result() also work with Item_field objects. There is some other issues with the code: - join->no_rows_in_result_called is used but never set. - Tables that are used with group functions are not properly marked as maybe_null, which is required if the table rows should be regarded as null-complemented (not existing). - The code that tries to detect if mixed_implicit_grouping should be set didn't take into account all usage of fields and sum functions. - Item_func::restore_to_before_no_rows_in_result() called the wrong function. - join->clear() does not use a table_map argument to clear_tables(), which caused it to ignore constant tables. - unclear_tables() does not correctly restore status to what is was before clear_tables(). Main bug fix was to always use a table_map argument to clear_tables() and always use join->clear() and clear_tables() together with unclear_tables(). Other fixes: - Fixed Item_func::restore_to_before_no_rows_in_result() - Set 'join->no_rows_in_result_called' when no_rows_in_result_set() is called. - Removed not used argument from setup_end_select_func(). - More code comments - Ensure that end_send_group() modifies the same fields as are in the result set. - Changed return_zero_rows() to use pointers instead of references, similar to the rest of the code. --- mysql-test/main/group_min_max.result | 113 +++++++++++++++ mysql-test/main/group_min_max.test | 115 +++++++++++++++ mysql-test/main/type_timestamp.result | 2 + mysql-test/main/type_timestamp.test | 1 + sql/item_func.h | 2 +- sql/sql_select.cc | 264 +++++++++++++++++++++------------- sql/sql_select.h | 5 +- sql/table.h | 10 +- 8 files changed, 405 insertions(+), 107 deletions(-) diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index fd9b5be4260..4c2693e5e4c 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4025,3 +4025,116 @@ drop table t1; # # End of 10.1 tests # +# +# MDEV-6768 Wrong result with agregate with join with no resultset +# +create table t1 +( +PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT, +PARENT_FIELD VARCHAR(10), +PRIMARY KEY (PARENT_ID) +) engine=innodb; +create table t2 +( +CHILD_ID INT NOT NULL AUTO_INCREMENT, +PARENT_ID INT NOT NULL, +CHILD_FIELD varchar(10), +PRIMARY KEY (CHILD_ID) +)engine=innodb; +INSERT INTO t1 (PARENT_FIELD) +SELECT 'AAAA'; +INSERT INTO t2 (PARENT_ID, CHILD_FIELD) +SELECT 1, 'BBBB'; +explain select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where +select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +PARENT_ID min(CHILD_FIELD) +NULL NULL +select +1, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +1 min(CHILD_FIELD) +1 NULL +select +IFNULL(t1.PARENT_ID,1), +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +IFNULL(t1.PARENT_ID,1) min(CHILD_FIELD) +1 NULL +# Check that things works with MyISAM (which has different explain) +alter table t1 engine=myisam; +alter table t2 engine=myisam; +explain select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +PARENT_ID min(CHILD_FIELD) +NULL NULL +drop table t1,t2; +# Check that things works if sub queries are re-executed +create table t1 (a int primary key, b int); +create table t2 (a int primary key, b int); +create table t3 (a int primary key, b int); +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1,1),(2,2),(3,3); +insert into t3 values (1,1),(3,3); +explain +select *, +(select +CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', +'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) +from t2,t3 +where t2.a=1 and t1.b = t3.a) as s1 +from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using index +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +select *, +(select +CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', +'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) +from t2,t3 +where t2.a=1 and t1.b = t3.a) as s1 +from t1; +a b s1 +1 1 t2:1;min_t3_b:1 +2 2 t2:t2a-null;min_t3_b:t3b-null +3 3 t2:1;min_t3_b:3 +drop table t1,t2,t3; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 506323599cb..3c5c1b5bb9b 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -5,6 +5,7 @@ --source include/no_valgrind_without_big.inc --source include/default_optimizer_switch.inc +--source include/have_innodb.inc # # TODO: @@ -1688,3 +1689,117 @@ drop table t1; --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # MDEV-6768 Wrong result with agregate with join with no resultset +--echo # + +create table t1 +( + PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT, + PARENT_FIELD VARCHAR(10), + PRIMARY KEY (PARENT_ID) +) engine=innodb; + +create table t2 +( + CHILD_ID INT NOT NULL AUTO_INCREMENT, + PARENT_ID INT NOT NULL, + CHILD_FIELD varchar(10), + PRIMARY KEY (CHILD_ID) +)engine=innodb; + +INSERT INTO t1 (PARENT_FIELD) +SELECT 'AAAA'; + +INSERT INTO t2 (PARENT_ID, CHILD_FIELD) +SELECT 1, 'BBBB'; + +explain select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + 1, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + IFNULL(t1.PARENT_ID,1), + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + + +--echo # Check that things works with MyISAM (which has different explain) + +alter table t1 engine=myisam; +alter table t2 engine=myisam; + +explain select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +drop table t1,t2; + +--echo # Check that things works if sub queries are re-executed + +create table t1 (a int primary key, b int); +create table t2 (a int primary key, b int); +create table t3 (a int primary key, b int); + +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1,1),(2,2),(3,3); +insert into t3 values (1,1),(3,3); + +explain +select *, + (select + CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', + 'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) + from t2,t3 + where t2.a=1 and t1.b = t3.a) as s1 +from t1; + +select *, + (select + CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', + 'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) + from t2,t3 + where t2.a=1 and t1.b = t3.a) as s1 +from t1; + +drop table t1,t2,t3; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index 58cb12ae267..dbccee08ada 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -1230,6 +1230,8 @@ SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r'); c1 Warnings: Warning 1292 Truncated incorrect datetime value: 'r' +SELECT * FROM t1 HAVING MIN(t1.c1) > 0; +c1 DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); INSERT INTO t1 VALUES ('2010-01-01 00:00:00'); diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index f12cc2a4bc3..c8517656071 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -810,6 +810,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); SELECT MIN(t1.c1) AS k1 FROM t1 HAVING (k1 >= ALL(SELECT 'a' UNION SELECT 'r')); SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r'); +SELECT * FROM t1 HAVING MIN(t1.c1) > 0; DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); diff --git a/sql/item_func.h b/sql/item_func.h index 41f2a52616d..3afe0d00661 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -381,7 +381,7 @@ public: { for (uint i= 0; i < arg_count; i++) { - args[i]->no_rows_in_result(); + args[i]->restore_to_before_no_rows_in_result(); } } void convert_const_compared_to_int_field(THD *thd); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 56a185acdd5..2be1fea9b03 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -142,10 +142,10 @@ static void update_depend_map_for_order(JOIN *join, ORDER *order); static ORDER *remove_const(JOIN *join,ORDER *first_order,COND *cond, bool change_list, bool *simple_order); static int return_zero_rows(JOIN *join, select_result *res, - List &tables, - List &fields, bool send_row, + List *tables, + List *fields, bool send_row, ulonglong select_options, const char *info, - Item *having, List &all_fields); + Item *having, List *all_fields); static COND *build_equal_items(JOIN *join, COND *cond, COND_EQUAL *inherited, List *join_list, @@ -1157,11 +1157,40 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables) DBUG_RETURN(0); } + /***************************************************************************** Check fields, find best join, do the select and output fields. mysql_select assumes that all tables are already opened *****************************************************************************/ +/* + Check if we have a field reference. If yes, we have to use + mixed_implicit_grouping. +*/ + +static bool check_list_for_field(List *items) +{ + List_iterator_fast select_it(*items); + Item *select_el; + + while ((select_el= select_it++)) + { + if (select_el->with_field) + return true; + } + return false; +} + +static bool check_list_for_field(ORDER *order) +{ + for (; order; order= order->next) + { + if (order->item[0]->with_field) + return true; + } + return false; +} + /** Prepare of whole select (including sub queries in future). @@ -1241,53 +1270,45 @@ JOIN::prepare(TABLE_LIST *tables_init, DBUG_RETURN(-1); /* - TRUE if the SELECT list mixes elements with and without grouping, - and there is no GROUP BY clause. Mixing non-aggregated fields with - aggregate functions in the SELECT list is a MySQL extenstion that - is allowed only if the ONLY_FULL_GROUP_BY sql mode is not set. + mixed_implicit_grouping will be set to TRUE if the SELECT list + mixes elements with and without grouping, and there is no GROUP BY + clause. + Mixing non-aggregated fields with aggregate functions in the + SELECT list or HAVING is a MySQL extension that is allowed only if + the ONLY_FULL_GROUP_BY sql mode is not set. */ mixed_implicit_grouping= false; if ((~thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) && select_lex->with_sum_func && !group_list) { - List_iterator_fast select_it(fields_list); - Item *select_el; /* Element of the SELECT clause, can be an expression. */ - bool found_field_elem= false; - bool found_sum_func_elem= false; - - while ((select_el= select_it++)) + if (check_list_for_field(&fields_list) || + check_list_for_field(order)) { - if (select_el->with_sum_func()) - found_sum_func_elem= true; - if (select_el->with_field) - found_field_elem= true; - if (found_sum_func_elem && found_field_elem) + TABLE_LIST *tbl; + List_iterator_fast li(select_lex->leaf_tables); + + mixed_implicit_grouping= true; // mark for future + + while ((tbl= li++)) { - mixed_implicit_grouping= true; - break; + /* + If the query uses implicit grouping where the select list + contains both aggregate functions and non-aggregate fields, + any non-aggregated field may produce a NULL value. Set all + fields of each table as nullable before semantic analysis to + take into account this change of nullability. + + Note: this loop doesn't touch tables inside merged + semi-joins, because subquery-to-semijoin conversion has not + been done yet. This is intended. + */ + if (tbl->table) + tbl->table->maybe_null= 1; } } } - table_count= select_lex->leaf_tables.elements; - TABLE_LIST *tbl; - List_iterator_fast li(select_lex->leaf_tables); - while ((tbl= li++)) - { - /* - If the query uses implicit grouping where the select list contains both - aggregate functions and non-aggregate fields, any non-aggregated field - may produce a NULL value. Set all fields of each table as nullable before - semantic analysis to take into account this change of nullability. - - Note: this loop doesn't touch tables inside merged semi-joins, because - subquery-to-semijoin conversion has not been done yet. This is intended. - */ - if (mixed_implicit_grouping && tbl->table) - tbl->table->maybe_null= 1; - } - uint real_og_num= og_num; if (skip_order_by && select_lex != select_lex->master_unit()->global_parameters()) @@ -3838,7 +3859,7 @@ bool JOIN::make_aggr_tables_info() set_items_ref_array(items0); if (join_tab) join_tab[exec_join_tab_cnt() + aggr_tables - 1].next_select= - setup_end_select_func(this, NULL); + setup_end_select_func(this); group= has_group_by; DBUG_RETURN(false); @@ -4220,13 +4241,7 @@ JOIN::reinit() } } - /* Reset of sum functions */ - if (sum_funcs) - { - Item_sum *func, **func_ptr= sum_funcs; - while ((func= *(func_ptr++))) - func->clear(); - } + clear_sum_funcs(); if (no_rows_in_result_called) { @@ -4510,12 +4525,12 @@ void JOIN::exec_inner() } else { - (void) return_zero_rows(this, result, select_lex->leaf_tables, - *columns_list, + (void) return_zero_rows(this, result, &select_lex->leaf_tables, + columns_list, send_row_on_empty_set(), select_options, zero_result_cause, - having ? having : tmp_having, all_fields); + having ? having : tmp_having, &all_fields); DBUG_VOID_RETURN; } } @@ -14626,10 +14641,36 @@ ORDER *simple_remove_const(ORDER *order, COND *where) } +/* + Set all fields in the table to have a null value + + @param tables Table list +*/ + +static void make_tables_null_complemented(List *tables) +{ + List_iterator ti(*tables); + TABLE_LIST *table; + while ((table= ti++)) + { + /* + Don't touch semi-join materialization tables, as the a join_free() + call may have freed them (and HAVING clause can't have references to + them anyway). + */ + if (!table->is_jtbm()) + { + TABLE *tbl= table->table; + mark_as_null_row(tbl); // Set fields to NULL + } + } +} + + static int -return_zero_rows(JOIN *join, select_result *result, List &tables, - List &fields, bool send_row, ulonglong select_options, - const char *info, Item *having, List &all_fields) +return_zero_rows(JOIN *join, select_result *result, List *tables, + List *fields, bool send_row, ulonglong select_options, + const char *info, Item *having, List *all_fields) { DBUG_ENTER("return_zero_rows"); @@ -14645,24 +14686,15 @@ return_zero_rows(JOIN *join, select_result *result, List &tables, Set all tables to have NULL row. This is needed as we will be evaluating HAVING condition. */ - List_iterator ti(tables); - TABLE_LIST *table; - while ((table= ti++)) - { - /* - Don't touch semi-join materialization tables, as the above join_free() - call has freed them (and HAVING clause can't have references to them - anyway). - */ - if (!table->is_jtbm()) - mark_as_null_row(table->table); // All fields are NULL - } - List_iterator_fast it(all_fields); + make_tables_null_complemented(tables); + + List_iterator_fast it(*all_fields); Item *item; /* Inform all items (especially aggregating) to calculate HAVING correctly, also we will need it for sending results. */ + join->no_rows_in_result_called= 1; while ((item= it++)) item->no_rows_in_result(); if (having && having->val_int() == 0) @@ -14676,12 +14708,12 @@ return_zero_rows(JOIN *join, select_result *result, List &tables, join->thd->limit_found_rows= 0; } - if (!(result->send_result_set_metadata(fields, + if (!(result->send_result_set_metadata(*fields, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))) { bool send_error= FALSE; if (send_row) - send_error= result->send_data(fields) > 0; + send_error= result->send_data(*fields) > 0; if (likely(!send_error)) result->send_eof(); // Should be safe } @@ -14697,49 +14729,42 @@ return_zero_rows(JOIN *join, select_result *result, List &tables, } /** - used only in JOIN::clear (always) and in do_select() - (if there where no matching rows) + Reset table rows to contain a null-complement row (all fields are null) + + Used only in JOIN::clear() and in do_select() if there where no matching rows. @param join JOIN - @param cleared_tables If not null, clear also const tables and mark all - cleared tables in the map. cleared_tables is only - set when called from do_select() when there is a - group function and there where no matching rows. + @param cleared_tables Used to mark all cleared tables in the map. Needed for + unclear_tables() to know which tables to restore to + their original state. */ static void clear_tables(JOIN *join, table_map *cleared_tables) { - /* - must clear only the non-const tables as const tables are not re-calculated. - */ + DBUG_ASSERT(cleared_tables); for (uint i= 0 ; i < join->table_count ; i++) { TABLE *table= join->table[i]; if (table->null_row) continue; // Nothing more to do - if (!(table->map & join->const_table_map) || cleared_tables) + (*cleared_tables)|= (((table_map) 1) << i); + if (table->s->null_bytes) { - if (cleared_tables) - { - (*cleared_tables)|= (((table_map) 1) << i); - if (table->s->null_bytes) - { - /* - Remember null bits for the record so that we can restore the - original const record in unclear_tables() - */ - memcpy(table->record[1], table->null_flags, table->s->null_bytes); - } - } - mark_as_null_row(table); // All fields are NULL + /* + Remember null bits for the record so that we can restore the + original const record in unclear_tables() + */ + memcpy(table->record[1], table->null_flags, table->s->null_bytes); } + mark_as_null_row(table); // All fields are NULL } } /** Reverse null marking for tables and restore null bits. + This return the tables to the state of before clear_tables(). We have to do this because the tables may be re-used in a sub query and the subquery will assume that the const tables contains the original @@ -20216,9 +20241,9 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab) end_select function to use. This function can't fail. */ -Next_select_func setup_end_select_func(JOIN *join, JOIN_TAB *tab) +Next_select_func setup_end_select_func(JOIN *join) { - TMP_TABLE_PARAM *tmp_tbl= tab ? tab->tmp_table_param : &join->tmp_table_param; + TMP_TABLE_PARAM *tmp_tbl= &join->tmp_table_param; /* Choose method for presenting result to user. Use end_send_group @@ -20289,7 +20314,7 @@ do_select(JOIN *join, Procedure *procedure) join->duplicate_rows= join->send_records=0; if (join->only_const_tables() && !join->need_tmp) { - Next_select_func end_select= setup_end_select_func(join, NULL); + Next_select_func end_select= setup_end_select_func(join); /* HAVING will be checked after processing aggregate functions, @@ -20764,6 +20789,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) { DBUG_ENTER("sub_select"); + /* Restore state if mark_as_null_row() have been called */ if (join_tab->last_inner) { JOIN_TAB *last_inner_tab= join_tab->last_inner; @@ -22126,11 +22152,18 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), { int idx= -1; enum_nested_loop_state ok_code= NESTED_LOOP_OK; + /* + join_tab can be 0 in the case all tables are const tables and we did not + need a temporary table to store the result. + In this case we use the original given fields, which is stored in + join->fields. + */ List *fields= join_tab ? (join_tab-1)->fields : join->fields; DBUG_ENTER("end_send_group"); if (!join->items3.is_null() && !join->set_group_rpa) { + /* Move ref_pointer_array to points to items3 */ join->set_group_rpa= true; join->set_items_ref_array(join->items3); } @@ -22138,10 +22171,12 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (!join->first_record || end_of_records || (idx=test_if_group_changed(join->group_fields)) >= 0) { + if (!join->group_sent && (join->first_record || (end_of_records && !join->group && !join->group_optimized_away))) { + table_map cleared_tables= (table_map) 0; if (join->procedure) join->procedure->end_group(); if (idx < (int) join->send_group_parts) @@ -22164,11 +22199,13 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), { if (!join->first_record) { - List_iterator_fast it(*join->fields); - Item *item; /* No matching rows for group function */ - join->clear(); + List_iterator_fast it(*fields); + Item *item; + join->no_rows_in_result_called= 1; + + join->clear(&cleared_tables); while ((item= it++)) item->no_rows_in_result(); } @@ -22194,7 +22231,14 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (join->rollup_send_data((uint) (idx+1))) error= 1; } - } + if (join->no_rows_in_result_called) + { + /* Restore null tables to original state */ + join->no_rows_in_result_called= 0; + if (cleared_tables) + unclear_tables(join, &cleared_tables); + } + } if (unlikely(error > 0)) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if (end_of_records) @@ -22496,6 +22540,7 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), { if (join->first_record || (end_of_records && !join->group)) { + table_map cleared_tables= (table_map) 0; if (join->procedure) join->procedure->end_group(); int send_group_parts= join->send_group_parts; @@ -22504,7 +22549,7 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (!join->first_record) { /* No matching rows for group function */ - join->clear(); + join->clear(&cleared_tables); } copy_sum_funcs(join->sum_funcs, join->sum_funcs_end[send_group_parts]); @@ -22527,6 +22572,8 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_RETURN(NESTED_LOOP_ERROR); } } + if (cleared_tables) + unclear_tables(join, &cleared_tables); if (end_of_records) goto end; } @@ -26629,11 +26676,8 @@ int JOIN::rollup_write_data(uint idx, TMP_TABLE_PARAM *tmp_table_param_arg, TABL (end_send_group/end_write_group) */ -void JOIN::clear() +void inline JOIN::clear_sum_funcs() { - clear_tables(this, 0); - copy_fields(&tmp_table_param); - if (sum_funcs) { Item_sum *func, **func_ptr= sum_funcs; @@ -26643,6 +26687,22 @@ void JOIN::clear() } +/* + Prepare for returning 'empty row' when there is no matching row. + + - Mark all tables with mark_as_null_row() + - Make a copy of of all simple SELECT items + - Reset all sum functions to NULL or 0. +*/ + +void JOIN::clear(table_map *cleared_tables) +{ + clear_tables(this, cleared_tables); + copy_fields(&tmp_table_param); + clear_sum_funcs(); +} + + /** Print an EXPLAIN line with all NULLs and given message in the 'Extra' column diff --git a/sql/sql_select.h b/sql/sql_select.h index 0dfecc98a48..4bac201c3b4 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -227,7 +227,7 @@ enum sj_strategy_enum typedef enum_nested_loop_state (*Next_select_func)(JOIN *, struct st_join_table *, bool); -Next_select_func setup_end_select_func(JOIN *join, JOIN_TAB *tab); +Next_select_func setup_end_select_func(JOIN *join); int rr_sequential(READ_RECORD *info); int rr_sequential_and_unpack(READ_RECORD *info); Item *remove_pushed_top_conjuncts(THD *thd, Item *cond); @@ -1730,7 +1730,8 @@ public: void join_free(); /** Cleanup this JOIN, possibly for reuse */ void cleanup(bool full); - void clear(); + void clear(table_map *cleared_tables); + void inline clear_sum_funcs(); bool send_row_on_empty_set() { return (do_send_rows && implicit_grouping && !group_optimized_away && diff --git a/sql/table.h b/sql/table.h index 6f7f4e63473..1414659b78f 100644 --- a/sql/table.h +++ b/sql/table.h @@ -3337,10 +3337,16 @@ inline void mark_as_null_row(TABLE *table) bfill(table->null_flags,table->s->null_bytes,255); } +/* + Restore table to state before mark_as_null_row() call. + This assumes that the caller has restored table->null_flags, + as is done in unclear_tables(). +*/ + inline void unmark_as_null_row(TABLE *table) { - table->null_row=0; - table->status= STATUS_NO_RECORD; + table->null_row= 0; + table->status&= ~STATUS_NULL_ROW; } bool is_simple_order(ORDER *order); -- cgit v1.2.1 From 9b6f87b62afb32ea238e39b362b8e761f53e541c Mon Sep 17 00:00:00 2001 From: sara Date: Wed, 3 May 2023 01:34:32 +0200 Subject: MDEV-30892 test galera.galera_log_bin is not deterministic galera.galera_log_bin test created the test tables and executed initial DML into node 2 Then connection is switched to node 1, where ALTER TABLE was attempted. But there is no guarantee that the table to alter was yet replicated to node 1. The fix in this commit, creates the test tables in node 1 instead, so it is guaranteed that they are available for the later ALTER Signed-off-by: Julius Goryavsky --- mysql-test/suite/galera/r/galera_log_bin.result | 4 ++-- mysql-test/suite/galera/r/galera_log_bin_ext.result | 4 ++-- mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result | 4 ++-- mysql-test/suite/galera/r/galera_log_bin_opt.result | 4 ++-- mysql-test/suite/galera/t/galera_log_bin.inc | 4 ++-- mysql-test/suite/galera/t/galera_log_bin_sst.inc | 4 ++-- 6 files changed, 12 insertions(+), 12 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_log_bin.result b/mysql-test/suite/galera/r/galera_log_bin.result index df7ace9d95a..31f953bfd36 100644 --- a/mysql-test/suite/galera/r/galera_log_bin.result +++ b/mysql-test/suite/galera/r/galera_log_bin.result @@ -1,10 +1,10 @@ connection node_2; connection node_1; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/r/galera_log_bin_ext.result b/mysql-test/suite/galera/r/galera_log_bin_ext.result index b110cb4dba3..9d7ea473241 100644 --- a/mysql-test/suite/galera/r/galera_log_bin_ext.result +++ b/mysql-test/suite/galera/r/galera_log_bin_ext.result @@ -2,11 +2,11 @@ connection node_2; connection node_1; connection node_1; connection node_2; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result b/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result index b110cb4dba3..9d7ea473241 100644 --- a/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result +++ b/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result @@ -2,11 +2,11 @@ connection node_2; connection node_1; connection node_1; connection node_2; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/r/galera_log_bin_opt.result b/mysql-test/suite/galera/r/galera_log_bin_opt.result index df7ace9d95a..31f953bfd36 100644 --- a/mysql-test/suite/galera/r/galera_log_bin_opt.result +++ b/mysql-test/suite/galera/r/galera_log_bin_opt.result @@ -1,10 +1,10 @@ connection node_2; connection node_1; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/t/galera_log_bin.inc b/mysql-test/suite/galera/t/galera_log_bin.inc index 4c245846752..3cac28047d4 100644 --- a/mysql-test/suite/galera/t/galera_log_bin.inc +++ b/mysql-test/suite/galera/t/galera_log_bin.inc @@ -1,11 +1,11 @@ --source include/galera_cluster.inc --source include/force_restart.inc ---connection node_1 +--connection node_2 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; ---connection node_2 +--connection node_1 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/t/galera_log_bin_sst.inc b/mysql-test/suite/galera/t/galera_log_bin_sst.inc index 3d20add6d9e..87420ad539f 100644 --- a/mysql-test/suite/galera/t/galera_log_bin_sst.inc +++ b/mysql-test/suite/galera/t/galera_log_bin_sst.inc @@ -6,11 +6,11 @@ --let $node_2=node_2 --source include/auto_increment_offset_save.inc ---connection node_1 +--connection node_2 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; ---connection node_2 +--connection node_1 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -- cgit v1.2.1 From 01ea779149a32f0dfd6fe8ea52c7ba51a69e1016 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 28 Apr 2023 14:41:27 +0400 Subject: MDEV-31174 New class Native_functions_hash --- plugin/versioning/versioning.cc | 6 ++-- sql/item_create.cc | 78 +++++++++++++++++++++++++---------------- sql/item_create.h | 57 +++++++++++++++++++++++------- sql/sql_lex.cc | 2 +- sql/sql_show.cc | 3 -- sql/sql_yacc.yy | 2 +- sql/sql_yacc_ora.yy | 2 +- 7 files changed, 99 insertions(+), 51 deletions(-) diff --git a/plugin/versioning/versioning.cc b/plugin/versioning/versioning.cc index e150e4e8465..8b9fb74e77b 100644 --- a/plugin/versioning/versioning.cc +++ b/plugin/versioning/versioning.cc @@ -140,7 +140,7 @@ Create_func_trt_trx_sees Create_func_trt_trx_sees::s_singleton; #define BUILDER(F) & F::s_singleton -static Native_func_registry func_array[] = +static const Native_func_registry func_array_vers[] = { { { C_STRING_WITH_LEN("TRT_BEGIN_TS") }, BUILDER(Create_func_trt)}, { { C_STRING_WITH_LEN("TRT_COMMIT_ID") }, BUILDER(Create_func_trt)}, @@ -164,7 +164,7 @@ static int versioning_plugin_init(void *p __attribute__ ((unused))) { DBUG_ENTER("versioning_plugin_init"); // No need in locking since we so far single-threaded - int res= item_create_append(func_array); + int res= native_functions_hash.append(func_array_vers); if (res) { my_message(ER_PLUGIN_IS_NOT_LOADED, "Can't append function array" , MYF(0)); @@ -177,7 +177,7 @@ static int versioning_plugin_init(void *p __attribute__ ((unused))) static int versioning_plugin_deinit(void *p __attribute__ ((unused))) { DBUG_ENTER("versioning_plugin_deinit"); - (void) item_create_remove(func_array); + (void) native_functions_hash.remove(func_array_vers); DBUG_RETURN(0); } diff --git a/sql/item_create.cc b/sql/item_create.cc index 616ba3a641a..a95d2ae4f9a 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -7249,7 +7249,7 @@ Create_func_year_week::create_native(THD *thd, const LEX_CSTRING *name, - keep 1 line per entry, it makes grep | sort easier */ -Native_func_registry func_array[] = +const Native_func_registry func_array[] = { { { STRING_WITH_LEN("ABS") }, BUILDER(Create_func_abs)}, { { STRING_WITH_LEN("ACOS") }, BUILDER(Create_func_acos)}, @@ -7609,9 +7609,10 @@ Native_func_registry func_array[] = { {0, 0}, NULL} }; -size_t func_array_length= sizeof(func_array) / sizeof(Native_func_registry) - 1; -static HASH native_functions_hash; +const size_t func_array_length= sizeof(func_array) / sizeof(Native_func_registry) - 1; + +Native_functions_hash native_functions_hash; extern "C" uchar* get_native_fct_hash_key(const uchar *buff, size_t *length, @@ -7628,85 +7629,89 @@ get_native_fct_hash_key(const uchar *buff, size_t *length, startup only (before going multi-threaded) */ -int item_create_init() +bool Native_functions_hash::init(size_t count) { - DBUG_ENTER("item_create_init"); + DBUG_ENTER("Native_functions_hash::init"); - if (my_hash_init(& native_functions_hash, + if (my_hash_init(this, system_charset_info, - array_elements(func_array), + (ulong) count, 0, 0, (my_hash_get_key) get_native_fct_hash_key, NULL, /* Nothing to free */ MYF(0))) - DBUG_RETURN(1); + DBUG_RETURN(true); - DBUG_RETURN(item_create_append(func_array)); + DBUG_RETURN(false); } -int item_create_append(Native_func_registry array[]) + +bool Native_functions_hash::append(const Native_func_registry array[]) { - Native_func_registry *func; + const Native_func_registry *func; - DBUG_ENTER("item_create_append"); + DBUG_ENTER("Native_functions_hash::append"); for (func= array; func->builder != NULL; func++) { - if (my_hash_insert(& native_functions_hash, (uchar*) func)) - DBUG_RETURN(1); + if (my_hash_insert(this, (uchar*) func)) + DBUG_RETURN(true); } #ifndef DBUG_OFF - for (uint i=0 ; i < native_functions_hash.records ; i++) + for (uint i=0 ; i < records ; i++) { - func= (Native_func_registry*) my_hash_element(& native_functions_hash, i); + func= (Native_func_registry*) my_hash_element(this, i); DBUG_PRINT("info", ("native function: %s length: %u", func->name.str, (uint) func->name.length)); } #endif - DBUG_RETURN(0); + DBUG_RETURN(false); } -int item_create_remove(Native_func_registry array[]) + +bool Native_functions_hash::remove(const Native_func_registry array[]) { - Native_func_registry *func; + const Native_func_registry *func; - DBUG_ENTER("item_create_remove"); + DBUG_ENTER("Native_functions_hash::remove"); for (func= array; func->builder != NULL; func++) { - if (my_hash_delete(& native_functions_hash, (uchar*) func)) - DBUG_RETURN(1); + if (my_hash_delete(this, (uchar*) func)) + DBUG_RETURN(true); } - DBUG_RETURN(0); + DBUG_RETURN(false); } + /* Empty the hash table for native functions. Note: this code is not thread safe, and is intended to be used at server shutdown only (after thread requests have been executed). */ -void item_create_cleanup() +void Native_functions_hash::cleanup() { - DBUG_ENTER("item_create_cleanup"); - my_hash_free(& native_functions_hash); + DBUG_ENTER("Native_functions_hash::cleanup"); + my_hash_free(this); DBUG_VOID_RETURN; } + Create_func * -find_native_function_builder(THD *thd, const LEX_CSTRING *name) +Native_functions_hash::find(THD *thd, const LEX_CSTRING &name) const { Native_func_registry *func; Create_func *builder= NULL; /* Thread safe */ - func= (Native_func_registry*) my_hash_search(&native_functions_hash, - (uchar*) name->str, - name->length); + func= (Native_func_registry*) my_hash_search(this, + (uchar*) name.str, + name.length); if (func) { @@ -7716,6 +7721,19 @@ find_native_function_builder(THD *thd, const LEX_CSTRING *name) return builder; } + +int item_create_init() +{ + return native_functions_hash.init(func_array, array_elements(func_array)); +} + + +void item_create_cleanup() +{ + native_functions_hash.cleanup(); +} + + Create_qfunc * find_qualified_function_builder(THD *thd) { diff --git a/sql/item_create.h b/sql/item_create.h index 8456644ff6d..60a757b95d8 100644 --- a/sql/item_create.h +++ b/sql/item_create.h @@ -144,16 +144,6 @@ protected: }; -/** - Find the native function builder associated with a given function name. - @param thd The current thread - @param name The native function name - @return The native function builder associated with the name, or NULL -*/ -extern Create_func *find_native_function_builder(THD *thd, - const LEX_CSTRING *name); - - /** Find the function builder for qualified functions. @param thd The current thread @@ -200,9 +190,52 @@ struct Native_func_registry Create_func *builder; }; + +class Native_functions_hash: public HASH +{ +public: + Native_functions_hash() + { + bzero(this, sizeof(*this)); + } + ~Native_functions_hash() + { + /* + No automatic free because objects of this type + are expected to be declared statically. + The code in cleanup() calls my_hash_free() which may not work correctly + at the very end of mariadbd shutdown. + The the upper level code should call cleanup() explicitly. + + Unfortunatelly, it's not possible to use DBUG_ASSERT(!records) here, + because the server terminates using exit() in some cases, + e.g. in the test main.named_pipe with the "Create named pipe failed" + error. + */ + } + bool init(size_t count); + bool init(const Native_func_registry array[], size_t count) + { + return init(count) || append(array); + } + bool append(const Native_func_registry array[]); + bool remove(const Native_func_registry array[]); + void cleanup(); + /** + Find the native function builder associated with a given function name. + @param thd The current thread + @param name The native function name + @return The native function builder associated with the name, or NULL + */ + Create_func *find(THD *thd, const LEX_CSTRING &name) const; +}; + +extern MYSQL_PLUGIN_IMPORT Native_functions_hash native_functions_hash; + +extern const Native_func_registry func_array[]; +extern const size_t func_array_length; + int item_create_init(); -int item_create_append(Native_func_registry array[]); -int item_create_remove(Native_func_registry array[]); void item_create_cleanup(); Item *create_func_dyncol_create(THD *thd, List &list); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f77b98f04f1..019377061a0 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -943,7 +943,7 @@ bool is_lex_native_function(const LEX_CSTRING *name) bool is_native_function(THD *thd, const LEX_CSTRING *name) { - if (find_native_function_builder(thd, name)) + if (native_functions_hash.find(thd, *name)) return true; if (is_lex_native_function(name)) diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 1ad20ac7593..a621c1de29a 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -76,9 +76,6 @@ extern size_t symbols_length; extern SYMBOL sql_functions[]; extern size_t sql_functions_length; -extern Native_func_registry func_array[]; -extern size_t func_array_length; - enum enum_i_s_events_fields { ISE_EVENT_CATALOG= 0, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index cf3927ac30c..cdc04d93708 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11485,7 +11485,7 @@ function_call_generic: This will be revised with WL#2128 (SQL PATH) */ - builder= find_native_function_builder(thd, &$1); + builder= native_functions_hash.find(thd, $1); if (builder) { item= builder->create_func(thd, &$1, $4); diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 87f71d8332b..5b734a27f8c 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -11600,7 +11600,7 @@ function_call_generic: This will be revised with WL#2128 (SQL PATH) */ - builder= find_native_function_builder(thd, &$1); + builder= native_functions_hash.find(thd, $1); if (builder) { item= builder->create_func(thd, &$1, $4); -- cgit v1.2.1 From 08a4732860348bcdc16b4ad8ecfc2b4b2e644ae5 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 3 May 2023 21:27:30 +0300 Subject: MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size The problem was that join_buffer_size conflicted with join_buffer_space_limit, which caused the query to be run without join buffer. However this caused wrong results as the optimizer assumed that hash+join buffer would ensure that the equi-join condition would be satisfied, and didn't check it itself. Fixed by not using join_buffer_space_limit when optimize_join_buffer_size=off. This matches the documentation at https://mariadb.com/kb/en/block-based-join-algorithms Other things: - Removed not used variable JOIN_TAB::join_buffer_size_limit - Give an error if we cannot allocate a join buffer. This can only happen if the join_buffer variables are wrongly configured or we are running out of memory. In the future, instead of returning an error, we could properly convert the query plan that uses BNL-H join into one that doesn't use join buffering: make sure the equi-join condition is checked where appropriate. Reviewer: Sergei Petrunia --- mysql-test/main/join_cache.result | 33 ++++++++++++++++-- mysql-test/main/join_cache.test | 28 +++++++++++++++ mysql-test/main/join_optimizer.test | 2 ++ sql/sql_join_cache.cc | 68 ++++++++++++++++++++++++------------- sql/sql_select.h | 1 - 5 files changed, 104 insertions(+), 28 deletions(-) diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 1837576e719..3b02740c67c 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -5655,13 +5655,13 @@ EXPLAIN SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; a a b b c +3 3 32 32 302 3 3 30 30 300 3 3 31 NULL NULL -3 3 32 32 302 set join_buffer_space_limit=@save_join_buffer_space_limit; set join_buffer_size=@save_join_buffer_size; set join_cache_level=@save_join_cache_level; @@ -6229,6 +6229,33 @@ EXPLAIN } drop table t1,t2,t3; # End of 10.3 tests +# +# MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +# +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +SET SESSION join_buffer_size= X; +Warnings: +Warning X Truncated incorrect join_buffer_size value: 'X' +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i +SET optimizer_switch='optimize_join_buffer_size=off'; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +ERROR HYX: Could not create a join buffer. Please check and adjust the value of the variables 'JOIN_BUFFER_SIZE (X)' and 'JOIN_BUFFER_SPACE_LIMIT (X)' +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i i +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; +# +# End of 10.4 tests +# set @@optimizer_switch=@save_optimizer_switch; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 07ac0b760cf..500fd9e1049 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4201,6 +4201,34 @@ drop table t1,t2,t3; --echo # End of 10.3 tests +--echo # +--echo # MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +--echo # + +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +--replace_regex /[0-9][0-9]+/X/ +SET SESSION join_buffer_size= 5250229460064350213; +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET optimizer_switch='optimize_join_buffer_size=off'; +--replace_regex /[0-9][0-9]+/X/ +--error ER_OUTOFMEMORY +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; + +--echo # +--echo # End of 10.4 tests +--echo # + # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_optimizer.test b/mysql-test/main/join_optimizer.test index 3afe82113b9..e5f6181944d 100644 --- a/mysql-test/main/join_optimizer.test +++ b/mysql-test/main/join_optimizer.test @@ -2,6 +2,8 @@ drop table if exists t0,t1,t2,t3; --enable_warnings +--source include/have_innodb.inc + --echo # --echo # BUG#38049 incorrect rows estimations with references from preceding table --echo # diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index beca5fc782a..6de76334908 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -805,20 +805,18 @@ size_t JOIN_CACHE::get_min_join_buffer_size() the estimated number of records in the partial join DESCRIPTION - At the first its invocation for the cache the function calculates the - maximum possible size of join buffer for the cache. If the parameter - optimize_buff_size true then this value does not exceed the size of the - space needed for the estimated number of records 'max_records' in the - partial join that joins tables from the first one through join_tab. This - value is also capped off by the value of join_tab->join_buffer_size_limit, - if it has been set a to non-zero value, and by the value of the system - parameter join_buffer_size - otherwise. After the calculation of the - interesting size the function saves the value in the field 'max_buff_size' - in order to use it directly at the next invocations of the function. - NOTES - Currently the value of join_tab->join_buffer_size_limit is initialized - to 0 and is never reset. + At the first its invocation for the cache the function calculates + the maximum possible size of join buffer for the cache. If the + parameter optimize_buff_size true then this value does not exceed + the size of the space needed for the estimated number of records + 'max_records' in the partial join that joins tables from the first + one through join_tab. This value is also capped off by the value + of the system parameter join_buffer_size. After the calculation of + the interesting size the function saves the value in the field + 'max_buff_size' in order to use it directly at the next + invocations of the function. + RETURN VALUE The maximum possible size of the join buffer of this cache @@ -842,8 +840,6 @@ size_t JOIN_CACHE::get_max_join_buffer_size(bool optimize_buff_size) space_per_record= len; size_t limit_sz= (size_t)join->thd->variables.join_buff_size; - if (join_tab->join_buffer_size_limit) - set_if_smaller(limit_sz, join_tab->join_buffer_size_limit); if (!optimize_buff_size) max_sz= limit_sz; else @@ -923,13 +919,25 @@ int JOIN_CACHE::alloc_buffer() curr_min_buff_space_sz+= min_buff_size; curr_buff_space_sz+= buff_size; - if (curr_min_buff_space_sz > join_buff_space_limit || - (curr_buff_space_sz > join_buff_space_limit && - (!optimize_buff_size || + if (optimize_buff_size) + { + /* + optimize_join_buffer_size=on used. We should limit the join + buffer space to join_buff_space_limit if possible. + */ + if (curr_min_buff_space_sz > join_buff_space_limit) + { + /* + Increase buffer size to minimum needed, to be able to use the + join buffer. + */ + join_buff_space_limit= curr_min_buff_space_sz; + } + if (curr_buff_space_sz > join_buff_space_limit && join->shrink_join_buffers(join_tab, curr_buff_space_sz, - join_buff_space_limit)))) - goto fail; - + join_buff_space_limit)) + goto fail; // Fatal error + } if (for_explain_only) return 0; @@ -2766,7 +2774,6 @@ bool JOIN_CACHE_BKAH::save_explain_data(EXPLAIN_BKA_TYPE *explain) int JOIN_CACHE_HASHED::init(bool for_explain) { - int rc= 0; TABLE_REF *ref= &join_tab->ref; DBUG_ENTER("JOIN_CACHE_HASHED::init"); @@ -2776,8 +2783,21 @@ int JOIN_CACHE_HASHED::init(bool for_explain) key_length= ref->key_length; - if ((rc= JOIN_CACHE::init(for_explain)) || for_explain) - DBUG_RETURN (rc); + if (JOIN_CACHE::init(for_explain)) + { + THD *thd= join->thd; + const char *errmsg= + "Could not create a join buffer. Please check and " + "adjust the value of the variables 'JOIN_BUFFER_SIZE (%llu)' and " + "'JOIN_BUFFER_SPACE_LIMIT (%llu)'"; + my_printf_error(ER_OUTOFMEMORY, errmsg, MYF(0), + thd->variables.join_buff_size, + thd->variables.join_buff_space_limit); + DBUG_RETURN (1); + } + + if (for_explain) + DBUG_RETURN(0); if (!(key_buff= (uchar*) join->thd->alloc(key_length))) DBUG_RETURN(1); diff --git a/sql/sql_select.h b/sql/sql_select.h index 4bac201c3b4..f2b9d272701 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -395,7 +395,6 @@ typedef struct st_join_table { bool idx_cond_fact_out; bool use_join_cache; uint used_join_cache_level; - ulong join_buffer_size_limit; JOIN_CACHE *cache; /* Index condition for BKA access join -- cgit v1.2.1 From 5fd46be5a7da83e935c59796733d52906b59f14c Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 4 May 2023 12:43:18 +0300 Subject: Fixed calculation of JOIN_CACHE::max_records The old code did set max_records to either number_of_rows (partial_join_cardinality) or memory size (join_buffer_space_limit) which did not make sense. Fixed by setting max_records to number of rows that fits into join_buffer_size. Other things: - Initialize buffer cache values in JOIN_CACHE constructors (safety) Reviewer: Sergei Petrunia --- mysql-test/main/join_cache.result | 2 +- sql/sql_join_cache.cc | 24 +++++++++++++++--------- sql/sql_join_cache.h | 10 ++-------- 3 files changed, 18 insertions(+), 18 deletions(-) diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 3b02740c67c..f5ddbfea733 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -3781,9 +3781,9 @@ id1 num3 text1 id4 id3 dummy 228808822 6 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 18 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 -228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 17 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 50 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 +228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 89 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 19 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 6de76334908..eac03404240 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -634,7 +634,7 @@ void JOIN_CACHE::create_remaining_fields() /* - Calculate and set all cache constants + Calculate and set all cache constants SYNOPSIS set_constants() @@ -829,6 +829,12 @@ size_t JOIN_CACHE::get_max_join_buffer_size(bool optimize_buff_size) size_t max_sz; size_t min_sz= get_min_join_buffer_size(); size_t len= 0; + double max_records, partial_join_cardinality= + (join_tab-1)->get_partial_join_cardinality(); + size_t limit_sz= (size_t) join->thd->variables.join_buff_size; + /* Expected join buffer space used for one record */ + size_t space_per_record; + for (JOIN_TAB *tab= start_tab; tab != join_tab; tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) { @@ -839,13 +845,17 @@ size_t JOIN_CACHE::get_max_join_buffer_size(bool optimize_buff_size) len+= get_max_key_addon_space_per_record() + avg_aux_buffer_incr; space_per_record= len; - size_t limit_sz= (size_t)join->thd->variables.join_buff_size; + /* Note that space_per_record can be 0 if no table fields where used */ + max_records= (double) (limit_sz / MY_MAX(space_per_record, 1)); + set_if_smaller(max_records, partial_join_cardinality); + set_if_bigger(max_records, 10.0); + if (!optimize_buff_size) max_sz= limit_sz; else { - if (limit_sz / max_records > space_per_record) - max_sz= space_per_record * max_records; + if ((size_t) (limit_sz / max_records) > space_per_record) + max_sz= space_per_record * (size_t) max_records; else max_sz= limit_sz; max_sz+= pack_length_with_blob_ptrs; @@ -855,7 +865,7 @@ size_t JOIN_CACHE::get_max_join_buffer_size(bool optimize_buff_size) max_buff_size= max_sz; } return max_buff_size; -} +} /* @@ -895,14 +905,10 @@ int JOIN_CACHE::alloc_buffer() join->thd->variables.join_buff_space_limit; bool optimize_buff_size= optimizer_flag(join->thd, OPTIMIZER_SWITCH_OPTIMIZE_JOIN_BUFFER_SIZE); - double partial_join_cardinality= (join_tab-1)->get_partial_join_cardinality(); buff= NULL; min_buff_size= 0; max_buff_size= 0; min_records= 1; - max_records= (size_t) (partial_join_cardinality <= join_buff_space_limit ? - (ulonglong) partial_join_cardinality : join_buff_space_limit); - set_if_bigger(max_records, 10); min_buff_size= get_min_join_buffer_size(); buff_size= get_max_join_buffer_size(optimize_buff_size); diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h index f75e9fd380f..ad9cea744d7 100644 --- a/sql/sql_join_cache.h +++ b/sql/sql_join_cache.h @@ -248,9 +248,6 @@ protected: /* The expected size of the space per record in the auxiliary buffer */ size_t avg_aux_buffer_incr; - /* Expected join buffer space used for one record */ - size_t space_per_record; - /* Pointer to the beginning of the join buffer */ uchar *buff; /* @@ -272,11 +269,6 @@ protected: the minimal size equal to min_buff_size */ size_t min_records; - /* - The maximum expected number of records to be put in the join buffer - at one refill - */ - size_t max_records; /* Pointer to the current position in the join buffer. @@ -542,6 +534,7 @@ protected: join_tab= tab; prev_cache= next_cache= 0; buff= 0; + min_buff_size= max_buff_size= 0; // Caches } /* @@ -557,6 +550,7 @@ protected: next_cache= 0; prev_cache= prev; buff= 0; + min_buff_size= max_buff_size= 0; // Caches if (prev) prev->next_cache= this; } -- cgit v1.2.1 From e74390d94f81d0bc6d0b2b8999f7ee44fd2092e9 Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 4 May 2023 13:06:39 +0300 Subject: Cleanup of sql_join_cache code (no logic changes) - Remove virtual from get_min_join_buffer_size() and get_max_join_buffer_size(). - Avoid some calls to get_min_buffer_size() - Simply cache usage in get_..._join_buffer_size() - Simplify get_max_join_buffer_size() when using optimize_buff_size - Reindented some long comments Reviewer: Sergei Petrunia --- sql/sql_join_cache.cc | 160 +++++++++++++++++++++++++------------------------- sql/sql_join_cache.h | 5 +- 2 files changed, 84 insertions(+), 81 deletions(-) diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index eac03404240..7d61ce31cca 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -694,16 +694,22 @@ void JOIN_CACHE::set_constants() (prev_cache ? prev_cache->get_size_of_rec_offset() : 0) + length + fields*sizeof(uint); pack_length_with_blob_ptrs= pack_length + blobs*sizeof(uchar *); - min_buff_size= 0; min_records= 1; + min_buff_size= get_min_join_buffer_size(); buff_size= (size_t)MY_MAX(join->thd->variables.join_buff_size, - get_min_join_buffer_size()); + min_buff_size); size_of_rec_ofs= offset_size(buff_size); size_of_rec_len= blobs ? size_of_rec_ofs : offset_size(len); size_of_fld_ofs= size_of_rec_len; base_prefix_length= (with_length ? size_of_rec_len : 0) + (prev_cache ? prev_cache->get_size_of_rec_offset() : 0); - /* + /* + Call ge_min_join_buffer_size() again as the size may have got smaller + if size_of_rec_ofs or some other variable changed since last call. + */ + min_buff_size= 0; + min_buff_size= get_min_join_buffer_size(); + /* The size of the offsets for referenced fields will be added later. The values of 'pack_length' and 'pack_length_with_blob_ptrs' are adjusted every time when the first reference to the referenced field is registered. @@ -767,30 +773,29 @@ uint JOIN_CACHE::get_record_max_affix_length() size_t JOIN_CACHE::get_min_join_buffer_size() { - if (!min_buff_size) + if (min_buff_size) + return min_buff_size; // use cached value + + size_t len= 0, len_last= 0, len_addon, min_sz, add_sz= 0; + + for (JOIN_TAB *tab= start_tab; tab != join_tab; + tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) { - size_t len= 0; - size_t len_last= 0; - for (JOIN_TAB *tab= start_tab; tab != join_tab; - tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) - { - len+= tab->get_max_used_fieldlength(); - len_last+= tab->get_used_fieldlength(); - } - size_t len_addon= get_record_max_affix_length() + - get_max_key_addon_space_per_record(); - len+= len_addon; - len_last+= len_addon; - size_t min_sz= len*(min_records-1) + len_last; - min_sz+= pack_length_with_blob_ptrs; - size_t add_sz= 0; - for (uint i=0; i < min_records; i++) - add_sz+= join_tab_scan->aux_buffer_incr(i+1); - avg_aux_buffer_incr= add_sz/min_records; - min_sz+= add_sz; - set_if_bigger(min_sz, 1); - min_buff_size= min_sz; + len+= tab->get_max_used_fieldlength(); + len_last+= tab->get_used_fieldlength(); } + len_addon= (get_record_max_affix_length() + + get_max_key_addon_space_per_record()); + len+= len_addon; + len_last+= len_addon; + min_sz= len*(min_records-1) + len_last; + min_sz+= pack_length_with_blob_ptrs; + for (uint i=0; i < min_records; i++) + add_sz+= join_tab_scan->aux_buffer_incr(i+1); + avg_aux_buffer_incr= add_sz/min_records; + min_sz+= add_sz; + set_if_bigger(min_sz, 1); + min_buff_size= min_sz; return min_buff_size; } @@ -822,48 +827,48 @@ size_t JOIN_CACHE::get_min_join_buffer_size() The maximum possible size of the join buffer of this cache */ -size_t JOIN_CACHE::get_max_join_buffer_size(bool optimize_buff_size) +size_t JOIN_CACHE::get_max_join_buffer_size(bool optimize_buff_size, + size_t min_sz) { - if (!max_buff_size) + if (max_buff_size) + return max_buff_size; // use cached value + + size_t limit_sz= (size_t) join->thd->variables.join_buff_size; + + if (!optimize_buff_size) + return max_buff_size= limit_sz; + + size_t max_sz; + size_t len= 0; + double max_records, partial_join_cardinality= + (join_tab-1)->get_partial_join_cardinality(); + /* Expected join buffer space used for one record */ + size_t space_per_record; + + for (JOIN_TAB *tab= start_tab; tab != join_tab; + tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) { - size_t max_sz; - size_t min_sz= get_min_join_buffer_size(); - size_t len= 0; - double max_records, partial_join_cardinality= - (join_tab-1)->get_partial_join_cardinality(); - size_t limit_sz= (size_t) join->thd->variables.join_buff_size; - /* Expected join buffer space used for one record */ - size_t space_per_record; - - for (JOIN_TAB *tab= start_tab; tab != join_tab; - tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) - { - len+= tab->get_used_fieldlength(); - } - len+= get_record_max_affix_length(); - avg_record_length= len; - len+= get_max_key_addon_space_per_record() + avg_aux_buffer_incr; - space_per_record= len; + len+= tab->get_used_fieldlength(); + } + len+= get_record_max_affix_length(); + avg_record_length= len; + len+= get_max_key_addon_space_per_record() + avg_aux_buffer_incr; + space_per_record= len; - /* Note that space_per_record can be 0 if no table fields where used */ - max_records= (double) (limit_sz / MY_MAX(space_per_record, 1)); - set_if_smaller(max_records, partial_join_cardinality); - set_if_bigger(max_records, 10.0); + /* Note that space_per_record can be 0 if no table fields where used */ + max_records= (double) (limit_sz / MY_MAX(space_per_record, 1)); + set_if_smaller(max_records, partial_join_cardinality); + set_if_bigger(max_records, 10.0); - if (!optimize_buff_size) - max_sz= limit_sz; - else - { - if ((size_t) (limit_sz / max_records) > space_per_record) - max_sz= space_per_record * (size_t) max_records; - else - max_sz= limit_sz; - max_sz+= pack_length_with_blob_ptrs; - set_if_smaller(max_sz, limit_sz); - } - set_if_bigger(max_sz, min_sz); - max_buff_size= max_sz; - } + if ((size_t) (limit_sz / max_records) > space_per_record) + max_sz= space_per_record * (size_t) max_records; + else + max_sz= limit_sz; + max_sz+= pack_length_with_blob_ptrs; + set_if_smaller(max_sz, limit_sz); + + set_if_bigger(max_sz, min_sz); + max_buff_size= max_sz; return max_buff_size; } @@ -906,11 +911,7 @@ int JOIN_CACHE::alloc_buffer() bool optimize_buff_size= optimizer_flag(join->thd, OPTIMIZER_SWITCH_OPTIMIZE_JOIN_BUFFER_SIZE); buff= NULL; - min_buff_size= 0; - max_buff_size= 0; - min_records= 1; - min_buff_size= get_min_join_buffer_size(); - buff_size= get_max_join_buffer_size(optimize_buff_size); + buff_size= get_max_join_buffer_size(optimize_buff_size, min_buff_size); for (tab= start_tab; tab!= join_tab; tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) @@ -1093,18 +1094,19 @@ int JOIN_CACHE::init(bool for_explain) /* - Check the possibility to read the access keys directly from the join buffer + Check the possibility to read the access keys directly from the join buffer SYNOPSIS check_emb_key_usage() DESCRIPTION - The function checks some conditions at which the key values can be read - directly from the join buffer. This is possible when the key values can be - composed by concatenation of the record fields stored in the join buffer. - Sometimes when the access key is multi-component the function has to re-order - the fields written into the join buffer to make keys embedded. If key - values for the key access are detected as embedded then 'use_emb_key' - is set to TRUE. + The function checks some conditions at which the key values can be + read directly from the join buffer. This is possible when the key + values can be composed by concatenation of the record fields + stored in the join buffer. Sometimes when the access key is + multi-component the function has to re-order the fields written + into the join buffer to make keys embedded. If key values for the + key access are detected as embedded then 'use_emb_key' is set to + TRUE. EXAMPLE Let table t2 has an index defined on the columns a,b . Let's assume also @@ -1257,7 +1259,7 @@ bool JOIN_CACHE::check_emb_key_usage() trailing spaces - significant part of fixed length fields that can have trailing spaces with the prepanded length - - data of non-blob variable length fields with the prepanded data length + - data of non-blob variable length fields with the prepanded data length - blob data from blob fields with the prepanded data length (5) record offset values for the data fields that are referred to from other caches @@ -1328,7 +1330,7 @@ uint JOIN_CACHE::write_record_data(uchar * link, bool *is_full) Check whether we won't be able to add any new record into the cache after this one because the cache will be full. Set last_record to TRUE if it's so. The assume that the cache will be full after the record has been written - into it if either the remaining space of the cache is not big enough for the + into it if either the remaining space of the cache is not big enough for the record's blob values or if there is a chance that not all non-blob fields of the next record can be placed there. This function is called only in the case when there is enough space left in @@ -1350,7 +1352,7 @@ uint JOIN_CACHE::write_record_data(uchar * link, bool *is_full) /* Put a reference to the fields of the record that are stored in the previous - cache if there is any. This reference is passed by the 'link' parameter. + cache if there is any. This reference is passed by the 'link' parameter. */ if (prev_cache) { diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h index ad9cea744d7..8bdce1bd592 100644 --- a/sql/sql_join_cache.h +++ b/sql/sql_join_cache.h @@ -602,9 +602,10 @@ public: void set_join_buffer_size(size_t sz) { buff_size= sz; } /* Get the minimum possible size of the cache join buffer */ - virtual size_t get_min_join_buffer_size(); + size_t get_min_join_buffer_size(); /* Get the maximum possible size of the cache join buffer */ - virtual size_t get_max_join_buffer_size(bool optimize_buff_size); + size_t get_max_join_buffer_size(bool optimize_buff_size, + size_t min_buffer_size_arg); /* Shrink the size if the cache join buffer in a given ratio */ bool shrink_join_buffer_in_ratio(ulonglong n, ulonglong d); -- cgit v1.2.1 From c874d5c68d24fb78bb6d59db1927ea5794891e38 Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 4 May 2023 19:13:30 +0300 Subject: Added missing test file --- mysql-test/suite/rpl/r/rpl_loaddata_local.result | 134 +++++++++++++++++++++++ mysql-test/suite/rpl/r/rpl_loaddatalocal.result | 134 ----------------------- 2 files changed, 134 insertions(+), 134 deletions(-) create mode 100644 mysql-test/suite/rpl/r/rpl_loaddata_local.result delete mode 100644 mysql-test/suite/rpl/r/rpl_loaddatalocal.result diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_local.result b/mysql-test/suite/rpl/r/rpl_loaddata_local.result new file mode 100644 index 00000000000..f0d24df2cb2 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_loaddata_local.result @@ -0,0 +1,134 @@ +include/master-slave.inc +[connection master] +create table t1(a int); +select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; +truncate table t1; +load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; +connection slave; +select a,count(*) from t1 group by a; +a count(*) +1 10000 +connection master; +drop table t1; +connection slave; +connection master; +create table t1(a int); +insert into t1 values (1), (2), (2), (3); +select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; +drop table t1; +create table t1(a int primary key); +load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; +Warnings: +Warning 1062 Duplicate entry '2' for key 'PRIMARY' +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +connection slave; +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +connection master; +drop table t1; +connection slave; +==== Bug22504 Initialize ==== +connection master; +SET sql_mode='ignore_space'; +CREATE TABLE t1(a int); +insert into t1 values (1), (2), (3), (4); +select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; +truncate table t1; +load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +4 +connection slave; +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +4 +==== Clean up ==== +connection master; +DROP TABLE t1; +connection slave; + +Bug #43746: +"return wrong query string when parse 'load data infile' sql statement" + +connection master; +SELECT @@SESSION.sql_mode INTO @old_mode; +SET sql_mode='ignore_space'; +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1), (2), (3), (4); +SELECT * INTO OUTFILE 'MYSQLD_DATADIR/bug43746.sql' FROM t1; +TRUNCATE TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +LOAD/* look mum, with comments in weird places! */DATA/* oh hai */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/* we are */INTO/* from the internets */TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1; +LOAD DATA /*!10000 LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1; +LOAD/*!999999 special comments that do not expand */DATA/*!999999 code from the future */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!999999 have flux capacitor */INTO/*!999999 will travel */TABLE t1; +SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER'; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +connection slave; + +Bug #59267: +"LOAD DATA LOCAL INFILE not executed on slave with SBR" + +connection master; +SELECT * INTO OUTFILE 'MYSQLD_DATADIR/bug59267.sql' FROM t1; +TRUNCATE TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug59267.sql' INTO TABLE t1; +SELECT 'Master', COUNT(*) FROM t1; +Master COUNT(*) +Master 44 +connection slave; +SELECT 'Slave', COUNT(*) FROM t1; +Slave COUNT(*) +Slave 44 +connection master; +DROP TABLE t1; +SET SESSION sql_mode=@old_mode; +connection slave; +connection master; + +Bug #60580/#11902767: +"statement improperly replicated crashes slave sql thread" + +connection master; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE TABLE t2(f1 INT, f2 TIMESTAMP); +INSERT INTO t2 VALUES(1, '2011-03-22 21:01:28'); +INSERT INTO t2 VALUES(2, '2011-03-21 21:01:28'); +INSERT INTO t2 VALUES(3, '2011-03-20 21:01:28'); +CREATE TABLE t3 AS SELECT * FROM t2; +CREATE VIEW v1 AS SELECT * FROM t2 +WHERE f1 IN (SELECT f1 FROM t3 WHERE (t3.f2 IS NULL)); +SELECT 1 INTO OUTFILE 'MYSQLD_DATADIR/bug60580.csv' FROM DUAL; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug60580.csv' INTO TABLE t1 (@f1) SET f2 = (SELECT f1 FROM v1 WHERE f1=@f1); +SELECT * FROM t1; +f1 f2 +NULL NULL +connection slave; +SELECT * FROM t1; +f1 f2 +NULL NULL +connection master; +DROP VIEW v1; +DROP TABLE t1, t2, t3; +connection slave; +connection master; +include/rpl_end.inc +# End of 5.1 tests diff --git a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result deleted file mode 100644 index f0d24df2cb2..00000000000 --- a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result +++ /dev/null @@ -1,134 +0,0 @@ -include/master-slave.inc -[connection master] -create table t1(a int); -select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; -truncate table t1; -load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; -connection slave; -select a,count(*) from t1 group by a; -a count(*) -1 10000 -connection master; -drop table t1; -connection slave; -connection master; -create table t1(a int); -insert into t1 values (1), (2), (2), (3); -select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; -drop table t1; -create table t1(a int primary key); -load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; -Warnings: -Warning 1062 Duplicate entry '2' for key 'PRIMARY' -SELECT * FROM t1 ORDER BY a; -a -1 -2 -3 -connection slave; -SELECT * FROM t1 ORDER BY a; -a -1 -2 -3 -connection master; -drop table t1; -connection slave; -==== Bug22504 Initialize ==== -connection master; -SET sql_mode='ignore_space'; -CREATE TABLE t1(a int); -insert into t1 values (1), (2), (3), (4); -select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; -truncate table t1; -load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; -SELECT * FROM t1 ORDER BY a; -a -1 -2 -3 -4 -connection slave; -SELECT * FROM t1 ORDER BY a; -a -1 -2 -3 -4 -==== Clean up ==== -connection master; -DROP TABLE t1; -connection slave; - -Bug #43746: -"return wrong query string when parse 'load data infile' sql statement" - -connection master; -SELECT @@SESSION.sql_mode INTO @old_mode; -SET sql_mode='ignore_space'; -CREATE TABLE t1(a int); -INSERT INTO t1 VALUES (1), (2), (3), (4); -SELECT * INTO OUTFILE 'MYSQLD_DATADIR/bug43746.sql' FROM t1; -TRUNCATE TABLE t1; -LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; -LOAD/* look mum, with comments in weird places! */DATA/* oh hai */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/* we are */INTO/* from the internets */TABLE t1; -LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; -LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1; -LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1; -LOAD DATA /*!10000 LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1; -LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1; -LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1; -LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1; -LOAD/*!999999 special comments that do not expand */DATA/*!999999 code from the future */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!999999 have flux capacitor */INTO/*!999999 will travel */TABLE t1; -SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER'; -LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; -connection slave; - -Bug #59267: -"LOAD DATA LOCAL INFILE not executed on slave with SBR" - -connection master; -SELECT * INTO OUTFILE 'MYSQLD_DATADIR/bug59267.sql' FROM t1; -TRUNCATE TABLE t1; -LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug59267.sql' INTO TABLE t1; -SELECT 'Master', COUNT(*) FROM t1; -Master COUNT(*) -Master 44 -connection slave; -SELECT 'Slave', COUNT(*) FROM t1; -Slave COUNT(*) -Slave 44 -connection master; -DROP TABLE t1; -SET SESSION sql_mode=@old_mode; -connection slave; -connection master; - -Bug #60580/#11902767: -"statement improperly replicated crashes slave sql thread" - -connection master; -CREATE TABLE t1(f1 INT, f2 INT); -CREATE TABLE t2(f1 INT, f2 TIMESTAMP); -INSERT INTO t2 VALUES(1, '2011-03-22 21:01:28'); -INSERT INTO t2 VALUES(2, '2011-03-21 21:01:28'); -INSERT INTO t2 VALUES(3, '2011-03-20 21:01:28'); -CREATE TABLE t3 AS SELECT * FROM t2; -CREATE VIEW v1 AS SELECT * FROM t2 -WHERE f1 IN (SELECT f1 FROM t3 WHERE (t3.f2 IS NULL)); -SELECT 1 INTO OUTFILE 'MYSQLD_DATADIR/bug60580.csv' FROM DUAL; -LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug60580.csv' INTO TABLE t1 (@f1) SET f2 = (SELECT f1 FROM v1 WHERE f1=@f1); -SELECT * FROM t1; -f1 f2 -NULL NULL -connection slave; -SELECT * FROM t1; -f1 f2 -NULL NULL -connection master; -DROP VIEW v1; -DROP TABLE t1, t2, t3; -connection slave; -connection master; -include/rpl_end.inc -# End of 5.1 tests -- cgit v1.2.1 From 84b9fc25a29b94a37eb9d5ac2e2c0f75c0efafda Mon Sep 17 00:00:00 2001 From: Monty Date: Fri, 5 May 2023 11:31:35 +0300 Subject: Fixed wrong test cases (embedded and ASAN) - main.selectivity failed because one test produced different result with embedded (missing feature). Fixed by moving the failing part to selectivity_notembedded. - Disabled maria.encrypt-no-key for embedded as embedded does not support encryption - Moved test from join_cache to join_cache_notasan that tried to alloc() a buffer bigger than available memory. --- mysql-test/main/join_cache.result | 27 ------- mysql-test/main/join_cache.test | 28 ------- mysql-test/main/join_cache_notasan.result | 27 +++++++ mysql-test/main/join_cache_notasan.test | 35 +++++++++ mysql-test/main/selectivity.result | 69 ----------------- mysql-test/main/selectivity.test | 79 ------------------- mysql-test/main/selectivity_innodb.result | 67 ---------------- mysql-test/main/selectivity_notembedded.result | 83 ++++++++++++++++++++ mysql-test/main/selectivity_notembedded.test | 102 +++++++++++++++++++++++++ mysql-test/suite/maria/encrypt-no-key.test | 1 + 10 files changed, 248 insertions(+), 270 deletions(-) create mode 100644 mysql-test/main/join_cache_notasan.result create mode 100644 mysql-test/main/join_cache_notasan.test create mode 100644 mysql-test/main/selectivity_notembedded.result create mode 100644 mysql-test/main/selectivity_notembedded.test diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index f5ddbfea733..6b39f936628 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6229,33 +6229,6 @@ EXPLAIN } drop table t1,t2,t3; # End of 10.3 tests -# -# MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size -# -CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t1 VALUES (1332945389); -CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t2 VALUES (1180244875), (1951338178); -SET SESSION join_buffer_size= X; -Warnings: -Warning X Truncated incorrect join_buffer_size value: 'X' -SET SESSION join_cache_level = 4; -SET optimizer_switch='optimize_join_buffer_size=on'; -SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -i -SET optimizer_switch='optimize_join_buffer_size=off'; -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -ERROR HYX: Could not create a join buffer. Please check and adjust the value of the variables 'JOIN_BUFFER_SIZE (X)' and 'JOIN_BUFFER_SPACE_LIMIT (X)' -SET SESSION join_buffer_size= 10000000; -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -i i -SET SESSION optimizer_switch= default; -SET SESSION join_buffer_size= default; -SET SESSION join_cache_level= default; -drop table t1,t2; -# -# End of 10.4 tests -# set @@optimizer_switch=@save_optimizer_switch; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 500fd9e1049..07ac0b760cf 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4201,34 +4201,6 @@ drop table t1,t2,t3; --echo # End of 10.3 tests ---echo # ---echo # MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size ---echo # - -CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t1 VALUES (1332945389); -CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t2 VALUES (1180244875), (1951338178); ---replace_regex /[0-9][0-9]+/X/ -SET SESSION join_buffer_size= 5250229460064350213; -SET SESSION join_cache_level = 4; -SET optimizer_switch='optimize_join_buffer_size=on'; -SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -SET optimizer_switch='optimize_join_buffer_size=off'; ---replace_regex /[0-9][0-9]+/X/ ---error ER_OUTOFMEMORY -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -SET SESSION join_buffer_size= 10000000; -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -SET SESSION optimizer_switch= default; -SET SESSION join_buffer_size= default; -SET SESSION join_cache_level= default; -drop table t1,t2; - ---echo # ---echo # End of 10.4 tests ---echo # - # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_cache_notasan.result b/mysql-test/main/join_cache_notasan.result new file mode 100644 index 00000000000..3cec949f5c6 --- /dev/null +++ b/mysql-test/main/join_cache_notasan.result @@ -0,0 +1,27 @@ +# +# MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +# +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +SET SESSION join_buffer_size= X; +Warnings: +Warning X Truncated incorrect join_buffer_size value: 'X' +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i +SET optimizer_switch='optimize_join_buffer_size=off'; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +ERROR HYX: Could not create a join buffer. Please check and adjust the value of the variables 'JOIN_BUFFER_SIZE (X)' and 'JOIN_BUFFER_SPACE_LIMIT (X)' +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i i +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/join_cache_notasan.test b/mysql-test/main/join_cache_notasan.test new file mode 100644 index 00000000000..7fd5e4e80b1 --- /dev/null +++ b/mysql-test/main/join_cache_notasan.test @@ -0,0 +1,35 @@ +# +# Tests that should be in join_cache but cannot be run with ASAN + +--source include/not_asan.inc +--source include/have_innodb.inc + +--echo # +--echo # MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +--echo # + +# This test tries to allocate a too big bufffer, for which ASAN gives an error + +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +--replace_regex /[0-9][0-9]+/X/ +SET SESSION join_buffer_size= 5250229460064350213; +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET optimizer_switch='optimize_join_buffer_size=off'; +--replace_regex /[0-9][0-9]+/X/ +--error ER_OUTOFMEMORY +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 7d7343847cd..a6866e55b13 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1937,75 +1937,6 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 DROP TABLE t1; # End of 10.2 tests -# -# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram -# -create table t0(a int); -insert into t0 select 1 from seq_1_to_78; -create table t1(a int); -insert into t1 select 1 from seq_1_to_26; -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; -insert into t10 select 34785 from t0, seq_1_to_8; -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; -insert into t10 select 65216 from t0, seq_1_to_52; -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; -insert into t10 select 65219 from t0; -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; -insert into t10 select 73915 from t0, seq_1_to_40; -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; -insert into t10 select 91302 from t0, seq_1_to_6; -insert into t10 select 91303 from t1; -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; -insert into t10 select 91305 from t0, seq_1_to_8; -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; -set use_stat_tables=preferably; -analyze table t10 persistent for all; -Table Op Msg_type Msg_text -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK -flush tables; -set @tmp=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t10 where a in (91303); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where -# Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -[ - [ - { - "column_name": "a", - "ranges": - ["91303 <= a <= 91303"], - "selectivity_from_histogram": 0.0357 - } - ] -] -set optimizer_trace=@tmp; -drop table t0,t1,t10; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index 06a3e32da95..def74394ec5 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1324,85 +1324,6 @@ DROP TABLE t1; --echo # End of 10.2 tests ---echo # ---echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram ---echo # -create table t0(a int); # This holds how many rows we hold in a bucket. -insert into t0 select 1 from seq_1_to_78; - -create table t1(a int); # one-third of a bucket -insert into t1 select 1 from seq_1_to_26; - -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; - -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; - - -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; - - -insert into t10 select 34785 from t0, seq_1_to_8; - -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; - -insert into t10 select 65216 from t0, seq_1_to_52; - -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; - -insert into t10 select 65219 from t0; - - -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; - -insert into t10 select 73915 from t0, seq_1_to_40; - - -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; - -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; - -insert into t10 select 91302 from t0, seq_1_to_6; - -insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; - -insert into t10 select 91305 from t0, seq_1_to_8; - -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; - -set use_stat_tables=preferably; -analyze table t10 persistent for all; -flush tables; - -set @tmp=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t10 where a in (91303); - ---echo # Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; - -set optimizer_trace=@tmp; -drop table t0,t1,t10; - set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index dfba12f2b05..13816f8185c 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1947,73 +1947,6 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 DROP TABLE t1; # End of 10.2 tests -# -# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram -# -create table t0(a int); -insert into t0 select 1 from seq_1_to_78; -create table t1(a int); -insert into t1 select 1 from seq_1_to_26; -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; -insert into t10 select 34785 from t0, seq_1_to_8; -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; -insert into t10 select 65216 from t0, seq_1_to_52; -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; -insert into t10 select 65219 from t0; -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; -insert into t10 select 73915 from t0, seq_1_to_40; -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; -insert into t10 select 91302 from t0, seq_1_to_6; -insert into t10 select 91303 from t1; -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; -insert into t10 select 91305 from t0, seq_1_to_8; -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; -set use_stat_tables=preferably; -analyze table t10 persistent for all; -Table Op Msg_type Msg_text -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK -flush tables; -set statement optimizer_trace=1 for -explain select * from t10 where a in (91303); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where -# Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -[ - [ - { - "column_name": "a", - "ranges": - ["91303 <= a <= 91303"], - "selectivity_from_histogram": 0.035714283 - } - ] -] -drop table t0,t1,t10; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity_notembedded.result b/mysql-test/main/selectivity_notembedded.result new file mode 100644 index 00000000000..8b298a95801 --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.result @@ -0,0 +1,83 @@ +# +# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +# +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set @save_use_stat_tables=@@use_stat_tables; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +SET histogram_size= 255; +set histogram_type='DOUBLE_PREC_HB'; +set use_stat_tables=preferably; +SET optimizer_use_condition_selectivity=3; +create table t0(a int); +insert into t0 select 1 from seq_1_to_78; +create table t1(a int); +insert into t1 select 1 from seq_1_to_26; +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; +insert into t10 select 34785 from t0, seq_1_to_8; +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; +insert into t10 select 65216 from t0, seq_1_to_52; +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; +insert into t10 select 65219 from t0; +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; +insert into t10 select 73915 from t0, seq_1_to_40; +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; +insert into t10 select 91302 from t0, seq_1_to_6; +insert into t10 select 91303 from t1; +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; +insert into t10 select 91305 from t0, seq_1_to_8; +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status OK +flush tables; +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where +# Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.selectivity_for_columns')) +[ + [ + { + "column_name": "a", + "ranges": + ["91303 <= a <= 91303"], + "selectivity_from_histogram": 0.0357 + } + ] +] +drop table t0,t1,t10; +set optimizer_trace=@tmp; +set @@histogram_size=@save_histogram_size; +set @histogram_type=@save_histogram_type; +set @use_stat_tables=@save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/selectivity_notembedded.test b/mysql-test/main/selectivity_notembedded.test new file mode 100644 index 00000000000..f79c370186f --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.test @@ -0,0 +1,102 @@ +--source include/have_stat_tables.inc +--source include/have_sequence.inc +--source include/not_embedded.inc + +--echo # +--echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +--echo # + +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set @save_use_stat_tables=@@use_stat_tables; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; + +SET histogram_size= 255; +set histogram_type='DOUBLE_PREC_HB'; +set use_stat_tables=preferably; +SET optimizer_use_condition_selectivity=3; + +create table t0(a int); # This holds how many rows we hold in a bucket. +insert into t0 select 1 from seq_1_to_78; + +create table t1(a int); # one-third of a bucket +insert into t1 select 1 from seq_1_to_26; + +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; + +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; + + +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; + + +insert into t10 select 34785 from t0, seq_1_to_8; + +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; + +insert into t10 select 65216 from t0, seq_1_to_52; + +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; + +insert into t10 select 65219 from t0; + + +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; + +insert into t10 select 73915 from t0, seq_1_to_40; + + +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; + +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; + +insert into t10 select 91302 from t0, seq_1_to_6; + +insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; + +insert into t10 select 91305 from t0, seq_1_to_8; + +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; + +analyze table t10 persistent for all; +flush tables; + +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); + +--echo # Must have selectivity_from_histogram <= 1.0: +--replace_result 0.035714283 0.0357 +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) +from information_schema.optimizer_trace; + +drop table t0,t1,t10; + +set optimizer_trace=@tmp; +set @@histogram_size=@save_histogram_size; +set @histogram_type=@save_histogram_type; +set @use_stat_tables=@save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/maria/encrypt-no-key.test b/mysql-test/suite/maria/encrypt-no-key.test index b3f2f4b4bf3..89c50d79a54 100644 --- a/mysql-test/suite/maria/encrypt-no-key.test +++ b/mysql-test/suite/maria/encrypt-no-key.test @@ -1,3 +1,4 @@ +--source include/not_embedded.inc # # MDEV-18496 Crash when Aria encryption is enabled but plugin not available # -- cgit v1.2.1 From a09f661f4392fc9574a5239972243eebc65fe7f2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 8 May 2023 11:42:24 -0700 Subject: MDEV-31181 Crash with EXPLAIN EXTENDED for single-table DELETE using IN predicand This bug affected EXPLAIN EXTENDED command for single-table DELETE that used an IN subquery in its WHERE clause. A crash happened if the optimizer chose to employ index_subquery or unique_subquery access when processing such command. The crash happened when the command tried to print the transformed query. In the current code of 10.4 for single-table DELETE statements the output of any explain command is produced after the join structures of all used subqueries have been destroyed. JOIN::destroy() sets the field tab of the JOIN_TAB structures created for subquery tables to NULL. As a result subselect_indexsubquery_engine::print(), subselect_indexsubquery_engine() cannot use this field to get the alias name of the joined table. This patch suggests to use the field TABLE_LIST::TAB that can be accessed from JOIN_TAB::tab_list to get the alias name of the joined table. Approved by Oleksandr Byelkin --- mysql-test/main/explain_non_select.result | 32 +++++++++++++++++++++++++++++++ mysql-test/main/explain_non_select.test | 28 +++++++++++++++++++++++++++ sql/item_subselect.cc | 18 ++++++++++------- sql/opt_subselect.cc | 1 + 4 files changed, 72 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result index d60f10f85c8..d7ed3992572 100644 --- a/mysql-test/main/explain_non_select.result +++ b/mysql-test/main/explain_non_select.result @@ -277,3 +277,35 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 drop table t1,t2; +# +# MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (4); +create table t2 (pk int primary key); +insert into t2 values (3), (5), (1); +create table t3 (a int, key(a)); +insert into t3 values (7), (5), (7), (3); +explain extended delete from t1 where a in (select pk from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +Warnings: +Note 1003 /* select#1 */ delete from `test`.`t1` where (`test`.`t1`.`a`,(((`test`.`t1`.`a`) in t2 on PRIMARY))) +delete from t1 where a in (select pk from t2); +select * from t1; +a +7 +4 +explain extended delete from t1 where a in (select a from t3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t3 index_subquery a a 5 func 2 100.00 Using index +Warnings: +Note 1003 /* select#1 */ delete from `test`.`t1` where (`test`.`t1`.`a`,(((`test`.`t1`.`a`) in t3 on a))) +delete from t1 where a in (select a from t3); +select * from t1; +a +4 +drop table t1,t2,t3; +# End of 10.4 tests diff --git a/mysql-test/main/explain_non_select.test b/mysql-test/main/explain_non_select.test index d9ff0fb7245..c0c543ad273 100644 --- a/mysql-test/main/explain_non_select.test +++ b/mysql-test/main/explain_non_select.test @@ -250,3 +250,31 @@ PREPARE stmt FROM 'EXPLAIN INSERT INTO t1 SELECT * FROM t2'; EXECUTE stmt; drop table t1,t2; +--echo # +--echo # MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (4); +create table t2 (pk int primary key); +insert into t2 values (3), (5), (1); +create table t3 (a int, key(a)); +insert into t3 values (7), (5), (7), (3); + +let $q1= +delete from t1 where a in (select pk from t2); + +eval explain extended $q1; +eval $q1; +select * from t1; + +let $q2= +delete from t1 where a in (select a from t3); + +eval explain extended $q2; +eval $q2; +select * from t1; + +drop table t1,t2,t3; + +--echo # End of 10.4 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index f88e1e7e101..ae0ab27ec31 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -4536,10 +4536,11 @@ void subselect_union_engine::print(String *str, enum_query_type query_type) void subselect_uniquesubquery_engine::print(String *str, enum_query_type query_type) { + TABLE *table= tab->tab_list ? tab->tab_list->table : tab->table; str->append(STRING_WITH_LEN("(")); tab->ref.items[0]->print(str, query_type); str->append(STRING_WITH_LEN(" in ")); - if (tab->table->s->table_category == TABLE_CATEGORY_TEMPORARY) + if (table->s->table_category == TABLE_CATEGORY_TEMPORARY) { /* Temporary tables' names change across runs, so they can't be used for @@ -4548,8 +4549,8 @@ void subselect_uniquesubquery_engine::print(String *str, str->append(STRING_WITH_LEN("")); } else - str->append(&tab->table->s->table_name); - KEY *key_info= tab->table->key_info+ tab->ref.key; + str->append(&table->s->table_name); + KEY *key_info= table->key_info+ tab->ref.key; str->append(STRING_WITH_LEN(" on ")); str->append(&key_info->name); if (cond) @@ -4567,12 +4568,13 @@ all other tests pass. void subselect_uniquesubquery_engine::print(String *str) { - KEY *key_info= tab->table->key_info + tab->ref.key; + TABLE *table= tab->tab_list ? tab->tab_list->table : tab->table; + KEY *key_info= table->key_info + tab->ref.key; str->append(STRING_WITH_LEN("(")); for (uint i= 0; i < key_info->user_defined_key_parts; i++) tab->ref.items[i]->print(str); str->append(STRING_WITH_LEN(" in ")); - str->append(&tab->table->s->table_name); + str->append(&table->s->table_name); str->append(STRING_WITH_LEN(" on ")); str->append(&key_info->name); if (cond) @@ -4587,11 +4589,12 @@ void subselect_uniquesubquery_engine::print(String *str) void subselect_indexsubquery_engine::print(String *str, enum_query_type query_type) { + TABLE *table= tab->tab_list ? tab->tab_list->table : tab->table; str->append(STRING_WITH_LEN("(")); tab->ref.items[0]->print(str, query_type); str->append(STRING_WITH_LEN(" in ")); - str->append(tab->table->s->table_name.str, tab->table->s->table_name.length); - KEY *key_info= tab->table->key_info+ tab->ref.key; + str->append(&table->s->table_name); + KEY *key_info= table->key_info+ tab->ref.key; str->append(STRING_WITH_LEN(" on ")); str->append(&key_info->name); if (check_null) @@ -5271,6 +5274,7 @@ subselect_hash_sj_engine::make_unique_engine() DBUG_RETURN(NULL); tab->table= tmp_table; + tab->tab_list= 0; tab->preread_init_done= FALSE; tab->ref.tmp_table_index_lookup_init(thd, tmp_key, it, FALSE); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 1c03b0bb4a3..db7a4c0fc14 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -4128,6 +4128,7 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab) sjm->materialized= FALSE; sjm_tab->table= sjm->table; + sjm_tab->tab_list= emb_sj_nest; sjm->table->pos_in_table_list= emb_sj_nest; DBUG_RETURN(FALSE); -- cgit v1.2.1 From 6544d88ff5a7b2ec66153933a3a0531b9091c357 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 9 May 2023 21:20:10 -0700 Subject: MDEV-31224 Crash with EXPLAIN EXTENDED for multi-table update of system table EXPLAIN EXTENDED should always print the field item used in the left part of an equality expression from the SET clause of an update statement as a reference to table column. Approved by Oleksandr Byelkin --- mysql-test/main/explain_non_select.result | 18 ++++++++++++++++++ mysql-test/main/explain_non_select.test | 19 +++++++++++++++++++ mysql-test/main/myisam_explain_non_select_all.result | 4 ++-- sql/sql_select.cc | 2 +- 4 files changed, 40 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result index d7ed3992572..009a568e2c2 100644 --- a/mysql-test/main/explain_non_select.result +++ b/mysql-test/main/explain_non_select.result @@ -308,4 +308,22 @@ select * from t1; a 4 drop table t1,t2,t3; +# +# MDEV-31224: EXPLAIN EXTENDED for multi-table update of system table +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); +EXPLAIN EXTENDED UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 update `test`.`t1` set `test`.`t2`.`b` = 4 where `test`.`t1`.`a` in (6,2) +UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); +SELECT * from t2; +b +4 +DROP TABLE t1, t2; # End of 10.4 tests diff --git a/mysql-test/main/explain_non_select.test b/mysql-test/main/explain_non_select.test index c0c543ad273..e861955b3f1 100644 --- a/mysql-test/main/explain_non_select.test +++ b/mysql-test/main/explain_non_select.test @@ -277,4 +277,23 @@ select * from t1; drop table t1,t2,t3; +--echo # +--echo # MDEV-31224: EXPLAIN EXTENDED for multi-table update of system table +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); + +let $q= +UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); + +eval EXPLAIN EXTENDED $q; +eval $q; +SELECT * from t2; + +DROP TABLE t1, t2; + --echo # End of 10.4 tests diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 3ca9629d027..b515cb4fd83 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -2689,7 +2689,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 update `test`.`t1` set NULL = 10 +Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 @@ -2734,7 +2734,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 update `test`.`t1` set NULL = 10 where `test`.`t1`.`c3` = 10 +Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 where `test`.`t1`.`c3` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2be1fea9b03..a5d6ca05a1e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -28169,7 +28169,7 @@ void st_select_lex::print_set_clause(THD *thd, String *str, else str->append(','); - item->print(str, query_type); + item->print(str, (enum_query_type) (query_type | QT_NO_DATA_EXPANSION)); str->append(STRING_WITH_LEN(" = ")); val->print(str, query_type); } -- cgit v1.2.1 From 8c6e314ba98cf2026d00aad764e1b9aa3c87ace2 Mon Sep 17 00:00:00 2001 From: Daniel Bartholomew Date: Wed, 10 May 2023 08:11:15 -0400 Subject: bump the VERSION --- VERSION | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/VERSION b/VERSION index 2abc1c05da3..91b253ca00c 100644 --- a/VERSION +++ b/VERSION @@ -1,4 +1,4 @@ MYSQL_VERSION_MAJOR=10 MYSQL_VERSION_MINOR=4 -MYSQL_VERSION_PATCH=29 +MYSQL_VERSION_PATCH=30 SERVER_MATURITY=stable -- cgit v1.2.1 From 7e7e12e747a8284efea697518940f6a647ff915c Mon Sep 17 00:00:00 2001 From: Oleg Smirnov Date: Mon, 24 Apr 2023 18:38:42 +0700 Subject: MDEV-30765 SHOW TABLES not working properly with lower_case_table_names=2 lower_case_table_names=2 means "table names and database names are stored as declared, but they are compared in lowercase". But names of objects in grants are stored in lowercase for any value of lower_case_table_names. This caused an error when checking grants for objects containing uppercase letters since table_hash_search() didn't take into account lower_case_table_names value --- mysql-test/main/lowercase_table2.opt | 1 + mysql-test/main/lowercase_table2.result | 36 +++++++++++++++++++++++++++------ mysql-test/main/lowercase_table2.test | 26 ++++++++++++++++++++++++ sql/sql_acl.cc | 2 +- 4 files changed, 58 insertions(+), 7 deletions(-) create mode 100644 mysql-test/main/lowercase_table2.opt diff --git a/mysql-test/main/lowercase_table2.opt b/mysql-test/main/lowercase_table2.opt new file mode 100644 index 00000000000..ac4d3211e89 --- /dev/null +++ b/mysql-test/main/lowercase_table2.opt @@ -0,0 +1 @@ +--lower-case-table-names=2 diff --git a/mysql-test/main/lowercase_table2.result b/mysql-test/main/lowercase_table2.result index 9194638a4d2..fe06fb671a2 100644 --- a/mysql-test/main/lowercase_table2.result +++ b/mysql-test/main/lowercase_table2.result @@ -14,7 +14,7 @@ SHOW CREATE TABLE T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci RENAME TABLE T1 TO T2; SHOW TABLES LIKE "T2"; Tables_in_test (T2) @@ -70,7 +70,7 @@ SHOW CREATE TABLE T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci RENAME TABLE T1 TO T2; SHOW TABLES LIKE "T2"; Tables_in_test (T2) @@ -319,18 +319,42 @@ Database (mysql_t%) mysql_TEST show create database mysql_test; Database Create Database -mysql_test CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 */ +mysql_test CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ show create database mysql_TEST; Database Create Database -mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 */ +mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ show create table mysql_TEST.T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table mysql_test.t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop database mysql_TEST; +# MDEV-30765 SHOW TABLES not working properly with +# lower_case_table_names=2 +# +create database db1; +use db1; +# lowercase table name +create table `a` (a int); +# uppercase table name +create table `B` (a int); +create user 'mysqltest_1'@'localhost' identified by 'password'; +grant select, show view on db1.`a` to 'mysqltest_1'@'localhost'; +grant select, show view on db1.`B` to 'mysqltest_1'@'localhost'; +connect conn1, localhost, mysqltest_1, password, test; +connection conn1; +use db1; +show tables; +Tables_in_db1 +B +a +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1; diff --git a/mysql-test/main/lowercase_table2.test b/mysql-test/main/lowercase_table2.test index 601089ca760..82c07bf9345 100644 --- a/mysql-test/main/lowercase_table2.test +++ b/mysql-test/main/lowercase_table2.test @@ -288,3 +288,29 @@ show create database mysql_TEST; show create table mysql_TEST.T1; show create table mysql_test.t1; drop database mysql_TEST; + +--echo # MDEV-30765 SHOW TABLES not working properly with +--echo # lower_case_table_names=2 +--echo # +create database db1; +use db1; +--echo # lowercase table name +create table `a` (a int); +--echo # uppercase table name +create table `B` (a int); + +create user 'mysqltest_1'@'localhost' identified by 'password'; + +grant select, show view on db1.`a` to 'mysqltest_1'@'localhost'; +grant select, show view on db1.`B` to 'mysqltest_1'@'localhost'; + +connect (conn1, localhost, mysqltest_1, password, test); +connection conn1; +use db1; +show tables; + +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1; \ No newline at end of file diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 4bb16e3248d..c764d2fe2f7 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -5462,7 +5462,7 @@ table_hash_search(const char *host, const char *ip, const char *db, const char *user, const char *tname, bool exact) { return (GRANT_TABLE*) name_hash_search(&column_priv_hash, host, ip, db, - user, tname, exact, FALSE); + user, tname, exact, (lower_case_table_names > 0)); } static bool column_priv_insert(GRANT_TABLE *grant) -- cgit v1.2.1