Author: dustin

Naming Your Webpage Download

The traditional way that a webpage provides a download for a user is by either opening it into a new window or redirecting to it. It may also choose to set the “Content-Disposition” response-header with a filename:

Content-Disposition: attachment; filename=your_filename.pdf

This is the common-way. However, this will force a download. What if you just want to present the document to the browser for it to be displayed to the user? Well, it turns out that RFC 2183 (“The Content-Disposition Header Field”) also provides you the “inline” type:

Content-Disposition: inline; filename=your_filename.pdf

This accomplishes what we want; The document will [probably] open in the browser, but, if the user wants to save it, it’ll default to the given filename.

Writing Custom MySQL Functions

Sometimes, if you spend a lot of time living in the database (using SQL routines and functions), you might either find missing functionality or find that you need to interface to other projects or libraries, directly. You might want to call a stronger random-number generator with a better entropy-source. You might want to invoke a YAML library.

MySQL has a couple of ways to add new functions: native functions (using libraries that are statically-linked into the server) and UDFs (“user-defined functions”, using libraries that are dynamically-linked with the server). Essentially the difference is whether you want to package your functionality into the server or whether you’d be willing to build it, copy it into the right place, and then tell MySQL to import it via a “create” query. In the case of the latter, you’ll have to “drop” it later, first, if it needs to be updated.

We’re going to do a quick run-through of how to write a UDF with MySQL Server 5.5 . For more information on “native” MySQL functions, you can look here. Note that we differentiate between C functions and SQL functions below by referring to the C functions as “native” functions. This is not meant to refer to MySQL’s “native” function support, which will not be referred to after this point.

It’s actually quite simple:

  • Define a native “init” function or a native “deinit” function, or both, to setup and teardown your library.
  • Define the main native function to do the work. You’ll get an array of argument types and values, and use a NULL to determine if you were given a NULL for an argument.
  • Set the (*is_null) parameter to 1 if you’re returning a NULL (but you have to indicate this possibility from the native “init” function).
  • You will return the value directly. If you’re returning a string, set the “length” parameter. You will tell MySQL what type you’re returning when you first import the function.
  • You’ll also have to define native “add” and “clear” functions if you’re writing an aggregate function (e.g. COUNT, SUM, etc..). You’ll be writing an accumulator where values are loaded and then evaluated.

Writing the Plugin

#include <mysql.h>
#include <m_string.h>

#ifdef HAVE_DLOPEN

my_bool testfunc_init(
    UDF_INIT *initid, 
    UDF_ARGS *args, 
    char *message);

longlong testfunc(
    UDF_INIT *initid, 
    UDF_ARGS *args, 
    char *is_null,
    char *error);

my_bool testfunc_init(
    UDF_INIT *initid __attribute__((unused)),
    UDF_ARGS *args __attribute__((unused)),
    char *message __attribute__((unused)))
{
    if(args->arg_count != 1)
    {
        strcpy(message, "testfunc must have exactly one argument.");
        return 1;
    }

    // Allow positive or negative integers.

    if(args->arg_type[0] != REAL_RESULT && 
       args->arg_type[0] != INT_RESULT)
    {
        strcpy(message, "testfunc must have an integer.");
        return 1;
    }

    return 0;
}

longlong testfunc(
    UDF_INIT *initid __attribute__((unused)), 
    UDF_ARGS *args,
    char *is_null __attribute__((unused)),
    char *error __attribute__((unused)))
{
    longlong value;

    if(args->arg_type[0] == REAL_RESULT) 
    {
        value = (longlong)*((double *)args->args[0]);
    }
    else //if(args->arg_type[0] == INT_RESULT)
    {
        value = *((longlong *)args->args[0]);
    }

    return value * 2;
}

#endif

This example SQL function obviously just returns the original integer doubled. The difference between an “integer” and a “real” integer is, also obviously, whether or not the value is negative.

Notes:

  • Plugin support can be disabled in the server. You should check for HAVE_DLOPEN to be defined.
  • Reportedly, at least one of the native “init” or “deinit” functions should be defined.

Building is simple:

$ gcc -shared -o udf_test.so -I /usr/local/Cellar/mysql/5.6.16/include/mysql udf_test.c

Using the Plugin

To use the plugin, copy it into your server’s plugin directory. You can determine this from your server’s variables:

mysql> SHOW VARIABLES LIKE "plugin_dir";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| plugin_dir    | /usr/local/Cellar/mysql/5.6.16/lib/plugin/ |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

To import a function (you may have defined more than one):

mysql> CREATE FUNCTION `testfunc` RETURNS INTEGER SONAME 'udf_test.so';
Query OK, 0 rows affected (0.00 sec)

MySQL will install it into its “func” table:

