F
Filament•3mo ago
dyo

Error in searchable TextColumn

I have attributes of name and phone using TextColumn searchable method. the user sometimes accidentally paste the text in search input with emoticon in it, and these error appeared.. Error: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation 'like' (Connection: mysql, SQL: select * from prospek where phone LIKE %asyaaAllahAlhamdul% or name LIKE %Maa syaa Allah Alhamdulillah.. 🤲🤲% limit 10) how can I return the search result to null, or remove the emoticon if it exist in the string again?
4 Replies
Matthew
Matthew•3mo ago
Not sure...but I'd sooner sort the collation out. It will be easier, and you're using a deprecated collation, so other potential problems will appear. Emoticons are handled in search in the latest collations:
No description
mstfkhazaal
mstfkhazaal•2mo ago
i have same problem @dyo in config/database.php add search_collation 
'mysql' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'search_collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
'mysql' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'search_collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
dyo
dyoOP•4w ago
thanks a lot for your help.. I'll try it later.. I just tried it, and I got error SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8mb3'
toeknee
toeknee•4w ago
You would need to change that first then re-run your migrations looks like you have a collation mixed db which is why it's a mess.

Did you find this page helpful?