4

I am working with Ubuntu 18.04 and MySQL Server - 8.0.18

Suddenly today MySQL can't start anymore

With the sudo service mysql status command appears:

sudo service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Sat 2019-11-02 17:05:23 -05; 1min 44s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1659 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
  Process: 1620 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 1659 (code=exited, status=2)
   Status: "Server startup in progress"

Nov 02 17:05:22 asus2016-vb01 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Nov 02 17:05:22 asus2016-vb01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Nov 02 17:05:22 asus2016-vb01 systemd[1]: Failed to start MySQL Community Server.
Nov 02 17:05:23 asus2016-vb01 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Nov 02 17:05:23 asus2016-vb01 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 9.
Nov 02 17:05:23 asus2016-vb01 systemd[1]: Stopped MySQL Community Server.
Nov 02 17:05:23 asus2016-vb01 systemd[1]: mysql.service: Start request repeated too quickly.
Nov 02 17:05:23 asus2016-vb01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Nov 02 17:05:23 asus2016-vb01 systemd[1]: Failed to start MySQL Community Server.

With the sudo service mysql restart command appears: 

sudo service mysql restart
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

Following and executing the two suggested commands:

  • systemctl status mysql.service
  • journalctl -xe

Therefore: sudo systemctl status mysql.service

sudo systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Sat 2019-11-02 17:08:21 -05; 38s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2633 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
  Process: 2594 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 2633 (code=exited, status=2)
   Status: "Server startup in progress"

Nov 02 17:08:20 asus2016-vb01 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Nov 02 17:08:20 asus2016-vb01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Nov 02 17:08:20 asus2016-vb01 systemd[1]: Failed to start MySQL Community Server.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: Stopped MySQL Community Server.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: mysql.service: Start request repeated too quickly.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: Failed to start MySQL Community Server.

And with journalctl -xe

-- Unit mysql.service has begun starting up.
Nov 02 17:08:20 asus2016-vb01 audit[2632]: AVC apparmor="STATUS" operation="profile_replace" info="same as current profile, skipping" profile="unconfined" name="/usr/sbin/mysqld" pid=2632 comm="apparmor_p
Nov 02 17:08:20 asus2016-vb01 audit[2633]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/etc/ssl/openssl.cnf" pid=2633 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=122
Nov 02 17:08:20 asus2016-vb01 audit[2633]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/2633/task/2634/mem" pid=2633 comm="mysqld" requested_mask="r" denied_mask="r" fsuid
Nov 02 17:08:20 asus2016-vb01 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Nov 02 17:08:20 asus2016-vb01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Nov 02 17:08:20 asus2016-vb01 systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has failed.
-- 
-- The result is RESULT.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Nov 02 17:08:21 asus2016-vb01 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
-- Subject: Automatic restarting of a unit has been scheduled

I followed the solution about this post:

I used the innodb_force_recovery = 10 (ten), it playing, because with 1 and 2 did not work

Therefore with the sudo systemctl start mysql command is possible start the server. I can see all the data and users saved in peace.

Now through the sudo systemctl status mysql command I get

sudo systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2019-11-02 17:26:48 -05; 11s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2769 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 2808 (mysqld)
   Status: "Server is operational"
    Tasks: 19 (limit: 4617)
   CGroup: /system.slice/mysql.service
           └─2808 /usr/sbin/mysqld

Nov 02 17:26:47 asus2016-vb01 systemd[1]: Starting MySQL Community Server...
Nov 02 17:26:48 asus2016-vb01 systemd[1]: Started MySQL Community Server.

Until this point would has no sense this post, but if I do either Alpha or Beta steps:

Alpha (steps): 
sudo systemctl stop mysql
remove innodb_force_recovery = 10
sudo systemctl start mysql         

Beta (steps):
restart the PC, 
(I can confirm `MySQL` was started), 
remove innodb_force_recovery = 10
restart the PC

the same problem happens again.

Therefore now is mandatory keep the innodb_force_recovery = 10, otherwise it returns to the same problem

Warning:

According with:

The correct range is from 1 to 6, I am assuming 10 is "interpreted" how the maximum value (6)

Is there a way to configure something and remove innodb_force_recovery? It to return to the normal or old configuration where all worked fine (without innodb_force_recovery)

