Site is working again

[Guest]

Ok, all non-geeks look away on this one.

I've done a bit of digging into the documentation for phpBB boards and here's what I've come up with on the back-end schema:

Users table:

CREATE TABLE [phpbb_users] (
[user_id] [int] NOT NULL ,
[user_active] [smallint] NULL ,
[username] [varchar] (25) NOT NULL ,
[user_password] [varchar] (32) NOT NULL ,
[user_session_time] [int] NOT NULL ,
[user_session_page] [smallint] NOT NULL ,
[user_lastvisit] [int] NOT NULL ,
[user_regdate] [int] NOT NULL ,
[user_level] [smallint] NOT NULL ,
[user_posts] [int] NOT NULL ,
[user_timezone] [decimal] (5,2) NOT NULL ,
[user_style] [int] NULL ,
[user_lang] [varchar] (255) NULL ,
[user_dateformat] [varchar] (14) NOT NULL ,
[user_new_privmsg] [smallint] NOT NULL ,
[user_unread_privmsg] [smallint] NOT NULL ,
[user_last_privmsg] [int] NOT NULL ,
[user_login_tries] [smallint] NOT NULL ,
[user_last_login_try] [int] NOT NULL ,
[user_emailtime] [int] NOT NULL ,
[user_viewemail] [smallint] NULL ,
[user_attachsig] [smallint] NULL ,
[user_allowhtml] [smallint] NULL ,
[user_allowbbcode] [smallint] NULL ,
[user_allowsmile] [smallint] NULL ,
[user_allowavatar] [smallint] NULL ,
[user_allow_pm] [smallint] NOT NULL ,
[user_allow_viewonline] [smallint] NOT NULL ,
[user_notify_pm] [smallint] NOT NULL ,
[user_popup_pm] [smallint] NULL ,
[user_rank] [int] NULL ,
[user_avatar_type] [smallint] NULL ,
[user_avatar] [varchar] (100) NULL ,
[user_email] [varchar] (255) NULL ,
[user_icq] [varchar] (15) NULL ,
[user_website] [varchar] (100) NULL ,
[user_occ] [varchar] (100) NULL ,
[user_from] [varchar] (100) NULL ,
[user_sig] [text] NULL ,
[user_sig_bbcode_uid] [char] (10) NULL ,
[user_aim] [varchar] (255) NULL ,
[user_yim] [varchar] (255) NULL ,
[user_msnm] [varchar] (255) NULL ,
[user_interests] [varchar] (255) NULL ,
[user_actkey] [varchar] (32) NULL ,
[user_newpasswd] [varchar] (32) NULL ,
[user_notify] [smallint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Posts table(s):

CREATE TABLE [phpbb_posts] (
[post_id] [int] IDENTITY (1, 1) NOT NULL ,
[topic_id] [int] NOT NULL ,
[forum_id] [int] NOT NULL ,
[poster_id] [int] NOT NULL ,
[post_time] [int] NOT NULL ,
[poster_ip] [char] (8) NULL ,
[post_username] [char] (25) NULL ,
[enable_bbcode] [smallint] NULL ,
[enable_html] [smallint] NULL ,
[enable_smilies] [smallint] NULL ,
[enable_sig] [smallint] NULL ,
[post_edit_time] [int] NULL ,
[post_edit_count] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [phpbb_posts_text] (
[post_id] [int] NOT NULL ,
[bbcode_uid] [char] (10) NULL ,
[post_subject] [char] (60) NULL ,
[post_text] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm guessing that the "poster_id" in the [phpbb_posts] table is Joined to "user_id" in the [phpbb_users] table. Notice also that the username is copied to the [phpbb_posts] for every post. I'm betting that if dummy guest accounts could be inserted into the users table for the ones that got whacked, the posts would show up again and even display the user names in the posts (although the actual information for the user in the users table would still be gone.) But at least the threads would make sense again. And if someone wanted their old user name and account back, that information could be inserted in the new dummy accounts on the back end.

John, I don't know if you're following this, but what do you think?

[Guest]

ALERT - More Techie talk!

I'm guessing that the "poster_id" in the [phpbb_posts] table is Joined to "user_id" in the [phpbb_users] table. Notice also that the username is copied to the [phpbb_posts] for every post. I'm betting that if dummy guest accounts could be inserted into the users table for the ones that got whacked, the posts would show up again and even display the user names in the posts (although the actual information for the user in the users table would still be gone.) But at least the threads would make sense again. And if someone wanted their old user name and account back, that information could be inserted in the new dummy accounts on the back end.

This web site describes the phpBB relationships -
phpbbdoctor

Another option would be to assign all those accounts to the anonymous user (user_id = -1). This is the forum's "dummy user." We wouldn't know who posted them but they would show up. I guess we aren't sure who posted them relying solely on the database.

Something like -

update phbb_posts
set poster_id = -1
where poster_id not in
(select user_id from phpbb_users)

Hope this helps,
Dave

[Guest]

So all my missing posts -- which would be every single thing I ever posted before the crash -- would reappear but I wouldn't have to claim them as mine? This could be interesting.

[Guest]

So all my missing posts -- which would be every single thing I ever posted before the crash -- would reappear but I wouldn't have to claim them as mine? This could be interesting.

Except for when you signed your name at the end of the post...



Dave

[Guest]

[topic_id] [int] NOT NULL ,
[forum_id] [int] NOT NULL ,
[poster_id] [int] NOT NULL ,
[post_time] [int] NOT NULL ,
[poster_ip] [char] ( NULL ,
[post_username] [char] (25) NULL ,
[enable_bbcode] [smallint] NULL ,
[enable_html] [smallint] NULL ,
[enable_smilies] [smallint] NULL ,
[enable_sig] [smallint] NULL ,
[post_edit_time] [int] NULL ,
[post_edit_count] [smallint] NULL
) ON [PRIMARY]

Well here's the way I look at it:

CREATE TABLE [FAM BIB FSHIP posts]
[poster_id] [intelligent] IDENTITY (1, 1) NOT DULL ,
[poster_id](1,2) [int] NOT DULL ,
[poster_id](1,3) NOT DULL ,
[poster_id](1,4) [int] NOT DULL ,

[poster_ip] [good CHARacter] but DULL ,
[poster_username Paidion] [VARiable char] DULL ,
[enable_barbaric_code] [smallint] DULL ,
[enable_hOtmEAl] [smallint] DULL , HuNGrY
[enable_smiles] [smallint] DULL ,
[enable_poster(x,x) [smallint] DULL ,
[PRIMARY][user] stv_grgg [int][good CHAR] NOT DULL

[Guest]

Thanks Dave for that link.

Your update query would definitely be the quickest way to recover the posts, but it would also forever consign those posts to the "dummy" user and we'd never be able to reconstruct them back to their original user (if that's even possible).



Michelle,

It looks like you're using your old name instead of MichelleM. So I think your old old posts are there, but your new old posts are not (does that make sense )



Paidion,

Thanks for having too much time on your hands, that gave me quite the chuckle.


Just FYI, I've PM'd John to see what the possibilities might be. I'd really like to see if we can get old posts restored for the benefit of future users (and for our own reference as well).

[Guest]

Michelle,

It looks like you're using your old name instead of MichelleM. So I think your old old posts are there, but your new old posts are not (does that make sense )

Yeah, that would makes sense but there are no posts of mine anywhere on the forum except the ones from the past month. It's ok.

[Guest]

one more thing...

John, (if you read this)

You may already know, and it may not be fixable, or important, but the "Who Is Online" counter does not seem to be registering guests since the latest crash.

[Guest]

Yeah, I cant seem to re-register at all. Thankfully, i had an old account, so I am able to continue posting. Perhaps this problem will be fixed soon.