DEV Community: Florian Köhler

MySQL/MariaDB character sets and collations explained – why utf8 is not UTF-8

Florian Köhler — Tue, 04 Jan 2022 12:04:32 +0000

Table Of Contents

Character sets and collations explained
Charater sets: The tale of □�💩
Why utf8 in MySQL is not UTF-8
Collations: How to sort things
What about performance?
Conclusion

The relationship between character sets and collations always seemed pretty vague to me, let alone the possible impact on performance. So if you are like me and want to know the difference between utf8 and utf8mb4 and why mixed collations are bad for your database performance, read on!

Character sets and collations explained

Since computers only understand binary and humans don't, we use character sets to map binary to specific characters. The difficulty was that different nations used different symbols and sometimes mapped the same binary sequence to other characters. So based on the character set you used, the same data could mean different things.

Furthermore, depending on your language, you may sort certain characters differently than in other languages.

To quote the MariaDB documentation:

A character set is a set of characters [and its mapping to binary] while a collation is the rules for comparing and sorting a particular character set.

– MariaDB: Character Set and Collation Overview

For example, the first character of this article, T (LATIN CAPITAL LETTER T), in binary looks like 1010100. The computer reads this binary sequence and knows that this is 84 in decimal. Now it looks up which character maps to this number. Using the UTF-8 character set, the number 84 equals T.

Charater sets: The tale of □�💩

One of the most known character sets is ASCII (American Standard Code for Information Interchange). It only supports 128 different characters and is nowadays primarily used for art 😉

   |           |
       |      |  |
  | |   |       |
      ___     _%%%_
  \,-' '_|    \___/    hjm
  /""----'

A sperm whale and a bowl of petunias above the surface of an alien planet by Harry Mason (Hajoma).

Someone even recreated the entire fourth episode of Star Wars in ASCII.

Even though 128 characters are enough to cover the English language and a few basic symbols, the limited range is unsuitable for international and modern communication. Therefore the UTF-8 standard was created, which is 100% backward compatible with ASCII but allows up to four bytes per character instead of just one.

Four bytes enable pretty much every character of every language, and that is why we can use all these funny emojis and don't see something like □�. Over time it became the de facto standard for everything.

Why `utf8` in MySQL is not UTF-8

Of course, such an extensive and versatile character set is handy for a database that may store very different and complex data. That is why MySQL already implemented the standard for UTF-8 (RFC 2279) in the pre-pre-release of MySQL 4.1 on March 28, 2002. The old standard even allowed for six bytes per character.

For whatever reason, a few months later, in September 2002, a MySQL developer decided to push a one-byte commit UTF8 now works with up to 3 byte sequences only to the repository and change the allowed bytes from six to three.

Since then, the character set called utf8 has been a crippled and proprietary variation as it neither conforms to the old nor the new definition (RFC 3629) of UTF-8. The misleading name still causes issues today.

This is probably one of most expensive single char commits in world...

– morphles, Nov 25, 2021

Because the utf8 character set only allows 3 bytes, you can't store some special characters in a database that utilizes this character set. Unfortunately, no one knows who made the change as all names were lost when moving the repository from BitKeeper to GitHub.

To remediate this mistake MySQL added the utf8mb4 charset in version 5.5.3. utf8mb4 fully implements the current standard. Now utf8 is an alias for utf8mb3 and will be switched to utf8mb4.

Collations: How to sort things

The order of numbers is pretty straightforward – four is lower and comes before the number five. Even the English alphabet is still pretty easy. But what about other languages with additional characters or completely different symbols? Which emoji comes first? 😃 (SMILING FACE WITH OPEN MOUTH) or 😋 (FACE SAVOURING DELICIOUS FOOD)?

Collations – sets of algorithms – determine how to sort certain characters. Which collation works best for you depends on your use case. But first, let's see how we can determine the difference between each collation.

To get a list of all collations your server supports, we can execute the following (shorted view):

MariaDB [(none)]> SHOW COLLATION;
+------------------------------+----------+------+---------+----------+---------+
| Collation                    | Charset  | Id   | Default | Compiled | Sortlen |
+------------------------------+----------+------+---------+----------+---------+
| utf8mb4_general_ci           | utf8mb4  |   45 | Yes     | Yes      |       1 |
| utf8mb4_bin                  | utf8mb4  |   46 |         | Yes      |       1 |
| utf8mb4_unicode_ci           | utf8mb4  |  224 |         | Yes      |       8 |
+------------------------------+----------+------+---------+----------+---------+

