MDEV-25282 Auto-shutdown on idle when socket-activatedbb-10.6-danielblack-MDEV-25282-Auto-shutdown-on-idle-when-socket-activated

Adds max_idle_execution system variable with a UINT_MAX
default value that corresponds to the time in seconds
under which the mariadbd executable will run in an idle state
with no connections.

Under systemd socket activiation this variable will get a 10 minute
default value. This will enable a service to be activated on
connection and fall back to a shutdown state after 10 minutes of
no queries. The systemd socket activation can restart the service
on the next connection transparently.

A global variable of server_last_activity is updated on
accepted connections (where max_idle_execution < UINT_MAX)
and when the connection count (standard or extra) is down
to <= 1 to keep the number of updates on a single variable
low (an not create another performance MDEV-21212 problem).

When the main accept loop times out on the max_idle_execution
seconds, and then the server_last_activity is checked along
with if current connection count (standard + extra) is 0
(in case a recently started connection hasn't finished
a query).

To make this neater, in main accept loop moved code to
handle_new_socket_connection that encompases accepting a connection
and the timeout mechanism has been separated too.

Changed when looping though possible connections, loop until
the end of the connection list and hereby assume two connection can
occur on the same poll/select call and both will be accepted.

12 files changed, 196 insertions, 49 deletions

diff --git a/include/my_service_manager.h b/include/my_service_manager.h
index 3eff1253f20..d86074e58a1 100644
--- a/include/my_service_manager.h
+++ b/include/my_service_manager.h
@@ -33,11 +33,12 @@
   sd_notifyf(0, "STATUS=" FMTSTR "\nEXTEND_TIMEOUT_USEC=%u\n", ##__VA_ARGS__, INTERVAL * 1000000)
 /* sd_listen_fds_with_names added v227 however RHEL/Centos7 has v219, fallback to sd_listen_fds */
 #ifndef HAVE_SYSTEMD_SD_LISTEN_FDS_WITH_NAMES
-#define sd_listen_fds_with_names(FD, NAMES) sd_listen_fds(FD)
+#define sd_listen_fds_with_names(UNSET, NAMES) sd_listen_fds(UNSET)
 #endif
 
 #else
-#define sd_listen_fds_with_names(FD, NAMES) (0)
+#define sd_listen_fds(UNSET) (0)
+#define sd_listen_fds_with_names(UNSET, NAMES) (0)
 #define sd_is_socket_unix(FD, TYPE, LISTENING, PATH, SIZE) (0)
 #define sd_is_socket_inet(FD, FAMILY, TYPE, LISTENING, PORT) (0)
 #define SD_LISTEN_FDS_START (0)
diff --git a/mysql-test/main/max_idle_execution.cnf b/mysql-test/main/max_idle_execution.cnf
new file mode 100644
index 00000000000..15cd3519776
--- /dev/null
+++ b/mysql-test/main/max_idle_execution.cnf
@@ -0,0 +1,9 @@
+!include include/default_my.cnf
+
+[mysqld.1]
+extra-port=        @ENV.MASTER_EXTRA_PORT
+extra-max-connections=1
+max-idle-execution=10
+
+[ENV]
+MASTER_EXTRA_PORT= @OPT.port
diff --git a/mysql-test/main/max_idle_execution.result b/mysql-test/main/max_idle_execution.result
new file mode 100644
index 00000000000..d54702bcfe2
--- /dev/null
+++ b/mysql-test/main/max_idle_execution.result
@@ -0,0 +1,27 @@
+disconnect default;
+'allow server to time out in 10 seconds'
+'should have timed out'
+connect(localhost,root,,test,,);
+connect fail_me,localhost,root;
+connect con0,localhost,root,,test;
+SELECT VARIABLE_VALUE AS THREAD_CONNECTED FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='THREADS_CONNECTED';
+THREAD_CONNECTED
+1
+SELECT 'we are back';
+we are back
+we are back
+SELECT VARIABLE_VALUE < 10 AS UPTIME_LESS_THAN_10_SECONDS FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='UPTIME';
+UPTIME_LESS_THAN_10_SECONDS
+1
+SELECT 'still here because the connection is open, but disconnecting now';
+still here because the connection is open, but disconnecting now
+still here because the connection is open, but disconnecting now
+disconnect con0;
+connect con2,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,;
+SELECT "extra connection just created: still here, only 5 seconds since last query";
+extra connection just created: still here, only 5 seconds since last query
+extra connection just created: still here, only 5 seconds since last query
+connect  default,localhost,root,,test,,;
+SELECT 'active extra connection kept the server up';
+active extra connection kept the server up
+active extra connection kept the server up
diff --git a/mysql-test/main/max_idle_execution.test b/mysql-test/main/max_idle_execution.test
new file mode 100644
index 00000000000..e5a49587361
--- /dev/null
+++ b/mysql-test/main/max_idle_execution.test
@@ -0,0 +1,38 @@
+--source include/not_embedded.inc
+
+--let $_server_id= `SELECT @@server_id`
+--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect
+--exec echo "wait" > $_expect_file_name
+disable_reconnect;
+disconnect default;
+
+--echo 'allow server to time out in 10 seconds'
+
+--sleep 10
+
+--echo 'should have timed out'
+# mtr weirdness
+--replace_regex /[\/0-9]+.*//
+--error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,201
+--connect fail_me,localhost,root
+
+--exec echo "restart" > $_expect_file_name
+--sleep 5
+--connect con0,localhost,root,,test
+
+--source include/wait_until_connected_again.inc
+SELECT VARIABLE_VALUE AS THREAD_CONNECTED FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='THREADS_CONNECTED';
+SELECT 'we are back';
+SELECT VARIABLE_VALUE < 10 AS UPTIME_LESS_THAN_10_SECONDS FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='UPTIME';
+
+--sleep 13
+SELECT 'still here because the connection is open, but disconnecting now';
+disconnect con0;
+--sleep 5
+
+--connect con2,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,
+SELECT "extra connection just created: still here, only 5 seconds since last query";
+
+--sleep 13 
+connect  default,localhost,root,,test,,;
+SELECT 'active extra connection kept the server up';
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 5d014240faa..319ba44beea 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -576,6 +576,10 @@ The following specify which files/extra groups are read (specified before remain
  --max-error-count=# Max number of errors/warnings to store for a statement
  --max-heap-table-size=# 
  Don't allow creation of heap tables bigger than this
+ --max-idle-execution=# 
+ If no new connections or running queries within this time
+ (in seconds) shutdown the server (Automatically
+ configured unless set explicitly)
  --max-join-size=#   Joins that are probably going to read more than
  max_join_size records return an error
  --max-length-for-sort-data=# 
@@ -1615,6 +1619,7 @@ max-delayed-threads 20
 max-digest-length 1024
 max-error-count 64
 max-heap-table-size 16777216
+max-idle-execution 18446744073709551615
 max-join-size 18446744073709551615
 max-length-for-sort-data 1024
 max-password-errors 18446744073709551615
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 bb3378139f2..bc05a72a3d2 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2012,6 +2012,16 @@ NUMERIC_BLOCK_SIZE	1024
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	MAX_IDLE_EXECUTION
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	BIGINT UNSIGNED
+VARIABLE_COMMENT	If no new connections or running queries within this time (in seconds) shutdown the server
+NUMERIC_MIN_VALUE	10
+NUMERIC_MAX_VALUE	4294967295
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	MAX_INSERT_DELAYED_THREADS
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	BIGINT UNSIGNED
diff --git a/sql/handle_connections_win.cc b/sql/handle_connections_win.cc
index ffacfcab88f..946f069eeb1 100644
--- a/sql/handle_connections_win.cc
+++ b/sql/handle_connections_win.cc
@@ -593,7 +593,7 @@ void network_init_win()
   }
 }
 
