The default depends on the distribution you take MariaDB from.
In rpm packages from SUSE, as far as I know, the default is utf8. Compiled in.
In all deb packages the default is utf8mb4_general_ci, set it my.cnf

In binary tarballs and rpms from mariadb.org the default is, indeed, latin1_swedish_ci. Changing it is a rather drastic incompatible change that will break existing applications. Given how few complains we ever got about it, we might better take some time thinking about how to do this transition in a least disruptive way possible. Instead of flipping the switch now.

In general, we agree it has to be done. It's not 1995 anymore. The question only is when to do it and how.

any news ?

> In rpm packages from SUSE, as far as I know, the default is utf8. Compiled in.

A default of `utf8` might actually be worse, since people are getting the 3-byte broken MySQL version of UTF-8 and they will not understand why most characters are stored correctly but some get mangled. It takes deep understanding of the history of both Unicode and MySQL to understand that in MySQL, `utf8` means something different than the rest of the world means by it.

> In all deb packages the default is utf8mb4_general_ci, set it my.cnf

That's a sane choice.

> In binary tarballs and rpms from mariadb.org the default is, indeed, latin1_swedish_ci. Changing it is a rather drastic incompatible change that will break existing applications.

I have heard this argument before and I find it rather weak. It might break existing swedish applications. That never need their text content to be read in any other part of the world. And for which 255 characters suffice...

For applications from any other place in the world, or that need more than 255 characters, this default makes no sense whatsoever. The idea that there might be many applications for which a swedish 255 character set is ok is imho laughable. Either apps stick to ASCII, in which case basically all encodings are ok for it, or they need support for more characters and then basically there is only one suitable encoding and that is utf8mb4. All others are broken out of the box.

In the meantime, this default continues to break all new applications. The backward compatibility argument is moot There is no compatibility with these legacy encodings. They only work for a small subset of machines in a particular region of the world and they break for all other regions. They are a left over of days gone by when people simply had no internet and transferred files to each other using floppy disks. With these legacy encodings, people from France cannot read files from Germany. People from England cannot read files from Sweden etc. There simply are ZERO applications in today's world for which latin1 makes sense. And there are ZERO aplications for which utf8mb4 is not the best and in fact ONLY encoding that makes sense. All other encodings will break some characters. Only utf8mb4 works under all circumstances.

> we might better take some time thinking about how to do this transition

With all due respect...

"the hardest thing about Unicode is not Unicode itself, but all the legacy encodings used in other software and files creeping into your project and breaking things."

I wrote this on may 5th, 2010. over 12 years ago! And it's still true. Please switch the default. Millions of devs will be better off for it.

The way MySQL (and now by inheritance MariaDB) handles Unicode is so problematic, I wrote a post to warn devs just about that in 2015. 7 years ago!

> In general, we agree it has to be done. It's not 1995 anymore. The question only is when to do it and how.

I'm glad you agree. And sorry for my rant. But frankly I have been watching the encoding drama unfold literally for well over a decade now and I've heard the backward compatibility argument so many times. So I get triggered by it

I suggest you have a look at what MySQL did. You just make a new major release and splatter this thing all over the release notes. Possibly you check whether an explicit encoding is set on import of data and print a warning if not. But in my experience there are only two scenarios with MySQL:

• People set the encoding explicitly -> they are not affected
• People forget to set the encoding -> they get latin1 -> their app is broken until they fix it

There are no apps using latin1 that are not broken. That is a blanket statement I know, but I challenge you to show me such an app. The nature of these legacy encodings is that they are incompatible out of the box. And someone that did not address their encoding issues still after all these years does not deserve to be helped / assisted by maintaining this broken default. New devs using MariaDB in their first project deserve to be helped.

I want to add some more. Sorry about bothering you but maybe it is ok because you might learn something?

There basically are 3 major problems that in MariaDB compound together to create a real drama that costs the world billions of dollars:

1: Very few developers understand character encodings
2. The default in one of the world's most popular data storage platforms is broken
3. The `utf8` encoding in that platform is also broken!

So what happens is this:

• A developer with little understanding of encodings installs MariaDB and connects his app to it
• He delivers the app and soon, customers complain about mangled characters
• He discovers that the default encoding is latin1_swedish and reads up on Unicode
• He switches the encoding to `utf8`, oblivious of the fact that that is in fact ALSO BROKEN!
• He spends valuable time trying to figure out how to fix the existing data with mangled text
• He delivers the fix to the customer
• For a while, all seems well
• Reports start to come in about emoji's being mangled
• The dev reads up on the Basic Multilingual Plane and the history of MySQL/MariaDB
• To his dismay, the dev realizes that `utf8` in MariaDB is something different altogether than what he assumed it was
• The dev finally changes the encoding to `utf8mb4`.
• A new attempt at fixing the broken data is made
• The second fix is delivered to the customer
• Finally the app works!

These are some of the most expensive changes that are possible. And every app that is made has to go through this process if the dev was not an encoding expert. These issues involve schema migrations, data migrations and the devs to read up a lot and do a lot of research. They demand of these devs that they become Unicode and MariaDB experts just to be able to understand what is happening. And every single dev has to go through this again and again. I have issues on my current project (logged by me) saying 'we have to change the encoding for these tables' and then a long list of tables. In many occasions, the server defaults can not be easily changed by the dev (managed databases). So we just have to tell every dev: 'if you create a table, set the encoding'. All because of this one default setting that is wrong.

People don't understand this. And that's ok. From your comments on how changing the default might break applications, I realize that you don't really understand this as well. And that's also ok. No one should be forced to learn about this stuff. There are better things we can do with our time. But maybe you can trust me and my experience and just change the default and be done with it. I promise you it will not break any applications that were not already broken and it will prevent the drama I described above for all new applications. And it will allow devs to not understand encodings and focus on their business logic instead of requiring them to become encoding/MariaDB experts. The world will be better off for it.

as a status update — this issue is related or depends on a bunch of other issues, notably:

• MDEV-19123 Change default charset to utf8mb4
• MDEV-27266 Improve UCA collation performance for utf8mb3 and utf8mb4
• MDEV-27490 Allow full utf8mb4 for identifiers
• MDEV-27009 Add UCA-14.0.0 collations

this is work in progress at the moment

If I read this correctly, you guys have fixed it!

That deserves a 'Hurray!!' and congratulations to all involved.
Thank you for making the world a better place!

StijnDeWitt, your original guess in 2022 was not correct.
Changing default collations for character sets is possible only starting from MDEV-30164 (11.2.1).
So one can set the default collation for utf8mb4 to say utf8mb4_uca1400_ai_ci

This task is still open. It's about changing the hard coded default to utf8mb4_uca1400_ai_ci.

We need to make sure:

• to do all possible optimization for utf8mb4_uca1400_ai_ci, to make sure it does not degrade much in performance comparing to utf8mb4_general_ci
• we change defaults for all Unicode character sets at the same time
• to add uca1400 collations for utf16be

The easiest solution to implement this task would be to change the default value for GLOBAL.character_set_collations from empty to:

utf8mb3=utf8mb3_uca1400_ai_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci

If we go this way, we should make sure that the implementation of @@character_set_collations does not have any performance problems with a non-empty value.

I would like to thank developers for listening

Hello serg,

Can you please review patches for this MDEV?

There are three patches. They are in this branch:

https://github.com/MariaDB/server/commits/bb-11.4-bar-MDEV-25829

Hello serg.

Please find a set of new patches for review here:

Step#1:

https://github.com/MariaDB/server/commit/58c1cccc7c8b40ba6d86d80760c6aa3aa965f1d3

Step#2:

https://github.com/MariaDB/server/commit/85c659860c0e4ea2408f1a47c71ee590f32ddc9f

Step#3:
https://github.com/MariaDB/server/commit/1413de15df01602a5fe799f8e136cf9c047da14e

Also, replied by email.

Thanks.

https://github.com/MariaDB/server/commit/58c1cccc7c8b40ba6d86d80760c6aa3aa965f1d3
https://github.com/MariaDB/server/commit/85c659860c0e4ea2408f1a47c71ee590f32ddc9f
https://github.com/MariaDB/server/commit/1413de15df01602a5fe799f8e136cf9c047da14e

are ok to push