The collation name is separated into three parts. The first part is always the character set the collation belongs to. A collation can only be associated with one character set, but there can be multiple collations for a character set. The second part always describes the functionality of the collation. And the optional third part can be one or more additional feature flags.

Here is a list of common feature flags:

Suffix	Meaning
`ci`	case-insensitive
`cs`	case-sensitive

The middle part is the most interesting because it specifies the collation algorithm.

bin stands for binary and sorts data by its binary notation and does not consider any language-specific rules.

general honors some rules but uses a simplified algorithm favoring speed over accuracy.

unicode or its versioned variants like unicode_520 use the official UCS (Universal Coded Character Set) algorithms. Unicode collations provide the most accurate sorting.

For example utf8mb4_general_ci does not knwo how to sort s and the German character ß ("sharp S") in contrast to utf8mb4_unicode_520_ci which sorts ß just fine (ß comes after s):

MariaDB [(none)]> SELECT 's' < 'ß' COLLATE utf8mb4_general_ci;
+---------------------------------------+
| 's' < 'ß' COLLATE utf8mb4_general_ci  |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT 's' > 'ß' COLLATE utf8mb4_general_ci;
+---------------------------------------+
| 's' > 'ß' COLLATE utf8mb4_general_ci  |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT 's' < 'ß' COLLATE utf8mb4_unicode_520_ci;
+-------------------------------------------+
| 's' < 'ß' COLLATE utf8mb4_unicode_520_ci  |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT 's' > 'ß' COLLATE utf8mb4_unicode_520_ci;
+-------------------------------------------+
| 's' > 'ß' COLLATE utf8mb4_unicode_520_ci  |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.000 sec)

Unfortunately, utf8mb4_unicode_ci is based on UCS 4.0, which is very old. Even the newer utf8mb4_unicode_520_ci (UCS 5.2.0) is more than ten years old. But newer collations are already discussed, and we may see collations based on UCS 14 in version 10.8 of MariaDB. MySQL already implemented a few collations based on more recent UCS versions.

There are language-specific variants like utf8mb4_german2_ci, but I have never used them personally. I recommend sticking to the unicode versions as they fit most use cases.

What about performance?

There are some considerations when choosing your collation. The most important one is always to use the same collation when comparing strings.

For example, when joining two tables with different sorting rulesets, MariaDB/MySQL cannot use indices and falls back scanning the entire tables.

Regarding the different collation algorithms: The unicode variants should be slower than the general variant due to the more complex algorithms, but this was years ago when computers were much weaker than now. With modern hardware, the speed gain should be hardly notable.

One last concern is the size stored on the disk because UTF-8 supports four bytes per character. But since UTF-8 is a variable-width character encoding, it only uses as many bytes as needed. For example, the first 128 characters like ASCII only use one byte.

Conclusion

Now you know the difference between utf8 and utf8mb4 and what collation to use. There is no reason to use utf8/utf8mb3, and I believe everyone who chose utf8 expected to get the real UTF-8 (utf8mb4), not a crippled version. So to use it, you have to specify utf8mb4 explicitly.

Furthermore, we should prefer accuracy over speed. There is no need to use an old and quirky collation like utf8mb4_general_ci with modern hardware.

If you ask yourself how to convert your database stay tuned and follow me. I will cover this topic in my next post. Until then, have fun!

Did I add value?

I don't like ads, and I respect your privacy. Therefore my blog has no advertisements or any tracking cookies.

If you like what you read, please support my work: Buy me a beer

If you can't make monetary support, I understand. Please like and share this content on the platform of your choice.

How To Fix Slow MariaDB Replication Lag

Florian Köhler — Wed, 29 Sep 2021 13:19:52 +0000

Hi there!

If you have experienced significant replication lags and replication so slow that it needed hours to complete or couldn't catch up at all, I may have a solution for you.

A couple of weeks ago, I set up a MariaDB replication from scratch for a production database. The database has a considerable size and is under heavy use.

As usual, I used Mariabackup to copy the entire data directory of MariaDB to the new replica server. Unfortunately, I had to interrupt my work and wasn't able to continue until 12 hours later. As I resumed where I left off, I noticed that the database grew substantially. Since I didn't want to resync about 200 GB of data, I left the rest to the replication.

