[MDEV-29446] Change SHOW CREATE TABLE to display default collations Created: 2022-09-02  Updated: 2023-11-27  Resolved: 2022-09-22

Status: Closed
Project: MariaDB Server
Component/s: Character Sets
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.0

Type: Task Priority: Critical
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-19123 Change default charset from latin1 to... Open
Problem/Incident
causes MDEV-29561 SHOW CREATE TABLE produces syntactica... Closed
causes MDEV-30101 Some tests still need result updates ... Open

 Description   

Currently MariaDB reduces SHOW CREATE TABLE output: COLLATE clause is not displayed if the collation is default for its character set.

CREATE OR REPLACE TABLE t1 (
 
  a VARCHAR(10),
 
  b VARCHAR(10) CHARACTER SET ucs2
 
) CHARACTER SET utf8mb4;
 
SHOW CREATE TABLE t1;


+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                  |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` varchar(10) DEFAULT NULL,
 
  `b` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+

Notice:

  • the definition of the column `b` does not have a COLLATE clause, because it's collation is ucs2_general_ci, which is default for the character set ucs2.
  • the table option does not have a COLLATE clause, because it's collation is utf8mb4_general_ci, which is default for the chracter set utf8mb4.

Under terms of MDEV-19123, we're going to change the default collation for Unicode charcter sets to uca1400_ai_ci.
This will break dump-restore:

mariadb-dump | mariadb

e.g. utf8mb4_general_ci will be erroneously replaced to uca1400_ai_ci.

To avoid this, SHOW CREATE TABLE should always diplay collations after CHARACTER SET clause, even default ones.

 Comments   
Comment by Alexander Barkov [ 2022-09-05 ]

serg, can you please review this patch:
https://github.com/MariaDB/server/commit/68ebe0e2f5eec98c2fcf44ac6ad18524fc6e7a91

?
Thanks.

Comment by Sergei Golubchik [ 2022-09-12 ]

68ebe0e2f5e is ok to push

Comment by Alexander Barkov [ 2022-09-14 ]

Pushed to 10.4

In 10.9 we'll additionally need to fix json_table code inside a big if() condition:

          ...
 
          (!(m_explicit_cs->state & MY_CS_PRIMARY) &&
 
           (str->append(STRING_WITH_LEN(" COLLATE ")) ||
 
            str->append(&m_explicit_cs->coll_name))))) ||
 
          ...

Comment by Alexander Barkov [ 2022-09-22 ]

Reopening the issue as 10.9 still needs an additional fix into the JSON_TABLE related code mentioned above.

Generated at Thu Feb 08 10:08:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.