a-journey-with-mysql-and-variables

MySQL is one of the most used relational database server. I assume you have basic idea about it. My main focus in this article will be about server variables and changing it. There are also some other things to go through.

Topics to cover

  1. How to install latest stable MySQL Community Version in Ubuntu?
  2. How to check variables of MySQL?
  3. How to insert Bangla and Emoji in database?

Install MySQL

To install MySQL Community Version from their website, I suggest to check out this page: https://dev.mysql.com/downloads/

I am here to show you how you can install it in Debian or Ubuntu.

First of all download installer deb from: https://dev.mysql.com/downloads/repo/apt/

Install it by:

$ sudo dpkg -i mysql-apt-config_*.deb

By default it will show you above view. To change the mysql-5.7, press enter and select. To proceed, scroll to the bottom OK and press enter.

It will install the installer. You need to update and install server now.

$ apt update; apt install mysql-server

Now need to set root password.

$ sudo mysql_secure_installation
  • Set root password
  • Remove anonymous user
  • Remove test database
  • Flush Privilege

To login:

$ mysql -u root -p

To check the users list:

$ mysql>  SELECT user,authentication_string,plugin,host FROM mysql.user;
+---------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+---------------+-------------------------------------------+-----------------------+-----------+
| root | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
+---------------+-------------------------------------------+-----------------------+-----------+
3 rows in set (0.00 sec)

Check Variables

Version:

$ mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.24 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.24 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

Collation:

$ mysql> SHOW VARIABLES LIKE  '%collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Character Set:

$ mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| 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 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

SQL Mode:

mysql> SHOW VARIABLES LIKE  '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Insert Bangla & Emoji

Create a database, table to test.

$ mysql> create database bookreview;
$ mysql> use bookreview;
$ mysql> CREATE TABLE review (
id INT(6) AUTO_INCREMENT PRIMARY KEY,
book_name VARCHAR(30) NOT NULL,
reviewer VARCHAR(30) NOT NULL,
review VARCHAR(100) NOT NULL
)

Default collation and character-set from above.

Normal values:

$ mysql> INSERT INTO review (book_name, reviewer, review) VALUES (
-> "Rich Dad Poor Dad",
-> "Fahad Ahammed",
-> "A must read book for anyone."
-> );
Query OK, 1 row affected (0.06 sec)
$ mysql> INSERT INTO review (book_name, reviewer, review) VALUES (
-> "Rich Dad Poor Dad",
-> "Color Abuser",
-> "This is a normal book with very normal approach to the context."
-> );
Query OK, 1 row affected (0.08 sec)
$ mysql>

Bangla and Emoji:

mysql> INSERT INTO review (book_name, reviewer, review) VALUES (
-> "Rich Dad Poor Dad",
-> "Kusum of Rokomari",
-> "A fool will read this. 😜😀😊😃"
-> );
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x9C\xF0\x9F…' for column 'review' at row 1
mysql> INSERT INTO review (book_name, reviewer, review) VALUES (
-> "Rich Dad Poor Dad",
-> "Sad Abul",
-> "বুঝিনা, এই ফালতু বই ও পড়া লাগে?"
-> );
ERROR 1366 (HY000): Incorrect string value: '\xE0\xA6\xAC\xE0\xA7\x81…' for column 'review' at row 1
mysql>

So, these errors are actually database character set and collations issue. We can change the database character set and collation or set when create.

Modify:

$ mysql> ALTER DATABASE bookreview CHARACTER SET utf8 COLLATE utf8_unicode_ci;

$ mysql> ALTER TABLE review CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

When create database:

$ mysql> CREATE DATABASE bookreview CHARACTER SET utf8 COLLATE utf8_unicode_ci;

After modifying or creating with utf8_unicode_ci as collation and utf8 as characterset, we should be able to insert Bangla in the database.

$ mysql> SHOW VARIABLES LIKE  '%database%';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| character_set_database | utf8 |
| collation_database | utf8_unicode_ci |
| skip_show_database | OFF |
+------------------------+-----------------+
3 rows in set (0.00 sec)
$ mysql> SHOW VARIABLES LIKE  '%colla%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_unicode_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)
$ mysql> INSERT INTO review (book_name, reviewer, review) VALUES ( "Rich Dad Poor Dad", "Sad Abul", "বুঝিনা, এই ফালতু বই ও পড়া লাগে?" );
Query OK, 1 row affected (0.18 sec)
$ mysql> select * from review;
+----+-------------------+---------------+-------------------------------------------------------------------------------+
| id | book_name | reviewer | review |
+----+-------------------+---------------+-------------------------------------------------------------------------------+
| 1 | Rich Dad Poor Dad | Fahad Ahammed | A must read book for anyone. |
| 2 | Rich Dad Poor Dad | Color Abuser | This is a normal book with very normal approach to the context. |
| 3 | Rich Dad Poor Dad | Sad Abul | বুঝিনা, এই ফালতু বই ও পড়া লাগে? |
+----+-------------------+---------------+-------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

What about emoticons?

$ mysql> INSERT INTO review (book_name, reviewer, review) VALUES (
"Rich Dad Poor Dad",
"Fool Rabbit",
"ভালো বই। 😜😀😊😃"
);
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x9C\xF0\x9F…' for column 'review' at row 1

It doesn’t work. 🙁

We need another collation to do that.

$ mysql> ALTER DATABASE bookreview CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
$ mysql> ALTER TABLE review CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

But changing only these will not work. You need several of them to work. character_set_client = utf8 to utf8mb4

But we can avoid all of this by just changing the client and server level global collation and character-set.

You need to make sure that you have two files in /etc/mysql/conf.d/

  1. mysql.cnf
  2. mysqld.cnf

Make sure they has following lines.

mysql.cnf

$ /etc/mysql/conf.d# cat mysql.cnf 
[mysql]
default-character-set = utf8mb4

mysqld.cnf

$ /etc/mysql/conf.d# cat mysqld.cnf 
[mysqld]
# Character_Set and Collation
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8mb4

Then a restart is required to work it out instantly.

Conclusion

This article is an ongoing process. I will update it with some newly found tweaks to make it enrich and useful for future reference and usage.

Previous ArticleNext Article
Fahad Ahammed is a System Administrator and DevOps in Software Department of OWSL. As a Linux enthusiast he breathes in server consoles or terminals. You can have a look to his website here: https://fahadahammed.com

Leave a Reply

Your email address will not be published. Required fields are marked *