IntelliJ products do not yet support MySQL’s utf8mb4 character set encodings. The […]

-->

When using variables inside SQL scripts within IntelliJ products (e.g. DataGrip), certain queries will not work because the encodings of the IntelliJ client and the server missmatch. This occurs for instance when you compare variables. A typical error message looks like this:

IntelliJ products do not yet support MySQL’s utf8mb4 character set encodings. The problem occurs when using variables in queries. Per default. IntelliJ uses a UTF-8 encoding for the connection. When you use utf8mb4 as the database default character set, then variables will be encoded in UTF-8 while the database content remailns in utf8mb4. It is not possible to provide the character set encodings to the IntelliJ settings, as it will refuse to connect.

Check your server settings using the MySQL client:

1

2

3

4

5

6

7

8

9

10

11

12

13

MySQL[cropster_research]>show variables like'%char%';

+--------------------------+----------------------------+

|Variable_name|Value|

+--------------------------+----------------------------+

|character_set_client|utf8mb4|

|character_set_connection|utf8mb4|

|character_set_database|utf8mb4|

|character_set_filesystem|binary|

|character_set_results|utf8|

|character_set_server|utf8mb4|

|character_set_system|utf8|

|character_sets_dir|/usr/share/mysql/charsets/|

+--------------------------+----------------------------+

This seems correct, but when you connect with the IntelliJ client, you will get wrong results when you use variables. Until the products support utf8mb4, you would need to add the following settings to the script in order to force the right settings.

Replication is an important concept for improving database performance and security. In this blog post, I would like to demonstrate how the consistency between a MySQL master and a slave can be verified. We will create two Docker containers, one for the master one for the slave.

Installing the Percona Toolkit

The Percona Toolkit is a collection of useful utilities, which can be obained for free from the company’s portal. The following commands install the prerequisits, download the package and eventually the package.

Now log into the slave container and add the connection details for the master:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

## Connect to the MySQL Slave instance

$mysql-uroot-h172.17.0.3

### Setup the slave

mysql>CHANGE MASTER TO

MASTER_HOST='172.17.0.2',

MASTER_PORT=3306,

MASTER_USER='replication',

MASTER_PASSWORD='SLAVE-SECRET',

MASTER_LOG_FILE='mysqld-bin.000002',

MASTER_LOG_POS=346;

Query OK,0rows affected,2warnings(0,05sec)

### Start and check

mysql>start slave;

Query OK,0rows affected(0,01sec)

mysql>show slave status\G

***************************1.row ***************************

Slave_IO_State:Waiting formaster tosend event

Master_Host:172.17.0.2

Master_User:percona

Master_Port:3306

Connect_Retry:60

Master_Log_File:mysqld-bin.000002

Read_Master_Log_Pos:346

Relay_Log_File:mysqld-relay-bin.000002

Relay_Log_Pos:284

Relay_Master_Log_File:mysqld-bin.000002

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Now our simple slave setup is running.

Get some test data

Lets download the Sakila test database and import it into the master. It will be replicated immediately.

1

2

3

4

5

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

~/Docker-Projects$tar xvfz sakila-db.tar.gz

mysql-uroot-h172.17.0.2<sakila-db/sakila-schema.sql

mysql-uroot-h172.17.0.2<sakila-db/sakila-data.sql

Verify that the data is on the slave as well:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql-uroot-h172.17.0.3-e"USE sakila;SHOW TABLES;"

+----------------------------+

|Tables_in_sakila|

+----------------------------+

|actor|

|actor_info|

|address|

|category|

|city|

|country|

|customer|

...

|store|

+----------------------------+

After our setup is completed, we can proceed with Percona pt-table checksum.

Percona pt-table-checksum

The Percona pt-table-checksum tool requires the connection information of the master and the slave in a specific format. This is called the DSN (data source name), which is a coma separated string. We can store this information in a dedicated database called percona in a table called dsns. We create this table on the master. Note that the data gets replicated to the slave within the blink of an eye.

1

2

3

4

5

6

7

8

