Question

MySQL Service is not responding

Posted April 9, 2021 943 views
MySQLUbuntu 20.04

A few days ago I notice that mysql service is not working, I have tried to restart it, but nothing, it sends an error like:

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

but ’/var/run/mysqld/mysqld.sock’ does not exists.

I also tried:

$ 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 Fri 2021-04-09 15:02:51 EDT; 14s ago
    Process: 3222 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 3230 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
   Main PID: 3230 (code=exited, status=2)
     Status: "Server startup in progress"

Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: Failed to start MySQL Community Server.
Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: Stopped MySQL Community Server.
Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Start request repeated too quickly.
Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 09 15:02:51 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: Failed to start MySQL Community Server.

Another thing is that now I have very big files in my data bases, and my disk is 100% full:

$ sudo find / -type f -size +1024M -exec ls -lh {} \;
-rw-r----- 1 mysql mysql 12G Apr  6 16:58 /var/lib/mysql/project_1/wp_options.ibd
-rw-r----- 1 mysql mysql 3.9G Apr  6 15:24 /var/lib/mysql/project_2/wp_options.ibd

I'am new to all of this about systems services and administration, so I don’t know whish steeps follows. I also tried this, but did not work either.

To finish, in /var/log/journal/11a47f9aabdc4fb2bec8c733d6ab3300/ there are files 50M+ of size, can I just delete'em?

Thanks a lot in advance.

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

Hi there,

Indeed if your disk space gets to 100% it is quite possible that MySQL would crash and won’t start after that.

I could suggest clearing some old logs from the /var/log directory. Just so you could have a little bit of space to start MySQL.

Another thing that you might have to do in this case is to upgrade the disk space of your Droplet so that you could have enough disk space for your MySQL data.

Once you have enough disk space, you could run the following MySQL query:

  • First access MySQL:
mysql -u root -p
  • Then optimize the 2 tables:
use project_2;
OPTIMIZE TABLE wp_options;

use project_1;
OPTIMIZE TABLE wp_options;

