XF 2.1 Error importing users

[duderuud]

I'm trying to import my production database into a test environment.

Importing of users fails with:

Error restoring dbname.xf_user from file dbname.xf_user.sql.gz: Duplicate entry '?????' for key 'xf_user.username'

Problem is, I cannot find the entry ????? in the dump file when I edit it in a texteditor. Anyone has any idea?

 

[Kruzya]

Looks like a problem with encoding in MySQL.

 

[duderuud]

Source server: MariaDB 10.4 with utf8mb4_general_ci collation
Destination server: Mysql 8.0 with utf8mb4_0900_ai_ci collation

Shouldn't be a problem I guess?

PS. Tried to dump with mysqldump and mydumper. Same results...
PS2. Total database is about 30GB in size with the duplicate user being the only issue. If it was encoding I would see more errors...

 

[duderuud]

Changed MySQL 8 to MariaDB 10.4 and the import went fine. Only seeing a difference in the usercount so that will keep me occupied

 

[JulianD]

I'm having the exact same problem when migrating my 16.04 server to a 20.04 with MySQL 8. When I try to import the database using the mysql command, I get that error on the xf_user table.

Any advice ?