-void handle_connections_win()
+void handle_connections_win(Atomic_counter<uint> *connection_count)
 {
   int n_waits;
 
@@ -631,12 +631,26 @@ void handle_connections_win()
   {
     DBUG_ASSERT(wait_events.size() <= MAXIMUM_WAIT_OBJECTS);
     DWORD idx = WaitForMultipleObjects((DWORD)wait_events.size(),
-                                       wait_events.data(), FALSE, INFINITE);
-    DBUG_ASSERT((int)idx >= 0 && (int)idx < (int)wait_events.size());
+                                       wait_events.data(), FALSE,
+                                       max_idle_execution < UINT_MAX ?
+                                         max_idle_execution * 1000 : INFINITE);
+    DBUG_ASSERT(idx == WAIT_TIMEOUT ||
+                  ((int)idx >= 0 && (int)idx < (int)wait_events.size()));
 
     if (idx == SHUTDOWN_IDX)
       break;
 
+    if (idx == WAIT_TIMEOUT)
+    {
+      if (*connection_count == 0 &&
+          microsecond_interval_timer() > (server_last_activity + max_idle_execution * 1000000))
+      {
+        sql_print_information("max_idle_execution time reached starting shutdown");
+        break;
+      }
+      continue;
+    }
+
     all_listeners[idx - LISTENER_START_IDX]->completion_callback();
   }
 
diff --git a/sql/handle_connections_win.h b/sql/handle_connections_win.h
index bf66c081473..388ead3e743 100644
--- a/sql/handle_connections_win.h
+++ b/sql/handle_connections_win.h
@@ -17,5 +17,5 @@
   Handles incoming socket and pipe connections, on Windows.
   Creates new (THD) connections..
 */
