问题描述
我有几个数据库:
- 当前
- 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.但是如果我对表 columns 尝试同样的操作: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).
我提交:从表中选择 COUNT(*)
或SELECT table_schema, table_name FROM tables
我得到了结果.
但是如果我提交SELECT * FROM 表
或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 FilesMariaDB 10.2dataib_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 FilesMariaDB 10.2inmysqld.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):丢失的连接仅对一个数据库持续存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！