bar, I've pushed a fixup commit into your branch, it's clearly named "fixup", so please rename it or squash with the main commit, whatever you prefer

bb-11.5-bar-MDEV-25829 af1418118e0462e0f5173b621ab8b42acab78446 is ok to push into 11.5

Hi, due this task we got some problems after updating MariaDB.
I could reduce the problem to the following sets of commands.
The question is now: Is that by purpose or is that a bug?

SET NAMES utf8mb4;

SET character_set_collations='utf8mb4=uca1400_ai_ci';

CREATE TEMPORARY TABLE `test` (

  `value` text DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test VALUES ('');

SELECT @a:=value FROM test;

SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));

-- results see below

-- now change to utf8mb3

SET NAMES utf8mb3;

SET character_set_collations='utf8mb3=uca1400_ai_ci';

SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));

-- results see below

Results:

MariaDB [translate5]> SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));

+--------------------+------------------------------------+---------------------------------+

| COLLATION(@a)      | COERCIBILITY(CONCAT('a', @a, 'b')) | COLLATION(CONCAT('a', @a, 'b')) |

+--------------------+------------------------------------+---------------------------------+

| utf8mb4_unicode_ci |                                  1 | utf8mb4_bin                     |

+--------------------+------------------------------------+---------------------------------+

and

MariaDB [translate5]> SELECT COLLATION(@a), COERCIBILITY(CONCAT('a', @a, 'b')), COLLATION(CONCAT('a', @a, 'b'));

+--------------------+------------------------------------+---------------------------------+

| COLLATION(@a)      | COERCIBILITY(CONCAT('a', @a, 'b')) | COLLATION(CONCAT('a', @a, 'b')) |

+--------------------+------------------------------------+---------------------------------+

| utf8mb4_unicode_ci |                                  5 | utf8mb4_unicode_ci              |

+--------------------+------------------------------------+---------------------------------+

My main question is:
Why is in case of NAMES = utf8mb4 (same charset as in table) the COERCIBILITY of the CONCAT 1 and therefore the COLLATION utf8mb4_bin and when using utf8mb3 (a different charset) COERCIBILITY is 5 and there fore the COLLATION utf8mb4_unicode_ci.

Is that somehow by purpose or a bug?

Thanks.

For simplicity I removed the third argument to CONCAT, as it has the same collation characteristics as the first one and therefore does not affect the result collation resolution.

In case of utf8mb3:

SET NAMES utf8mb3;

SET character_set_collations='utf8mb3=uca1400_ai_ci';

