Convert to utf8mb4 after upgrade

[sijad]

Hi, one of my clients upgrade to IPS4 without converting to utf8mb4 and i need to know how i can change charset to utf8mb4, is there a tool like convertutf8? or is it safe to upgrade to utf8mb4 manually (from cli or phpmyadmin)?
i can see there's an option in conf_global.php named sql_utf8mb4 in a fresh install of IPS4 but i can't see this on a upgraded conf_global.php, should i add this option to conf_global and set it as TRUE?
should i change sql_charset in conf_global to utf8mb4 as i can't see this option in a fresh install of IPS4

thanks in advanced

[steve00]

Not sure how they upgraded without it converting as when upgrading it informs you that have to convert to utf8mb4 ?

[AutoItScript]

I don't know if it's of use, but I couldn't get the IPS utf8 converter working so I had to change my collation to utf8_unicode_ci myself with this script:

<?php
	$db = mysql_connect('localhost','USERNAME','PASSWORD');
	if(!$db) echo "Cannot connect to the database - incorrect details";
		mysql_select_db('FORUMDATABASE'); 

	$result=mysql_query('show tables');

	while($tables = mysql_fetch_array($result)) 
	{
		foreach ($tables as $key => $value) 
		{
			mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
		}
	}

	echo "Finished!";
?>

You might be able to modify it to convert to utf8mb4. You'll need to run it at the command line as it will timeout on large databases. Use at your own risk, take backups, etc etc

I've not done a utf8mb4 install so I can't help with the conf_global.php options.

[AutoItScript]

Not sure how they upgraded without it converting as when upgrading it informs you that have to convert to utf8mb4 ?

​You only have to be on utf8_unicode_ci to upgrade.  When you do a clean install you get the option of utf8_unicode_ci or utf8mb4. They must already be on utf8_unicode_ci.

[Kirill N]

@steve00 @AutoItScript Thanks for the responses guys, I'm the client I'm a total MySQL noob so I asked my host to perform the conversion for me, here's their response, is this correct?

I have changed the the "character_set" and "collation_database" of the database "ipboard" to utf8mb4. Please verify the following snippet. 

+++++++++++++++++++++++++++++
mysql> use ipboard;
Database changed
mysql> show variables like "character_set_database";
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like "collation_database";
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_unicode_ci |
+--------------------+--------------------+
1 row in set (0.00 sec)

+++++++++++++++++++++++++++++++

[AutoItScript]

It sounds like he's might have just changed the overall database collation, you have to do it at the database, table and each column on each table. (That's why I needed the script to do it, too much manual work).

If you run this mysql query it should show the collation of each part http://stackoverflow.com/questions/4948356/query-to-show-all-tables-and-their-collation 

[Kirill N]

It sounds like he's might have just changed the overall database collation, you have to do it at the database, table and each column on each table. (That's why I needed the script to do it, too much manual work).

If you run this mysql query it should show the collation of each part http://stackoverflow.com/questions/4948356/query-to-show-all-tables-and-their-collation 

​Thanks, I ran that query and this is what it returned:

 

[sijad]

​Thanks, I ran that query and this is what it returned:

 

​Hi, please run this query change {{DBNAME}} with DB name (I've attached file)

 

query.txt