Public service announcement: Check your MySQL backup!

Forgot to say that $config['fullUnicode'] = true; was already on my config.php file... 😵‍💫

By the way, here is what I get if I run the command on my live XenForo, only ONE table to convert:

1649010068564.webp


Also, is there a way to find out which table is going to be converted?


Sorry for such noob questions, but this is definitely not my job at all 😅
 
With many users, they'll be relying on the import features in their web control panel (cPanel, Plesk, etc.) rather than command line in moving from another server.

I'm not aware of any such modifications you can make in the first two, and had no problems doing a cPanel to Plesk transfer.
 
El Porcharo said:
Forgot to say that $config['fullUnicode'] = true; was already on my config.php file... 😵‍💫

By the way, here is what I get if I run the command on my live XenForo, only ONE table to convert:

Also, is there a way to find out which table is going to be converted?
Click to expand...

ALL tables should convert.

utf8mb4.webp
 
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​
 
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
 
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...
 
So I had this before... and it works.. but adding that mb4 is an issue.

character-set-server = utf8
collation_server = utf8_unicode_ci

@Masetrix - I am not sure, if I follow both of your links. I think Kier is mentioning to make utf8mb4 has default one.
 
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.
 
Well.. I still occasionally find some old threads with ? instead of emojis, even if I followed instructions the day it was published.

It would be helpful to understand why it's happening...
 
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.
 
You must log in or register to reply here.
Top Bottom