Forum Software Upgrade

feedback, comments and suggestions pertaining to the *. forums
paulb
Posts: 764
Joined: Mon Jan 20, 2014 9:02 pm

Re: Forum Software Upgrade

Postby paulb » Mon May 16, 2016 12:46 pm

BigEd wrote:
danielj wrote:Probably the easiest thing is to just run an update query for the rogue characters along the lines of

Code: Select all

UPDATE thingytable SET column = REPLACE(column, 'roguecharacters', 'propercharacter')


Unless anyone can think of a reason why that wouldn't work?


Depends on whether there's a latin-1 equivalent for all the characters people have used... it doesn't seem like an ideal solution to me.


I think you'd switch the phpBB encoding back to UTF-8, if it has subsequently changed itself to something else, then you'd do something like...

Code: Select all

update phpbb3_posts set post_text = convert(post_text using utf8) where post_time > UPGRADE_TIME


Do it in a transaction and then roll it back if it doesn't look as if it did the right thing, of course.

User avatar
danielj
Posts: 5141
Joined: Thu Oct 02, 2008 4:51 pm
Location: Manchester

Re: Forum Software Upgrade

Postby danielj » Mon May 16, 2016 12:51 pm

=D> Great stuff Roland :D

d.

User avatar
sorvad
Posts: 2172
Joined: Wed Aug 24, 2005 12:13 pm
Location: Back of beyond

Re: Forum Software Upgrade

Postby sorvad » Mon May 16, 2016 7:11 pm

seconded, amazing effort being put into this at the moment =D>

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Tue May 17, 2016 6:17 am

OK guys,

{ Damn ... I posted this yesterday on my test server :oops: }

I have found a way to partially update the forum. With these commands:

Code: Select all

mysqldbcompare --server1=root:root@localhost  --difftype=sql --skip-diff --skip-object-compare  --skip-row-count stardot1:stardot2 > dbdiff.sql
grep -v UPDATE dbdiff.sql > fixdb.sql


I can do most of the updates in the database except the update-queries. I want to skip them because there are very much update-queries that write the bad characters back into the database. So here is my plan:

  • On Wednesday, early in the morning, I'll disable the forum again
  • I create a new database backup
  • I download this backup to my test computer
  • I generate the "diff" file as in the code above
  • I restore the backup from Sunday with the correct encoding
  • I run the "diff" script to re-enter the new topics, posts, pm's etc.
  • Re-enable the forum and expect most of the stuff to be working again

This will take a few hours because of importing very big SQL scripts and transferring large amount of data. There might be a risk that some stuff might get corrupted because I'm messing with the database. If we detect any problems within 48 hours we can decide to restore the latest backup and discarding any new posts since then. We have three options at the moment:

1. Just leave it as is
2. Restore the backup from Sunday and loose about 120 posts
3. Try this solution

IMHO option 3 is worth a try:

Screen Shot 2016-05-16 at 21.47.03.png


This post is imported on my test server so it might succeed this way. But I cannot guarantee that everything will be find. The special characters in the last 120 posts will be malformed but those are easy to restore by the posters if it's necessary.

Your opinions please ....
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
BigEd
Posts: 1397
Joined: Sun Jan 24, 2010 10:24 am
Location: West
Contact:

Re: Forum Software Upgrade

Postby BigEd » Tue May 17, 2016 6:24 am

Certainly something like option 3 is worth a try: you have backups and you have a way to roll forward the deltas between them.

But I'm not entirely comfortable with the grep -v UPDATE - how many lines does that remove, and what do they look like? If it's only looking through the data which has been added since the restore, why is it seeing anything bad?

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Tue May 17, 2016 6:44 am

Most of the update queries are administrative tasks, like the number of views, downloads etc. I can include the updates except updates on the phpbb3_posts table. If I leave them also then the "bad" characters will return.

If I leave out all the phpbb3_posts updates on the message body and subject then there will be a few queries that moved some posts to another forum_id. So I will also include the other update queries as well. And the same for private messages.
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
1024MAK
Posts: 6680
Joined: Mon Apr 18, 2011 4:46 pm
Location: Looking forward to summer in Somerset, UK...

Re: Forum Software Upgrade

Postby 1024MAK » Tue May 17, 2016 8:24 am

Roland, that sounds like a plan :D

Good luck.

Mark
For a "Complete BBC Games Archive" visit www.bbcmicro.co.uk NOW!
BeebWiki‬ - for answers to many questions...

User avatar
BigEd
Posts: 1397
Joined: Sun Jan 24, 2010 10:24 am
Location: West
Contact:

Re: Forum Software Upgrade

Postby BigEd » Tue May 17, 2016 2:29 pm

Hmm, I still don't understand what bits you're aiming to exclude when bringing in the post-backup changes. But I can see you're testing as you go, so I'm sure you'll only go live if all looks good.

User avatar
sweh
Posts: 1833
Joined: Sat Mar 10, 2012 12:05 pm
Location: New York, New York
Contact:

Re: Forum Software Upgrade

Postby sweh » Tue May 17, 2016 2:39 pm

I'd say "go with option 3".

