Notifications illegal mix of collations error
Hi,
I tried to set up notifications for my Filament application. I followed the documentation for migrating the notifications table and created it. Afterward, I added
->databaseNotifications() to my AdminPanelProvider.
However, when I try to access the panel, I encounter a collation error. I attempted to change the table collation between utf8mb3_general_ci and utf8mb3_unicode_ci, but it didn't make any difference. I managed to fix the error by changing the where clause in this line to whereJsonContains. However, since I can't really modify a file in the vendor directory, I would like to know if you have another solution.
Thanks in advance.
https://github.com/filamentphp/filament/blob/3.x/packages/notifications/src/Livewire/DatabaseNotifications.php#L88GitHub
filament/packages/notifications/src/Livewire/DatabaseNotifications....
A collection of beautiful full-stack components for Laravel. The perfect starting point for your next app. Using Livewire, Alpine.js and Tailwind CSS. - filamentphp/filament
Solution:Jump to solution
Hi,
I managed to make it work by deleting my db completely and recreating it in utf8mb4_unicode_ci so it's definitely an encoding error... I'll just have to find out how to convert my current db to utf8mb4_unicode_ci I think I did it wrongly last night. Thank you very much for the help...
32 Replies
"illegal mix" its look like your columns and the table dont share the same encoding
if can check that and make sure they all the same
looks like they are the same
dose all of your other tables using the same collection?
yeah
🤔
I just checked everything I have utf8mb3_unicode_ci everywhere
after sending the notification, dose the data stored in db correctly ?
not missing character or something!?
seeing similar issues with dif character set but all resolved when switching to utf-8*unicode
I am sure you have a reasons for using
utf8mb3_unicode_ci !it looks like yes but it doesn't work with an empty table either
I'm not sure... I thought I was in utf8_unicode_ci or utf8mb4_unicode_ci and I discovered this evening that it wasn't... Could trying to change everything to one of the other two solve my problem?
laravel default is
utf8mb4_unicode_ci
I used notifications before, didnt have any issues with characters
so I am not 100 sure but if you can, change it 🙂I just tried and it didn't change anything
what the change you made in
whereJsonContains?I replaced the
where of this line : https://github.com/filamentphp/filament/blob/3.x/packages/notifications/src/Livewire/DatabaseNotifications.php#L88
by whereJsonContains. I saw taht the where was on the json in the data column and i look in the laravel docs how to do a where on a json field. They talk about using whereJsonContains so i tried and it workedGitHub
filament/packages/notifications/src/Livewire/DatabaseNotifications....
A collection of beautiful full-stack components for Laravel. The perfect starting point for your next app. Using Livewire, Alpine.js and Tailwind CSS. - filamentphp/filament
Laravel - The PHP Framework For Web Artisans
Laravel is a PHP web application framework with expressive, elegant syntax. We’ve already laid the foundation — freeing you to create without sweating the small things.
are you using matia db?
or normall mysql?
mariadb
not supported 🙂
I think this can be a bug
oh thx, I got so used to consider that mariadb and mysql were almost the same that I didn't ask myself the question
okay so from what i understand it's related to my mariadb version
https://github.com/filamentphp/filament/pull/8305#issuecomment-1711419170
I'm not sure that these two issues are linked. Here it says that updating to mariadb 10.6 resolved this bug but I'm on 10.11 so I also have this patch but still have an issue
can you check if you have the json ext ienabeld in maria
of course i just have to figure out how to do that
I have the php json extension but the mariadb json extension I don't know
I am not sre how to find it too, I have phpMyadmin that show all installed ext
where it is in phpmyadmin ?
widely cant find it 🙂 also dont have mariadb here only at work
but if you see this when creating a new column it's installed
I think it's enabled since I see it there
Well i'm gonna sleep.
In any case, even if we haven't yet found the solution, I really appreciate the time and help you gave me tonight, so thank you
you're welcome 🙂
Solution
Hi,
I managed to make it work by deleting my db completely and recreating it in utf8mb4_unicode_ci so it's definitely an encoding error... I'll just have to find out how to convert my current db to utf8mb4_unicode_ci I think I did it wrongly last night. Thank you very much for the help
glad you find the root of the issue 🙂
Ya it's super-complex to update "everything" to a new encoding/collation. You have to do not only the tables, but also inside each table must do every field that accepts text in some way .... and in some cases depending on charsets the data inside the fields doesn't actually get converted (could be because it's not detected as needing to be changed, or other reasons), and in those cases sometimes it's best to convert those text-related fields to their blob (binary) equivalent, since blob (kinda) "preserves" it in another form, and then change the collation and then convert it back to its text type again so that it un-blob's it into the right collation.
It could be that it's just your JSON fields that still contained the wrong kind of data, and so maybe you could get away with only doing special treatment of those first in case that's "enough".
Hopefully you can recreate the problem locally using a backup from the live server, so you'll be able to safely simulate the conversion and be certain that it's clean before publishing the migration to production.
Yeah I'll try to tweak something to make it work 🙂
thanks a lot to both of you