In recent MariaDB version the following will not succeed (resp. always yields an empty result set):
|
$sql = <<<'SQL' |
|
SELECT t.TABLE_NAME, |
|
t.ENGINE, |
|
t.AUTO_INCREMENT, |
|
t.TABLE_COMMENT, |
|
t.CREATE_OPTIONS, |
|
t.TABLE_COLLATION, |
|
ccsa.CHARACTER_SET_NAME |
|
FROM information_schema.TABLES t |
|
INNER JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY ccsa |
|
ON ccsa.COLLATION_NAME = t.TABLE_COLLATION |
|
SQL; |
The reason is, that CCSA.collation_name in recent MariaDB versions (tested version: 10.11.7) only contains the short collation name without prepended character set, while TABLES.table_collation contains the long table name. For example, in the case of the newly introduced utf8mb4_uca1400_ai_ci the corresponding row from COLLATION_CHARACTER_SET_APPLICABILITY is (CSV export):
"COLLATION_NAME","CHARACTER_SET_NAME","FULL_COLLATION_NAME","ID","IS_DEFAULT"
"uca1400_ai_ci","utf8mb4","utf8mb4_uca1400_ai_ci","2304",
while the TABLES.TABLE_COLLATION holds the full collation name utf8mb4_uca1400_ai_ci.
It also seems that this is a recent change in MariaDB; one Ubuntu "Jammy" system I am using runs MariaDB 10.6.16 which only has the COLLATION_NAME and CHARACTER_SET_NAME in that table, and the collation name is the full name with the character set prepended.
In recent MariaDB version the following will not succeed (resp. always yields an empty result set):
dbal/src/Schema/MySQLSchemaManager.php
Lines 463 to 474 in 7fb00fd
The reason is, that
CCSA.collation_namein recent MariaDB versions (tested version: 10.11.7) only contains the short collation name without prepended character set, whileTABLES.table_collationcontains the long table name. For example, in the case of the newly introducedutf8mb4_uca1400_ai_cithe corresponding row fromCOLLATION_CHARACTER_SET_APPLICABILITYis (CSV export):while the
TABLES.TABLE_COLLATIONholds the full collation nameutf8mb4_uca1400_ai_ci.It also seems that this is a recent change in MariaDB; one Ubuntu "Jammy" system I am using runs MariaDB 10.6.16 which only has the
COLLATION_NAMEandCHARACTER_SET_NAMEin that table, and the collation name is the full name with the character set prepended.