Bug #107871 Mysql 5.7 crashed and cannot select data from one table any more
Submitted: 13 Jul 2022 14:12 Modified: 15 Jul 2022 14:12
Reporter: n n Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:mysql 5.7.2 OS:Windows (10)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: innodb can't open DB, server exits

[13 Jul 2022 14:12] n n
Description:
Hi, I've thinking about it whole night , but I have not any idea . 
One of my table 'k_data_plus' cannot open any more after whole night data insert from my python script. And everytime i want to select(or any dml sentence) from this table , or use mysqldump to dump database , it'll make my mysql service immediate exit. I have 4GBytes data in this table . And i don't want to download it again. I've tried the method in https://stackoverflow.com/questions/33829888/innodb-attempted-to-open-a-previously-opened-... . It doesn't effect . I've search your bug database , didn't match this case. Hope for your reply. Mysql 5.7 is not out of date in our area . I'm about to changing to use 8.0 but this abort me.
How can I avoid of this question and repair it . If it really happends in my company . I think it is really hard to repair in a large amount of servers.
Version: '5.7.34'  socket: ''  port: 3306  MySQL Community Server (GPL)
2022-07-13T13:01:04.727979Z 2 [ERROR] InnoDB: Trying to access page number 3 in space 790, space name quant_analyser/k_data_plus, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2022-07-13T13:01:04.729505Z 2 [ERROR] InnoDB: Server exits.

How to repeat:
I have no idea . Just insert data with python script whole night. And set windows10 sleep when no actions after 2 hour.

Suggested fix:
I hope it is fix in 8.0 version. I'll use it soon,but I can't dump my data.
[13 Jul 2022 19:58] MySQL Verification Team
Hi,

I cannot reproduce this. 

With the "sleep after 2 hours" - is your MySQL Server host configured to go to sleep or your client is configured to go to sleep. You cannot setup your server to go to sleep, that can lead to all kind of unsupported situations, and desktop operating system consider "activity" moving your mouse, not background process doing something important, like accepting connections and writing data.
[13 Jul 2022 22:38] n n
I think sleep maybe not the key problem. Sleep is windows10's(not server,just my personal computer)  action.  Do MySQL has it own graceful shutdown strategy?  I didn't happen this question before even download whole night like this time. Difference is only this time I download about 4Gbytes data into one table.Maybe it is too large? Do I need to enlarge tablespace with any settings? It seems like it is a random happened question which really make me confused. How can i give u more information to help you check about this question?
[14 Jul 2022 2:02] MySQL Verification Team
Hi,

I do not see you have a crash resembling a bug. This really looks like a support question and we do have great MySQL Support team that would be able to help you out do whatever is that you are trying to do. 

If you can show me a bug that is reproducible I would be happy to work on it but from what I see at the moment
 - you have a client (windows10?) that is executing long running query? or mysqldump? or ?
 - you have a server (windows server? linux? solaris? bsd? something else?) where your MySQL Server is running
 - sometimes after few hours your client (or your server?) goes to sleep
 - sometimes after something goes to sleep your MySQL Server crash

This crash can be a bug. For this bug we need
1. operating system data
2. version of MySQL data (I assume 5.7.2 but select @@version would be helpful)
3. crash log
4. if your MySQL Server is running on some unix-like system, a core dump would be required too

On the other hand the "InnoDB: Trying to access page number ...." is not a bug, this is corrupted data directory. How it came to corruption I can't say but if your OS is sending MySQL Server to sleep while MySQL is doing something - no clue what could happen, especially on Windows if you have some antivirus intercepting disk IO. How to recover your data and get your server up and running is not something a BUG's system is a place to get help. Either open a ticket with MySQL Support team or check out free support on our https://forums.mysql.com/ or you can read our documentation that contain everything you need to know on how to recover: https://dev.mysql.com/doc/refman/5.7/en/innodb-recovery.html

With regards to shutdown, yes, of course, there is a way to gracefully shutdown MySQL Server ("mysqladmin shutdown" will do the trick): https://dev.mysql.com/doc/refman/5.7/en/server-shutdown.html 

Kind regards
[14 Jul 2022 2:09] MySQL Verification Team
> Difference is only this time I download about 4Gbytes 
> data into one table.Maybe it is too large?

Not sure if you are doing a SELECT from table to download table data to your client or you are doing INSERT into table to download data from your client into the table.