Some edge cases that may be worth testing:
* Unread message counts
* New user creations
* Profile changes
* Search index
* Image uploads

But, overall, I think option 3 is the right approach.
Rgds
Stephen

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Wed May 18, 2016 6:53 am

StarDot server was unreachable this morning. It wasn't me [-X
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Wed May 18, 2016 10:25 am

1024MAK wrote:Me 'alf's have been abused :shock: (link)
Mark


Your 'alf's are back to normal. So is Dave's signature and all the other specials chars. Except the special chars that were posted in the last couple of days.

I think we're back now.

Please check if everything works fine for you! Please make local backups in local text files if you have important posts because we will return to today @7:00am if there still are severe issues with the board.
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
1024MAK
Posts: 6680
Joined: Mon Apr 18, 2011 4:46 pm
Location: Looking forward to summer in Somerset, UK...

Re: Forum Software Upgrade

Postby 1024MAK » Wed May 18, 2016 11:38 am

roland wrote:StarDot server was unreachable this morning. It wasn't me [-X

Yeah, I noticed that the site was down when I tried at around 00:30 (UK BST) last night. I thought work was being done on the site so didn't worry about it.

Thanks for the forum fix =D>

Mark
For a "Complete BBC Games Archive" visit www.bbcmicro.co.uk NOW!
BeebWiki‬ - for answers to many questions...

User avatar
BigEd
Posts: 1397
Joined: Sun Jan 24, 2010 10:24 am
Location: West
Contact:

Re: Forum Software Upgrade

Postby BigEd » Wed May 18, 2016 11:57 am

All looking good for me - thanks for battling with this until it submitted!

User avatar
lurkio
Posts: 1157
Joined: Tue Apr 09, 2013 11:30 pm
Location: Doomawangara
Contact:

Re: Forum Software Upgrade

Postby lurkio » Wed May 18, 2016 12:03 pm

Just checking to see if I can get the pound-sign to display properly.

£

Yes, it seems to work here but not when I edit a recent post in another thread. I guess that's to be expected..?
Last edited by lurkio on Wed May 18, 2016 12:10 pm, edited 1 time in total.

User avatar
1024MAK
Posts: 6680
Joined: Mon Apr 18, 2011 4:46 pm
Location: Looking forward to summer in Somerset, UK...

Re: Forum Software Upgrade

Postby 1024MAK » Wed May 18, 2016 12:10 pm

Right, I've 'ad a quick look around :-P
All looks okay, no problems spotted so far...
Even Arcadian's avatar is back :lol:

Symbol test:
£&@$€

Need to use a PC to try the others, as too much like hard work on these battery powered radio linked paperweights :roll:
So well done StarDot team =D> \:D/ =D> \:D/ =D>

Now, after all that good work, when is the next scheduled excitement, I mean upgrade due?

Mark
For a "Complete BBC Games Archive" visit www.bbcmicro.co.uk NOW!
BeebWiki‬ - for answers to many questions...

User avatar
BigEd
Posts: 1397
Joined: Sun Jan 24, 2010 10:24 am
Location: West
Contact:

Re: Forum Software Upgrade

Postby BigEd » Wed May 18, 2016 12:21 pm

lurkio wrote:... but not when I edit a recent post in another thread.


Just tried to PM you with a reply to that message, and it looked (from the preview) as if it would have been OK. Here's the text:
Just for interest, trying to edit the special characters in the post you linked.

Subject: Intelligent Adventures For The Electron And BBC

LOLprice of £403

missing the characters "£" (pound) and "#" (hash)


Which is to say, I did have to tweak the two pound signs to remove the stray character ahead of them, but that was easy and it was enough.

User avatar
lurkio
Posts: 1157
Joined: Tue Apr 09, 2013 11:30 pm
Location: Doomawangara
Contact:

Re: Forum Software Upgrade

Postby lurkio » Wed May 18, 2016 12:31 pm

BigEd wrote:
lurkio wrote:... but not when I edit a recent post in another thread.
Just tried to PM you with a reply to that message, and it looked (from the preview) as if it would have been OK. Here's the text: ... Which is to say, I did have to tweak the two pound signs to remove the stray character ahead of them, but that was easy and it was enough.


Thanks. Yes, the preview always looks fine, but I've tried editing that post a few times now, and I still can't get the pound-sign to display properly. Not a big deal to me, but I just thought I'd report it. (New posts in the same thread can display the character properly.)

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Wed May 18, 2016 12:36 pm

1024MAK wrote:Now, after all that good work, when is the next scheduled excitement, I mean upgrade due?


I have learned a lot about the server and the forum so I expect the next excitement to be a bit more relaxed. I now know how to restore the database after a failed update :lol: And besides, I now have a test system to perform a test update.
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
hoglet
Posts: 6390
Joined: Sat Oct 13, 2012 6:21 pm
Location: Bristol

Re: Forum Software Upgrade

Postby hoglet » Wed May 18, 2016 12:43 pm

Roland,

Many many thanks for stepping up to this challenge. I know how nerve-racking production systems can be.

Well done for getting it sorted.

