I have two Wordpress websites running off a single droplet. They are on LEMP stack.

I hadn’t checked them in a while but when I did, I found that one of them displayed the message: Error Establishing Database Connection after disk filled up

I opened up the Digital Ocean control panel and realized that I was using 100% disk space. Upon some investigation, I found that it was because a Wordpress plugin called ‘Updraft’ was saving larger and larger db backups in the content folder.

After backing up those backups to my computer, I deleted them all from the server.

Then I went to this tutorial and started following the steps.

sudo systemctl start mysql Didn’t work.

I then created /etc/my.cnf and added the lines

[mysql]
innodb_force_recovery = 6

I still got the same error. Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

This is the result of systemctl status mysql.service:

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Mon 2021-01-25 02:01:19 UTC; 12s ago
  Process: 12744 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
  Process: 12736 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 12744 (code=exited, status=2);         : 12745 (mysql-systemd-s)
    Tasks: 2
   Memory: 224.0K
      CPU: 438ms
   CGroup: /system.slice/mysql.service
           └─control
             ├─12745 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─12789 sleep 1

This is the result of sudo journalctl -xe:

Jan 25 02:02:20 BlueAdapterS2 audit[12928]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/etc/
Jan 25 02:02:20 BlueAdapterS2 kernel: audit: type=1400 audit(1611540140.858:131): apparmor="DENIED" operation="open" prof
Jan 25 02:02:21 BlueAdapterS2 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Jan 25 02:02:25 BlueAdapterS2 kernel: [UFW BLOCK] IN=eth0 OUT= MAC=b2:a6:ac:23:31:4a:80:7f:f8:66:e8:30:08:00 SRC=34.77.93
Jan 25 02:02:32 BlueAdapterS2 kernel: [UFW BLOCK] IN=eth0 OUT= MAC=b2:a6:ac:23:31:4a:18:2a:d3:e0:df:f0:08:00 SRC=194.26.2
Jan 25 02:02:49 BlueAdapterS2 sudo[13008]:      kev : TTY=pts/0 ; PWD=/home/kev ; USER=root ; COMMAND=/bin/journalctl -xe
Jan 25 02:02:49 BlueAdapterS2 sudo[13008]: pam_unix(sudo:session): session opened for user root by kev(uid=0)

Finally, here is part of the mysql log:

2021-01-25T01:23:43.119785Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2021-01-25T01:23:43.119858Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2021-01-25T01:23:43.284921Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-25T01:23:43.287155Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.31-0ubuntu0.16.04.1) starting as process 5405 ...
2021-01-25T01:23:43.293301Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-01-25T01:23:43.293342Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-25T01:23:43.293351Z 0 [Note] InnoDB: Uses event mutexes
2021-01-25T01:23:43.293358Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-01-25T01:23:43.293365Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2021-01-25T01:23:43.293373Z 0 [Note] InnoDB: Using Linux native AIO
2021-01-25T01:23:43.293664Z 0 [Note] InnoDB: Number of pools: 1
2021-01-25T01:23:43.293780Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-01-25T01:23:43.295277Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-01-25T01:23:43.308704Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-25T01:23:43.310582Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-25T01:23:43.322585Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-01-25T01:23:43.323343Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 32014615639
2021-01-25T01:23:43.445307Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 32019858432
2021-01-25T01:23:43.476823Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 32022746925
2021-01-25T01:23:43.476920Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-01-25T01:23:43.476926Z 0 [Note] InnoDB: Starting crash recovery.
2021-01-25T01:23:43.505719Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 2021-01-25 01:23:43 0xa42a8b40  InnoDB: Assertion failure in thread 2754251584 in file log0recv.cc line 2078
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
01:23:43 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
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 = 75719 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 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x3c)[0x8a906ec]
/usr/sbin/mysqld(handle_fatal_signal+0x32a)[0x836d58a]
[0xb7714bb4]
[0xb7714bd1]
/lib/i386-linux-gnu/libc.so.6(gsignal+0x39)[0xb7094eb9]
/lib/i386-linux-gnu/libc.so.6(abort+0x157)[0xb7096417]
/usr/sbin/mysqld[0x8343b13]
/usr/sbin/mysqld[0x8b7cfb0]
57 58 59 /usr/sbin/mysqld(_Z22recv_recover_page_funcmP11buf_block_t+0x863)[0x8b7dbc3]
/usr/sbin/mysqld(_Z20buf_page_io_completeP10buf_page_tb+0x3d9)[0x8d04b39]
/usr/sbin/mysqld(_Z12fil_aio_waitm+0x125)[0x8d83695]
/usr/sbin/mysqld(io_handler_thread+0xc1)[0x8c5f261]
/lib/i386-linux-gnu/libpthread.so.0(+0x6295)[0xb740f295]
/lib/i386-linux-gnu/libc.so.6(clone+0x6e)[0xb71501ae]
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.

I’m at a complete loss on how to get the database up and running again. Any help would be very much appreciated.

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
2 answers

@kevkix

thanks for posting this. I’ve got some bad news. The output tells me that the database instance suffered some corruption. When we trouble shoot an instance that won’t start we often iterate over the various levels of innodb_force_recovery. As you’ve already gotten to level 6 without the instance being started successfully it’s really going to require 1 of 2 actions to get you running again.

  1. Contact a specialist Innodb data recovery company to do a deep data recovery. This is certainly not a cheap route. I’ve seen 10s of 1000s of $ spent on very little success. I could only recommend this route if the data on that filesystem is extremely important and irrecoverable.

  2. Use your latest backup to recover the instance. This is the kind of situation the backups are taken for. With the disk filling up, your latest backup may be somewhat out of date depending on how close to the disk-fill-event the backup was taken.

