###一、問題重現
版本5.7.11，我們首先設定引數：
- max_connections = 20 
- table_open_cache = 20
- table_definition_cache=10 
- open_files_limit=300
- innodb_open_files=300
- OS中設定 hard    nofile  300  soft    nofile  300

接下來我們建立3個大約80分割槽的表，注意建立期間可能要重啟一次資料庫，釋放一次open table cache/open table share，接下來我們連續開啟3個表當開啟最後一個表的時候如下：
```
mysql> select * from testpar1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
```
檢視日誌如下：
```
2020-10-16T22:36:41.736524-05:00 2 [ERROR] InnoDB: Operating system error number 24 in a file operation.
2020-10-16T22:36:41.736527-05:00 2 [ERROR] InnoDB: Error number 24 means 'Too many open files'
2020-10-16T22:36:41.736529-05:00 2 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-10-16T22:36:41.736531-05:00 2 [Warning] InnoDB: Cannot open './test/testpar1#P#p20190528.ibd'. Have you deleted .ibd files under a running mysqld server?
2020-10-16T22:36:41.736534-05:00 2 [ERROR] InnoDB: Trying to do I/O to a tablespace which exists without .ibd data file. I/O type: read, page: [page id: space=74, page number=3], I/O length: 16384 bytes
2020-10-16T22:36:41.736537-05:00 2 [ERROR] InnoDB: trying to read page [page id: space=74, page number=3] in nonexisting or being-dropped tablespace
2020-10-16T22:36:41.736541-05:00 2 [ERROR] [FATAL] InnoDB: Unable to read page [page id: space=74, page number=3] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/5.7/en/ for more details. Aborting...
2020-10-16 22:36:41 0x7f8b751ab700  InnoDB: Assertion failure in thread 140236941866752 in file ut0ut.cc line 920
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
02:36:41 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=2147483648
read_buffer_size=131072
max_used_connections=1
max_threads=20
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2120458 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f8b38000ae0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f8b751aaea8 thread_stack 0x40000
/opt/mysql/mysql3320/bin/mysqld(my_print_stacktrace+0x35)[0xf45595]
/opt/mysql/mysql3320/bin/mysqld(handle_fatal_signal+0x4a4)[0x77fd34]
/lib64/libpthread.so.0(+0xf630)[0x7f8c7bbea630]
/lib64/libc.so.6(gsignal+0x37)[0x7f8c7a76a387]
/lib64/libc.so.6(abort+0x148)[0x7f8c7a76ba78]
/opt/mysql/mysql3320/bin/mysqld[0x10dd465]
/opt/mysql/mysql3320/bin/mysqld(_ZN2ib5fatalD1Ev+0xb3)[0x10e2b33]
/opt/mysql/mysql3320/bin/mysqld(_Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb+0xf56)[0x11267c6]
/opt/mysql/mysql3320/bin/mysqld(_Z31btr_cur_open_at_index_side_funcbP12dict_index_tmP9btr_cur_tmPKcmP5mtr_t+0x3a5)[0x1103405]
/opt/mysql/mysql3320/bin/mysqld[0x1062d7b]
/opt/mysql/mysql3320/bin/mysqld(_Z15row_search_mvccPh15page_cur_mode_tP14row_prebuilt_tmm+0x1f62)[0x106d4f2]
/opt/mysql/mysql3320/bin/mysqld(_ZN11ha_innobase10index_readEPhPKhj16ha_rkey_function+0x247)[0xf7c687]
/opt/mysql/mysql3320/bin/mysqld(_ZN11ha_innobase11index_firstEPh+0x39)[0xf6ccf9]
/opt/mysql/mysql3320/bin/mysqld(_ZN11ha_innopart19index_first_in_partEjPh+0x2d)[0xf8fa7d]
/opt/mysql/mysql3320/bin/mysqld(_ZN16Partition_helper36handle_unordered_scan_next_partitionEPh+0x182)[0xc6d6e2]
/opt/mysql/mysql3320/bin/mysqld(_ZN7handler14ha_index_firstEPh+0x127)[0x7c8787]
/opt/mysql/mysql3320/bin/mysqld(_Z15join_read_firstP7QEP_TAB+0x6f)[0xce6ddf]
/opt/mysql/mysql3320/bin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x2ba)[0xce7cca]
/opt/mysql/mysql3320/bin/mysqld(_ZN4JOIN4execEv+0x28a)[0xce622a]
/opt/mysql/mysql3320/bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x250)[0xd51d00]
/opt/mysql/mysql3320/bin/mysqld[0xd12973]
/opt/mysql/mysql3320/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x3325)[0xd16605]
/opt/mysql/mysql3320/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3ad)[0xd1851d]
/opt/mysql/mysql3320/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x117d)[0xd196fd]
/opt/mysql/mysql3320/bin/mysqld(_Z10do_commandP3THD+0x194)[0xd1a1f4]
/opt/mysql/mysql3320/bin/mysqld(handle_connection+0x29c)[0xde605c]
/opt/mysql/mysql3320/bin/mysqld(pfs_spawn_thread+0x174)[0x1213184]
/lib64/libpthread.so.0(+0x7ea5)[0x7f8c7bbe2ea5]
/lib64/libc.so.6(clone+0x6d)[0x7f8c7a8328dd]
```
我們注意日誌中的error number 24 ，這是就是Linux errno
- EMFILE 24 /* Too many open files */

