MariaDB Uca 1400 Collations Utf8mb3_uca1400_ai_ci

Published
Length
3 min read

Luis Felipe Dias

All posts

The mariadb uca 1400 collations utf8mb3_uca1400_ai_ci rule sorts three-byte text without case or accent checks. You set it up at the server, database, table, or column level. Teams often hand out broad alter rights so engineers can update old text rules. Broad roles let one bad query lock tables or read private user rows. How mariadb uca 1400 collations utf8mb3_uca1400_ai_ci works A collation is a rule that tells the database how to sort and compare text. The UCA 1400 part means the rule foll

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

The mariadb uca 1400 collations utf8mb3_uca1400_ai_ci rule sorts three-byte text without case or accent checks. You set it up at the server, database, table, or column level. Teams often hand out broad alter rights so engineers can update old text rules. Broad roles let one bad query lock tables or read private user rows.

How mariadb uca 1400 collations utf8mb3_uca1400_ai_ci works

A collation is a rule that tells the database how to sort and compare text. The UCA 1400 part means the rule follows the Unicode Collation Algorithm version fourteen. The three-byte prefix means each text character uses up to three bytes of storage. That old format covers common scripts, while the four-byte format adds emojis and rare symbols.

The suffix has two short tags that control how text checks run. The first tag ignores accents, so plain letters match letters with accent marks. The second tag ignores letter case, so upper and lower case letters match. This setup helps search features find names or words even when users type variants.

Database admins apply this sorting rule when they create or change tables and columns. When you change a column rule, the engine often rebuilds the table index. Index rebuilds take time and hold locks on busy tables in production. Engineers need schema change privileges to run these updates during release windows.

Why collation changes create role and data risks

Native database roles decide who can log in and which schemas they can touch. To fix sorting bugs or upgrade tables, teams often grant broad admin rights. An engineer with schema change rights can also run read queries on any table. Once the session starts, the database trusts every statement that user sends.

Native grants do not check what each command does in flight. A developer who logs in to check a sorting bug can dump customer tables. Test queries on text columns often return emails, phone numbers, card numbers, or national IDs. Without query checks, raw private fields show up right on the developer screen.

Shared database accounts make this gap worse during busy schema migrations. Teams pass around one admin password so scripts and people can update collation settings. When a bad table alter locks production, the native log shows only the shared user. Security teams cannot tell which person ran the command or what data came back.

GitLab CI Security + AI Agent Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

How to manage schema updates and query checks

Start by separating schema change duties from daily read tasks in your database. Give developers read roles for testing how text sorting behaves on sample records. Keep alter privileges out of standard user roles used for daily debugging.

Next, require peer review before anyone runs a table change on production data. A small collation update on a large table can block writes for many minutes. Checking the command before it runs stops accidental downtime during peak traffic hours.

Finally, add field masking to rewrite sensitive text columns in query results. When engineers test sorting on user tables, masking rewrites matched fields in results. They can verify that text sorts in the right order while emails stay hidden.

Check MySQL queries and results on the wire

MySQL grants control who logs in. They do not check single queries or mask sensitive columns once a session starts. With hoop.dev, you run an open-source proxy, a sidecar for your MySQL. It runs next to MySQL, RDS, or Aurora and reads the MySQL wire protocol.

Multi-statement batches and prepared queries are split and checked one by one by hoop.dev guardrails. When a rule refuses a query, MySQL returns a native error packet with your message. On the response path, hoop.dev data masking rewrites string values in returned rows that match your rules. Every query and decision is logged per resource. See the MIT-licensed project on GitHub.

Frequently asked questions

What do the ai and ci tags mean in this collation name?

The ai tag stands for accent insensitive, which treats accented and plain letters as equal. The ci tag stands for case insensitive, which treats capital and small letters as equal. Together, they make text searches and sorting forgiving for end users.

Should new tables use three-byte or four-byte text formats?

New tables should use the four-byte text format whenever possible. The three-byte format is an older standard that cannot store four-byte characters like emojis. Teams mostly use the three-byte UCA 1400 rule when updating older schemas without changing storage sizes.

Why do collation updates cause risk in production databases?

Changing a column collation forces the database to re-sort and rebuild text indexes. That process requires elevated schema rights and can lock large tables during peak traffic. Giving engineers permanent admin roles for these tasks also exposes raw customer data during fixing.

The query runs. The emails stay hidden.