mysql> SELECT * FROM `mysql`.`func`;
+----------+-----+-------------+----------+
| name     | ret | dl          | type     |
+----------+-----+-------------+----------+
| testfunc |   2 | udf_test.so | function |
+----------+-----+-------------+----------+
1 row in set (0.00 sec)

If you need to unload it (or need to update it and unload it before doing so):

mysql> DROP FUNCTION `testfunc`;
Query OK, 0 rows affected (0.00 sec)

Testing

mysql> SELECT testfunc(111);
+---------------+
| testfunc(111) |
+---------------+
|           222 |
+---------------+
1 row in set (0.00 sec)

For more general information, see 24.3.2 Adding a New User-Defined Function. For more information on arguments, see here. For more information on return values and errors, see 22.3.2.4 UDF Return Values and Error Handling.

Installing the Nginx Long-Polling/Comet Module on a Mac Using Homebrew

If you’re developing software on a Mac that’s targeted for use in a Linux environment, you’re not alone. You might be lucky-enough to be working in a scripting language-based project, so the difference between environments isn’t nearly as brutal as it would be if you actually had to perform builds. Still, there is the occasional environmental difference.

One such difference is what baked-in Nginx modules you’ll get on an Ubuntu host versus your Mavericks host. What if you need the same push/Comet module on your Mac that you get when you install the nginx-extras package? This is the nginx-push-stream-module module (there’s at least one other module with a similar name, which is usually an additional source of confusion).

You might think that you need to download the source to that module, potentially have to deal with build-dependencies, clone the standard nginx Homebrew formula, modify it, and build. You’d be wrong.

It’s very simple. After you’ve uninstalled your previous nginx formula, run:

$ brew install nginx-full --with-push-stream-module
==> Installing nginx-full from homebrew/homebrew-nginx
==> Installing nginx-full dependency: push-stream-nginx-module
==> Downloading https://github.com/wandenberg/nginx-push-stream-module/archive/0.4.1.tar.gz
Already downloaded: /Library/Caches/Homebrew/push-stream-nginx-module-0.4.1.tar.gz
🍺  /usr/local/Cellar/push-stream-nginx-module/0.4.1: 75 files, 1.1M, built in 2 seconds
==> Installing nginx-full
==> Downloading http://nginx.org/download/nginx-1.6.2.tar.gz
######################################################################## 100.0%
==> ./configure --prefix=/usr/local/Cellar/nginx-full/1.6.2 --with-http_ssl_module --with-pcre --with-ipv6 --sbin-path=/
==> make
==> make install
...

This requires the nginx-full formula because, besides push-stream-nginx-module, it has a massive number of definitions for third-party modules, whereas the normal nginx formula has very few. Your configuration will be largely preserved from the old Nginx to the new (only the launch configs should change).

For reference, this is the list of modules that nginx-full is configured for:

  def self.third_party_modules
    {
      "lua" => "Compile with support for LUA module",
      "echo" => "Compile with support for Echo Module",
      "auth-digest" => "Compile with support for Auth Digest Module",
      "set-misc" => "Compile with support for Set Misc Module",
      "redis2" => "Compile with support for Redis2 Module",
      "array-var" => "Compile with support for Array Var Module",
      "accept-language" => "Compile with support for Accept Language Module",
      "accesskey" => "Compile with support for HTTP Access Key Module",
      "auth-ldap" => "Compile with support for Auth LDAP Module",
      "auth-pam" => "Compile with support for Auth PAM Module",
      "cache-purge" => "Compile with support for Cache Purge Module",
      "ctpp2" => "Compile with support for CT++ Module",
      "headers-more" => "Compile with support for Headers More Module",
      "tcp-proxy" => "Compile with support for TCP proxy",
      "dav-ext" => "Compile with support for HTTP WebDav Extended Module",
      "eval" => "Compile with support for Eval Module",
      "fancyindex" => "Compile with support for Fancy Index Module",
      "mogilefs" => "Compile with support for HTTP MogileFS Module",
      "mp4-h264" => "Compile with support for HTTP MP4/H264 Module",
      "notice" => "Compile with support for HTTP Notice Module",
      "subs-filter" => "Compile with support for Substitutions Filter Module",
      "upload" => "Compile with support for Upload module",
      "upload-progress" => "Compile with support for Upload Progress module",
      "php-session" => "Compile with support for Parse PHP Sessions module",
      "anti-ddos" => "Compile with support for Anti-DDoS module",
      "captcha" => "Compile with support for Captcha module",
      "autols" => "Compile with support for Flexible Auto Index module",
      "auto-keepalive" => "Compile with support for Auto Disable KeepAlive module",
      "ustats" => "Compile with support for Upstream Statistics (HAProxy style) module",
      "extended-status" => "Compile with support for Extended Status module",
      "upstream-hash" => "Compile with support for Upstream Hash Module",
      "consistent-hash" => "Compile with support for Consistent Hash Upstream module",
      "healthcheck" => "Compile with support for Healthcheck Module",
      "log-if" => "Compile with support for Log-if Module",
      "txid" => "Compile with support for Sortable Unique ID",
      "upstream-order" => "Compile with support for Order Upstream module",
      "unzip" => "Compile with support for UnZip module",
      "var-req-speed" => "Compile with support for Var Request-Speed module",
      "http-flood-detector" => "Compile with support for Var Flood-Threshold module",
      "http-remote-passwd" => "Compile with support for Remote Basic Auth password module",
      "realtime-req" => "Compile with support for Realtime Request module",
      "counter-zone" => "Compile with support for Realtime Counter Zone module",
      "mod-zip" => "Compile with support for HTTP Zip Module",
      "rtmp" => "Compile with support for RTMP Module",
      "dosdetector" => "Compile with support for detecting DoS attacks",
      "push-stream" => "Compile with support for http push stream module",
    }
  end

