Change UTF8 to UTF8MB4

[mesteele101]

Just changed all mine through phpmyadmin and didn't get any errors. Seems to be ok.

Can you give us a blow by blow action report on how you did this?

[NoGi]

I did mine a little while back now via phpmyadmin. Ran a similar query table by table and manually fixing up the index columns as they came up with the string too long error.

The other thing I did was to change the default collation for MySQL as well.

[Owdy]

i can tell you, its a lot of work, i better will wait for a converter from invision!

I want that too

[xtech]

Unfortunately it's not that simple. Some of the indexes are created with a length based on 3 bytes per char in mind. So when you try and change the collation on those tables you get an error about indexes being too large. To fix it you have to completely delete the indexes on that table, recreate them with a length suitable for 4 bytes per char. Then you can change the collation.

I did it on my board by deleting all the indexes and then changing the collation type in conf_global.php. Then when you run the support tool it spits out the commands for recreating the indexes - even with the correct index lengths for utf8mb4. Not for the faint hearted, it took about 5 hours to recreate.

Info about the index issue: https://mathiasbynens.be/notes/mysql-utf8mb4 

 

That was true for IPS 3.4.x, but i suppose not anymore for 4.x. I tried at the time to convert to utf8mb4 as i was using windows server, but i had the problem of the indexes length. At the time, ips told me that 3.4.x did not support utf8mb4. As ips4 supports it, there won't be any trouble.

Edited June 23, 2015 by xtech

[mesteele101]

The thing we have to think is: "is that so important to use emojis that they worth converting to utf8mb4?" Even some browsers cannot display them properly!

If your site runs fine and you do not need emojis, do not convert to utf8mb4: there are also some disadvantages to it also, like the increased database space, inherent to the use of 4 byte character encoding. The full UTF8 implementation needs 4 bytes for some characeters, it's true. But if see the table of the 4-byte characters, i doubt you use them.

In a normal UTF8 use, 99.99% of the caracters used are 1, 2 or 3 bytes.

According to my experience, Utf8mb4 is a must for the fellows using Windows Server and IIS, because it will solve many character conversion problems arising from the odd php implementation on windows.

We all need to be looking to the future and possible problems that not converting will eventually cause.

This has nothing to do with IIS, its a new suggested standard for MySQL, and they actually suggest that utf8mb4 is the new standard that everyone should be trying to achieve. IPS has adopted this at it's new standard character set.

It seems to me that it would be in the best interest of IPS to make sure everyone running IPB 4.x is running the same character set. This will eventually come back and byte everyone at some point.

[TSP]

I know I did at least one utf8 to utf8mb4-conversion with the IPS stand-alone converter script when I prepared some 3.4 forums a while back. So at least it worked for me. 

