Author Archives: miraenda

Database Disaster Condition Testing

phpBB3 sites tested:

MariaDB => http://endar.org/mysql (converted using WHM’s MySQL/MariaDB Upgrade area from MySQL 5.6)
MySQL => http://endar.org/mysql
SQLite => http://the4400.center/sqlite3
PostgreSQL => http://miraenda.me/postgres

Disaster Conditions:

Of note, points listed after each RDBMS in ( ).

1) Hard reboot / crash

a) Method

# ps x | awk {'print $1'} | xargs kill

Then used Digital Ocean to power off the machine as well, then Power back up.

b) Results

MariaDB = Site working and no corruption after server back online. (2)
MySQL = Site working and no corruption after server back online. (2)
PostgreSQL = Site working and no corruption after server back online. (2)
SQLite3 = Site working and no corruption after server back online. (2)

2) Killing SQL process directly*

a) Method

# ps -o pid= -u $USER | xargs kill -9

Replacing $USER with the username of the database service such as mysql or postgres.

b) Results

MariaDB = Site working and no corruption after restart (2)
MySQL = Site working and no corruption after restart (2)
PostgreSQL = Site working and no corruption after restart (2)
SQLite3 = Couldn’t kill processes, so giving 2 by default* (2)

*SQLite3 doesn’t run as a daemon, so this isn’t possible for SQLite3

3) Filling up a partition’s file system

a) Method

# fallocate -l 12G foo

b) Results

MariaDB = Can view site and pages and even create a user (2)
MySQL = Cannot create user or view posts, but can see main site page (1)
PostgreSQL = Cannot create user or view posts, but can see main site page (1)
SQLite3 = Error on main page after 3-5 minutes (0)

4) Filling up a partition’s inodes

a) Method

# while [ $(df --output=iavail / | tail -n1) -gt 0 ]; do
touch $((i++));
done

b) Results

MariaDB = Continued to work even after restart (2)
MySQL = Continued to work even after restart (2)
PostgreSQL = Continued to work even after restart (2)
SQLite3 = Failed to load posts (0)

5) Read-only File System

a) Method

# chmod 444 /var/lib/mysql
# chmod 444 /var/lig/pgsql
# chmod 444 /home/the4400/sqlite.sql

b) Results

MariaDB = The site quit working with error establishing a database connection error (0)
MySQL = The site quit working with error establishing a database connection error (0)
PostgreSQL = The site continued to work until “systemctl restart postgresql” was issued, then it showed a connection error. (1)
SQLite3 = The site quit working with SQL error “attempt to write a readonly database” (0)

6) Invalid ownership permissions

a) Method

# chown -R root.root /var/lib/mysql
# chown -R root.root /var/lib/pgsql
# chown root.root /home/the4400/sqlite.sql

MariaDB = Forum worked to view pages until MySQL restarted, then it fails to start. Had to move /var/lib/mysql/tc.log entirely then restart before it would work after fixing ownership permissions. Giving negative point for this extra step as the error was found only in the /var/lib/mysql/hostname.err log file. (-1)
MySQL = Forum worked to view pages until MySQL restarted, then it fails to start (1)
PostgreSQL = Forum ceased working immediately. Site worked again after fixing permissions without a restart (0)
SQLite3 = Forum worked to view pages even with ownership changed to another user’s ownership. Functions like logout, login, registration failed (1)

7) Insert Bad Data into sql files

a) Method

MariaDB and MySQL
# echo "11111" >> /var/lib/mysql/ibdata1
# systemctl restart mysql

# echo "11111" >> /var/lib/mysql/endar_pp3/pp3_users.ibd
# systemctl restart mysql

PostgreSQL
# echo "11111" >> /var/lib/pgsql/data/base/1/pg_filenode.map
# systemctl restart postgres

# echo "11111" >> /var/lib/pgsql/data/base/16392/pg_internal.init
# echo "11111" >> /var/lib/pgsql/data/base/16392/pg_filenode.map
# echo "11111" >> /var/lib/pgsql/data/base/16392/16602
# systemctl restart postgres

SQLite3
# echo "11111" >> /home/the4400/sqlite.sql

b) Results

MariaDB = Site quits working as do all other sites with InnoDB on the server. No error on startup noted. Without a backup, there is no way to get the sites to working again under InnoDB. (-1)
MySQL = Site quits working as do all other sites with InnoDB on the server. No error on startup noted. Without a backup, there is no way to get the sites to working again under InnoDB. (-1)
PostgreSQL = Site continues to work no matter which files have bad values placed into them. (2)
SQLite3 = Site continued to work without issues. (2)

8) Insert Bad Values into configuration file

a) Method

MariaDB and MySQL
# echo "badvalue=this" >> /etc/my.cnf
# systemctl restart mysql

PostgreSQL
# echo "badvalue=this" >> /var/lib/pgsql/data/postgresql.conf
# systemctl restart postgres

SQLite3
# echo ".badvalue=this" >> /home/the4400/.sqliterc
# sqlite3 sqlite.sql -init /home/the4400/.sqliterc

b) Results

MariaDB = No warning on startup that it is failing even though it fails to start properly. Error is logged to /var/lib/mysql/hostname.err file. Site has an error about database connectivity. (-1)
MySQL = No warning on startup that it is failing even though it fails to start properly. Error is logged to /var/lib/mysql/hostname.err file. Site has an error about database connectivity. (-1)
PostgreSQL = Fails during startup with “systemctl status postgresql.service” indicated to check for error. Site shows an error about database connectivity. (0)
SQLite3 = Doesn’t impact the forum loading as these values only impact sqlite3 working for CLI handling of database checking. It errors and simply doesn’t load the file if you have bad values. The site still works without issues. (2)

screen-shot-2016-09-27-at-9-06-30-am