Illegal mix of collations: IntelliJ and UTF8mb4

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:

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.

Continue reading


Flashing a OnePlus One from CM to LineageOS

As Cyanogen Inc closed down its operations in December 2016, CyanogenMod was affected too and my OnePlus One (OPO) did not receive updates anymore. This is not ideal, as new and old bugs have will remain. For instance did my phone often not reconnect to the 4G network, when a wifi connection was lost, This was very annoying. For this reason, if was about time to upgrade to a new OS: LineageOS.

LineageOS

LineageOS is a fork of CyanogenMod and contunes the quite successful project for our benefit. LineageOS 14 is compatible with Android 7.1 and very easy to install. These are the steps which I had to follow.

How to Upgrade

  • Create a backup with Helios. Use the Chrome Helium app if the app on your mobile phone refuses to start.
  • Download and install adb
  • Download the TWRP custom ROM: https://twrp.me/Devices/ 
  • Download LineageOS. The codename for the OPO is bacon.
  • Download the Google apps mini distribution (stock is too large) here.
  • Enable developer tools and connect the phone with a USB cable
  • Reboot the device with adb:  ./adb reboot bootloader 
  • Check if the device is recognized:  ./fastboot devices 
  • Enable OEM unlock:  fastboot oem unlock 
  • Install the custom rom:  fastboot flash recovery twrp-x.x.x-x-bacon.img 
  • Reboot into the new ROM: With the device powered down, hold the Volume Down and Power buttons.

Copy the files to the device

Install both zip files by selecting first the LineageOS and then the Google Apps Zip file

Thats it. Reboot and begin with the setup or restore the backup.

Update 28.09.2017

The problem that the phone would not reconnect to 3G/4G again after losing the Wifi signal still persisted with LineageOS. A friend recommended flashing the firmware of the device. After installing the version 2016_1-25_.4.0.1.c7-00011 downloaded from here solved the issue for now. No more connection problems so far

Continue reading


Verifying Replication Consistency with Percona’s pt-table-checksum

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.

Setting up a Test Environment with Docker

The following command creates and starts a docker container. Note that these are minimal examples and are not suitable for a serious environment.

Get the IP address from the master container:

You can connect to this container like this and verify the server id:

We repeat the command for the slave, but use a different id. port and name:

For simplicity, we did not use Docker links, but will rather use IP addresses assigned by Docker directly.

Replication Setup

First, we need to setup a user with replication privileges. This user will connect from the slave to the master.

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

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.

Verify that the data is on the slave as well:

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.

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 pt-table-checksum tool the connects to the master instance and the the slave. It computes checksums of all databases and tables and compares results. You can use the tool like this:

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 -u root -h 172.17.0.3 -e "DELETE FROM sakila.film_category WHERE film_id > 995;" and update a row in the city table with 

Now execute the command again:

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.

With –dry-run, you only see affected tables, but not the actual data because it does not really access the databases tables in question. Use –print additionally or instead of dry-run to get a list:

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.

The command –no-check-child-tables ignores child tables and the command –no-foreign-key-checks ignores foreign keys.

Run the checksum command again to verify that the data has been restored:

0 DIFFS, we are done!

 

 

 

 

 

 

 

 

 

 

 

 

 

Continue reading


Fixing Random Freezes with Ubuntu 16.04 LTS, Intel Skylake and an Nvidia GPU

My Lenovo ThinkCentre m900 (10FHCTO1WW) with an Intel i7-6700 showed weird and random freezes from day 1 when trying to install Mint 18 / Ubuntu 16 with any kernel newer than 3x. After investigating for quite some hours, I gave up and installed an Ubuntu 14.04 LTS on it. The device is certified to it, but the old version did not support all features and even some basic things such as audio did not work. At lest the random freezes were gone and I could work with that machine. Now that the system will not receive updates soon, I gave it another try and setup Mint 18.2 (Sonya). Unfortunately, the Lenovo machine froze again after a few minutes, filling up the log again with the following error messages. 

I started the investigation again and found a different trace, which pointed to the graphics card. The important hint and solution came from SO. Following a few other forum posts, it became clear that the Nvidia drivers do not play nicely with recent kernels for some specific Nvidia cards ind combination with newer kernels. So I followed the proposed steps and disabled the card complete. Just removing the card in the BIOS and uninstalling the drivers was not enough. I also had to blacklist the modules for the nouveau kernel driver.

  1. Disable the Nvidia card in the BIOS and use the Intel onchip GPU
  2. Remove all Nvidia packages: 
    sudo apt-get remove nvidia* && sudo apt autoremove
  3. Blacklist the module:  
    sudo vim /etc/modprobe.d/blacklist.conf


  4. Reboot

The card is not used any more and the freezes stopped.

I hope I do not have to remove this article again and the system remains as stable as it is now for six hours.

Continue reading


Predicting Visitors with Facebook Prophet

Facebook open sourced its forecasting tool Prohpet for time series data. Although forecasting is not a trivial task, the libraries are very easy to use and produce nice results quickly.  In this basic blog post, I am going to forecast the visitor statistics based on the historical data I collected with Piwik.

Python Prerequisites

Install and initialize a new virtual Python environment

Install Prophet and its Dependencies

Within your new Python virtual environment, install the required dependencies first and then Prophet

Get the Data from your Piwik Database

We aggregate the data from the visitors table per day and store the result in a CSV file. In the case of this blog, I started collecting visitor traffic data from early 2013. Prophet allows displaying not only trends and seasonality, but also to forecast into the future.

Usually MySQL runs with a security setting that prevents writing files to the server’s disk (for a good reason). Check the variable secure-file-priv to find the path you can use for exporting.

The data now looks similar like this:

This is exactly the format which Prophet expects.

Forecasting with Prophet

The short but nice tutorial basically shows it all. Here is the script, it is basically the very same as from the tutorial:

The results are the forecast graph and the components as nice graphs. Facebook Prophet incorporates seasonal variations, holidays and trends derived from historical data.

As you can see, the weekend is rather low on visitors and that the beginning summer is also rather weak.

 

 

Continue reading