CREATE DATABASE percona;

USEpercona;

CREATE TABLE`DSN-Table`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`dsn`varchar(255)NOTNULL,

PRIMARY KEY(`id`)

);

The next step involves creating permissions on the slave and the master!

The percona user is needed to run the script. Note that the IP address is this time from the (Docker) host, having the IP 172.17.0.1 by default. In real world scenarios, this script would either be run on the master or on the slave directly.

Now we need to add the information about the slave to the table we created. The Percona tool could also read this from the process list, but it is more reliable if we add the information ourselves. To do so, we add a record to the table we just created, which describes the slave DSN:

The result shows a difference in the MySQL internal table for permissions. This is obviously not what we are interested in, as permissions are individual to a host. So we rather exclude the MySQL internal database and also the percona database, because it is not what we are interested in. Also in order to test it the tool works, we delete the last five category assignments from the table with
mysql-uroot-h172.17.0.3-e"DELETE FROM sakila.film_category WHERE film_id > 995;" and update a row in the city table with

1

mysql-uroot-h172.17.0.3-e"update sakila.city SET city='Innsbruck' WHERE city_id=590;"

You see that there is a difference in the tables sakila.city and in the table sakila.film_category. The tool does not report the actual number of differences, but rather the number of different chunks. To get the actual differences, we need to use a different tool, which utilises the checksum table that the previous step created.

Show the differences with pt-tabel-sync

The pt-table-sync tool is the counter part for the pt-table-checksum util. It can print or even replay the SQL statements that would render the slave the same state again to be in sync with the master. We can run a dry-run first, as the tool is potentially dangerous.

The command shows how we can rename back from Innsbruck to Yuncheng again and also provides the INSERT statements to get the deleted records back.When we replace –print with –execute, the data gets written to the master and replicated to the slave. To allow this, we need to set the permissions on the master

This error indicates that updating the city table has consequences, because it is a FK to child tables. In this example, we are bold and ignore this warning. This is absolutely not recommended for real world scenarios.

Hibernate Search is a very convenient way for storing database content in a Lucine index and add fulltext search capabilities to data driven projects simply by annotating classes. It can be easily integrated into Spring Boot applications and as long as only the basic features are used, it works out of the box. The fun starts when the Autoconfiguration cannot find out how to properly configure things automatically, then it gets tricky quite quickly. Of course this is natural behaviour, but one gets spoiled quite quickly.

The current version of Spring Boot is 1.5.2. This version uses Hibernate ORM 5.0. The latest stable Hibernate Search versions are 5.6.1.Final and 5.7.0.Final, which in in contrast require Hibernate ORM 5.1 and 5.2 respectively. Also you need Java 8 now. For this reason if you need the latest Spring Search features in combination with Spring Boot, you need to adapt the dependencies as follows:

Note that the Hibernate Entity Manager needs to be excluded, because it has been integrated into the core in the new Hibernate version. Details are given in the Spring Boot documentation.

Enforcing the Dependencies to be Loaded in the Correct Sequence

As written earlier, Spring Boot takes care of a lot of configurations for us. Most of the time, this works perfectly and reduces the pain for configuring a new application manually. In some particular cases, Spring cannot figure out that there exists a dependency between different services, which needs to be resolved in a specified order. A typical use case is the implementation of FieldBridges for Hibernate Search. FieldBrides translate between the actual Object from the Java World and the representation of such an object in the Lucene index. Typically an EnumBridge is used for indexing Enums, which are often used for realizing internationalization (I18n).

When the Lucene Index is created, Hibernate checks if Enum fields need to be indexed and if there exist Bridge that converts between the object and the actual record in the Index. The problem here is that Hibernate JPA is loaded at a very early stage in the Spring Boot startup proces. The problem only arises if the BridgeClass utilises @Autowired fields which get injected. Typically, these fields would get injected when the AnnotationBeanConfigurerAspect bean is loaded. Hibernate creates the session with the session factory auto configuration before the spring configurer aspect bean was loaded. So the FieldBridge used by Hibernate during the initialization of the index does not have the service injected yet, causing a nasty Null Pointer Exception.

