• If you are still using CentOS 7.9, it's time to convert to Alma 8 with the free centos2alma tool by Plesk or Plesk Migrator. Please let us know your experiences or concerns in this thread:
    CentOS2Alma discussion

MySQL error: Can't open file: 'foo.ibd' (errno: 1)

R

rihad

Guest
Hi all,

After our database FS /var crashed on Debian 3.1 we had to pull its directory from backups (the only backup we had), including /var/lib/mysql/*. Despite chowning/chmodding everything thereunder for access by user mysql and starting the server successfully, the logs are full of messages like:

Jan 8 13:16:20 ns3 mysqld[1565]: 080108 13:16:20 [ERROR] /usr/sbin/mysqld: Can't open file: 'misc.ibd' (errno: 1)
Jan 8 13:16:20 ns3 mysqld[1565]: 080108 13:16:20 InnoDB error:
Jan 8 13:16:20 ns3 mysqld[1565]: Cannot find table psa/smtp_poplocks from the internal data dictionary
Jan 8 13:16:20 ns3 mysqld[1565]: of InnoDB though the .frm file for the table exists. Maybe you
Jan 8 13:16:20 ns3 mysqld[1565]: have deleted and recreated InnoDB data files but have forgotten
Jan 8 13:16:20 ns3 mysqld[1565]: to delete the corresponding .frm files of InnoDB tables, or you
Jan 8 13:16:20 ns3 mysqld[1565]: have moved .frm files to another database?
Jan 8 13:16:20 ns3 mysqld[1565]: Look from section 15.1 of http://www.innodb.com/ibman.html
Jan 8 13:16:20 ns3 mysqld[1565]: how you can resolve the problem.

The said link no longer exists: http://www.innodb.com/ibman.html
There are several tables, not only misc.

How can I bring everything together? I hear the same thing happens when you simply stop the server, copy the directory containing ibdata1 somewhere else, and start the server. How to make it work again? Please somebody help, this is urgent...

Thanks.
 
Additionally, it's only the psa db that got hosed, clients' own db are ok.
 
PSA DB is InnoDB the clients ones usually is MyISAM. Do you have backup? Best is to dump back from backup. If not, then the best is to start the database with the files what is in /var/lib/mysql/ ibdata1;ib_logfile0;ib_logfile1 - this files are need to be there to start normally the innodb (as far as I know). Hope this help.
 
Thanks, but like I said, the directory structure is the only backup we have :( Files ibdata1;ib_logfile0;ib_logfile1 are all there chmodded to 660 owner mysql group mysql. I'm having a hard time understanding why innodb can't deal with the complete directory structure. Anyone?
/usr/sbin/mysqld: Can't open file: 'misc.ibd' (errno: 1)
The "(error: 1)" part is suspicious, too: it probably means the server is denied access to something.
 
Can't open is usually file is not there phisically. Can you check? Did you tried to repair? I am out from ideas.
 
"File not there" would normally be errno 2 (ENOENT), but not 1 (EPERM), which is my case. And the whole /var/lib/mysql/* directory structure is there with proper permissions as far as I can see (owned by mysql:mysql, files chmodded to 660 and directories to 700).

Sad, let's see if somebody else has already solved seen such situations.
 
You cant backup IDB by copying file structures. They are transactional databases that are built using log files. So what you can do is drop and recreate the databases, perhaps copy over your old file system backups, and then you will have to dump the logs into the table. In order to back them up, you also have to do a point in time backup of the binary log file for IDB as well.

We ran something like this:
Code:
mysqlbinlog -d database_name --to-last-log --force-read /path/to/Binary_Log> outfile.sql

You can also pipe the ouput directly into mysqld if you prefer.

I've ran into this on windows servers before when a mysql install was on a partition that ran out of space and the IDB databases all crashes becuase the binary_log couldnt be written to anymore which corrupted the tables.
 
Back
Top