Well, It did not go as planned. The replication was so slow that it couldn't catch up.

The server had enough resources to handle the amount of data, so why was it so slow? After some research, I found that while MariaDB fully benefits from multiple CPU cores, the replication process does not!

The replication process only runs on one core and processes events in serial. Luckily, you can fix this by increasing slave_parallel_threads.

On your replica server, set the value to the number of CPU cores you can spare. You can change this parameter without restarting the database, but to do this, you first have to stop the replication:

STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_threads = 4;
START SLAVE SQL_THREAD;
SHOW GLOBAL VARIABLES LIKE 'slave_parallel_threads';

Now your replica server will execute events in parallel (shortened view):

MariaDB [mysql]> SHOW PROCESSLIST;
+--------------+-----------------------------------------------------------------------------+
| Command      | State                                                                       |
+--------------+-----------------------------------------------------------------------------+
| Slave_IO     | Waiting for master to send event                                            |
| Slave_worker | Waiting for work from SQL thread                                            |
| Slave_worker | Waiting for prior transaction to commit                                     |
| Slave_worker | Closing tables                                                              |
| Slave_worker | Waiting for work from SQL thread                                            |
| Slave_SQL    | Slave has read all relay log; waiting for the slave I/O thread to update it |
+--------------+-----------------------------------------------------------------------------+

I hope this is helpful to anyone who has similar issues.

How Access Control Lists (ACLs) work and how to use them

Florian Köhler — Thu, 23 Sep 2021 11:05:02 +0000

Table Of Contents

Requirements
What exactly are ACLs
Recap the Basics
Viewing current ACLs
How ACLs work
- Masks and effective rights explained
- Precedence of ACLs
Working with ACL entries
- How to create/modify ACL entries
- Creating default permissions for new files
- Removing ACL entries
Conclusion

I often use ACLs when I have to give permissions to specific users or groups without compromising security. But I never fully understood how they work, as they are pretty complex. This guide will explain how they work and how to use them.

Requirements

I assume experience with Linux and the command line.

ACLs must be supported and enabled by the filesystem (mounted with the acl option). As systemd depends on the acl package and the most common filesystems have ACLs enabled by default, any modern Linux distribution should work.

If you are unsure if ACLs are enabled, you can check this with the following command:

tune2fs -l /dev/sdXY | grep "Default mount options:"

If enabled, the output should look something like this:

Default mount options:    user_xattr acl

To set acl as a default mount option for a filesystem, use the tune2fs utility:

tune2fs -o acl /dev/sdXY

What exactly are ACLs

Access Control Lists allow for more fine-grained and flexible permissions for files and directories. Based on the draft for POSIX 1003.1e, ACLs are a superset of standard Linux permissions.

They are handy if you have complex permission requirements. For example, you got a docroot of your Web application owned by the www-data user and the developer group. The new product owner needs read access to the log files of the application. Now what? She does not have the same UID as the www-data user, and adding her to the developer group would be too permissive.

This situation can be tricky as standard Linux permissions only allow one user and one group. Sure, you could make everything read-only to everyone, but that would be a bad idea. You can solve this situation with ACLs by adding a new user with read-only permissions to the docroot.

The situation above is just a simple example, and there are way more complex scenarios that ACLs can solve.

Even though the draft never made it to an official release, most Unix-like systems implemented Access Control Lists.

Recap the Basics

Before we dive in, let's quickly recap how basic permissions on Linux work. Linux gives us three entities for which we can manage permissions separately:

User (owner)
Group (owner group)
Other (everyone else)

For each entity, we can set three permissions (there are more, but for the sake of simplicity, we will ignore them for now):

Read (octal 4)
Write (octal 2)
e Xecute (octal 1)

For example:

drwxr-x--- 2 cassidy developer 4096 Sep 11 13:05 exampledir

Here we can see that the owner is the user cassidy and the owner group is developer. Cassidy can read and create files in the directory, whereas the group members can only read/list the directory's content. Everyone else has no access at all.

Viewing current ACLs

After we reviewed how basic permissions work, let's look at identifying files with Access Control Lists. Luckily ls knows about ACLs and indicates what files have ACLs by appending a + to the permissions:

drwxr-x---+ 2 cassidy developer 4096 Sep 11 13:05 exampledir

Now that we know that this file has ACLs let's display all ACLs by using the getfacl command:

[root@lab docroot]# getfacl exampledir
# file: exampledir
# owner: cassidy
# group: developer
user::rwx
user:finley:rwx            #effective:r-x
group::r-x
mask::r-x
other::---
default:user::rwx
default:user:finley:rwx    #effective:r-x
default:group::r-x
default:mask::r-x
default:other::---

That's a lot to digest. Let's break it down.

How ACLs work

The first three lines display the filename, the owner, and the owner group:

# file: exampledir
# owner: cassidy
# group: developer

The next block specifies individual user permissions. This example shows that the user finley has read and execution rights for this directory. The first line without a user name, user::rwx, equals to the permission of the owner (Cassidy):

user::rwx
user:finley:rwx

The third block contains group permissions. In this case, there are only the permissions of the owner group, but there could be a named entry like group:management:r-x, which would allow the management group to access the contents of this directory:

group::r-x

Next, we have the mask block. Masks can be challenging, and we will cover them later in detail. For now, keep in mind that masks limit access rights, and the comments like #effective:r-x display the actual permissions.

mask::r-x

Next up is the other block. It works like the previous blocks and specifies the permissions for everyone else:

other::---

Lastly, we have the default block. This block exists only on directories and includes all other blocks mentioned before. Here we can specify what permission should apply to new files within a directory.

default:user::rwx
default:user:finley:rwx    #effective:r-x
default:group::r-x
default:mask::r-x
default:other::---

ACLs with only the three base entries user::, group:: and other:: are called minimal ACLs. ACLs containing named entries are called extended ACLs.

Masks and effective rights explained

After looking at the output, let's address probably the most confusing aspect about Access Control Lists: Masks and effective rights.

Standard permissions can't reflect complex ACLs. Therefore the working group agreed on a complex masking mechanism to preserve maximum compatibility between basic permissions and ACLs.

To better understand masks, let's start with five simple statements that always will be true:

If the ACL has no mask entry, the permissions of the owner group will correspond to the ACL group.
If the ACL has named users or groups, it will have a mask entry.
If the ACL has a mask entry, the permissions of the owner group will correspond to the mask entry.
Unless otherwise stated, the mask entry's permissions will be the union of all permissions affected by an ACL and recalculate on every change.
Masks denote maximum access rights that can be granted by a named user entry, named group entry, or the group:: entry.

The first statement is pretty self-explanatory. As long as there is no mask, the permissions of the owner group and the group:: entry will be the same. Changes to the owner group will be reflected in the group:: entry and the other way around.

The next one states that as soon as you add named user or group entries, setfacl will automatically add a mask entry if it does not exist.

The third statement is where it gets interesting. If a mask exists, the meaning of the owner group will change. The owner group will equal the mask entry. Changes with chmod to the owner group will change the mask entry. Changes via setfacl to the mask entry will change the owner group's permissions.

But how can we manage the permissions of the owner group without changing the mask? Don't worry. You can change permissions for the owner group via the group:: entry.

Thanks to the fourth statement, we know that the mask's permissions can change and always equal the union of named users, named groups, and the group:: entry.

To prevent the mask from recalculating on change, use -n option.

The last statement lets us know, and this is important, that you can't grant more permissions than specified in the mask for named users, named groups, and group:: entries. This restriction is what causes the so-called effective rights.

For example, if we add the following ACL and mask:

user:finley:rwx
mask::r-x

The user finley only has the rights rx, as w is not allowed by the mask.

Like every other entry, we can change the mask explicitly:

setfacl -m m:rw

Unless you use -n to prevent the mask from recalculating, all following changes will overwrite your mask again.

Precedence of ACLs

One last thing to understand is the following order in which the algorithm checks for permission:

File owner.
Named user entries.
Owner group (or group:: entry).
Named group entries
Other

The first match determines the access to the resource. The order is essential, as it will deny writing access if a named user entry with r exists even when the user is a member of matching group entry with correct permissions:

[root@lab docroot]# getfacl exampledir
# file: exampledir
# owner: cassidy
# group: developer
user::rwx
user:finley:r-x
group::rwx
mask::rwx
other::r-x

[finley@lab docroot]$ groups
finley developer