As a result, you can use a similar command to install each of these modules.

The guy who’s responsible for this is easily worth his weight in donations.

Retrieving Multiple Result-Sets from SQLAlchemy

SQLAlchemy is a great Python-based database client, but, traditionally, it leaves you stuck when it comes to stored-procedures that return more than one dataset. This means that you’d have to either call separate queries or merge multiple datasets into one large, unnatural one. However, there is a way to read multiple datasets but it requires accessing the raw MySQL layer (which isn’t too bad).

This is the test-routine:

delimiter //

CREATE PROCEDURE `get_sets`()
BEGIN
    SELECT
        'value1' `series1_col1`,
        'value2' `series1_col2`;

    SELECT
        'value3' `series2_col1`,
        'value4' `series2_col2`;

    SELECT
        'value5' `series3_col1`,
        'value6' `series3_col2`;
END//

delimiter ;

The code:

import json

import sqlalchemy.pool

def _run_query(connection, query, parameters={}):
    sets = []

    try:
        cursor = connection.cursor()

        cursor.execute(query, parameters)

        while 1:
            #(column_name, type_, ignore_, ignore_, ignore_, null_ok, column_flags)
            names = [c[0] for c in cursor.description]

            set_ = []
            while 1:
                row_raw = cursor.fetchone()
                if row_raw is None:
                    break

                row = dict(zip(names, row_raw))
                set_.append(row)

            sets.append(list(set_))

            if cursor.nextset() is None:
                break

            # nextset() doesn't seem to be sufficiant to tell the end.
            if cursor.description is None:
                break
    finally:
        # Return the connection to the pool (won't actually close).
        connection.close()

    return sets

def _pretty_json_dumps(data):
    return json.dumps(
            data,
            sort_keys=True,
            indent=4, 
            separators=(',', ': ')) + "\n"

def _main():
    dsn = 'mysql+mysqldb://root:root@localhost:3306/test_database'

    engine = sqlalchemy.create_engine(
                dsn, 
                pool_recycle=7200,
                poolclass=sqlalchemy.pool.NullPool)

    # Grab a raw connection from the connection-pool.
    connection = engine.raw_connection()

    query = 'CALL get_sets()'
    sets = _run_query(connection, query)

    print(_pretty_json_dumps(sets))

if __name__ == '__main__':
    _main()

The output:

[
    [
        {
            "series1_col1": "value1",
            "series1_col2": "value2"
        }
    ],
    [
        {
            "series2_col1": "value3",
            "series2_col2": "value4"
        }
    ],
    [
        {
            "series3_col1": "value5",
            "series3_col2": "value6"
        }
    ]
]

Things to observe in the example:

  • The query parameters are still escaped (our parameters have spaces in them), even though we have to use classic Python string-substitution formatting with the raw connection-objects.
  • It’s up to us to extract the column-names from the cursor for each dataset.
  • The resulting datasets can’t be captured as generators, as they have to be read entirely before jumping to the next dataset. Technically, you can yield each dataset, but this has almost no usefulness since you’d rarely be required need to read through them sequentially and you’d only benefit if there were a large number of datasets.
  • The raw_connection() method claims a connection from the pool, and its close() method will return it to the pool without actually closing it.
  • I added pool_recycle for good measure. This is an enormous pain to have to deal with, if you’re new to SA and your connections keep “going away” because MySQL is closing them before SA can recycle them.

REFERENCE: Multiple Result Sets

Configuring Your Private Docker Registry for SSL

This post provides some redundancy since the Docker-provided reference to the example doesn’t have a lot of surface area.

It’s not entirely straight-forward how to configure Nginx to forward requests to your Registry instance, as several options are required, for Registry compatibility.

