CODEWITHSUNDEEP
mysqlmariadb
Neo Anderson
Neo Anderson

Reputation: 648

MariaDb Gone Away On Spesefic Row


in my database when select from 2 of 10 tables, in spesefic rows , i give this message :

ERROR 2006 (HY000): MySQL server has gone away

for example whene i try :

select * from records where id=238

every thing is ok , and even this:

select * from records where id=270

is ok.
but when try this one:

select * from records where id=239

return error :
enter image description here

i already search in stackoverflow and google for this and all refrence mention that increace buffer size and...
but i dont think this problem relate to this subjects , its seems that some data is currept the table.

p.s : engine that use is innodb
any clue please

Upvotes: 0

Views: 55

Answers (1)

Hector Vido
Hector Vido

Reputation: 852

MySQL closes itself when a operation tries to read from a corrupted InnoDB table.

This is very annoying but is the only way I know by heart.

Is possible to recover all data in most of times, the procedure is the same for MySQL, MariaDB and XtraDB.

There is a lot of explanation on the official manual https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html.

But, essentially, you need to add an option named innodb_force_recovery on MySQL configuration file, restart MySQL and try to SELECT that corrupted data. This options goes from number 1 to 6, but above 3 you could lose some data.

When you found a number that allow you to make a SELECT on that rows, mysqldump the entire table to recover after.

Depending on table state you'll need to remove the file manually before start MySQL without innodb_force_recovery:

rm /var/lib/mysql/myschema/corrupted_table.ibd

This will cause other problems, but once the database start you'll need to drop the old tablespace, create "the same table" in another database and swap the tablespaces:

USE a;
ALTER TABLE corrupted_table DISCARD TABLESPACE;
CREATE DATABASE b;
-- import the dump inside database b
-- fisically move the "ibd" file from "b" to "a"
-- mv /var/lib/mysql/a/corrupted_table.idb /var/lib/mysql/b/corrupted_table.idb
USE a;
ALTER TABLE corrupted_table IMPORT TABLESPACE;
DROP TABLE b.corrupted_table;

Upvotes: 1

Related Questions