|OS type and version
||SUGGESTED - MariaDB 5.5.68
I’m following these instructions to migrate a server:
Migration path is:
- Generate backup and SCP to another server
- Rebuild the server from CentOS 7.9 to AlmaLinux 9.2
- SCP the backup back to the server
After the restoration, nearly all emojies in the database tables are replaced with question marks “?”.
I’m using utf8mb4_unicode_ci collation for all the tables.
How keep emojies during the migration? They are quite widely used in my app.
Any ideas or suggestions appreciated .
BR; More / Helsinki
Keeping up my monologue. Found out that mysqldump default character set is utf8. When backing up and restoring with it, I get exactly the same result.
With mysqldump, adding parameter --default-character-set=utf8mb4 when backing up resolves the issue and emojis are transferred correctly.
If/when Virtualmin backup-domain is using mysqldump, which I suspect, is there a way to add this parameter for “virtualmin backup-domain” command?. The documentation mentions “–option” parameter, but there are no details if it could be used in this scenario.
Anyone, any thoughts?
are you now using mariadb database server in replacement for mysql community server ?
if so you may find that mariadb does not understand the encoding of your database/table/field.
This would be worth checking
I would make the next attempt. From the backup I would use (if it is OK) everything except the database. I would make an SQL dump including database create line from the CLI in the source VM, I would go to the destination VM where I would create a database with the same format then I would import the dump. We basically did the Virtualmin part manually.
MariaDB seems to support all character set and collation features, that is not the problem, I’m pretty sure.
ok no worries I had the issue … the collations were all wrong took hours to manually fix it !
Thanks and yes, that is my plan-B. I just have some ten virtual servers to be handled. Well… mysqldump is pretty easy tool to backup/restore.
If you succeed, it means that there is an issue in Virtualmin related to database backup.
If you going from mysql to mariaDB I guess (is that a bug though), I don’t think VM backup/restore converts between the 2.
No-no, sorry being unclear. I’m going from MariaDB to MariaDB.
In this case, I doubt that a full SQL dump taken from one side to another will not work.
@Jamie any thoughts on why encoding is breaking on backup restore? I assume it’s related to the big version jump between the MariaDB in CentOS 7 and Alma 9, but that’s a guess, and I’m sure there’s some incantation to prevent it.
Just to be clear, there’s the same issue when backing up and restoring locally in one and same CentOS 7 server.
Oh, that’s surprising, and useful information. So, I guess we’re not keeping up with encoding or overriding it somewhere.
I thought I addressed this issue already in this patch:
But that would require Webmin 2.102+ to have installed.
@Ilia @Joe Thanks for the feedback!
I updated Webmin to the latest, and got 2.101. But as far as I can see, the mentioned collation fix is include starting 2.100. I double checked from the disk and yes, I seem to have it.
However, after testing again (backup and restore in the same system), the problem persists.
Ilia, isn’t the fix you mention relate to collation and not character set? The problem I have is definitely character set related, so collation fixes won’t help me.
The UI “Backup Database” seem to have “Character set for backup” option, which is exactly what I need. I just can’t find the parameter in the command line “virtualmin backup-domain” command.
Not 2.100. 2.102. Virtualmin repo does not have it yet. Sorry!
Jamie, just released Webmin 2.103. You can take a small risk and install it manually to see if that works for you.
Also, I would strongly advised against using CentoOS 7 for clean installs in the 4Q of 2023. You should try Rocky 9 or Alma 9 or Debian 12 or Ubuntu 22.04.
Thanks a lot, I’ll try it out later.
And yes, Alma 9 is the ultimate target. This is all about migrating my servers from CentOS 7 to fresh-built Alma 9.
Unfortunately, no luck:
- Updated Webmin 2.103 from the RPM. The dashboard shows “Webmin version 2103”. So, all good.
- Prepared a virtual server with small table - db, table and columns are in utf8mb4 character set. And some data with emojis.
- Backed up on command line with “virtualmin backup-domain…” command
- Deleted the virtual server
- Restored back with “virtualmin restore-domain…”
The same result as before: Nearly all emojis are replaced with ? characters.