Starting the Registry (for your reference). In this case, we’re storing our images in S3, and forwarding from port 5001 on the host system to 5000 on the Docker container:

sudo /usr/local/bin/docker run 
    -d 
    -e SETTINGS_FLAVOR=s3 
    -e AWS_BUCKET=deploy-docker_images 
    -e STORAGE_PATH=/registry 
    -e AWS_KEY=<your AWS access-key> 
    -e AWS_SECRET=<your AWS secret-key> 
    -e SEARCH_BACKEND=sqlalchemy 
    -p 5001:5000 
    registry

This is the Nginx config, with help from the Docker example:

server {
        listen 5000;
        server_name localhost;

        ssl on;
        ssl_certificate /etc/ssl/certs/your.certificate.pem;
        ssl_certificate_key /etc/ssl/private/your.private_key.pem;

        client_max_body_size 0; # disable any limits to avoid HTTP 413 for large image uploads

        # required to avoid HTTP 411: see Issue #1486 (https://github.com/docker/docker/issues/1486)
        chunked_transfer_encoding on;

        location / {
                proxy_pass http://127.0.0.1:5001;
                proxy_set_header  Host           $http_host;   # required for docker client's sake
                proxy_set_header  X-Real-IP      $remote_addr; # pass on real client's IP
                proxy_set_header  Authorization  ""; # see https://github.com/dotcloud/docker-registry/issues/170
                proxy_read_timeout               900;
        }
}

Tool to Identify Supported Ciphersuites for a Given Server

cipherscan

$ ./cipherscan www.google.com:443
...................
prio  ciphersuite                  protocols                    pfs_keysize
1     ECDHE-RSA-CHACHA20-POLY1305  TLSv1.2                      ECDH,P-256,256bits
2     ECDHE-RSA-AES128-GCM-SHA256  TLSv1.2                      ECDH,P-256,256bits
3     ECDHE-RSA-AES128-SHA         TLSv1.1,TLSv1.2              ECDH,P-256,256bits
4     ECDHE-RSA-RC4-SHA            SSLv3,TLSv1,TLSv1.1,TLSv1.2  ECDH,P-256,256bits
5     AES128-GCM-SHA256            TLSv1.2
6     AES128-SHA256                TLSv1.2
7     AES128-SHA                   TLSv1.1,TLSv1.2
8     RC4-SHA                      SSLv3,TLSv1,TLSv1.1,TLSv1.2
9     RC4-MD5                      SSLv3,TLSv1,TLSv1.1,TLSv1.2
10    ECDHE-RSA-AES256-GCM-SHA384  TLSv1.2                      ECDH,P-256,256bits
11    ECDHE-RSA-AES256-SHA384      TLSv1.2                      ECDH,P-256,256bits
12    ECDHE-RSA-AES256-SHA         SSLv3,TLSv1,TLSv1.1,TLSv1.2  ECDH,P-256,256bits
13    AES256-GCM-SHA384            TLSv1.2
14    AES256-SHA256                TLSv1.2
15    AES256-SHA                   SSLv3,TLSv1,TLSv1.1,TLSv1.2
16    ECDHE-RSA-DES-CBC3-SHA       SSLv3,TLSv1,TLSv1.1,TLSv1.2  ECDH,P-256,256bits
17    DES-CBC3-SHA                 SSLv3,TLSv1,TLSv1.1,TLSv1.2
18    ECDHE-RSA-AES128-SHA256      TLSv1.2                      ECDH,P-256,256bits

Certificate: trusted, 2048 bit, sha1WithRSAEncryption signature

You can also get a JSON result:

$ /cipherscan -j -starttls xmpp jabber.ccc.de:5222
{
    "target": "jabber.ccc.de:5222",
    "date": "Sat, 19 Apr 2014 11:40:40 -0400",
    "ciphersuite": [
        {
            "cipher": "DHE-RSA-AES256-SHA",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "DH,1024bits"
        },
        {
            "cipher": "AES256-SHA",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "None"
        },
        {
            "cipher": "EDH-RSA-DES-CBC3-SHA",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "DH,1024bits"
        },
        {
            "cipher": "DES-CBC3-SHA",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "None"
        },
        {
            "cipher": "DHE-RSA-AES128-SHA",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "DH,1024bits"
        },
        {
            "cipher": "AES128-SHA",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "None"
        },
        {
            "cipher": "RC4-SHA",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "None"
        },
        {
            "cipher": "RC4-MD5",
            "protocols": [
                "SSLv3",
                "TLSv1"
            ],
            "pubkey": [
                "2048"
            ],
            "sigalg": [
                "sha1WithRSAEncryption"
            ],
            "trusted": "False",
            "pfs": "None"
        }
    ]
}