Help!!... must reverse last SQL query

[louboumian]

Hello!

I was trying to install a MOD on my PHPbb forum. The last MYSQL query :

------------------
UPDATE phpbb_posts p, phpbb_users u SET p.user_avatar = u.user_avatar, p.user_avatar_type = u.user_avatar_type WHERE p.poster_id = u.user_id and u.user_avatar != "" and p.user_avatar = "";
------------------

seems to have screwed up the DB, since I get this message on trying to access the forum:

------------------
Could not query forums information

DEBUG MODE

SQL Error : 1054 Unknown column 'p.post_id' in 'on clause'

SELECT f.*, p.post_time, p.post_username, u.username, u.user_id FROM (( phpbb_forums f LEFT JOIN phpbb_posts p ON p.post_id = f.forum_last_post_id ) LEFT JOIN phpbb_users u ON u.user_id = p.poster_id ) ORDER BY f.cat_id, f.forum_order

Line : 167
File : index.php
-------------------------------

Is there a way to reverse this last SQL querry, and if yes, what would be the script?
( I am using PHPmyadmin to launch queries).

Thanks in advance!

LB

[RTH10260]

The first UPDATE sql statement cannot have damaged your database layout, it is clearly updating the posts table with the users avatars.

The error message lists that you seem to be missing a column now altogether. Assuming that this was working just fine before adding the mod, this means the mod installation did change one or more tables in the forum setup. Check with the mod installation procedure.

As an outsider to phpbb, it looks as if the 'post_id' could have been a key field to the 'posts' table. If that column was accidentially deleted, your only way to recover is to restore that the database (I assume that a restore of the one 'posts' table could be sufficient). You will need to rebuild indexes after that table import.

[louboumian]

Thanks Richard!

I actually ran two SQL statements before the one I posted (but thought I was the last one that did the damage):

----------------
ALTER TABLE 'phpbb_posts' ADD 'user_avatar' VARCHAR(100) NOT NULL default '';
ALTER TABLE 'phpbb_posts' ADD 'user_avatar_type' TINYINT NOT NULL;
----------------

I did a complete backup of the database and of all the forum files before fiddling around with this MOD. I have reloaded the original files and tried to restore the database using PHPmyadmin, SQL, and putting the address of my backup DB file into the field for "Location of the text file:" then pressed GO with Compression on "Autodetect" (the back-up file has an sql extension). To no avail... I don't know what you mean by reindexing. If I completely erase my DB on the server and create an empty one with the same name then re-upload my backup DB, would that work? :confused:

[louboumian]

Problem solved!! :)

With Excel, I compared my backup Php_posts table definition with the one online, the first column was "post_id" whereas the one online was "user_avatar".
So I must have screwed up when fiddling around with the SQL statement, modifying instead of adding a column... I renamed the column to post_id and now the whole forum is back running. :D pfewww....
I still don't understand why the restore from my back-up file did not correct the structure though...

Thanks again for giving me a clue Richard!

LB