Clean *and complete* database?

[guspasho]

I had a heavily-modded board using pphBB 2.0.17 that I needed to upgrade and finally attempted to. Because of the mods that I didn't want to reinstall, it was suggested to me to upload the code changes for each version release with EasyMOD, to .18, .19, .20, .21. I did so (running /install/update_to_latest.php after each step, as advised in this topic: http://www.phpbb.com/phpBB/viewtopic.php?t=446440 ) but I noticed some errors along the way, I think relating to an inability to create or alter the database table "phpbb_sessions_keys". I wasn't sure what to do, and the rest of the update had gone through anyway, updating my version number, so I ignored the problem.

Now, because I still wasn't satisfied I had a "clean" board again, I installed a new phpBB 2.0.21 board into another directory of my site and set it up to use the same database and database user, (just a different prefix for the database tables so as not to overwrite the existing data, and then changing the prefix later to access the existing data.) This worked for a while until the new, clean and unmodded forums went live and someone attempted to log in with cookies disabled in their browser. Then I got an error message:

phpBB : Critical Error

Error updating session key

DEBUG MODE

SQL Error : 1146 Table 'kc_phpbb.phpbb_sessions_keys' doesn't exist

INSERT INTO phpbb_sessions_keys(key_id, user_id, last_ip, last_login) VALUES ('6a1c8e3e79e9d6b4c2cf4d5364b6a24b', 612, '463b806a', 1159036670)

Line : 229
File : sessions.php

This database error took down my forums for a whole morning for all users. I fixed the problem by copying the database table "tempdelete_sessions_keys" to phpbb_sessions_keys (tempdelete was the table prefix I used when I created the new board on the existing database.) I was able to replicate it by deleting the copied table and disabling cookies in FireFox and attempting to log in, which reproduced the error.

Anyway, what I learned was that even though I may have a clean board, my database is not what it should be. It was missing at least one table that the upgrade process, for whatever reason, could not create.

I have a set of tables created by the 2.0.21 installer that I can be confident have the correct structure for a phpBB 2.0.21 board but I don't know enough about phpMyAdmin or MySQL to use that to make sure the tables that have my actual data is correct for use with a phpBB 2.0.21 board - you know?

I did run a script on my database called "Clean Tables" that allowed me to remove any additions to the database that any mods may have made, such as tables that EasyMOD creates, but apparantly it doesn't add any tables or structure that is missing. Is there a script that does this? Or alternatively, is there a way anyone can show me how I can use phpMyAdmin to correct my database structure in case anything else is missing? Thanks.

[who_cares]

that error means your database hasn't been updated correctly.

what version of phpbb do you have stored in the database?

[guspasho]

I don't know. How do I find out? I know it wasn't updated correctly. I would like to know how to update it correctly, even if I have to do it manually.

EDIT: if you mean the version value in the phpbb_config table, it is ".0.21" (without the quotes.) But as I said, even when I updated and had errors the version changed on my board anyway.

[who_cares]

try this:
http://www.phpbb.com/kb/article.php?article_id=351

[guspasho]

You are running phpbb version: 2..0.21

[who_cares]

here's some sql to insert the missing table:

Code: Select all

CREATE TABLE " . $table_prefix . "sessions_keys (`key_id` varchar(32) NOT NULL default '0', `user_id` mediumint(8) NOT NULL default '0', `last_ip` varchar(8) NOT NULL default '0', `last_login` int(11) NOT NULL default '0', PRIMARY KEY (`key_id`,`user_id`), KEY `last_login` (`last_login`)) TYPE=MyISAM;
UPDATE " . USERS_TABLE . " SET user_active = 0 WHERE user_id = -1
INSERT INTO " . CONFIG_TABLE . " (config_name, config_value) VALUES ('allow_autologin','1')
INSERT INTO " . CONFIG_TABLE . " (config_name, config_value) VALUES ('max_autologin_time','0')

[guspasho]

I've already created the table phpbb_sessions_keys by copying the table created by a fresh installation of phpBB 2.0.21 that I made using the same database but a different table prefix. Will this do?

What I really need to know is what other changes to the database do I need to make? Is there a php script available that will "validate" my database for phpBB 2.0.21?

[who_cares]

What I really need to know is what other changes to the database do I need to make? Is there a php script available that will "validate" my database for phpBB 2.0.21?

there is, I'll see if I can get you a copy

[guspasho]

Thanks. It's much appreciated.

Please note, there's one (I couldn't find the location again) called "Clean Tables" that only removes additions to standard phpBB databases, and it didn't specify which version of phpBB it was meant for. It removed tables created by EasyMOD and one or two other mods I had installed before but it did not create phpbb_sessions_keys or do any other action such as creating what was not already there.

[who_cares]

okay, try this script