Investigate & reduce the size of Drupal sqlite3 database

Posted on February 28, 2019 by lpryszcz

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!

EDIT: The db failed after update to drupal v8.7.6

Lately, I’ve updated drupal and discovered this morning the drupal db file to be corrupted Error: no such collation sequence: NOCASE_UTF8. This is because in the latest update, drupal rebuilt table definitions and NOCASE_UTF8 came back which causes sqlite vacuum crashing again. The solution is very simple, just recover your db from backup and remove replace NOCASE_UTF8 with NOCASE .

sed -i.bck 's/NOCASE_UTF8/NOCASE     /g' .ht.sqlite

Running Jupyter as public service

Posted on June 24, 2016 by lpryszcz

Some time ago, I’ve written about setting up IPython as a public service. Today, I’ll write about setting up Jupyter, IPython descendant, that beside Python supports tons of other languages and frameworks.

Jupyter notebook will be running in separate user, so your personal files are safe, but not as system service. Therefore, you will need to restart it upon system reboot. I recommend running it in SCREEN session, so you can easily login into the server and check the Jupyter state.

Install & setup Jupyter

#
sudo apt-get install build-essential python-dev
sudo pip install jupyter

# create new user
sudo adduser jupyter
 
# login as new user
su jupyter

# make sure to add `unset XDG_RUNTIME_DIR` to ~/.bashrc
# otherwise you'll encounter: OSError: [Errno 13] Permission denied: '/run/user/1003/jupyter'
echo 'unset XDG_RUNTIME_DIR' >> ~/.bashrc
source ~/.bashrc

# generate ssl certificates
mkdir ~/.ssl
openssl req -x509 -nodes -days 999 -newkey rsa:1024 -keyout ~/.ssl/mykey.key -out ~/.ssl/mycert.pem

# generate config
jupyter notebook --generate-config

# generate pass and checksum
ipython -c "from IPython.lib import passwd; passwd()"
# enter your password twice, save it and copy password hash
## Out[1]: 'sha1:[your hashed password here]'
 
# add to ~/.jupyter/jupyter_notebook_config.py
c.NotebookApp.ip = '*'
c.NotebookApp.open_browser = False
c.NotebookApp.port = 8881
c.NotebookApp.password = u'sha1:[your hashed password here]'
c.NotebookApp.certfile = u'/home/jupyter/.ssl/mycert.pem'
c.NotebookApp.keyfile = u'/home/jupyter/.ssl/mykey.key'

# create some directory for notebook files ie. ~/Public/jupyter
mkdir -p ~/Public/jupyter && cd ~/Public/jupyter
 
# start notebook server
jupyter notebook

Add kernels

You can add multiple kernels to Jupyter. Here I’ll cover installation of some:

Python

sudo pip install ipykernel

# if you wish to use matplotlib, make sure to add to 
# ~/.ipython/profile_default/ipython_kernel_config.py
c.InteractiveShellApp.matplotlib = 'inline'

BASH kernel

sudo pip install bash_kernel
sudo python -m bash_kernel.install

Perl

This didn’t worked for me:/

sudo cpan Devel::IPerl

IRkernel

Follow this tutorial.

Haskell

sudo apt-get install cabal-install
git clone http://www.github.com/gibiansky/IHaskell
cd IHaskell
./ubuntu-install.sh

Then, just navigate to https://YOURDOMAIN.COM:8881/, accept self-signed certificate and enjoy!
Alternatively, you can obtain certificate from Let’s encrypt.

Using existing domain encryption aka Apache proxy
If your domain is already HTTPS, you may consider setting up Jupyter on localhost and redirect all incoming traffic (already encrypted) to particular port on localhost (as suggested by @shebang).

# enable Apache mods
sudo a2enmod proxy proxy_http proxy_wstunnel && sudo service apache2 restart

# add to your Apache config
    <Location "/jupyter" >
        ProxyPass http://localhost:8881/jupyter
        ProxyPassReverse http://localhost:8881/jupyter
    </Location>
    <Location "/jupyter/api/kernels/" >
        ProxyPass        ws://localhost:8881/jupyter/api/kernels/
        ProxyPassReverse ws://localhost:8881/jupyter/api/kernels/
    </Location>
    <Location "/jupyter/api/kernels/">
        ProxyPass        ws://localhost:8881/jupyter/api/kernels/
        ProxyPassReverse ws://localhost:8881/jupyter/api/kernels/
    </Location>

# update you Jupyter config (~/.jupyter/jupyter_notebook_config.py)
c.NotebookApp.ip = 'localhost'
c.NotebookApp.open_browser = False
c.NotebookApp.port = 8881
c.NotebookApp.base_url = '/jupyter'
c.NotebookApp.password = u'sha1:[your hashed password here]'
c.NotebookApp.allow_origin = '*'

