How to Make mod_rewrite Do What You Mean

A Yahoo! engineer once said to me, “The most important feature of Apache httpd is mod_rewrite. It allows large sites like ours that have frequent content structure changes to be controlled with redirects.”

But mod_rewrite seems as stubborn as a mule sometimes …

A technique that I use is to take a cookbook entry and work backwards. Instead of trying to make everything work at once, hard-code in a result I want and then gradually generalize the rules to handle more matches

Posted in Open Source, Tech | Leave a comment

Developing Twitter Apps with Perl

Most of the Twitter-related blogs and sample code on the web are obsolete, so I wrote this overview for 2015.

The most important things to know are:

  1. Twitter API 1.1 is required using OAuth and SSL
  2. your must register with Twitter for two sets of tokens:
    1. consumer credentials (API key).
    2. Twitter-approved OAuth tokens. To register for these, you need a Twitter account with an associated mobile phone number. Since Twitter has a unique constraint on mobile numbers, that means you have to register a new phone number or move an old phone number from an existing account. To move your phone number, just SMS “Start” to 40404 and answer the messages sent back. You need to know the password of the new Twitter account, but not the old account.
  3. The Perl CPAN module I use is Net::Twitter
  4. to do multi-tenant tweeting, use the same consumer key as above but request the OAuth tokens from each client.

The Net:Twitter API calls I use are:

  1. update (tweet)
    use Net::Twitter::Lite::WithAPIv1_1;
    
       my $nt = Net::Twitter::Lite::WithAPIv1_1->new(
           consumer_key        => 'abc',
           consumer_secret     => 'def',
           access_token        => 'ghi',
           access_token_secret => 'jkl',
           ssl => 1,
       );
    
       my $status = 'Hello, world!';
       my $o $nt->update($status);
    
       my $status_id = 0;
       if (defined $o) {
          $status_id = $o->{'id'};
          print "status id=$status_id\n";
       }
    
  2. destroy_status (delete tweet)
       my $o = $nt->destroy_status($status_id);
    

To delete a tweet, you must save the status ID of the tweet. (Likely that means inserting the status ID in a database.) Also, only the account who tweeted can delete the tweet.

Posted in API Programming, Tech | Leave a comment

Upgrading Percona Server 5.5 to 5.6 on CentOS

Percona LogoI like using Percona Server for some projects because you get to see what their clients feel is important for operating MySQL at scale, as reflected in the features that Percona adds. Some examples are fast Innodb log replay and transportable Innodb tablespaces.

A drawback of Percona Server is that they do limited QA on packaging, so I find that the grant tables get in a bad state after using yum update a few times. So I recommend periodically doing a fresh install.

Here’s the steps I used for upgrading from 5.5 to 5.6 on CentOS this weekend. It’s helpful as a checklist for non-DBAs, and as a pre-flight for DBAs so they will know what to expect in advance.

(Nearly all of this applies to upgrading to Percona XtraDB Cluster (Galera) as well. Just change the package names and start the first server with the pxc command.)

  1. read changelog and decide if your app will work with 5.6. For example, timestamp formats have changed since 5.5.
  2. stop apps and monitoring.
  3. backup old databases with mysqldump and capture the old grant commands.

    To mysqldump the MySQL grant tables, you may need the –skip-lock-tables option:

    # mysqldump -h host -u root --skip-lock-tables -p mysql >grants.sql
    
  4. remove Percona packages:
    # yum remove Percona-Server-client-55 Percona-Server-server-55 \
    Percona-Server-shared-55
    
  5. You must rename my.cnf so that yum install will generate the mysql grant tables:
    # mv /etc/my.cnf /etc/my.cnf.old;
    # rm -fr /var/lib/mysql
    # also, comment out deprecated option like table_cache or
    #    the new server will not start
    
  6. Find and remove any files that should have been removed:
    # find / -name Percona
    # find / -name mysql
    
  7. Install new packages:
    # yum install Percona-Server-client-56 Percona-Server-server-56 \
    Percona-Server-shared-56
    
  8. Can move my.cnf back now:
    # mv /etc/my.cnf/old /etc/my.cnf
    
  9. # service mysql start # if it doesn't start, read mysqld.err
    
  10. if the grant tables were not created, you can do this:
    # chown mysql:mysql /var/lib/mysql
    # chgrp mysql /var/lib/mysql
    # mysql_install_db --user=mysql --ldata=/var/lib/mysql
    
  11. if you use replication, add grant on master now:

    mysql> grant replication slave on *.* to 'repl'@'slave-ip' identified by 'pw';
    mysql> flush logs;
  12. Post-install commands which only need to be run once on the master:
    mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
    mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
    mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
    
  13. If you use replication, on the slave:
    mysql> change master to master_host='master-ip',
       master_user='repl',
       master_password='pw',
       master_log_file='my-binlog-prefix.000002',
       master_log_pos=4;
    mysql> start slave;
    mysql> show slave status\G
    
  14. restore your database backups (not the mysql database tables, as their format changes over time. Use GRANT statements instead.)
