[ITW] Character & Collation converter script

2

Fri Oct 30, 2020 3:45 pm
Clan Leader
Top Dog
2611 Posts
coRpSE
In-Game
CRSED

Most Played:
This week: 34.1hrs.
Total Played: 211hrs.
  
For many of you, if you are a site owner, especially and Evo site owner, you will need this, but you don't know it yet. Just recently, I noticed an error in my database when I ran an SQL, and upon research of this error, I found that uft8, which is the character set that Evo uses, is depreciated in MySQL 8.0, and will be removed in the future.

Well, you may think, "Well coRpSE, didn't you already make a conversion script to change the character set and the collation of a database?". The answer is yes, yes I did. "Well, cant we just use that, and the answer is yes and at the same time, no.

What do I mean by that? Well, you can use it but no all tables will be affected. The reason for this is, any table that has a key value using varchar(255) will throw an error, because utf8mb4 uses 4 bytes for each character, vs utf8 which used 3 bytes. Well, varchar can only have a maximum of 1000 bytes, so 255 x 4 = 1020 which is over the allowed 1000. To fix this, you have to edit each varchar to be 250 instead of 255. 250 x 4 = 1000. So what was once varchar(255) now needs to be varchar(250). That conversion script I made before will not change the varchar since I am not sure if it's even possible to distinct if the table is using varchar(255).

Now, if you followed that is the slightest, then you have to understand that I now had to go through the default DB of Evo, and find all the varchar(255), and write each individual update. I think there was something like 137 of them, then I have a few others. I also had to find away around several other issues that have come up. Well, the status of the update script is, I am about 90% done with it. I put in about 10 hours on it yesterday researching, and trying different things, and today, doing a bunch of individual calls. I am hoping to maybe be done with it in a couple of days. There is no rush because utf8 will still be around for a while, but, I rather get this done now, than later.

If your interested in getting your site updated, follow this post because I will be doing a video with it because of the complications that can occure.


Expand
Forums ©