phpBB3 SQL database table doesn't exist ERROR; cannot import from backup or drop the broken database

Question

I installed phpBB3 to Mac OS X server (10.9) and it had been running fine since April until a few hours ago. I can't tell the exact cause of the problem, but it appeared that after installing OS X Server 10.9.4 update, somehow it's database lost its integrity. So I'll explain what happened and what I tried.

When I tried to access the forum I got the following error message.

General Error 
SQL ERROR [ mysql4 ]
Table 'phpbb3.phpbb_config' doesn't exist [1146]
An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

Then I went to phpMyAdmin and clicked the database phpbb3. It said Table is missing. I didn't know how to fix it. So I used TimeMachine to recover the previous status of the system before OS X Server update. The forum was OK, but I blindly tried the OS update again and ended up with repeating the problem. I used TimeMachine again. This time the forum hasn't recovered. Meanwhile TimeMachine backup just became full and I realized that I lost all the TimeMachine backups but the latest one. This way, I can't get back to recover the phpBB3 forum any longer.

I gave up recovering with a TimeMachine backup and tried to recover the database by importing the last backup via phpMyAdmin. The backup was made 2 months ago, but still better than nothing. However, import didn't work either.

Error
SQL query:

CREATE TABLE IF NOT EXISTS `phpbb_acl_groups` (
  `group_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `auth_option_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `auth_role_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `auth_setting` tinyint(2) NOT NULL DEFAULT '0',
  KEY `group_id` (`group_id`),
  KEY `auth_opt_id` (`auth_option_id`),
  KEY `auth_role_id` (`auth_role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
MySQL said: Documentation

#1813 - Tablespace for table '`phpbb3`.`phpbb_acl_groups`' exists. Please DISCARD the tablespace before IMPORT.

Then I tried to delete the database phpbb3 but I failed.

Error 
SQL query: 
DROP DATABASE `phpbb3` MySQL said: Documentation

#1010 - Error dropping database (can't rmdir './phpbb3', errno: 66)

Finally, I found out where the actual database files are (/usr/local/mysql/data/phpbb3). I looked inside the folder and there are still bunch of files; all of them ended with .ibd extension, including phpbb_acl_groups.ibd (147 KB; this is the first one in the alphabetical order).

Here's my question: What should I do? Should I manually delete the /usr/local/mysql/data/phpbb3 folder and try to import the latest (2 months ago) database export file? Or could I somehow recover the database from what left in /usr/local/mysql/data/phpbb3 folder?

Kouichi C. Nakamura Kouichi C. Nakamura · Accepted Answer · 2014-07-20T00:17:50

I found this article.

mysqldump problems with restore error: 'Please DISCARD the tablespace before IMPORT'

It says that the folder /usr/local/mysql/data/phpbb3 should contain .ibd files and .frm files. But the ERROR Tablespace for table 'phpbb3.phpbb_acl_groups' exists. means .frm files are all gone. That was the case indeed.

So I judged that the folder /usr/local/mysql/data/phpbb3 is no use any more. Now that all the TimeMachine backups are gone, the only way is to rely on the available database backup file. I forced to remove the folder from finder. Created the new empty database via phpMyAdmin, and then imported the database backup. This time there was no error and I was able to log on to the phpBB3 forum. Although I lost new data, the forum itself seems fine.

phpBB3 SQL database table doesn't exist ERROR; cannot import from backup or drop the broken database

2 Answers