Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

MahmoudAdelAli's avatar

utf8mb4 Collection Error when excute query

hello , after i deploy the app to heroku and run migrations when i login in i got the following error

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8mb3'

select * from `users` where `username` = developer limit 1

and the part of error

  $request->merge([

            $user_id => $request->login_id

        ]);


        if (Auth::attempt($request->only($user_id, 'password'), $request->filled('remember'))) {

            return redirect()->route('home')->with([

                'message' => 'تم تسجيل الدخول بنجاح',

                'alert-type' => 'success'

            ]);

        } else {

            return redirect()->route('login')->with([

                'message' => 'خطأ اثناء تسجيل الدخول',

                'alert-type' => 'danger'

            ]);

        }

    }

i don't know what's problem here my database config

 'connections' => [

        'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8',
            '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'),
            ]) : [],
        ],

        'pgsql' => [
            'driver' => 'pgsql',
            'host' => $DATABASE_URL["host"],
            'port' => $DATABASE_URL["port"],
            'database' => ltrim($DATABASE_URL["path"], "/"),
            'username' => $DATABASE_URL["user"],
            'password' => $DATABASE_URL["pass"],
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'require',
        ],

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            // 'encrypt' => env('DB_ENCRYPT', 'yes'),
            // 'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
        ],

    ],

and my default

    'default' => env('DB_CONNECTION', 'pgsql'),
0 likes
3 replies
RayC's avatar
RayC
Best Answer
Level 10

Your DB Character set is different on your local machine vs your server:

CHARACTER SET 'utf8mb3'

Your collation in the config is invalid (change to below):

// Current
'collation' => 'utf8mb4_unicode_ci',

// Try changing to
'collation' => 'utf8mb3_unicode_ci',
1 like
MahmoudAdelAli's avatar

@RayC that's the correct answer but i'll add this comment

add 
 'collation' => 'utf8_unicode_ci',
RayC's avatar

@MahmoudAdelAli To add even further for future reference and explanation:

From the MySQL 8 manual: MySQL supports multiple Unicode character sets:

  • utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character.

  • utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character. This character set is deprecated in MySQL 8.0, and you should use utfmb4 instead.

  • utf8: An alias for utf8mb3. In MySQL 8.0, this alias is deprecated; use utf8mb4 instead. utf8 is expected in a future release to become an alias for utf8mb4.

Please or to participate in this conversation.