I tested with a Debian Testing VM: Linux testing 5.15.0-2-amd64 #1 SMP Debian 5.15.5-2 (2021-12-18) x86_64 GNU/Linux
MariaDB [test]> CREATE OR REPLACE TABLE t1 (a CHAR(10)) CHARACTER SET utf8mb4 COLLATE DEFAULT;
|
Query OK, 0 rows affected (0.005 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> SHOW CREATE TABLE t1;
|
+-------+-----------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` char(10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
|
+-------+-----------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
The result seems ok to me.
Maybe you have different default settings (database default character set/defautl collation)?
What is the result of
select * from information_schema.schemata;
|
MariaDB [test]> status
|
--------------
|
mysql Ver 15.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
|
|
Connection id: 31
|
Current database: test
|
Current user: root@localhost
|
SSL: Not in use
|
Current pager: stdout
|
Using outfile: ''
|
Using delimiter: ;
|
Server: MariaDB
|
Server version: 10.6.7-MariaDB-3 Debian buildd-unstable
|
Protocol version: 10
|
Connection: Localhost via UNIX socket
|
Server characterset: utf8mb4
|
Db characterset: utf8mb4
|
Client characterset: utf8mb3
|
Conn. characterset: utf8mb3
|
UNIX socket: /run/mysqld/mysqld.sock
|
Uptime: 1 min 11 sec
|
Update: I am able to reproduce using a database test2 with other charset and collation:
select * from information_schema.schemata;
|
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
|
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT |
|
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
|
| def | information_schema | utf8mb3 | utf8mb3_general_ci | NULL | |
|
| def | test2 | latin1 | latin1_swedish_ci | NULL | |
|
| def | sys | utf8mb3 | utf8mb3_general_ci | NULL | |
|
| def | performance_schema | utf8mb3 | utf8mb3_general_ci | NULL | |
|
| def | mysql | utf8mb4 | utf8mb4_general_ci | NULL | |
|
| def | test | utf8mb4 | utf8mb4_general_ci | NULL | |
|
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
|
6 rows in set (0.001 sec)
|
|
MariaDB [test]> use test2
|
Database changed
|
MariaDB [test2]> CREATE OR REPLACE TABLE t1 (a CHAR(10)) CHARACTER SET utf8mb4 COLLATE DEFAULT;
|
Query OK, 0 rows affected (0.006 sec)
|
|
MariaDB [test2]> show create table t1;
|
+-------+----------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` char(10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+----------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
So it depends on the database settings and the chosen charset/collation for the table creation.
So maybe if charset utf8mb4 and the default collation is incompatible to utf8mb4 there is no fallback to fix this?
Or should the query be denied with an error message?
How behaves Mysql in this cases? (I wish these behave the same on both.)
I tested with a Debian Testing VM: Linux testing 5.15.0-2-amd64 #1 SMP Debian 5.15.5-2 (2021-12-18) x86_64 GNU/Linux
MariaDB [test]>
The result seems ok to me.
Maybe you have different default settings (database default character set/defautl collation)?
What is the result of
MariaDB [test]> status
--------------
mysql Ver 15.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
Connection id: 31
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.6.7-MariaDB-3 Debian buildd-unstable
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb3
Conn. characterset: utf8mb3
UNIX socket: /run/mysqld/mysqld.sock
Uptime: 1 min 11 sec
Update: I am able to reproduce using a database test2 with other charset and collation:
select * from information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT |
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
| def | information_schema | utf8mb3 | utf8mb3_general_ci | NULL | |
| def | test2 | latin1 | latin1_swedish_ci | NULL | |
| def | sys | utf8mb3 | utf8mb3_general_ci | NULL | |
| def | performance_schema | utf8mb3 | utf8mb3_general_ci | NULL | |
| def | mysql | utf8mb4 | utf8mb4_general_ci | NULL | |
| def | test | utf8mb4 | utf8mb4_general_ci | NULL | |
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
6 rows in set (0.001 sec)
MariaDB [test]> use test2
Database changed
MariaDB [test2]> CREATE OR REPLACE TABLE t1 (a CHAR(10)) CHARACTER SET utf8mb4 COLLATE DEFAULT;
Query OK, 0 rows affected (0.006 sec)
MariaDB [test2]> show create table t1;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
So it depends on the database settings and the chosen charset/collation for the table creation.
So maybe if charset utf8mb4 and the default collation is incompatible to utf8mb4 there is no fallback to fix this?
Or should the query be denied with an error message?
How behaves Mysql in this cases? (I wish these behave the same on both.)