Issue converting Database to UTF8 Before Upgrading

[gabs007]

Greetings. I'm planning to upgrade my community and move it to a new server. This way I can kill 2 birds with a stone. I thought migration would be easier by keeping a copy of the community in the old server, just in case ! 

However, I'm having a serious issue trying to convert to utf-8. I'm not even sure it's converting the data. 

When I pass the  ips4.php inspection in the actual server (which will be the old server), I get this:

Quote

You are not running a compatible version of PHP. You need PHP 5.5.0 or above. You should contact your hosting provider or system administrator to ask for an upgrade.  (not a big deal, since new server uses 5.6)

Some or all of the columns in your database are not using the utf8_unicode_ci collation (admin_login_logs.admin_ip_address is utf8_general_ci). Download the UTF8 Database Converter

I'm running MYSQL Version 5.1.73 in the old server.

 

I move the whole plesk data to the new server with  php 5.6  and mysql  5.5.50 
When I pass the ips4.php inspection, it says I have to convert the database only. This is the message in red I get.

Quote

• Some or all of the columns in your database are not using the utf8_unicode_ci collation (admin_login_logs.admin_ip_address is utf8_general_ci). Download the UTF8 Database Converter

Everything else is in green.

My conf_global.php file has this parameter

Quote

$INFO['sql_charset']            =    '';

I load the utf converter and I get this message:

Quote

The database is set to UTF-8, however 315 table(s) have incorrect collations and need fixing.

If I change my conf_global.php  to include utf-8 like   

 

$INFO['sql_charset']			=	'utf8';

Then I get an error when the website loads.

Quote

FATAL ERROR

Your settings could not be read by IP.Board. This is a fatal error and IP.Board cannot function while this issue persists.

This issue is generally caused by changing your character set in the ACP to one that does not support data stored in the rest of your settings, or by restoring a database backup/completing a server transfer and importing your database tables using the wrong character set or collation. You should contact IPS Technical Support for further assistance.

My assumption is that database is not set to UTF8. When I replace the conf_global.php file, the website works again.

 

From here, it doesn't matter what I do to convert the database to utf-8 and to fix the incorrect collations.  What I've done so far is correcting the collections, and run the conversion. I've done it using the website, and also using the command line.  Everything seems unaffected. The website works fine after the conversions, as long as the sql_charset is null, if I modify the conf_global.php file to include this:

$INFO['sql_charset']			=	'utf8';

I get again the same "fatal error". 

If I pass the utf8convertor it tells me that the database has been converted and everything is ok.  I think there are 2 options to convert data to new utf8 formats. I always choose the first one, the one without emoji support. 

If I load the isp4.php file with the data "converted", it says I'm ready to upgrade to ISP4. It's obvious the convertor has achieved something, because it says now I'm ready. 

 

I can upgrade to ISP4 and everything works fine. But when I load the new website, it is a mess because all data seems not converted or converted wrong. I put an example.

 

I've been trying this a few times with the same exact result.  
With IPS 4 I can change the "sql_charset" of the conf_global.php to "utf-8". It does not affect anything. It can be null or utf-8. 
Of course I can edit posts and replace the  broken text, but it's just too much work.

 

Do you have any suggestion of what I'm doing wrong here ? I have the feeling the problem has to do with the conversion, but I don't know what I'm doing wrong.

Help appreciated. 

 

 

 

[TSP]

I would submit a ticket to IPS and ask for their assistance here. As it's difficult to figure out what the issue is, without access to your servers. 

However, if you would like to try to debug a bit more on your own: 

If the tables are already imported and classified as utf8, then the tables may have been imported with the incorrect character set to the database. If you have access to and knowledge on how to run manual SQL queries I would run the following queries to get more information from the Admin CP on both servers. Run them from the SQL toolbox in the admin CP:

SHOW TABLE STATUS;

Scroll horizontally until you see the column "Collation". Scroll down as you look for the general state of collation on all tables, for example: what collation appears the most frequent? Note any exceptions to the general rule and what table it is and the collation classified. Note any differences in the result you see between how it is on the old and new server. Note what you've changed on the new server compared to the old server at the time of comparison.

