I’ve always compiled my software on servers from source. Call me a perfectionist / idiot, whatever, but I do it for several reasons, including making sure I’ve got the latest features and security patches, as well as being able to tailor the software I use on servers so it’s a minimal install of the software, still providing what I require.
Anyways, I’ve just found a quirk with getting MariaDB to starting up. As far as I could tell from the provided systemd .service file, it was being run by the user that owned the data directory I’d configured at compile time, this directory was specified correctly in the my.cnf, and when I tried to run the service manually it worked. but something was blocking it when it came to running it via systemctl.
I was receiving the following messages:
Sep 25 22:37:58 centos7 systemd: Starting MariaDB database server...
Sep 25 22:37:58 centos7 mysqld: 2016-09-25 22:37:58 139993404594304 [Note] /usr/local/bin/mysqld (mysqld 10.1.17-MariaDB) starting as process 7978 ...
Sep 25 22:37:58 centos7 mysqld: 2016-09-25 22:37:58 139993404594304 [Warning] Can't create test file /usr/local/mariadb/data/centos7.lower-test
Sep 25 22:37:58 centos7 mysqld: 2016-09-25 22:37:58 139993404594304 [ERROR] mysqld: File './mysql-bin.index' not found (Errcode: 30 "Read-only file system")
Sep 25 22:37:58 centos7 mysqld: 2016-09-25 22:37:58 139993404594304 [ERROR] Aborting
Sep 25 22:37:58 centos7 systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE
Sep 25 22:37:58 centos7 systemd: Failed to start MariaDB database server.
Sep 25 22:37:58 centos7 systemd: Unit mariadb.service entered failed state.
Sep 25 22:37:58 centos7 systemd: mariadb.service failed.
I was inspecting the provided mariadb.service file, trying to spot anything that might indicate why the user couldn’t write to a folder it owned, when I came across this:
# Prevent writes to /usr, /boot, and /etc
Recently whilst writing some code I needed to pivot rows to columns in a MySQL results set. Now with some other DB providers this is standard functionality. Oracle (of which I have previous experience of implementing pivot with), PostgreSQL and MS SQLServer all provide this. Unfortunately MySQL in it’s ultimate wisdom has never implemented this feature. This lead to one of two possibilities:
Implement two separate queries and combine the results together in a PHP foreach loop
As it supports them, a MySQL Stored Procedure
The first option was obviously not preferable, it’s far slower than getting the database to do the work. This lead to the second being my obvious preferred option. My main experience of writing stored procedures and functions in SQL is using PL/SQL with an Oracle DB. I have in general used MySQL on much lighter systems, however have in the odd situation required a stored procedure, so thought why not. The solution was to use a cursor in the stored procedure looping through said cursor to generate a SQL statement, then executing that as a prepared statement to return the results set required. This is then called from PHP by using the MySQL “CALL” functionality.
Here’s an example of the stored procedure I produced:
Whilst running multiple queries in one call, via the PHP MySQL PDO driver, I noticed that although PDO was behaving as if all the queries had executed successfully, the entries in the database weren’t all appearing that should have. Obviously something was happening that wasn’t being reported. PDO was setup to throw Exceptions on any errors when the SQL executed so there was clearly a bug somewhere.