Investigate & reduce the size of Drupal sqlite3 database

Today while performing regular Drupal update and backup, I’ve realised Drupal sqlite3 database sites/default/files/.ht.sqliteis over 440 Mb! I found it peculiar, as our website isn’t storing that much information and the size grew significantly since last time I’ve looked it up couple of months ago. I’ve decided to investigate what’s eating up so much DB space.

Investigate what’s eating up space within your sqlite3 db

There is super useful program called sqlite3_analyzer. This program analyses your database file and reports what’s actually taking your disk space. You can download it from here (download precompiled sqlite3-tools). Note, under Linux you’ll likely need to install 32bit-libraries ie. under Ubuntu/Debian execute

sudo apt install libc6-i386 lib32stdc++6 lib32gcc1 lib32ncurses5 lib32z1  

Once you have the program, simply execute sqlite3_analyzer DB_NAME | less and the program will produce detailed report about your DB space consumption. For me it looked like that:

Can you spot how much space the actual data is taking? Yes, only 4.7% (20k pages). And what’s taking most of the space? Freelist.

Quick googling taught me, that freelist is simply empty space left after deletes or data moving. You may ask, why isn’t it cleaned up later? You see, having entire database with all tables in one file is very handy, but troublesome. Every time given table is edited, the space that is freed isn’t used, but rather marked as freelist. And those regions get cleaned up only when vacuumcommand is issued. This should happen automatically from time-to-time if auto vacuum is enabled. I couldn’t know why isn’t it working by default with Drupal…

Reduce the size of sqlite3 DB file

Nevertheless, I’ve decided to perform vacuummanually. Of course I’ve backed-up the db, just in case (you should always do that!). But sqlite3 .ht.sqlite vacuum returned Error: no such collation sequence: NOCASE_UTF8. At this point, I though maybe simple DB dump and recovery would solve my problem – after all that’s more or less what happens under the hood when you perform vacuum.

sqlite3 .ht.sqlite.bck .dump > db.sql
sqlite3 .ht.sqlite < db.sql

DB recovered after dump was indeed smaller (16 Mb), but it was missing some tables (sqlite3 .ht.sqlite .tables). Interestingly, when I’ve investigated the schema of the missing tables (sqlite3 .ht.sqlite.bck .schema block_content), I’ve realised that all of those contain NOCASE_UTF8 in table schema. I found that really peculiar! After further googling and rather lengthy reading, I’ve realised NOCASE_UTF8 is invalid in sqlite3, but it can be replaced simply with NOCASE.

Replace DB schema directly on sqlite3 db

In the brave (and firstly stupid I though) attempt, I’ve decided just to replace wrong statements directly on the DB file using sed (sed 's/NOCASE_UTF8/NOCASE/g' .ht.sqlite.bck > .ht.sqlite). As expected, the database file got corrupted. This is because all tables location are stored internally in the same file, so truncating some text from the DB file isn’t the wisest idea as I’ve expected. Then, I’ve decided to replace NOCASE_UTF8, but keeping the same size of the statement after replacement using white spaces. To my surprise it worked & allowed me to reduce the size of DB from 440 to 30 Mb 🙂

sed 's/NOCASE_UTF8/NOCASE     /g' .ht.sqlite.bck > .ht.sqlite
sqlite3 .ht.sqlite vacuum
-rw-rw-r--  1 lpryszcz www-data  32638976 Feb 28 13:57 .ht.sqlite
-rw-rw-r-- 1 lpryszcz www-data 451850240 Feb 28 13:45 .ht.sqlite.bck

Finally, to make sure, that there is no data missing between old and new, reduced DB, you can use sqldiff .ht.sqlite .ht.sqlite.bck. It’ll simply report all SQL command that will transform one DB into another and nothing if DB contain identical information.

Hopefully replacing NOCASE_UTF8 with NOCASE will allow auto vacuum to proceed as expected on the Drupal DB in the future!

Why did I switch back to Raspbian on my RPi server?

Frankly, while I’m super happy user of Ubuntu on desktop and servers, I’ve been quite unsatisfied with Ubuntu for Raspberry Pi. Mostly, cause it ruined a few of my SD cards. I’ve realised that writing file access info to the card was one of the key factor here. Unfortunately, Ubuntu for RPi won’t but with noatime… while latest Rasbian has it by defualt.

What I like a lot about Raspbian Stretch Lite, is that beside natively supporting all Raspberry Pi features, it’s also cross platform compatible – it works super well on both, RPi2 and RPi3.

And yes, this blog among few other things is server from RPi2 🙂

Changing MAC address of USB LAN adapter permamently

It’s been long time since the last post… But time came that I’ve faced serious problem when trying to change MAC address of my USB LAN adapter.

As recommended by numerous pages found by googling change MAC address Linux, I’ve tried ifconfig eth0 hw ether NEWMAC and macchanger. It changed MAC of my devices (as seen in ifconfig output), yet after plugging the LAN cable, the MAC was automatically restored to permanent one.

