Laravel 11 connection with MariaDB fails with "Unknown collation: 'utf8mb4_uca1400_ai_ci' "

[MircoBabin]

Laravel 11 connection with MariaDB fails with "Unknown collation: 'utf8mb4_uca1400_ai_ci' "

[MircoBabin]

Additional information the SHOW COLLATION; statement on the MariaDB database gives:

Collation	Charset	Id	Default	Compiled	Sortlen
big5_chinese_ci	big5	1	Yes	Yes	1
big5_bin	big5	84		Yes	1
big5_chinese_nopad_ci	big5	1025		Yes	1
big5_nopad_bin	big5	1108		Yes	1
dec8_swedish_ci	dec8	3	Yes	Yes	1
dec8_bin	dec8	69		Yes	1
dec8_swedish_nopad_ci	dec8	1027		Yes	1
dec8_nopad_bin	dec8	1093		Yes	1
cp850_general_ci	cp850	4	Yes	Yes	1
cp850_bin	cp850	80		Yes	1
cp850_general_nopad_ci	cp850	1028		Yes	1
cp850_nopad_bin	cp850	1104		Yes	1
hp8_english_ci	hp8	6	Yes	Yes	1
hp8_bin	hp8	72		Yes	1
hp8_english_nopad_ci	hp8	1030		Yes	1
hp8_nopad_bin	hp8	1096		Yes	1
koi8r_general_ci	koi8r	7	Yes	Yes	1
koi8r_bin	koi8r	74		Yes	1
koi8r_general_nopad_ci	koi8r	1031		Yes	1
koi8r_nopad_bin	koi8r	1098		Yes	1
latin1_german1_ci	latin1	5		Yes	1
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15		Yes	1
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48		Yes	1
latin1_general_cs	latin1	49		Yes	1
latin1_spanish_ci	latin1	94		Yes	1
latin1_swedish_nopad_ci	latin1	1032		Yes	1
latin1_nopad_bin	latin1	1071		Yes	1
latin2_czech_cs	latin2	2		Yes	4
latin2_general_ci	latin2	9	Yes	Yes	1
latin2_hungarian_ci	latin2	21		Yes	1
latin2_croatian_ci	latin2	27		Yes	1
latin2_bin	latin2	77		Yes	1
latin2_general_nopad_ci	latin2	1033		Yes	1
latin2_nopad_bin	latin2	1101		Yes	1
swe7_swedish_ci	swe7	10	Yes	Yes	1
swe7_bin	swe7	82		Yes	1
swe7_swedish_nopad_ci	swe7	1034		Yes	1
swe7_nopad_bin	swe7	1106		Yes	1
ascii_general_ci	ascii	11	Yes	Yes	1
ascii_bin	ascii	65		Yes	1
ascii_general_nopad_ci	ascii	1035		Yes	1
ascii_nopad_bin	ascii	1089		Yes	1
ujis_japanese_ci	ujis	12	Yes	Yes	1
ujis_bin	ujis	91		Yes	1
ujis_japanese_nopad_ci	ujis	1036		Yes	1
ujis_nopad_bin	ujis	1115		Yes	1
sjis_japanese_ci	sjis	13	Yes	Yes	1
sjis_bin	sjis	88		Yes	1
sjis_japanese_nopad_ci	sjis	1037		Yes	1
sjis_nopad_bin	sjis	1112		Yes	1
hebrew_general_ci	hebrew	16	Yes	Yes	1
hebrew_bin	hebrew	71		Yes	1
hebrew_general_nopad_ci	hebrew	1040		Yes	1
hebrew_nopad_bin	hebrew	1095		Yes	1
tis620_thai_ci	tis620	18	Yes	Yes	4
tis620_bin	tis620	89		Yes	1
tis620_thai_nopad_ci	tis620	1042		Yes	4
tis620_nopad_bin	tis620	1113		Yes	1
euckr_korean_ci	euckr	19	Yes	Yes	1
euckr_bin	euckr	85		Yes	1
euckr_korean_nopad_ci	euckr	1043		Yes	1
euckr_nopad_bin	euckr	1109		Yes	1
koi8u_general_ci	koi8u	22	Yes	Yes	1
koi8u_bin	koi8u	75		Yes	1
koi8u_general_nopad_ci	koi8u	1046		Yes	1
koi8u_nopad_bin	koi8u	1099		Yes	1
gb2312_chinese_ci	gb2312	24	Yes	Yes	1
gb2312_bin	gb2312	86		Yes	1
gb2312_chinese_nopad_ci	gb2312	1048		Yes	1
gb2312_nopad_bin	gb2312	1110		Yes	1
greek_general_ci	greek	25	Yes	Yes	1
greek_bin	greek	70		Yes	1
greek_general_nopad_ci	greek	1049		Yes	1
greek_nopad_bin	greek	1094		Yes	1
cp1250_general_ci	cp1250	26	Yes	Yes	1
cp1250_czech_cs	cp1250	34		Yes	2
cp1250_croatian_ci	cp1250	44		Yes	1
cp1250_bin	cp1250	66		Yes	1
cp1250_polish_ci	cp1250	99		Yes	1
cp1250_general_nopad_ci	cp1250	1050		Yes	1
cp1250_nopad_bin	cp1250	1090		Yes	1
gbk_chinese_ci	gbk	28	Yes	Yes	1
gbk_bin	gbk	87		Yes	1
gbk_chinese_nopad_ci	gbk	1052		Yes	1
gbk_nopad_bin	gbk	1111		Yes	1
latin5_turkish_ci	latin5	30	Yes	Yes	1
latin5_bin	latin5	78		Yes	1
latin5_turkish_nopad_ci	latin5	1054		Yes	1
latin5_nopad_bin	latin5	1102		Yes	1
armscii8_general_ci	armscii8	32	Yes	Yes	1
armscii8_bin	armscii8	64		Yes	1
armscii8_general_nopad_ci	armscii8	1056		Yes	1
armscii8_nopad_bin	armscii8	1088		Yes	1
utf8_general_ci	utf8	33	Yes	Yes	1
utf8_bin	utf8	83		Yes	1
utf8_unicode_ci	utf8	192		Yes	8
utf8_icelandic_ci	utf8	193		Yes	8
utf8_latvian_ci	utf8	194		Yes	8
utf8_romanian_ci	utf8	195		Yes	8
utf8_slovenian_ci	utf8	196		Yes	8
utf8_polish_ci	utf8	197		Yes	8
utf8_estonian_ci	utf8	198		Yes	8
utf8_spanish_ci	utf8	199		Yes	8
utf8_swedish_ci	utf8	200		Yes	8
utf8_turkish_ci	utf8	201		Yes	8
utf8_czech_ci	utf8	202		Yes	8
utf8_danish_ci	utf8	203		Yes	8
utf8_lithuanian_ci	utf8	204		Yes	8
utf8_slovak_ci	utf8	205		Yes	8
utf8_spanish2_ci	utf8	206		Yes	8
utf8_roman_ci	utf8	207		Yes	8
utf8_persian_ci	utf8	208		Yes	8
utf8_esperanto_ci	utf8	209		Yes	8
utf8_hungarian_ci	utf8	210		Yes	8
utf8_sinhala_ci	utf8	211		Yes	8
utf8_german2_ci	utf8	212		Yes	8
utf8_croatian_mysql561_ci	utf8	213		Yes	8
utf8_unicode_520_ci	utf8	214		Yes	8
utf8_vietnamese_ci	utf8	215		Yes	8
utf8_general_mysql500_ci	utf8	223		Yes	1
utf8_croatian_ci	utf8	576		Yes	8
utf8_myanmar_ci	utf8	577		Yes	8
utf8_thai_520_w2	utf8	578		Yes	4
utf8_general_nopad_ci	utf8	1057		Yes	1
utf8_nopad_bin	utf8	1107		Yes	1
utf8_unicode_nopad_ci	utf8	1216		Yes	8
utf8_unicode_520_nopad_ci	utf8	1238		Yes	8
ucs2_general_ci	ucs2	35	Yes	Yes	1
ucs2_bin	ucs2	90		Yes	1
ucs2_unicode_ci	ucs2	128		Yes	8
ucs2_icelandic_ci	ucs2	129		Yes	8
ucs2_latvian_ci	ucs2	130		Yes	8
ucs2_romanian_ci	ucs2	131		Yes	8
ucs2_slovenian_ci	ucs2	132		Yes	8
ucs2_polish_ci	ucs2	133		Yes	8
ucs2_estonian_ci	ucs2	134		Yes	8
ucs2_spanish_ci	ucs2	135		Yes	8
ucs2_swedish_ci	ucs2	136		Yes	8
ucs2_turkish_ci	ucs2	137		Yes	8
ucs2_czech_ci	ucs2	138		Yes	8
ucs2_danish_ci	ucs2	139		Yes	8
ucs2_lithuanian_ci	ucs2	140		Yes	8
ucs2_slovak_ci	ucs2	141		Yes	8
ucs2_spanish2_ci	ucs2	142		Yes	8
ucs2_roman_ci	ucs2	143		Yes	8
ucs2_persian_ci	ucs2	144		Yes	8
ucs2_esperanto_ci	ucs2	145		Yes	8
ucs2_hungarian_ci	ucs2	146		Yes	8
ucs2_sinhala_ci	ucs2	147		Yes	8
ucs2_german2_ci	ucs2	148		Yes	8
ucs2_croatian_mysql561_ci	ucs2	149		Yes	8
ucs2_unicode_520_ci	ucs2	150		Yes	8
ucs2_vietnamese_ci	ucs2	151		Yes	8
ucs2_general_mysql500_ci	ucs2	159		Yes	1
ucs2_croatian_ci	ucs2	640		Yes	8
ucs2_myanmar_ci	ucs2	641		Yes	8
ucs2_thai_520_w2	ucs2	642		Yes	4
ucs2_general_nopad_ci	ucs2	1059		Yes	1
ucs2_nopad_bin	ucs2	1114		Yes	1
ucs2_unicode_nopad_ci	ucs2	1152		Yes	8
ucs2_unicode_520_nopad_ci	ucs2	1174		Yes	8
cp866_general_ci	cp866	36	Yes	Yes	1
cp866_bin	cp866	68		Yes	1
cp866_general_nopad_ci	cp866	1060		Yes	1
cp866_nopad_bin	cp866	1092		Yes	1
keybcs2_general_ci	keybcs2	37	Yes	Yes	1
keybcs2_bin	keybcs2	73		Yes	1
keybcs2_general_nopad_ci	keybcs2	1061		Yes	1
keybcs2_nopad_bin	keybcs2	1097		Yes	1
macce_general_ci	macce	38	Yes	Yes	1
macce_bin	macce	43		Yes	1
macce_general_nopad_ci	macce	1062		Yes	1
macce_nopad_bin	macce	1067		Yes	1
macroman_general_ci	macroman	39	Yes	Yes	1
macroman_bin	macroman	53		Yes	1
macroman_general_nopad_ci	macroman	1063		Yes	1
macroman_nopad_bin	macroman	1077		Yes	1
cp852_general_ci	cp852	40	Yes	Yes	1
cp852_bin	cp852	81		Yes	1
cp852_general_nopad_ci	cp852	1064		Yes	1
cp852_nopad_bin	cp852	1105		Yes	1
latin7_estonian_cs	latin7	20		Yes	1
latin7_general_ci	latin7	41	Yes	Yes	1
latin7_general_cs	latin7	42		Yes	1
latin7_bin	latin7	79		Yes	1
latin7_general_nopad_ci	latin7	1065		Yes	1
latin7_nopad_bin	latin7	1103		Yes	1
utf8mb4_general_ci	utf8mb4	45	Yes	Yes	1
utf8mb4_bin	utf8mb4	46		Yes	1
utf8mb4_unicode_ci	utf8mb4	224		Yes	8
utf8mb4_icelandic_ci	utf8mb4	225		Yes	8
utf8mb4_latvian_ci	utf8mb4	226		Yes	8
utf8mb4_romanian_ci	utf8mb4	227		Yes	8
utf8mb4_slovenian_ci	utf8mb4	228		Yes	8
utf8mb4_polish_ci	utf8mb4	229		Yes	8
utf8mb4_estonian_ci	utf8mb4	230		Yes	8
utf8mb4_spanish_ci	utf8mb4	231		Yes	8
utf8mb4_swedish_ci	utf8mb4	232		Yes	8
utf8mb4_turkish_ci	utf8mb4	233		Yes	8
utf8mb4_czech_ci	utf8mb4	234		Yes	8
utf8mb4_danish_ci	utf8mb4	235		Yes	8
utf8mb4_lithuanian_ci	utf8mb4	236		Yes	8
utf8mb4_slovak_ci	utf8mb4	237		Yes	8
utf8mb4_spanish2_ci	utf8mb4	238		Yes	8
utf8mb4_roman_ci	utf8mb4	239		Yes	8
utf8mb4_persian_ci	utf8mb4	240		Yes	8
utf8mb4_esperanto_ci	utf8mb4	241		Yes	8
utf8mb4_hungarian_ci	utf8mb4	242		Yes	8
utf8mb4_sinhala_ci	utf8mb4	243		Yes	8
utf8mb4_german2_ci	utf8mb4	244		Yes	8
utf8mb4_croatian_mysql561_ci	utf8mb4	245		Yes	8
utf8mb4_unicode_520_ci	utf8mb4	246		Yes	8
utf8mb4_vietnamese_ci	utf8mb4	247		Yes	8
utf8mb4_croatian_ci	utf8mb4	608		Yes	8
utf8mb4_myanmar_ci	utf8mb4	609		Yes	8
utf8mb4_thai_520_w2	utf8mb4	610		Yes	4
utf8mb4_general_nopad_ci	utf8mb4	1069		Yes	1
utf8mb4_nopad_bin	utf8mb4	1070		Yes	1
utf8mb4_unicode_nopad_ci	utf8mb4	1248		Yes	8
utf8mb4_unicode_520_nopad_ci	utf8mb4	1270		Yes	8
cp1251_bulgarian_ci	cp1251	14		Yes	1
cp1251_ukrainian_ci	cp1251	23		Yes	1
cp1251_bin	cp1251	50		Yes	1
cp1251_general_ci	cp1251	51	Yes	Yes	1
cp1251_general_cs	cp1251	52		Yes	1
cp1251_nopad_bin	cp1251	1074		Yes	1
cp1251_general_nopad_ci	cp1251	1075		Yes	1
utf16_general_ci	utf16	54	Yes	Yes	1
utf16_bin	utf16	55		Yes	1
utf16_unicode_ci	utf16	101		Yes	8
utf16_icelandic_ci	utf16	102		Yes	8
utf16_latvian_ci	utf16	103		Yes	8
utf16_romanian_ci	utf16	104		Yes	8
utf16_slovenian_ci	utf16	105		Yes	8
utf16_polish_ci	utf16	106		Yes	8
utf16_estonian_ci	utf16	107		Yes	8
utf16_spanish_ci	utf16	108		Yes	8
utf16_swedish_ci	utf16	109		Yes	8
utf16_turkish_ci	utf16	110		Yes	8
utf16_czech_ci	utf16	111		Yes	8
utf16_danish_ci	utf16	112		Yes	8
utf16_lithuanian_ci	utf16	113		Yes	8
utf16_slovak_ci	utf16	114		Yes	8
utf16_spanish2_ci	utf16	115		Yes	8
utf16_roman_ci	utf16	116		Yes	8
utf16_persian_ci	utf16	117		Yes	8
utf16_esperanto_ci	utf16	118		Yes	8
utf16_hungarian_ci	utf16	119		Yes	8
utf16_sinhala_ci	utf16	120		Yes	8
utf16_german2_ci	utf16	121		Yes	8
utf16_croatian_mysql561_ci	utf16	122		Yes	8
utf16_unicode_520_ci	utf16	123		Yes	8
utf16_vietnamese_ci	utf16	124		Yes	8
utf16_croatian_ci	utf16	672		Yes	8
utf16_myanmar_ci	utf16	673		Yes	8
utf16_thai_520_w2	utf16	674		Yes	4
utf16_general_nopad_ci	utf16	1078		Yes	1
utf16_nopad_bin	utf16	1079		Yes	1
utf16_unicode_nopad_ci	utf16	1125		Yes	8
utf16_unicode_520_nopad_ci	utf16	1147		Yes	8
utf16le_general_ci	utf16le	56	Yes	Yes	1
utf16le_bin	utf16le	62		Yes	1
utf16le_general_nopad_ci	utf16le	1080		Yes	1
utf16le_nopad_bin	utf16le	1086		Yes	1
cp1256_general_ci	cp1256	57	Yes	Yes	1
cp1256_bin	cp1256	67		Yes	1
cp1256_general_nopad_ci	cp1256	1081		Yes	1
cp1256_nopad_bin	cp1256	1091		Yes	1
cp1257_lithuanian_ci	cp1257	29		Yes	1
cp1257_bin	cp1257	58		Yes	1
cp1257_general_ci	cp1257	59	Yes	Yes	1
cp1257_nopad_bin	cp1257	1082		Yes	1
cp1257_general_nopad_ci	cp1257	1083		Yes	1
utf32_general_ci	utf32	60	Yes	Yes	1
utf32_bin	utf32	61		Yes	1
utf32_unicode_ci	utf32	160		Yes	8
utf32_icelandic_ci	utf32	161		Yes	8
utf32_latvian_ci	utf32	162		Yes	8
utf32_romanian_ci	utf32	163		Yes	8
utf32_slovenian_ci	utf32	164		Yes	8
utf32_polish_ci	utf32	165		Yes	8
utf32_estonian_ci	utf32	166		Yes	8
utf32_spanish_ci	utf32	167		Yes	8
utf32_swedish_ci	utf32	168		Yes	8
utf32_turkish_ci	utf32	169		Yes	8
utf32_czech_ci	utf32	170		Yes	8
utf32_danish_ci	utf32	171		Yes	8
utf32_lithuanian_ci	utf32	172		Yes	8
utf32_slovak_ci	utf32	173		Yes	8
utf32_spanish2_ci	utf32	174		Yes	8
utf32_roman_ci	utf32	175		Yes	8
utf32_persian_ci	utf32	176		Yes	8
utf32_esperanto_ci	utf32	177		Yes	8
utf32_hungarian_ci	utf32	178		Yes	8
utf32_sinhala_ci	utf32	179		Yes	8
utf32_german2_ci	utf32	180		Yes	8
utf32_croatian_mysql561_ci	utf32	181		Yes	8
utf32_unicode_520_ci	utf32	182		Yes	8
utf32_vietnamese_ci	utf32	183		Yes	8
utf32_croatian_ci	utf32	736		Yes	8
utf32_myanmar_ci	utf32	737		Yes	8
utf32_thai_520_w2	utf32	738		Yes	4
utf32_general_nopad_ci	utf32	1084		Yes	1
utf32_nopad_bin	utf32	1085		Yes	1
utf32_unicode_nopad_ci	utf32	1184		Yes	8
utf32_unicode_520_nopad_ci	utf32	1206		Yes	8
binary	binary	63	Yes	Yes	1
geostd8_general_ci	geostd8	92	Yes	Yes	1
geostd8_bin	geostd8	93		Yes	1
geostd8_general_nopad_ci	geostd8	1116		Yes	1
geostd8_nopad_bin	geostd8	1117		Yes	1
cp932_japanese_ci	cp932	95	Yes	Yes	1
cp932_bin	cp932	96		Yes	1
cp932_japanese_nopad_ci	cp932	1119		Yes	1
cp932_nopad_bin	cp932	1120		Yes	1
eucjpms_japanese_ci	eucjpms	97	Yes	Yes	1
eucjpms_bin	eucjpms	98		Yes	1
eucjpms_japanese_nopad_ci	eucjpms	1121		Yes	1
eucjpms_nopad_bin	eucjpms	1122		Yes	1

