TravisCI: Setup MySQL Tables+Data before running Tests

test, mysql, mariadb, travis, continuous integration, before_install

In case your projects make use of external databases like MySQL/MariaDB you need to setup your continuous integration tests with dedicated testcases including application specific database structures. This requires some initial steps to load the database dump before starting the tests. Thanks to travisci.org you do’t need to do this kind of stuff within your application – just use the test configuration!

Travis+MySQL Server#

First of all, we add MySQL Server as service within our .travis.yml file. This initializes a dedicated database instance for testing. Additionally we hook into the before_install action to initialize our database structure. In this example all SQL commands are loaded from an external file located in our test directory.

language: node_js
node_js:
  - "7"
  - "7.6"
  - "8"
services:
  - mysql
before_install:
  - mysql -u root --password="" < test/travis.sql

Initial Database Setup#

Our Test Database structure is definied within a dedicated SQL file in test/travis.sql. It contains all necessary commands to add a new user, create demo database, create demo tables and finally add some test-data.

# Create Testuser
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'dev'@'localhost';

# Create DB
CREATE DATABASE IF NOT EXISTS `demo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `demo`;

# Create Table
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL,
  `created_on` timestamp NULL DEFAULT NULL,
  `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(50) DEFAULT NULL,
  `salt` varchar(20) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

# Add Data

systemd: Start your Firewall before network interfaces coming up

linux debian, ubuntu, systemd, networking, uptables

There are a serveral “tutorials” and code snippets out there but they wont work on modern systemd versions and may cause fatal errors! In case you want to start your firewall before the network interfaces will be initialized, you have to hook into the special systemd target network-pre.target. It is a passive target which is invoked before any network services has been started.

Additionally, you have to explicit set the DefaultDependencies=no option – otherwise systemd automatically adds dependency of the type After=basic.target to your service and your firewall is invoked AFTER networking has been started!

Systemd Service File#

The following service file assumes that your firewall script is located in /usr/sbin/myfirewall.sh

[Unit]
Description=MyFirewall

# Start before Network Interfaces coming up
Before=network-pre.target
Wants=network-pre.target
After=local-fs.target

# Do not start after basic.target!
DefaultDependencies=no

[Service]
ExecStart=/usr/sbin/myfirewall start
ExecStop=/usr/sbin/myfirewall stop

# Just Execute the shell script
Type=oneshot
RemainAfterExit=yes

Debugging Service Startup#

The systemd-analyze utility provides a really cool way to show the system startup. Finally you should verify that your firewall is executed before networking has started!

# dump the service startup
systemd-analyze plot > /root/systemd_startup.svg

Example#

Startup of the Firewall and Networking

External USB 3.0 SSD with Full Disk Encryption

ata encryption, aes256, ASM1053E, ubuntu, linux, external case, caddy, intel 535 series

Preface# External USB drives are everywhere these days, used as storage extension, data transport facility or backup drive. If you need a reliable, schock resistent and secure solution an external SSD might be the best choice instead of an old fashioned hard drive. Especially in case the drive got stolen, a SSD can protect your data […]

HowTo: Wakeup your Synology NAS from Standby/Power Save Mode

timeout, linux, ubuntu, backup, scp, sftp, System Hibernation, backup

Scheduled Backups from Remote Locations#

As poweruser, you may have different servers out there which send their backups to a centralized backup location – in this example, a Synology NAS. The file transfers can be done by ftp, sftp, scp, nfs or another supported protocol.

In case you want to safe energy costs, it possible to enable the power safe mode which turns the system (as well as the HDDs) in standby mode. It can be waked-up by accessing the web-interface or some other file services, but this will take around 30-60s! In most cases, this behaviour will cause a timeout or connection refused error in your backup scripts. To prevent this, you can wake up your NAS before running the backup tasks. The following script tries to access the Web-Interface (DSM) on port 80 for a several times and returns 0 as exit code in case a valid response is returned by the remote server.

Wake-Up Script#

#!/bin/bash

# Synology NAS Wake-up
# ------------------------------------

# hostname/ip set ?
if [ -z "$1" ]; then
    echo "Usage: synology_wakeup.sh <hostname>"
    exit 1
