Public service announcement: Check your MySQL backup!

bzcomputers said:
ALL tables should convert.

View attachment 266797
Click to expand...

😵 OMG!

Thanks buddy for showing me this!
I did notice already the Collation column in the main database index in the past, but completely forgot about!

So I looked into it and I can see that this is the only non-compliant table I have in my live site and that I'm then going to convert:

1649018249750.webp​

AndyB said:
View attachment 266802

Source: https://xenforo.com/community/threads/convert-myisam-to-innodb.202456/post-1558164
Click to expand...

Thanks, Andy! (y)

I was just about to ask if these were regular or not:

1649018312083.webp​
 
hellreturn said:
default-character-set=utf8mb4

MySQL 5.7 doesn't start when I add above line in my.cnf config. Anyone else facing same issue?

Code: 
mysql -V
mysql  Ver 14.14 Distrib 5.7.41, for Linux (x86_64) using  EditLine wrapper
Click to expand...

MySQL :: MySQL 5.7 Reference Manual :: 10.5 Configuring Application Character Set and Collation

dev.mysql.com dev.mysql.com
stackoverflow.com

How to set the default character-set?

I want to create a new db in mysql. I set utf8 as the default-character-set, but when I attempted to restart the db, mysql failed to restart and posted the following prompt: Trying to start the se...
stackoverflow.com stackoverflow.com

Hth...
 
mazzly said:
I managed to do this when moving my forum over to Centminmod (thanks @eva2000 for a superfast LEMP stack)

All posts/threads/messages/whatever from a year back (when we did XF1.5->XF2 upgrade) had emojis showing as ?

I noticed this too late (2 days after migration) and wasn't able to simply roll back the database (because of new threads/posts etc)

Luckily I still had the old database up and running and could do the following "merge dump" on original server:
Code: 
mysqldump --skip-add-drop-table --no-create-info --no-create-db --replace --default-character-set=utf8mb4 -u USERNAME -p DBNAME xf_post xf_thread xf_profile_post xf_profile_post_comment xf_poll_response xf_poll xf_conversation_message xf_unfurl_result > dump.sql

Then I copied the file over and ran this on the new server:
Code: 
mysql --default-character-set=utf8mb4 -u USERNAME -p DBNAME < dump.sql

Which then replaced the previously wrongly imported data with correct encoding, while still allowing newly added threads/posts/messages stay intact.

Hope that helps someone who notices this too late! :)

I guess if you still have the original dump available, you could restore it in an identical environment and then do the above dump command to get a "merge dump", your mileage might vary :D
Click to expand...

I recommend following this method. Also, I'd include xf_conversation_master in the tables list, as it holds the title of the conversations, just in case someone used emojis there, too.
 
Apologies for the necroposting. I'm looking at backups from MySQL 5.7, and even with the following mysqldump command:

mysqldump --opt --single-transaction --default-character-set=utf8mb4

Code: 
DROP TABLE IF EXISTS `import_log_xenforo22_1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `import_log_xenforo22_1` (
  `import_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `content_type` varbinary(25) NOT NULL,
  `old_id` varbinary(100) NOT NULL,
  `new_id` varbinary(100) NOT NULL,
  PRIMARY KEY (`import_log_id`),
  UNIQUE KEY `content_type_old_id` (`content_type`,`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1771 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

The character_set_client value mentioned by Kier is still showing utf8.

Would these still have the issue with emojis?
 
MaximilianKohler said:
I think the title of this post should be changed to point out that this is for older versions of Xenforo. Newer ones have this solved by default.
Click to expand...
Since which version?

The autobackup module in the AAPanel still scrambles up the UTF8MB4 backup data, rendering all emojis wrecked.
That's why OP's suggestion is still of vital necessity.
 
MaximilianKohler said:
What is this?
Click to expand...
Tracy answered this above. I need to mention something:

1. AAPanel is the English version of Baota panel targeting markets outside of Mainland China. Itself is free but they sell enterprise-level services.
2. Till now, Baota panel has issues with Debian 12 while installing MySQL. I guess that they have limited human resources in maintaining the codebase.
3. Baota panel recompiles apache / nginx / mySQL components from the scratch in lieu of using the available builds from APT / DNF. I dunno why.
 
Hello,

Is this still a concern in XenForo 2.0? I did find a line similar to the following in our database:

Code: 
/*!40101 SET character_set_client = utf8 */;

However, we migrated to a new host and apparently, I don’t see an issue with older posts with smilies.

We were on cPanel earlier and now using DirectAdmin for database backups. Please let me know if a change in configuration is still required.

Thanks
 
You must log in or register to reply here.
Back
Top Bottom