Note, it’s crucial to add Apache proxy for kernels (/jupyter/api/kernels/), otherwise you won’t be able to use terminals due to failed: Error during WebSocket handshake: Unexpected response code: 400 error.

Enable HTTPS for your domains in 5 minutes & for free!

Posted on June 23, 2016 by lpryszcz

For a while, I’ve been thinking about encryption domains, like this one. But cost & complications associated with enabling SSL encryption prohibited me to do so…
Today, I’ve realised, Let’s encrypt, new certificate authority, that is completely free, automated and open, makes SSL encryption super easy!
Try it yourself (this if for Ubuntu 14.04 & Apache, for another system configuration check https://certbot.eff.org/):

sudo apt-get install git

sudo git clone https://github.com/letsencrypt/letsencrypt /opt/letsencrypt
cd /opt/letsencrypt
sudo ./letsencrypt-auto --apache -d DOMAIN1 -d DOMAIN2

# setup weekly cron autorenewal on Monday at 2:30
sudo crontab -e
# and paste `30 2 * * 1 /opt/letsencrypt/letsencrypt-auto renew >> /var/log/le-renew.log`

If you wish to redirect all traffic domain through HTTPS, do following:

# enable mod_rewrite engine in apache2
sudo a2enmod rewrite

# add to your apache conf file
    # redirect to HTTPS
    RewriteEngine on
    RewriteCond %{HTTPS} off [OR]
    RewriteCond %{HTTP_HOST} ^YOUR_DOMAIN\.COM*
    RewriteRule ^(.*)$ https://YOUR_DOMAIN.COM/$1 [L,R=301]

# reload apache2 configuration
sudo service apache2 reload

Voilà!

Inspired by digitalocean.
Thanks to @sheebang for underlining the importance of renewing the certificates!

Add users accounts in batch to Drupal8

Posted on June 21, 2016 by lpryszcz

I have spent quite some time today, trying to add batch of user accounts intro Drupal8. After all it’s not that difficult, all process is limited to installation of PHP module (that was stripped from Drupal8) and creating new page. Make sure, this page is available only to system administrator!

Install & enable PHP filter

PHP filter was removed from Drupal8 core.

Create new PHP-enabled page (PHP filter)

Below code, defines HTML form and PHP code, that reads the input and store users information. Entries are skipped if given user name is already registered.
Make sure, this page is saved, but UNPUBLISHED! Otherwise, other user will be able to use it and register user accounts!!!

<?php 
if (!empty($_POST)): 
echo "Adding users...<br>";

// read textarea
$text = trim($_POST['users']);
$textAr = explode("\n", $text);
$textAr = array_filter($textAr, 'trim'); // remove any extra \r characters left behind

$i = 0;
foreach ($textAr as $line) {
// explode line
$parts = preg_split('/\s+/', $line);

// check if added
$user = user_load_by_name($parts[0]); //$user_id = $user->uid;
if(!$user){
$i++; 
echo " $i $parts[0] $parts[1] <br>";

$language = \Drupal::languageManager()->getCurrentLanguage()->getId();
$user = \Drupal\user\Entity\User::create();

//Mandatory settings
$user->setPassword(user_password());
$user->enforceIsNew();
$user->setEmail($parts[1]);
$user->setUsername($parts[0]);

//Optional settings
$user->set("init", $parts[1]);
$user->set("langcode", $language);
$user->set("preferred_langcode", $language);
$user->set("preferred_admin_langcode", $language);
$user->activate();
//Save user
$res = $user->save();

// create user profile alias: /user/username -> /user/uid
$uid = $user->get('uid')->value;
$system_path = "/user/$uid";
$path_alias = "/user/$parts[0]";
$path = \Drupal::service('path.alias_storage')->save($system_path, $path_alias, $language);

// sent email
_user_mail_notify('register_no_approval_required', $user);
}
else {
$uid = $user->get('uid')->value;
echo "- $parts[0] already present with uid=$uid!<br>";
} 
}

echo "Added $i users!<br>"; 
?>

<?php else: ?>
    <form action="/add_users" method="post">
<p>Provide new users data, one user per line, in this order: username and email (separated by spaces). </p>
        <textarea name="users" style="width: 400px; height: 200px;"></textarea><br>
        <input type="submit">
    </form>
<?php endif; ?>

Voilà!

Inspired by drupal8.ovh & this discussion & this.

BioInfoExpert

Bioinformatics day-by-day

Category Archives: webserver