[MDEV-8942] utf8 charater set doesn't affect table creation. Created: 2015-10-14  Updated: 2015-11-17  Resolved: 2015-11-17

Status: Closed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 10.1.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: passion053 Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: need_feedback
Environment:

CentOS 7.1


 Description   

( First of all, sorry for my poor english. My mother tongue is not english. )

1. Add below statement to /etc/my.cnf.d/server.cnf

character-set-server = utf8
collation-server = utf8_general_ci

2. Restart DB and check character set.

3. Create new database and move to that database.
(e.g. CREATE DATABASE testDB; )
(e.g. USE testDB; )

4. Create table without 'CHARACTER SET' option.
(e.g. CREATE TABLE testTable; )

5. Check 'SHOW CREATE TABLE' statement result.
( e.g. SHOW CREATE TABLE testTable; )
You can see 'character_set_database' value is still latin1

In MariaDB 10.0.21, Once I set 'character-set-server = utf8', all tables are created with utf8 even though I didn't write the 'character_set' option in 'CREATE TABLE' statement. And I think this is normal.

Please check.

Thank you.

 Comments   
Comment by Elena Stepanova [ 2015-10-19 ]

When I do exactly what you described, I get the expected result – the table is created with character set UTF8, see the output below. 

# add options to the config file 
 
 
$ cat /etc/my.cnf.d/server.cnf 
#
 
# These groups are read by MariaDB server.
 
# Use it for options that only the server (but not clients) should see
 
#
 
# See the examples of server my.cnf files in /usr/share/mysql/
 
#
 
 
 
# this is read by the standalone daemon and embedded servers
 
[server]
 
 
 
character-set-server = utf8
 
collation-server = utf8_general_ci
 
 
 
# this is only for the mysqld standalone daemon
 
[mysqld]
 
 
 
#
 
# * Galera-related settings
 
#
 
[galera]
 
# Mandatory settings
 
#wsrep_provider=
 
#wsrep_cluster_address=
 
#binlog_format=row
 
#default_storage_engine=InnoDB
 
#innodb_autoinc_lock_mode=2
 
#bind-address=0.0.0.0
 
#
 
# Optional setting
 
#wsrep_slave_threads=1
 
#innodb_flush_log_at_trx_commit=0
 
 
 
# this is only for embedded server
 
[embedded]
 
 
 
# This group is only read by MariaDB servers, not by MySQL.
 
# If you use the same .cnf file for MySQL and MariaDB,
 
# you can put MariaDB-only options here
 
[mariadb]
 
 
 
# This group is only read by MariaDB-10.1 servers.
 
# If you use the same .cnf file for MariaDB of different versions,
 
# use this group for options that older servers don't understand
 
[mariadb-10.1]
 
 
 
# Start the server
 
 
 
$ sudo /etc/init.d/mysql start
 
Starting MySQL. SUCCESS! 
 
 
MariaDB [(none)]> select @@version;
 
+----------------+
 
| @@version      |
 
+----------------+
 
| 10.1.7-MariaDB |
 
+----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> show variables like '%char%';
 
+--------------------------+----------------------------+
 
| Variable_name            | Value                      |
 
+--------------------------+----------------------------+
 
| character_set_client     | utf8                       |
 
| character_set_connection | utf8                       |
 
| character_set_database   | utf8                       |
 
| character_set_filesystem | binary                     |
 
| character_set_results    | utf8                       |
 
| character_set_server     | utf8                       |
 
| character_set_system     | utf8                       |
 
| character_sets_dir       | /usr/share/mysql/charsets/ |
 
+--------------------------+----------------------------+
 
8 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> create database testDB;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [(none)]> use testDB
 
Database changed
 
MariaDB [testDB]> create table testTable (i int);
 
Query OK, 0 rows affected (0.09 sec)
 
 
 
MariaDB [testDB]> show create table testTable;
 
+-----------+--------------------------------------------------------------------------------------------+
 
| Table     | Create Table                                                                               |
 
+-----------+--------------------------------------------------------------------------------------------+
 