With corrupted datadir, if you are doing SELECT, every time you arrive to the point of corruption, the server will crash (read about data recovery on the link I provided in previous answer). If you are doing INSERT, you DO NOT want to insert data into corrupted table. First repair this table, rebuild it, and then INSERT data into it.

Both selecting and inserting 4G of data is not a "whole night" operation, you either have your server running on some puny VM or you are doing something wrong. I just did a dump and restore of 47G ( so 10x more data, both select from database to disk scp to another server and then load that from disk into database ) on a pretty average Linux server pair in few hours.
[14 Jul 2022 13:59] n n
error log from crash day to now

Attachment: errorlog.txt (text/plain), 184.79 KiB.

[14 Jul 2022 13:59] n n
Thank u for your kindly reply.
I'll offer my state below.😃

=============================First Segment Start===============================

1. Q: - you have a client (windows10?) that is executing long running query?
or mysqldump? or ?
A : I try to get data from a web interface. So I'm doing frequently insert. All the behaviour is only execute a insert thread into one table.
The next day I wake up my computer, and check about the table data. I use navicat to open my table . It tell me 2003 cannt connect to mysql server. After reboot mysql service . I try to open other table in same database , it worked . Only this table corrupted.

2.Q:you have a server (windows server? linux? solaris? bsd? something
else?) where your MySQL Server is running
A: I want to express my mysql 5.7 run's on my personal computer . Not on the company big server machine. So my machine performance maybe not so well and not as stable as company server or any cloud service server.

3.sometimes after few hours your client (or your server?) goes to
sleep
A: Not mysql server goes to sleep. It is my personal computer operating system's behaviour . But I think there're still active process running , win 10 will not go to deep sleep mode.

4.sometimes after something goes to sleep your MySQL Server crash
A: Mysql Server crach caused by operating system sleeping is only my guess. Because I didn't do any complex action to the table . Only insert many times. So I think maybe the crash is caused by other info .

=============================First Segment End===============================

This crash can be a bug. For this bug we need
1. operating system data
2. version of MySQL data (I assume 5.7.2 but select @@version would be
helpful)
3. crash log
4. if your MySQL Server is running on some unix-like system, a core dump
would be required too
=============================Second Segment Start===============================

Q1: operating system data

OS Name:                   Microsoft Windows 10 Professional
OS Version:                10.0.19043 N/A Build 19043
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Standalone Workstation
OS Build Type:             Multiprocessor Free
Registered Owner:          Microsoft User
Registered Organization:   Microsoft USA
Product ID:                --
Original Install Date:     2021/5/21, 22:01:35
System Boot Time:          2022/7/14, 19:58:53
System Manufacturer:       Gigabyte Technology Co., Ltd.
System Model:              Z390 UD
System Type:               x64-based PC
Processor(s):              1 Processor(s) Installed.
                           [01]: Intel64 Family 6 Model 158 Stepping 13 GenuineIntel ~3000 Mhz
BIOS Version:              American Megatrends Inc. F9, 2019/10/15
Windows Directory:         C:\windows
System Directory:          C:\windows\system32
Boot Device:               \Device\HarddiskVolume3
System Locale:             --
Input Locale:              --
Time Zone:                 --
Total Physical Memory:     16,316 MB
Available Physical Memory: 9,312 MB
Virtual Memory: Max Size:  22,716 MB
Virtual Memory: Available: 12,161 MB
Virtual Memory: In Use:    10,555 MB
Page File Location(s):     C:\pagefile.sys

