XF 2.2 ø is seen as a normal o under utf8mb4_general_ci

unproperlypropagated

unproperlypropagated

Active member
Xenforo considers ø and o as different characters, allowing 2 different people having usernames like
yolo and yolø

But then when you export this database and try to re-import it:
Code: 
ERROR 1062 (23000) at line 7098: Duplicate entry 'yolo' for key 'xf_user.username'

Suggestion: instead of utf8mb4_general_ci use utf8mb4_unicode_ci (Accent-Sensitive)

I'm not sure about modifying the database so if an admin can reply it'll be grand.
 
Sort by date Sort by votes
If XenForo allows both and inserts both then that is consistent and as designed and is already accepted by the collation.

It would really only fail if you are importing it into a database with a different collation that doesn’t allow this or the exported database has exported both as yolo despite the original stored value being different.

You will need to ensure the database dump contains distinct values or the database you are importing into is the same collation as the source.
 
Upvote 0 Downvote
What I can tell you is that utf8mb4_general_ci treats o and ø as distinct letters. As long as the dump still contains them as separate characters (i.e. nothing has rewritten them both to yolo), and the destination database is also using utf8mb4_general_ci, there shouldn’t be an issue.

After all, if the distinct names “yolo” and “yolø” were inserted into the database originally, there’s no reason that should have changed now.
 
Upvote 0 Downvote
I think i know why this happens to me.
The source server is on MySQL 5.7 and the target is on MySQL 8.0
So it seems like the collation behaves differently on different versions.
Or I'm completely insane and need to be locked up.
 
Upvote 0 Downvote
See

Steffen

XF 2.2 Thread 'Changing the database collation to the MySQL 8.0 default "utf8mb4_0900_ai_ci"'

By default, XenForo uses the MySQL collation "utf8mb4_general_ci" which is supported not only by MySQL 8.0 but by older MySQL versions like 5.7, too. In MySQL 8.0, the new collation "utf8mb4_0900_ai_ci" became the default. It is more modern and has better performance.

Since "utf8mb4_0900_ai_ci" is accent-insensitive ("ai"), some usernames that are considered to be different in "utf8mb4_general_ci" are considered to be equal in "utf8mb4_0900_ai_ci" which means there will be collisions (e.g. "Andre" and "André") and that some usernames would have to be changed when making the...
 
Upvote 0 Downvote
which operating system are you running your database server on?

You may need to use the --hex-blob option when dumping your database to ensure you aren't losing data. In my experience, this doesn't seem to be a problem on Linux => Linux database transfers, but I did have this issue when importing in to Windows for my dev environment.

See this post/thread for more information:

Sim

Post in thread 'Public service announcement: Check your MySQL backup!'

x3sphere said:
Not a charset issue but sorta related -- I was testing some recent backups done with mysqldump and found that I could not restore them properly due to issues with the VARBINARY fields that XF uses.

From what I've gathered, it seems to be recommended to use the --hex-blob option if you have binary data in your database. I tried this and I can now import my backups without any issues.

When dumping tables through some other tools like phpMyAdmin, it seems to use the hex format by default, so not an issue when exporting there. At any rate I'm glad I caught this before I actually had to...
Click to expand...
  • Like
 
Upvote 0 Downvote
The import is ubuntu 18 > ubuntu 22
Mysql 5.7 > Mysql 8
I was able to import the database after manually renaming "duplicated" users.
But now I get these errors as if Xenforo allows a username that's already registered