Note the final content of /var/log/mysql/error.log is

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.
2019-11-02T23:24:37.346875Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 1668
2019-11-02T23:24:37.804662Z 1 [ERROR] [MY-012671] [InnoDB] Encryption algorithm support missing: N
2019-11-02T23:24:37.804783Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: log0recv.cc:3563:err == DB_SUCCESS thread 139850038036224
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
23:24:37 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x555c74a041c0
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 = 7f315fd6bce0 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x555c72ab2edd]
/usr/sbin/mysqld(handle_fatal_signal+0x303) [0x555c71b5bc63]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890) [0x7f317319a890]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7) [0x7f31712d3e97]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141) [0x7f31712d5801]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x2ec) [0x555c72d6015c]
/usr/sbin/mysqld(+0x2074038) [0x555c72c1d038]
/usr/sbin/mysqld(recv_recovery_from_checkpoint_start(log_t&, unsigned long)+0x660) [0x555c72c26e40]
/usr/sbin/mysqld(srv_start(bool, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)+0x1741) [0x555c72d0e701]
/usr/sbin/mysqld(+0x1fe3f25) [0x555c72b8cf25]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x9f) [0x555c72868f5f]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x5b4) [0x555c72a7d8f4]
/usr/sbin/mysqld(+0x1076dc5) [0x555c71c1fdc5]
/usr/sbin/mysqld(+0x24182b9) [0x555c72fc12b9]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7f317318f6db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f31713b688f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 1
Status: NOT_KILLED

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 am working with VirtualBox - Ubuntu is a guest. It had been working for more of 3 months, now suddenly this situation appeared.

More Info:

One

As requested desertangels

sudo /usr/sbin/mysqld
2019-11-03T00:07:06.466871Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 2307
2019-11-03T00:07:06.470277Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2019-11-03T00:07:06.472544Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-11-03T00:07:06.475967Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.

Two

Executing cat /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
port            = 3307
bind-address    = 0.0.0.0
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log

Three

BTW: just in case, with mysql -u root -p appears:

mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Of course, the server is not running

Manuel Jordan
  • 1,768
  • 7
  • 30
  • 50
  • try to run without systemd service run normal via terminal # sudo /usr/sbin/mysqld and see what will output . –  Nov 02 '19 at 23:57
  • @desertangels done, added the output requested – Manuel Jordan Nov 03 '19 at 00:10
  • i thought it will sort more details , strange , check /etc/mysql/conf.d/mysql.cnf , the configuration file lock for user and bind-address sections –  Nov 03 '19 at 00:14
  • try to change bind-address to activate the listen socket –  Nov 03 '19 at 00:15
  • Added the .cnf data – Manuel Jordan Nov 03 '19 at 00:18
  • I remember now that I had this situation since I lost internet connection, and I restarted Ubuntu. Seems behind the scenes MySQL was affected – Manuel Jordan Nov 03 '19 at 00:20
  • yes check mysql.cnf i suggest to run as user=mysql bind-address=127.X.X.X change it without to conflict others listen sockets . –  Nov 03 '19 at 00:20
  • I will try it, but I use the current approach to let other machines access this MySQL instance. – Manuel Jordan Nov 03 '19 at 00:21
  • i suggest 2 importants commands lines to manage the mysql # sudo mysql_install_db , # sudo mysql_secure_installation the 1st for create directory layout the 2nd to secure mysql and remove dangerous defaults . –  Nov 03 '19 at 00:23
  • app-armor is also blocking the start. AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" – nobody Nov 03 '19 at 08:46
  • After btrfs-convert root file system from ext4 to btrfs, my mysql server encountered same error (MY-012671 MY-013183) when starting it (systemd, Fedora 35). – LiuYan 刘研 Oct 20 '21 at 01:06

7 Answers7

1

If your problem not fix, you can try check more problem.

Maybe your mysql crash, check log in

sudo cat /var/log/mysql/error.log

Or check

sudo ls -l /var/crash
Pablo Bianchi
  • 15,657
Bobi arip
  • 11
1

I had the same issue and after hours I found this: I added this to path /etc/mysql/my.cnf

[mysql]
innodb_force_recovery = 1

If you use mysqld use this:

[mysqld]
innodb_force_recovery = 1
Kamuran Sönecek
  • 111
0

Simple run update command: sudo apt-get update && sudo apt-get upgrade. It auto update permissions necessary for mysql.

It solves my problem.

Pablo Bianchi
  • 15,657
Sidedevelopers
  • 1
0

I resolved this by updating the /etc/my.cnf from

log-error = /var/log/mysqld.err

to

log-error = /var/log/mysql/mysqld.err
xelat
  • 101
0

In some cases, a low-RAM server (512 Mb in my case) might give you similar results (I haven't compared all the details from logs, so I don't say same results). In such a case you might want to make a swap file (make sure that this is fine for your hardware: I've heard that SSDs are not doing well when being used for a swap file).

The following commands will create a swap file of 4 Gb size:

sudo fallocate -l 4G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile

Then, start the mysql service:

sudo systemctl start mysql

If that was the case, there will be no error. Otherwise, you can run the following commands to delete swap:

sudo swapoff -a
sudo rm /swapfile
Boris N
  • 101
0

Make sure you have this file /etc/mysql/my.cnf.

When I have created and add this : [mysqld] innodb_force_recovery = 1

It's work for me.

user1679888
  • 9
-1

give access to /var/log/mysql/* for mysql

sudo chown mysql:mysql /var/log/mysql/*
Tejas Lotlikar
  • 2,945
  • 5
  • 17
  • 26
Ali sadikin
  • 1