Note that before running the optimize commands, you would need to have at least the size of the table as available space.
Let me know how it goes!
Regards,
Bobby

  • Hi @bobbyiliev

    The problem still persists.

    I’ve upgrade the droplet size, that’s ok, also clean the logs, etc, but mysql services doesn’t start.

    The error is still the same:
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ’/var/run/mysqld/mysqld.sock’ (2)

    $ sudo service mysql start
    Job for mysql.service failed because the control process exited with error code.
    See "systemctl status mysql.service" and "journalctl -xe" for details.
    
    $ 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 Sun 2021-04-11 13:31:43 EDT; 1min 3s ago
        Process: 2621 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
        Process: 2629 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
       Main PID: 2629 (code=exited, status=2)
         Status: "Server startup in progress"
    
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Failed with result 'exit-code'.
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: Failed to start MySQL Community Server.
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: Stopped MySQL Community Server.
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Start request repeated too quickly.
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: mysql.service: Failed with result 'exit-code'.
    Apr 11 13:31:43 ubuntu-s-1vcpu-1gb-sfo2-01 systemd[1]: Failed to start MySQL Community Server.
    

    What should I do now?

    Thanks a lot…

    • Hi there @themingisprose,

      To me it looks like that your MySQL service is stuck in a crash loop as the status output that you’ve shared indicates that the status is: “Server startup in progress”.

      I would recommend checking your MySQL error log for more information on what might be wrong.

      Also, I could suggest checking your /var/log/syslog log for more information as well.

      Feel free to share the errors from the logs here so that I could try to advise you further.

      Regards,
      Bobby

      • Ok, this is what I have in /var/log/mysql/mysql_error.log:

        2021-04-13T15:09:40.421146Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 18084                            
        2021-04-13T15:09:40.432286Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.                                                                     
        2021-04-13T15:09:40.644444Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:10815:initial_fsize == (file->size * phy_page_size) thread 139997624125184
        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.                                                                                                                                    
        15:09:40 UTC - mysqld got signal 6 ;                                                                                                                               
        Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.                                                                     
        Thread pointer: 0x562daafdf8d0                                                                                                                                     
        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 = 7f53bca7bca0 thread_stack 0x46000                                                                                                                   
        /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x562da5015961]                                                                   
        /usr/sbin/mysqld(handle_fatal_signal+0x31b) [0x562da3e40e0b]                                                                                                       
        /lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0) [0x7f53c98e53c0]                                                                                                   
        /lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb) [0x7f53c8f4318b]                                                                                                     
        /lib/x86_64-linux-gnu/libc.so.6(abort+0x12b) [0x7f53c8f22859]                                                                                                      
        /usr/sbin/mysqld(+0xe89b63) [0x562da3ba1b63]                                                                                                                       
        /usr/sbin/mysqld(fil_tablespace_redo_extend(unsigned char*, unsigned char const*, page_id_t const&, unsigned long, bool)+0x510) [0x562da54a12f0]                   
        /usr/sbin/mysqld(+0x24b67e9) [0x562da51ce7e9]                                                                                                                      
        /usr/sbin/mysqld(+0x24b9f1e) [0x562da51d1f1e]                                                                                                                      
        /usr/sbin/mysqld(recv_recovery_from_checkpoint_start(log_t&, unsigned long)+0x2b82) [0x562da51d7f62]                                                               
        /usr/sbin/mysqld(srv_start(bool)+0x2136) [0x562da52d97f6]                                                                                                          
        /usr/sbin/mysqld(+0x23fc787) [0x562da5114787]                                                                                                                      
        /usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x9e) [0x562da4d9a77e]                                                        
        /usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x1a9) [0x562da4fdd529]                                                                     
        /usr/sbin/mysqld(+0x11e4b08) [0x562da3efcb08]                                                                                                                      
        /usr/sbin/mysqld(+0x28af8ca) [0x562da55c78ca]                                                                                                                      
        /lib/x86_64-linux-gnu/libpthread.so.0(+0x9609) [0x7f53c98d9609]                                                                                                    
        /lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7f53c901f293]                                                                                                       
        
        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                                                                                        
        
        

        Reading the log, it points to this man page where says to set innodb_force_recovery = 1, but I’m not sure about it. Any way, I think I’ll try 😬.

        In syslog every thing is like:

        Apr 13 11:45:47 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168821.153424] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=193.123.198.11 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x00 TTL=241 ID=15355 PROTO=TCP SPT=8
        Apr 13 11:46:26 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168860.561849] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=45.146.164.211 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x20 TTL=244 ID=10148 PROTO=TCP SPT=4
        Apr 13 11:46:41 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168875.647530] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=45.55.40.24 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x00 TTL=248 ID=54321 PROTO=TCP SPT=5062
        Apr 13 11:46:54 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168887.904234] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=45.141.84.57 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x00 TTL=234 ID=2979 PROTO=TCP SPT=4565
        Apr 13 11:47:16 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168910.304057] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=45.145.66.91 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x00 TTL=242 ID=34428 PROTO=TCP SPT=495
        Apr 13 11:47:28 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168922.609310] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=162.142.125.166 DST=159.65.103.101 LEN=44 TOS=0x00 PREC=0x00 TTL=42 ID=62982 PROTO=TCP SPT=6
        Apr 13 11:47:31 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168925.870850] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=157.230.245.91 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x00 TTL=246 ID=58301 PROTO=TCP SPT=4
        Apr 13 11:47:36 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168930.104764] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=206.189.181.12 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x00 TTL=56 ID=29469 PROTO=TCP SPT=34
        Apr 13 11:47:51 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168945.054467] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=167.248.133.85 DST=159.65.103.101 LEN=44 TOS=0x00 PREC=0x00 TTL=40 ID=9141 PROTO=TCP SPT=598
        Apr 13 11:47:59 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168953.269705] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=205.185.125.73 DST=159.65.103.101 LEN=44 TOS=0x00 PREC=0x00 TTL=243 ID=54321 PROTO=TCP SPT=4
        Apr 13 11:48:09 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168963.554226] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=14.215.165.144 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x00 TTL=237 ID=57162 PROTO=TCP SPT=4
        Apr 13 11:48:20 ubuntu-s-1vcpu-1gb-sfo2-01 kernel: [168974.051044] [UFW BLOCK] IN=eth0 OUT= MAC=12:b6:b8:e1:a9:dc:fe:00:00:00:01:01:08:00 SRC=45.146.164.58 DST=159.65.103.101 LEN=40 TOS=0x00 PREC=0x20 TTL=243 ID=45713 PROTO=TCP SPT=42
        
        

        Thanks

        • Hi there,

          Yes, indeed it looks like that you have corruption in the InnoDB tablespace.

          You would need to start MySQL in recovery mode and try to repair the data. This could be very tricky to do, you could take a look at the answer here for some steps on how to do this:

          https://www.digitalocean.com/community/questions/innodb-force-recovery-help

          If this does not help, you might have to revert to a backup.
          Regards,
          Bobby

          • Ok, I’ve been trying to solve this, but I fail every time.

            I’ve reached the point of set innodb_force_recovery to 3

            I followed some tutorials and man pages, including yours, but nothing, I have no other way but start over and reinstall every thing (mysql)

            The good news is that I’ve been using my droplet to play and learn, I have nothing important there, but just now I’m gonna start a new serious project, so I have some items in my TODO list, like learn how to automatically backup data bases and stuff like that.

            But now I have a new doubt, b'couse log says:

            Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.

            Could this happens in this envioroment?

            Thanks a lot for your help and patient.

            Regards

          • Hi there,

            I could suggest the following things:

            • Make sure that you always have enough disk space. InnoDB is usually quite stable unless the server runs out of disk space.
            • You could enable Droplet backups, that way you can always revert to a backup in case that something goes wrong.
            • You could use the following script to backup your databases: How to export/backup a MySQL/MariaDB database with mysqldump
            • Another option is to use a backup solution like SnapShooter

            Regards,
            Bobby

Hello @themingisprose

If the server is running out of disk space you can follow Bobby’s suggestion to upgrade the disk space. You can also look for log files that are taking a lot of disk space and try to free some space from the droplet.

You can check the disk space usage using the df -h command and examine the disk space partition usage.

Regards,
Alex