Also;

I would recommend spending a little time getting your server configuration reviewed and tuned. You might benefit from having a replica available for your application too, but at the very least you should enable the binary logging feature to permit a point-in-time recovery if needed in the future.

Let me know if I can be any more help.

Good luck!

Andy

  • Ah that’s a shame. I did have the automatic backup turned on but they don’t go back far enough unfortunately.

    I guess I will live with the loss.

    Thanks very much for your help.

Hello @kevkix, I’m Andy, one of DigitalOcean’s MySQL DBAs. I look after the internal MySQL databases and I think I can assist you today.

Can you attempt to start the mysqld process without the ‘innodbforcerecovery = 6’ set, then post the mysql error log output for that startup attempt in this thread.

I notice that journalctl ouput shows “INVALIDARGUMENT” in the mysqld line, it could point to a configuration problem. Can you also paste the contents of the mysql configuration file?

This should get us started.

Regards

A

  • Thanks so much for helping.

    Here is the last entry of the log after deleting the innodbforcedrecovery command:

    2021-01-26T06:05:44.612621Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
    2021-01-26T06:05:44.612680Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
    2021-01-26T06:05:44.777680Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2021-01-26T06:05:44.780095Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.31-0ubuntu0.16.04.1) starting as process 3426 ...
    2021-01-26T06:05:44.784398Z 0 [Note] InnoDB: PUNCH HOLE support available
    2021-01-26T06:05:44.784421Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2021-01-26T06:05:44.784429Z 0 [Note] InnoDB: Uses event mutexes
    2021-01-26T06:05:44.784436Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2021-01-26T06:05:44.784443Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
    2021-01-26T06:05:44.784450Z 0 [Note] InnoDB: Using Linux native AIO
    2021-01-26T06:05:44.784751Z 0 [Note] InnoDB: Number of pools: 1
    2021-01-26T06:05:44.784856Z 0 [Note] InnoDB: Not using CPU crc32 instructions
    2021-01-26T06:05:44.786653Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    2021-01-26T06:05:44.796993Z 0 [Note] InnoDB: Completed initialization of buffer pool
    2021-01-26T06:05:44.798785Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2021-01-26T06:05:44.811901Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
    2021-01-26T06:05:44.813113Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 32014615639
    2021-01-26T06:05:44.912143Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 32019858432
    2021-01-26T06:05:44.947294Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 32022746925
    2021-01-26T06:05:44.947397Z 0 [Note] InnoDB: Database was not shutdown normally!
    2021-01-26T06:05:44.947406Z 0 [Note] InnoDB: Starting crash recovery.
    2021-01-26T06:05:44.976170Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Progress in percent: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 2021-01-26 06:05:44 0xa4316b40  InnoDB: Assertion failure in thread 2754702144 in file log0recv.cc line 2078
    InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)
    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.7/en/forcing-innodb-recovery.html
    InnoDB: about forcing recovery.
    06:05:44 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.
    Attempting to collect some information that could help diagnose the problem.
    As this is a crash and something is definitely wrong, the information
    collection process might fail.
    
    key_buffer_size=16777216
    read_buffer_size=131072
    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 = 75719 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 0x30000
    /usr/sbin/mysqld(my_print_stacktrace+0x3c)[0x8a906ec]
    /usr/sbin/mysqld(handle_fatal_signal+0x32a)[0x836d58a]
    [0xb7782bb4]
    [0xb7782bd1]
    /lib/i386-linux-gnu/libc.so.6(gsignal+0x39)[0xb7102eb9]
    /lib/i386-linux-gnu/libc.so.6(abort+0x157)[0xb7104417]
    /usr/sbin/mysqld[0x8343b13]
    /usr/sbin/mysqld[0x8b7cfb0]
    /usr/sbin/mysqld(_Z22recv_recover_page_funcmP11buf_block_t+0x863)[0x8b7dbc3]
    57 58 59 /usr/sbin/mysqld(_Z20buf_page_io_completeP10buf_page_tb+0x3d9)[0x8d04b39]
    /usr/sbin/mysqld(_Z12fil_aio_waitm+0x125)[0x8d83695]
    /usr/sbin/mysqld(io_handler_thread+0xc1)[0x8c5f261]
    /lib/i386-linux-gnu/libpthread.so.0(+0x6295)[0xb747d295]
    /lib/i386-linux-gnu/libc.so.6(clone+0x6e)[0xb71be1ae]
    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.
    

    Here is the contents of /etc/mysql/my.cnf:

    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    # 
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mysql.conf.d/
    

    Contents of /etc/mysql/conf.d/mysql.cnf:

    [mysql]
    

    Contents of /etc/mysql/conf.d/mysqldump.cnf:

    [mysqldump]
    quick
    quote-names
    max_allowed_packet  = 16M
    

    Contents of etc/mysql/mysql.conf.d/mysqld.cnf:

    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    # 
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    [mysqld_safe]
    socket      = /var/run/mysqld/mysqld.sock
    nice        = 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket      = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir     = /usr
    datadir     = /var/lib/mysql
    tmpdir      = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address        = 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer_size     = 16M
    max_allowed_packet  = 16M
    thread_stack        = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover-options  = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit   = 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries   = /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id      = 1
    #log_bin            = /var/log/mysql/mysql-bin.log
    expire_logs_days    = 10
    max_binlog_size   = 100M
    #binlog_do_db       = include_database_name
    #binlog_ignore_db   = include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    

    Contents of etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf:

    [mysqld_safe]
    syslog
    

    Let me know if there’s anything else I can provide. Thanks again.