Clean *and complete* database?

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
guspasho
Registered User
Posts: 45
Joined: Thu Sep 15, 2005 9:25 pm
Location: Portland, OR
Contact:

Clean *and complete* database?

Post by 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.
Portland's Anime Convention, Labor Day weekend, http://www.kumoricon.com
who_cares
Registered User
Posts: 5106
Joined: Fri Jan 14, 2005 11:04 pm
Location: ATL
Contact:

Post by who_cares »

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

what version of phpbb do you have stored in the database?
guspasho
Registered User
Posts: 45
Joined: Thu Sep 15, 2005 9:25 pm
Location: Portland, OR
Contact:

Post by 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.
Last edited by guspasho on Wed Sep 27, 2006 4:07 am, edited 1 time in total.
Portland's Anime Convention, Labor Day weekend, http://www.kumoricon.com
who_cares
Registered User
Posts: 5106
Joined: Fri Jan 14, 2005 11:04 pm
Location: ATL
Contact:

Post by who_cares »

guspasho
Registered User
Posts: 45
Joined: Thu Sep 15, 2005 9:25 pm
Location: Portland, OR
Contact:

Post by guspasho »

You are running phpbb version: 2..0.21
Portland's Anime Convention, Labor Day weekend, http://www.kumoricon.com
who_cares
Registered User
Posts: 5106
Joined: Fri Jan 14, 2005 11:04 pm
Location: ATL
Contact:

Post by 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
Registered User
Posts: 45
Joined: Thu Sep 15, 2005 9:25 pm
Location: Portland, OR
Contact:

Post by 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?
Portland's Anime Convention, Labor Day weekend, http://www.kumoricon.com
who_cares
Registered User
Posts: 5106
Joined: Fri Jan 14, 2005 11:04 pm
Location: ATL
Contact:

Post by who_cares »

guspasho wrote: 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
Registered User
Posts: 45
Joined: Thu Sep 15, 2005 9:25 pm
Location: Portland, OR
Contact:

Post by 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.
Portland's Anime Convention, Labor Day weekend, http://www.kumoricon.com
who_cares
Registered User
Posts: 5106
Joined: Fri Jan 14, 2005 11:04 pm
Location: ATL
Contact:

Post by who_cares »

okay, try this script
Locked

Return to “2.0.x Support Forum”