Example EnumBridge

The following EnumBridge example utilises an injected Service, which needs to be available before Hibernate starts. If not taken care of, this causes a Null Pointer Exception.

Java

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

@Configurable

publicclassHibernateSearchEnumBridgeExampleextendsEnumBridge

{

privateI18nMessageService i18nMessageService;

@Autowired

@Required

publicvoidsetI18nMessageService(I18nMessageService service){

this.i18nMessageService=service;

}

@Override

publicStringobjectToString(Objectobject)

{

returni18nMessageService.getMessageForEnum(object);

}

@Override

publicEnum<?extendsEnum>stringToObject(Stringname)

{

returnEnum.valueOf(name);

}

@Override

publicvoidsetAppliedOnType(Class<?>returnType)

{

super.setAppliedOnType(returnType);

}

}

Enforce Loading the Aspect Configurer Before the Session Factory

In order to enforce that the AnnotationBeanConfigurerAspect is created before the Hibernate Session Factory is created, we simply implement our own HibernateJpaAutoConfiguration by extension and add the AnnotationBeanConfigurerAspect to the constructor. Spring Boot now knows that it needs to instantiate the AnnotationBeanConfigurerAspect before it can instantiate the HibernateJpaAutoConfiguration and we then have wired Beans ready for the consumption of the bridge. I found the correct hint here and here.

As it has turned out, using @DependsOn annotations did not work and also @Ordering the precedence of the Beans was not suffucient. With this little hack, we can ensure the correct sequence of initialization.

Installing MySQL via apt-get is a simple task, but the migration between different MySQL versions requires planning and testing. Thus installing one central instance of the database system might not be suitable, when the version of MySQL or project specific settings should be switched quickly without interfering with other applications. Using one central instance can quickly become cumbersome. In this article, I will describe how any number of MySQL instances can be stored and executed from within a user’s home directory.

Adapting MySQL Data an Log File Locations

Some scenarios might require to run several MySQL instances at once, other scenarios cover sensitive data, where we do not want MySQL to write any data on non-encrypted partitions. This is especially true for devices which can get easily stolen, for instance laptops. If you use a laptop for developing your applications from time to time, chances are good that you need to store sensitive data in a database, but need to make sure that the data is encrypted when at rest. The data stored in a database needs to be protected when at rest.

This can be solved with full disk encryption, but this technique has several disadvantages. First of all, full disk encryption only utilises one password. This entails, that several users who utilise a device need to share one password, which reduces the reliability of this approach. Also when the system needs to be rebooted, full disk encryption can become an obstacle, which increases the complexity further.

Way easier to use is the transparent home directory encryption, which can be selected during many modern Linux setup procedures out of the box. We will use this encryption type for this article, as it is reasonable secure and easy to setup. Our goal is to store all MySQL related data in the home directory and run MySQL with normal user privileges.

Creating the Directory Structure

The first step is creating a directory structure for storing the data. In this example, the user name is stefan, please adapt to your needs.

1

2

3

4

5

6

$cd~

$mkdir-pMySQL-5.6-Local/MySQL-5.6-Data# Data storage

$mkdir-pMySQL-5.6-Local/MySQL-5.6-Conf# Config file

$mkdir-pMySQL-5.6-Local/MySQL-5.6-Log# Log file

$mkdir-pMySQL-5.6-Local/MySQL-5.6-Tmp# Tmp

$mkdir-pMySQL-5.6-Local/MySQL-5.6-Scripts# Start and stop scripts

Create a Configuration File

Make sure to use absolute paths and utilise the directories we created before. Store this file in
MySQL-5.6-Local/MySQL-5.6-Conf/my-5.6.cnf. The configuration is pretty self explanatory.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

[client]

port=3306

socket=/home/stefan/MySQL-5.6-Local/mysqld-5.6.sock

[mysqld_safe]

pid-file=/home/stefan/MySQL-5.6-Local/mysqld-5.6.pid

nice=0