If you don't mind me asking, where did things go wrong at the weekend? Was it using MySQL directly to restore the backup, rather than the phpBB restore tool?

Dave

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Wed May 18, 2016 12:44 pm

lurkio wrote:Thanks. Yes, the preview always looks fine, but I've tried editing that post a few times now, and I still can't get the pound-sign to display properly. Not a big deal to me, but I just thought I'd report it. (New posts in the same thread can display the character properly.)


I can change them \:D/

The trick is to remove the two back characters, save the post and then edit the post again and add the £ sign. So if it is really important in a post then this is the way to fix it.
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Wed May 18, 2016 12:53 pm

hoglet wrote:Roland,
Many many thanks for stepping up to this challenge. I know how nerve-racking production systems can be.
Well done for getting it sorted.
If you don't mind me asking, where did things go wrong at the weekend? Was it using MySQL directly to restore the backup, rather than the phpBB restore tool?
Dave


The phpBB restore failed. We didn't find out why at that moment as we couldn't find the log files. That's also why we couldn't fix the failed update issue. Now I know where to find them :mrgreen: When I installed my test system and played with it, I found out where de log files are and it turned out that the phpBB restore failed because of the php max_execution_time being too low (60s). I did an import of the database backup via the mysql command line and I still don't understand why the characters were malformed.

We didn't expect the update to fail, after all, it's not a major update so we didn't test it before and as the board was hacked in the past we also didn't want to wait longer with the update. But now it seems to last even longer :oops:
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
kieranhj
Posts: 489
Joined: Sat Sep 19, 2015 10:11 pm
Location: Farnham, Surrey, UK

Re: Forum Software Upgrade

Postby kieranhj » Wed May 18, 2016 12:56 pm

I lost a PM and a post from late last night (around 11pm UK time) BTW. Not sure if this was upgrade related?

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Wed May 18, 2016 1:49 pm

kieranhj wrote:I lost a PM and a post from late last night (around 11pm UK time) BTW. Not sure if this was upgrade related?


Probably not because yesterday late in the evening the server got also into troubles. It was rebooted before I started my work this morning. If you really want it, I can have a look at the backups to see if I find your PM and post.
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
kieranhj
Posts: 489
Joined: Sat Sep 19, 2015 10:11 pm
Location: Farnham, Surrey, UK

Re: Forum Software Upgrade

Postby kieranhj » Wed May 18, 2016 8:15 pm

Don't worry, not important. I can repost. Thanks.

User avatar
BigEd
Posts: 1397
Joined: Sun Jan 24, 2010 10:24 am
Location: West
Contact:

Re: Forum Software Upgrade

Postby BigEd » Thu May 19, 2016 12:43 pm

I'm finding I cannot change my notification settings, or successfully unsubscribe from a thread.

User avatar
BigEd
Posts: 1397
Joined: Sun Jan 24, 2010 10:24 am
Location: West
Contact:

Re: Forum Software Upgrade

Postby BigEd » Thu May 19, 2016 12:58 pm

Not sure if this could be related to the recent changes, but I see quite a number of image attachments with no inline image. For example
http://www.stardot.org.uk/forums/viewto ... 379#p52379

User avatar
paulv
Posts: 3557
Joined: Tue Jan 25, 2011 6:37 pm
Location: Leicestershire
Contact:

Re: Forum Software Upgrade

Postby paulv » Thu May 19, 2016 1:39 pm

BigEd wrote:Not sure if this could be related to the recent changes, but I see quite a number of image attachments with no inline image. For example
http://www.stardot.org.uk/forums/viewto ... 379#p52379


They've been like that since at least the major forum upgrade happened a few months ago... Whether more have gone that way since, I don't know but I believe it's been done to reduce bandwidth by making images load like that so they're on demand which will help with mobile and slow network connections.

Paul

User avatar
BigEd
Posts: 1397
Joined: Sun Jan 24, 2010 10:24 am
Location: West
Contact:

Re: Forum Software Upgrade

Postby BigEd » Thu May 19, 2016 1:53 pm

Ah right, thanks.

User avatar
roland
Posts: 2779
Joined: Thu Aug 29, 2013 8:29 pm
Location: Born (NL)
Contact:

Re: Forum Software Upgrade

Postby roland » Thu May 19, 2016 2:49 pm

BigEd wrote:I'm finding I cannot change my notification settings, or successfully unsubscribe from a thread.


Is that only at existing subscriptions or also new subscriptions? I had no problems with new subscriptions but I don't have any older subscriptions.
256K + 6502 Inside
MAN WOMAN :shock:

User avatar
danielj
Posts: 5141
Joined: Thu Oct 02, 2008 4:51 pm
Location: Manchester

Re: Forum Software Upgrade

Postby danielj » Thu May 19, 2016 2:56 pm

So in the user control panel you select "Manage Subscriptions", then mark the ones you don't want anymore, then click "Unwatch Marked" at the bottom right? I have some from before the upgrade and it'll let me unsubscribe to them?

d.


Return to “stardot.org.uk”

Who is online

Users browsing this forum: No registered users and 2 guests