diff options
author | Mike Griffin <mg@webmail.us> | 2022-07-29 16:07:42 +1000 |
---|---|---|
committer | Daniel Black <daniel@mariadb.org> | 2022-08-02 20:31:08 +1000 |
commit | 53c4e4d054cb5ac895535abc9815e70378dbf1b5 (patch) | |
tree | d483afede383efe63772d54b7055cb6a15c786f1 | |
parent | 5ac528a91f2d8a3b84e83b434b08fa9dc428f80c (diff) | |
download | mariadb-git-53c4e4d054cb5ac895535abc9815e70378dbf1b5.tar.gz |
MDEV-18702 mysqldump: add variable 'max-statement-time'
With a global non-default max-statement-time of a time interval that exceed
the query time mysqldump queries when doing a backup.
To solve both, add a max-statement-time option, defaulting to 0 (unlimited time).
Also like mariabackup, set the session wait_timeout=DEFAULT (28800). The
time/processing between mysqldump times isn't expected to get that
close ever, but let's adopt the standard of mariabackup as no-one has
challenged it has having a detrimental effect.
Reviewer and test case author Daniel Black
-rw-r--r-- | client/mysqldump.c | 14 | ||||
-rw-r--r-- | man/mysqldump.1 | 30 | ||||
-rw-r--r-- | mysql-test/main/mysqldump-timing.result | 71 | ||||
-rw-r--r-- | mysql-test/main/mysqldump-timing.test | 26 |
4 files changed, 141 insertions, 0 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index 6cdc3db3c08..0d9b48e585e 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -146,6 +146,7 @@ static ulonglong opt_system= 0ULL; static my_bool insert_pat_inited= 0, debug_info_flag= 0, debug_check_flag= 0, select_field_names_inited= 0; static ulong opt_max_allowed_packet, opt_net_buffer_length; +static double opt_max_statement_time= 0.0; static MYSQL mysql_connection,*mysql=0; static DYNAMIC_STRING insert_pat, select_field_names; static char *opt_password=0,*current_user=0, @@ -162,6 +163,7 @@ static my_bool server_supports_switching_charsets= TRUE; static ulong opt_compatible_mode= 0; #define MYSQL_OPT_MASTER_DATA_EFFECTIVE_SQL 1 #define MYSQL_OPT_MASTER_DATA_COMMENTED_SQL 2 +#define MYSQL_OPT_MAX_STATEMENT_TIME 0 #define MYSQL_OPT_SLAVE_DATA_EFFECTIVE_SQL 1 #define MYSQL_OPT_SLAVE_DATA_COMMENTED_SQL 2 static uint opt_mysql_port= 0, opt_master_data; @@ -464,6 +466,10 @@ static struct my_option my_long_options[] = &opt_max_allowed_packet, &opt_max_allowed_packet, 0, GET_ULONG, REQUIRED_ARG, 24*1024*1024, 4096, (longlong) 2L*1024L*1024L*1024L, MALLOC_OVERHEAD, 1024, 0}, + {"max-statement-time", MYSQL_OPT_MAX_STATEMENT_TIME, + "Max statement execution time. If unset, overrides server default with 0.", + &opt_max_statement_time, &opt_max_statement_time, 0, GET_DOUBLE, + REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"net_buffer_length", OPT_NET_BUFFER_LENGTH, "The buffer size for TCP/IP and socket communication.", &opt_net_buffer_length, &opt_net_buffer_length, 0, @@ -6816,6 +6822,7 @@ static void dynstr_realloc_checked(DYNAMIC_STRING *str, ulong additional_size) int main(int argc, char **argv) { + char query[48]; char bin_log_name[FN_REFLEN]; int exit_code; int consistent_binlog_pos= 0; @@ -6857,6 +6864,13 @@ int main(int argc, char **argv) if (!path) write_header(md_result_file, *argv); + /* Set MAX_STATEMENT_TIME to 0 unless set in client */ + my_snprintf(query, sizeof(query), "/*!100100 SET @@MAX_STATEMENT_TIME=%f */", opt_max_statement_time); + mysql_query(mysql, query); + + /* Set server side timeout between client commands to server compiled-in default */ + mysql_query(mysql, "/*!100100 SET WAIT_TIMEOUT=DEFAULT */"); + /* Check if the server support multi source */ if (mysql_get_server_version(mysql) >= 100000) { diff --git a/man/mysqldump.1 b/man/mysqldump.1 index ae6d76f24c3..140bc45c7c6 100644 --- a/man/mysqldump.1 +++ b/man/mysqldump.1 @@ -1361,6 +1361,21 @@ Sets the maximum packet length to send to or receive from server\&. .sp -1 .IP \(bu 2.3 .\} +.\" mysqldump: max-statement-time option +.\" max-statement-time option: mysqldump +\fB\-\-max\-statement\-time=\fR\fB\fIseconds\fR\fR +.sp +Sets the maximum time any statement can run before being timed out by the server. (Default value is 0 (no limit))\& +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} .\" mysqldump: net-buffer-length option .\" net-buffer-length option: mysqldump \fB\-\-net\-buffer\-length=\fR\fB\fIlength\fR\fR @@ -2619,6 +2634,21 @@ The maximum size of the buffer for client/server communication\&. The maximum is .sp -1 .IP \(bu 2.3 .\} +max_statement_time +.sp +A query that has taken more than max_statement_time seconds will be aborted and the backup will +fail\&. The argument will be treated as a decimal value with microsecond precision\&. A value +of 0 (default) means no timeout\&. The maximum timeout is 31536000 seconds\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} net_buffer_length .sp The initial size of the buffer for client/server communication\&. When creating multiple\-row diff --git a/mysql-test/main/mysqldump-timing.result b/mysql-test/main/mysqldump-timing.result new file mode 100644 index 00000000000..71120ebf2d5 --- /dev/null +++ b/mysql-test/main/mysqldump-timing.result @@ -0,0 +1,71 @@ +# +# MDEV-18702 mysqldump should use max_statement_time=0 and/or allow setting one +# +CREATE DATABASE test1; +USE test1; +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (0); +LOCK TABLE t1 WRITE; +timeout without t1 contents expected + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8mb4 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +SET @save_max_statement_time=@@max_statement_time; +SET GLOBAL max_statement_time=0.1; +UNLOCK TABLES;; +This would be a race condition otherwise, but default max_statement_time=0 makes it succeed + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8mb4 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t1` WRITE; +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +INSERT INTO `t1` VALUES (0); +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +SET GLOBAL max_statement_time=@save_max_statement_time; +DROP DATABASE test1; +# +# End of 10.3 test +# diff --git a/mysql-test/main/mysqldump-timing.test b/mysql-test/main/mysqldump-timing.test new file mode 100644 index 00000000000..25921fdd0cd --- /dev/null +++ b/mysql-test/main/mysqldump-timing.test @@ -0,0 +1,26 @@ + + +--echo # +--echo # MDEV-18702 mysqldump should use max_statement_time=0 and/or allow setting one +--echo # + +CREATE DATABASE test1; +USE test1; +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (0); +LOCK TABLE t1 WRITE; +--echo timeout without t1 contents expected +--error 2 +--exec $MYSQL_DUMP --max-statement-time=1 --skip-lock-tables --skip-comments test1 t1 +SET @save_max_statement_time=@@max_statement_time; +SET GLOBAL max_statement_time=0.1; +--send UNLOCK TABLES; +--echo This would be a race condition otherwise, but default max_statement_time=0 makes it succeed +--exec $MYSQL_DUMP --skip-lock-tables --skip-comments test1 t1 +--reap +SET GLOBAL max_statement_time=@save_max_statement_time; +DROP DATABASE test1; + +--echo # +--echo # End of 10.3 test +--echo # |