Error innodb operating system error number 2 in a file operation

I’m developing locally and using Mamp Pro.
Basically MySQL is having trouble starting and here’s the error message from the error log:

Any idea how to resolve this?

2017-03-02 00:39:08 9518 [Note] Plugin 'FEDERATED' is disabled.
2017-03-02 00:39:08 9518 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-02 00:39:08 9518 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-02 00:39:08 9518 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-02 00:39:08 9518 [Note] InnoDB: Memory barrier is not used
2017-03-02 00:39:08 9518 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-03-02 00:39:08 9518 [Note] InnoDB: Using CPU crc32 instructions
2017-03-02 00:39:08 9518 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-03-02 00:39:08 9518 [Note] InnoDB: Completed initialization of buffer pool
2017-03-02 00:39:08 9518 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-02 00:39:08 9518 [Note] InnoDB: Log scan progressed past the checkpoint lsn 663573438
2017-03-02 00:39:08 9518 [Note] InnoDB: Database was not shutdown normally!
2017-03-02 00:39:08 9518 [Note] InnoDB: Starting crash recovery.
2017-03-02 00:39:08 9518 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-03-02 00:39:08 9518 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace affiliate_blog_1_old/wpab1_7_woocommerce_order_itemmeta uses space ID: 367 at filepath: ./affiliate_blog_1_old/wpab1_7_woocommerce_order_itemmeta.ibd. Cannot open tablespace freshwp/freshwp_wfblocksadv which uses space ID: 367 at filepath: ./freshwp/freshwp_wfblocksadv.ibd
2017-03-02 00:39:08 7fff7b652000  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./freshwp/freshwp_wfblocksadv.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
170302 00:39:08 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended

From the above code the error seems to be:

2017-03-02 00:39:08 9518 [ERROR] InnoDB: Attempted to open a previously opened tablespace.

Previous tablespace affiliate_blog_1_old/wpab1_7_woocommerce_order_itemmeta
uses space ID: 367
at filepath: ./affiliate_blog_1_old/wpab1_7_woocommerce_order_itemmeta.ibd.

Cannot open tablespace freshwp/freshwp_wfblocksadv which uses space ID: 367
at filepath: ./freshwp/freshwp_wfblocksadv.ibd

Hi,

It happened DB crash with this messages.

2017-07-24 17:40:20 2b358f091940  InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

170724 17:40:20 [ERROR] InnoDB: File /tmp/#sql654b_24aa3_0.ibd: 'open' returned OS error 71. Cannot continue operation

pure virtual method called

pure virtual method called

pure virtual method called

terminate called without an active exception

terminate called without an active exception

terminate called recursively

170724 17:40:20 [ERROR] 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.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed,

something is definitely wrong and this may fail.

Server version: 10.0.29-MariaDB

key_buffer_size=402653184

read_buffer_size=2097152

max_used_connections=211

max_threads=8194

thread_count=91

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 84462163 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2b353bd7e008

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...

170724 17:40:23 mysqld_safe Number of processes running now: 0

170724 17:40:23 mysqld_safe mysqld restarted

170724 17:40:24 [Note] /MARIA/mariadb/bin/mysqld (mysqld 10.0.29-MariaDB) starting as process 30193 ...

2017-07-24 17:40:24 2b3670579600 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc a

nd with the InnoDB's internal memory allocator.

It happened two times for 3 month.
I don’t know why this happened.
Please help me.

Here is the error log.

2013-11-18 21:25:25 a171e1a8  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./testdb/dr_actions.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

Step 1. Open mysql configuration file, mine is /opt/lampp/etc/my.cnf

Step 2. In [mysqld] section, add the following line:
innodb_force_recovery = 1

Step 3. Save the file and try starting MySQL

Step 4. Remove that line which you just added and Save. 

After needing to reset the root password of a MySQL instance I went to restart my service and retrieved the following error from mysqld.log

[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 3253
[ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
[ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
[ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
[ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
[ERROR] [MY-012981] [InnoDB] Cannot continue operation.

From what I’ve read online, something with my.cnf or directory permissions of my /var/lib/mysql (750) may be off.

I’m a bit out of my element, but I did not find journalctl -xe or systemctl status to be very helpful.

My my.cnf file contains the following:

#bind-address=127.0.0.1
port=3306
#--skip_networking=0
#user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
tmpdir = /var/lib/mysql/tmp

Can anyone help troubleshoot so I can get this up and running again?

OK this is driving me mad. Ubuntu 20.04.5 LTS (on WSL)

I am assuming that it crashed, then upgraded mysql, and now it won’t start:

2022-08-31T16:37:29.463168Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This redo log was created with MySQL 8.0.28, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2022-08-31T16:37:29.463211Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2022-08-31T16:37:29.842520Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-08-31T16:37:29.842925Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-08-31T16:37:29.842966Z 0 [ERROR] [MY-010119] [Server] Aborting

I tried removing the redo logs ib_logfile0 and ib_logfile1 and then I get:

2022-09-03T16:56:05.188621Z 1 [ERROR] [MY-013862] [InnoDB] Neither found #innodb_redo subdirectory, nor ib_logfile* files in ./
2022-09-03T16:56:05.188663Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2022-09-03T16:56:05.672331Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-09-03T16:56:05.672561Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-09-03T16:56:05.672585Z 0 [ERROR] [MY-010119] [Server] Aborting

Also tried removing ibdata1 and I get

2022-09-03T17:20:34.205872Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-09-03T17:20:34.205907Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-09-03T17:20:34.205914Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2022-09-03T17:20:34.205929Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2022-09-03T17:20:34.205977Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.

I guess I could downgrade to 8.0.28 but surely there is a way to recover?

I tried various innodb_force_recovery settings and none of them helped.

How do I fix this please?

Thanks

I’ve created a MySQL backup with PerconaXtrabackup tool via (thanks to this guide):

innobackupex --user=backupmysqluser --password=xyz --compress --stream=xbstream ./ > /var/backups/db/firstBackup.db

It succeeds creating the hot backup. Now I want to test the restoration of it, so then, first, I decompress it:

cd /var/backups/db
mkdir decompressed
xbstream -x -C decompressed

Now I try to prepare it:

innobackupex --apply-log /var/backups/db/decompressed/

And the result is this error:

160719 20:06:28 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.3 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 6a46905)
xtrabackup: cd to /var/backups/db/decompressed
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
  xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.

It’s strange because in the decompressed folder there’s no xtrabackup_logfile, but there is a file named xtrabackup_logfile.qp instead.

What did I do wrong?