This one was a little tricky. The same system where the junior admin made a little mess of the MariaDB server, there was this long string of errors about “InnoDB: Ignoring tablespace for because it could not be opened.” Apparently, somehow the db got deleted manually from the filesystem and we got left with a zillion of these:
2023-08-22 19:04:31 0 [Warning] InnoDB: Ignoring tablespace for `foo`.`core_layout_link` because it could not be opened.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘./foo/core_layout_update.ibd’ OS error: 71
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Could not find a valid tablespace file for “foo`.`core_layout_update“. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2023-08-22 19:04:31 0 [Warning] InnoDB: Ignoring tablespace for `foo`.`core_layout_update` because it could not be opened.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘./foo/core_resource.ibd’ OS error: 71
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Could not find a valid tablespace file for “foo`.`core_resource“. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2023-08-22 19:04:31 0 [Warning] InnoDB: Ignoring tablespace for `foo`.`core_resource` because it could not be opened.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘./foo/paypal_ipn_log.ibd’ OS error: 71
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Could not find a valid tablespace file for “foo`.`paypal_ipn_log“. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
Now, “foo” being the db name, apparently i need to recreate the file structure in /var/lib/mysql and then i can go ahead and restart the sql server and delete the db and everything should go back to normal.
Because they were too many of these, i had to do a little bash wizardry to automate the process and make it a little faster:
cat mysql.err |grep “Could not find a valid tablespace file” |cut -d ‘`’ -f 5 >> list
This would get me a list of the tables, while using ‘`’ as the delimiter for the field i was trying to find. i just cycled between the fields numbers before i got to the right one
So we go to the MySQL folder and re-create the foo db folder (where foo is the db name):
cd /var/lib/mysql
mkdir foo
Then i made this script, that would create the required files:
cd foo
nano x.sh
#!/bin/bash -x
list=$(cat ./list)# Loopty loop
for item in $list; do
touch $item.frm
touch $item.ibd
done
chmod 700 x.sh
./x.sh
Now we have to adjust permissions on the db folder and the files inside:
cd /var/lib/mysql
chown -R mysql:mysql foo
restart mysql and go ahead and delete the foo db and the errors should be gone.
Cheers!