How to fix error: Lost connection to MySQL server during query?

Question

linux

There is an information site with a knowledge base.
One fine morning, according to the metric, I notice that attendance has dipped significantly over the past day.
I go to the site - an error connecting to the database. The daily backup was made the current night, that is, it already contains an error.
Mysql 5.7.23
Ubuntu 16.04.1 Trying
to connect to MySQL. Any query to one table produces an error:
Lost connection to MySQL server during query?

MySQL log

2018-08-20T05:10:47.022666Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-08-20T05:10:47.022728Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2018-08-20T05:10:47.197039Z 0 [Warning] option 'net_buffer_length': unsigned value 536870912 adjusted to 1048576
2018-08-20T05:10:47.197073Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-20T05:10:47.199115Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-0ubuntu0.16.04.1) starting as process 15650 ...
2018-08-20T05:10:47.203962Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-08-20T05:10:47.203985Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-08-20T05:10:47.203995Z 0 [Note] InnoDB: Uses event mutexes
2018-08-20T05:10:47.204001Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-08-20T05:10:47.204007Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-08-20T05:10:47.204012Z 0 [Note] InnoDB: Using Linux native AIO
2018-08-20T05:10:47.204424Z 0 [Note] InnoDB: Number of pools: 1
2018-08-20T05:10:47.204585Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2018-08-20T05:10:47.206665Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-08-20T05:10:47.217553Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-08-20T05:10:47.220127Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-08-20T05:10:47.232641Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-08-20T05:10:47.234316Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 930035778
2018-08-20T05:10:47.234328Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 930035787
2018-08-20T05:10:47.234335Z 0 [Note] InnoDB: Database was not shutdown normally!
2018-08-20T05:10:47.234340Z 0 [Note] InnoDB: Starting crash recovery.
2018-08-20T05:10:47.359530Z 0 [ERROR] InnoDB: Space ID in fsp header is 1702065259, but in the page header it is 1870161781.
2018-08-20T05:10:47.359555Z 0 [ERROR] InnoDB: Data file './kubium/game.ibd' uses page size 0, but the innodb_page_size start-up parameter is 16384
2018-08-20T05:10:47.359578Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-08-20T05:10:47.359586Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-08-20T05:10:47.359605Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-08-20T05:10:47.359613Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `kubium/game`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2018-08-20T05:10:47.359626Z 0 [Warning] InnoDB: Ignoring tablespace `kubium/game` because it could not be opened.
2018-08-20T05:10:47.440722Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2018-08-20T05:10:47.440745Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-08-20T05:10:47.440799Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-08-20T05:10:47.498664Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-08-20T05:10:47.499709Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-08-20T05:10:47.499724Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-08-20T05:10:47.500094Z 0 [Note] InnoDB: Waiting for purge to start
2018-08-20T05:10:47.550321Z 0 [Note] InnoDB: 5.7.23 started; log sequence number 930035787
2018-08-20T05:10:47.550589Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-08-20T05:10:47.550723Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-08-20T05:10:47.554667Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180820  8:10:47
2018-08-20T05:10:47.556929Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-08-20T05:10:47.556958Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2018-08-20T05:10:47.556970Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2018-08-20T05:10:47.557020Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2018-08-20T05:10:47.566786Z 0 [Note] Event Scheduler: Loaded 0 events
2018-08-20T05:10:47.567067Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.23-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2018-08-20T05:10:48.024221Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
*** Error in `/usr/sbin/mysqld': malloc(): memory corruption: 0x00007fcbfc124080 ***
======= Backtrace: =========
/lib/x86_64-linux-gnu/libc.so.6(+0x777e5)[0x7fcc3d2537e5]
/lib/x86_64-linux-gnu/libc.so.6(+0x8213e)[0x7fcc3d25e13e]
/lib/x86_64-linux-gnu/libc.so.6(__libc_malloc+0x54)[0x7fcc3d260184]
/usr/sbin/mysqld(my_malloc+0x88)[0xe8fdd8]
/usr/sbin/mysqld(alloc_root+0xe9)[0xe8b879]
/usr/sbin/mysqld(strmake_root+0x16)[0xe8bc16]
/usr/sbin/mysqld(_Z14open_table_defP3THDP11TABLE_SHAREj+0xffd)[0xcf010d]
/usr/sbin/mysqld(_Z15get_table_shareP3THDP10TABLE_LISTPKcmjPij+0x16f)[0xbf357f]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0xb2a)[0xbf46ca]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x79b)[0xbfb9bb]
/usr/sbin/mysqld(_Z21open_tables_for_queryP3THDP10TABLE_LISTj+0x59)[0xbfc169]
/usr/sbin/mysqld[0xcae17f]
/usr/sbin/mysqld(_Z14get_all_tablesP3THDP10TABLE_LISTP4Item+0x70e)[0xcaea7e]
/usr/sbin/mysqld[0xc9964c]
/usr/sbin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x195)[0xcaaab5]
/usr/sbin/mysqld(_ZN4JOIN14prepare_resultEv+0x95)[0xc8efb5]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0xa8)[0xc1dbf8]
/usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x233)[0xc8fa53]
/usr/sbin/mysqld[0x753f3b]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x465b)[0xc5170b]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3ad)[0xc53dbd]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x102a)[0xc54efa]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x1c7)[0xc563b7]
/usr/sbin/mysqld(handle_connection+0x288)[0xd18718]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xeb3504]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fcc3de4e6ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fcc3d2e341d]

Tried different solutions to the problem. In particular, I increased mysql values:

connect_timeout = 43200
max_allowed_packet = 1024M
net_buffer_length = 512M
innodb_force_recovery = 4

The table has never been reached.
Is there any way to revive her? Thanks

Answer 1

It can be seen that something has come up with the database files and also a memory corruption error.
There appears to be a major glitch in QEMU or caused by a glitch in the host machine. As a result, while the DBMS was running, the database files, including service files, were damaged (at least the files in the /var/lib/mysql directory). It is also worth checking the integrity of the OS itself.
I recommend that you announce the problem to visitors, and in the meantime contact MySQL + InnoDB restore specialists, since there was no good backup.
It makes sense to create a TAR archive with compression of the /var/lib/mysql directory with the hope of subsequent data recovery.

Answer 2

Judging by:
1. make a copy of /var/lib/mysql to another drive
2. Explore and solve:
2.1. option 1 - broken memory - run memtest, maybe the system is overheating? we eliminate or, if both problems are not confirmed, then we move on. Although it looks like a third-party virtual server. But there may be a problem.
If the problem is with RAM, then you can test it inside the OS by creating compressed archives and checking their integrity; in case of problems with RAM, checksum errors will appear sooner or later.
2.2. option 2 - either the data files are corrupted, and mysql becomes bad because of the crooked code. files can be corrupted by incorrect server shutdown or problems with the block device:

2018-08-20T05:10:47.359613Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `kubium/game`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2018-08-20T05:10:47.359626Z 0 [Warning] InnoDB: Ignoring tablespace `kubium/game` because it could not be opened.

- this may be an indirect sign of file problems.
2.2.1. check the state of smartctl block devices - the presence of offline uncorrectable or relocated sectos - may be the cause of data corruption - replacement of the drive. For someone else's hosting, this is not available. You can check indirectly by reading the /dev/vda
2.2.2 block device. we fsck the file system, the presence of errors in the file system may indicate damage to the contents of the database files. we fix and pray that the most important files were not affected.
2.2.3. check the structure of innodb/myisam files, for this we use standard diagnostic tools or auxiliary utilities, for example "Percona Data Recovery Tool for InnoDB can help recover corrupted or deleted InnoDB tables. https://launchpad.net/percona-data-recovery-tool- f..."if there are problems, we try to fix them. A simple old way to solve some problems is to dump the database into a sql
file, and import it again into the database. You can rename the old one.
solve
2.3 option 3 - similar problems can be observed when sticking binary database files from a more recent version of mysql - check this
version.You can try to update the mysql version or change it to mariadb, maybe some problems have already been solved
. when the free RAM in the system is exhausted, the OOM Killer is launched, which kills the processes in the system, it could well have killed the mysql process right in the middle of a critical change to the database files.This can be found in the logs.

Answer 3

memory corruption: 0x00007fcbfc124080
Possibly broken memory