MySQL崩溃(“某些指针可能无效并且导致转储中止") [英] MySQL crashes ("Some pointers may be invalid and cause the dump to abort")

问题描述

我有一个5GB的MySQL数据库，称为"tweets"，我需要从中访问表"searchresults".但是，当我对其执行查询或创建转储时，MySQL服务器(在Windows 10上运行)始终崩溃，并在同一行出现相同错误.

I have a 5GB MySQL database called "tweets", from which I need to access the table "searchresults". But when I perform a query on it or create a dump, the MySQL Server (Running on Windows 10) crashes all the time with the same error at the same row.

例如，如果我尝试使用以下命令转储数据库:

For example, if I try to dump the database with the following command:

C:\xampp\mysql\bin>mysqldump.exe --user root --force tweets > D:\secondtry.sql

我在cmd.exe窗口中收到以下错误消息，并一遍又一遍地完全相同:

I get the following error message in the cmd.exe window with the exact same row over and over again:

mysqldump.exe: Error 2013: Lost connection to MySQL server during query when dumping table `searchresults` at row: 5222907

mysqldump.exe: Couldn't execute 'SELECT engine FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'stats'': MySQL server has gone away (2006)

mysqldump.exe: Couldn't execute 'SET SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)

mysqldump.exe: Couldn't execute 'SELECT `COLUMN_NAME` AS `Field`, `COLUMN_TYPE` AS `Type`, `IS_NULLABLE` AS `Null`, `COLUMN_KEY` AS `Key`, `COLUMN_DEFAULT` AS `Default`, `EXTRA` AS `Extra`, `COLUMN_COMMENT` AS `Comment` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = 'tweets' AND TABLE_NAME = 'stats'': MySQL server has gone away (2006)

mysqldump.exe: Couldn't execute 'UNLOCK TABLES': MySQL server has gone away (2006)

，并且在mysql_error.log中，我收到以下消息，并且服务器关闭:

and in the mysql_error.log I receive the following message and the server shuts down:

Server version: 10.1.8-MariaDB <br/> key_buffer_size=16777216 <br/> read_buffer_size=262144 <br/> max_used_connections=1 <br/>  max_threads=1001 <br/> thread_count=1 <br/> It is possible that mysqld could use up to <br/> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = <br/> 787099 K  bytes of memory Hope that's ok; if not, decrease some <br/> variables in the equation. 

Thread pointer: 0x0x5b93168 <br/> Attempting backtrace. You can use the <br/> following information to find out where mysqld died. If you see no <br/> messages after this, something went terribly wrong... <br/> mysqld.exe!my_parameter_handler() <br/> mysqld.exe!my_mb_ctype_mb()<br/> mysqld.exe!??0Global_read_lock@@QAE@XZ()<br/> mysqld.exe!??0Global_read_lock@@QAE@XZ()<br/> mysqld.exe!?store_record_for_lookup@Stat_table@@IAEXXZ()<br/> mysqld.exe!??0Global_read_lock@@QAE@XZ()<br/> mysqld.exe!??0Global_read_lock@@QAE@XZ()<br/> mysqld.exe!??0Global_read_lock@@QAE@XZ()<br/> mysqld.exe!?store_record_for_lookup@Stat_table@@IAEXXZ()<br/> mysqld.exe!?store_record_for_lookup@Stat_table@@IAEXXZ()<br/> mysqld.exe!?ha_rnd_next@handler@@QAEHPAE@Z()<br/> mysqld.exe!?rr_sequential@@YAHPAUREAD_RECORD@@@Z()<br/> mysqld.exe!?sub_select@@YA? AW4enum_nested_loop_state@@PAVJOIN@@PAUst_join_table@@_N@Z() <br/> mysqld.exe!?setup_end_select_func@@YAP6A?AW4enum_nested_loop_state@@PAVJOIN@@PAUst_join_table@@_N@Z0@Z() <br/> mysqld.exe!?exec_inner@JOIN@@QAEXXZ() <br/> mysqld.exe!?exec@JOIN@@QAEXXZ()<br/> mysqld.exe!?handle_select@@YA_NPAVTHD@@PAULEX@@PAVselect_result@@K@Z()<br/> mysqld.exe!??0Table_scope_and_contents_source_st@@QAE@ABU0@@Z()<br/> mysqld.exe!?mysql_execute_command@@YAHPAVTHD@@@Z()<br/> mysqld.exe!?mysql_parse@@YAXPAVTHD@@PADIPAVParser_state@@@Z()<br/> mysqld.exe!?dispatch_command@@YA_NW4enum_server_command@@PAVTHD@@PADI@Z()<br/> mysqld.exe!?do_command@@YA_NPAVTHD@@@Z()<br/> mysqld.exe!?threadpool_process_request@@YAHPAVTHD@@@Z()<br/> mysqld.exe!?tp_end@@YAXXZ() <br/> KERNEL32.DLL!SetUserGeoID()<br/> ntdll.dll!TpSimpleTryPost() <br/> ntdll.dll!EtwNotificationRegister()<br/> KERNEL32.DLL!BaseThreadInitThunk()<br/> ntdll.dll!RtlUnicodeStringToInteger()<br/> ntdll.dll!RtlUnicodeStringToInteger()<br/>