[finley@lab docroot]$ touch exampledir/testfile
touch: cannot touch 'exampledir/testfile': Permission denied

Working with ACL entries

At this point, we should have a good understanding of how Access Control Lists work. Finally, let's modify ACLs. Fortunately, this is pretty straightforward. To set or remove ACLS, use the setfacl command. The syntax is always:

setfacl [option] [action/specification] file

A colon separates the specification into three sections: object type, associated object, and permissions. Here is a list of all object types:

Name	Type	Text form
Owner	`ACL_USER_OBJ`	`u::rwx`
Named user	`ACL_USER`	`u:name:rwx`
Owner group	`ACL_GROUP_OBJ`	`g::rwx`
Named group	`ACL_GROUP`	`g:name:rwx`
Mask	`ACL_MASK`	`m::rwx`
Others	`ACL_OTHER`	`o::rwx`

The following example indicates that we want to modify the permissions for the user finley. It is possible to use UIDs and specify permissions as octal numbers or characters:

u:finley:6
u:33:rw

You can modify multiple entries simultaneously by separating specifications with a comma:

u:finley:rwx,g:accounting:rx

If you use setfacl on a file system that does not support ACLs, setfacl tries to reflect the desired permissions via the standard permissions and output an error. Be aware that this could lead to unexpected results.

Here are some common options for setfacl, but I will explain them in detail in the following sections.

Tip: If you are unsure if your ACL results in the expected outcome, you can use the --test option to display the new ACL entries without changing the current ones.

OPTION	DESCRIPTION
`-m`	Modify or add an ACL entry (always needs to be the last option).
`-d`	Sets ACL entry as default (Only works on directories).
`-R`	Recursively applies changes.
`-x`	Removes specified entry.
`-k`	Removes all default entries.
`-b`	Removes all entries.
`-n`	Prevents the mask from being recalculated.

How to create/modify ACL entries

To create or modify ACLs, use the modify option -m and follow it with your specification explained above. If the same object exists, the new entry will overwrite existing permissions. For example, to add or change the permissions for the user finley to rwx, execute the following:

setfacl -m u:finely:rwx exampledir

It is crucial that after the option -m, the specification follows immediately. So if you want to change the permissions recursively, you have to write -Rm.

-mR will result in an error!

Tip: If you want to apply read-only permissions for files and directories recursively, you can use rX. A capital X only applies execution rights to directories. So all files would get r permissions, and all directories would become rx permissions.

Creating default permissions for new files

If you want newly created files to get specific permissions automatically, you can specify default permissions on the parent directory:

setfacl -dm u:finely:rwx exampledir

This only works for directories (-Rdm ignores files). All new files and directories in exampledir will inherit these permissions (new directories will also inherit the default entries). Unfortunately, this applies only to newly created files, not copied ones.

Removing ACL entries

To remove single entries, use the -x option instead of -m:

setfacl -x g:accounting exampledir

Like with the -m option, you can use the -d switch to remove single default entries: -dx.

To remove all default entries, use the -k option:

setfacl -k exampledir

If you want to remove all ACL entries, you can nuke them with the -b option, but be careful when to use it!

setfacl -b exampledir

Furthermore, be aware that unless you use the -n switch, the mask will recalculate when removing entries. So check for possible breaking changes!

Conclusion

You have now learned how Linux Access Control Lists work and how to use them. I hope it helps to solve complex permission structures more confidently.

If you want to read more about ACLs, I recommend the article from Andreas Grünbacher, one of the draft's authors for POSIX ACLs. Not to mention the man pages for acl, getfacl, and setfacl:

If you found any mistakes or want to say hi, send me a message.

DEV Community: Florian Köhler

MySQL/MariaDB character sets and collations explained – why utf8 is not UTF-8

Character sets and collations explained

Charater sets: The tale of □�💩

Why utf8 in MySQL is not UTF-8

Collations: How to sort things

What about performance?

Conclusion

Did I add value?

How To Fix Slow MariaDB Replication Lag

How Access Control Lists (ACLs) work and how to use them

Requirements

What exactly are ACLs

Recap the Basics

Viewing current ACLs

How ACLs work

Masks and effective rights explained

Precedence of ACLs

Working with ACL entries

How to create/modify ACL entries

Creating default permissions for new files

Removing ACL entries

Conclusion

Why `utf8` in MySQL is not UTF-8