SHOW VARIABLES LIKE '%char%';

Feel free to share the result of this query for both servers.

Also feel free to explain how you exported the database from the old server and imported to the new one. What tools did you use, which options did you choose.

 

 

[gabs007]

Ok, since  I tried again and I have the test IPS site updated,  I will advance the data I have after upgrading.  Later (in a few hours) I will  restore everything again, and I will catch data before the upgrade.

I don't actually export the database. I use the Plesk migration tool.  From the new server, I send the root password and I get a copy of the whole system from the old server: database & files. After importing, the website seems to be working fine,  except for the IP, I have to rename my host file with the new server ip.  I have used plesk migration tool in the past with wordpress websites and  I didn't have any issue.

So far I can advance some data after IPS has been upgraded:

SHOW TABLE STATUS;

All tables are 

 

SHOW VARIABLES LIKE '%char%';

 

** ADD:  in the original server with msyql 5.1.73 I have the same exact configuration with IPS 3.4.7

 

 

Maybe the character_set_server with "latin1" value might have something to do ?

And finally this is how values are stored in the database. With the wrong data.

 

 

 

 

 

 

 

 

[TSP]

Ideally you should run the "SHOW VARIABLES LIKE '%char%'"-query from within the admin CP-panel in the SQL toolbox. That is because then you'll get the connection charset the actual IPS installation uses (which may not be the same as in phpmyadmin). Do this on both the current installation on the old server and the test installation on the new one. 

(The first query doesn't matter where you run it from)

character_set_server only tells the default charset for new databases on the server. 

[gabs007]

The website working now in the "old" server has this configuration.

However, phpmyadmin in the old server says this:

 

 

The new IPS SQL Toolbox with the upgraded IPS4 has this configuration

 

[TSP]

Okay. I don't remember the details on this, and really I find charsets to be quite the pain to work with. I'm glad they chose to only support utf8 on IPS 4, as that will be a godsend in the long run for everyone. 

It seems that on your old installation there has been inserted latin1-data to an utf8-database. This is because your "old" forum installation connects to the server with the latin1-charset, it doesn't help that your database is utf8, the connection needs to be as well. You can't simply change the connection charset (sql_charset in conf_global.php) to utf8 on the old server to resolve it either, since then it attempts to read latin1-data with utf8-data, which doesn't work flawlessly and likely crashes IPS 3.4 due to settings saved as serialized arrays. 

IPS 4 only supports utf8 and utf8mb4, so in that case it doesn't really matter what's configured in conf_global.php, IPS 4 will use one of the variants of utf8 regardless. 

I really feel you should submit a ticket on this, but you'll likely need for them to resolve the situation from your current installation, as they don't support test installation. 

In the ticket, provide them with the following information: 

• Access details, Admin CP and FTP credentials to your live installation ("old" server/current live installation)
• The date you ran the utf8-converter script on the current live installation/"old" server (if you believe the board was first installed with utf8, then specify this)
• Tell them it's a 3.4-installation (which is no longer supported), but that the issue prevents you from moving to a new server you have, where you intend to upgrade to IPS 4.1. They do support help in upgrading from 3.4 to 4.1 last time I checked, so this should be covered I think)
• Tell them you seem to have latin1-data that have been inserted into your utf8-tables because your connection charset was not specified to utf8 after the utf8-conversion you've done earlier. You're also unable to change the sql_charset to utf8 at this point for your current installation for the same reason. 
• Link them to this topic so they can see the entire discussion here if necessary

[gabs007]

Thank you very much for the help.

I have submited a ticket and I'm waiting for a reply. I hope there is a way to fix the data so I can transfer my community and upgrade it.

 

[TSP]

Great! Please keep us updated in this topic on how it goes

[gabs007]

I received a quick reply from support.   I'm really thankful.

Good news is that they will take care of the upgrade like a normal upgrade. IPS support will fix it during next week I guess.  I only had to move my 3.4.7 community to a new server.