Skip to content

In case I'm not around.

Category: howto

MySQL drop all tables in database using queries

Tags: ,

During development I sometimes need to drop all tables in a database. When executed, all existing tables are dropped, so be careful executing it.

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  FROM information_schema.tables
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

Via Stackoverflow

Leave a Comment

Convert Windows line endings to Linux line endings using vim

Tags:

Windows uses the CR/LF characters (Carriage Return and Line Feed) to indicate a line-ending. Under Linux this is only one character, LF. You might see some strange characters at line-endings when editing a file in Linux that was saved in Windows.

To convert these line endings under Linux perform the following steps:

  • Edit the file with vim
  • Give the command :set ff=unix
  • Save the file

Solution Source

Leave a Comment

Custom cron interval on openshift cron cartridge

Tags:

Openshift v1 does not allow custom cron times, cronjobs can only be placed in the folders minutely, hourly, daily, weekly and monthly.

You can use the script below to run a cronjob every 5 minutes. You can vary the timestamp by modifying the +4 parameter. The script must be placed in the minutely folder. Here it will run every minute, and write a timestamp to a temporary file. It will check the creation date of the file, and if it is more than 4 minutes ago the cronjob will run. After that the file will be removed and recreated via the touch command.

The example below will run a PHP cronjob.

#!/bin/bash
if [ ! -f $OPENSHIFT_DATA_DIR/last_run ];
then
    touch $OPENSHIFT_DATA_DIR/last_run
    cd $OPENSHIFT_REPO_DIR;php cronjob.php RememberCron
else
 if [[ $(find $OPENSHIFT_DATA_DIR/last_run -mmin +4) ]]; then #run every 5 mins
   cd $OPENSHIFT_REPO_DIR;php cronjob.php RememberCron
   rm -f $OPENSHIFT_DATA_DIR/last_run
   touch $OPENSHIFT_DATA_DIR/last_run
  fi  
fi

Leave a Comment

Mopidy http server on network

Tags:

I recently installed the mopidy audio player on my raspberry pi to stream music in my livingroom. After installing it it was not immediately clear to me how I could access the installed web client. The example configuration did show an example mpd section with a network config. But that did not make the web interface available at port 6680. It turns out that the http interface has a different config. This is explained in the docs but I did not get that far after the getting started guide. The example below makes mpd listen on all interfaces.

[http]
hostname = ::

Please note that this will make the HTTP server available to every node on the network. This HTTP endpoint will be available without any security protections.

Leave a Comment

Disable blue blinking light on Odroid C2

Tags:

I recently purchases an Odroid C2 unit which I used for hosting some sites. This site, amongst others, runs on it. The unit sits in my livingroom behind the couch. The case is transparent and there was an annoying blue light which blinks ever second. At day time this is not a problem, but during the evening and nights this became a problem. Lucky I found an easy solution on the Odroid forum:

sudo -Hi
echo none >/sys/class/leds/blue\:heartbeat/trigger

By executing these commands on the terminal of the device the blinking will stop. It is removed at reboot however, so you should add the 2nd line to your /etc/rc.local file to have it set at boot time.

Source

Leave a Comment

Enabling swap on raspberry pi

Yesterday I was building something from source on my Raspberry Pi B model. It was a text to speech project with some large source files, for the models. Compilation would keep failing because of an out of memory error (which is not surprising, since this model only has 512 MB of memory). Using swap on a raspberry pi is not something you should do normally, but when running into problems like this, I feel it is ok to temporarily enable swap, until compilation is complete.

I decided to grab an old usb stick and use that as the swap partition. You should never use the SD or Flash card of the device itself, since the constant writes will wear it out pretty fast. On stackexchange I found a simple guide to enable swap (raspberrypi.stackexchange):

mkswap /dev/sda
swapon /dev/sda

WARNING: all data will be cleared from the usb drive! This assumes that the usbstick is mapped as device sda after insertion. After you can run the memory-intensive task. Be aware that it will probably very slow, because of the swapping. My build task ran for 1,5 day. When the job is completed, you can run the command below and remove the usb drive.

swapoff /dev/sda

Leave a Comment

Free space on /boot path in Linux

Tags:

When executing apt-get upgrade on my Linux machine I got the error that it could not execute because the /boot folder was full. And indeed, when executing du -h there was only 3% of space left on /boot.

As it turns out, there are old kernels stored in /boot, limiting me from updating. Since I only use the latest one the older versions can be removed.

This link explains in simple steps how to find and remove the older kernel versions.

The summary of the page above is to execute the command below (at your own risk!). First execute the dry-run to check what is removed:

dpkg -l linux-* | awk '/^ii/{ print $2}' | grep -v -e `uname -r | cut -f1,2 -d"-"` | grep -e [0-9] | grep -E "(image|headers)" | xargs sudo apt-get --dry-run remove

If everything checks out run:

dpkg -l linux-* | awk '/^ii/{ print $2}' | grep -v -e `uname -r | cut -f1,2 -d"-"` | grep -e [0-9] | grep -E "(image|headers)" | xargs sudo apt-get -y purge

Leave a Comment

NPM workaround for EROFS error

Tags:

On my Virtualbox with lubuntu, upon running npm install I encountered problems with installing some packges.

npm ERR! rofs EROFS, symlink '../rimraf/bin.js'
npm ERR! rofs This is most likely not a problem with npm itself
npm ERR! rofs and is related to the file system being read-only.
npm ERR! rofs 
npm ERR! rofs Often virtualized file systems, or other file systems
npm ERR! rofs that don't support symlinks, give this error.

As the error explains, this probably has to do with the filesystem being virtualized. This was also in a shared folder with the host machine.

To work around this, use the –no-bin-link command option to prevent npm from attempting to create symbolic links. As a result, the commands of the package will not be available in _node_modules/.bin_. But it will still be available from the bin folder of the package itself.

Leave a Comment

Set charset on PDO connection with mysql in PHP

Tags: ,

When using PDO to connect to MySQL it is wise to explicitly set the character set to utf8 (of course, only when using utf8 is the charset). In the MySQL or MySQLi extension I would normally execute the query SET NAMES utf8 to set it.

In PDO the charset can be specified in the connection string:

$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);

The charset option is only used since PHP 5.3.6, so take this into account when running an older version of PHP. In that case you should run the following statement after constructing the PDO object:

$conn->exec('SET NAMES utf8');

But you should’nt be running such an old version of PHP anyway.

Leave a Comment