Upgrade from vBulletin 4.2.2 to vBulletin 5.1.3 PL1 corrupts publishing times in blogs. Where are records located?

[jdj]

I upgraded my live site last Thursday night from vBulletin 4.2.2 to vBulletin 5.1.3 PL1. After the upgrade I didn't notice immediately but the dates for one of the members' blogs had changed to dates in the future, most of them in 2015 which means that nobody else's blog shows in the blogs tab because the system thinks that they are all older. Nothing else was done to these blog posts to change them and the dates were displaying correctly immediately prior to upgrade. The blogs were appearing in the correct chronological order, we were using the blog widget in the vB4 CMS and it was picking up the blogs and ordering them correctly. There were also no error messages during the upgrade, it all appeared to be working correctly, although it took a long time.

I put a support ticket in and the support ticket said that the only way to change this was to make direct changes to the database; but support wouldn't provide support for this.

We didn't do anything to the blogs themselves, all we did was run the upgrade script. So it was the upgrade that corrupted the database, if that's where the incorrect dates are now being served from.

I've taken a copy of the backup database, renamed it and I'm examining it in phpmyadmin in order to try and identify the correct dates so that I can then take the correct dates and put them in the relevant records in the live database. That ought not to be too difficult as although they are displayed in seconds and I'm assuming that's Unix time, it's possible to convert Unix time into Excel time in Excel and check the dates. So all I have to do is check the dates then copy and paste from one record to another. I can also do a trial run on my test installation to check that an amended date displays correctly.

So does anybody know:

- where in the vBulletin tables in vBulletin 4 are the dates that individual blogs are published
- where in the vBulletin tables in vBulletin 5 are the dates that individual blogs are published

Clearly, if I can identify where these records are it ought not to be difficult to make the edits.

Anyone know where these records are located?

[Lynne]

In vB4, the publish date for the blog is stored in blog.dateline

In vB5, the publish date for the blog is stored in node.publishdate (You can probably search node.title for the blog entry title.)

As always, you should test any database change on a backup first. And, before doing any database change on a live site, make a database backup.

[jdj]

Thank you.

[jdj]

In vB4, the publish date for the blog is stored in blog.dateline

In vB5, the publish date for the blog is stored in node.publishdate (You can probably search node.title for the blog entry title.)

As always, you should test any database change on a backup first. And, before doing any database change on a live site, make a database backup.

When you say the publish date, does this mean the date that the content was actually contributed to the site, or does this mean the date on which the contributor set the content to be published? In the vB4 CMS there used to be a facility saying 'Publish on' and you could choose the date at which the content went live. From memory you could also do this with the blogs.

PS: This server keeps giving me a message saying "Invalid Server Response" today.

[jdj]

When you say the publish date, does this mean the date that the content was actually contributed to the site, or does this mean the date on which the contributor set the content to be published? In the vB4 CMS there used to be a facility saying 'Publish on' and you could choose the date at which the content went live. From memory you could also do this with the blogs.

OK, I think I know the answer to this now. I went to the blog table in my backup vB4 file (the one taken before upgrade) in phpmyadmin and downloaded all the blog data into an excel spreadsheet. I then converted all the Unix times from seconds into dates in Excel so that I could read them and understand the problem. There is another column in the table that says "Pending". So now that I can read all the dates I can see that against all the dates that were future dates at the time of the upgrade there is a "1" in this column. Against all the past dates there is a "0". So the publish date is the publish date and the system only publishes the blog once the current date has passed.

So the upgrade didn't corrupt the data; vBulletin 5 just published all the pending articles even though they should not yet have been published.

Where in vBulletin 5 do I change the setting of all these pending articles back from 0 to 1, i.e. from actually published to pending? Where in the vBulletin 5 tables is this setting?

[Lynne]

The problem is that vB5 doesn't hae the options to post the Blog entries in the future, like vB4 did. That is probably why they all suddenly showed up as Published.

[jdj]

The problem is that vB5 doesn't hae the options to post the Blog entries in the future, like vB4 did. That is probably why they all suddenly showed up as Published.

So if you have a vB4 installation and you are using either the CMS, or the blogs, and you decide to upgrade to vB5, if any of your users are using the "publish on" feature rather than just hitting "publish now" then all their posts will just turn up as soon as you upgrade.

What options do you have in vB5 for manually managing the problem? How can you 'unpublish' them and then go back and publish as the to-be-published-on dates come up?

[Mark.B]

The only option for this is to manually edit the published status. vB5 does not have a facility to publish blogs on a future date.

[jdj]

The only option for this is to manually edit the published status. vB5 does not have a facility to publish blogs on a future date.

OK....so what options in vBulletin 5 do you have for "manually editing the published status"?

[jdj]

displayed in seconds and I'm assuming that's Unix time, it's possible to convert Unix time into Excel time in Excel and check the dates.

If anyone wants to know how to convert Unix time into real time using Excel (downloading the blogs table from MySQL into Excel is easy, it's one of the default downloads in MySQL).

Unix time is counted in seconds from 1/1/1970. There are 86,400 seconds in one day. So the formula in Excel is

=CELL/86,400+"1/1/1970"

That gives you the correct number of seconds and in order to display it in the correct format you right click in Excel, format cells, then choose "custom". In the custom field you use

dd/mm/yyyy hh:mm:ss

[Mark.B]

OK....so what options in vBulletin 5 do you have for "manually editing the published status"?

You'd have to edit each blog entry individually.

[jdj]

You'd have to edit each blog entry individually.

And do what specifically? Is your only option to "unapprove" the post, and if so, does the blog owner get a message telling them their post has been "unapproved"? What other options do you have?

[Lynne]

If you set node.approved to 0, then I believe only the user who created the entry and the moderators will see the entry (and it will say Unapproved below it). At least, testing that on my test site showed me that a regular user couldn't see the blog entry once I did that (and then did Clear System Cache). You may want to test this on your test site also before making the effort to do this.

[jdj]

If you set node.approved to 0, then I believe only the user who created the entry and the moderators will see the entry (and it will say Unapproved below it).

So the only option is to "unapprove" the posts and the users will see that their future posts have been "unapproved".

[Mark.B]

That is probably the best option yes.

If you're looking for a built in, automated solution to this where you just press a button or two - I'm sorry but there isn't one. As mentioned, the facility to do this is not built into vB5 so the best we can do for you is come up with workarounds, which is what we're doing here. I appreciate that it's not ideal, but that's where we are.