I believe you are using MySQL 5.7?

@imariusalin what DB version are you using?

@imariusalin what DB version are you using?

mariadb Ver 15.1 Distrib 10.11.6-MariaDB, for Linux (x86_64) using readline 5.1 and tested on mysql 8.0 also

@imariusalin seems highly unlikely that this is failing for the mentioned engine versions since our test suites test these versions. You're 100% sure you're not running MySQL 5.7?

Yes, I'm sure. Plus, on my Windows machine, I'm using MariaDB 10.4.32. To replicate what I did, I installed it with laravel new project, followed all the steps, and, by default, it chose SQLite for some reason, even though I chose MySQL. Then, I went into the .env file, set up my database details, and tried to run php artisan migrate.

Wouldn't it be better to use DB_CONNECTION=mariadb?

True, the solution worked for MariaDB, but I'm facing the same error on my Windows machine. Another complication is that, by default, not everyone knows the exact version of the software they're using, which leads to this error. Moreover, not many people know how to fix it. If you search online, you'll find that many people have encountered this issue.

Just as a suggestion, I think it is good to have a default DB_COLLATION that will work on most of the systems and not cause an issue, and for those who want to use another DB_COLLATION i am pretty sure they already know how to handle this.

@imariusalin I don't understand. You're using the MYSQL connection for MariaDB while there is a MariaDB connection? Does the issue also happen for you if the MariaDB connection? It seems not from your posts above but want to double check.

@imariusalin I don't understand. You're using the MYSQL connection for MariaDB while there is a MariaDB connection? Does the issue also happen for you if the MariaDB connection? It seems not from your posts above but want to double check.

It happend on Windows using mariadb driver from xampp. On linux after i have changed the driver type it worked.

New Project Setup

After the change!

In the first image it says unknown database "teste" it seems like the database is not correctly created?

As for your original report shows, that you tried to use a MySQL 8 Collation on either a MySQL 5.7 or MariaDB database. (Like @driesvints and @crynobone said)

MySQL 8: utf8mb4_0900_ai_ci (not supported by MariaDB and MySQL 5.7)
MariaDB: utf8mb4_uca1400_ai_ci (not supported by MySQL 8 and MySQL 5.7)

Older and replaced with the above: utf8mb4_unicode_ci, that was also the reason why it was changed in Laravel 11.

In the first image it says unknown database "teste" it seems like the database is not correctly created?

As for your original report shows, that you tried to use a MySQL 8 Collation on either a MySQL 5.7 or MariaDB database. (Like @driesvints and @crynobone said)

MySQL 8: utf8mb4_0900_ai_ci (not supported by MariaDB and MySQL 5.7) MariaDB: utf8mb4_uca1400_ai_ci (not supported by MySQL 8 and MySQL 5.7)

Older and replaced with the above: utf8mb4_unicode_ci, that was also the reason why it was changed in Laravel 11.

The issue happens if you don't know what type of DB you are using. In the screenshots, I'm using Windows 11 with XAMPP and MariaDB 10.x with the correct driver. On my Linux machine, I had MariaDB Ver 15.1 Distrib 10.11.6-MariaDB, but the driver was set wrong to MySQL and gave the error. Some MariaDB/mysql versions still don't have this DB_COLLATION, and it gives the issue on the first install.

Maybe instead of changing the default collation, it would be better to show a reasonable error, like:

It seems you are using MariaDB and also try to use a MySQL 8.0 only collation, we recommend changing the driver to mariadb or change the collation at your own risk

Or show what Database Version you are using, so that it is easier debugable.

There is some error in mariadb default config: 'collation' => env('DB_COLLATION', 'utf8mb4_uca1400_ai_ci'),

New default laravel config while setting mariadb uses by default DB_COLLATION: utf8mb4_uca1400_ai_ci
while there is no something like that in MariaDB:
https://mariadb.com/kb/en/supported-character-sets-and-collations/

There is only "uca1400_ai_ci"

New default laravel config:

    'mariadb' => [
        'driver' => 'mariadb',
        '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_uca1400_ai_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'),
        ]) : [],
    ], 

also It's only present from 23.07 Enterprise. I suggest go for utf8mb4_unicode_ci
newest xampp: MariaDB 10.4.32

Hi. My server setup in Laravel Forge use MariaDB 10.11

By default creating a table will use
Type : Innodb
Encoding : utf8mb4
Collation : utf8mb4_unicode_ci

