MariaDB:错误2013(HY000):丢失的连接仅对于一个数据库仍然存在 [英] MariaDB: ERROR 2013 (HY000): Lost connection persists only for one database

问题描述

我有几个数据库:
- curr
- curr_add

I have several databases:
- curr
- curr_add

以及同一MariaDB实例上的许多其他对象.当我向 curr_add 提交一条sql时，我得到了连接和结果.当我将查询发送到 curr 时，出现错误:
MariaDB: ERROR 2013 (HY000): Lost connection to MySQL server during query

and many others on the same MariaDB instance. When I submit an sql to curr_add I get connection and the results. When I send the query to curr I get the error:
MariaDB: ERROR 2013 (HY000): Lost connection to MySQL server during query

，服务停止.每次我想访问数据库 curr 时，重新启动服务时，服务将停止.同一实例的所有其他数据库不是这种情况.实例和数据库已经存在一年了，我从来没有遇到过这样的问题.有没有办法找出问题所在以及如何解决?

and the service stops. When I restart the service each time I want to access the database curr the serivice stops. This is not the case for all other databases of the same instance. The instance and databases exists since one year and I had never such problems. Is there a way to find out what is the problem and how to solve it?

工作环境:

• Windows 7
• MariaDB 10.2.6
• 我以root身份输入:mysql -uroot -h localhost -p

更新(1):

我可以访问数据库 information_schema .例如，我可以计算表 system_variables :SELECT COUNT(*) FROM system_variables的行.但是，如果我尝试对表列 :SELECT COUNT(*) FROM columns进行相同操作，则连接将丢失(参见图).

I have access to database information_schema. For example, I can count the rows of table system_variables: SELECT COUNT(*) FROM system_variables. But if I try the same with table columns: SELECT COUNT(*) FROM columns, the connection will be lost (see figure).

我提交:
SELECT COUNT(*) FROM tables
或
SELECT table_schema, table_name FROM tables
我得到结果.
但是如果我提交
SELECT * FROM tables
或
SELECT table_schema, table_name, engine, table_rows FROM tables LIMIT 10
连接将会丢失.

I submit:
SELECT COUNT(*) FROM tables
or
SELECT table_schema, table_name FROM tables
I get results.
But if I submit
SELECT * FROM tables
or
SELECT table_schema, table_name, engine, table_rows FROM tables LIMIT 10
the connection will be lost.

摘要:一些数据库断开了mysql-server的连接，也断开了一些表的列.

Summary: some database disconnect the mysql-server and also some columns of tables.

更新(2):文件.err中的错误信息

Update (2): error informations from file .err

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2017-11-28 19:42:43 7820 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2017-11-28 19:42:43 7820 [Note] InnoDB: Uses event mutexes
2017-11-28 19:42:43 7820 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-11-28 19:42:43 7820 [Note] InnoDB: Number of pools: 1
2017-11-28 19:42:43 7820 [Note] InnoDB: Using generic crc32 instructions
2017-11-28 19:42:43 7820 [Note] InnoDB: Initializing buffer pool, total size = 14G, instances = 8, chunk size = 128M
2017-11-28 19:42:44 7820 [Note] InnoDB: Completed initialization of buffer pool
2017-11-28 19:42:44 7820 [Note] InnoDB: Highest supported file format is Barracuda.
2017-11-28 19:42:44 7820 [Note] InnoDB: Starting crash recovery from checkpoint LSN=556718604758
2017-11-28 19:42:52 7820 [Note] InnoDB: 128 out of 128 rollback segments are active.
2017-11-28 19:42:52 7820 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-11-28 19:42:52 7820 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-11-28 19:42:52 7820 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-11-28 19:42:52 7820 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2017-11-28 19:42:52 7820 [Note] InnoDB: 5.7.14 started; log sequence number 556718604767
2017-11-28 19:42:52 4868 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7784ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-11-28 19:42:52 7380 [Note] InnoDB: Loading buffer pool(s) from C:\Program Files\MariaDB 10.2\data\ib_buffer_pool
2017-11-28 19:42:52 7820 [Note] Server socket created on IP: '::'.
2017-11-28 19:42:52 7820 [Note] Reading of all Master_info entries succeded
2017-11-28 19:42:52 7820 [Note] Added new Master_info '' to hash table
2017-11-28 19:42:52 7820 [Note] C:\Program Files\MariaDB 10.2\bin\mysqld.exe: ready for connections.
Version: '10.2.6-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution
2017-11-28 19:44:08 7380 [Note] InnoDB: Buffer pool(s) load completed at 171128 19:44:08
2017-11-28 19:44:20 9820 [Warning] InnoDB: Retry attempts for reading partial data failed.
2017-11-28 19:44:20 9820 [ERROR] InnoDB: Tried to read 16384 bytes at offset 4947968, but was only able to read 0
2017-11-28 19:44:20 9820 [ERROR] InnoDB: File (unknown): 'read' returned OS error 0. Cannot continue operation
171128 19:44:20 [ERROR] mysqld got exception 0x80000003 ;
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.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

