laravel 11 connect with mysql database

Question

I want to connect with mysql database but here show the error like this

 at vendor\laravel\framework\src\Illuminate\Database\Connection.php:813
    809▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    810▕                 );
    811▕             }
    812▕
  ➜ 813▕             throw new QueryException(
    814▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    815▕             );
    816▕         }
    817▕     }

  1   vendor\laravel\framework\src\Illuminate\Database\Connectors\MySqlConnector.php:121
      PDOException::("SQLSTATE[HY000]: General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci'")

  2   vendor\laravel\framework\src\Illuminate\Database\Connectors\MySqlConnector.php:121
      PDO::exec("SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci', SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';")

Connect with mysql database of laravel 11 but show the error

Answer 1

In case if you are on a fresh installation of Laravel 11, there are some changes that had not been mentioned on the upgrade docs. One of these changes is that now the default Session driver is the database session driver instead of the file session driver. So you will need to revert it from your .env file to use the file driver:

SESSION_DRIVER=file
SESSION_LIFETIME=120

And/Or, if you want to keep using database session driver as the default driver, and if you do not use MySQL 8.0, another change had been done in Laravel 11 and may break your application - in case if you're still using MySQL 5.7 - and I do not see that it is documented anywhere is that now the new default MySQL collation is utf8mb4_0900_ai_ci instead of utf8mb4_unicode_ci, this is according to that Laravel 11 now is using SQLITE as default database driver as mentioned. To solve this, you may need to add this env variable to your .env file:

DB_COLLATION=utf8mb4_unicode_ci

Or whatever the collation you need.

Answer 2

The most straightforward solution is to upgrade your MySQL server to version 8.0 or later, which supports the utf8mb4_0900_ai_ci collation. This will allow you to use the collation without any issues.

If upgrading MySQL isn't an option, you can change the collation used by Laravel to one that's supported by your MySQL version.

in config/database.php:

'mysql' => [
    // ...
    'collation' => 'utf8mb4_unicode_ci',
    // ...
],

After changes don't forget to run:

php artisan config:clear