At first, I thought it’s the fault of NetworkManager, so I’ve stopped it. But the problem still persisted. After some tinkering, I’ve realised, the MAC can be specified also in NetworkManager alone by adding to /etc/NetworkManager/NetworkManager.conf two lines:

[connection]
ethernet.cloned-mac-address=NEWMAC

and restarting NetworkManager

sudo service network-manager restart

Note, when I’ve changed MAC in NetworkManager using GUI, the permanent MAC was also restored upon LAN cable connection.

Hope this helps someone having similar problem with USB LAN adapter.

VLC subtitles downloading (VLSub) doesn’t work / hangs

For some weeks already, I’ve been annoyed by not working VLSub extension of VLC. It simply hangs during downloading the subtitles. Apparently, this is associated with changes in OpenSubtitles.org remote access. Today, I’ve found simple solution for this issue:

  • Update VLC
  • sudo add-apt-repository ppa:videolan/master-daily
    sudo apt-get update && sudo apt-get upgrade -y
    
  • Replace HTTP/1.1 with HTTP/1.0 in /usr/lib/vlc/lua/extensions/VLSub.luac
  • sudo sed -i.bak 's\HTTP/1.1\HTTP/1.0\g' /usr/lib/vlc/lua/extensions/VLSub.luac
    
  • Restart VLC and enjoy!

Solution found on VideoLan forum.

Raspberry Pi2 with Ubuntu Sever and Drupal?

I decided to celebrate 25th B-day of Linux by putting the latest Ubuntu 16.04 on my Raspberry Pi 2 and setting up a webserver.
This is how I did it:

  1. First, get Ubuntu armf image and prepare memory card
  2. # get image
    wget http://cdimage.ubuntu.com/ubuntu/releases/16.04/release/ubuntu-16.04-preinstalled-server-armhf+raspi2.img.xz
    
    # make sure your SD card is on sdb ie by df -h
    xzcat ubuntu-16.04-preinstalled-server-armhf+raspi2.img.xz | sudo dd of=/dev/sdb
    
  3. Configure new user & setup Drupal8 webserver
  4. # create new user & change hostname
    sudo adduser USERNAME && sudo usermod -a -G sudo USERNAME
    # edit /etc/hostname and add `127.0.1.1 newHostname` to /etc/hosts
    sudo reboot
    
    # generate locales
    sudo locale-gen en_US.UTF-8
    sudo dpkg-reconfigure locales
    
    # install software
    sudo apt install htop apache2 mysql-server libapache2-mod-php php-mysql php-sqlite3 php-curl php-xml php-gd git sqlite3 emacs-nox
    

My first impressions?
sudo apt is veeery slow. At first, I thought it’s due to old SD card I’ve been using, but it’s also true for newer SD card.
Some packages are missing (ie. git-lfs), but you can get them using some workarounds.

But everything just works!
You can check the mirror of https://ngschool.eu/ running on RPi2 here.
Maybe it’s not speed devil, but it stable and uses almost no energy 🙂

Cheers!

Inspired by Ubuntu’s Insights.

Installation of git-lfs on Ubuntu RPi2 (armf)

Unfortunately, the standard way to install git-lfs doesn’t work on RPi2. But there is simple workaround:

# install go
sudo apt-get install golang
# you may want to grab a coffee at this stage... 😉 

# get git-lfs from github
sudo -i
mkdir /root/gocode
export GOPATH=/root/gocode
go get github.com/github/git-lfs
cp gocode/bin/git-lfs /usr/bin

Reducing the size of large git repository

The github repository of #NGSchool website has grown to over 5GB. I wanted to reduce the size & simplify this repository, but this task turned out to quite complicated. Instead, I have decided to leave current repo as is (and probably removed it soon) and start new repo for existing version. I could do that, as I don’t care about version earlier than the one I’m currently using. This is short how-to:

  1. Push all changes and remove .git folder
  2. git push origin master
    rm -rI .git
    
  3. Rename existing repo
  4. Settings > Repository name > RENAME

  5. Start new repository using old repo name
  6. Don’t need to create any files as all already exists.

  7. Init your local repo and add new remote
  8. git init
    git remote add origin git@github.com:USER/REPO
    
  9. Commit changes and push
  10. git add --all . && git commit -m "fresh" && git push origin master
    

Doing so, my new repo size is below 1GB, which is much better compared to 5GB previously.

Convert xls table into abstract book PDF

I had to generate Abstract book for #NGSchool2016 (). I had spreadsheet generated by Google Forms with all necessary information. I could copy-paste all entries and format it later on, but I found LaTeX more robust for the task.
As I had already LaTeX template, the only missing part was conversion of .xls to .tex. Thus I have written simple script, xls2tex.py, that generate .tex file based on table from .xls file.
This script, among many other things, convert utf into LaTeX escape characters.

xls2tex.py depends on xlrd and utf8tolatex (from pylatexenc/latexencode, but this is given as single file)

# install dependencies
sudo apt-get install python-xlrd
# generate tex
xls2tex.py

# generate pdf
make

Output pdf.