Server version: 10.2.6-MariaDB
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=1
max_threads=65537
thread_count=7
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 136026 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7d1b9fd8
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...
mysqld.exe!my_parameter_handler()[my_init.c:259]
mysqld.exe!raise()[signal.cpp:516]
mysqld.exe!abort()[abort.cpp:71]
mysqld.exe!os_file_handle_error_cond_exit()[os0file.cc:5209]
mysqld.exe!os_file_read_page()[os0file.cc:5091]
mysqld.exe!os_file_read_func()[os0file.cc:5433]
mysqld.exe!fil_io()[fil0fil.cc:5436]
mysqld.exe!buf_read_page_low()[buf0rea.cc:179]
mysqld.exe!buf_read_page()[buf0rea.cc:436]
mysqld.exe!buf_page_get_gen()[buf0buf.cc:4267]
mysqld.exe!btr_cur_search_to_nth_level()[btr0cur.cc:1115]
mysqld.exe!btr_pcur_open_low()[btr0pcur.ic:457]
mysqld.exe!btr_pcur_open_on_user_rec_func()[btr0pcur.cc:597]
mysqld.exe!dict_load_foreign()[dict0load.cc:3334]
mysqld.exe!dict_load_foreigns()[dict0load.cc:3587]
mysqld.exe!dict_load_table_one()[dict0load.cc:2958]
mysqld.exe!dict_load_table()[dict0load.cc:2670]
mysqld.exe!dict_table_open_on_name()[dict0dict.cc:1174]
mysqld.exe!ha_innobase::open_dict_table()[ha_innodb.cc:6976]
mysqld.exe!ha_innobase::open()[ha_innodb.cc:6618]
mysqld.exe!handler::ha_open()[handler.cc:2507]
mysqld.exe!open_table_from_share()[table.cc:3278]
mysqld.exe!open_table()[sql_base.cc:1874]
mysqld.exe!open_and_process_table()[sql_base.cc:3409]
mysqld.exe!open_tables()[sql_base.cc:3926]
mysqld.exe!open_and_lock_tables()[sql_base.cc:4682]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:6352]
mysqld.exe!mysql_execute_command()[sql_parse.cc:3448]
mysqld.exe!mysql_parse()[sql_parse.cc:7879]
mysqld.exe!dispatch_command()[sql_parse.cc:1814]
mysqld.exe!do_command()[sql_parse.cc:1361]
mysqld.exe!threadpool_process_request()[threadpool_common.cc:346]
mysqld.exe!tp_callback()[threadpool_common.cc:192]
ntdll.dll!TpPostWork()
ntdll.dll!RtlRealSuccessor()
kernel32.dll!BaseThreadInitThunk()
ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x78b38ff0): SELECT COUNT(*) FROM curr.patient
Connection ID (thread ID): 9
Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

推荐答案

基于堆栈跟踪，似乎InnoDB系统表空间比预期的要短.当功能dict_load_foreigns()访问InnoDB系统表SYS_FOREIGN或SYS_FOREIGN_COLS时，它正在请求不在缓冲池中的页面.页面读取请求导致InnoDB自杀，因为文件太短.

Based on the stack trace, it seems to be the InnoDB system tablespace that is shorter than expected. When the function dict_load_foreigns() is accessing the InnoDB system table SYS_FOREIGN or SYS_FOREIGN_COLS, it is requesting a page that is not in the buffer pool. The page read request causes InnoDB to commit suicide, because the file is too short.

众所周知，InnoDB不会报告有问题的文件名.我们应该在某个时候重构MariaDB中的I/O代码.在这种情况下，我们确实知道问题出在InnoDB系统表空间中，因为InnoDB内部的SYS_表位于此处.

InnoDB notoriously does not report the problematic file name. We should refactor the I/O code in MariaDB at some point. In this case, we do know that the problem is in the InnoDB system tablespace, because the InnoDB internal SYS_ tables are located there.

MariaDB跟踪器中已经存在一些相关的错误.我认为这些情况已经涵盖了这种情况:

There already exist some related bugs in the MariaDB tracker. I think that this scenario is already covered by these:

• MDEV-13542在损坏的页面上崩溃无济于事(是的，我复制了古代 MySQL Bug#10132 )
• MDEV-11633将InnoDB系统表空间设置为可选(这不会很快就会发生，并且设计尚未完成)

• MDEV-13542 Crashing on a corrupted page is unhelpful (Yes, I copied the title of the ancient MySQL Bug#10132)
• MDEV-11633 Make the InnoDB system tablespace optional (This is not going to happen soon, and the design is not finalized)

首先了解腐败是如何发生的，这将很有趣.在 MDEV-11556 之前，MariaDB中的InnoDB数据文件扩展名不是完全崩溃安全的. (MySQL根本不包含此修复程序.)

It would be interesting to know how the corruption occurred in the first place. Before MDEV-11556, InnoDB data file extension in MariaDB was not fully crash-safe. (MySQL does not contain this fix at all.)

是否可以在某个时间点复制文件?复制过程中有错误吗?还是系统表空间最初可能由多个文件组成，但是服务器使用错误的innodb_data_file_path启动，因此忽略了最后一个文件?在访问丢失"文件中的页面之前，一切都会很好.

Could it be that the files were copied at some point? A bug in the copy procedure? Or could the system tablespace have originally consisted of multiple files, but the server was started up with the wrong innodb_data_file_path so that the last file(s) were ignored? Everything would appear fine until a page in the ‘missing’ files is being accessed.

您可能会问:如何解决此错误?不幸的是，我认为目前没有任何方法可以跳过对外键元数据的读取.因此，如果元数据表已损坏，则在最坏的情况下，您将无法访问任何InnoDB表.为此，我欢迎您 MariaDB错误报告.

You might ask: How to work around this error? Unfortunately, I don’t think that there currently is any way to skip the read of the foreign key metadata. So, if the metadata tables are corrupted, in the worst case you will be unable to access any InnoDB tables. For this, I would welcome a MariaDB bug report.

这篇关于MariaDB:错误2013(HY000):丢失的连接仅对于一个数据库仍然存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！