6

After I reboot my server, I can no longer start mysql.

I have followed several solutions posted here:

I run the following commands to start the service:

mysql
>> 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.
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 Wed 2022-01-26 15:18:33 UTC; 47s ago
    Process: 2612 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)
 journalctl -xe
>> Startup of the manager took 379405 microseconds.
Jan 26 14:33:24 sumentserver polkit-agent-helper-1[2223]: pam_unix(polkit-1:auth): authentication failure; logname= uid=1001 euid=0 tty= >
-- Reboot --
Jan 26 14:44:33 sumentserver systemd[1685]: Reached target Paths.
-- Subject: A start job for unit UNIT has finished successfully
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- A start job for unit UNIT has finished successfully.

The server has Ubuntu 20.4

Any tip about what can I do?

2
  • 1
    It is giving not enough info about why it failed., normally a logfile (/var/log/mysql/...) is being generated, does that not give a clue about what failed?
    – Luuk
    Jan 26 at 15:40
  • What version of MySQL? Did you recently upgrade? Did your OS come with a different copy of MySQL?
    – Rick James
    Jan 26 at 17:53

2 Answers 2

3

I faced the same problem today ,the source of the issue might be different but, read carefully /var/log/mysql/error.log it should point to the error.

I deleted one table that I didn't need .MYD and .MYI files. It was a mistake , I should drop the table , but my server was on 100% memory usage.

What I did was reading the error on:

sudo tail -f /var/log/mysql/error.log

Keep two terminal open at the same time, first run

sudo tail -f /var/log/mysql/error.log 

on first terminal , then run on the second terminal

sudo systemctl stop mysql.service   ---to shut it down
sudo systemctl start mysql.service  ---to try and start up and see the reason why it is not starting.

For me it was:

2022-01-26T16:30:36.213788Z 0 [Note] InnoDB: Database was not shutdown normally!
2022-01-26T16:30:36.213802Z 0 [Note] InnoDB: Starting crash recovery.
2022-01-26T16:30:36.213830Z 0 [ERROR] InnoDB: Tablespace 605 was not found at ./contratti/ip_log2_bak.ibd.
2022-01-26T16:30:36.213840Z 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
2022-01-26T16:30:36.644359Z 0 [ERROR] InnoDB: Cannot continue operation.
2022-01-26T16:30:38.045091Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-01-26T16:30:38.062994Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.34-log) starting as process 2843 ...
2022-01-26T16:30:38.416074Z 0 [Note] InnoDB: PUNCH HOLE support available

I was able to fixing by adding on /etc/mysql/mysql.conf.d/mysqld.cnf

innodb_force_recovery = 2

From MySQL docs

If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost.

After this change I stopped and started again MySQL and it is working great.

3
  • That is a good tip. In my case it led me to: 2022-01-26T15:10:01.855905Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBS> Configuration fixed. Thanks a lot! This saved the day! Jan 26 at 17:06
  • Is ip_log2 one of your tables?
    – Rick James
    Jan 26 at 17:55
  • @RickJames yes, it is. It's table which is single table replicated from another server. ip_log2_bak was a table with old data with MyISAM engine. I executed following commands. renamed ip_log2_bak to ip_log2_bak_old. Create another ip_log2_bak with InnoDB engine and used insert into ip_log2_bak (select * from ip_log2_bak_old) . This stuck because of physical memory, so I deleted the ip_log2_bak files from var/lib/mysql/database/ip_log2_bak.MYD MYI. Silly mistake Jan 26 at 18:12
0

Following debuging commens from @Ergest Basha, after checking the logs it looked like a configuration issue:

2022-01-26T15:10:01.855905Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBS>

Roll back to default

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.