Install Innotop from Source

Innotop is a great tool but not included in the current Ubuntu repositories. Here is how you install it manually:

# Install perl database interface
sudo apt-get install libdbi-perl
# Install MySQL and Terminal perl modules
sudo cpan Term::ReadKey DBD::mysql
# Clone innotop
git clone https://github.com/innotop/innotop.git
# Enter directory
cd innotop
# Make
perl Makefile.PL
# Install
sudo make install

Then you can run innotop like this

innotop --user $ADMIN_USER --password $ADMIN_PASSWORD --host $HOST

 

Continue reading


Grafana and InfluxDB with SSL inside a Docker Container

Self-signed SSL certificates

On the host, create a directory for storing the self signed SSL certificates. This directory will be mounted in the Grafana container as well as in the InfluxDB container to /var/ssl . Create the self signed SSL certificates as follows:

mkdir -p /docker/ssl
cd /docker/ssl/
# Generate a private key
openssl genrsa -des3 -out server.key 1024
# Generate CSR
openssl req -new -key server.key -out server.csr
# Remove password
openssl rsa -in server.key -out server.key
# Generate self signed cert
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
# Set permissions
sudo chmod 644 server.crt
sudo chmod 600 server.key

Next, create a config directory and create individual configuration files for Grafana and InfluxB: mkdir conf 

Grafana

In the file ./conf/grafana/defaults.ini set the protocol to https and provide the paths to the mounted ssl directory in the container.

#################################### Server ##############################
[server]
# Protocol (http, https, socket)
protocol = https
...
...
# https certs & key file
cert_file = /var/ssl/server.crt
cert_key = /var/ssl/server.key
...

InfluxDB

The file ./conf/influxdb/influxdb.conf is also pretty simple. Add a [http] category and add the settings:

[meta]
  dir = "/var/lib/influxdb/meta"
[data]
  dir = "/var/lib/influxdb/data"
  engine = "tsm1"
  wal-dir = "/var/lib/influxdb/wal"
[http]
  https-enabled = true
  https-certificate ="/var/ssl/server.crt"
  https-private-key ="/var/ssl/server.key"

Environment

You can set environment variables in env files  for the services.

env.grafana

GF_INSTALL_PLUGINS=grafana-clock-panel,briangann-gauge-panel,natel-plotly-panel,grafana-simple-json-datasource

env.influxdb

INFLUXDB_REPORTING_DISABLED=true
INFLUXDB_DB=
INFLUXDB_HTTP_AUTH_ENABLED=true
INFLUXDB_ADMIN_USER=admin
INFLUXDB_ADMIN_PASSWORD=
INFLUXDB_USER=
INFLUXDB_USER_PASSWORD=
INFLUXDB_WRITE_USER=
INFLUXDB_WRITE_USER_PASSWORD=

Docker Compose

Now you can launch the service by using docker-compose up  for the following file. Note

version: '2'

services:
    influxdb:
        image: influxdb:latest
        container_name: influxdb
        ports:
            - "8083:8083"
            - "8086:8086"
            - "8090:8090"
        env_file:
            - 'env.influxdb'
        volumes:
            - data-influxdb:/var/lib/influxdb
            - /docker/ssl:/var/ssl
            - /docker/conf/influxdb/influxdb.conf:/etc/influxdb/influxdb.conf


    grafana:
        image: grafana/grafana:latest
        container_name: grafana
        ports:
            - "3000:3000"
        links:
            - influxdb
        env_file:
            - 'env.grafana'
        volumes:
            - data-grafana:/var/lib/grafana
            - /docker/ssl:/var/ssl
            - /docker/conf/grafana/defaults.ini:/usr/share/grafana/conf/defaults.ini
volumes:
  data-influxdb:
  data-grafana:

Lets Encrypt Setup

If you require valid certificates, you can also use certificates from lets encrypt.

First, create the certificates on the host:

certbot certonly --standalone --preferred-challenges http --renew-by-default -d iot.example.org

Then use this docker-compose file.

version: '2'

services:
    influxdb:
        image: influxdb:latest
        container_name: influxdb
        ports:
            - "8083:8083"
            - "8086:8086"
            - "8090:8090"
        env_file:
            - 'env.influxdb'
        volumes:
            - data-influxdb:/var/lib/influxdb
            - /etc/letsencrypt/live/iot.example.org/fullchain.pem:/var/ssl/server.crt
            - /etc/letsencrypt/live/iot.example.org/privkey.pem:/var/ssl/server.key
            - /docker/conf/influxdb/influxdb.conf:/etc/influxdb/influxdb.conf


    grafana:
        image: grafana/grafana:latest
        container_name: grafana
        ports:
            - "3000:3000"
        links:
            - influxdb
        env_file:
            - 'env.grafana'
        volumes:
            - data-grafana:/var/lib/grafana
            - /etc/letsencrypt/live/iot.example.org/fullchain.pem:/var/ssl/server.crt
            - /etc/letsencrypt/live/iot.example.org/privkey.pem:/var/ssl/server.key
            - /docker/conf/defaults.ini:/usr/share/grafana/conf/defaults.ini
volumes:
  data-influxdb:
  data-grafana:

Continue reading


Jupyter docker stacks with a custom user

Jupyter allows to set a custom user instead of jovyan which is the default for all containers of the Jupyter Docker Stack. You need to change this user or its UID and GID in order to get the permissions right when you mount a volume from the host into the Jupyter container. The following steps are required:

  1. Create an unprivileged user and an asociated group on the host. Here we call the user and the group docker_worker
  2. Add your host user to the group. This gives you the permissions to modify and read the files also on the host. This is useful if your working directory on the hist is under source code control (eg. git)
  3. Launch the container with the correct settings that change the user inside the container