Code: 
Server error log
XF\Db\DuplicateKeyException: MySQL query error [1062]: Duplicate entry 'DankGabe' for key 'xf_user.username' src/XF/Db/AbstractStatement.php:230
Generated by: Unknown account Mar 10, 2025 at 5:00 PM
Stack trace
INSERT  INTO `xf_user` (`visible`, `activity_visible`, `user_group_id`, `timezone`, `language_id`, `last_summary_email_date`, `username`, `email`, `user_state`, `privacy_policy_accepted`, `terms_accepted`, `display_style_group_id`, `secondary_group_ids`, `secret_key`, `user_id`, `username_date`, `username_date_visible`, `style_id`, `style_variation`, `permission_combination_id`, `message_count`, `question_solution_count`, `alerts_unviewed`, `alerts_unread`, `conversations_unread`, `register_date`, `last_activity`, `trophy_points`, `avatar_date`, `avatar_width`, `avatar_height`, `avatar_highdpi`, `avatar_optimized`, `gravatar`, `security_lock`, `is_moderator`, `is_admin`, `is_staff`, `is_banned`, `reaction_score`, `vote_score`, `custom_title`, `warning_points`, `wp_user_id`, `ozzmodz_usiu_upload_date`, `andrew_user_note_count`, `andrew_privileged_user_note_count`, `andrew_reg_country`, `ap_ss_avatar_frame`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------

#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1062, '23000')
#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1062, '23000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(219): XF\Db\AbstractAdapter->query('INSERT  INTO `x...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1572): XF\Db\AbstractAdapter->insert('xf_user', Array, false)
#5 src/XF/Mvc/Entity/Entity.php(1300): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Service/User/RegistrationService.php(320): XF\Mvc\Entity\Entity->save()
#7 src/addons/OzzModz/VerifyEmail/XF/Service/User/Registration.php(76): XF\Service\User\RegistrationService->_save()
#8 src/XF/Service/ValidateAndSavableTrait.php(42): OzzModz\VerifyEmail\XF\Service\User\Registration->_save()
#9 src/XF/Pub/Controller/RegisterController.php(451): XF\Service\User\RegistrationService->save()
#10 src/addons/OzzModz/Spaminator/XF/Pub/Controller/Register.php(134): XF\Pub\Controller\RegisterController->actionRegister()
#11 src/XF/Mvc/Dispatcher.php(362): OzzModz\Spaminator\XF\Pub\Controller\Register->actionRegister(Object(XF\Mvc\ParameterBag))
#12 src/XF/Mvc/Dispatcher.php(264): XF\Mvc\Dispatcher->dispatchClass('XF:Register', 'Register', Object(XF\Mvc\RouteMatch), Object(OzzModz\VerifyEmail\XF\Pub\Controller\Register), NULL)
#13 src/XF/Mvc/Dispatcher.php(121): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(OzzModz\VerifyEmail\XF\Pub\Controller\Register), NULL)
#14 src/XF/Mvc/Dispatcher.php(63): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#15 src/XF/App.php(2826): XF\Mvc\Dispatcher->run()
#16 src/XF.php(806): XF\App->run()
#17 index.php(23): XF::runApp('XF\\Pub\\App')
#18 {main}
Request state
array(4) {
  ["url"] => string(18) "/register/register"
  ["referrer"] => string(76) "/register/?email_verification_code=q-Kc6HCXhlaCppcO"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(15) {
    ["_xfToken"] => string(8) "********"
    ["0b389fa9a482813fa38e2fd6997c5ef8165e9b6d"] => string(8) "DankGabe"
    ["url"] => string(0) ""
    ["email_verification_code"] => string(16) "q-Kc6HCXhlaCppcO"
    ["4ec8a1f453f0b637fd714844b0340a6847da181a"] => string(8) "********"
    ["password_confirm"] => string(8) "********"
    ["additional_security"] => string(0) ""
    ["cf-turnstile-response"] => string(1008) "0.IIGZ7VpeT_vM3UeNTcD1Lh-dXZ1GaAGpHpISgqkL74ucBwMwBDAaEwoZjAWy3ZwYinmME-mUI9UVU8Ay-csNOczrYn8ORF2xaLcZvDXv4eem9zjppX92fi0BHGQEM5_8x_7RS7P1ob0dEmRM_q2XwuRY8uIVdQFP4XyQ1BBxPjRBakqfnVYFIamGqGQOgeN7sykkQFGaKdwhVC68-Fc9rybSo8cXDGr7rR8zhonz90IsV4Fk-CGWncs-nV-hm_XYL6mrbmx-bcV2KSAoZNabVq-XJKS5U4wtSZ98pbc9I2C1iqszFjLfGgJMXe_eXMQe8y-3AEIGKSpg4tkAnmiDNnon86cv9FWBN_4qc_sUstgcbBPa7IRn_ngKRH8UQnfNPfpWQ7JT4IgX63B7xwk472FGn-sT4Bxsw5O1lEM2HiWvmbCaTJB-eipn4cmtWb1hEQbIseBV1rgBV6qEqPnPohjqQE7eCCgkE09RfyR3Xa7doEgM1XkQq3kPj_JBnRT2ZrsGdDuCLfLdvoI69Te46mIsAmxlS0axwKt8zDzB9aruvC0qArs3sZP-HrnM2butuymuFRokVyx5VXugSkp_7iLGVdPAcrMCin-uPaeoSAUv-ifk_T2FAZ7ES3BIZSyHvBt2MkpsbCBF6jOAMUqFGQU70oGrOFaXI72_vxcFJojra4eD-WOmUvZX1Y_wjkH1bUdESnqlumOsvjaNI2NsGvpKuVhh-9foMSQG53RE0Sxe7GtWuZacT3_w4W0sJxH2z6iyjIHWd2Iada104Oy2nFTfoy0C16NDhEImAWk98uPwlOV_fXFj9NNhz3dBb8UDi1q4kWP27NRbRUuo8UOR11ldmT8-dWDZZNKNEMETY2MjkCaG7C0JJYnJcH3_KCGab5kS2LUUiqe6JBK6lVmz1Q.CVrnBmHOpPt9xcUiZgKyQA.ac0de0e49979646568404b020e3ec0bc912cbab9c53428f8617907cbe5897410"
    ["accept"] => string(1) "1"
    ["website_code"] => string(0) ""
    ["reg_key"] => string(16) "6Jms6g8pkVppodHK"
    ["90ab8c858c919cfeb276827c3940fe95b2e30127"] => string(16) "America/New_York"
    ["_xfResponseType"] => string(4) "json"
    ["_xfWithData"] => string(1) "1"
    ["_xfRequestUri"] => string(51) "/register/?email_verification_code=q-Kc6HCXhlaCppcO"
  }
}
 
Upvote 0 Downvote
^ By that error; It sounds like XenForo is allowing duplicate usernames during registration, but the uniqueness check only happens when MySQL enforces it at the database level. This suggests that: The MySQL unique constraint is still working, but XenForo's logic isn't catching duplicates before trying to insert them.

However when i tried to reproduce with that same username and others I couldn't. So idk HOW that error came to be!
 
Last edited:
Upvote 0 Downvote
Sim said:
Also, do you have $config['fullUnicode'] = true; set in your config.php ?
Click to expand...
Well, you can do this at least.

Emoji support | Manual | XenForo

End-user documentation for XenForo
xenforo.com xenforo.com

This will at least try to verify the database is in the correct collation.

But failing that it will be a case of manually changing all tables to the correct character set and collation.
 
Upvote 0 Downvote
Chris D said:
Well, you can do this at least.

Emoji support | Manual | XenForo

End-user documentation for XenForo
xenforo.com xenforo.com

This will at least try to verify the database is in the correct collation.

But failing that it will be a case of manually changing all tables to the correct character set and collation.
Click to expand...
On my imported database old emoji show up as "?"Is it too late to do anything or adding that to config and running php cmd.php xf:convert-utf8mb4 would do something for me?
 
Upvote 0 Downvote
You must log in or register to reply here.
Back
Top Bottom