log_error=/home/stefan/MySQL-5.6-Local/MySQL-5.6-Log/mysql_error.log

[mysqld]

user=stefan

pid-file=/home/stefan/MySQL-5.6-Local/mysqld-5.6.pid

socket=/home/stefan/MySQL-5.6-Local/mysqld-5.6.sock

port=3306

basedir=/usr

datadir=/home/stefan/MySQL-5.6-Local/MySQL-5.6-Data

tmpdir=/home/stefan/MySQL-5.6-Local/MySQL-5.6-Tmp

log_error=/home/stefan/MySQL-5.6-Local/MySQL-5.6-Log/mysql_error.log

bind-address=127.0.0.1

Stop the Running MySQL Instance

If you already have a running MySQL instance, make sure to shut it down. You can also disable MySQL from starting automatically.

1

2

sudo service msql stop

echo"manual"|sudo tee/etc/init/mysql.override

Setting up Apparmor

Apparmor protects sensitive applications by defining in which directory they might write. We need to update this configuration to suit our needs. We need to make sure that the global configuration file for the central MySQL instance also includes an additional local information. Edit this file first:
/etc/apparmor.d/usr.sbin.mysqld and make sure that the reference for the local file is not commented out.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

# vim:syntax=apparmor

# Last Modified: Fri Feb 28 18:06:30 2014

#include <tunables/global>

/usr/sbin/mysqld{

#include <abstractions/base>

#include <abstractions/nameservice>

#include <abstractions/user-tmp>

# .... shortened

# Include local configuration!!

include<local/usr.sbin.mysqld>

}

Now we need to add the directories in stean’s home directory to the local file by editing
/etc/apparmor.d/local/usr.sbin.mysqld .

1

2

3

4

5

6

7

# Site-specific additions and overrides for usr.sbin.mysqld.

# For more details, please see /etc/apparmor.d/local/README.

# Site-specific additions and overrides for usr.sbin.mysqld.

# For more details, please see /etc/apparmor.d/local/README.

/home/stefan/MySQL-5.6-Local/r,

