Upgrade from 3.8.7 to 4.2.5 to 5.5.5

[NH Steve]

Hello all, we just upgraded from 3.8.7 to 4.2.5 to 5.5.5 walking through the instructions in the sticky.

I know there are a lot of things to probably still do like rebuilding search indexes and things like that but before we get there, I was hoping to fix a couple of our issues.

First, when you try to upload a photo into a thread you get an error. After researching the error it has to do with the database collation:

Database error in vBulletin 5.5.5:

Invalid SQL:

SELECT
IF(filehash = MD5(filedata), 1, 0) AS filehash_matches,
IF(filesize = LENGTH(filedata), 1, 0) AS filesize_matches
FROM vb_filedata AS fd
WHERE filedataid = 25586

/**checkFiledataConsistency**/;

MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
Error Number : 1267
Request Date : Tuesday, December 3rd 2019 @ 01:26:41 PM
Error Date : Tuesday, December 3rd 2019 @ 01:26:43 PM
Classname : vB_Database_MySQLi
MySQL Version : 10.3.20-MariaDB-log-cll-lve

I saw some similar issues people have run into and I know that the collation needs to be the same in your DB.

After checking, it looks like we have about half the tables as utf8_unicode_ci and the other half is latin1_swedish_ci.

When doing the upgrade, there was a pop up box mentioning something about not being able to move forward with a certain charset unless we changed it so we did. In the config file we have this currently:

$config['Mysqli']['charset'] = 'utf8';

I assume my next step is to save a copy of the DB and then try to alter the DB with a query changing all the tables to UTF-8 collation? Are there any bad things that can happen from doing that?

Also, along the same lines, some of the posts have some funky characters in it instead of apostrophes. That also looks to be impacted by the charset so I went into the AdminCP and changed the HTML charset from ISO8859-1 to UTF-8 saved and looked again at the results.

Still have the funky characters but now they are different funky characters. They went from squares to &tm and similar characters.


Secondly, on another topic that I cant seem to find info on. We have a "Journals" section where members can go post stories and post pictures and is set up to not allow any replies. So its an ongoing journal that can only be replied to / posted by the original author. I cant seem to find the setting to turn off replies for anyone else if you aren't the author.

Any advice is greatly appreciated, thanks in advance!

[Wayne Luke]

The database error is caused by changing server settings over the years. Many years ago, the default character collation was latin1_general_ci. Older versions of vBulletin used this default on new installation. When you upgrade, we continue to ask the database server what character collation to use. This value changed in newer servers to be something like ut8_general_ci. The server doesn't know how to compare the two because characters are in different sort orders based on the character collation. In the case of latin1 vs utf8, characters may not even be in the sort order for latin1.

You need to convert all of your tables to use the same character collation for every field. We provide some scripts to help with this. You can read about them here: https://forum.vbulletin.com/articles...database-tools

If it were a new install, we would have set everything to use UTF8MB4 as the character set and a collation of uf8mb4_general_ci. This is what we recommend. We don't do this on upgrades because it can break your data if not done correctly.

---

There is no permission to prevent users from replying to other user's topics but allow users to reply to their own topics. You should look into moving these journals into the Blogs feature. Each user can create their own blog and determine if others can comment on each individual entry or not.

[NH Steve]

Thanks for the quick reply and explanation Wayne, I am reviewing the link you posted!

[NH Steve]

Looks like that's pretty involved and tbh, I have never done anything with the command line. I am going to see if my host server admin can help unless you or someone on your team can assist? Also I didn't know if it was better to keep asking questions in this thread or start a new topic so let me know if it should be the latter:

- Using LATEST ACTIVITY mode instead of POSTS, is there a way to get the post numbers to show up?

- When I post the time is set to 1 hour in the future. I have the server settings and my personal user settings set correctly GMT - 5:00 (Eastern Time), any other ideas?

Thanks again!