fi

# get the server response. 5 connection tries with 10s delay -> 200s wait
serverResponse=$(wget --quiet --max-redirect=0 --retry-connrefused --timeout=20 --wait=10 --tries 5 --server-response -O /dev/null $1 2>&1)

# http detection pattern (response will be empty on con_refused)
detectionPattern="HTTP/1.1 (200|30[0-8])"

# server online ?
if [[ $serverResponse =~ $detectionPattern ]] ; then
    exit 0
else
    exit 1
fi

Usage#

Just run the script by passing the ip addess/hostname to it. On error (non responding nas) the script will return the exit code 1.

#!/bin/bash

# your backup/pre backup script

# wakeup your NAS by its IP/Hostname
./synology_wakeup.sh 192.168.0.100

# successfull ?
if [ $? -ne 0 ]; then
   echo "ERROR - Synology NAS seems to be offline!"
   exit 1
fi

 

 

 

HowTo: Flashing NodeMCU Firmware to ESP8266

esptool.py, nodemcu, ubuntu, debian

NodeMCU Firmware# From time to time, you should take a look into the official NodeMCU Firmware repository. It is under heavy development and new modules and features are added constantly. Since version 0.9.6, there are no binary releases available, because the large amount of available module will not fit into the flash. Instead, you can […]

Firmware Update of IBM ServeRaid BR10i with Ubuntu

system x, x3550 m3, LSI SAS 1068E, ibm 7944

I have just got a used IBM x3550 M3 server as development machine and ran into some trouble when trying to add some SSDs: the throughput was pretty slow (~100MB/s). Generally the BR10i controller supports 3G SATA2 and therefore i expected a throughput of ~250MB. I’ve figured out, that this behaviour is caused by the firmware […]

Howto: (re-)Enable SCP/SSH Login on Synology DSM 6.0 for non admin users [UPDATE]

a update which may break your backup tasks! change the user shell permanently

When updating to the latest DSM 6.0 final, you may have noticed that your scp backup accounts won’t work anymore (this also affects ssh the login). It is caused by a reset of the login shell settings in /etc/passwd ! It is happened in part of a “security enhancement” – normal users, which does not […]

Tweaking Minidlna Media Server on AsusWRT Merlin

usb storage, disable album arts, performance

You’re running AsusWRT Merlin and have some trouble with minidlna, e.g. bad media indexing performance or broken media databases ?

This can be caused by using an USB Stick as media database storage! Internally, minidlna is using an SQLite database to store the media file index – and sometime this database may broke (slow, unsynced file operations, user terminated processes).

As a workaround, it’s possible to move the media database to the temporary filesystem (ramdisk). As a disadvantage, on every system shutdown (reboot/power cycle) the database will be destroyed. But it only takes a view minutes to recreate it, because the ramdisk storage is a lot faster than the attached USB drive!

Just create an additional user config file in your JFFS /jffs/configs/minidlna.conf.add (will be automatically appended to the system generated minidlna.conf file!)

# Move the database to the tmp directory (ramdisk, will be recreated on reboot !!)
db_dir=/tmp/.minidlna

# create a custom minidlna logfile
log_dir=/var/log

# disable album art indexing
album_art_names=NO_ALBUM_ARTS.x

 

Ubuntu 15.10 with Dell Wireless 5570 HSPA+ (42Mbps) Mobile Broadband Card

mbmi to qmi modeswitch, magical ffc-auth sequence

A several days ago, i’ve replaced the Gobi 2000 WWAN Card with a newer version, a Dell 5570 Module. It’s a branded Sierra Wireless card, so i hoped it will work out of the box on Ubuntu 15.10 – but nothing. After some investigations if figured out, that the card is locked in “low-power” / […]

Ubuntu 15.10 – Disable Wake on LAN (WOL) permanently

ubuntu, systemd, Wily Werewolf, notebook, battery power drain

Preface# In some cases, Wake on LAN is enabled by default and you cannot disable it in your BIOS/UEFI because the setting is not available. On notebooks/ultrabooks, WOL can drain/discharge your battery even your device is powered-off! There are a several guides out there, which didn’t even work. The general mistake is, that the WOL […]