-extern void handle_connections_win();
+extern void handle_connections_win(Atomic_counter<uint> *connection_count);
 extern void network_init_win();
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 249dde9177c..0088706b8e8 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -474,7 +474,8 @@ ulong malloc_calls;
 ulong specialflag=0;
 ulong binlog_cache_use= 0, binlog_cache_disk_use= 0;
 ulong binlog_stmt_cache_use= 0, binlog_stmt_cache_disk_use= 0;
-ulong max_connections, max_connect_errors;
+ulong max_connections, max_connect_errors, max_idle_execution;
+ulonglong server_last_activity;
 uint max_password_errors;
 ulong extra_max_connections;
 uint max_digest_length= 0;
@@ -4093,6 +4094,13 @@ static int init_common_variables()
     SYSVAR_AUTOSIZE(back_log, MY_MIN(900, (50 + max_connections / 5)));
   }
 
+  /*
+    max_idle_execution, defaults to 10mins under systemd socket activation,
+    otherwise 136 years or so.
+  */
+  if (IS_SYSVAR_AUTOSIZE(&max_idle_execution))
+    SYSVAR_AUTOSIZE(max_idle_execution, sd_listen_fds(0) ? 6000 : UINT_MAX);
+
   unireg_init(opt_specialflag); /* Set up extern variabels */
   if (!(my_default_lc_messages=
         my_locale_by_name(lc_messages)))
@@ -5809,7 +5817,7 @@ int mysqld_main(int argc, char **argv)
   start_memory_used= global_status_var.global_memory_used;
 
 #ifdef _WIN32
-  handle_connections_win();
+  handle_connections_win(&connection_count);
 #else
   handle_connections_sockets();
 
@@ -6064,17 +6072,60 @@ static void set_non_blocking_if_supported(MYSQL_SOCKET sock)
 }
 
 
-void handle_connections_sockets()
+static void handle_socket_timeout()
+{
+  if (connection_count == 0 && extra_connection_count == 0 &&
+      microsecond_interval_timer() > (server_last_activity + max_idle_execution * 1000000))
+  {
+    sql_print_information("max_idle_execution time reached starting shutdown");
+    abort_loop= 1;
+  }
+}
+
+
+static void handle_new_socket_connection(MYSQL_SOCKET sock)
 {
-  MYSQL_SOCKET sock= mysql_socket_invalid();
-  uint error_count=0;
   struct sockaddr_storage cAddr;
+  uint error_count= 0;
+
+  for (uint retry= 0; retry < MAX_ACCEPT_RETRY; retry++)
+  {
+    size_socket length= sizeof(struct sockaddr_storage);
+    MYSQL_SOCKET new_sock;
+
+    new_sock= mysql_socket_accept(key_socket_client_connection, sock,
+                                  (struct sockaddr *)(&cAddr),
+                                  &length);
+    if (mysql_socket_getfd(new_sock) != INVALID_SOCKET)
+      handle_accepted_socket(new_sock, sock);
+    else if (socket_errno != SOCKET_EINTR && socket_errno != SOCKET_EAGAIN)
+    {
+      /*
+        accept(2) failed on the listening port.
+        There is not much details to report about the client,
+        increment the server global status variable.
+      */
+      statistic_increment(connection_errors_accept, &LOCK_status);
+      if ((error_count++ & 255) == 0) // This can happen often
+        sql_perror("Error in accept");
+      if (socket_errno == SOCKET_ENFILE || socket_errno == SOCKET_EMFILE)
+        sleep(1); // Give other threads some time
+      break;
+    }
+  }
+}
+
+
+void handle_connections_sockets()
+{
+  MYSQL_SOCKET sock;
   int retval;
 #ifdef HAVE_POLL
   // for ip_sock, unix_sock and extra_ip_sock
   Dynamic_array<struct pollfd> fds(PSI_INSTRUMENT_MEM);
 #else
   fd_set readFDs,clientFDs;
+  struct timespec timeout;
 #endif
 
   DBUG_ENTER("handle_connections_sockets");
@@ -6099,6 +6150,7 @@ void handle_connections_sockets()
   }
 #endif
 
+  server_last_activity= microsecond_interval_timer();
   sd_notify(0, "READY=1\n"
             "STATUS=Taking your SQL requests now...\n");
 