The screenshot below is taken to show all the option available. Since this is from Forge, it should be a good reference of a live setup from Laravel ecosystem. I did have to set DB_COLLATION=utf8mb4_unicode_ci

It seems in the future MariaDB wants to use the short name uca1400_ai_ci for multiple collations. The full collation name with the charset prefix is still available:

You can see this on the following site:
https://jira.mariadb.org/browse/MDEV-27009

in the sections INFORMATION_SCHEMA.COLLATIONS changes and INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY changes

The COLLATION_NAME column will display the short name, without character set prefix.

This is only the display value (probably can also be used), but the long name with the charset prefix is still available. (The naming change probably happened after the merge of laravel/framework#48455 because in the discussion there was already the name in use.)

Maybe @grooverdan can say something, as he works for MariaDB and probably knows more.

@Jubeki

There was wrong assumption for MariaDB version:
laravel/framework#48455 (comment)

uca1400_ai_ci is based on Unicode 14 and available in MariaDB since 10.10.1 (Laravel 11.x will support
MariaDB 10.11+ so we're covered https://laravel.com/docs/master/database).

So wrong version assumtion (10.11+) or we have wrong info in docs:
Minimum MariaDB 10.3+ https://laravel.com/docs/11.x/database#introduction
but uca1400_ai_ci is from 10.10.1
newest xampp version MariaDB 10.4.32 (for windows users xampp is very common It's why I'm relating to this)

It seems the supported mariadb versions were reverted in the docs with laravel/docs#9113

I also did the PR with 10.11+ in mind.

What is the actual solution here @Jubeki

@taylorotwell

XAMPP is often used with Windows and the only alternative would be Laragon (which uses MySQL 5.7 so a similar Problem).

MariaDB 10.4, 10.5 and 10.6 are still supported for up to two years (see https://endoflife.date/mariadb).

There are two options in my opinion without reverting the default collation to the older one:

1. Contribute to XAMPP / Laragon: Raise the versions to MySQL 8.0 or MariaDB 10.11, not sure if these changes will be accepted.

2. While installing a fresh application or running migrations detect the used Database Version and make approbiate collation changes (or provide needed changes), while also informing the developer about theses changes. The biggest Problem with this is, that the development environment and production environment can be different. If a Developer doesn't know which database version is used locally or simply uses a different version than in production. Changing the collation can simply be done in the .env

I would have loved to recommend Laravel Herd when it is released for Windows, but the database service is only available in the PRO version.

@Jubeki can we just revert the MariaDB collation back to what it was for Laravel 10, or use null? I don't think getting this fixed in XAMPP is realistic.

null should probably be a valid option, I will test if it works with MariaDB 10.4 (using Docker)

Thanks - let me know

@taylorotwell Switching to null is not an option, because it will result in latin1_swedish_ci which is worse than utf8mb4_unicode_ci

Laravel 10 used utf8mb4_unicode_ci, so reverting to that is an option.

It would be interesting to know, what people use in production and/or local development.

So can I update just the MariaDB configuration to utf8mb4_unicode_ci?

Updated MariaDB default.

@taylorotwell If many people are still using MySQL 5.7 (not sure) than the same problem will happen again.

Maybe it is best to revert both configurations for Laravel 11, and already announce or plan for Laravel 12, that older databases will be dropped?

Let's wait to see if we get issues with the 5.7 thing.

I see Unknown collation: 'utf8mb4_0900_ai_ci' for MySQL 5.7 with DB_CONNECTION=mysql.

If the docs states that Laravel 11 supports MySQL 5.7+ OOTB, it is better to revert it too.

Yep, XAMPP (ApacheFriends/xampp-build#17) appears to be moving slowly. and Laragon development appears to have stalled.

It seems in the future MariaDB wants to use the short name uca1400_ai_ci for multiple collations.

Technically multiple character sets, but yes, short names ensure accidental character set miss-match doesn't occur.

The full collation name with the charset prefix is still available:

I did some prototype wordpress work on pulling a modern collation from what was available here: grooverdan/wordpress-develop@86500d3#diff-101f4101613ae810fc8e54675d9437f9aed83f89dfe2bd3068b2ed2964eece03R896-R938 (that I should get back to, feel free to use).

Apart from that, not much I can add that you haven't covered already, uca1400 isn't accessible on < 10.10.1 MariaDB versions.

found another user in distress.

Thanks @grooverdan. Appreciate the feedback 👍