Posted in Linux, MySQL, Open Source, Oracle, Tech | Leave a comment

ArXiv Paper on Using Additive Noise to Determine Cause and Effect

One doesn’t see many scientific papers that are immediately useful, but this one qualifies:

arXiv.org: Distinguishing Cause From Effect Using Observational Data: Methods And Benchmarks
Physics arXiv Blog: Cause And Effect: The Revolutionary New Statistical Test That Can Tease Them Apart

“They say the additive noise model is up to 80 per cent accurate in correctly determining cause-and-effect” … “in the very simple situation in which one variable causes the other.”

Posted in Tech | Leave a comment

HAProxy and SSL SNI Support

The HAProxy 1.5 branch has SSL support built-in, so you don’t need stunnel or other SSL-termination helpers now.

I tested SSL Server Name Indication (SNI) functionality with HAProxy 1.5.10, OpenSSL 1.0.2 and two SSL certificates (GeoTrust from Namecheap.com) on 3 Dell 1950 servers and it worked fine for me. HAProxy ran on one server and the others ran Apache HTTPD using virtual servers for each domain being load balanced.

SNI lets you use one IP address with multiple SSL certificates. For each site, you just create a single PEM file with key, crt and chain entries, in that exact order. Using SNI reduces the number of IP addresses you need, and also avoids having a separate stunnel process for each SSL certificate.

SNI works fine with most desktop browsers since 2003, but not IE8 or older on Windows XP. Also, custom client applications and embedded devices that use SSL may be confused with SNI. I noticed that the Nagios plugin cannot see the second certificate, even with -H hostname specified.

For GeoTrust certs for Apache+OpenSSL as of Feb. 15 2015, the correct installation of the 4 certificates is:

cat server.key server.crt rapidssl_cabundle.crt >server.pem

-----BEGIN RSA PRIVATE KEY-----
-----END RSA PRIVATE KEY-----
-----BEGIN CERTIFICATE-----
-----END CERTIFICATE-----
INTERMEDIATE CA:
---------------------------------------
-----BEGIN CERTIFICATE-----
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
-----END CERTIFICATE-----

Troubleshooting:

  1. note that haproxy prints a general error message of “unable to load SSL private key from PEM file”, regardless if it’s a missing filename, incorrect file permissions or incorrectly formatted certificates, so check the filename and permissions first.
  2. ensure there’s no malformed header (dashed) lines and delete blank lines
  3. OpenSSL certs are in PEM format by default, so there’s no need to convert them. (Usually it’s Windows users who have to do PEM conversion.)
  4. After haproxy starts, it’s important to verify the certificate chain. Use sslchecker.com and use the Chain Details button to see the intermediate and root certificate names and dates.

A new section in haproxy.cfg is needed to listen on port 443:

frontend https-in
    bind *:443 ssl crt /etc/ssl/server1.pem crt /etc/ssl/server2.pem
    reqadd X-Forwarded-Proto:\ https
    default_backend application-backend

For CentOS 5 users, SNI requires you to build haproxy from source with a newer version of OpenSSL statically. The README tells you how to do that. Use the latest version of OpenSSL to avoid errors about missing function names.

cd openssl-1.0.2
export STATICLIBSSL=/tmp/staticlibssl
make clean
./config --prefix=$STATICLIBSSL no-shared
make && make test && make install
cd ../haproxy-1.5*
make clean
make TARGET=linux26 USE_OPENSSL=1 SSL_INC=$STATICLIBSSL/include SSL_LIB=$STATICLIBSSL/lib ADDLIB=-ldl
service haproxy stop
make install
service haproxy start

For those upgrading from previous versions of haproxy, old .cfg files should still work, but warnings are emitted for timeout settings, as they have been renamed in 1.5:

service haproxy start
[...]
   | While not properly invalid, you will certainly encounter various problems
   | with such a configuration. To fix this, please ensure that all following
   | timeouts are set to a non-zero value: 'client', 'connect', 'server'.

1.5 has only been GA since June 2014, so ensure you test it adequately for your requirements and keep an eye on the changelog.

SO: Configure multiple SSL certificates in Haproxy
HAProxy and SNI-based SSL offloading with intermediate CA
blog.haproxy.com: Enhanced SSL load-balancing with Server Name Indication (SNI) TLS extension
blog.haproxy.com: How to get SSL with HAProxy getting rid of stunnel, stud, nginx or pound

sslmate.com: Buy SSL certs from the command line
How exactly does AES-NI work?

Posted in Linux, Open Source, Tech | Leave a comment