@@ -6106,10 +6158,12 @@ void handle_connections_sockets()
   while (!abort_loop)
   {
 #ifdef HAVE_POLL
-    retval= poll(fds.get_pos(0), fds.size(), -1);
+    /* poll timeout in milliseconds */
+    retval= poll(fds.get_pos(0), fds.size(), max_idle_execution * 1000);
 #else
+    timeout= { max_idle_execution, 0};
     readFDs=clientFDs;
-    retval= select(FD_SETSIZE, &readFDs, NULL, NULL, NULL);
+    retval= select(FD_SETSIZE, &readFDs, NULL, NULL, &timeout);
 #endif
 
     if (retval < 0)
@@ -6132,50 +6186,27 @@ void handle_connections_sockets()
       break;
 
     /* Is this a new connection request ? */
-#ifdef HAVE_POLL
-    for (size_t i= 0; i < fds.size(); ++i)
+    sock= mysql_socket_invalid();
+    for (size_t i= 0; i < listen_sockets.size(); i++)
     {
+#ifdef HAVE_POLL
       if (fds.at(i).revents & POLLIN)
-      {
-        sock= listen_sockets.at(i);
-        break;
-      }
-    }
 #else  // HAVE_POLL
-    for (size_t i=0; i < listen_sockets.size(); i++)
-    {
       if (FD_ISSET(mysql_socket_getfd(listen_sockets.at(i)), &readFDs))
+#endif // HAVE_POLL
       {
         sock= listen_sockets.at(i);
-        break;
+        handle_new_socket_connection(sock);
       }
     }
-#endif // HAVE_POLL
-
-    for (uint retry=0; retry < MAX_ACCEPT_RETRY; retry++)
+    /* timeout */
+    if (mysql_socket_getfd(sock) == INVALID_SOCKET)
     {
-      size_socket length= sizeof(struct sockaddr_storage);
-      MYSQL_SOCKET new_sock;
-
-      new_sock= mysql_socket_accept(key_socket_client_connection, sock,
-                                    (struct sockaddr *)(&cAddr),
-                                    &length);
-      if (mysql_socket_getfd(new_sock) != INVALID_SOCKET)
-        handle_accepted_socket(new_sock, sock);
-      else if (socket_errno != SOCKET_EINTR && socket_errno != SOCKET_EAGAIN)
-      {
-        /*
-          accept(2) failed on the listening port.
-          There is not much details to report about the client,
-          increment the server global status variable.
-        */
-        statistic_increment(connection_errors_accept, &LOCK_status);
-        if ((error_count++ & 255) == 0) // This can happen often
-          sql_perror("Error in accept");
-        if (socket_errno == SOCKET_ENFILE || socket_errno == SOCKET_EMFILE)
-          sleep(1); // Give other threads some time
-        break;
-      }
+      handle_socket_timeout();
+    }
+    else if (max_idle_execution <  UINT_MAX)
+    {
+      server_last_activity= microsecond_interval_timer();
     }
   }
   sd_notify(0, "STOPPING=1\n"
diff --git a/sql/mysqld.h b/sql/mysqld.h
index 5356fd967c7..cfbfe31e123 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -225,7 +225,7 @@ extern ulong query_cache_min_res_unit;
 extern ulong slow_launch_threads, slow_launch_time;
 extern MYSQL_PLUGIN_IMPORT ulong max_connections;
 extern uint max_digest_length;
-extern ulong max_connect_errors, connect_timeout;
+extern ulong max_connect_errors, connect_timeout, max_idle_execution;
 extern uint max_password_errors;
 extern my_bool slave_allow_batching;
 extern my_bool allow_slave_start;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 0f85da68c8e..124637178d2 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -195,6 +195,7 @@ enum enum_binlog_row_image {
 extern char internal_table_name[2];
 extern char empty_c_string[1];
 extern MYSQL_PLUGIN_IMPORT const char **errmesg;
+extern ulonglong server_last_activity;
 
 extern "C" LEX_STRING * thd_query_string (MYSQL_THD thd);
 extern "C" unsigned long long thd_query_id(const MYSQL_THD thd);
@@ -3949,6 +3950,9 @@ public:
     set_time_for_next_stage();
     if (utime_after_query >= utime_after_lock + variables.long_query_time)
       server_status|= SERVER_QUERY_WAS_SLOW;
+    /* If we're tracking idle execution, and we're down to the last connection */
+    if (max_idle_execution <  UINT_MAX && *scheduler->connection_count <= 1)
+      server_last_activity= utime_after_query;
   }
   inline ulonglong found_rows(void)
   {
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index fa4e769232d..3faadb08ee5 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -1724,6 +1724,14 @@ Sys_max_connect_errors(
        VALID_RANGE(1, UINT_MAX), DEFAULT(MAX_CONNECT_ERRORS),
        BLOCK_SIZE(1));
 
+static Sys_var_ulong Sys_max_idle_execution(
+       "max_idle_execution",
+       "If no new connections or running queries within this time (in seconds) "
+       "shutdown the server",
+       AUTO_SET GLOBAL_VAR(max_idle_execution), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(10, UINT_MAX), DEFAULT(UINT_MAX),
+       BLOCK_SIZE(1));
+
 static Sys_var_on_access_global<Sys_var_uint,
                                 PRIV_SET_SYSTEM_GLOBAL_VAR_MAX_PASSWORD_ERRORS>
 Sys_max_password_errors(