Category Archives: MySQL

Linux, MariaDB, MySQL, SystemD

MariaDB 10.1 and SystemD start issue

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
ProtectSystem=full

So there you have it. I’d configured it to have the data directory under the mariadb program directory under /usr. MariaDB was allowing me to configure a data directory at compile time that it knew it wouldn’t be able to boot the daemon and write to (if I used systemd, which, lets face it, is becoming the norm, at-least on RedHat based distros). There’s a lot of guides on the net that as an example have the data directory created under /usr/local/mysql/data. This obviously isn’t an excuse. Using Javascript as an example there’s a lot of bad guides on the internet that produce quick hacks rather than quality long term code.

I actually both understand and agree with the systemd setting especially when you take into account the Filesystem Hierarchy Standard (FHS)

Possible Solutions:

  • Move the data directory to somewhere else. According to the FHS this should lie under /var/lib/mariadb
  • Set the ProtectSystem value to false

I’d recommend the former

MySQL

MySQL & Pivot functionality

Leave a comment

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:

  1. Implement two separate queries and combine the results together in a PHP foreach loop
  2. 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:

DROP PROCEDURE IF EXISTS pivot_user_preferences;

DELIMITER $$

CREATE PROCEDURE pivot_user_preferences(IN user_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE definition_label CHAR(255);
    DECLARE label_cursor CURSOR FOR SELECT DISTINCT label FROM example_db.user_preferences;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET @user_id = user_id;
    SET @sql_statement = 'SELECT u.* ';

    OPEN label_cursor;

    read_loop: LOOP
        FETCH label_cursor INTO definition_label;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql_statement = CONCAT(@sql_statement, ', GROUP_CONCAT(IF(up.label = \'', definition_label, '\', up.value, NULL)) AS ', definition_label);
    END LOOP;

    CLOSE label_cursor;

    SET @sql_statement = CONCAT(@sql_statement, ' FROM users u LEFT JOIN user_preferences up ON u.id = up.user_id WHERE u.id = (?) GROUP BY id');
    PREPARE stmt FROM @sql_statement;
    EXECUTE stmt USING @user_id;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

MySQL, PHP, Web Development

PHP MySQL PDO and the Multiple Queries Bug

Leave a comment

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.

Unfortunately it’s a bug in PHP and it seems this bug has existed since at-least April 2012: https://bugs.php.net/bug.php?id=61613

I had three choices:

  1. manually split the queries up into different statements and replace the bind variables with their relevant values (which wasn’t feasible as there were hundreds),
  2. Use str_replace to replace the bind_variables with their relevant quoted values and run the query at the sql prompt (not real world situation due to null values etc.).
  3. Write some code to split the statement into individual queries and  execute said queries individually with the relevant bind variables.

The following is a full example of how to split a long string of multiple sql queries and the relevant bind variables for that query into their separate sections, and execute them one at a time:

<?php
$myConn = new PDO(
	'mysql:host=localhost;dbname=dbname',
	'username',
	'password'
);
$myConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$multiSqlStatement = '
INSERT INTO users (username) VALUES (:username);
INSERT INTO groups (name, type) VALUES (:group_name, :group_type);
INSERT INTO logs (entry) VALUES (NOW());
';
$bindVariables = array(
	':group_type' => 'this_type',
	':username' => 'my_username',
	':group_name' => 'Other'
);

$tempSql = preg_replace('/[\n\r\s]+/', ' ', $multiSqlStatement);
$tempSqlParts = explode(';', $tempSql);
foreach ($tempSqlParts as $tempSqlPart) {
    $tempSqlPart = trim($tempSqlPart);
    if (!empty($tempSqlPart)) {
        $tempBindVars = (0 < preg_match_all('/\:[a-z0-9_]+/i', $tempSqlPart, $matches))
        	? array_intersect_key($bindVariables, array_flip($matches[0]))
        	: array();
        $tempStatement = $myConn->prepareStatement($tempSqlPart);
        $tempStatement->execute($tempBindVars);
        $tempStatement->closeCursor();
    }
}