Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30164

System variable for default collations

Details

    Description

      We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

      A hard coded change in the collation library is not desirable, and is hardly even possible.

      It should be implemented on the server (rather than collation library) level, to set default collation overrides per-session:

      • For old-to-new replication
      • Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
      • We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
      • Some users will want to have accent sensitive comparison by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

      A new system variable @@character_set_collations

      The easiest way to solve all these problems is introducing a new system variable, in this format:
      'charset1=collation1,charset2=collation2,charset3=collation3'.
      That is, a comma separated list of charset/collation pairs.

      For example:

      SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';

      The new variable will understand contextually typed collations:

      # No needs for the charset prefix in the collation name:
      		 
      SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';

      The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

      Convenience syntax (postponed)

      Originally there was a proposal to add a convenience syntax, however we won't add it under terms of this task:

      Let's additionally introduce a nicer syntax to set this variable in a more readable way:

      SET CHARACTER SET COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;

      Usage example:

      SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
      		 
      CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
      		 
      SHOW CREATE TABLE t1;


      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                                                     |
      		 
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t1    | CREATE TABLE `t1` (
      		 
        `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
      		 
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

      The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:

      • Column collation
      • Table collation
      • Database collation
      • CHAR(expr USING csname)
      • CONVERT(expr USING csname)
      • CAST(expr AS CHAR CHARACTER SET csname)
      • '' - character string literal
      • _utf8mb3'text' - a character string literal with an introducer
      • _utf8mb3 X'61' - a character string literal with an introducer with hex notation
      • _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
      • @@collation_connection after a SET NAMES without COLLATE

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25829 Change default Unicode collation to uca1400_ai_ci

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33182 Server assertion fails when trying to test the connection with DBeaver

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-31748 Reuse Simple_tokenizer

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-31739 Document @@character_set_collations (MDEV-30164)

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-32192 @@character_set_collations to resolve IMPLICIT collation on JOINs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather that collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 0x61 - a character string literal with an introducer with hex notation
            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather that collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 0x61 - a character string literal with an introducer with hex notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}

            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather that collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 0x61 - a character string literal with an introducer with hex notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}

            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 0x61 - a character string literal with an introducer with hex notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}

            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 0x61 - a character string literal with an introducer with hex notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}

            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}

            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}

            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            bar Alexander Barkov added a comment -

            Here's a new version of the patch:

            https://github.com/MariaDB/server/commit/940d028521f02845eda315fc03dc3434920a56a3

            bar Alexander Barkov added a comment - Here's a new version of the patch: https://github.com/MariaDB/server/commit/940d028521f02845eda315fc03dc3434920a56a3
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@collations_session='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@collations_session='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@collations_session='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@collations_session.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.


            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}

            Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

            {quote}
            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}
            {quote}

            Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

            {quote}
            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET DEFAULT COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}
            {quote}

            Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

            h2. Convenience syntax (postponed)
            Originally there was a proposal to add a convenience syntax, however we won't add it under terms of this task:
            {quote}
            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET CHARACTER SET COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}
            {quote}

            h2. Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparision by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

            h2. Convenience syntax (postponed)
            Originally there was a proposal to add a convenience syntax, however we won't add it under terms of this task:
            {quote}
            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET CHARACTER SET COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}
            {quote}

            h2. Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparison by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            h2. A new system variable @@character_set_collations
            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

            h2. Convenience syntax (postponed)
            Originally there was a proposal to add a convenience syntax, however we won't add it under terms of this task:
            {quote}
            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET CHARACTER SET COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}
            {quote}

            h2. Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            serg Sergei Golubchik made changes -
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 10.12 [ 28320 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov added a comment - - edited

            serg, please review a new patch version:

            https://github.com/MariaDB/server/commit/8d51c6d234b1730d4ff3b2c1fe7828eeca81998b

            bar Alexander Barkov added a comment - - edited serg , please review a new patch version: https://github.com/MariaDB/server/commit/8d51c6d234b1730d4ff3b2c1fe7828eeca81998b
            bar Alexander Barkov made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov added a comment -

            Hello serg,
            Please find a new patch version here:
            https://github.com/MariaDB/server/commit/d8519e13fafce94447da11b5b895798744491955
            and my reply by email.
            Thanks.

            bar Alexander Barkov added a comment - Hello serg , Please find a new patch version here: https://github.com/MariaDB/server/commit/d8519e13fafce94447da11b5b895798744491955 and my reply by email. Thanks.
            bar Alexander Barkov made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            bar Alexander Barkov added a comment -

            serg, a new patch version is here:
            https://github.com/MariaDB/server/commit/5102d86beae6b7464b1271081f8003ae81850a5b
            Please also find my reply by email.
            Thanks.

            bar Alexander Barkov added a comment - serg , a new patch version is here: https://github.com/MariaDB/server/commit/5102d86beae6b7464b1271081f8003ae81850a5b Please also find my reply by email. Thanks.
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Fix Version/s 11.2.1 [ 29034 ]
            Fix Version/s 11.2 [ 28603 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Description We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collations per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparison by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            h2. A new system variable @@character_set_collations
            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

            h2. Convenience syntax (postponed)
            Originally there was a proposal to add a convenience syntax, however we won't add it under terms of this task:
            {quote}
            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET CHARACTER SET COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}
            {quote}

            h2. Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            		We're planning to change default collation for Unicode character sets to uca1400_ai_ci soon.

            A hard coded change in the collation library is not desirable, and is hardly even possible.

            It should be implemented on the server (rather than collation library) level, to set default collation overrides per-session:

            - For old-to-new replication
            - Some users may still want to use the legacy xxx_general_ci instead of uca1400_ai_ci.
            - We'll likely add more UCA versions in the future, so users will also want to choose the desired default UCA version.
            - Some users will want to have accent sensitive comparison by default, i.e. uca140_as_ci instead of uca1400_ai_ci.

            h2. A new system variable @@character_set_collations
            The easiest way to solve all these problems is introducing a new system variable, in this format:
            'charset1=collation1,charset2=collation2,charset3=collation3'.
            That is, a comma separated list of charset/collation pairs.

            For example:
            {code:sql}
            SET @@character_set_collations='utf8mb4=utf8mb4_uca1400_ai_ci, utf8mb3=utf8mb4_uca1400_ai_ci';
            {code}

            The new variable will understand contextually typed collations:

            {code:sql}
            # No needs for the charset prefix in the collation name:
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci, utf8mb3=uca1400_ai_ci';
            {code}

            The new variable will be global and session. The global value should be settable in the server command line and in my.cnf.

            h2. Convenience syntax (postponed)
            Originally there was a proposal to add a convenience syntax, however we won't add it under terms of this task:
            {quote}
            Let's additionally introduce a nicer syntax to set this variable in a more readable way:

            {code:sql}
            SET CHARACTER SET COLLATION uca1400_ai_ci FOR utf8mb3, utf8mb4, ucs2, utf16, utf32;
            {code}
            {quote}

            h2. Usage example:

            {code:sql}
            SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
            CREATE OR REPLACE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
            SHOW CREATE TABLE t1;
            {code}
            {noformat}
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1 | CREATE TABLE `t1` (
              `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            Notice, the column was created with the collation utf8mb4_uca1400_ai_ci, which is the current session default collation for utf8mb4 as stated in @@character_set_collations.

            The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
            - Column collation
            - Table collation
            - Database collation
            - CHAR(expr USING csname)
            - CONVERT(expr USING csname)
            - CAST(expr AS CHAR CHARACTER SET csname)
            - '' - character string literal
            - _utf8mb3'text' - a character string literal with an introducer
            - _utf8mb3 X'61' - a character string literal with an introducer with hex notation
            - _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
            - @@collation_connection after a {{SET NAMES}} without {{COLLATE}}
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            bar Alexander Barkov made changes -

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.