Level 39
Why do you think it's a db problem?
First try a simple direct sql to insert 'é' and see the result.
1 like
Jun 21, 2022
4
Level 1
Diacritics treated as the same character in database
Hi all,
We are building a website using laravel, vue, vuex and vuetify.
In our signup page, we have a ‘skills’ section that allows you to enter multiple skills as ‘chips’ in a combobox.
When the form is saved, each of these chips is saved in a table if they do not already exist in the table.
If I save some variation of ‘e’ in the table (let’s say ‘e’), then enter any other kind of ‘e’ (let’s say ‘é’) into the form and save it, ‘e’ will be saved in the database and if the form is reopened, the ‘e’ will be in the combobox, despite the user having entered ‘é’ originally.
I have attempted to fix the problem by ensuring that all of the character sets are utf8mb4 in config/database.php:
mysql: 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',
'pgsql’: 'charset' => 'utf8mb4',
'sqlsrv’: 'charset' => 'utf8mb4',
and then running ‘php artisan migrate:fresh --seed’ to rebuild the database, but to no avail.
I did not change the mysql configs, but the other two were originally utf8
Details: Laravel Framework 9.11.0 Running in a linux docker container on my windows 10 machine
Thanks in advance for any help
Level 1
Here is the result after trying to insert 'é' :
When there is already an 'e' in the table:
Query 1: Duplicate entry 'é' for key 'skills.skills_title_unique'
After deleting the 'e' in the table:
Query 1 Ok: 1 row affected
The table now contains the 'é', but no 'e'
Now if I attempt to insert an 'e' into the table:
Query 1: Duplicate entry 'e' for key 'skills.skills_title_unique'
It appeared most likely to be a database problem after googling similar issues. Most solutions were about ensuring that the charset was utf8mb4. I'm still a bit stuck, though.
Level 39
Ok, you have to change the collation
try
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Found here https://stackoverflow.com/questions/29570831/mysql-distinct-and-accents
I use postgresql that is ok with accent and case sensitive as default settings.
1 like
Level 1
@sr57 Thank you very much, that appears to have solved it.
And thank you as well for the link, the explanation was useful.
Please or to participate in this conversation.