Q2. version of MySQL data (I assume 5.7.2 but select @@version would be
helpful)
A: 5.7.34   (Have you seen the question of stackoverflow i offered before, they're this version either, so this maybe a bug version i think )

Q3.crash log  ( my crash happened in 2022-07-10T01:39:24.066454Z ... maybe earlier or later)
It appends to Files .

4.if your MySQL Server is running on some unix-like system, a core dump
would be required too
A: mysql server run's on my win10 operating system.

THANK FOR U PATIENCE SO MUCH!!! MY LOG IS REALLY TOO LONG!

===============================Segment 2 end===================================

===============================Segment 3 start=================================
Segment 3 is for second reply.

Q:Not sure if you are doing a SELECT from table to download table data to
your client or you are doing INSERT into table to download data from
your client into the table.

A: I'm doing insert ignore into xxx_table values
(xx,xx,xx,xx,xx,xx),(xx,xx,xx,xx,xx,xx)
(xx,xx,xx,xx,xx,xx),(xx,xx,xx,xx,xx,xx)
(xx,xx,xx,xx,xx,xx),(xx,xx,xx,xx,xx,xx)
... about 200 lines

2: If you are doing
INSERT, you DO NOT want to insert data into corrupted table. First
repair this table, rebuild it, and then INSERT data into it.
A: I really aprroved to your suggestion.

3. I just did a dump and restore of 47G ( so 10x
more data, both select from database to disk scp to another server

A: Yeah , I know that. Do Your 47G data stores in one table ? Really amazing. Our company suggest we only save about 5,000,000 line data in one table to make index more faster. So I didn't store in one table many data before. I think maybe oracle 11g or 19c can hold it before.

4.https://dev.mysql.com/doc/refman/5.7/en/innodb-recovery.html
A: 
     1.this website is very detail . Really thank u for your searching.  But when i use "check table" to check . Mysql service will immediate shutdown .
     2. I use the recovery mode last night , but it doesn't effective. https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html . I try 123456 all mode last night... Oh.. really tired...

=================================END======================================\
Really thank u... I really want to know the real reason, I'll try to stick on the bbs you offered . I think if it really happens in such as financial system in company . It is really a fatal problem i think . Especially nowadays we use k8s ,docker . There're really mountains of server machine . If happens question like this in master node . It may cause some data loss.

Kind regards!!!
[14 Jul 2022 14:15] MySQL Verification Team
Hi,

>  Mysql Server crach caused by operating system sleeping is only my guess. Because I didn't do any complex action to the table . Only insert many times. So I think maybe the crash is caused by other info .

Since you can reproduce this and we cannot, do a following test. Disable sleep on your desktop computer that runs MySQL Server and see if the problem will go away. Before you do this test make sure you check all tables in your database. So completely disable sleep. On top of disabling the sleep you can install something like ampfetamine https://apps.microsoft.com/store/detail/amphetamine/9NBLGGH4WHSP?hl=en-us&gl=US that will force the Windows to stay up. After you test this you can then return your sleep settings to what they were, of course.

kind regards
[14 Jul 2022 14:20] MySQL Verification Team
Hi,

>  But when i use "check table" to check . Mysql service will immediate shutdown .

This shows your datadir is corrupted, that table is corrupted. You cannot keep using that server as is with corrupted data. All the tests you do with corrupted data are invalid as any write / read from corrupted page will result in crash.

Follow the recovery procedure (basically you dump your data piece by piece till you get to the corrupted part, then you skip those corrupted records and dump rest of the table, piece by piece) from documentation and dump all your data, reinstall that mysql server with clean datadir, make sure you have no antivirus touching this datadir (99.9% of data corruption on windows is caused by antivirus, so you need to tell your antivirus to not touch your mysql data files) and then restore your data. Make sure you make mysqldump type backup, not binary backup, as binary backup can still be corrupted.
[14 Jul 2022 14:51] n n
mysqldump -uroot -pddd databasename tablename --where="limit 1 " > F:\mysqlql\whole1990to2000sql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect

I try several ways and several "WHERE" condition to dump it. Every I dump it will lose connect.

Maybe I can try not sleeping some day... Or maybe you can have a teamviewer link to my computer to see detail question ?
[15 Jul 2022 13:27] n n
Hi , I've delete that copy that table structure and delete that table. It take me too much time to recover one night download data. So I delete it. I still want to know what real reason cause it. You give me lots of answer . Really thank for your patience . I know it is hard to check real reason from limited logs. I 'm trying to download it again . Hope this would not happen again 😂 . Best regard to U!
[15 Jul 2022 13:39] n n
Oh , I don't want to use this old version any more . I think i need to update to mysql 8.0. I like 5.7 version because it doesn't open strict sql gramma check default. I'm to lazy to find that option to close checking. Such as "GROUP BY" etc. And mysql 5.7 is the first database I've use . So I really love it. But this unstable make me doubt at it. And your official page suggested customer to use and ask latest version , said it have repair mountains of bug . So I think it's smart to use efficient version rather than the cherished one. 
emmm , I've install a 8.0 by installer in my computer before . And this 5.7 version is a zip , I'm going to unzip a 8.0 version to cover this package and initialize to load elder 5.7 data. Will two 8.0 version conflicted ? Is my operation right?
[15 Jul 2022 14:05] n n
The software you recommend is really beautiful. I think I should see more application on microsoft store now. Really helpful and well design.
[15 Jul 2022 14:12] n n
I'm opening Amphetamine now , waiting for the result after 8 hours like that day now. I'll give you my feedback after . Hope this time will success.