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!
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!
Comment