[solved] MySQL service issue - corrupted ibdata1 file

aoz · February 20, 2017, 1:27pm

yunohost: 2.5.6
yunohost.admin:  2.5.1
moulinette: 2.5.2
ssowat: 2.6.1

Hi,

This weekend, I migrated from owncloud to nextcloud. I also upgraded yunohost from 2.5.5 to 2.5.6.

Since that, baikal seems broken, accessing to myDomain.fr/baikal or myDomain.fr/baikal/admin displays:

Baïkal was not able to establish a connexion to the configured MySQL database (as configured in Specific/config.system.php).

edit: nextcloud is also dead “HTTP ERROR 500” (it was working yesterday after the migration from owncloud…)
Many errors in the nginx domain log /var/log/nginx/monDomain.fr-error.log

Could it be linked to what is described in this thread? A conflict between nextcloud and baikal?

If I remove baikal and nextcloud before to re-install them, will I lost my data (contacts, calendars, files)?

aoz · February 21, 2017, 9:42am

I have the feeling that my problem is actually linked to a corruption of a mySQL table, due to an electrical power shutdown (that actually occurred yesterday, just before the problems started).

If I try to connect to mysql, I get the following message:

# mysql
ERROR 2002 (HY000): Can t connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

A similar issue can be found in this discussion.

I found that my corrupted table is this one:

# myisamchk -a *.MYI  | grep error
/var/lib/mysql/mysql/#sql-3e94_1.MYI

Any idea to fix/investigate the issue?

Bram · February 21, 2017, 1:59pm

I don’t know how close to your this is, but here is a wiki page on how I’ve managed to fixed a self-corrupted mysqldb on internet cubes : https://wiki.labriqueinter.net/howto:fix_self_corrupt_mysql

Warning: this is quite long to do and quite technical

aoz · February 21, 2017, 4:09pm

Indeed, it seems tricky. But what could be the alternative? Does anyone knows for what purpose is the corrupted table used for? Could I reset/erase it and what would I lose?

edit: after investigation, it seems that the table which was corrupted (#sql-3e94_1.MYI) was a useless temporary file. I removed that file, and the other ones seem not corrupted (no output when running “myisamchk -a *.MYI | grep error”).

Nevertheless, mysql service does not starts:

# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Tue 2017-02-21 19:50:04 CET; 46min ago
  Process: 638 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Feb 21 19:50:04 myComputer mysql[638]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Feb 21 19:50:04 myComputer systemd[1]: mysql.service: control process exited, code=exited status=1
Feb 21 19:50:04 myComputer systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Feb 21 19:50:04 myComputer systemd[1]: Unit mysql.service entered failed state.

And if I tail the error log:

# tail -n 40 /var/lib/mysql/myComputer.err
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.

key_buffer_size=16384
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49469 K  bytes of memory
Hope that is ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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...
stack_bottom = 0 thread_stack 0x20000
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.
170221 20:32:16 [Note] Plugin 'FEDERATED' is disabled.
170221 20:32:16 InnoDB: The InnoDB memory heap is disabled
170221 20:32:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170221 20:32:16 InnoDB: Compressed tables use zlib 1.2.8
170221 20:32:16 InnoDB: Using Linux native AIO
170221 20:32:16 InnoDB: Initializing buffer pool, size = 128.0M
170221 20:32:16 InnoDB: Completed initialization of buffer pool
170221 20:32:16 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1117420816
170221 20:32:16  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1117424458
170221 20:32:16  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
170221 20:32:16  InnoDB: Waiting for the background threads to start

/var/log/mysql.log and /var/log/mysql.err are empty files.

I tried to :
> dpkg-reconfigure mysql-server-5.5
> grep mysql /var/log/syslog → Debian paste error

And if I try the @Bram procedure (when doing mysqld --skip-grant-tables):

 # su -s /bin/bash -c "/usr/sbin/mysqld --skip-grant-tables" - mysql 
170221 22:09:47 [Note] /usr/sbin/mysqld (mysqld 5.5.54-0+deb8u1) starting as process 10695 ...
170221 22:09:47 [Note] Plugin 'FEDERATED' is disabled.
170221 22:09:47 InnoDB: The InnoDB memory heap is disabled
170221 22:09:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170221 22:09:47 InnoDB: Compressed tables use zlib 1.2.8
170221 22:09:47 InnoDB: Using Linux native AIO
170221 22:09:47 InnoDB: Initializing buffer pool, size = 128.0M
170221 22:09:47 InnoDB: Completed initialization of buffer pool
170221 22:09:47 InnoDB: highest supported file format is Barracuda.
170221 22:09:47  InnoDB: Waiting for the background threads to start
170221 22:09:48 InnoDB: 5.5.54 started; log sequence number 1117424458
170221 22:09:48 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
170221 22:09:48 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
170221 22:09:48 [Note] Server socket created on IP: '0.0.0.0'.
170221 22:09:48 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.54-0+deb8u1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
170221 22:09:48  InnoDB: Assertion failure in thread 1720050768 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
21:09:48 UTC - mysqld got signal 6 ;
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.
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.

key_buffer_size=16384
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49469 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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...
stack_bottom = 0 thread_stack 0x20000
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.

What else could I investigate? Thanks for any support.

aoz · February 22, 2017, 3:03pm

The problem is coming from a corrupted “/var/lib/mysql/ibdata1” file.

with the current ibdata1 file, the service does not start (Even “mysqld_safe” can not start).
if I erase the ibdata1 file, the service starts (a new ibdata1 file is created) but it is logged that many tables are missing (for baikal, nextcloud, rainloop…). Of course.

I have a save of the /var/lib/mysql/ files (from two weeks ago). So I could restore them. BUT, in between, I have migrated from owncloud to nextcloud.

So I think I should :

remove Nextcloud
install Owncloud (from github)
restore my previous mysql files
upgrade to Nextcloud

Do you agree?

Bram · February 22, 2017, 8:36pm

Sorry, that’s really out of my field of knowledge

There is apparently a procedure to attempt to restore ibdata1 https://dba.stackexchange.com/questions/45991/mysql-ibdata1-corrupt

I guess your approach could work but I’m really not sure about it, sorry

aoz · February 23, 2017, 1:31pm

I did it. It worked!

Bram · February 23, 2017, 8:03pm

The stackoverflow procedure?

aoz · February 26, 2017, 9:03pm

No, the one I described in my previous message.

Not so obvious, actually. But I was finally able to get my system back with nextcloud working. To be honest, I lost some of my data (I use nextcloud only to sync files and for the calendars).