It is important to know that during the launch the container needs root privileges in order to change the settings in the mounted host volume and inside the container. After the permissions have been changed, the user is switched back and does not run with root privileges, but your new user. Thus make sure to secure your Docker service, as the permissions inside the container also apply to the host.

Prepare an unprivileged user on the host

1. sudo groupadd -g 1011 docker_worker
2. sudo useradd -s /bin/false -u 1010 -g 1020 docker_worker
3. Add your user to the group: sudo usermod -a -G docker_worker stefan

Docker-compose Caveats

It is important to know that docker-compose supports either an array or a dictionary for environment variables (docs).  In the case below we use arrays and we quote all variables. If you accidentally use a dictionary, then the quotes would be passed along to the Jupyter script. You would then see this error message: 

/usr/local/bin/start-notebook.sh: ignoring /usr/local/bin/start-notebook.d/*
Set username to: docker_worker
Changing ownership of /home/docker_worker to 1010:1020
chown: invalid user: ‘'-R'’

The docker-compose file

version: '2'
services:
    datascience-notebook:
        image: jupyter/base-notebook:latest
        volumes:
            - /tmp/jupyter_test_dir:/home/docker_worker/work            
        ports:
            - 8891:8888
        command: "start-notebook.sh"
        user: root
        environment:
          NB_USER: 'docker_worker'
          NB_UID: 1010
          NB_GID: 1020
          CHOWN_HOME: 'yes'
          CHOWN_HOME_OPTS: -R

Here you can see that we set the variables that cause the container to ditch jovyan in favor of docker_worker.

NB_USER: ‘docker_worker’
NB_UID: 1010
NB_GID: 1020
CHOWN_HOME: ‘yes’
CHOWN_HOME_OPTS: -R

This facilitates easy version control of the working directory of Jupyter. I also added the snipped to my Github Jupyter template.

Continue reading


Unbrick a Netgear WNR3500L V2

It finally happened… After experimenting with alternative firmware for my WNR3500L router, I uploaded the wrong version and converted the device into a rather expensive paperweight. After some searching, I found this page, explaining how to revitalize the router by using a USB-TTL converter cable. You can get  (e.g. CP2102 Module Modul USB to TTL Converter) for a few bucks Ebay, but expect a few weeks for the delivery from China.  If you follow the tutorial on the OpenRouters page closely, it should work out quite nicely.

Update: 2018-01-21

It happened again. I flashed an unsupported version of LEDE on the device and it did not want to boot again. I followed the same procedure as described here, but the router was complaining about a version missmatch of the device ID and the image ID. Turns out, I had to explicitly use binary mode for FTP. Here are the steps again.

  1. Connect the serial cables as shown
  2. Make sure router is off
  3. Launch minicom in a terminal and make sure the settings are valid for ttyUSB0
  4. Boot the router and immediately press Ctrl+C in the terminal
  5. Type tftpd in the prompt, the router should start a FTP server on 192.168.1.1
  6. Open a second terminal, cd into the directory where the firmware is
  7. Make sure its the right version 🙂
  8. Start the FTP client: ftp 192.168.1.1
  9. Change to binary mode (this is very important): mode binary
  10. Move the file: put FIRMWARE.chr
  11. The router should detect the file and process it
  12. Reboot and enjoy

Connecting a Serial TTYUSB Device to the Router

 

USB-TTL

When you see this message below, you need to abort the boot process.

Willkommen zu minicom 2.7

Optionen: I18n 
Übersetzt am Feb  7 2016, 13:37:27.
Port /dev/ttyUSB0, 17:47:29

Drücken Sie CTRL-A  Z für Hilfe zu speziellen Tasten
Found a Samsung NAND flash with 2048B pages or 128KB blocks; total size 128MB
Decompressing...done


CFE for WNR3500Lv2 version: v1.0.9
Build Date: Fri May  6 11:54:17 CST 2011 
Init Arena
Init Devs.
Boot partition size = 262144(0x40000)
NFLASH Boot partition size = 524288(0x80000)
et0: Broadcom BCM47XX 10/100/1000 Mbps Ethernet Controller 5.60.136 
CPU type 0x19749: 480MHz
Tot mem: 131072 KBytes

Device eth0:  hwaddr 84-1B-5E-4E-FF-84, ipaddr 192.168.1.1, mask 255.255.255.0
        gateway not set, nameserver not set
Checking crc...done.
Loader:raw Filesys:raw Dev:flash0.os File: Options:(null)
Loading: .. 3848 bytes read
Entry at 0x80001000
Closing network.
Starting program at 0x80001000

When pressing Ctrl+C does not work, make sure your minicom settings look like this:

A - Serieller Anschluss      : /dev/ttyUSB0                           |
B - Pfad zur Lockdatei       : /var/lock                              |
C - Programm zur Rufannahme  :                                        |
D - Programm zum Wählen      :                                        |
E - Bps/Par/Bits             : 115200 8N1                             |
F - Hardware Flow Control    : Nein                                   |
G - Software Flow Control    : Nein

 

Continue reading


Illegal mix of collations: IntelliJ and UTF8mb4

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:

[HY000][1267] Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) \
   and (utf8mb4_general_ci,IMPLICIT) for operation 'like'

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:

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.

SET character_set_connection=utf8mb4;
SET collation_connection=utf8mb4_unicode_520_ci;

Continue reading