wrong characters for romanian after upgrade to 5.6.5

[clauz]

Could this be related to the DB that have latin collation instead of UTF-8?
See attached images for the settings in Language and the error.

[Trevor Hannant]

What are the collations on your database tables?

Is the following line in your core/includes/config.php file commented out or not - and if it is, what's the value for this?:

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

[clauz]

the line is commented:
// $config['Mysqli']['charset'] = 'utf8';

My forum DB has both UTF-8 and latin tables:

[Wayne Luke]

You will need to change your Locale to a UTF-8 Locale. It should be ro_RO.utf8 but verify that with your hosting provider as they will be the only ones who know what locales they have installed on the server.

[clauz]

I changed locale to ro_RO.utf8 after I spoke with my Hosting provider.
Unfortunately I have another kind of wrong character (missing characters). If I want to correct them manually then the error will appear if I choose English language.
Because English has HTML character set ISO-8859-1 .

Maybe the solution is to convert DB to UTF-8 so I will be able to set also English to UTF-8?

[Wayne Luke]

ISO-8859-1 is a subset of UTF-8 characters specifically tuned to the English language. The HTML Character Set is not related to the database. It only tells the browser how to display characters. You should be able to change this to UTF-8 for English. All new installs and our language packs for English are currently using UTF-8.

[clauz]

Hello, I notice that users that have English language set on the forum are not affected . Can I change the language to English for all users by a MySql (PHPMyAdmin) query?
I don't want to delete Romanian language completely because I made a lot of changes in the translation and I don't want to loose it. Maybe later I can find a solution to use again Romanian.

[Trevor Hannant]

UPDATE user SET languageid=X;

where X is the ID of the language (you can get this from the admin CP).

[clauz]

In order to solve the issue I used DB Tools to update tables to INNODB and utf8mb4.
The conversion was somehow successfully , now we can use any character.

Anyway, for the Old posts not all characters were converted correctly, all î Î â Â are replaced to question mark .
Example:

instead of România there is Rom?nia

There is any fix for this?
If not, I was thinking to run a MYSql string replace . Is this correct?

UPDATE vb_text
SET rawtext = REPLACE(rawtext, 'Rom?nia', 'România')

how about this , the wilt character % can be used?

UPDATE vb_text
SET rawtext = REPLACE(rawtext, '%?n%', '%în%')

Which is the table with thread title, because I will also have to replace characters in topic titles?

Thank you.

[Wayne Luke]

When the system shows a question mark it basically means there is no glyph in the font to show that character. Searching the database for Rom?nia will probably not return any results because the ? isn't actually in the database. It would be the HTML Entity value for that character. In your example you would most likely want to search for România.

You can use an online HTML Entity encoder/decoder like this one: https://mothereff.in/html-entities

Put the text you want to see in the "decoded" field.

Though, what I would do is...

Go to one of the posts with the problem and get the node id by clicking on the #X in the upper right corner of the post. There will be a number in the URL. For example the URL for the post above this is: https://forum.vbulletin.com/forum/vbulletin-5-connect/vbulletin-5-installations/4463137-wrong-characters-for-romanian-after-upgrade-to-5-6-5?p=4464161#post4464161

You're looking for the number after post#. Then run a query on the database to see what the text actually looks like.

SELECT * FROM text WHERE nodeid=4464161

Replace 4464161 with the number of your post. Then you can use the results to replace text in the database at large. I recommend making backups as you update text so you can revert from any mistakes.

[clauz]

Thank you for your help. Unfortunately the result is "?" not the HTML encoder.
See an example of return result from DB:

Turneu B+ "WTTD - AmaTur Rom?nia", Constanța - Duminică 

It seems that all other diacritics are fine except for this case where I have "?" directly in the DB.
When I search for "Rom?nia" in vb_text I found over 1000 entries:

SELECT * FROM `vb_text` WHERE rawtext like '%Rom?nia%'

Showing rows 0 - 24 (1351 total, Query took 0.2103 seconds.)

Can I still use the replacement query even if the character is "?"
Can I use wildcard % in the query?
What should be the exact UPDATE query?

[Wayne Luke]

I don't know why the data would have been replaced previously. Only vBulletin 5.3 and above really support UTF-8. Previous versions (including 3 and 4) would have replaced those characters with HTML Entities when the record was created.

You would want to use the REPLACE function of MySQL. Here is a tutorial on this: https://www.mysqltutorial.org/mysql-...-function.aspx

[clauz]

I manage to make the replace using tables "text" and "node".
Thank you for your help. I hope I will not have troubles in the future, since now the DB is utf8_general_ci collation and tables are INNODB.

[Wayne Luke]

The best collation to use is either utf8mb4_general_ci or utf8mb4_unicode_ci. The utf8mb4 means each character takes 4 bytes to store and this is needed for combination characters like the emoji characters popular on mobile devices. General or unicode is how things are sorted. ci means case-insensitive. You can see these on this site when you click on the smilie icon within the editor menu as shown in the image below.

[clauz]

The best collation to use is either utf8mb4_general_ci or utf8mb4_unicode_ci. The utf8mb4 means each character takes 4 bytes to store and this is needed for combination characters like the emoji characters popular on mobile devices. General or unicode is how things are sorted. ci means case-insensitive. You can see these on this site when you click on the smilie icon within the editor menu as shown in the image below.

Sorry, my mistake, I wanted to write utf8mb4_general_ci.
My DB is converted with DBTools to utf8mb4_general_ci.