Trying to get some variables. Some pointers may be invalid and cause<br/> the dump to abort. Query (0x5b9a908): SELECT /*!40001 SQL_NO_CACHE */<br/>
* FROM `searchresults`  <br/> Connection ID (thread ID): 2  <br/> Status: NOT_KILLED<br/>

到目前为止，我已经尝试过:

So far I have tried :

1. 我将my.ini中的innodb_force_recovery设置为1和6
2. 我在mysqldump.exe中使用了参数"--force"，-skip-extended-insert"和"--hex-blob"
3. 我使用PHPMyAdmin，MySQLWorkbench，甚至尝试使用Microsoft SQL迁移工具将数据库转换为MSSQL数据库
4. 我在my.ini中增加了max_allowed_pa​​cket选项
5. 我使用十六进制编辑器在.idb文件中找到损坏的行将其删除，但是我找不到要删除的内容
6. 使用我的备份，但是错误已经在那里
7. 修复表"，但出现以下响应表的存储引擎不支持修复"
8. 使用"ALTER TABLE searchresults ENGINE = InnoDB;"重建表.但是在重建过程中发生错误.
9. 将my.ini中的innodb_log_file_size参数设置为最大4G会引发相同的错误.
10. 使用其他计算机

1. I set innodb_force_recovery in the my.ini to 1 and 6
2. I used the parameters "--force", "--skip-extended-insert" and "--hex-blob" for the mysqldump.exe
3. I used PHPMyAdmin, MySQLWorkbench and even tried the Microsoft SQL Migration Tool to convert the DataBase into a MSSQL database
4. I increased the max_allowed_packet option in the my.ini
5. I used an Hex Editor to find the corrupt row in the .idb file to delete it, but I could not find out what I have to delete
6. Use my backup, but the error is already in there
7. "Repair Table", but I get the following response "The storage engine for the table doesn't support repair"
8. Rebuilt the table with "ALTER TABLE searchresults ENGINE = InnoDB;" but the error occurs during the rebuild.
9. Setting the innodb_log_file_size parameter in the my.ini to the maximum of 4G throws the same error.
10. Use a different computer

我很确定行5222907或5222908中存在损坏的数据集，这就是服务器崩溃的原因.如果我以后可以访问其余数据，那么丢失一行就完全可以了.我什至可能会丢失前5222908行.但是当我无法删除损坏的数据时.如果我使用查询

I am pretty sure that there is a corrupt dataset in row 5222907 or 5222908 and that is why the server crashes. It would be totally fine for me to lose one row, if I can access the rest of the data afterwards. I could even lose the first 5222908 rows. But when I can't delete the corrupt data. If I use the query

DELETE FROM searchresults LIMIT 5222908

服务器再次崩溃.

对于此问题的任何提示，我将非常感谢.数据对我来说非常重要，因为我的硕士论文需要使用该数据，这将很快到期.

I would be very thankful for any tips on this matter. The data is very important to me, because I need it for my Master Thesis, which is due very soon.

感谢您的时间和精力！

Thank you for your time and effort!

这是我的表结构(注意，没有键或索引):

This is my table structure (notice, there are no keys or indexes):

CREATE TABLE `searchresults` (
 `id` bigint(20) DEFAULT NULL,
 `user` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 `createdAt` timestamp NULL DEFAULT NULL,
 `retweetcount` int(11) DEFAULT NULL,
 `favoritecount` int(11) DEFAULT NULL,
 `message` varchar(500) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci

我这样使用INSERT INTO:

I used an INSERT INTO like this:

SELECT id INTO OUTFILE 'C:/Temp/allCount.csv' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM searchresults;

根据选择的列(id，retweetcount等)，我得到不同数量的行.它们始终低于5222907，但这意味着，问题仍然可能是与参数相关的问题，而不是损坏错误.你怎么认为?您知道我可以在mysql配置中调整的一些其他参数吗?

And depending on the column I select (id, retweetcount etc.) I get a different amount of rows. They are always lower than 5222907, but this means, the issue could still be something parameter related instead of a corruption error. What do you think? Do you know some additional parameters I could adjust in the mysql config?

推荐答案

您可以研究修改mysql服务器的这些属性:

You could look into modifying these properties of mysql server:

nano/etc/mysql/my.cnf

nano /etc/mysql/my.cnf

query_cache_size=16M
key_buffer_size=256M

或者甚至可以尝试增加mysql客户端查询的允许执行时间:

Or maybe even try increasing the allowable execution time for the query from mysql client:

SET GLOBAL MAX_STATEMENT_TIME=1000;

在执行查询之前.

这篇关于MySQL崩溃(“某些指针可能无效并且导致转储中止")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！