0

I am currently facing the following error when I run the tail command on the error log.

    root@sendy-droplet:/# tail -30 /var/log/mysql/error.log
2022-08-02T21:45:40.816581Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-0ubuntu0.20.04.2) starting as process 3049
2022-08-02T21:45:40.830054Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-08-02T21:45:40.831372Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-08-02T21:45:40.831393Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-02T21:45:40.831805Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-0ubuntu0.20.04.2)  (Ubuntu).
2022-08-02T21:46:00.633544Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-0ubuntu0.20.04.2) starting as process 3093
2022-08-02T21:46:00.643248Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-08-02T21:46:00.643353Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-08-02T21:46:00.643374Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-02T21:46:00.643903Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-0ubuntu0.20.04.2)  (Ubuntu).
2022-08-02T21:46:01.295373Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-0ubuntu0.20.04.2) starting as process 3110
2022-08-02T21:46:01.306837Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-08-02T21:46:01.306949Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-08-02T21:46:01.306962Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-02T21:46:01.307391Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-0ubuntu0.20.04.2)  (Ubuntu).
2022-08-02T21:46:02.066704Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-0ubuntu0.20.04.2) starting as process 3120
2022-08-02T21:46:02.075175Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-08-02T21:46:02.075298Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-08-02T21:46:02.075314Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-02T21:46:02.075803Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-0ubuntu0.20.04.2)  (Ubuntu).
2022-08-02T21:46:02.791184Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-0ubuntu0.20.04.2) starting as process 3130
2022-08-02T21:46:02.798780Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-08-02T21:46:02.798883Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-08-02T21:46:02.798897Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-02T21:46:02.799347Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-0ubuntu0.20.04.2)  (Ubuntu).
2022-08-02T21:46:03.555588Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-0ubuntu0.20.04.2) starting as process 3140
2022-08-02T21:46:03.563822Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-08-02T21:46:03.563917Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-08-02T21:46:03.563930Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-02T21:46:03.564373Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-0ubuntu0.20.04.2)

I am really not sure how I got here as everything was running until I got an error that I couldn't connect to the db anymore. I have tried starting into safe mode using

mysqld_safe --skip-grant-tables&

it runs but the moment I try to enter anything from mysql it fails (same error as above). Everything in the conf files are fine and my next step is trying to do a forced recoevery using

innodb_force_recovery=1

however I have read that it can be possibly dangerous running these commands (and increasing the number) so before I run it I wanted to double check with the community first to see if they have any idea what could be causing this/maybe another fix? And if innodb_force_recopvery=1 is ok to try out.

As well I can see the data files (meaning the idb files) in the data folder and they have data in it (due to there size) so I know there must be something there!!

If anyone has any resources or input please let me know!

Update: I have ran innodb recovery in my.conf file from 1 to 5 and it refused to start. Running initialize also gave me the following errors.

2022-08-03T21:00:31.755720Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.

That data path /var/lib/mysql/ holds my current data so i don't want to remove it.

Zachattack
  • 51
  • 8
  • The error seems to be showing something [similar to this question](https://stackoverflow.com/questions/52719378/failed-to-find-valid-data-directory-mysql-generic-binary-installion). – FanoFN Aug 03 '22 at 00:02
  • @FanoFN I have seen this but I am afraid that mysql --initialize will erase my data that I currently have – Zachattack Aug 03 '22 at 00:39
  • I'm not aware that it will erase data but if I'm not mistaken, if there is data in the directory, the initialization is aborted. You've manged to get the database running with `innodb_force_recovery=1`, correct? Did you check the log after that? Usually when there's a corrupt table, you should be able to identify which table from the log. – FanoFN Aug 03 '22 at 01:50
  • @FanoFN no I haven't tried innodb_force_recovery yet as I was really unsure if it would break anything. I will try it after trying the initialize and hopefully that works! – Zachattack Aug 03 '22 at 12:37
  • 1
    Looks like a question for [dba.se] – Tangentially Perpendicular Aug 03 '22 at 22:15
  • According to [the official documentation](https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html), level 1 is to ` (SRV_FORCE_IGNORE_CORRUPT)` so it should be safe to use. If you read further, the docs said _"This value can permanently corrupt data files"_ starting from level 4 so if you really have to use level 4 and above, make sure you have backups first. – FanoFN Aug 03 '22 at 23:33

1 Answers1

0

The parameter innodb_force_recopvery may be useful, but it can't completely guarantee the usability of the data table. The table of innodb still needs to be repaired, or the data can be found and recovered by using tools.

dogs Cute
  • 564
  • 3
  • 9