Yearly Archives: 2014

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();
    }
}

DNS, Linux, Shell Scripting

CloudFlare and Dynamic DNS

Leave a comment

Recently I changed ISPs.  This generally isn’t a big deal. It didn’t cause any difference in speed or  connection reliability, however my old ISP had, regardless of me not ordering one, given me a static IP address.  I’d taken advantage of this and assigned the IP to a DNS record to give me easy to remember access to my files when I’m away from home.  Anyway when I changed ISPs I discovered my new one changed my IP address every few days, which is an unfortunate thing to discover when you’re not at home.

I took a look at my router and found it only supported DynDns, which I then found out that, apart from to some DLink users, didn’t provide provide a free version of their service to the general public, and I don’t have a DLink router.

The next option was a free alternative to DynDns, there were several, and they all required running some kind of linux demon / cron script, which I would then have to alter my existing A record DNS entry to a CNAME to point to the new dynamic DNS record.

Before setting this up I thought I’d look into my existing DNS provider just incase there was any way of updating DNS records on the fly without having to maintain two separate DNS providers. I found that CloudFlare had an API, which I could update records with. The example provided was a Curl example, which made me wonder, surely I could just write a bash script to update the record with via a cron job. I didn’t want to have to install any major scripting language on my minimal Linux distro, excluding any php, perl etc. solution I’d found that worked with CloudFlare.

I checked CloudFlare’s API rate limiting and found it certainly wouldn’t be limiting for what I was trying to do (it’s approximately 1200 requests every 5 mins if you’re wondering). I then found MyExternalIP that could provide my external IP address with a rate limit that was easy to deal with of roughly 1/s, after which it was just a case of writing the bash script.

I worked a script out using Curl requests to get the information I needed including the specific id of the DNS record I was trying to update and then used sed to get the information I needed out each requests response. I made it compatible with the CloudFlareAPI call I was making that way any parameter passed to the call could be passed by a command line argument to the script itself.

The resultant script I added to my crontab to run every half hour.  Everything worked perfectly so I thought  I’d create a gist at github so others could take advantage of this:

#!/bin/sh
#
# CloudFlare Dynamic DNS
#
# Updates CloudFlare records with the current public IP address
#
# Takes the same basic arguments as A/CNAME updates in the CloudFlare v4 API
# https://www.cloudflare.com/docs/client-api.html#s5.2
#
# Use with cron jobs etc.
#
# e.g.
#
# manually run:
# cloudflare_dyn_dns.sh -key 404613183ab3971a2118ae5bf03d63e032f9e -email test@example.com -zone example.com -name extra
#
# cronjob entry to run every 5 minutes:
# */5 * * * * /path/to/cloudflare_dyn_dns.sh -key 404613183ab3971a2118ae5bf03d63e032f9e -email test@example.com -zone example.com -name extra >> /path/to/cloudflare_dyn_dns.log
#
# will both set the type A DNS record for extra.example.com to the current public IP address for user test@example.com with the provided API key

key=
email=
zone=
zone_id=
type=A
rec_id=
name=
content=
ttl=1
proxied=false

while [ "$1" != "" ]; do
    case $1 in
        -key )     shift
                   key=$1
                   ;;
        -email )   shift
                   email=$1
                   ;;
        -zone )    shift
                   zone=$1
                   ;;
        -zone_id ) shift
                   zone_id=$1
                   ;;
        -type )    shift
                   type=$1
                   ;;
        -rec_id )  shift
                   rec_id=$1
                   ;;
        -name )    shift
                   name=$1
                   ;;
        -content ) shift
                   content=$1
                   ;;
        -ttl )     shift
                   ttl=$1
                   ;;
        -proxied ) shift
                   proxied=$1
                   ;;
        * )        echo "unknown parameter $1"
                   exit 1
    esac
    shift
done

if [ "$content" = "" ]
then
    content=`curl -s http://myexternalip.com/raw`
    if [ "$content" = "" ]
    then
        date
        echo "No IP address to set record value with."
        exit 1
    fi
fi

if [ "$name" = "" ]
then
    echo "You must provide the name of the record you wish to change."
    exit 1
fi

if [ "$zone" = "" ]
then
    echo "You must provide the domain you wish to change."
    exit 1
fi

if [ "$name" = "$zone" ]
then
    hostname="$name"
else
    hostname="$name.$zone"
fi

command -v host > /dev/null 2>&1
if [ "$?" = "1" ]
then
    command -v nslookup > /dev/null 2>&1
    if [ "$?" = "1" ]
    then
        echo "Cannot find a way to check existing $type record for $hostname"
        exit 1
    fi
    existing_content=`nslookup -type=$type $hostname | awk -F 'Address: ' 'NR==6 { print $2 }'`
else
    existing_content=`host -t $type $hostname | sed -E 's/.+?\s+([^\s]+)$/\1/'`
fi

if [ "$content" = "$existing_content" ]
then
    echo "Existing record value $existing_content is the same as provided content $content. Exiting."
    exit
fi

if [ "$key" = "" ]
then
    echo "You must provide your user API token."
    exit 1
fi

if [ "$email" = "" ]
then
    echo "You must provide your user email."
    exit 1
fi

# Get the zone id for the entry we're trying to change if it's not provided
if [ "$zone_id" = "" ]
then
    zone_response_json=`curl -X GET "https://api.cloudflare.com/client/v4/zones?name=$zone" -H "X-Auth-Email: $email" -H "X-Auth-Key: $key" -H "Content-Type: application/json"`
    zone_id=`echo $zone_response_json | sed -E "s/.+\"result\":\[\{\"id\":\"([a-f0-9]+)\"[^\}]+$zone.+/\1/g"`
    if [ "$zone_id" = "" ]
    then
        echo "Cloudflare DNS Zone id could not be found, please make sure it exists"
        exit 1
    fi
fi

# Get the record id for the entry we're trying to change if it's not provided
if [ "$rec_id" = "" ]
then
    rec_response_json=`curl -X GET "https://api.cloudflare.com/client/v4/zones/$zone_id/dns_records?name=$hostname" -H "X-Auth-Email: $email" -H "X-Auth-Key: $key" -H "Content-Type: application/json"`
    rec_id=`echo $rec_response_json | sed -E "s/.+\"result\":\[\{\"id\":\"([a-f0-9]+)\"[^\}]+\"type\":\"$type\"[^\}]+$hostname.+/\1/g"`
    if [ "$rec_id" = "" ]
    then
        echo "Cloudflare DNS Record id could not be found, please make sure it exists"
        exit 1
    fi
fi

# Update the DNS record
update_response=`curl -X PUT "https://api.cloudflare.com/client/v4/zones/$zone_id/dns_records/$rec_id" -H "X-Auth-Email: $email" -H "X-Auth-Key: $key" -H "Content-Type: application/json" --data "{\"id\":\"$rec_id\",\"type\":\"$type\",\"name\":\"$hostname\",\"content\":\"$content\",\"ttl\":$ttl,\"proxied\":$proxied}"`

success_val=`echo $update_response | sed -E "s/.+\"success\":(true|false).+/\1/g"`
if [ "$success_val" = "true" ]
then
    echo "Record Updated."
else
    echo "Record update failed."
    exit 1
fi