/home/stefan/MySQL-5.6-Local/**rwk,

An incorrect Apparmor configuration is often the cause of permission errors, which can be a pain. Make sure to reload the the Apparmor service and if you struggle with it, consider to disable it temporarily and check if the rest works. Do not forget to turn it on again.

1

2

sudo service apparmor reload# reload

sudo service apparmor teardown# disable for testing

Initialize the Local MySQL Instance

Now it is time to initialize the MySQL instance. In this step, MySQL creates all the files it needs in the data directory. It is important that the data directory is empty, when you initiate the following commands.

Conclusion

The technique described above allows to install and run multiple MySQL instances from within the user’s home directory. The MySQL instances run with user privileges and can utilise dedicated data and log file directories. As the all data is stored within the $HOME directory, we can easily apply transparent encryption to protect data at rest.

In a recent article, I described how to setup a basic MySQL Cluster with two data nodes and a combined SQL and management node. In this article, I am going to highlight a hew more things and we are going to adapt the cluster a little bit.

Using Hostnames

For making our lives easier, we can use hostnames which are easier to remember than IP addresses. Hostnames can be specified for each VM in the file
/etc/hosts . For each request to the hostname, the operating system will lookup the corresponding IP address. We need to change this file on all three nodes to the following example:

1

2

3

4

5

6

7

8

9

10

11

root@node1:~# cat /etc/hosts

127.0.0.1localhost

192.168.0.81node1

192.168.0.82node2

192.168.0.83node3

# The following lines are desirable for IPv6 capable hosts

::1localhost ip6-localhost ip6-loopback

ff02::1ip6-allnodes

ff02::2ip6-allrouters

Now we can lookup IP addresses by the associated hostname.

Adapting Node1

The management and the SQL node are both running in the same VM. Now that we introduced the hostnames for all machines, we need to reconfigure the cluster manager and the MySQL Server. The cluster configuration is stored in the file
/opt/mysql/server-5.7/mysql-cluster/config.ini . Replace all the IP addresses with the corresponding host name.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

root@node1:~# cat /opt/mysql/server-5.7/mysql-cluster/config.ini

[ndb_mgmd]

NodeId=1

# Management process options:

hostname=node1# Hostname of the manager

datadir=/opt/mysql/server-5.7/mysql-cluster/cluster-data# Directory for the log files

[ndbd]

NodeId=2

hostname=node2# Hostname of the first data node

datadir=/usr/local/mysql/data# Remote directory for the data files

[ndbd]

NodeId=3

hostname=node3

datadir=/usr/local/mysql/data# Remote directory for the data files

[mysqld]

# SQL node options:

NodeId=4

hostname=node1

For reconfiguring the SQL node, we need to adapt the file
/etc/my.cnf . We need to replace all IP addresses in this file too.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

root@node1:~# cat /etc/my.cnf

[mysqld_safe]

socket=/tmp/mysql.sock

nice=0

[mysqld]

#

# * Basic Settings

#

user=mysql

pid-file=/tmp/mysql.pid

socket=/tmp/mysql.sock

port=3306

basedir=/opt/mysql/server-5.7

datadir=/opt/mysql/server-5.7/data

tmpdir=/tmp

skip-external-locking

bind-address=node1

key_buffer_size=16M

max_allowed_packet=16M

thread_stack=192K

thread_cache_size=8

query_cache_limit=1M

query_cache_size=16M

log_error=/var/log/mysql/error.log

expire_logs_days=10

max_binlog_size=100M

#

# ssl-ca=/etc/mysql/cacert.pem

# ssl-cert=/etc/mysql/server-cert.pem

# ssl-key=/etc/mysql/server-key.pem

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

ndbcluster# run NDB storage engine

ndb-connectstring=node1

[mysql_cluster]

# Options for MySQL Cluster processes:

ndb-connectstring=node1

If done, stop the cluster and the SQL node like this:

1

2

3

4

5

6

root@node1:~# /opt/mysql/server-5.7/bin/ndb_mgm -e shutdown

Connected toManagement Server at:node1:1186

3NDB Cluster node(s)have shutdown.

Disconnecting toallow management server toshutdown.

root@node1:~# service mysql stop

Adapt the Data Nodes

Replacing the IP adresses with hostnames is pretty straightforward. Change the IP address on each node with the host name in the file
/etc/my.cnf :

1

2

3

4

5

6

[mysqld]

# Options for mysqld process:

ndbcluster

[mysql_cluster]

ndb-connectstring=node1

Startup the Cluster

Now that we have exchanged all IP addresses for the hostname, we can restart the cluster as follows:

Encrypt Data in Transit

For improving the security of the cluster, we can encrypt the traffic which is exchanged between the MySQL node and the MySQL client. Please note that this encryption method is not specific for the cluster, but rather encrypts the traffic between the MySQL client and the MySQL server. You can use this method also for standard MySQL server setups. It does not cover encryption between the data nodes. By design, data nodes are intended to be run in a private network, not via the open Interwebs.

Create a CA Authority Key and Certificate

To do so, we work on node 1. In the first step we create a CA authority and a CA certificate.

What about the Data Nodes?

So far we encrypted only the traffic between the MySQL client and the MySQL server. This configuration does not differ from a single server or replication setup and does not include the traffic between the data nodes, which is not encrypted at all. MySQL cluster has been designed to be run in a controlled environment within high speed network locally. The MySQL Cluster FAQ states:

It is very unlikely that a cluster would perform reliably under such conditions, as NDB Cluster was designed and implemented with the assumption that it would be run under conditions guaranteeing dedicated high-speed connectivity such as that found in a LAN setting using 100 Mbps or gigabit Ethernet—preferably the latter. We neither test nor warrant its performance using anything slower than this.

Also, it is extremely important to keep in mind that communications between the nodes in an NDB Cluster are not secure; they are neither encrypted nor safeguarded by any other protective mechanism. The most secure configuration for a cluster is in a private network behind a firewall, with no direct access to any Cluster data or management nodes from outside.