###二、5.7中open_files_limit 設定規則

在MySQL的官方文件中有如下說明：
```
The effective open_files_limit value is based on the value specified at system startup (if any) and
the values of max_connections and table_open_cache, using these formulas:
• 10 + max_connections + (table_open_cache * 2)
• max_connections * 5
• Operating system limit if that limit is positive but not Infinity
• If operating system limit is Infinity: open_files_limit value if specified at startup, 5000 if not

The server attempts to obtain the number of file descriptors using the maximum of those values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system will permit.The effective value is 0 on systems where MySQL cannot change the number of open files.
On Unix, the value cannot be set greater than ulimit -n.
```
也就是說引數的設定規則來自4個方面：
-  10 + max_connections + (table_open_cache * 2)
-  max_connections * 5
- 作業系統設定的open files
- 引數open_files_limit的設定，預設值為5000

對於如上4個值會取其中的最大值，獲取作業系統open files設定的時候使用的是getrlimit函式獲取，設定則使用的是setrlimit函式，這個比較和設定函式如下：
```
static uint set_max_open_files(uint max_file_limit) my_set_max_open_files
{
  struct rlimit rlimit;
  uint old_cur;
  DBUG_ENTER("set_max_open_files");
  DBUG_PRINT("enter",("files: %u", max_file_limit));

if (!getrlimit(RLIMIT_NOFILE,&rlimit)) //獲取OS設定的 nofile值
  {
    old_cur= (uint) rlimit.rlim_cur;
    DBUG_PRINT("info", ("rlim_cur: %u  rlim_max: %u",
			(uint) rlimit.rlim_cur,
			(uint) rlimit.rlim_max));
    if (rlimit.rlim_cur == (rlim_t) RLIM_INFINITY)
      rlimit.rlim_cur = max_file_limit;
    if (rlimit.rlim_cur >= max_file_limit) //如果OS設定大於 調整的引數 那麼以os設定為準
      DBUG_RETURN(rlimit.rlim_cur);		/* purecov: inspected */ 
    rlimit.rlim_cur= rlimit.rlim_max= max_file_limit;
    if (setrlimit(RLIMIT_NOFILE, &rlimit))  //如果OS值小於 則進行調整設定為 調整的值
      max_file_limit= old_cur;			/* Use original value */
    else//成功
    {
      rlimit.rlim_cur= 0;			/* Safety if next call fails */
      (void) getrlimit(RLIMIT_NOFILE,&rlimit);
      DBUG_PRINT("info", ("rlim_cur: %u", (uint) rlimit.rlim_cur));
      if (rlimit.rlim_cur)			/* If call didn't fail */
	max_file_limit= (uint) rlimit.rlim_cur;
    }
  }
  DBUG_PRINT("exit",("max_file_limit: %u", max_file_limit));
  DBUG_RETURN(max_file_limit);
}

```
對於innodb_open_files來說不能大於open_files_limit的設定，這段程式碼如下：
```
	if (innobase_open_files < 10) { //不能小於10
		innobase_open_files = 300;
		if (srv_file_per_table && table_cache_size > 300) {
			innobase_open_files = table_cache_size;
		}
	}

if (innobase_open_files > (long) open_files_limit) { //不能超過open_files_limit的大小
		ib::warn() << "innodb_open_files should not be greater"
                       " than the open_files_limit.\n";
		if (innobase_open_files > (long) table_cache_size) {
			innobase_open_files = table_cache_size;
		}
	}
```

