What causes "COLLATION '%s' is not valid for CHARACTER SET '%s'" in apache/shardingsphere?

Pair the collation with its own charset: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci (check MySQL's information_schema.COLLATIONS for the mapping)

apache/shardingsphere · error · CollationCharsetMismatchException

1253

1253

Error message

COLLATION '%s' is not valid for CHARACTER SET '%s'

What it means

CollationCharsetMismatchException (error code 1253, MySQL error semantics) thrown by MySQLSetVariableAdminExecutor when processing SET NAMES ... COLLATE <collation>: the parsed collation's character set name must equal the already-established connection character set name. MySQL requires a collation to belong to the charset it is applied to; the proxy enforces the same rule before accepting context.withConnectionCollation(collation).

Source

Thrown at proxy/backend/dialect/mysql/src/main/java/org/apache/shardingsphere/proxy/backend/mysql/handler/admin/executor/MySQLSetVariableAdminExecutor.java:197

                && variableAssigns.subList(1, 4).stream().allMatch(each -> first.getStartIndex() == each.getStartIndex() && first.getStopIndex() == each.getStopIndex());
    }
    
    private MySQLSessionCharsetContext updateCharsetContext(final MySQLSessionCharsetContext context, final VariableAssignSegment variableAssign,
                                                            final boolean setNamesWithCollationAssignment) {
        String variableName = variableAssign.getVariable().getVariable().toLowerCase(Locale.ROOT);
        switch (variableName) {
            case CHARACTER_SET_CLIENT:
                return context.withClientCharacterSet(setNamesWithCollationAssignment
                        ? parseCharacterSet(variableAssign.getAssignValue())
                        : parseClientCharacterSet(variableAssign.getAssignValue()));
            case CHARACTER_SET_RESULTS:
                return updateResultCharacterSet(context, variableAssign.getAssignValue());
            case CHARACTER_SET_CONNECTION:
                return context.withConnectionCollation(parseConnectionCharacterSet(variableAssign.getAssignValue()));
            case COLLATION_CONNECTION:
                MySQLCharacterSets collation = parseCollation(variableAssign.getAssignValue());
                if (setNamesWithCollationAssignment && !context.getConnectionCharacterSetName().equals(collation.getCharacterSetName())) {
                    throw new CollationCharsetMismatchException(collation.getCollationName(), context.getConnectionCharacterSetName());
                }
                if (setNamesWithCollationAssignment) {
                    validateClientCharacterSet(context.getClientCharacterSetName());
                }
                return context.withConnectionCollation(collation);
            default:
                return context;
        }
    }
    
    private MySQLCharacterSets parseClientCharacterSet(final String value) {
        String normalizedValue = formatVariableValue(value).toLowerCase(Locale.ROOT);
        validateClientCharacterSet(normalizedValue);
        return parseCharacterSet(value);
    }
    
    private void validateClientCharacterSet(final String characterSet) {
        if (IMPERMISSIBLE_CLIENT_CHARACTER_SETS.contains(characterSet)) {

View on GitHub (pinned to e952770a21)

Solutions

Pair the collation with its own charset: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci (check MySQL's information_schema.COLLATIONS for the mapping)
If you only need the collation, set COLLATION_CONNECTION alone without a SET NAMES collation clause, keeping the existing charset consistent
Align driver charset/collation properties (characterEncoding, connectionCollation) so they reference the same charset family

Example fix

-- before
SET NAMES utf8mb4 COLLATE latin1_swedish_ci;

-- after
SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Defensive patterns

Strategy: validation

Validate before calling

// client-side pre-check: collation must belong to the charset being set
if (!collation.getCharacterSetName().equals(charsetName)) {
    throw new IllegalArgumentException("Collation " + collationName
            + " belongs to " + collation.getCharacterSetName() + ", not " + charsetName);
}

Prevention

Always generate SET NAMES collation from the same charset family (utf8mb4 -> utf8mb4_*)
Fetch the charset->collation mapping from information_schema.COLLATIONS when building SET statements dynamically

When it happens

Trigger: SET NAMES utf8mb4 COLLATE latin1_bin (collation belongs to latin1, not utf8mb4), or SET NAMES followed by SET COLLATION_CONNECTION to a collation from a different charset while setNamesWithCollationAssignment is true: context.getConnectionCharacterSetName() != collation.getCharacterSetName() triggers the throw.

Common situations: Copy-pasted SET NAMES statements mixing charset and collation from different families; client drivers defaulting one charset while app code overrides only the collation; migrating configs from utf8 to utf8mb4 and updating only half of the pair.

Related errors

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/2573d9732f84961e. Report an issue: GitHub.