- How to fix corrupt table in PhpBB3 Database using PhpMyAdmin (phpbb_users table corrupt in PhpBB3 forum).
- How to add a forum administrator/founder using PhpMyAdmin.
PhpBB3 is used for a forum application. The forums were all working fine until I started receiving the following SQL error when trying to access the forum website:
Can't open file: 'phpbb_users.MYI'. (errno: 145) [1016]The users table in the database had become corrupt or deadlocked. When looking at the database tables used by the PhpBB3 forums via PhpMyAdmin, I noticed that the "phpbb_users" table had an error number (145) when trying to retrieve data about the table, and on the summary page which displays all tables in the selected database the phpbb_users table status was "in use".
Important: The following was my solution to the unknown problem which caused the database to become corrupt. If you are experiencing similar problems or are receiving the same error message (SQL Error 1016 Can't open file: 'phpbb_users.MYI'. (errno: 145) [1016] ), there are a number of different possiblities which may have caused the corruption. Also, the extent of
damage to the database will also determine the chance of PhpMyAdmin or similar being able to repair the table without affecting the overall functionality of the application or database. In some cases, you won't be able to repair the damaged table, and as a result will be required to retore the entire database from a working backup in order to have the forums functioning again.
Please understand that the steps I have taken to solve this problem may not work in different situations or server environments. The steps were successful in this specific scenario, but do risk causing more problems or corruption to the database particularly if the underlying cause of the initial database corruption still exists. Continue at your own risk.
If possible, export as much of the database as possible (all tables except the corrupted) prior to performing any repairs on the database, as there is a high chance that you will loose some data
which may affect the PhpBB3 functionality in various ways. If successful, any entries which may
have been removed during the repair can then be added back into the table from a previous (non corrupt) backup.
I can't stress how important it is to make and keep regular backups of the database and website, as cases like this one put you at risk of losing all, or a large amount of data from the database if no recent backups are available after a serious/major problem.
The Process:
1. Repair the Corrupt Table using PhpMyAdminUsing PhpMyAdmin, determine which tables have become corrupt by displaying all tables in the database. The corrupt databases in this case are "in use", produce an error if trying to display any records from the table. I was able to repair the corrupt table by selecting the table and choosing "Repair Table" from the drop-down menu after the list of tables.
Once repaired, the database and forum was functioning again, but to repair the table a row was removed from the table by PhpMyAdmin, which happened to be the administrator/founder account. This didn't stop the forums from functioning, but I was not able to log into the Administration control panel to manage and administer the forums.
2. Create New Account to become a Forum Founder/AdministratorAfter the initial forum founder/administrator account was removed from the database, the newest member of the forums became the original administrator/founder account even though there have been many users register to become members since the forums were initially launched. This was due to a script on the main site which helps to maintain forum user accounts, and the corresponding account on the main website (separate to the forum); When a user logs in to the main website, details from the user's forum account are retrieved to make use of some additional profile data. An un-initialized forum account is created for the user if none exists, but is only used to store additional profile information until the user activates the forum account properly.
In this case, the administrator/founder account was recreated as PhpMyAdmin had removed the entry from the phpbb_users table when repairing the corrupt database table, freeing up the username used by the administrator/founder of the forums. Usually, both accounts exist when logging on as the administrator, so a new forum account would not need to be created. As the "administrator" account had been recreated, all founder permissions were lost and the user was now a standard "registered user".
3. Set the Founder/Administrator Permissions: Modify the phpbb_users tableTo reset the permissions on the new administrator account (or any account) to make the user a founder, I had to login to the database using PhpMyAdmin and update the entry in the phppp_users table to grant the new administrator. I then Set the "user_type" value for the new administrator/founder account to "3" using the PhpMyAdmin interface.
4. Purge the CacheAfter updating the user's type, I manually purged the cache by removing all files in the "cache/" directory except for .htaccess and index.html. Warning: This may remove HTML or CSS customisations made to templates if done via the Administration Control Panel, as a fresh copy of all required template files will be copied from the template directory of the selected style/theme when required.
Once founder/administrator permissions were granted again on the new administrator account, I was able to login to the Administration Control Panel to manage the board/forum.
When attempting to view the user profile of the original administrator account which was used to post a thread or reply on the forums, a "The requested user does not exist" message is displayed as the original administrator account no longer exists. As the founder/administrator is now a new user, all posts created using the original administrator account need to be set as posts on the new administrator account.
5. Relink the Author Link on Posts to Point to the New Profile
To relink posts written using the original administrator/founder account to the new administrator account created, do the following (this applies when the database entry for the original administrator/founder account no longer exists in the users table, and that the username of the new account is the same as the username of the original account). This will not set the new administrator account's number of posts, as this is a separate field in the users table which will have to be modified manually if required. Setting the new administrator/founder account to have the same user_id will only fix the author link on posts to point to the new account profile:
- Get the user_id of the original administrator/founder account and the new account (with the same username) by obtaining the value of query string paramater "u" of the address/url to the user profile. Example: /memberlist.php?mode=viewprofile&u=21&sid=...
- Using PhpMyAdmin or similar, edit the entry in the users table where the "user_id" is equal to the user_id of the new administrator account.
- Set the user_id field to the user_id of the original administrator/founder account which no longer exists.
- Make sure the user_type value for the account is set to 3 (global administrator/founder). Apply the changes.
- Set the user_group value to 5 to add the user to the administrators group.
Warning: Changing "user_type" and "user_group" values affects users permissions across the forum. Configuring incorrectly may result in the wrong account being granted founder permissions on the forum which would result in the user having access to everything (including the Administration control Panel of the forum). If permissions are granted to forums via groups, changing the group_id for a user by editing database records may also result in the user being granted elevated permissions or the account becoming inactive. You should always maintain forum permissions and users via the forums Administration Control Panel where possible. When the Administration Control panel is not accessible for the above or any other reason, records/entries in the database can be modified directly, but extreme caution must be taken to avoid applying incorrect changes to user data, or changes which may result in the user's account not functioning at all.
SummaryThe phpbb_users table of the database used by the PhpBB3 forums had become corrupt for an unknown reason. Repairing the table in the database using PhpMyAdmin resulted in the record for the forum Administrator/Founder being removed. A new account was created, and modified using PhpMyAdmin to be granted the required administrator/founder permissions. The user_id was then set to match the id of the previous forum administrator account (same username).
The Phpbb3 Forums are now functioning fully again, including the forum founder/administrator account for access to the Administration Control Panel to manage the forums. I have also increased the frequency of incremental database and web server backups to help avoid this in future.
Posts