| testTable | CREATE TABLE `testTable` (
 
  `i` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 
+-----------+--------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

However, it's easy to make a mistake while doing this test, please check that's not what happened in your case.
Please note that new tables are created not with character_set_server from your configuration; new tables are created with the default character set of the schema in which they are created. It means that if you ran the test slightly differently, you would receive exactly the result that you are describing.

For example:

# Run server with the default character set / collation 
 
 
MariaDB [testDB]> show variables like '%character_set\_%';
 
+--------------------------+-------------------------------------+
 
| Variable_name            | Value                               |
 
+--------------------------+-------------------------------------+
 
| character_set_client     | utf8                                |
 
| character_set_connection | utf8                                |
 
| character_set_database   | latin1                              |
 
| character_set_filesystem | binary                              |
 
| character_set_results    | utf8                                |
 
| character_set_server     | latin1                              |
 
| character_set_system     | utf8                                |
 
+--------------------------+-------------------------------------+
 

MariaDB [test]> create database testDB;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [(none)]> show create database testDB;
 
+----------+-------------------------------------------------------------------+
 
| Database | Create Database                                                   |
 
+----------+-------------------------------------------------------------------+
 
| testDB   | CREATE DATABASE `testDB` /*!40100 DEFAULT CHARACTER SET latin1 */ |
 
+----------+-------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 

# NOW restart the server with new options character-set-server=utf8, collation-server=utf8_general_ci
 
 
 
# Connect to the server with the default database testDB
 
$ mysql -uroot testDB
 
 
 
MariaDB [testDB]> show variables like '%character_set\_%';
 
+--------------------------+--------+
 
| Variable_name            | Value  |
 
+--------------------------+--------+
 
| character_set_client     | utf8   |
 
| character_set_connection | utf8   |
 
| character_set_database   | latin1 |
 
| character_set_filesystem | binary |
 
| character_set_results    | utf8   |
 
| character_set_server     | utf8   |
 
| character_set_system     | utf8   |
 
+--------------------------+--------+
 
7 rows in set (0.01 sec)

Since your default database testDB was created with character set latin1, your character_set_database shows latin1, and all tables in this schema will be created with character set latin1.

MariaDB [testDB]> show create table testTable;
 
+-----------+----------------------------------------------------------------------------------------------+
 
| Table     | Create Table                                                                                 |
 
+-----------+----------------------------------------------------------------------------------------------+
 
| testTable | CREATE TABLE `testTable` (
 
  `i` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-----------+----------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

But if you now create a new database, it will be created with the new default character set, and tables which you later create inside that new schema will inherit the new character set:

MariaDB [testDB]> create database testDB2;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [testDB]> use testDB2;
 
Database changed
 
MariaDB [testDB2]> show create database testDB2;
 
+----------+------------------------------------------------------------------+
 
| Database | Create Database                                                  |
 
+----------+------------------------------------------------------------------+
 
| testDB2  | CREATE DATABASE `testDB2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
 
+----------+------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [testDB2]> show variables like '%character_set\_%';
 
+--------------------------+--------+
 
| Variable_name            | Value  |
 
+--------------------------+--------+
 
| character_set_client     | utf8   |
 
| character_set_connection | utf8   |
 
| character_set_database   | utf8   |
 
| character_set_filesystem | binary |
 
| character_set_results    | utf8   |
 
| character_set_server     | utf8   |
 
| character_set_system     | utf8   |
 
+--------------------------+--------+
 
7 rows in set (0.00 sec)
 
 
 
MariaDB [testDB2]> create table testTable2 (i int);
 
Query OK, 0 rows affected (0.67 sec)
 
 
 
MariaDB [testDB2]> show create table testTable2;
 
+------------+---------------------------------------------------------------------------------------------+
 
| Table      | Create Table                                                                                |
 
+------------+---------------------------------------------------------------------------------------------+
 
| testTable2 | CREATE TABLE `testTable2` (
 
  `i` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 
+------------+---------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

Please let me know if it explains your observations.

Comment by Elena Stepanova [ 2015-11-17 ]

Closing for now as "can't reproduce". If you have more information and still think there is a bug, please comment to re-open.

Generated at Thu Feb 08 07:30:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.