utf8mb4 and latin - encoding problem !

[Blackhorse]

Hello,

The repeated scenario : ) encoding problem !

I am on VPS Centos server, Php 7. 6, Mysql 5.7 with encoding problem

Running an Arabic forum, default charset and collation plus client of course were Latin BUT Data is utf8 (Arabic).

After upgrading to vb5, i realized the disaster when the hidden ghost revealed himself !! (a night mare) 12 years of data on the edge !

Months of reading and trials and asking for support, but I had to manage myself, prepared my tools. Despite disappointment, I admit that the last vBulletin upgrade to 5.5 is Marvelous and for the 1st time, site accept a utf8 language xml without objection.I can see there is a real advance in solving the matter. I can feel the solution is so close, but i am still not aware of it.

- Now, my test site is the old vb 5.4.3 on the same server, I tried to test the database charset and encoding conversion before i go ahead to run this process on the online 5.5 forum.

- I have updated my server to speak utf8mb4 charset and utf8mb4_uncode_ci, modified My.cnf, php.ini, and Locale.

- I have changed tables' engine to InnoDB

-Now, the Process of conversion, i have converted charset and collation twice:

The 1st time: by help of vBulletin tools, it was great. Smoothly converted data (the most needed tables) automatically, to utf8 and utf8_general_ci. I had to convert the rest manually - Done (EXCPET: cms grid, words and phrase). I have restarted the server, changed the locale, language xml file uploaded in utf8 BUT the whole sites changed to (blue red ?????.) as shown in the picture 1. and this was the message:

Error Invalid SQL: SELECT p1.varname AS p1var, p1.text AS default_text, p1.fieldname, IF(p1.languageid = -1, 'MASTER', 'USER') AS type, p2.phraseid, p2.varname AS p2var, p2.text, NOT ISNULL(p2.phraseid) AS found, p1.product FROM phrase AS p1 LEFT JOIN phrasetype AS phrasetype ON (p1.fieldname = phrasetype.fieldname) LEFT JOIN phrase AS p2 ON (p2.varname = p1.varname AND p2.fieldname = p1.fieldname AND p2.languageid = 20) WHERE p1.languageid = 0 AND p1.fieldname = 'global' ORDER BY p1.varname;

## /home/xxx/public_html/old/test/core/vb/database.php(1189) Exception Thrown #0 /home/xxx/public_html/old/test/core/vb/database/mysqli.php(201): vB_Database->halt() #1 /home/xxxxx/public_html/old/test/core/vb/database.php(557): vB_Database_MySQLi->execute_query(true, Object(mysqli)) #2 /home/xxxx/public_html/old/test/core/vb/db/result.php(126): vB_Database->query_read('Invalid SQL:\r\n\n...') #3 /home/xxxx/public_html/old/test/core/vb/db/result.php(63): vB_dB_Result->rewind() #4 /home/xxxx/public_html/old/test/core/vb/db/mysql/querydefs.php(5816): vB_dB_Result->__construct(Object(vB_Database_MySQLi), '\n\t\tSELECT p1.va...') #5 /home/xxxxx/public_html/old/test/core/vb/db/query/method.php(175): vB_dB_MYSQL_QueryDefs->fetchPhrases(Array, Object(vB_Database_MySQLi), false, false) #6 /home/xxxxx/public_html/old/test/core/vb/db/assertor.php(304): vB_dB_Query_Method->execSQL() #7 /home/xxxxx/public_html/old/test/core/includes/adminfunctions_language.php(289): vB_dB_Assertor->assertQuery('fetchPhrases', Array) #8 /home/xxxxx/public_html/old/test/core/admincp/language.php(865): fetch_custom_phrases(20, 'global') #9 /home/xxxxx/public_html/old/test/includes/api/interface/collapsed.php(142): require_once('/home/xxxxx/pu...') #10 /home/xxxxx/public_html/old/test/includes/vb5/frontend/controller/relay.php(33): Api_Interface_Collapsed->relay('admincp/languag...') #11 /home/xxxxx/public_html/old/test/index.php(74): vB5_Frontend_Controller_Relay->admincp('language.php') #12 {main}

I said it is due to config. file i ran quickly and changed it to utf; unfortunately site stopped : )))))

The 2nd time: i decided to convert manually to utf8mb4 and utf8mb4_unicode_ci. Again, all tables have been converted EXCEPT the above three (cms grid, words and phrase).The exact result copied from above happened for the second time. The same message, the same attached picture and site stopped once i uncomment utf8 in config file !

I know there must be something wrong in my approach, I need your assistance, just list steps to follow, what do you think i have missed? you can get from the above lines what i could not. I don't blame the 3 non-converted tables, i can empty the word table and try converting it. But i suspect it is a php problem i have to correct or modify, Why the head of html has no language meta?

I can use ssh if there is a solution through terminal commands, please enlighten me with.

Your opinion and support is greatly appreciated

[Blackhorse]

locale --> ar_SA.utf8 Charset variables attached please Wayne, your advice is urgently needed @Wayne Luke

[Blackhorse]

https://www.xxxxx.org/old/test//index.php

[Blackhorse]

Glenn Vergara Please, your guidance and help here

[Wayne Luke]

Why do you keep making additional topics for the same issue? This only slows down support of your issue.

In order to resolve the issue, we will need access to the command line of your server, access to the database, access to SFTP or SCP, and access to your AdminCP. You will need to purchase a Support Contract in order to do this.

[Blackhorse]

It is not meant to be additional topics. All the time, i am working on the problem and try to discuss each point i find or think of.
I have full control on the site, so Yes please, your support is needed.