但是值得注意的是如果我們使用mysqld_safe來啟動資料庫，那麼mysqld_safe也會透過my_print_defaults進行引數解析獲取其中的open_files_limit，同時呼叫ulimit -n 進行設定如下：
```
+ arg=--open-files-limit=300
+ case "$arg" in
+ open_files=300
...
+ ulimit -n 300
```
因為mysqld_safe和mysqld屬於父子程式，那麼mysqld複製了其中的PCB那麼資源使用上限和mysqld_safe一致，比如這裡mysqld的file limit也是300。我們可以簡單測試一下如下：

- mytest簡單的獲取當前程式的file limit
```
#include<stdio.h>
#include<stdlib.h>
#include <sys>
#include <unistd.h>
int main(void){
 struct rlimit rlimit;
 getrlimit(RLIMIT_NOFILE,&rlimit);
 printf("limit files soft limit is %ld ,hard limit is %ld\n",(uint) rlimit.rlim_cur,(uint) rlimit.rlim_max);
 sleep(1000);
}
```
- mytest.sh 設定ulimit -n 呼叫mytest，模擬mysqld_safe呼叫mysqld
```
#!/bin/bash
ulimit -n 300
/opt/mysql/mysql3306/install/support-files/mytest

```

測試如下
- 當前OS設定open file limit是30000，直接跑mytest：
```
[root@mgr1 support-files]# ulimit -a |grep file
core file size          (blocks, -c) unlimited
file size               (blocks, -f) unlimited
open files                      (-n) 30000
file locks                      (-x) unlimited
[root@mgr1 support-files]# ./mytest
limit files soft limit is 30000 ,hard limit is 30000

```

- 當前OS設定open file limit是30000，mytest.sh呼叫mytest：

```
[root@mgr1 support-files]# ulimit -a |grep file
core file size          (blocks, -c) unlimited
file size               (blocks, -f) unlimited
open files                      (-n) 30000
file locks                      (-x) unlimited
[root@mgr1 support-files]# ./mytest.sh 
limit files soft limit is 300 ,hard limit is 300

這是一個父子程式
root      25782  25751  0 03:43 pts/2    00:00:00 /bin/bash ./mytest.sh
root      25783  25782  0 03:43 pts/2    00:00:00 /opt/mysql/mysql3306/install/support-files/mytest
```

因此我們如果引數檔案設定了open_files_limit，那麼使用mysqld_safe啟動的時候將會使用open_files_limit設定的值，這一點5.7版本是要注意的。總結一下：
- 如果使用mysqld直接啟動資料庫那麼將遵守官方文件規則。
- 如果使用mysqld_safe拉取mysqld，如果引數檔案設定了open_files_limit那麼mysqld_safe會獲取open_files_limit的設定並且使用ulimit -n 進行修改，然後拉起mysqld，不依賴OS的設定，官方文件規則去掉第三條。
- 如果使用mysqld_safe拉取mysqld，如果引數檔案沒有設定open_files_limit則遵守官方文件規則。

###三、錯誤流程

#####1、報錯流程
- os_file_get_last_error_low:

```
		ib::error()
			<< "Operating system error number "
			<< err
			<< " in a file operation.";
		...
		} else if (!os_diagnose_all_o_direct_einval(err)) {
			if (strerror(err) != NULL) {

ib::error()
					<< "Error number " << err << " means '"
					<< strerror(err) << "'";
			}

ib::info() << OPERATING_SYSTEM_ERROR_MSG;
		}

```