CREATE OR REPLACE TEMPORARY TABLE t1 (

  `value` text DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test VALUES ('');

SELECT @a:=value FROM t1;

SELECT

  COLLATION(@a), COERCIBILITY(@a),

  COLLATION('a'), COERCIBILITY('a'),

  COLLATION(CONCAT('a', @a)), COERCIBILITY(CONCAT('a', @a))\G

*************************** 1. row ***************************

                COLLATION(@a): utf8mb4_unicode_ci

             COERCIBILITY(@a): 5

               COLLATION('a'): utf8mb3_uca1400_ai_ci

            COERCIBILITY('a'): 5

   COLLATION(CONCAT('a', @a)): utf8mb4_unicode_ci

COERCIBILITY(CONCAT('a', @a)): 5

The result collation resolution is done as follows:

• There are two argument to CONCAT with different character sets (and collations), with equal collation coercibility levels 5 (COERCIBLE).
• The utf8mb4 side wins, because utf8mb4 is a superset for utf8mb3.
• The collation and the coercibility of the CONCAT are copied from the first argument `@a`

In case of utf8mb4:

SET NAMES utf8mb4;

SET character_set_collations='utf8mb4=uca1400_ai_ci';

CREATE OR REPLACE TEMPORARY TABLE t1 (

  `value` text DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test VALUES ('');

SELECT @a:=value FROM t1;

SELECT

  COLLATION(@a), COERCIBILITY(@a),

  COLLATION('a'), COERCIBILITY('a'),

  COLLATION(CONCAT('a', @a)), COERCIBILITY(CONCAT('a', @a))\G

*************************** 1. row ***************************

                COLLATION(@a): utf8mb4_unicode_ci

             COERCIBILITY(@a): 5

               COLLATION('a'): utf8mb4_uca1400_ai_ci

            COERCIBILITY('a'): 5

   COLLATION(CONCAT('a', @a)): utf8mb4_bin

COERCIBILITY(CONCAT('a', @a)): 1

The result collation resolution is done as follows:

• There are two arguments with equal character sets, different collations, equal collation coercibility levels.
• None of the side wins.
• The result collation is chosen as a neutral collation utf8mb4_bin, with a special collation coercibility level 1, which means "NONE" (or "CONFLICT"). Note, this conflicting state can be resolved to a non-conflicting state by adding an explicit COLLATE clause after the CONCAT.

Observations

The result collation resolution itself seems to work as expected in both cases (with utf8mb3 and utf8mb4).

However this CONCAT() should rather return an error when used in the SELECT list without an explicit COLLATE clause following the CONCAT (which would resolve the conflict).

Just checked with MySQL-8.3.0. It also allows to use an expression with the coercibility level of 1 (NONE) in the SELECT list:

drop table if exists t1, t2;

create  table t1 (

  a varchar(10) character set utf8mb4 collate utf8mb4_general_ci,

  b varchar(10) character set utf8mb4 collate utf8mb4_unicode_ci

);

create table t2 as select concat(a,b) from t1;

show create table t2;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                              |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t2    | CREATE TABLE `t2` (

  `concat(a,b)` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Which I'd say is rather a bug. But this behavior has been this way for many years in MySQL and then MariaDB.

Not sure if we should change this behavior in MariaDB at this point (i.e. raise an error instead of returning an utf8mb4_bin result).

Possible workarounds to avoid having such a conflicting state in a SELECT list expression:

• Use an explicit COLLATE with one of the argument:

  SELECT CONCAT('a' COLLATE utf8mb4_unicode_ci, @a);

  SELECT CONCAT('a', @a COLLATE utf8mb4_unicode_ci);

• Use an explicit COLLATE following the CONCAT, to resolve the conflicting state:

  SELECT CONCAT('a', @a) COLLATE utf8mb4_unicode_ci;

greenman
It looks like the kb documentation on COERCIBILITY needs an update since with this MDEV a new derivation DERIVATION_CAST is added between DERIVATION_SYSCONST and DERIVATION_COERCIBLE, and COERCIBLE applies to both literal strings and user variables now, and possible other changes.

https://mariadb.com/kb/en/coercibility/

modified   sql/sql_type.h

@@ -3019,13 +3019,46 @@ static inline my_repertoire_t &operator|=(my_repertoire_t &a,

 enum Derivation

 {

-  DERIVATION_IGNORABLE= 6,

-  DERIVATION_NUMERIC= 5,

-  DERIVATION_COERCIBLE= 4,

+  DERIVATION_IGNORABLE= 7, // Explicit NULL

+

+  /*

+    Explicit or implicit conversion from numeric/temporal data to string:

+    - Numbers/temporals in string context

+    - Numeric user variables

+    - CAST(numeric_or_temporal_expr AS CHAR)

+  */

+  DERIVATION_NUMERIC= 6,

+

+  /*

+    - String literals

+    - String user variables

+  */

+  DERIVATION_COERCIBLE= 5,

+

+  /*

+    String cast and conversion functions:

+    - BINARY(expr)

+    - CAST(string_expr AS CHAR)

+    - CONVERT(expr USING cs)

+  */

+  DERIVATION_CAST= 4,

+

+  /*

+    utf8 metadata functions:

+    - DATABASE()

+    - CURRENT_ROLE()

+    - USER()

+  */

   DERIVATION_SYSCONST= 3,

+

+  /*

+    - Table columns

+    - SP variables

+    - BINARY(expr) and CAST(expr AS BINARY)

+  */

   DERIVATION_IMPLICIT= 2,

-  DERIVATION_NONE= 1,

-  DERIVATION_EXPLICIT= 0

+  DERIVATION_NONE= 1,      // A mix (e.g. CONCAT) of two differrent collations

+  DERIVATION_EXPLICIT= 0   // An explicit COLLATE clause

 };

UPDATE: also MDEV-35041 added another derivation