diff options
38 files changed, 768 insertions, 603 deletions
diff --git a/cmake/cpack_rpm.cmake b/cmake/cpack_rpm.cmake index 630936b746c..4f33f6f951d 100644 --- a/cmake/cpack_rpm.cmake +++ b/cmake/cpack_rpm.cmake @@ -240,6 +240,7 @@ ELSEIF(RPM MATCHES "(rhel|centos)8") ALTERNATIVE_NAME("server" "mariadb-server-utils") ALTERNATIVE_NAME("shared" "mariadb-connector-c" ${MARIADB_CONNECTOR_C_VERSION}-1) ALTERNATIVE_NAME("shared" "mariadb-connector-c-config" ${MARIADB_CONNECTOR_C_VERSION}-1) + ALTERNATIVE_NAME("devel" "mariadb-connector-c-devel" ${MARIADB_CONNECTOR_C_VERSION}-1) SETA(CPACK_RPM_client_PACKAGE_PROVIDES "mariadb-galera = 3:%{version}-%{release}") SETA(CPACK_RPM_common_PACKAGE_PROVIDES "mariadb-galera-common = 3:%{version}-%{release}") SETA(CPACK_RPM_common_PACKAGE_REQUIRES "MariaDB-shared") diff --git a/extra/mariabackup/datasink.cc b/extra/mariabackup/datasink.cc index 29bdc061014..a576526d7ff 100644 --- a/extra/mariabackup/datasink.cc +++ b/extra/mariabackup/datasink.cc @@ -23,7 +23,6 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA #include "common.h" #include "datasink.h" #include "ds_compress.h" -#include "ds_archive.h" #include "ds_xbstream.h" #include "ds_local.h" #include "ds_stdout.h" @@ -45,13 +44,6 @@ ds_create(const char *root, ds_type_t type) case DS_TYPE_LOCAL: ds = &datasink_local; break; - case DS_TYPE_ARCHIVE: -#ifdef HAVE_LIBARCHIVE - ds = &datasink_archive; -#else - die("mariabackup was built without libarchive support"); -#endif - break; case DS_TYPE_XBSTREAM: ds = &datasink_xbstream; break; diff --git a/extra/mariabackup/datasink.h b/extra/mariabackup/datasink.h index 5c82556b9ba..4bede4ec9e7 100644 --- a/extra/mariabackup/datasink.h +++ b/extra/mariabackup/datasink.h @@ -63,7 +63,6 @@ static inline int dummy_remove(const char *) { typedef enum { DS_TYPE_STDOUT, DS_TYPE_LOCAL, - DS_TYPE_ARCHIVE, DS_TYPE_XBSTREAM, DS_TYPE_COMPRESS, DS_TYPE_ENCRYPT, diff --git a/extra/mariabackup/ds_archive.cc b/extra/mariabackup/ds_archive.cc deleted file mode 100644 index 3a5081119b3..00000000000 --- a/extra/mariabackup/ds_archive.cc +++ /dev/null @@ -1,282 +0,0 @@ -/****************************************************** -Copyright (c) 2013 Percona LLC and/or its affiliates. - -Streaming implementation for XtraBackup. - -This program is free software; you can redistribute it and/or modify -it under the terms of the GNU General Public License as published by -the Free Software Foundation; version 2 of the License. - -This program is distributed in the hope that it will be useful, -but WITHOUT ANY WARRANTY; without even the implied warranty of -MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -GNU General Public License for more details. - -You should have received a copy of the GNU General Public License -along with this program; if not, write to the Free Software -Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA - -*******************************************************/ - -#include <my_global.h> -#include <my_base.h> -#include <archive.h> -#include <archive_entry.h> -#include "common.h" -#include "datasink.h" - -#if ARCHIVE_VERSION_NUMBER < 3000000 -#define archive_write_add_filter_none(X) archive_write_set_compression_none(X) -#define archive_write_free(X) archive_write_finish(X) -#endif - -typedef struct { - struct archive *archive; - ds_file_t *dest_file; - pthread_mutex_t mutex; -} ds_archive_ctxt_t; - -typedef struct { - struct archive_entry *entry; - ds_archive_ctxt_t *archive_ctxt; -} ds_archive_file_t; - - -/*********************************************************************** -General archive interface */ - -static ds_ctxt_t *archive_init(const char *root); -static ds_file_t *archive_open(ds_ctxt_t *ctxt, const char *path, - MY_STAT *mystat); -static int archive_write(ds_file_t *file, const void *buf, size_t len); -static int archive_close(ds_file_t *file); -static void archive_deinit(ds_ctxt_t *ctxt); - -datasink_t datasink_archive = { - &archive_init, - &archive_open, - &archive_write, - &archive_close, - &dummy_remove, - &archive_deinit -}; - -static -int -my_archive_open_callback(struct archive *a __attribute__((unused)), - void *data __attribute__((unused))) -{ - return ARCHIVE_OK; -} - -static -ssize_t -my_archive_write_callback(struct archive *a __attribute__((unused)), - void *data, const void *buffer, size_t length) -{ - ds_archive_ctxt_t *archive_ctxt; - - archive_ctxt = (ds_archive_ctxt_t *) data; - - xb_ad(archive_ctxt != NULL); - xb_ad(archive_ctxt->dest_file != NULL); - - if (!ds_write(archive_ctxt->dest_file, buffer, length)) { - return length; - } - return -1; -} - -static -int -my_archive_close_callback(struct archive *a __attribute__((unused)), - void *data __attribute__((unused))) -{ - return ARCHIVE_OK; -} - -static -ds_ctxt_t * -archive_init(const char *root __attribute__((unused))) -{ - ds_ctxt_t *ctxt; - ds_archive_ctxt_t *archive_ctxt; - struct archive *a; - - ctxt = my_malloc(sizeof(ds_ctxt_t) + sizeof(ds_archive_ctxt_t), - MYF(MY_FAE)); - archive_ctxt = (ds_archive_ctxt_t *)(ctxt + 1); - - if (pthread_mutex_init(&archive_ctxt->mutex, NULL)) { - msg("archive_init: pthread_mutex_init() failed.\n"); - goto err; - } - - a = archive_write_new(); - if (a == NULL) { - msg("archive_write_new() failed.\n"); - goto err; - } - - archive_ctxt->archive = a; - archive_ctxt->dest_file = NULL; - - if(archive_write_add_filter_none(a) != ARCHIVE_OK || - archive_write_set_format_pax_restricted(a) != ARCHIVE_OK || - /* disable internal buffering so we don't have to flush the - output in xtrabackup */ - archive_write_set_bytes_per_block(a, 0) != ARCHIVE_OK) { - msg("failed to set libarchive archive options: %s\n", - archive_error_string(a)); - archive_write_free(a); - goto err; - } - - if (archive_write_open(a, archive_ctxt, my_archive_open_callback, - my_archive_write_callback, - my_archive_close_callback) != ARCHIVE_OK) { - msg("cannot open output archive.\n"); - return NULL; - } - - ctxt->ptr = archive_ctxt; - - return ctxt; - -err: - my_free(ctxt); - return NULL; -} - -static -ds_file_t * -archive_open(ds_ctxt_t *ctxt, const char *path, MY_STAT *mystat) -{ - ds_archive_ctxt_t *archive_ctxt; - ds_ctxt_t *dest_ctxt; - ds_file_t *file; - ds_archive_file_t *archive_file; - - struct archive *a; - struct archive_entry *entry; - - xb_ad(ctxt->pipe_ctxt != NULL); - dest_ctxt = ctxt->pipe_ctxt; - - archive_ctxt = (ds_archive_ctxt_t *) ctxt->ptr; - - pthread_mutex_lock(&archive_ctxt->mutex); - if (archive_ctxt->dest_file == NULL) { - archive_ctxt->dest_file = ds_open(dest_ctxt, path, mystat); - if (archive_ctxt->dest_file == NULL) { - return NULL; - } - } - pthread_mutex_unlock(&archive_ctxt->mutex); - - file = (ds_file_t *) my_malloc(sizeof(ds_file_t) + - sizeof(ds_archive_file_t), - MYF(MY_FAE)); - - archive_file = (ds_archive_file_t *) (file + 1); - - a = archive_ctxt->archive; - - entry = archive_entry_new(); - if (entry == NULL) { - msg("archive_entry_new() failed.\n"); - goto err; - } - - archive_entry_set_size(entry, mystat->st_size); - archive_entry_set_mode(entry, 0660); - archive_entry_set_filetype(entry, AE_IFREG); - archive_entry_set_pathname(entry, path); - archive_entry_set_mtime(entry, mystat->st_mtime, 0); - - archive_file->entry = entry; - archive_file->archive_ctxt = archive_ctxt; - - if (archive_write_header(a, entry) != ARCHIVE_OK) { - msg("archive_write_header() failed.\n"); - archive_entry_free(entry); - goto err; - } - - file->ptr = archive_file; - file->path = archive_ctxt->dest_file->path; - - return file; - -err: - if (archive_ctxt->dest_file) { - ds_close(archive_ctxt->dest_file); - archive_ctxt->dest_file = NULL; - } - my_free(file); - - return NULL; -} - -static -int -archive_write(ds_file_t *file, const void *buf, size_t len) -{ - ds_archive_file_t *archive_file; - struct archive *a; - - archive_file = (ds_archive_file_t *) file->ptr; - - a = archive_file->archive_ctxt->archive; - - xb_ad(archive_file->archive_ctxt->dest_file != NULL); - if (archive_write_data(a, buf, len) < 0) { - msg("archive_write_data() failed: %s (errno = %d)\n", - archive_error_string(a), archive_errno(a)); - return 1; - } - - return 0; -} - -static -int -archive_close(ds_file_t *file) -{ - ds_archive_file_t *archive_file; - int rc = 0; - - archive_file = (ds_archive_file_t *)file->ptr; - - archive_entry_free(archive_file->entry); - - my_free(file); - - return rc; -} - -static -void -archive_deinit(ds_ctxt_t *ctxt) -{ - struct archive *a; - ds_archive_ctxt_t *archive_ctxt; - - archive_ctxt = (ds_archive_ctxt_t *) ctxt->ptr; - - a = archive_ctxt->archive; - - if (archive_write_close(a) != ARCHIVE_OK) { - msg("archive_write_close() failed.\n"); - } - archive_write_free(a); - - if (archive_ctxt->dest_file) { - ds_close(archive_ctxt->dest_file); - archive_ctxt->dest_file = NULL; - } - - pthread_mutex_destroy(&archive_ctxt->mutex); - - my_free(ctxt); -} diff --git a/extra/mariabackup/ds_archive.h b/extra/mariabackup/ds_archive.h deleted file mode 100644 index f419fca0c9f..00000000000 --- a/extra/mariabackup/ds_archive.h +++ /dev/null @@ -1,28 +0,0 @@ -/****************************************************** -Copyright (c) 2013 Percona LLC and/or its affiliates. - -Streaming interface for XtraBackup. - -This program is free software; you can redistribute it and/or modify -it under the terms of the GNU General Public License as published by -the Free Software Foundation; version 2 of the License. - -This program is distributed in the hope that it will be useful, -but WITHOUT ANY WARRANTY; without even the implied warranty of -MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -GNU General Public License for more details. - -You should have received a copy of the GNU General Public License -along with this program; if not, write to the Free Software -Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA - -*******************************************************/ - -#ifndef DS_ARCHIVE_H -#define DS_ARCHIVE_H - -#include "datasink.h" - -extern datasink_t datasink_archive; - -#endif diff --git a/extra/mariabackup/ds_xbstream.cc b/extra/mariabackup/ds_xbstream.cc index 4d165ad11d9..3bf8bd086c2 100644 --- a/extra/mariabackup/ds_xbstream.cc +++ b/extra/mariabackup/ds_xbstream.cc @@ -126,14 +126,20 @@ xbstream_open(ds_ctxt_t *ctxt, const char *path, MY_STAT *mystat) pthread_mutex_lock(&stream_ctxt->mutex); if (stream_ctxt->dest_file == NULL) { stream_ctxt->dest_file = ds_open(dest_ctxt, path, mystat); - if (stream_ctxt->dest_file == NULL) { - return NULL; - } } pthread_mutex_unlock(&stream_ctxt->mutex); + if (stream_ctxt->dest_file == NULL) { + return NULL; + } file = (ds_file_t *) my_malloc(PSI_NOT_INSTRUMENTED, - sizeof(ds_file_t) + sizeof(ds_stream_file_t), MYF(MY_FAE)); + sizeof(ds_file_t) + + sizeof(ds_stream_file_t), + MYF(MY_FAE)); + if (!file) { + msg("my_malloc() failed."); + goto err; + } stream_file = (ds_stream_file_t *) (file + 1); xbstream = stream_ctxt->xbstream; diff --git a/libmysqld/libmysql.c b/libmysqld/libmysql.c index 9a17b9b4f09..9a0858fd067 100644 --- a/libmysqld/libmysql.c +++ b/libmysqld/libmysql.c @@ -3954,6 +3954,7 @@ static my_bool is_binary_compatible(enum enum_field_types type1, static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field) { + my_bool field_is_unsigned; DBUG_ENTER("setup_one_fetch_function"); /* Setup data copy functions for the different supported types */ @@ -4030,6 +4031,7 @@ static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field) /* Setup skip_result functions (to calculate max_length) */ param->skip_result= skip_result_fixed; + field_is_unsigned= MY_TEST(field->flags & UNSIGNED_FLAG); switch (field->type) { case MYSQL_TYPE_NULL: /* for dummy binds */ param->pack_length= 0; @@ -4037,23 +4039,23 @@ static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field) break; case MYSQL_TYPE_TINY: param->pack_length= 1; - field->max_length= 4; /* as in '-127' */ + field->max_length= field_is_unsigned ? 3 : 4; /* '255' and '-127' */ break; case MYSQL_TYPE_YEAR: case MYSQL_TYPE_SHORT: param->pack_length= 2; - field->max_length= 6; /* as in '-32767' */ + field->max_length= field_is_unsigned ? 5 : 6; /* 65536 and '-32767' */ break; case MYSQL_TYPE_INT24: - field->max_length= 9; /* as in '16777216' or in '-8388607' */ + field->max_length= 8; /* '16777216' or in '-8388607' */ param->pack_length= 4; break; case MYSQL_TYPE_LONG: - field->max_length= 11; /* '-2147483647' */ + field->max_length= field_is_unsigned ? 10 : 11; /* '4294967295' and '-2147483647' */ param->pack_length= 4; break; case MYSQL_TYPE_LONGLONG: - field->max_length= 21; /* '18446744073709551616' */ + field->max_length= 20; /* '18446744073709551616' or -9223372036854775808 */ param->pack_length= 8; break; case MYSQL_TYPE_FLOAT: diff --git a/man/mysqldump.1 b/man/mysqldump.1 index b3f346ca085..e942b10cdc5 100644 --- a/man/mysqldump.1 +++ b/man/mysqldump.1 @@ -2277,7 +2277,7 @@ servers \- remote (federated) servers as \fBCREATE SERVER\fR\&. .sp -1 .IP \(bu 2.3 .\} -stats \- statistics tables, InnoDB and Engine Independent Table Statistics (EITS), are dumped as \fBREPLACE INTO\fR (or \fBINSERT IGNORE\fR if \fB\-\-insert\-into\fR is specified) statements without (re)creating tables\&. +stats \- statistics tables, InnoDB and Engine Independent Table Statistics (EITS), are dumped as \fBREPLACE INTO\fR (or \fBINSERT IGNORE\fR if \fB\-\-insert\-ignore\fR is specified) statements without (re)creating tables\&. .RE .RS 4 .ie n \{\ @@ -2287,17 +2287,17 @@ stats \- statistics tables, InnoDB and Engine Independent Table Statistics (EITS .sp -1 .IP \(bu 2.3 .\} -timezones \- timezone related system tables dumped as \fBREPLACE INTO\fR (or \fBINSERT IGNORE\fR if \fB\-\-insert\-into\fR is specified) statements without (re)creating tables\&. +timezones \- timezone related system tables dumped as \fBREPLACE INTO\fR (or \fBINSERT IGNORE\fR if \fB\-\-insert\-ignore\fR is specified) statements without (re)creating tables\&. .RE .sp -The format of the output is affected by \fB\-\-replace\fR and \fB\-\-insert\-into\fR\&. The \fB\-\-replace\fR option will output \fBCREATE OR REPLACE\fR +The format of the output is affected by \fB\-\-replace\fR and \fB\-\-insert\-ignore\fR\&. The \fB\-\-replace\fR option will output \fBCREATE OR REPLACE\fR forms of SQL, and also \fBDROP IF EXISTS\fR prior to \fBCREATE\fR, if a \fBCREATE OR REPLACE\fR option isn't available. .sp With \fB\-\-system=user\fR (or \fBall\fR), and \fB\-\-replace\fR, SQL is generated to generate an error if attempting to import the dump with a connection user that is being replaced within the dump\&. .sp -The \fB\-\-insert\-into\fR option will cause \fBCREATE IF NOT EXIST\fR forms of SQL to generated if available. +The \fB\-\-insert\-ignore\fR option will cause \fBCREATE IF NOT EXIST\fR forms of SQL to generated if available. .sp -For stats, and timezones, \fB\-\-replace\fR and \fB\-\-insert\-into\fR have the usual effects. +For stats, and timezones, \fB\-\-replace\fR and \fB\-\-insert\-ignore\fR have the usual effects. .sp Enabling specific options here will cause the relevant tables in the mysql database to be ignored when dumping the mysql database or \fB\-\-all\-databases\fR\&. .sp diff --git a/mysql-test/main/alias.result b/mysql-test/main/alias.result index 0d14607f613..266991afbec 100644 --- a/mysql-test/main/alias.result +++ b/mysql-test/main/alias.result @@ -223,16 +223,16 @@ disconnect c1; # create or replace table t1 (a int); create or replace table t2 (b int); -insert into t1 values(1<<30),(1<<29); +insert into t1 values(111111111),(-2147483648); insert into t2 values(1),(2); select t1.a as a1 from t1 as t1,t2 order by t2.b,t1.a; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t1 t1 a a1 3 11 10 Y 32768 0 63 +def test t1 t1 a a1 3 11 11 Y 32768 0 63 a1 -536870912 -1073741824 -536870912 -1073741824 +-2147483648 +111111111 +-2147483648 +111111111 drop table t1,t2; # # End of 10.4 tests diff --git a/mysql-test/main/alias.test b/mysql-test/main/alias.test index 2408509ba10..881cc34d6c6 100644 --- a/mysql-test/main/alias.test +++ b/mysql-test/main/alias.test @@ -231,7 +231,7 @@ disconnect c1; --echo # create or replace table t1 (a int); create or replace table t2 (b int); -insert into t1 values(1<<30),(1<<29); +insert into t1 values(111111111),(-2147483648); insert into t2 values(1),(2); --enable_metadata select t1.a as a1 from t1 as t1,t2 order by t2.b,t1.a; diff --git a/mysql-test/main/mdev-504.result b/mysql-test/main/mdev-504.result deleted file mode 100644 index e34e57be6ed..00000000000 --- a/mysql-test/main/mdev-504.result +++ /dev/null @@ -1,25 +0,0 @@ -set @save_use_stat_tables=@@global.use_stat_tables; -SET GLOBAL net_write_timeout = 900; -CREATE TABLE A ( -pk INTEGER AUTO_INCREMENT PRIMARY KEY, -fdate DATE -) ENGINE=MyISAM; -CREATE PROCEDURE p_analyze() -BEGIN -DECLARE attempts INTEGER DEFAULT 100; -wl_loop: WHILE attempts > 0 DO -ANALYZE TABLE A; -SET attempts = attempts - 1; -END WHILE wl_loop; -END | -CREATE FUNCTION rnd3() RETURNS INT -BEGIN -RETURN ROUND(3 * RAND() + 0.5); -END | -SET GLOBAL use_stat_tables = PREFERABLY; -connection default; -DROP TABLE A; -DROP PROCEDURE p_analyze; -DROP FUNCTION rnd3; -SET GLOBAL use_stat_tables = @save_use_stat_tables; -SET GLOBAL net_write_timeout = DEFAULT; diff --git a/mysql-test/main/mdev-504.test b/mysql-test/main/mdev-504.test deleted file mode 100644 index 277b5a038a0..00000000000 --- a/mysql-test/main/mdev-504.test +++ /dev/null @@ -1,82 +0,0 @@ ---source include/not_valgrind.inc ---source include/no_protocol.inc - -set @save_use_stat_tables=@@global.use_stat_tables; - -SET GLOBAL net_write_timeout = 900; - -CREATE TABLE A ( - pk INTEGER AUTO_INCREMENT PRIMARY KEY, - fdate DATE -) ENGINE=MyISAM; - ---delimiter | - -CREATE PROCEDURE p_analyze() -BEGIN - DECLARE attempts INTEGER DEFAULT 100; - wl_loop: WHILE attempts > 0 DO - ANALYZE TABLE A; - SET attempts = attempts - 1; - END WHILE wl_loop; -END | - -CREATE FUNCTION rnd3() RETURNS INT -BEGIN - RETURN ROUND(3 * RAND() + 0.5); -END | - ---delimiter ; - -SET GLOBAL use_stat_tables = PREFERABLY; - ---let $trial = 100 - ---disable_query_log ---disable_result_log ---disable_warnings -while ($trial) -{ - - --connect (con1,localhost,root,,) - --send CALL p_analyze() - - --connect (con2,localhost,root,,) - --send CALL p_analyze() - - --let $run = 100 - - while ($run) - { - --connect (con3,localhost,root,,) - - let $query = `SELECT CASE rnd3() - WHEN 1 THEN 'INSERT INTO A (pk) VALUES (NULL)' - WHEN 2 THEN 'DELETE FROM A LIMIT 1' - ELSE 'UPDATE IGNORE A SET fdate = 2 LIMIT 1' END`; - --eval $query - --disconnect con3 - --dec $run - } - - --connection con2 - --reap - --disconnect con2 - --connection con1 - --reap - --disconnect con1 - - --dec $trial -} - ---enable_query_log ---enable_result_log ---enable_warnings - -# Cleanup ---connection default -DROP TABLE A; -DROP PROCEDURE p_analyze; -DROP FUNCTION rnd3; -SET GLOBAL use_stat_tables = @save_use_stat_tables; -SET GLOBAL net_write_timeout = DEFAULT; diff --git a/mysql-test/main/truncate_notembedded.result b/mysql-test/main/truncate_notembedded.result index 18a01c684e7..67beb79707c 100644 --- a/mysql-test/main/truncate_notembedded.result +++ b/mysql-test/main/truncate_notembedded.result @@ -5,7 +5,7 @@ CREATE TABLE t1 (a INT) ENGINE=MyISAM; LOCK TABLE t1 READ; connect con1,localhost,root,,test; -SET SESSION max_session_mem_used= 65536; +SET SESSION max_session_mem_used= 45500; LOCK TABLE t1 WRITE; connection default; SELECT * FROM t1; @@ -13,7 +13,6 @@ a UNLOCK TABLES; connection con1; TRUNCATE TABLE t1; -ERROR HY000: The MariaDB server is running with the --max-thread-mem-used=65536 option so it cannot execute this statement disconnect con1; connection default; DROP TABLE t1; diff --git a/mysql-test/main/truncate_notembedded.test b/mysql-test/main/truncate_notembedded.test index 7e186c8f7d5..c1fab2d3609 100644 --- a/mysql-test/main/truncate_notembedded.test +++ b/mysql-test/main/truncate_notembedded.test @@ -9,7 +9,7 @@ CREATE TABLE t1 (a INT) ENGINE=MyISAM; LOCK TABLE t1 READ; --connect (con1,localhost,root,,test) -SET SESSION max_session_mem_used= 65536; +SET SESSION max_session_mem_used= 45500; --send LOCK TABLE t1 WRITE; @@ -19,7 +19,10 @@ UNLOCK TABLES; --connection con1 --reap ---error ER_OPTION_PREVENTS_STATEMENT +# This may work or fail as different servers uses different amount of +# memory and the statement may work or not. What is important is that we +# don't get a crash here! +--error 0,ER_OPTION_PREVENTS_STATEMENT TRUNCATE TABLE t1; --disconnect con1 diff --git a/mysql-test/suite/encryption/r/innodb_encryption.result b/mysql-test/suite/encryption/r/innodb_encryption.result index 4ede82ebd38..3b1552be4be 100644 --- a/mysql-test/suite/encryption/r/innodb_encryption.result +++ b/mysql-test/suite/encryption/r/innodb_encryption.result @@ -19,7 +19,7 @@ innodb_system # Success! # Now turn off encryption and wait for threads to decrypt everything SET GLOBAL innodb_encrypt_tables = off; -# Wait max 10 min for key encryption threads to encrypt all spaces +# Wait max 10 min for key encryption threads to decrypt all spaces SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; NAME diff --git a/mysql-test/suite/encryption/t/innodb_encryption.test b/mysql-test/suite/encryption/t/innodb_encryption.test index 1c8d200458a..2b0b2b8d7fb 100644 --- a/mysql-test/suite/encryption/t/innodb_encryption.test +++ b/mysql-test/suite/encryption/t/innodb_encryption.test @@ -33,7 +33,7 @@ AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NA --echo # Now turn off encryption and wait for threads to decrypt everything SET GLOBAL innodb_encrypt_tables = off; ---echo # Wait max 10 min for key encryption threads to encrypt all spaces +--echo # Wait max 10 min for key encryption threads to decrypt all spaces --let $wait_timeout= 600 --let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; --source include/wait_condition.inc diff --git a/mysql-test/suite/galera/r/galera_schema.result b/mysql-test/suite/galera/r/galera_schema.result new file mode 100644 index 00000000000..24a4099c94d --- /dev/null +++ b/mysql-test/suite/galera/r/galera_schema.result @@ -0,0 +1,114 @@ +connection node_2; +connection node_1; +CREATE TABLE IF NOT EXISTS wsrep_cluster +( +cluster_uuid CHAR(36) PRIMARY KEY, +view_id BIGINT NOT NULL, +view_seqno BIGINT NOT NULL, +protocol_version INT NOT NULL, +capabilities INT NOT NULL +) ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS wsrep_cluster_members +( +node_uuid CHAR(36) PRIMARY KEY, +cluster_uuid CHAR(36) NOT NULL, +node_name CHAR(32) NOT NULL, +node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS wsrep_cluster_members_history +( +node_uuid CHAR(36) PRIMARY KEY, +cluster_uuid CHAR(36) NOT NULL, +last_view_id BIGINT NOT NULL, +last_view_seqno BIGINT NOT NULL, +node_name CHAR(32) NOT NULL, +node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS wsrep_streaming_log +( +node_uuid CHAR(36), +trx_id BIGINT, +seqno BIGINT, +flags INT NOT NULL, +frag LONGBLOB NOT NULL, +PRIMARY KEY (node_uuid, trx_id, seqno) +) ENGINE=InnoDB; +DELETE FROM wsrep_cluster; +DELETE FROM wsrep_cluster_members; +ALTER TABLE wsrep_cluster STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members_history STATS_PERSISTENT=0; +ALTER TABLE wsrep_streaming_log STATS_PERSISTENT=0; +SHOW CREATE TABLE wsrep_cluster; +Table Create Table +wsrep_cluster CREATE TABLE `wsrep_cluster` ( + `cluster_uuid` char(36) NOT NULL, + `view_id` bigint(20) NOT NULL, + `view_seqno` bigint(20) NOT NULL, + `protocol_version` int(11) NOT NULL, + `capabilities` int(11) NOT NULL, + PRIMARY KEY (`cluster_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE wsrep_cluster_members; +Table Create Table +wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( + `node_uuid` char(36) NOT NULL, + `cluster_uuid` char(36) NOT NULL, + `node_name` char(32) NOT NULL, + `node_incoming_address` varchar(256) NOT NULL, + PRIMARY KEY (`node_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE wsrep_cluster_members_history; +Table Create Table +wsrep_cluster_members_history CREATE TABLE `wsrep_cluster_members_history` ( + `node_uuid` char(36) NOT NULL, + `cluster_uuid` char(36) NOT NULL, + `last_view_id` bigint(20) NOT NULL, + `last_view_seqno` bigint(20) NOT NULL, + `node_name` char(32) NOT NULL, + `node_incoming_address` varchar(256) NOT NULL, + PRIMARY KEY (`node_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE wsrep_streaming_log; +Table Create Table +wsrep_streaming_log CREATE TABLE `wsrep_streaming_log` ( + `node_uuid` char(36) NOT NULL, + `trx_id` bigint(20) NOT NULL, + `seqno` bigint(20) NOT NULL, + `flags` int(11) NOT NULL, + `frag` longblob NOT NULL, + PRIMARY KEY (`node_uuid`,`trx_id`,`seqno`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE mysql.wsrep_cluster; +Table Create Table +wsrep_cluster CREATE TABLE `wsrep_cluster` ( + `cluster_uuid` char(36) NOT NULL, + `view_id` bigint(20) NOT NULL, + `view_seqno` bigint(20) NOT NULL, + `protocol_version` int(11) NOT NULL, + `capabilities` int(11) NOT NULL, + PRIMARY KEY (`cluster_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE mysql.wsrep_cluster_members; +Table Create Table +wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( + `node_uuid` char(36) NOT NULL, + `cluster_uuid` char(36) NOT NULL, + `node_name` char(32) NOT NULL, + `node_incoming_address` varchar(256) NOT NULL, + PRIMARY KEY (`node_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE mysql.wsrep_streaming_log; +Table Create Table +wsrep_streaming_log CREATE TABLE `wsrep_streaming_log` ( + `node_uuid` char(36) NOT NULL, + `trx_id` bigint(20) NOT NULL, + `seqno` bigint(20) NOT NULL, + `flags` int(11) NOT NULL, + `frag` longblob NOT NULL, + PRIMARY KEY (`node_uuid`,`trx_id`,`seqno`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +DROP TABLE wsrep_cluster; +DROP TABLE wsrep_cluster_members; +DROP TABLE wsrep_cluster_members_history; +DROP TABLE wsrep_streaming_log; diff --git a/mysql-test/suite/galera/t/galera_schema.test b/mysql-test/suite/galera/t/galera_schema.test new file mode 100644 index 00000000000..a3ee814c393 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_schema.test @@ -0,0 +1,61 @@ +--source include/galera_cluster.inc + +CREATE TABLE IF NOT EXISTS wsrep_cluster +( + cluster_uuid CHAR(36) PRIMARY KEY, + view_id BIGINT NOT NULL, + view_seqno BIGINT NOT NULL, + protocol_version INT NOT NULL, + capabilities INT NOT NULL +) ENGINE=InnoDB; + +CREATE TABLE IF NOT EXISTS wsrep_cluster_members +( + node_uuid CHAR(36) PRIMARY KEY, + cluster_uuid CHAR(36) NOT NULL, + node_name CHAR(32) NOT NULL, + node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; + +CREATE TABLE IF NOT EXISTS wsrep_cluster_members_history +( + node_uuid CHAR(36) PRIMARY KEY, + cluster_uuid CHAR(36) NOT NULL, + last_view_id BIGINT NOT NULL, + last_view_seqno BIGINT NOT NULL, + node_name CHAR(32) NOT NULL, + node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; + +CREATE TABLE IF NOT EXISTS wsrep_streaming_log +( + node_uuid CHAR(36), + trx_id BIGINT, + seqno BIGINT, + flags INT NOT NULL, + frag LONGBLOB NOT NULL, + PRIMARY KEY (node_uuid, trx_id, seqno) +) ENGINE=InnoDB; + +DELETE FROM wsrep_cluster; +DELETE FROM wsrep_cluster_members; + +ALTER TABLE wsrep_cluster STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members_history STATS_PERSISTENT=0; +ALTER TABLE wsrep_streaming_log STATS_PERSISTENT=0; + +SHOW CREATE TABLE wsrep_cluster; +SHOW CREATE TABLE wsrep_cluster_members; +SHOW CREATE TABLE wsrep_cluster_members_history; +SHOW CREATE TABLE wsrep_streaming_log; + +SHOW CREATE TABLE mysql.wsrep_cluster; +SHOW CREATE TABLE mysql.wsrep_cluster_members; +#SHOW CREATE TABLE mysql.wsrep_cluster_members_history; +SHOW CREATE TABLE mysql.wsrep_streaming_log; + +DROP TABLE wsrep_cluster; +DROP TABLE wsrep_cluster_members; +DROP TABLE wsrep_cluster_members_history; +DROP TABLE wsrep_streaming_log; diff --git a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result index ba01eab0e26..f51eb815cd5 100644 --- a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result +++ b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result @@ -14,7 +14,7 @@ wsrep_cluster CREATE TABLE `wsrep_cluster` ( `protocol_version` int(11) NOT NULL, `capabilities` int(11) NOT NULL, PRIMARY KEY (`cluster_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SHOW CREATE TABLE mysql.wsrep_cluster_members; Table Create Table wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( @@ -23,7 +23,7 @@ wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( `node_name` char(32) NOT NULL, `node_incoming_address` varchar(256) NOT NULL, PRIMARY KEY (`node_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SELECT COUNT(*) AS EXPECT_1 FROM mysql.wsrep_cluster; EXPECT_1 1 diff --git a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result index 2a29afd62be..d9d3e817bed 100644 --- a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result +++ b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result @@ -14,7 +14,7 @@ wsrep_cluster CREATE TABLE `wsrep_cluster` ( `protocol_version` int(11) NOT NULL, `capabilities` int(11) NOT NULL, PRIMARY KEY (`cluster_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SHOW CREATE TABLE mysql.wsrep_cluster_members; Table Create Table wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( @@ -23,7 +23,7 @@ wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( `node_name` char(32) NOT NULL, `node_incoming_address` varchar(256) NOT NULL, PRIMARY KEY (`node_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SELECT @@sql_safe_updates; @@sql_safe_updates 1 diff --git a/mysql-test/suite/galera_sr/r/GCF-627.result b/mysql-test/suite/galera_sr/r/GCF-627.result index 65d8c95ad08..7cd2ab63ff3 100644 --- a/mysql-test/suite/galera_sr/r/GCF-627.result +++ b/mysql-test/suite/galera_sr/r/GCF-627.result @@ -2,7 +2,6 @@ connection node_2; connection node_1; connection node_1; CREATE TABLE t1 (f1 INTEGER, f2 VARCHAR(10)) ENGINE=InnoDB; -CREATE TABLE t2 (f1 INTEGER); SET SESSION wsrep_trx_fragment_size = 1; SET AUTOCOMMIT=OFF; START TRANSACTION; @@ -16,9 +15,10 @@ connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; INSERT INTO t1 VALUES (2); ERROR 42S02: Table 'test.t1' doesn't exist connection node_1; -SELECT * FROM mysql.wsrep_streaming_log; -node_uuid trx_id seqno flags frag +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; +COUNT(*) +0 connection node_2; -SELECT * FROM mysql.wsrep_streaming_log; -node_uuid trx_id seqno flags frag -DROP TABLE t2; +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; +COUNT(*) +0 diff --git a/mysql-test/suite/galera_sr/t/GCF-627.test b/mysql-test/suite/galera_sr/t/GCF-627.test index ad351eb9da6..6990c12314d 100644 --- a/mysql-test/suite/galera_sr/t/GCF-627.test +++ b/mysql-test/suite/galera_sr/t/GCF-627.test @@ -1,9 +1,7 @@ --source include/galera_cluster.inc ---source include/have_innodb.inc --connection node_1 CREATE TABLE t1 (f1 INTEGER, f2 VARCHAR(10)) ENGINE=InnoDB; -CREATE TABLE t2 (f1 INTEGER); SET SESSION wsrep_trx_fragment_size = 1; SET AUTOCOMMIT=OFF; @@ -25,11 +23,9 @@ INSERT INTO t1 VALUES (2); --let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.wsrep_streaming_log --source include/wait_condition.inc -SELECT * FROM mysql.wsrep_streaming_log; +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; --connection node_2 --let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.wsrep_streaming_log --source include/wait_condition.inc -SELECT * FROM mysql.wsrep_streaming_log; - -DROP TABLE t2; +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; diff --git a/mysql-test/suite/innodb/r/innodb_defrag_stats.result b/mysql-test/suite/innodb/r/innodb_defrag_stats.result index 598124e4ccb..b60f5f5c5a9 100644 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats.result +++ b/mysql-test/suite/innodb/r/innodb_defrag_stats.result @@ -1,22 +1,8 @@ -DROP TABLE if exists t1; -select @@global.innodb_stats_persistent; -@@global.innodb_stats_persistent -0 -set global innodb_defragment_stats_accuracy = 20; +SET GLOBAL innodb_defragment_stats_accuracy = 20; +DELETE FROM mysql.innodb_index_stats; # Create table. CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -# Populate data -INSERT INTO t1 VALUES(1, REPEAT('A', 256)); -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; # Not enough page splits to trigger persistent stats write yet. select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) = 0 @@ -27,7 +13,7 @@ count(stat_value) = 0 select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) = 0 1 -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; # Persistent stats recorded. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 @@ -39,6 +25,7 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like count(stat_value) > 0 1 # Delete some rows. +BEGIN; delete from t1 where a between 100 * 20 and 100 * 20 + 30; delete from t1 where a between 100 * 19 and 100 * 19 + 30; delete from t1 where a between 100 * 18 and 100 * 18 + 30; @@ -59,9 +46,7 @@ delete from t1 where a between 100 * 4 and 100 * 4 + 30; delete from t1 where a between 100 * 3 and 100 * 3 + 30; delete from t1 where a between 100 * 2 and 100 * 2 + 30; delete from t1 where a between 100 * 1 and 100 * 1 + 30; -# restart -# Server Restarted -# Confirm persistent stats still there after restart. +COMMIT; select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 1 @@ -74,9 +59,6 @@ count(stat_value) > 0 optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK -select sleep(2); -sleep(2) -0 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 1 @@ -109,9 +91,6 @@ count(stat_value) > 0 1 # Table rename should cause stats rename. rename table t1 to t2; -select sleep(1); -sleep(1) -0 select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) = 0 1 @@ -130,48 +109,37 @@ count(stat_value) > 0 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) > 0 1 -# Drop index should cause stats drop. +# Drop index should cause stats drop, but will not. drop index SECOND on t2; -select sleep(3); -sleep(3) -0 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats +WHERE table_name like '%t2%' AND index_name='SECOND'; +stat_name stat_value>0 +n_leaf_pages_defrag 1 +n_leaf_pages_reserved 1 +n_page_split 1 +n_pages_freed 1 +# +# MDEV-26636: Statistics must not be written for temporary tables +# +SET GLOBAL innodb_defragment_stats_accuracy = 1; +CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) +ENGINE=InnoDB; +INSERT INTO t SELECT seq, '' FROM seq_1_to_100; # restart -Server Restarted -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; +database_name table_name index_name last_update stat_name stat_value sample_size stat_description +SELECT table_name, index_name, stat_name, stat_value>0 +FROM mysql.innodb_index_stats; +table_name index_name stat_name stat_value>0 +t2 PRIMARY n_leaf_pages_defrag 1 +t2 PRIMARY n_leaf_pages_reserved 1 +t2 PRIMARY n_page_split 1 +t2 PRIMARY n_pages_freed 1 +t2 SECOND n_leaf_pages_defrag 1 +t2 SECOND n_leaf_pages_reserved 1 +t2 SECOND n_page_split 1 +t2 SECOND n_pages_freed 1 # Clean up DROP TABLE t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 +SELECT * FROM mysql.innodb_index_stats; +database_name table_name index_name last_update stat_name stat_value sample_size stat_description diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats.test b/mysql-test/suite/innodb/t/innodb_defrag_stats.test index 2a5026a68e5..e1e88a07477 100644 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats.test +++ b/mysql-test/suite/innodb/t/innodb_defrag_stats.test @@ -1,41 +1,23 @@ --source include/have_innodb.inc ---source include/big_test.inc --source include/not_valgrind.inc --source include/not_embedded.inc +--source include/have_sequence.inc ---disable_warnings -DROP TABLE if exists t1; ---enable_warnings +SET GLOBAL innodb_defragment_stats_accuracy = 20; ---disable_query_log -let $innodb_defragment_stats_accuracy_orig=`select @@innodb_defragment_stats_accuracy`; ---enable_query_log - -select @@global.innodb_stats_persistent; -set global innodb_defragment_stats_accuracy = 20; +DELETE FROM mysql.innodb_index_stats; --echo # Create table. CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; ---echo # Populate data -INSERT INTO t1 VALUES(1, REPEAT('A', 256)); -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; --echo # Not enough page splits to trigger persistent stats write yet. select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; --echo # Persistent stats recorded. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); @@ -43,6 +25,7 @@ select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); --echo # Delete some rows. +BEGIN; let $num_delete = 20; while ($num_delete) { @@ -50,17 +33,13 @@ while ($num_delete) eval delete from t1 where a between $j and $j + 30; dec $num_delete; } +COMMIT; ---source include/restart_mysqld.inc ---echo # Server Restarted - ---echo # Confirm persistent stats still there after restart. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); optimize table t1; -select sleep(2); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); @@ -84,7 +63,6 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like --echo # Table rename should cause stats rename. rename table t1 to t2; -select sleep(1); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); @@ -94,32 +72,30 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); ---echo # Drop index should cause stats drop. +--echo # Drop index should cause stats drop, but will not. drop index SECOND on t2; -select sleep(3); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats +WHERE table_name like '%t2%' AND index_name='SECOND'; + +--echo # +--echo # MDEV-26636: Statistics must not be written for temporary tables +--echo # +SET GLOBAL innodb_defragment_stats_accuracy = 1; +CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) +ENGINE=InnoDB; +INSERT INTO t SELECT seq, '' FROM seq_1_to_100; --source include/restart_mysqld.inc ---echo Server Restarted -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); +SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT table_name, index_name, stat_name, stat_value>0 +FROM mysql.innodb_index_stats; --echo # Clean up DROP TABLE t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); - ---disable_query_log -EVAL SET GLOBAL innodb_defragment_stats_accuracy = $innodb_defragment_stats_accuracy_orig; ---enable_query_log +SELECT * FROM mysql.innodb_index_stats; diff --git a/scripts/wsrep_sst_mariabackup.sh b/scripts/wsrep_sst_mariabackup.sh index 562b9b929f2..54632e5f79b 100644 --- a/scripts/wsrep_sst_mariabackup.sh +++ b/scripts/wsrep_sst_mariabackup.sh @@ -949,8 +949,10 @@ then tmpdir=$(parse_cnf "$encgroups" 'tmpdir') if [ -z "$tmpdir" ]; then xtmpdir="$(mktemp -d)" - else + elif [ "$OS" = 'Linux' ]; then xtmpdir=$(mktemp '-d' "--tmpdir=$tmpdir") + else + xtmpdir=$(TMPDIR="$tmpdir"; mktemp '-d') fi wsrep_log_info "Using '$xtmpdir' as mariabackup temporary directory" diff --git a/scripts/wsrep_sst_rsync.sh b/scripts/wsrep_sst_rsync.sh index d90e87b68f2..e16ed75cb16 100644 --- a/scripts/wsrep_sst_rsync.sh +++ b/scripts/wsrep_sst_rsync.sh @@ -725,8 +725,10 @@ EOF tmpdir=$(parse_cnf '--mysqld|sst' 'tmpdir') if [ -z "$tmpdir" ]; then tmpfile="$(mktemp)" - else + elif [ "$OS" = 'Linux' ]; then tmpfile=$(mktemp "--tmpdir=$tmpdir") + else + tmpfile=$(TMPDIR="$tmpdir"; mktemp '-d') fi wsrep_log_info "Extracting binlog files:" diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 1966a77aa3e..7e053e60fdf 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -620,14 +620,18 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, if (!table->check_virtual_columns_marked_for_read()) { DBUG_PRINT("info", ("Trying direct delete")); - if (select && select->cond && - (select->cond->used_tables() == table->map)) + bool use_direct_delete= !select || !select->cond; + if (!use_direct_delete && + (select->cond->used_tables() & ~RAND_TABLE_BIT) == table->map) { DBUG_ASSERT(!table->file->pushed_cond); if (!table->file->cond_push(select->cond)) + { + use_direct_delete= TRUE; table->file->pushed_cond= select->cond; + } } - if (!table->file->direct_delete_rows_init()) + if (use_direct_delete && !table->file->direct_delete_rows_init()) { /* Direct deleting is supported */ DBUG_PRINT("info", ("Using direct delete")); diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 977e459f9df..624c183c83b 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -754,15 +754,20 @@ int mysql_update(THD *thd, !table->check_virtual_columns_marked_for_write()) { DBUG_PRINT("info", ("Trying direct update")); - if (select && select->cond && - (select->cond->used_tables() == table->map)) + bool use_direct_update= !select || !select->cond; + if (!use_direct_update && + (select->cond->used_tables() & ~RAND_TABLE_BIT) == table->map) { DBUG_ASSERT(!table->file->pushed_cond); if (!table->file->cond_push(select->cond)) + { + use_direct_update= TRUE; table->file->pushed_cond= select->cond; + } } - if (!table->file->info_push(INFO_KIND_UPDATE_FIELDS, &fields) && + if (use_direct_update && + !table->file->info_push(INFO_KIND_UPDATE_FIELDS, &fields) && !table->file->info_push(INFO_KIND_UPDATE_VALUES, &values) && !table->file->direct_update_rows_init(&fields)) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d2fc4273f49..7d73bc97ea4 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -74,6 +74,9 @@ /* warning C4065: switch statement contains 'default' but no 'case' labels */ #pragma warning (disable : 4065) #endif +#ifdef __GNUC__ +#pragma GCC diagnostic ignored "-Wunused-label" /* yyexhaustedlab: */ +#endif int yylex(void *yylval, void *yythd); diff --git a/sql/wsrep_schema.cc b/sql/wsrep_schema.cc index 0df1b527afe..252f41cb380 100644 --- a/sql/wsrep_schema.cc +++ b/sql/wsrep_schema.cc @@ -1,4 +1,4 @@ -/* Copyright (C) 2015-2019 Codership Oy <info@codership.com> +/* Copyright (C) 2015-2021 Codership Oy <info@codership.com> This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -54,7 +54,7 @@ static const std::string create_cluster_table_str= "view_seqno BIGINT NOT NULL," "protocol_version INT NOT NULL," "capabilities INT NOT NULL" - ") ENGINE=InnoDB"; + ") ENGINE=InnoDB STATS_PERSISTENT=0"; static const std::string create_members_table_str= "CREATE TABLE IF NOT EXISTS " + wsrep_schema_str + "." + members_table_str + @@ -63,7 +63,7 @@ static const std::string create_members_table_str= "cluster_uuid CHAR(36) NOT NULL," "node_name CHAR(32) NOT NULL," "node_incoming_address VARCHAR(256) NOT NULL" - ") ENGINE=InnoDB"; + ") ENGINE=InnoDB STATS_PERSISTENT=0"; #ifdef WSREP_SCHEMA_MEMBERS_HISTORY static const std::string cluster_member_history_table_str= "wsrep_cluster_member_history"; @@ -76,7 +76,7 @@ static const std::string create_members_history_table_str= "last_view_seqno BIGINT NOT NULL," "node_name CHAR(32) NOT NULL," "node_incoming_address VARCHAR(256) NOT NULL" - ") ENGINE=InnoDB"; + ") ENGINE=InnoDB STATS_PERSISTENT=0"; #endif /* WSREP_SCHEMA_MEMBERS_HISTORY */ static const std::string create_frag_table_str= @@ -88,7 +88,7 @@ static const std::string create_frag_table_str= "flags INT NOT NULL, " "frag LONGBLOB NOT NULL, " "PRIMARY KEY (node_uuid, trx_id, seqno)" - ") ENGINE=InnoDB"; + ") ENGINE=InnoDB STATS_PERSISTENT=0"; static const std::string delete_from_cluster_table= "DELETE FROM " + wsrep_schema_str + "." + cluster_table_str; @@ -96,6 +96,26 @@ static const std::string delete_from_cluster_table= static const std::string delete_from_members_table= "DELETE FROM " + wsrep_schema_str + "." + members_table_str; +/* For rolling upgrade we need to use ALTER. We do not want +persistent statistics to be collected from these tables. */ +static const std::string alter_cluster_table= + "ALTER TABLE " + wsrep_schema_str + "." + cluster_table_str + + " STATS_PERSISTENT=0"; + +static const std::string alter_members_table= + "ALTER TABLE " + wsrep_schema_str + "." + members_table_str + + " STATS_PERSISTENT=0"; + +#ifdef WSREP_SCHEMA_MEMBERS_HISTORY +static const std::string alter_members_history_table= + "ALTER TABLE " + wsrep_schema_str + "." + members_history_table_str + + " STATS_PERSISTENT=0"; +#endif + +static const std::string alter_frag_table= + "ALTER TABLE " + wsrep_schema_str + "." + sr_table_str + + " STATS_PERSISTENT=0"; + namespace Wsrep_schema_impl { @@ -675,13 +695,27 @@ int Wsrep_schema::init() Wsrep_schema_impl::execute_SQL(thd, create_members_history_table_str.c_str(), create_members_history_table_str.size()) || + Wsrep_schema_impl::execute_SQL(thd, + alter_members_history_table.c_str(), + alter_members_history_table.size()) || #endif /* WSREP_SCHEMA_MEMBERS_HISTORY */ Wsrep_schema_impl::execute_SQL(thd, create_frag_table_str.c_str(), - create_frag_table_str.size())) { + create_frag_table_str.size()) || + Wsrep_schema_impl::execute_SQL(thd, + alter_cluster_table.c_str(), + alter_cluster_table.size()) || + Wsrep_schema_impl::execute_SQL(thd, + alter_members_table.c_str(), + alter_members_table.size()) || + Wsrep_schema_impl::execute_SQL(thd, + alter_frag_table.c_str(), + alter_frag_table.size())) + { ret= 1; } - else { + else + { ret= 0; } diff --git a/storage/innobase/btr/btr0defragment.cc b/storage/innobase/btr/btr0defragment.cc index d8c9cadb2b2..09454aeccc1 100644 --- a/storage/innobase/btr/btr0defragment.cc +++ b/storage/innobase/btr/btr0defragment.cc @@ -353,6 +353,7 @@ btr_defragment_save_defrag_stats_if_needed( { if (srv_defragment_stats_accuracy != 0 // stats tracking disabled && index->table->space_id != 0 // do not track system tables + && !index->table->is_temporary() && index->stat_defrag_modified_counter >= srv_defragment_stats_accuracy) { dict_stats_defrag_pool_add(index); diff --git a/storage/innobase/fsp/fsp0fsp.cc b/storage/innobase/fsp/fsp0fsp.cc index a4f622a19ec..962d18d8081 100644 --- a/storage/innobase/fsp/fsp0fsp.cc +++ b/storage/innobase/fsp/fsp0fsp.cc @@ -1177,7 +1177,7 @@ fsp_alloc_free_page( /* It must be that we are extending a single-table tablespace whose size is still < 64 pages */ - ut_a(!is_system_tablespace(space_id)); + ut_a(!is_predefined_tablespace(space_id)); if (page_no >= FSP_EXTENT_SIZE) { ib::error() << "Trying to extend a single-table" " tablespace " << space->name << " , by single" diff --git a/storage/innobase/row/row0ftsort.cc b/storage/innobase/row/row0ftsort.cc index 5ec73d9677c..8513210d602 100644 --- a/storage/innobase/row/row0ftsort.cc +++ b/storage/innobase/row/row0ftsort.cc @@ -1635,7 +1635,6 @@ row_fts_merge_insert( aux_table = dict_table_open_on_name(aux_table_name, FALSE, FALSE, DICT_ERR_IGNORE_NONE); ut_ad(aux_table != NULL); - dict_table_close(aux_table, FALSE, FALSE); aux_index = dict_table_get_first_index(aux_table); ut_ad(!aux_index->is_instant()); @@ -1760,6 +1759,8 @@ row_fts_merge_insert( } exit: + dict_table_close(aux_table, FALSE, FALSE); + fts_sql_commit(trx); trx->op_info = ""; diff --git a/storage/spider/mysql-test/spider/r/udf_pushdown.result b/storage/spider/mysql-test/spider/r/udf_pushdown.result new file mode 100644 index 00000000000..4ca734165e7 --- /dev/null +++ b/storage/spider/mysql-test/spider/r/udf_pushdown.result @@ -0,0 +1,218 @@ +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 +# +# MDEV-26545 Spider does not correctly handle UDF and stored function in where conds +# + +##### enable general_log ##### +connection child2_1; +SET @general_log_backup = @@global.general_log; +SET @log_output_backup = @@global.log_output; +SET @@global.general_log = 1; +SET @@global.log_output = "TABLE"; +TRUNCATE TABLE mysql.general_log; + +##### create databases ##### +connection master_1; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child2_1; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; + +##### create tables ##### +connection child2_1; +CHILD_CREATE_TABLE +connection master_1; +MASTER_CREATE_TABLE +CREATE TABLE ta_l ( +id INT NOT NULL, +a INT, +PRIMARY KEY(id) +) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 +INSERT INTO ta_l VALUES +(1, 11), +(2, 22), +(3, 33), +(4, 44), +(5, 55); + +##### create functions ##### +connection master_1; +CREATE FUNCTION `plusone`( param INT ) RETURNS INT +BEGIN +RETURN param + 1; +END // +connection child2_1; +CREATE FUNCTION `plusone`( param INT ) RETURNS INT +BEGIN +RETURN param + 1; +END // + +########## spider_use_pushdown_udf=0 ########## +connection master_1; +SET @@spider_use_pushdown_udf = 0; + +##### test SELECTs ##### +connection master_1; +SELECT * FROM ta_l WHERE id = plusone(1); +id a +2 22 +SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32); +id a +3 33 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%"; +argument +select `id`,`a` from `auto_test_remote`.`ta_r` +select `id`,`a` from `auto_test_remote`.`ta_r` + +##### test UPDATEs ##### +connection master_1; +UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1); +SELECT * FROM ta_l; +id a +1 11 +2 222 +3 33 +4 44 +5 55 +UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32); +SELECT * FROM ta_l; +id a +1 11 +2 222 +3 333 +4 44 +5 55 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%"; +argument +select `id`,`a` from `auto_test_remote`.`ta_r` for update +update `auto_test_remote`.`ta_r` set `a` = 222 where `id` = 2 limit 1 +select `id`,`a` from `auto_test_remote`.`ta_r` for update +update `auto_test_remote`.`ta_r` set `a` = 333 where `id` = 3 and `a` = 33 limit 1 + +##### test DELETEs ##### +connection master_1; +DELETE FROM ta_l WHERE id = plusone(1); +SELECT * FROM ta_l; +id a +1 11 +3 333 +4 44 +5 55 +DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43); +SELECT * FROM ta_l; +id a +1 11 +3 333 +5 55 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%"; +argument +select `id` from `auto_test_remote`.`ta_r` for update +delete from `auto_test_remote`.`ta_r` where `id` = 2 limit 1 +select `id`,`a` from `auto_test_remote`.`ta_r` for update +delete from `auto_test_remote`.`ta_r` where `id` = 4 and `a` = 44 limit 1 + +##### reset records ##### +connection master_1; +TRUNCATE TABLE ta_l; +INSERT INTO ta_l VALUES +(1, 11), +(2, 22), +(3, 33), +(4, 44), +(5, 55); + +########## spider_use_pushdown_udf=1 ########## +connection master_1; +SET @@spider_use_pushdown_udf = 1; + +##### test SELECTs ##### +connection master_1; +SELECT * FROM ta_l WHERE id = plusone(1); +id a +2 22 +SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32); +id a +3 33 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%"; +argument +select t0.`id` `id`,t0.`a` `a` from `auto_test_remote`.`ta_r` t0 where (t0.`id` = (`plusone`(1))) +select t0.`id` `id`,t0.`a` `a` from `auto_test_remote`.`ta_r` t0 where ((t0.`id` in( (`plusone`(1)) , (`plusone`(2)))) and (t0.`a` = (`plusone`(32)))) + +##### test UPDATEs ##### +connection master_1; +UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1); +SELECT * FROM ta_l; +id a +1 11 +2 222 +3 33 +4 44 +5 55 +UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32); +SELECT * FROM ta_l; +id a +1 11 +2 222 +3 333 +4 44 +5 55 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%"; +argument +update `auto_test_remote`.`ta_r` set `a` = (`plusone`(221)) where (`id` = (`plusone`(1))) +update `auto_test_remote`.`ta_r` set `a` = (`plusone`(332)) where ((`id` in( (`plusone`(1)) , (`plusone`(2)))) and (`a` = (`plusone`(32)))) + +##### test DELETEs ##### +connection master_1; +DELETE FROM ta_l WHERE id = plusone(1); +SELECT * FROM ta_l; +id a +1 11 +3 333 +4 44 +5 55 +DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43); +SELECT * FROM ta_l; +id a +1 11 +3 333 +5 55 +connection child2_1; +SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%"; +argument +delete from `auto_test_remote`.`ta_r` where (`id` = (`plusone`(1))) +delete from `auto_test_remote`.`ta_r` where ((`id` in( (`plusone`(1)) , (`plusone`(2)) , (`plusone`(3)))) and (`a` = (`plusone`(43)))) + +deinit +connection master_1; +DROP FUNCTION `plusone`; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +SET @@global.general_log = @general_log_backup; +SET @@global.log_output = @log_output_backup; +DROP FUNCTION `plusone`; +DROP DATABASE IF EXISTS auto_test_remote; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +end of test diff --git a/storage/spider/mysql-test/spider/t/udf_pushdown.inc b/storage/spider/mysql-test/spider/t/udf_pushdown.inc new file mode 100644 index 00000000000..160e8af21b2 --- /dev/null +++ b/storage/spider/mysql-test/spider/t/udf_pushdown.inc @@ -0,0 +1,48 @@ +--echo +--echo ##### test SELECTs ##### +--connection master_1 +SELECT * FROM ta_l WHERE id = plusone(1); +SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32); + +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%"; + --disable_query_log + TRUNCATE TABLE mysql.general_log; + --enable_query_log +} + +--echo +--echo ##### test UPDATEs ##### +--connection master_1 +UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1); +SELECT * FROM ta_l; +UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32); +SELECT * FROM ta_l; + +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%"; + --disable_query_log + TRUNCATE TABLE mysql.general_log; + --enable_query_log +} + +--echo +--echo ##### test DELETEs ##### +--connection master_1 +DELETE FROM ta_l WHERE id = plusone(1); +SELECT * FROM ta_l; +DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43); +SELECT * FROM ta_l; + +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%"; + --disable_query_log + TRUNCATE TABLE mysql.general_log; + --enable_query_log +} diff --git a/storage/spider/mysql-test/spider/t/udf_pushdown.test b/storage/spider/mysql-test/spider/t/udf_pushdown.test new file mode 100644 index 00000000000..2eadbbbb40b --- /dev/null +++ b/storage/spider/mysql-test/spider/t/udf_pushdown.test @@ -0,0 +1,141 @@ +--disable_warnings +--disable_query_log +--disable_result_log +--source test_init.inc +--enable_result_log +--enable_query_log + +--echo # +--echo # MDEV-26545 Spider does not correctly handle UDF and stored function in where conds +--echo # + +let $CHILD_CREATE_TABLE= + CREATE TABLE ta_r ( + id INT NOT NULL, + a INT, + PRIMARY KEY(id) + ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +let $MASTER_CREATE_TABLE_OUTPUT= + CREATE TABLE ta_l ( + id INT NOT NULL, + a INT, + PRIMARY KEY(id) + ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1; + +let $MASTER_CREATE_TABLE= + CREATE TABLE ta_l ( + id INT NOT NULL, + a INT, + PRIMARY KEY(id) + ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1; + +--echo +--echo ##### enable general_log ##### +--connection child2_1 +SET @general_log_backup = @@global.general_log; +SET @log_output_backup = @@global.log_output; +SET @@global.general_log = 1; +SET @@global.log_output = "TABLE"; +TRUNCATE TABLE mysql.general_log; + +--echo +--echo ##### create databases ##### +--connection master_1 +CREATE DATABASE auto_test_local; +USE auto_test_local; +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + CREATE DATABASE auto_test_remote; + USE auto_test_remote; +} + +--echo +--echo ##### create tables ##### +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + --disable_query_log + echo CHILD_CREATE_TABLE; + eval $CHILD_CREATE_TABLE; + --enable_query_log +} + +--connection master_1 +--disable_query_log +echo MASTER_CREATE_TABLE; +echo $MASTER_CREATE_TABLE_OUTPUT; +eval $MASTER_CREATE_TABLE; +--enable_query_log + +INSERT INTO ta_l VALUES + (1, 11), + (2, 22), + (3, 33), + (4, 44), + (5, 55); + +--echo +--echo ##### create functions ##### +--connection master_1 +DELIMITER //; +CREATE FUNCTION `plusone`( param INT ) RETURNS INT +BEGIN + RETURN param + 1; +END // +DELIMITER ;// + +--connection child2_1 +DELIMITER //; +CREATE FUNCTION `plusone`( param INT ) RETURNS INT +BEGIN + RETURN param + 1; +END // +DELIMITER ;// + +--echo +--echo ########## spider_use_pushdown_udf=0 ########## +--connection master_1 +SET @@spider_use_pushdown_udf = 0; +--source udf_pushdown.inc + +--echo +--echo ##### reset records ##### +--connection master_1 +TRUNCATE TABLE ta_l; +INSERT INTO ta_l VALUES + (1, 11), + (2, 22), + (3, 33), + (4, 44), + (5, 55); + +--echo +--echo ########## spider_use_pushdown_udf=1 ########## +--connection master_1 +SET @@spider_use_pushdown_udf = 1; +--source udf_pushdown.inc + +--echo +--echo deinit +--disable_warnings +--connection master_1 +DROP FUNCTION `plusone`; +DROP DATABASE IF EXISTS auto_test_local; +if ($USE_CHILD_GROUP2) +{ + --connection child2_1 + SET @@global.general_log = @general_log_backup; + SET @@global.log_output = @log_output_backup; + DROP FUNCTION `plusone`; + DROP DATABASE IF EXISTS auto_test_remote; +} +--disable_query_log +--disable_result_log +--source test_deinit.inc +--enable_result_log +--enable_query_log +--enable_warnings +--echo +--echo end of test diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc index 57e3893d09b..4c908ab3157 100644 --- a/storage/spider/spd_db_mysql.cc +++ b/storage/spider/spd_db_mysql.cc @@ -6658,11 +6658,17 @@ int spider_db_mbase_util::open_item_func( separator_str_length = SPIDER_SQL_AND_LEN; } break; + case Item_func::FUNC_SP: case Item_func::UDF_FUNC: use_pushdown_udf = spider_param_use_pushdown_udf( spider->wide_handler->trx->thd, spider->share->use_pushdown_udf); if (!use_pushdown_udf) + /* + This is the default behavior because the remote nodes may deal with + the function in an unexpected way (e.g. not having the same + definition). Users can turn it on if they know what they are doing. + */ DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); if (str) { diff --git a/storage/spider/spd_param.cc b/storage/spider/spd_param.cc index ec0949b579f..b23877ca92a 100644 --- a/storage/spider/spd_param.cc +++ b/storage/spider/spd_param.cc @@ -2005,7 +2005,7 @@ static MYSQL_THDVAR_INT( "Remote server transmission existence when UDF is used at condition and \"engine_condition_pushdown=1\"", /* comment */ NULL, /* check */ NULL, /* update */ - -1, /* def */ + 0, /* def */ -1, /* min */ 1, /* max */ 0 /* blk */ |