Need testers for some big changes on staging

I know absolutely nothing about database structures, but does this shed any light whatsoever? https://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5

So I just did as raw a test as possible. I created a script that created a database connection, queried for forum 8003, and got back '💡 Gamers' Plane development'. That means it's not a retrieval issue.

So here's a great confusing example: https://gamersplane.com/oneRunScripts/check_data.php. That's the raw data from the database just printed out. It does not print out an emoji, as you can see. Why not?

I appreciate the link, and actually found it earlier today. It both added info and confusion.

Might it be possible to update incrementally, instead of 5.5 to 8.4 directly? Maybe the jump is what's causing the issue?

I also found this article which contains a detailed instruction on updating utf8 to utf8mb4: link. Perhaps it can be of use? It has pretty detailed steps.

Sorry. My thought was that if it is an issue with "regular" utf8 in 5.5 being brought into 8.4, the perhaps converting everything in place in 5.5 and then bringing it in to 8.4 may circumvent whatever is corrupting the translation. But as I said, I know almost nothing about databases per se. I just have some experience with general problem solving. Thanks.

A thought I shared earlier today too, leading me to that article. I actually did try that, and updating utf8 to utf8mb4 in 5.5 worked. Moving past 5.5 did not.

Maybe? But utf8mb4 should be the same regardless of version.
I actually found that and that led to me doing my earlier test of utf8 on 5.5 to utf8mb4 on 5.5. As you link see from the link above, I think it actually has something to do with getting PHP to show emojis. I think the fact that it's working now may be conincidental/accidental.

I've been attempting to look for various update instructions, and it looks like 5.7 is something of a barrier. One article actually claimed that you can't update 5.6 (and below, presumably) to 8.0 directly (which is questionable :'D). Maybe you can try go 5.5 to 5.7 to 8.4, since it's just one extra step? Maybe it tries to convert something along the way because 5.7 used different defaults.

5.7 is currently running on staging, and you can see it isn't working :/ I'll try something different tomorrow in regards to 5.7 (putting 5.5 utf8 on staging, upgrading to 5.7, then going utf8mb4). But 5.5 utf8 -> 5.5 utf8mb4 -> 5.7 apparently broke stuff.

I'm wondering if there's something different in how PDO communicated with 5.5 vs 5.7+? Honestly, I'm running out of ideas, and no one online seems to have answers besides "This is clearly a UTF-8 issue you're not handling properly" and "Have you tried (insert all the things I've mentioned here I've tried)".

At least we know that it breaks at this point, it's already new information!

And yeah, there isn't a lot on this topic online. :( I'm mostly running into your own posts and old Reddit/MySQL threads with not a lot of actual answers. I saw a Reddit instruction about updating from 5.1 to 8.0 incrementally, but it had no mentions of specific stuff regarding Unicode, unfortunately.

(By the way, I feel your pain in getting responses like "You asked wrong", it's really unhelpful even when done with good intentions.)

Also, an interesting piece of information I found:
utf8mb4general_ci is indeed used in a lot of aforementioned sources. That might be part of the problem too if you're using it instead of utf8mb4_0900_ai_ci? It is about 5.7 to 8.0, but still.

Interesting. Running the same query on the command line shows that php may be breaking things? Command line mysql prints the correct emoji.

But as far as I can tell, prod is just taking the data and dumping it out in the same way, so why does it work? Why does prod work but a test on prod doesn't?

Given that mysql appears to be returning the correct data on both prod and staging (I get the same output shown in the screenshot above from both), and the php script turns it into garbage on both, it looks to be php related, especially since you noted that where angular was involved it displayed correctly [ref].

If possible, can you spin up a container with newer php and see what your oneRunScripts/check_data.php does with that? Even if new php does not run the site, it will tell us if old php is doing this.

It may be a php setting in the container, and a fresh container with php8 (and no special config) would reveal some of that and tell us where to look next.

Yah, I'll try that. So for example, I got MySQL 5.7 working with the existing data. I then updated to MySQL 8.0, but that failed. So there's definitely SOMETHING up between 5.7 and 8 and PHP.

Ok, someone gave me a suggestion, and I've added it to the forum title of forums. Can someone check if titles are showing up ok, but posts aren't? If it's working as I hope (and my initial tests seem to suggest), then we know the problem and now have to figure out how to make a final solve. Also, if someone could make a forum with an emoji with it, it should not show up correctly. Let me know.

💡 Gamers' Plane development now shows all the emojis in the thread names.

A thread with emojis does not show them [ref].
When you say make a 'forum' do you mean a 'game'?

I create a game with an emoji in the title, it shows up correctly.

See the Game List and the Game Details page.

Let me know if that was not what you meant, or if that 'should not show up' was meant to be 'should now show up'. :)

The email about the new game calls it 'New Game: ⠌ emoji game' in the subject line, messing up the emoji.

In the body of the email is displays the emoji correctly as 'game for 2 players called " emoji game".' ... which did not copy paste correct? Strange? The text is actually 'game for 2 players called "â\235\214 emoji game".' but gmail is sorta fixing it to display?