Laravel Error When Migrating

PHP
,
1

I tried to make a migration with composer command php aritisan make:migration create_students_table`, and then I got this migration file:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('students', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->integer('score');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('students');
    }
};

I added the name and score column from the up() function, and also I’ve made a schema in localhost myphpadmin with under the name ‘learning-laravel-11’, and then this is the configuration of my .env file

APP_NAME=Laravel
APP_ENV=local
APP_KEY=base64:IRJpq9MlMvgS9B/EhOMyEiI9Srz5C33cfMm6ngTritY=
APP_DEBUG=true
# APP_TIMEZONE=UTC
APP_URL=http://localhost

# APP_LOCALE=en
# APP_FALLBACK_LOCALE=en
# APP_FAKER_LOCALE=en_US

# APP_MAINTENANCE_DRIVER=file
# APP_MAINTENANCE_STORE=database

# BCRYPT_ROUNDS=12

LOG_CHANNEL=stack
# LOG_STACK=single
LOG_DEPRECATIONS_CHANNEL=null
LOG_LEVEL=debug

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=learning-laravel-11
DB_USERNAME=root
DB_PASSWORD=

SESSION_DRIVER=database
SESSION_LIFETIME=120
SESSION_ENCRYPT=false
SESSION_PATH=/
SESSION_DOMAIN=null

BROADCAST_CONNECTION=log
FILESYSTEM_DISK=local
QUEUE_CONNECTION=database

CACHE_STORE=database
CACHE_PREFIX=

MEMCACHED_HOST=127.0.0.1

REDIS_CLIENT=phpredis
REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_MAILER=log
MAIL_HOST=127.0.0.1
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null
MAIL_FROM_ADDRESS="hello@example.com"
MAIL_FROM_NAME="${APP_NAME}"

AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=us-east-1
AWS_BUCKET=
AWS_USE_PATH_STYLE_ENDPOINT=false

VITE_APP_NAME="${APP_NAME}"

But when I entered the php artisan migrate command, it says as following:

PS C:\xampp\htdocs\learning-laravel-11> php artisan migrate

   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci' (Connection: mysql, SQL: select table_name as `name`, (data_length + index_length) as `size`, table_comment as `comment`, engine as `engine`, table_collation as `collation` from information_schema.tables where table_schema = 'learning-laravel-11' and table_type in ('BASE TABLE', 'SYSTEM VERSIONED') order by table_name)

  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';")

Is there anything wrong with my configuration?

2

Seriously? No answers at all? :sweat_smile:

3

I’ve been working with PHP for a few years now, and very little of that means anything to me. I wonder whether you might get a bit more response in the “Server Config” or “Databases” section of the forum, I’ve always thought of this section for questions regarding actual PHP code.

4

You’d have to figure out where laravel gets the idea to use utf8mb4_0900_ai_ci as collation and configure something else. So I would start with a global find all files in your project (including vendor) for utf8mb4_0900_ai_ci and see where that leads you.

5

The error you are encountering, SQLSTATE[HY000]: General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci', indicates that the collation utf8mb4_0900_ai_ci is not recognized by your MySQL server. This usually happens when the MySQL version you are using does not support this collation.

utf8mb4_0900_ai_ci is a collation introduced in MySQL 8.0, so if you are using a version prior to MySQL 8.0 (such as MySQL 5.7 or older), you will encounter this issue.

Solutions

Solution 1: Update MySQL Version

If possible, update your MySQL server to version 8.0 or later, as it supports the utf8mb4_0900_ai_ci collation.

Solution 2: Modify Laravel Configuration

If upgrading MySQL is not an option, you can modify the default string collation used by Laravel to one supported by your current MySQL version.

  1. Open your config/database.php file.
  2. Locate the mysql connection configuration.

Modify the mysql connection configuration to set a compatible collation, such as utf8mb4_unicode_ci. Here’s how you can do it:

php

'mysql' => [
    'driver' => 'mysql',
    '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' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Apply the Changes

After making this change, run the config:cache command to clear and refresh the configuration cache:

bash

php artisan config:cache

Run Migrations

Try running your migrations again:

bash

php artisan migrate
6

Just to add.

This error mostly happens if you use mariaDB instead of mySQL. When in the past the both databases were nearly 99% compatible, the new versions use different collations. The new 0900_ai collation is the standard in mySQL, while mariaDB does not even provide it.