(There was a bug in that it wouldn't work to get it over to utf8mb4 without issues before all tables had been converted to utf8_unicode_ci-collation though.)

Edited June 23, 2015 by TSP

[mesteele101]

That was true for IPS 3.4.x, but i suppose not anymore for 4.x. I tried at the time to convert to utf8mb4 as i was using windows server, but i had the problem of the indexes length. At the time, ips told me that 3.4.x did not support utf8mb4. As ips4 supports it, there won't be any trouble.

IPS not only supports it but is new standard for IPB. At this point it looks like anyone converting from 3.x to 4.x will automatically be converted to the new utf8mb4 standard. However those of us that was so inclined to convert prior to IPS adding this auto conversion to utf8mb4 will be stuck on utf8 for the remaining time were utilizing IPB.

I was getting indexing length errors. I created a .sql file using phpmyadmin and converted all the utf8 statements to utf8mb4 and imported to a new database. I received the index error. How do you get past that in phpmyadmin. I don't mind spending the time doing this on a test database until I get it right for the final conversion.

[mesteele101]

Ok, I think I found something that may make it easy to convert but I'm not able to grep in Windows:

http://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5

If someone can verify it works that would be great, and I'll load up something giving me access to grep.

[AutoItScript]

It won't work. You'll still come across the index too long issue as I keep saying...  Well you're not going to be happy until you get a step by step so try this. At your own large risk!

• Backup your board.
• Take the board offline
• Backup your board!!!!
• Add/Change conf_global.php to include 'sql_utf8mb4' = true
• In the ACP goto Support -> Something is not working
• It will spit out hundreds of MySql commands, copy them to a text file (there's no chance of them working from Fix Automatically unless your board is small)
• Run the commands in phpmyadmin one by one or in small batches. Make a note of the tables that give errors about indexes being too long

For the tables with index-too-long error:

• Goto the table / Structure tab.
• Scroll down and click on "+indexes"
• Delete all but the primary indexes
• Rerun the Support tool in the ACP, it should give you the correct command to recreate the index with the correct length

Edited June 24, 2015 by AutoItScript

[opentype]

IPS not only supports it but is new standard for IPB. At this point it looks like anyone converting from 3.x to 4.x will automatically be converted to the new utf8mb4 standard. However those of us that was so inclined to convert prior to IPS adding this auto conversion to utf8mb4 will be stuck on utf8 for the remaining time were utilizing IPB.

Stop spreading these false information. The UTF8 type is a user-choice during installation or upgrade. There is no “automatic” conversion, because that wouldn’t even work, e.g. when your database runs on MySQL 5.1. In addition, your are not “stuck” with UTF8. Its YOUR MySQL database on YOUR server. You can do whatever you want with it. If you are not able to, maybe you should better move to IPS’ clould solution. But don’t expect IPS to become your server admin. 

Edited June 24, 2015 by Ralf H.

[NoGi]

For the tables with index-too-long error:

• Goto the table / Structure tab.
• Scroll down and click on "+indexes"
• Delete all but the primary indexes
• Rerun the Support tool in the ACP, it should give you the correct command to recreate the index with the correct length

The ones that are currently erroring for me via ACP are these:

here are some problems with your database. Normally it is safe to try to fix these problems automatically however if your community is large, you may want to run the necessary queries manually. If so, the queries to run are:

ALTER TABLE `gallery_albums` CHANGE COLUMN `album_name_seo` `album_name_seo` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '';

ALTER TABLE `gallery_images` CHANGE COLUMN `image_parent_permission` `image_parent_permission` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '';

ALTER TABLE `blog_blogs` CHANGE COLUMN `blog_authorized_users` `blog_authorized_users` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '';

ALTER TABLE `cms_databases` CHANGE COLUMN `database_key` `database_key` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '';

ALTER TABLE `nexus_licensekeys` CHANGE COLUMN `lkey_key` `lkey_key` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '';

I've already converted them to MB4 with the smaller varchar length but it looks like this is trying to put it back to 255 which is why it's giving the error.

[AutoItScript]

It's ok for the column to be VARCHAR(255) it's the index that needs to be shorter. In my cms_databases the column database_key is VARCHAR(255) but the index for database_key is (191)  (4 x 191 = 764 which is close to the max index length under Innodb). You should be able to drop the index and the support tool should give you a new command to recreate the index that uses the right value. 

You might need to drop the index and change to utb8mb4 with the commands you have above before recreating the index. i.e. converting the columns while the index is not present.

Edited July 1, 2015 by AutoItScript

[NoGi]

My example is:

in mysql:

blog_authorized_users	varchar(191)	utf8mb4_unicode_ci

 

From auto repair

ALTER TABLE `blog_blogs` CHANGE COLUMN `blog_authorized_users` `blog_authorized_users` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '';

Specified key was too long; max key length is 1000 bytes

 

I can't see any associated index?

[AutoItScript]

This is what it looks like for cms_databases. Table -> Structure -> Indexes

 

[NoGi]

Yeah, my cms database key is the same. But what does it show for your blog_authorized_users column in blogs_blogs table? That's where one of my problems is. Mine is currently varchar(191) but the database check in ACP wants to make it varchar(255) and fails. I think this is where my upgrades are failing on database check as well.

[AutoItScript]

I don't have blogs. But you are still looking at the wrong place, double check the arrows on the screenshot. Scroll right down to the bottom of the page you showed and click in Indexes. You almost certainly need to drop the index for blog_authorized_users.

Edited July 1, 2015 by AutoItScript

[NoGi]

When I click on index down the bottom, it creates an index. A good chance I am still not doing this right though.

[AutoItScript]

DROP INDEX blog_authorized_users ON blogs_blogs;
DROP INDEX blog_authorized_users_2 ON blogs_blogs;
DROP INDEX blog_authorized_users_3 ON blogs_blogs;

Then the support tool should work.

Edited July 1, 2015 by AutoItScript

[NoGi]

OK, I think we may be talking about 2 different things. Even with the index gone I am still getting the error in ACP. Like I mentioned above, I've got the varchar already set to 191 but ACP keeps wanting to set it to 255 which is where it's failing.

Index gone:

ACP doesn't say to recreate, gives same error:

Confirmed through phpmyadmin:

In summary, I think I have it setup right at varchar(191) but, ACP and the upgrades fail on database check as IPS4 wants it to be varchar(255) which the utfmb4 collation won't allow me to do.

[AutoItScript]

Trust me VARCHAR(255) is correct for the column. But it won't "take" until there are no more varchar indexes. I need to see the structure of your indexes so we can see which other ones we need to drop as well. Let me see if there is a mysql command to show them because your phpmyadmin is not showing them.

show indexes from blogs_blogs;

Edited July 1, 2015 by AutoItScript

[NoGi]

Like this one?

Actually, setting that to ignore seems to have worked.

[AutoItScript]

Yeah that's one of them. So you are looking for indexes with varchars in them. In the case above if you "drop index auth_user on blogs_blogs" the support tool should recreate it with the right values. In my case I actually dropped all the indexes on problem tables and let the tool recreate them all. Far quicker than inspecting each one.

You don't want to be ignored things on indexes, you want them nuked and created properly.

Edited July 1, 2015 by AutoItScript

[Owdy]

Ok, i think ill stay in normal utf8...

[NoGi]

Ok, i think ill stay in normal utf8...

I made the move to UTFMB4 while I was still on 3.4.7. I just had 4 or 5 columns left that were giving me issues which I've now resolved with @AutoItScript's help. At least it's all done now and I don't have to worry about it in future.

Edited July 1, 2015 by NoGi

[LaCollision]

It won't work. You'll still come across the index too long issue as I keep saying...  Well you're not going to be happy until you get a step by step so try this. At your own large risk!

• Backup your board.
• Take the board offline
• Backup your board!!!!
• Add/Change conf_global.php to include 'sql_utf8mb4' = true
• In the ACP goto Support -> Something is not working
• It will spit out hundreds of MySql commands, copy them to a text file (there's no chance of them working from Fix Automatically unless your board is small)
• Run the commands in phpmyadmin one by one or in small batches. Make a note of the tables that give errors about indexes being too long

For the tables with index-too-long error:

• Goto the table / Structure tab.
• Scroll down and click on "+indexes"
• Delete all but the primary indexes
• Rerun the Support tool in the ACP, it should give you the correct command to recreate the index with the correct length

Hi!

Thanks for sharing your experience

I might need some further explanation!

When I add 'sql_utf8mb4' => true in the conf_global.php, and then I go in the ACP > Support > Something is not working, I don't get the MySQL commands.

Did you change all the fields collations to utf8mb4 before going in the ACP > Support?

Thanks a lot