[MircoBabin]

I found https://dba.stackexchange.com/questions/248904/mysql-to-mariadb-unknown-collation-utf8mb4-0900-ai-ci and added DB_COLLATION=utf8mb4_unicode_520_ci to the .env.

DB_CONNECTION=mariadb
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=...
DB_USERNAME=...
DB_PASSWORD=...
DB_COLLATION=utf8mb4_unicode_520_ci

Then the error is resolved. And I get the expected SQLSTATE[42S02]: Base table or view not found: 1146 Table 'sessions' doesn't exist because the migrate has not been executed.

Maybe the possible MariaDB collation problems should be mentioned somewhere in the documentation https://laravel.com/docs/11.x/database?

[Jubeki]

This was already fixed in the skeleton and in the Framework and does not happen in a fresh Laravel Application.

If you use Windows with the command composer create-project laravel/laravel:^11.0 example-app then you should remove the :^11.0 which was also fixed in the docs. Windows does not support the ^ operator in the console and will always install the skeleton on the first version. Without the version constraint it will use the latest laravel skeleton version.

[driesvints]

Thanks @Jubeki

[MircoBabin]

Thanks @Jubeki

Indeed I'm on Windows. And indeed this is all caused because of the ^11.0 mistake I made in #50959. I have manually updated the /config directory and composer.json from the latest skeleton version in https://github.com/laravel/laravel The DB_COLLATION= isn't neccesary anymore, everything works as expected now.

For future readers: the skeleton changed config/database.php for mysql and mariadb to:

            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),