Error after update MySQL5 to MySQL8

[FerScrich]

Hi:
I'm running vBulletin 4.2.5 with PHP5
I needed to switch from one server to another. PHP version is the same with the same modules, and Apache too. The only change was with MySQL that changed to MySQL8.
The first error i found is that new MySQL server was configured to use utf8 as default so in includes/config.php i set the charset as my database is in latin1:

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

Apart from that i didn't noticed any other error until today that i tried to add a subscription and system fails with message:

Invalid SQL:
UPDATE vb_usertextfield SET
rank = '<img src="images/ranks/usuario_VIP.png" alt="" border="" />'
WHERE userid = 26;

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = '<img src="images/ranks/usuario_VIP.png" alt="" border="" />'
WHER' at line 2
Error Number : 1064
Request Date : Tuesday, April 6th 2021 @ 07:36:44 AM
Error Date : Tuesday, April 6th 2021 @ 07:36:45 AM 

Seems that the error is that rank is a reserved word and should be marked as that right?

[Wayne Luke]

Rank was added as a reserved word in 8.0.2 so you would be correct. However the development of vBulletin 4.2.5 ended before the release of MySQL 8.0.2 so there won't be a fix made available. Unfortunately, this happens after software reaches end of life and there isn't an easy way around it. You can manually resolve the issue by changing rank to `rank`.

[FerScrich]

Rank was added as a reserved word in 8.0.2 so you would be correct. However the development of vBulletin 4.2.5 ended before the release of MySQL 8.0.2 so there won't be a fix made available. Unfortunately, this happens after software reaches end of life and there isn't an easy way around it. You can manually resolve the issue by changing rank to `rank`.

Hi:
Yes but where do i need to change rank to `rank`?
I changed in: includes/class_dm.php line 600

$sql .= "\r\n\t$fieldname = " . (isset($this->rawfields["$fieldname"]) 

To:

$sql .= "\r\n\t`$fieldname` = " . (isset($this->rawfields["$fieldname"]) 

Seems to work, I'm not sure if is the best solution but at least i can add subscriptions.

[Wayne Luke]

Hi:
Yes but where do i need to change rank to `rank`?
I changed in: includes/class_dm.php line 600

$sql .= "\r\n\t$fieldname = " . (isset($this->rawfields["$fieldname"]) 

To:

$sql .= "\r\n\t`$fieldname` = " . (isset($this->rawfields["$fieldname"]) 

Seems to work, I'm not sure if is the best solution but at least i can add subscriptions.

If it resolves the issue then it is the place. However, there may be other locations in the code. I can't provide a definitive answer if this is the only place.