Adjust table_open_cache to avoid getting error 24 (too many open files)

MDEV--15609 engines/funcs.crash_manytables_number crashes with error 24
           (too many open files)
MDEV-10286  Adjustment of table_open_cache according to system limits
            does not work when open-files-limit option is provided

Fixed by adjusting tc_size downwards if there is not enough file
descriptors to use.

Other changes:
- Ensure that there is 30 (was 10) extra file descriptors for other usage
- Decrease TABLE_OPEN_CACHE_MIN to 200 as it's better to have a smaller
  table cache than getting error 24
- Increase minimum of max_connections and table_open_cache from 1 to 10
  as 1 is not usable for any real application, only for testing.

29 files changed, 193 insertions, 85 deletions

diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 2581eb7b503..313b62ad7cc 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -4458,6 +4458,7 @@ sub extract_warning_lines ($$) {
      qr|SSL error: Failed to set ciphers to use|,
      qr/Plugin 'InnoDB' will be forced to shutdown/,
      qr|Could not increase number of max_open_files to more than|,
+     qr|Changed limits: max_open_files|,
      qr/InnoDB: Error table encrypted but encryption service not available.*/,
      qr/InnoDB: Could not find a valid tablespace file for*/,
      qr/InnoDB: Tablespace open failed for*/,
diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result
index 315aea0ef46..cb66ff483e1 100644
--- a/mysql-test/r/connect.result
+++ b/mysql-test/r/connect.result
@@ -147,7 +147,7 @@ drop table t1;
 CREATE USER mysqltest_u1@localhost;
 GRANT USAGE ON *.* TO mysqltest_u1@localhost;
 
-SET GLOBAL max_connections = 3;
+SET GLOBAL max_connections = 10;
 SET GLOBAL event_scheduler = ON;
 
 # -- Waiting for Event Scheduler to start...
@@ -191,6 +191,13 @@ event_scheduler
 mysqltest_u1
 mysqltest_u1
 mysqltest_u1
+mysqltest_u1
+mysqltest_u1
+mysqltest_u1
+mysqltest_u1
+mysqltest_u1
+mysqltest_u1
+mysqltest_u1
 root
 
 # -- Resetting variables...
diff --git a/mysql-test/r/mdev375.result b/mysql-test/r/mdev375.result
index 32580804686..b3b83af0988 100644
--- a/mysql-test/r/mdev375.result
+++ b/mysql-test/r/mdev375.result
@@ -1,5 +1,5 @@
 SET GLOBAL log_warnings=4;
-SET GLOBAL max_connections=2;
+SET GLOBAL max_connections=10;
 SELECT 1;
 1
 1
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index e114f424ede..0f865c151fa 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1906,9 +1906,9 @@ DROP TABLE t1;
 #
 # BUG#48438 - crash with error in unioned query against merge table and view...
 #
-SET GLOBAL table_open_cache=3;
+SET GLOBAL table_open_cache=10;
 CREATE TABLE t1(a INT);
-SELECT 1 FROM t1 AS a1, t1 AS a2, t1 AS a3, t1 AS a4 FOR UPDATE;
+SELECT 1 FROM t1 AS a1, t1 AS a2, t1 AS a3, t1 AS a4, t1 AS a5, t1 AS a6, t1 AS a7, t1 AS a8, t1 AS a9, t1 AS a10, t1 AS a11 FOR UPDATE;
 1
 SELECT TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
diff --git a/mysql-test/r/mysqld--help,win.rdiff b/mysql-test/r/mysqld--help,win.rdiff
index 06116a2e630..6e18d16feaf 100644
--- a/mysql-test/r/mysqld--help,win.rdiff
+++ b/mysql-test/r/mysqld--help,win.rdiff
@@ -121,10 +121,10 @@
  sync-relay-log 10000
  sync-relay-log-info 10000
  sysdate-is-now FALSE
--table-cache 431
+-table-cache 421
 +table-cache 2000
  table-definition-cache 400
--table-open-cache 431
+-table-open-cache 421
 +table-open-cache 2000
  tc-heuristic-recover OFF
  thread-cache-size 0
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index e84921bfa0a..7b78eae185e 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -1438,9 +1438,9 @@ sync-master-info 10000
 sync-relay-log 10000
 sync-relay-log-info 10000
 sysdate-is-now FALSE
-table-cache 431
+table-cache 421
 table-definition-cache 400
-table-open-cache 431
+table-open-cache 421
 tc-heuristic-recover OFF
 thread-cache-size 0
 thread-pool-idle-timeout 60
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 57d704c36be..ad5bddda035 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -7946,7 +7946,7 @@ CLOSE cur1;
 end|
 set @tmp_toc= @@table_open_cache;
 set @tmp_tdc= @@table_definition_cache;
-set global table_open_cache=1;
+set global table_open_cache=10;
 set global table_definition_cache=1;
 Warnings:
 Warning	1292	Truncated incorrect table_definition_cache value: '1'
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index b621fc13ee4..a01c8f48a4d 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -748,10 +748,10 @@ Warnings:
 Warning	1292	Truncated incorrect table_open_cache value: '-1'
 SHOW VARIABLES LIKE 'table_open_cache';
 Variable_name	Value
-table_open_cache	1
+table_open_cache	10
 SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
 VARIABLE_NAME	VARIABLE_VALUE
-TABLE_OPEN_CACHE	1
+TABLE_OPEN_CACHE	10
 SET GLOBAL table_open_cache=DEFAULT;
 set character_set_results=NULL;
 select ifnull(@@character_set_results,"really null");
diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_max_con.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_max_con.result
index 31e4bc9f843..69e1ddb29f5 100644
--- a/mysql-test/suite/perfschema/r/hostcache_ipv4_max_con.result
+++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_max_con.result
@@ -525,7 +525,7 @@ COUNT_UNKNOWN_ERRORS	0
 FIRST_ERROR_SEEN	set
 LAST_ERROR_SEEN	set
 set global max_user_connections = 0;
-set global max_connections = 3;
+set global max_connections = 10;
 flush user_resources;
 select "Con4a is alive";
 Con4a is alive
diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_max_con.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_max_con.result
index 4416b78d009..56eb6fe6ac6 100644
--- a/mysql-test/suite/perfschema/r/hostcache_ipv6_max_con.result
+++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_max_con.result
@@ -525,7 +525,7 @@ COUNT_UNKNOWN_ERRORS	0
 FIRST_ERROR_SEEN	set
 LAST_ERROR_SEEN	set
 set global max_user_connections = 0;
-set global max_connections = 3;
+set global max_connections = 10;
 flush user_resources;
 select "Con4a is alive";
 Con4a is alive
diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_max_con.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_max_con.test
index 3bf5ef3b68d..a76e04ea35e 100644
--- a/mysql-test/suite/perfschema/t/hostcache_ipv4_max_con.test
+++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_max_con.test
@@ -176,9 +176,17 @@ disconnect con4c;
 --source ../include/wait_for_pfs_thread_count.inc
 
 set global max_user_connections = 0;
-set global max_connections = 3;
+set global max_connections = 10;
 flush user_resources;
 
+connect (tmp_con1,localhost,root,,);
+connect (tmp_con2,localhost,root,,);
+connect (tmp_con3,localhost,root,,);
+connect (tmp_con4,localhost,root,,);
+connect (tmp_con5,localhost,root,,);
+connect (tmp_con6,localhost,root,,);
+connect (tmp_con7,localhost,root,,);
+
 connect (con5a,"127.0.0.1",quota,,test,$MASTER_MYPORT,);
 select "Con4a is alive";
 select current_user();
@@ -233,6 +241,13 @@ connect (con5d,"127.0.0.1",quota,,test,$MASTER_MYPORT,);
 
 disconnect con5a;
 disconnect con5b;
+--disconnect tmp_con1
+--disconnect tmp_con2
+--disconnect tmp_con3
+--disconnect tmp_con4
+--disconnect tmp_con5
+--disconnect tmp_con6
+--disconnect tmp_con7
 
 # Wait for all disconnects
 --source ../include/wait_for_pfs_thread_count.inc
diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_max_con.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_max_con.test
index 0ced79544a3..96a5d10d6e1 100644
--- a/mysql-test/suite/perfschema/t/hostcache_ipv6_max_con.test
+++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_max_con.test
@@ -177,9 +177,17 @@ disconnect con4c;
 --source ../include/wait_for_pfs_thread_count.inc
 
 set global max_user_connections = 0;
-set global max_connections = 3;
+set global max_connections = 10;
 flush user_resources;
 
+connect (tmp_con1,localhost,root,,);
+connect (tmp_con2,localhost,root,,);
+connect (tmp_con3,localhost,root,,);
+connect (tmp_con4,localhost,root,,);
+connect (tmp_con5,localhost,root,,);
+connect (tmp_con6,localhost,root,,);
+connect (tmp_con7,localhost,root,,);
+
 connect (con5a,"::1",quota,,test,$MASTER_MYPORT,);
 select "Con4a is alive";
 select current_user();
@@ -215,6 +223,13 @@ connect (con5d,"::1",quota,,test,$MASTER_MYPORT,);
 
 disconnect con5a;
 disconnect con5b;
+--disconnect tmp_con1
+--disconnect tmp_con2
+--disconnect tmp_con3
+--disconnect tmp_con4
+--disconnect tmp_con5
+--disconnect tmp_con6
+--disconnect tmp_con7
 
 # Wait for all disconnects
 --source ../include/wait_for_pfs_thread_count.inc
diff --git a/mysql-test/suite/sys_vars/r/max_connections_basic.result b/mysql-test/suite/sys_vars/r/max_connections_basic.result
index 708342b016d..f9b2c034400 100644
--- a/mysql-test/suite/sys_vars/r/max_connections_basic.result
+++ b/mysql-test/suite/sys_vars/r/max_connections_basic.result
@@ -27,20 +27,24 @@ SELECT @@global.max_connections;
 @@global.max_connections
 65536
 SET @@global.max_connections = 1;
+Warnings:
+Warning	1292	Truncated incorrect max_connections value: '1'
 SELECT @@global.max_connections;
 @@global.max_connections
-1
+10
 SET @@global.max_connections = 2;
+Warnings:
+Warning	1292	Truncated incorrect max_connections value: '2'
 SELECT @@global.max_connections;
 @@global.max_connections
-2
+10
 '#--------------------FN_DYNVARS_074_04-------------------------#'
 SET @@global.max_connections = -1;
 Warnings:
 Warning	1292	Truncated incorrect max_connections value: '-1'
 SELECT @@global.max_connections;
 @@global.max_connections
-1
+10
 SET @@global.max_connections = 100000000000;
 Warnings:
 Warning	1292	Truncated incorrect max_connections value: '100000000000'
@@ -57,13 +61,13 @@ Warnings:
 Warning	1292	Truncated incorrect max_connections value: '-1024'
 SELECT @@global.max_connections;
 @@global.max_connections
-1
+10
 SET @@global.max_connections = 0;
 Warnings:
 Warning	1292	Truncated incorrect max_connections value: '0'
 SELECT @@global.max_connections;
 @@global.max_connections
-1
+10
 SET @@global.max_connections = 100001;
 Warnings:
 Warning	1292	Truncated incorrect max_connections value: '100001'
@@ -98,15 +102,17 @@ WHERE VARIABLE_NAME='max_connections';
 1
 '#---------------------FN_DYNVARS_074_07----------------------#'
 SET @@global.max_connections = TRUE;
+Warnings:
+Warning	1292	Truncated incorrect max_connections value: '1'
 SELECT @@global.max_connections;
 @@global.max_connections
-1
+10
 SET @@global.max_connections = FALSE;
 Warnings:
 Warning	1292	Truncated incorrect max_connections value: '0'
 SELECT @@global.max_connections;
 @@global.max_connections
-1
+10
 '#---------------------FN_DYNVARS_074_08----------------------#'
 SET @@global.max_connections = 5000;
 SELECT @@max_connections = @@global.max_connections;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index f851dfcc5a4..6a84765cf60 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -1837,7 +1837,7 @@ DEFAULT_VALUE	151
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	The number of simultaneous clients allowed
-NUMERIC_MIN_VALUE	1
+NUMERIC_MIN_VALUE	10
 NUMERIC_MAX_VALUE	100000
 NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
@@ -3847,13 +3847,13 @@ READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	TABLE_OPEN_CACHE
 SESSION_VALUE	NULL
-GLOBAL_VALUE	2000
-GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+GLOBAL_VALUE	100
+GLOBAL_VALUE_ORIGIN	CONFIG
 DEFAULT_VALUE	2000
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	The number of cached open tables
-NUMERIC_MIN_VALUE	1
+NUMERIC_MIN_VALUE	10
 NUMERIC_MAX_VALUE	1048576
 NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
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 5bc6a6e1743..413ef1ca58d 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2019,7 +2019,7 @@ DEFAULT_VALUE	151
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	The number of simultaneous clients allowed
-NUMERIC_MIN_VALUE	1
+NUMERIC_MIN_VALUE	10
 NUMERIC_MAX_VALUE	100000
 NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
@@ -4547,13 +4547,13 @@ READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	TABLE_OPEN_CACHE
 SESSION_VALUE	NULL
-GLOBAL_VALUE	2000
-GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+GLOBAL_VALUE	100
+GLOBAL_VALUE_ORIGIN	CONFIG
 DEFAULT_VALUE	2000
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	The number of cached open tables
-NUMERIC_MIN_VALUE	1
+NUMERIC_MIN_VALUE	10
 NUMERIC_MAX_VALUE	1048576
 NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
diff --git a/mysql-test/suite/sys_vars/r/table_open_cache_basic.result b/mysql-test/suite/sys_vars/r/table_open_cache_basic.result
index 4c635783753..bc373003e1d 100644
--- a/mysql-test/suite/sys_vars/r/table_open_cache_basic.result
+++ b/mysql-test/suite/sys_vars/r/table_open_cache_basic.result
@@ -1,7 +1,7 @@
 SET @start_value = @@global.table_open_cache ;
 SELECT @start_value;
 @start_value
-2000
+421
 '#--------------------FN_DYNVARS_001_01------------------------#'
 SET @@global.table_open_cache  = 99;
 SET @@global.table_open_cache  = DeFAULT;
@@ -15,13 +15,17 @@ SELECT @@global.table_open_cache  = 400;
 0
 '#--------------------FN_DYNVARS_001_03------------------------#'
 SET @@global.table_open_cache  = 8;
+Warnings:
+Warning	1292	Truncated incorrect table_open_cache value: '8'
 SELECT @@global.table_open_cache ;
 @@global.table_open_cache
-8
+10
 SET @@global.table_open_cache  = 1;
+Warnings:
+Warning	1292	Truncated incorrect table_open_cache value: '1'
 SELECT @@global.table_open_cache ;
 @@global.table_open_cache
-1
+10
 SET @@global.table_open_cache  = 1073741824;
 Warnings:
 Warning	1292	Truncated incorrect table_open_cache value: '1073741824'
@@ -42,7 +46,7 @@ Warnings:
 Warning	1292	Truncated incorrect table_open_cache value: '-1'
 SELECT @@global.table_open_cache  ;
 @@global.table_open_cache
-1
+10
 SET @@global.table_open_cache = 100000000000;
 Warnings:
 Warning	1292	Truncated incorrect table_open_cache value: '100000000000'
@@ -54,13 +58,13 @@ Warnings:
 Warning	1292	Truncated incorrect table_open_cache value: '-1024'
 SELECT @@global.table_open_cache ;
 @@global.table_open_cache
-1
+10
 SET @@global.table_open_cache = 0;
 Warnings:
 Warning	1292	Truncated incorrect table_open_cache value: '0'
 SELECT @@global.table_open_cache ;
 @@global.table_open_cache
-1
+10
 SET @@global.table_open_cache = 10000.01;
 ERROR 42000: Incorrect argument type to variable 'table_open_cache'
 SET @@global.table_open_cache = ON;
@@ -78,15 +82,17 @@ SELECT @@global.table_open_cache = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL
 1
 '#---------------------FN_DYNVARS_001_09----------------------#'
 SET @@global.table_open_cache = TRUE;
+Warnings:
+Warning	1292	Truncated incorrect table_open_cache value: '1'
 SELECT @@global.table_open_cache ;
 @@global.table_open_cache
-1
+10
 SET @@global.table_open_cache = FALSE;
 Warnings:
 Warning	1292	Truncated incorrect table_open_cache value: '0'
 SELECT @@global.table_open_cache ;
 @@global.table_open_cache
-1
+10
 '#---------------------FN_DYNVARS_001_10----------------------#'
 SET @@global.table_open_cache = 10;
 SELECT @@table_open_cache = @@global.table_open_cache ;
@@ -104,4 +110,4 @@ ERROR 42S22: Unknown column 'table_open_cache' in 'field list'
 SET @@global.table_open_cache = @start_value;
 SELECT @@global.table_open_cache ;
 @@global.table_open_cache
-2000
+421
diff --git a/mysql-test/suite/sys_vars/t/sysvars_innodb.opt b/mysql-test/suite/sys_vars/t/sysvars_innodb.opt
index 462e83d98b6..c3eec59bc17 100644
--- a/mysql-test/suite/sys_vars/t/sysvars_innodb.opt
+++ b/mysql-test/suite/sys_vars/t/sysvars_innodb.opt
@@ -1 +1,2 @@
 --loose-innodb-flush-log-at-timeout=3
+--table_open_cache=200
diff --git a/mysql-test/suite/sys_vars/t/sysvars_server_embedded.opt b/mysql-test/suite/sys_vars/t/sysvars_server_embedded.opt
new file mode 100644
index 00000000000..c3f0d3bd51f
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/sysvars_server_embedded.opt
@@ -0,0 +1 @@
+--table_open_cache=100
diff --git a/mysql-test/suite/sys_vars/t/sysvars_server_notembedded.opt b/mysql-test/suite/sys_vars/t/sysvars_server_notembedded.opt
new file mode 100644
index 00000000000..c3f0d3bd51f
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/sysvars_server_notembedded.opt
@@ -0,0 +1 @@
+--table_open_cache=100
diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test
index 20989e70ef9..c2d44c08e22 100644
--- a/mysql-test/t/connect.test
+++ b/mysql-test/t/connect.test
@@ -143,9 +143,18 @@ GRANT USAGE ON *.* TO mysqltest_u1@localhost;
 
 --echo
 let $saved_max_connections = `SELECT @@global.max_connections`;
-SET GLOBAL max_connections = 3;
+SET GLOBAL max_connections = 10;
 SET GLOBAL event_scheduler = ON;
 
+connect (tmp_con1,localhost,mysqltest_u1,,);
+connect (tmp_con2,localhost,mysqltest_u1,,);
+connect (tmp_con3,localhost,mysqltest_u1,,);
+connect (tmp_con4,localhost,mysqltest_u1,,);
+connect (tmp_con5,localhost,mysqltest_u1,,);
+connect (tmp_con6,localhost,mysqltest_u1,,);
+connect (tmp_con7,localhost,mysqltest_u1,,);
+connection default;
+
 --echo
 --echo # -- Waiting for Event Scheduler to start...
 let $wait_condition =
@@ -230,6 +239,13 @@ let $wait_condition =
 --disconnect con_2
 --disconnect con_3
 --disconnect con_super_1
+--disconnect tmp_con1
+--disconnect tmp_con2
+--disconnect tmp_con3
+--disconnect tmp_con4
+--disconnect tmp_con5
+--disconnect tmp_con6
+--disconnect tmp_con7
 
 --echo
 --echo # -- Restoring default connection...
diff --git a/mysql-test/t/mdev375.test b/mysql-test/t/mdev375.test
index fe259b37808..7380e7a32e1 100644
--- a/mysql-test/t/mdev375.test
+++ b/mysql-test/t/mdev375.test
@@ -4,7 +4,16 @@
 --source include/not_embedded.inc
 
 SET GLOBAL log_warnings=4;
-SET GLOBAL max_connections=2;
+SET GLOBAL max_connections=10;
+
+--connect (tmp_con1,localhost,root,,)
+--connect (tmp_con2,localhost,root,,)
+--connect (tmp_con3,localhost,root,,)
+--connect (tmp_con4,localhost,root,,)
+--connect (tmp_con5,localhost,root,,)
+--connect (tmp_con6,localhost,root,,)
+--connect (tmp_con7,localhost,root,,)
+--connect (tmp_con8,localhost,root,,)
 
 --connect (con1,localhost,root,,)
 SELECT 1;
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
index 62260ba43aa..8c7ad012a65 100644
--- a/mysql-test/t/myisam.test
+++ b/mysql-test/t/myisam.test
@@ -1216,9 +1216,9 @@ DROP TABLE t1;
 --echo #
 --echo # BUG#48438 - crash with error in unioned query against merge table and view...
 --echo #
-SET GLOBAL table_open_cache=3;
+SET GLOBAL table_open_cache=10;
 CREATE TABLE t1(a INT);
-SELECT 1 FROM t1 AS a1, t1 AS a2, t1 AS a3, t1 AS a4 FOR UPDATE;
+SELECT 1 FROM t1 AS a1, t1 AS a2, t1 AS a3, t1 AS a4, t1 AS a5, t1 AS a6, t1 AS a7, t1 AS a8, t1 AS a9, t1 AS a10, t1 AS a11 FOR UPDATE;
 SELECT TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
 DROP TABLE t1;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 4b2230ea7da..eeabb0486ca 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -9408,7 +9408,7 @@ DELIMITER ;|
 set @tmp_toc= @@table_open_cache;
 set @tmp_tdc= @@table_definition_cache;
 
-set global table_open_cache=1;
+set global table_open_cache=10;
 set global table_definition_cache=1;
 call p1();
 
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 4f57d6d1910..7875d41d706 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -4287,11 +4287,20 @@ static int init_common_variables()
 
   /* connections and databases needs lots of files */
   {
-    uint files, wanted_files, max_open_files;
+    uint files, wanted_files, max_open_files, min_tc_size, extra_files,
+      min_connections;
+    ulong org_max_connections, org_tc_size;
 
+    /* Number of files reserved for temporary files */
+    extra_files= 30;
+    min_connections= 10;
     /* MyISAM requires two file handles per table. */
-    wanted_files= (10 + max_connections + extra_max_connections +
+    wanted_files= (extra_files + max_connections + extra_max_connections +
                    tc_size * 2);
+    min_tc_size= MY_MIN(tc_size, TABLE_OPEN_CACHE_MIN);
+    org_max_connections= max_connections;
+    org_tc_size= tc_size;
+
     /*
       We are trying to allocate no less than max_connections*5 file
       handles (i.e. we are trying to set the limit so that they will
@@ -4303,41 +4312,52 @@ static int init_common_variables()
       requested (value of wanted_files).
     */
     max_open_files= MY_MAX(MY_MAX(wanted_files,
-                            (max_connections + extra_max_connections)*5),
-                        open_files_limit);
+                                  (max_connections + extra_max_connections)*5),
+                           open_files_limit);
     files= my_set_max_open_files(max_open_files);
+    SYSVAR_AUTOSIZE_IF_CHANGED(open_files_limit, files, ulong);
 
-    if (files < wanted_files)
-    {
-      if (!open_files_limit)
-      {
-        /*
-          If we have requested too much file handles than we bring
-          max_connections in supported bounds.
-        */
-        SYSVAR_AUTOSIZE(max_connections,
-           (ulong) MY_MIN(files-10-TABLE_OPEN_CACHE_MIN*2, max_connections));
-        /*
-          Decrease tc_size according to max_connections, but
-          not below TABLE_OPEN_CACHE_MIN.  Outer MY_MIN() ensures that we
-          never increase tc_size automatically (that could
-          happen if max_connections is decreased above).
-        */
-        SYSVAR_AUTOSIZE(tc_size, 
-                        (ulong) MY_MIN(MY_MAX((files - 10 - max_connections) / 2,
-                                              TABLE_OPEN_CACHE_MIN), tc_size));
-	DBUG_PRINT("warning",
-		   ("Changed limits: max_open_files: %u  max_connections: %ld  table_cache: %ld",
-		    files, max_connections, tc_size));
-	if (global_system_variables.log_warnings > 1)
-	  sql_print_warning("Changed limits: max_open_files: %u  max_connections: %ld  table_cache: %ld",
-			files, max_connections, tc_size);
-      }
-      else if (global_system_variables.log_warnings)
-	sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files);
-    }
-    SYSVAR_AUTOSIZE(open_files_limit, files);
+    if (files < wanted_files && global_system_variables.log_warnings)
+      sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files);
+
+    /*
+      If we have requested too much file handles than we bring
+      max_connections in supported bounds. Still leave at least
+      'min_connections' connections
+    */
+    SYSVAR_AUTOSIZE_IF_CHANGED(max_connections,
+                               (ulong) MY_MAX(MY_MIN(files- extra_files-
+                                                     min_tc_size*2,
+                                                     max_connections),
+                                              min_connections),
+                               ulong);
+
+    /*
+      Decrease tc_size according to max_connections, but
+      not below min_tc_size.  Outer MY_MIN() ensures that we
+      never increase tc_size automatically (that could
+      happen if max_connections is decreased above).
+    */
+    SYSVAR_AUTOSIZE_IF_CHANGED(tc_size,
+                               (ulong) MY_MIN(MY_MAX((files - extra_files -
+                                                      max_connections) / 2,
+                                                     min_tc_size),
+                                              tc_size), ulong);
+    DBUG_PRINT("warning",
+               ("Current limits: max_open_files: %u  max_connections: %ld  table_cache: %ld",
+                files, max_connections, tc_size));
+    if (global_system_variables.log_warnings > 1 &&
+        (max_connections < org_max_connections ||
+         tc_size < org_tc_size))
+      sql_print_warning("Changed limits: max_open_files: %u  max_connections: %lu (was %lu)  table_cache: %lu (was %lu)",
+			files, max_connections, org_max_connections,
+                        tc_size, org_tc_size);
   }
