Need testers for some big changes on staging

Which is a bit old. If you have to mess with php it might be worth seeing if you can get the site working with version 8 while you are at it?

I tried. Some of the packages we use changed in ways that would require a lot of recoding. If that's the solution, I'll do it, but it'll take time.

Pity to divert V2 for that if we can avoid it.

That was my thought exactly.

Adding new unicode works see this post, so it is not the php.

Once we can see exactly what bits the database is storing for those old ones we could look at scripting a query that goes through and replaces those with the correct points.

This does sound like a problem that others must have come across, maybe there is an existing solution somewhere on google or the mysql forums. What I saw yesterday indicated they knew about issues with mysql 5's utf8 implementation.

Are these two text fields coming from the same piece of data? Because one is rendered properly (Games Dropdown menu) and the other is not (Forum title):


Suggests the underlying data is correct, but it is not being rendered properly in places.

That's incredibly useful because yes, there's only one source of data now.

Ok, it's likely a PHP issue then. The header displays data via AngularJS. The body displays data is straight from PHP.

It was on main and got corrupted by import/export. (I added it after the first import in case there would be another one after Keleth mentioned testing other alphabets.) New posts in Cyrillic on staging do look as they should.

If there is anything that would be useful to test, let me know! :D

That seems correct.

Running the query:
select * from forums where forumID = 8582;
yields
forumID title description forumType parentID heritage order gameID threadCount
8582 ?‍♂️ Suggestions Add suggestions for improvement here. f 8003 0002-8003-8582 1 3181 29

Which shows the emoji (in the terminal). So it seems the data is correct.

The function that Adam pointed to (Thanks Adam!) is in a php file and converts utf-8 to single byte format (description). That sounds a lot like the culprit we are looking for. Did it get put back in during the refactor?

That function isn't in the production code, nor is it in the staging one, so unfortunately it's not the issue.

The data is being corrupted when we put it in through PHP as well. Looking at the data from the new post 1705621, which displays correctly on the staging site, the command line mysql query:

select messageFullText from posts where postID = 1705622;
shows
new unicode test ?, ?, ?, ?, ? ????? ? ? ?? ? ? ? ? ? ? ?

So it is being translated twice and stored wrong, probably to deal with the old database storing it wrong in the first place.

Wait, so new stuff works, both in saving and printing. So that means it's not a PHP issue? Then wtf is the problem. I have no idea what to look at anymore. I'm looking at the code that saves a post to the database, and it doesn't do any modification to the post. Not even tag stripping, which is a big deal. The print part doesn't do any modification for forums.

The old data in the database has correct utf8 strings, but displays wrong.

Anything newly entered has corrupted utf8 strings in the database but displays correctly, so something broke the uft8 before it got to the database, and is fixing it again on its way out of the database, this 'fix' is corrupting the correct utf8 in the database on the way out so it does not display correctly.

What happens if you do this on the main site? If it's stored like this on the main site, then maybe it's being "fixed" during the migration.

main site new unicode test — ю, щ, ц, ы, ф ??‍♂️ ? ? ?️ ? ⏳ ✅ ? ? ❌ ?

Mysql command line on main site for the the above post yields:

messageFullText
main site new unicode test — ю, щ, ц, ы, ф ??‍♂️ ? ? ?️ ? ⏳ ✅ ? ? ❌ ?

So the main site is storing the data in a way that mysql 5 on the main site understands.

Data imported into the staging database comes out correctly, as per the above post. Only data entered via the website comes out wrong.

New information: MySQL 8.4 doesn't like MySQL 5.5's utf8mb4 data?

I took the code on prod and put it on staging, put up the same data, and it all worked like prod. That was expected and all good. I then updated the tables from utf8 to utf8mb4 (the new table structure we want). It continued to work, emojis and all. Then I did a backup of that data and updated to 8.4. When I imported the utf8mb4 data from 5.5, emoji's showed up broken again.

So... I don't know.I'm looking at the code, and it doesn't seem to be doing anything weird. I'm writing up some test code to do as raw a data test as I can.

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