- fil_node_open_file : 
```
ib::warn() << "Cannot open '" << node->name << "'."
				" Have you deleted .ibd files under a"
				" running mysqld server?";
```
- _fil_io :
``` 
if (!req_type.ignore_missing()) {
				ib::error()
					<< "Trying to do I/O to a tablespace"
					" which exists without .ibd data file."
					" I/O type: "
					<< (req_type.is_read()
					    ? "read" : "write")
					<< ", page: "
					<< page_id_t(page_id.space(),
						     cur_page_no)
					<< ", I/O length: " << len << " bytes";
			}

```
- buf_read_page
```

if (err == DB_TABLESPACE_DELETED) {
		ib::error() << "trying to read page " << page_id
			<< " in nonexisting or being-dropped tablespace";
	}
```

#####2、訊號處理發出

當上面的報錯聯絡100次後將會觸發crash，其中BUF_PAGE_READ_MAX_RETRIES為100

```
		if (buf_read_page(page_id, page_size, trx)) { //進行物理檔案讀取
			buf_read_ahead_random(page_id, page_size,
					      ibuf_inside(mtr), trx);

retries = 0;
		} else if (retries < BUF_PAGE_READ_MAX_RETRIES) {
			++retries;
			DBUG_EXECUTE_IF(
				"innodb_page_corruption_retries",
				retries = BUF_PAGE_READ_MAX_RETRIES;
			);
		} else {
			ib::fatal() << "Unable to read page " << page_id
				<< " into the buffer pool after "
				<< BUF_PAGE_READ_MAX_RETRIES << " attempts."
				" The most probable cause of this error may"
				" be that the table has been corrupted. Or,"
				" the table was compressed with with an"
				" algorithm that is not supported by this"
				" instance. If it is not a decompress failure,"
				" you can try to fix this problem by using"
				" innodb_force_recovery."
				" Please see " REFMAN " for more"
				" details. Aborting...";
		}

```
fatal的解構函式如下：
```
fatal::~fatal()
{
	sql_print_error("[FATAL] InnoDB: %s", m_oss.str().c_str());
	ut_error;
}
```
ut_error會呼叫assert(0)，觸發abort()，發出signal 6訊號，導致crash。值得注意的是5.7.29的報錯已經不一樣了，這段程式碼有所變化，並且不會觸發crash，報錯如下：
```
2020-10-15T23:24:06.546126-05:00 2 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-10-15T23:24:06.546130-05:00 2 [Note] InnoDB: fil_sys open file LRU len 276
2020-10-15T23:24:06.547016-05:00 2 [ERROR] InnoDB: Operating system error number 24 in a file operation.
2020-10-15T23:24:06.547030-05:00 2 [ERROR] InnoDB: Error number 24 means 'Too many open files'
2020-10-15T23:24:06.547035-05:00 2 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-10-15T23:24:06.547039-05:00 2 [Note] InnoDB: fil_sys open file LRU len 276
```

###四、關於assert斷言

我們可以簡單寫一個assert觸發一下abort函式看看捕獲的訊號是什麼如下：
```
#include<stdlib.h>
#include<stdio.h>
#include <assert.h>
#include <unistd.h>
#include <signal.h>

#define ut_error assert(0)

void handsig6(int sig)
{
  if(sig ==  SIGABRT){
    printf("%s\n%s\n","I catch SIGABRT singal is  6","do other things exp dump crash stack!!")        
  exit(0);
  }
}

int fatal()
{
        printf("get fatal error\n");
        ut_error;
}

int main(void)
{
  signal(SIGABRT,handsig6);
  fatal();
}
```
執行如下：
```
get fatal error
a.out: test10.c:23: fatal: Assertion `0' failed.
I catch SIGABRT singal is  6
do other things exp dump crash stack!!
```
可以看到觸發的正是SIGABRT訊號即訊號6</signal.h></unistd.h></assert.h></stdio.h></stdlib.h></unistd.h></sys></stdlib.h></stdio.h>

MySQL：5.7.11 超過最大開啟檔案數crash原因解析

相關文章