+  /*
+    Max_connections and tc_cache are now set.
+    Now we can fix other variables depending on this variable.
+  */
+
   unireg_init(opt_specialflag); /* Set up extern variabels */
   if (!(my_default_lc_messages=
         my_locale_by_name(lc_messages)))
diff --git a/sql/set_var.h b/sql/set_var.h
index cf86ecf18fa..fe66eaff775 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -394,6 +394,16 @@ int sql_set_variables(THD *thd, List<set_var_base> *var_list, bool free);
     set_sys_var_value_origin(&VAR, sys_var::AUTO);      \
   } while(0)
 
+#define SYSVAR_AUTOSIZE_IF_CHANGED(VAR,VAL,TYPE)        \
+  do {                                                  \
+    TYPE tmp= (VAL);                                    \
+    if (VAR != tmp)                                     \
+    {                                                   \
+      VAR= (VAL);                                       \
+      set_sys_var_value_origin(&VAR, sys_var::AUTO);    \
+    }                                                   \
+  } while(0)
+
 void set_sys_var_value_origin(void *ptr, enum sys_var::where here);
 
 enum sys_var::where get_sys_var_value_origin(void *ptr);
diff --git a/sql/sql_const.h b/sql/sql_const.h
index 946cf13e2ae..0cd214d8bc7 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -124,7 +124,7 @@
 #define MAX_ACCEPT_RETRY	10	// Test accept this many times
 #define MAX_FIELDS_BEFORE_HASH	32
 #define USER_VARS_HASH_SIZE     16
-#define TABLE_OPEN_CACHE_MIN    400
+#define TABLE_OPEN_CACHE_MIN    200
 #define TABLE_OPEN_CACHE_DEFAULT 2000
 #define TABLE_DEF_CACHE_DEFAULT 400
 /**
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 2ec886f0528..8f4ab34e4c4 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -1387,7 +1387,7 @@ static bool fix_max_connections(sys_var *self, THD *thd, enum_var_type type)
 static Sys_var_ulong Sys_max_connections(
        "max_connections", "The number of simultaneous clients allowed",
        PARSED_EARLY GLOBAL_VAR(max_connections), CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(1, 100000),
+       VALID_RANGE(10, 100000),
        DEFAULT(MAX_CONNECTIONS_DEFAULT), BLOCK_SIZE(1), NO_MUTEX_GUARD,
        NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(fix_max_connections));
 
@@ -3192,7 +3192,7 @@ static bool fix_table_open_cache(sys_var *, THD *, enum_var_type)
 static Sys_var_ulong Sys_table_cache_size(
        "table_open_cache", "The number of cached open tables",
        GLOBAL_VAR(tc_size), CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(1, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT),
+       VALID_RANGE(10, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT),
        BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(fix_table_open_cache));
 
diff --git a/storage/tokudb/mysql-test/tokudb_bugs/r/db917.result b/storage/tokudb/mysql-test/tokudb_bugs/r/db917.result
index 9c29033429e..9276664f84f 100644
--- a/storage/tokudb/mysql-test/tokudb_bugs/r/db917.result
+++ b/storage/tokudb/mysql-test/tokudb_bugs/r/db917.result
@@ -1,8 +1,8 @@
 drop table if exists t1;
 set @orig_table_open_cache = @@global.table_open_cache;
 create table t1(a int) engine = tokudb partition by key(a) partitions 2 (partition p0 engine = tokudb, partition p1 engine = tokudb);
-lock tables t1 read;
-set @@global.table_open_cache = 1;
+lock tables t1 read,t1 as t2 read,t1 as t3 read, t1 as t4 read, t1 as t5 read, t1 as t6 read, t1 as t7 read, t1 as t8 read, t1 as t9 read, t1 as t10 read;
+set @@global.table_open_cache = 10;
 begin;
 insert into t1 values(1),(1);
 select * from t1 where c like _ucs2 0x039C0025 collate ucs2_unicode_ci;
diff --git a/storage/tokudb/mysql-test/tokudb_bugs/t/db917.test b/storage/tokudb/mysql-test/tokudb_bugs/t/db917.test
index ae94d7b30de..781fdb012f1 100644
--- a/storage/tokudb/mysql-test/tokudb_bugs/t/db917.test
+++ b/storage/tokudb/mysql-test/tokudb_bugs/t/db917.test
@@ -7,8 +7,8 @@ drop table if exists t1;
 enable_warnings;
 set @orig_table_open_cache = @@global.table_open_cache;
 create table t1(a int) engine = tokudb partition by key(a) partitions 2 (partition p0 engine = tokudb, partition p1 engine = tokudb);
-lock tables t1 read;
-set @@global.table_open_cache = 1;
+lock tables t1 read,t1 as t2 read,t1 as t3 read, t1 as t4 read, t1 as t5 read, t1 as t6 read, t1 as t7 read, t1 as t8 read, t1 as t9 read, t1 as t10 read;
+set @@global.table_open_cache = 10;
 begin;
 insert into t1 values(1),(1);
 # when the bug is present, this results in a lock wait timeout