Information is Contagious

IPv6 Functions for MySQL

2010-04-05T16:13:00.000-07:00

MySQL has inet_aton and inet_ntoa functions for converting IPv4 addresses to an unsigned 32 bit integer value, and back into dotted decimal format. Since IPv6 is becoming more popular, I decided to create a couple functions for handling IPv6 addresses as well. In this case, we want to convert the friendly ::0:53D6:0001 style addresses to a 16 byte binary value that is easier to store. But we also want to extract them in a human readable format as well. To do that we now have inet_aton6() and inet_ntoa6().

Examples:


mysql> SELECT inet_aton6('0:0:0:0:0:0:af00:b001');
+------------------------------------------+
|      inet_aton6('0:0:0:0:0:0:af00:b001') |
+------------------------------------------+
| 000000000000000000000000AF00B001         |
+------------------------------------------+

mysql> SELECT inet_ntoa6(unhex('000000000000000000000000AF00B001'));
+-------------------------------------------------------+
| inet_ntoa6(unhex('000000000000000000000000AF00B001')) |
+-------------------------------------------------------+
| 0:0:0:0:0:0:af00:b001                                 |
+-------------------------------------------------------+

When storing in a table, it's recommended to use BINARY(16), and use UNHEX() to convert the hexidecimal string representation into a binary string. inet_aton6 should do that itself, but returning binary strings from a udf causes errors.


CREATE TABLE `address` (
  `ipv6` binary(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> INSERT INTO address (ipv6) VALUES (unhex(inet_aton6('0:0:0:0:0:0:af00:b001')));

mysql> SELECT inet_ntoa6(ipv6) FROM address;
+-----------------------+
| inet_ntoa6(ipv6)      |
+-----------------------+
| 0:0:0:0:0:0:af00:b001 |
+-----------------------+

Enjoy!

Qsh.pl: distributed query tool

2010-03-08T12:48:00.000-08:00

I've written quite a few tools over time to connect to many mysql servers and run queries. Most of these have been pretty specific to a small set of tasks such as running an alter across many servers. Any sysadmin that is in charge of many servers is probably familiar with dsh, and as I was using recently I realized how all those specific tools I've written for mysql could be generalized into a dsh like tool. Thus, Qsh.pl was born! (download at launchpad)

Usage should be familiar to anyone who has used dsh before, it even will read group files made for dsh in /etc/dsh/group/or /usr/local/etc/group/.

Here's an example where this tool was quite useful. I was getting a query error for SHOW GLOBAL STATUS. This was a curious result since we're running mysql 5.0 everywhere. So what better way to find out which machines are complaining than just run it everywhere:


# qsh.pl -Mcg all_servers --user root --ask-pass --db=test -e 'SHOW GLOBAL STATUS'  2>error.log
{snip ... lots of output}
Done. Total time 2.919

My group file for all_servers includes 120 mysql servers, executing that query on all of them took a total of 2.9 seconds, not bad. I also redirected stderr to a file, so any query errors are easy to find:

cat error.log
myserver1: Query Error (1064) You have an error in your SQL syntax ...
myserver2: Query Error (1064) You have an error in your SQL syntax ...

Ok, we found all the servers that return an error, why do they complain?

# qsh.pl -Mcm myserver1,myserver2,myserver3 --user root --ask-pass --db=test -e 'SELECT VERSION()'
Password:
myserver1: +-----------------+
myserver1: |       VERSION() |
myserver1: +-----------------+
myserver1: | 4.1.21-standard |
myserver1: +-----------------+
myserver3: +----------------------------+
myserver3: |                  VERSION() |
myserver3: +----------------------------+
myserver3: | 5.0.66a-enterprise-gpl-log |
myserver3: +----------------------------+
myserver2: +-----------------+
myserver2: |       VERSION() |
myserver2: +-----------------+
myserver2: | 4.1.21-standard |
myserver2: +-----------------+
Done. Total time 0.063

Ooops! That's right, still a few old versions for legacy reasons.

That's just one example of how I used it. There are probably lots of use cases out there, but since it's new I'm still learning to rely on it. It certainly makes things faster when I can think about querying many servers at once, and is a more efficient way to work when dealing with many machines. It might be useful for:

+ comparing explain plan between many machines
+ altering large tables across many slaves, before promoting one to master.
+ grabbing status output from many machines to feed into awk or sed

Three Types of Sets: Performance Comparrisson

2010-02-01T12:00:00.000-08:00

In a previous post I talked about how to take a classic SQL anti-pattern (storing lists as CSV in a single field), normalize it, and how to write queries for it.

There are two other ways to handle sets of values: The built in SET column type, and using a INT/BIGINT column as a bitfield.

The mysql set data type provides a convenient way of working with a list of values, where each field could represent multiple simultaneous values. It's really just a wrapper around bitmask operations -- it lest you work with them using text as well as numerical values.

But how do they perform? My quick test showed that aside from a couple edge cases, they all preformed about the same speed. In fact it was surprising to me to see that the bitmask type queries were really not that much faster than the more normalised table structure with multiple rows. I would have guessed that the extra grouping operations would have added significant overhead, but they didn't.

Query	Avg. Time
SELECT * from bitmask WHERE val&3 LIMIT 1000	0.0023512
SELECT * from bitmask WHERE val&3=3 LIMIT 1000	0.0149182
select * from settable where s&3=3 LIMIT 1000	0.0155686
select id FROM normalset group by id HAVING sum(val='yellow') and sum(val='cyan') LIMIT 1000	0.016244
select id, BIT_OR(CASE val WHEN 'cyan' THEN 1 WHEN 'yellow' THEN 2 ELSE 0 END) as bitvals, count(*) FROM normalset WHERE val in ('cyan','yellow') GROUP BY id HAVING bitvals=3 LIMIT 1000	0.0163958
select * from settable where s like '%cyan%yellow%' LIMIT 1000	0.0333536

Notice that that the top query in the list is an order of magnitude faster than the others, however it doesn't actually answer the same question as the other queries. I included it anyway because it was a curious result -- apparently bit manipulation operations alone are very very fast in mysql, but just adding an equality test slows the query down considerably. The cost of the equality test is much much higher than I would have anticipated. It would be an interesting project to track down exactly why that is.

You can see that working with the SET datatype using string values has the worst performance. When you use it like an ordinary bitmask, then the performance is identical to the other, faster methods (no surprise there.) Working with it using text values involves some internal conversions. IMO this means ordinary bitmasks are the clear winner over the SET datatype -- the SET datatype requires an ALTER TABLE to add new values, and the the convenience of using text values should be avoided because they're slow. If you need maximum flexibility, the more normalized structure seems to preform just as well.

To generate the test data, I created about 200k unique items and randomly assigned any of a set of 4 values to each item. This ended up with about 400k associations, so each item had on average two values out of the set. Statements follow:


-- creating SET table, generating values
create table settable (id serial, s set('cyan','yellow','magenta','black'), key (s) );
insert into settable (s) values ( 'cyan,yellow' ), ( 'yellow,magenta'), ( 'cyan,black,yellow'), ( 'magenta' );

-- repeat until desired # of rows reached:
insert into settable (s) select truncate(rand()*15+1,0) from settable;

-- creating normalized set, copy values from settable
create table normalset (id int not null default 0, val char(8), key(id) );
insert into normalset SELECT id, 'cyan' FROM settable WHERE s like '%cyan%';
insert into normalset SELECT id, 'yellow' FROM settable WHERE s like '%yellow%';
insert into normalset SELECT id, 'magenta' FROM settable WHERE s like '%magenta%';
insert into normalset SELECT id, 'black' FROM settable WHERE s like '%black%';

-- create bitmask, copy values from settable
CREATE TABLE bitmask ( id serial, val int unsigned NOT NULL default '0', UNIQUE KEY (id), KEY (val) );
insert into bitmask SELECT id, s+0 FROM settable;

How to Fix the Comma Separated List of Doom

2009-12-29T10:41:00.000-08:00

This is a solution that I've seen a few people have trouble with now, so I want to make more people aware of it:

Say you have some items that can have any of a set of associated values. These could be a list flags, it could be keywords or tags. This is often seen as the anti-pattern known as "Comma Separated List of Doom" (CSLoD). Naive developers will often create something like the following:


create table cslod ( id serial, vals varchar(255) );
insert into cslod values (NULL, "cyan,yellow" );
select * from cslod;
+----+-------------+
| id | vals        |
+----+-------------+
|  1 | cyan,yellow |
+----+-------------+

I know that most people reading this already understand this is bad but let me re-iterate: Please please please don't do this! It will be very very difficult to write meaningful queries later.

The natural solution is to create separate rows for each value in the list:


create table normalset (id int unsigned not null default 0, val varchar(12) not null default '', primary key (id, val) );
insert into normalset VALUES (1, "cyan"), (1, "yellow" );
select * from normalset;
+----+--------+
| id | val    |
+----+--------+
|  1 | cyan   |
|  1 | yellow |
+----+--------+

Ok, to be truly normalised, val should be an id with a foreign key to another unique list of values, but it's easier to illustrate with this not-quite-perfect structure.

Now, using the improved structure, how would we write a query that retrieves all ids that have *both* cyan and yellow as values?


insert into normalset VALUES (2, "cyan"), (2, "black" ), (3, "magenta"), (4,"cyan"), (4, "magenta"), (4, "yellow");

select * from normalset;
+----+---------+
| id | val     |
+----+---------+
|  1 | cyan    |
|  1 | yellow  |
|  2 | cyan    |
|  2 | black   |
|  3 | magenta |
|  4 | cyan    |
|  4 | magenta |
|  4 | yellow  |
+----+---------+

We want to return 1 and 4:


SELECT id FROM normalset GROUP BY id HAVING SUM(val='cyan') AND SUM(val='yellow');
+----+
| id |
+----+
|  1 |
|  4 |
+----+

Simple! The trick is to use GROUP BY, and then you can write conditions based on aggregates and filter them in the HAVING clause. There are a few other ways to obtain the same result:


SELECT id, GROUP_CONCAT(val order by val) as theset, count(*) FROM normalset GROUP BY id HAVING theset like '%cyan%yellow%'

SELECT id, BIT_OR(CASE val WHEN 'cyan' THEN 1 WHEN 'yellow' THEN 2 ELSE 0 END) as bitvals, count(*) FROM normalset GROUP BY id HAVING bitvals=3

The first form can be easily used to find other conditions, such as all rows that have cyan as a value but NOT yellow:


SELECT id FROM normalset GROUP BY id HAVING SUM(val='cyan') AND NOT SUM(val='yellow');
+----+
| id |
+----+
|  2 |
+----+

I have also been comparing these methods to using the SET datatype, and plain ol' bitmasks. I'll add a performance comparison of various methods in another post.

Restore a Single Table From mysqldump

2009-11-24T18:10:00.001-08:00

Ok, this is inspired by another post on the same subject. I would have just commented, but there was a required signup. Ew.

While the method there with juggling grants works fine, I'd like to highlight another way that I think is much simpler: use grep & sed to create a file which contains only the table you want to restore.


mysql> show tables;
+------------------+
| Tables_in_gtowey |
+------------------+
| t1               |
| t2               |
| t3               |
+------------------+
3 rows in set (0.00 sec)

# mysqldump gtowey > dump.sql

Given 3 tables in a mysqldump file, lets restore just t2


# grep -n 'Table structure' dump.sql
19:-- Table structure for table `t1`
40:-- Table structure for table `t2`
61:-- Table structure for table `t3`

Now just use the line numbers to extract the table with sed:

sed -n '40,61 p' dump.sql > t2.sql

That's it, you have everything you need with no cleanup of mysql grants to do afterward.

Simple Test Case Techiques

2009-10-05T17:06:00.000-07:00

Ok, I know that most of you reading this would think of this as painfully obvious, but it's also clear that a lot of beginning mysql users are timid about actually diving in and discovering answers. In an effort to answer fewer simple questions, I'm hoping to get this out to more people who may be beginners with mysql.

The most common barrier seems to be "I don't have a test environment!" This is easily remedied:

1. Even if your production enviroment is linux, you can still install mysql on your windows-based workstation to play around with. The installation on any environment can be done in minutes, so lack of SQL is not a good excuse.

2. The perfect companion for testing mysql is MySQL Sandbox. You can even test replication setups!

3. Even if you just have a production environment, small test cases can be done inside a different database and are unlikely to affect production traffic.

Techniques for Test Cases

Say you have a question about valid dates, lets say you're unsure about how NULL works with DATETIME types, and you don't want to run an update on your production data to test it.

Remember that you can pare down a question like this to the most basic elements. You just need three things:

1. A table with a date column
2. A row with a valid date
3. An update statement

1. CREATE TABLE d ( a DATETIME);
2. INSERT INTO d VALUES (NOW());
3. SELECT * FROM d; UPDATE d SET a=NULL; SELECT * FROM d;

There! The trick is to remember how simple the create table syntax is, and how easy it is to put data into it. All kinds of extremely simple tables can be created, with no fuss at the command line. I think many beginners get used to seeing more complex CREATE TABLE statements full of lots of columns and options, and don't realize just how much is optional.

There are a few things I tend do do as a matter of habit:

1. Keep all identifiers down to one or two letters -- reduced typing makes it easier to write these cases.

2. Columns are usually: a,b,c,d or x,y,z -- again, don't worry about names, keep them simple!

CREATE TABLE t1 (a INT, b VARCHAR(32), c DATE ); -- very simple with 3 different data types to play with.

3. SERIAL is a great shortcut -- although I tell everyone that BIGINT is a terrible default primary key column type, the SERIAL column type is great for quickly creating a table with an auto incrementing primary key

CREATE TABLE t2 ( a SERIAL, b INT)# SERIAL maps to bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY

4. MySQL has functions which help populate data for tests, use NOW() / CURDATE() for dates, MD5(now()) for strings, and RAND() for numbers (just examples)

INSERT INTO t1 VALUES (RAND()*POW(2,32), MD5(NOW()), NOW());

5. Randomized dates are easy.

SELECT NOW() - INTERVAL TRUNCATE(RAND()*48,0) HOUR # generates a random date in the last 48 hours

6. Use the power of INSERT ... SELECT to generate large sets of data quickly:

INSERT INTO t1 SELECT ( RAND()*10000, MD5(RAND()*10000), CURDATE() - INTERVAL RAND(360) DAY ); # every time you run this, you will double the amount of data in your table! You can quickly "make" millions of rows this way.

7. Test functions without even using tables

SELECT DATE_FORMAT(CURDATE()," %d, %m %Y"); # This format string isn't too useful, but now you can play with it until you get the output you want.

8. Test updates as SELECT statements. If you want to know if the statement

UPDATE client SET balance=IF(client_type='premium' && balance, balance+1000, balance) AND join_date >= CURDATE()-INTERVAL 90 DAY

will do the right thing before you run it against your database, re-write it as a select, and move the IF conditions to be part of your result set (or other conditions)

SELECT client_type, balance, IF(client_type='premium' && balance, balance+1000, balance) FROM client WHERE join_date >= CURDATE()-INTERVAL 90 DAY # now you can see exactly what the new value will be for each row, without making any changes

9. Remember the MySQL manual is a great resource!

Enjoy, the more you're able to answer your own questions, the more powerful you become! Please feel free to post your own favorite techniques.

How to Select 'this Wednesday' or Other Relative Dates

2009-08-05T12:30:00.000-07:00

I have some bad news and good news. The bad: there is no built in MySQL function for finding a relative date. The good: it's still pretty trivial to get MySQL to calculate it.

The trick is that you need to start with a known date, such as:

mysql> SELECT DATE_FORMAT(CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) DAY, '%W, %M %D, %Y') AS d;
+----------------------------+
| d                          |
+----------------------------+
| Saturday, August 1st, 2009 |
+----------------------------+

That gets you the date for the Saturday that ends the previous week. Then "this Wednesday" or "last Thursday" or almost any other relative date is simple to calculate, provided one caveat. You must do one additional check to see if were already passed the target day of the week.

If today is Tuesday, then "this Wednesday" is 4 days from our reference date above. However if it's already Thursday of this week, then we have to add 7+4 days instead.

mysql> set @dayofweek=4;
mysql> SELECT DATE_FORMAT(CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) DAY + INTERVAL (DAYOFWEEK(CURDATE())>=@dayofweek)*7+@dayofweek DAY, '%W, %M %D, %Y') AS d;
+-----------------------------+
| d |
+-----------------------------+
| Wednesday, August 5th, 2009 |
+-----------------------------+

For "next Wednesday," you only have to add another week.

For dates such as "last Monday" the process is similar:

mysql> set @dayofweek=2;
mysql> SELECT DATE_FORMAT(CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) DAY + INTERVAL (DAYOFWEEK(CURDATE())<=@dayofweek)*-7 + @dayofweek DAY, '%W, %M %D, %Y') AS d;
+--------------------------+
| d |
+--------------------------+
| Monday, August 3rd, 2009 |
+--------------------------+

Note that it's not necessary to do this using SET @dayofweek=N; That is simply to make the query a little more readable in this case.

MySQL Midpoint Stored Function

2009-07-31T11:08:00.001-07:00

MySQL GIS functions don't seem to include a Midpoint function; it's trivial to write one, but I thought posting this might save someone a few minutes. Enjoy!

DROP FUNCTION IF EXISTS midpoint;
DELIMITER //
CREATE FUNCTION midpoint(line GEOMETRY)
RETURNS POINT DETERMINISTIC
BEGIN

IF NumPoints(line) != 2 THEN
RETURN NULL;
END IF;

RETURN GeomFromText(CONCAT('POINT(',(X(StartPoint(line)) +X(EndPoint(line))) / 2,' ',(Y(StartPoint(line))+Y(EndPoint(line))) / 2,')'));
END //
DELIMITER ;

Decimal Math Precision

2009-07-30T13:35:00.000-07:00

I ran into this little problem today; when dividing two whole number the result MySQL gave me was nowhere near as precise as I needed it to be. The values were truncated which caused me some errors in my application.

Example:
mysql> select 1*0.00001;
+-----------+
| 1*0.00001 |
+-----------+
| 0.00001 |
+-----------+

Ok, that looks fine. Let's do the same thing using division:

mysql> select 1/100000;
+----------+
| 1/100000 |
+----------+
| 0.0000 |
+----------+

Oops! What happened?

The issue is that the maximum precision of the result value depends on the number of decimal places in the arguments. Since the second version uses two whole numbers, the result uses the default number of decimal places, which is 4. So you really have to take care to make sure you're getting the precision you want out of your math operations!

To MySQL's credit, the behavior is clearly documented (however, I maintain it's still a bit troublesome because how often would you expect the division operator to have a list of instructions and caveats?)

There are at least a couple simple solutions:
mysql> SELECT CAST(1/100000 AS DECIMAL(8,5) );
+---------------------------------+
| CAST(1/100000 AS DECIMAL(8,5) ) |
+---------------------------------+
| 0.00001 |
+---------------------------------+

Or

mysql> SET div_precision_increment=5;
mysql> SELECT 1/100000;
+----------+
| 1/100000 |
+----------+
| 0.00001 |
+----------+

Reverse Geocoding using MySQL GIS

2009-05-18T10:42:00.000-07:00

Normal geocoding is the process of taking an address and converting it into latitude and longitude. Reverse geocoding goes the other direction; given a latitude and longitude we want to know the closest street address.

Google Maps API, Yahoo Maps, Microsoft, GeoNames.Org and others can provide both these services, but they either cost, or have restrictive terms of service that makes them unavailable for high volume applications. See Geo::Coder::US (for US addresses only). This uses a local data source, and requires you to download the data from the US Census manually. It can import a format known as Tiger/Line into a local BerkeleyDB file, then uses that to calculate lat/lng values from street addresses. However, it provides no method for reverse lookups.

This is because the parsed data is simply a tree of values with the following form:

 /zip/street name/type//

Each key contains a list of numbers which are actually the spatial representation of the street, broken down into segments for each block. It is a series of line segments, which are called "shape files," because they describe the streets in spatial terms. For example:


/94702/Ashby/Ave// :  $VAR1 = [
# record 1
      37848907,   # start lat (from) $value/1_000_000 = the decimal form.
      122297583,  # start lng
      400,  # even num start (range)
      598,  # even num end
      0,  # zero street marker
      401,  # odd num start
      599,  # odd num end
#record 2 .. N


#   ends with final point
      37853426,
      122279480
    ];
] )

For forward geocoding, you simply start with a known address, then use that as a key to find the appropriate spatial data for that street. The lat/lon is an interpolated value.

For reverse geocoding, it's impossible to start with a lat/lng and return the street without iterating for each and every street and doing comparisons to find the "closest." To be able to do this efficiently, we need a spatial index on this data. CPAN has a module for R-Tree indexes, but mysql also provides a convenient way to store all this shape data, and do spatial queries.

This example uses a modified version of MySQL, which includes improved GIS support

Building A Reverse Geocoding DB

Get the Data
Here's a quick PERL script to spider the Census Bureau site and download all the data. It's split by State and county so there are a lot of files:


use HTML::Parser;
my $base_url = 'http://www2.census.gov/geo/tiger/tigerua';

package IdentityParse;
use base "HTML::Parser";
use Data::Dumper;
use LWP::Simple;

my @urls;
my $burl;

sub start {
    my ($self, $tag ,$attr, $attrseq, $origtext) = @_;
    unless ($tag =~ /^a$/) {
            return;
    }

    if (defined $attr->{'href'}) {
            if ($attr->{'href'} =~ /[A-Z]\/$/) { # directories: add to list
                    push @urls, "$burl/$attr->{'href'}";
            }
            if ($attr->{'href'} =~ /\.ZIP$/i ) { # zip file, download it
                    if (! -e $attr->{'href'}) {
                            # we don't have it yet!
                            `wget '$burl/$attr->{href}'`;
                    }
            }
    }
}


my $p = new IdentityParse;

push @urls, $base_url;
while (@urls) {
my $url = shift @urls;
my $content = get $url;
$burl = $url;
$p->parse($content);
}

Convert Using Geo::Coder::US

* Install Geo::Coder::US

This comes with a series of support scripts, among these is import_tiger_zip.pl which will import all the files into a BerkeleyDB file.

 find /path/to/tigerline/data/ -name "*ZIP"  \
-exec ./import_tiger_zip.pl geocoder.db {} \;

The result is:

 -rw-rw-r-- 1 root root 724M Apr 24 12:15 geocoder.db

Import into MySQL

Once you have the geocoder.db output, this script can be used to generate sql statements for inserting this into a spatially aware mysql database.


for i in `cat z2`; do perl ./bdb_mysql_export.pl \
geocoder.db $i >> geocoder.sql; done


#!/usr/bin/perl -w

use Geo::Coder::US;
use DB_File;
use Data::Dumper;
use strict;
use vars qw(%db *db);

my $filename = shift @ARGV or die "Usage: $0 \n";

Geo::Coder::US->set_db($filename);
my @streets;
my %streets;
for my $arg (@ARGV) {
 my $val;
 my $path = "/$arg/";
 my $zip  = $arg;
 my ( $key, $value );
 $Geo::Coder::US::DBO->seq( $key = $path, $value, R_CURSOR );
 while ( $key and $value and $key =~ /^$path/i ) {
     $streets{$key} =
       [ split( ", ", join( ", ", unpack( "w*", $value ) ) ) ];
     $Geo::Coder::US::DBO->seq( $key, $value, R_NEXT );
 }

}
my $DEBUG = 0;

#print Dumper \%streets;
#my @data = values %streets;
foreach my $k (keys %streets) {
 my @data = @{ $streets{$k} } ;
# if (lc($k) =~ /fairoaks/) { $DEBUG = 1; } else { $DEBUG = 0; };
# print "Doing street: $k\n" if ($DEBUG);
# print Dumper(\@data) if ($DEBUG);
# next if (!$DEBUG);
my $first = 1;
 my (@from, @to, @range, @best, $matched, @range2);
    shift @data;
    while (@data) {
            @from = splice( @data, 0, 2 ) if $data[0] > 1_000_000;
            while (@data and $data[0] < 1_000_000) {
                shift @data if not $data[0]; # skip street-side zero marker
                @range = splice( @data, 0, 2 );
                shift @data while @data and $data[0] < 1_000_000;
            }
            last unless @data;
            @to = splice( @data, 0, 2 );

           print "From  : " . Dumper(\@from)."\n"  if ($DEBUG);;
           print "To    : ". Dumper(\@to)."\n"  if ($DEBUG);;
           print "Range1: ". Dumper(\@range). "\n"  if ($DEBUG);;
           print "Range2: ". Dumper(\@range2) ."\n"  if ($DEBUG);;
           my %found;
     @found{qw{ zip street type prefix suffix }}  = split "/", substr($k, 1), 5;
           @found{qw{ frlat frlong tolat tolong }}      = map( $_ / 1_000_000, @from, @to );
           @found{qw{ fradd toadd }}          = @range;
           $found{$_} *= -1 for qw/frlong tolong/;
        print "Found :". Dumper(\%found)."\n"  if ($DEBUG);
        if ( defined($found{fradd})) {
          $found{street} =~ s/'/''/g;
        next if (!defined($found{tolong}));

        my $q =  "INSERT INTO reverse_geocode (zip, street, type, prefix, from_addr, to_addr, line_segment) VALUES ('$found{zip}','$found{street}','$found{type}', '$found{prefix}',$found{fradd}, $found{toadd}, GeomFromText('LineString($found{frlat} $found{frlong}, $found{tolat} $found{tolong})') );";
        print $q ."\n"; # if ($DEBUG);
        }
        if (defined($data[0]) && $data[0] > 1_000_000) {
                @to = splice(@data, 0,2 );
        }
        }
}

Here's the basic table structure:


CREATE TABLE `reverse_geocode` (
`zip` varchar(5) NOT NULL DEFAULT '0',
`street` varchar(64) NOT NULL DEFAULT '',
`type` varchar(8) NOT NULL DEFAULT '',
`from_addr` int(11) NOT NULL DEFAULT '0',
`to_addr` int(11) NOT NULL DEFAULT '0',
`line_segment` geometry NOT NULL,
`prefix` varchar(4) NOT NULL DEFAULT '',
SPATIAL KEY `line_segment` (`line_segment`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And the .sql file can be imported with something like:

 mysql -u -p geocode <>

The result is

mysql> show table status like 'reverse_geocode' \G
*************************** 1. row ***************************
       Name: reverse_geocode
     Engine: MyISAM
    Version: 10
 Row_format: Dynamic
       Rows: 16005729
Avg_row_length: 81
Data_length: 1306599484
Max_data_length: 281474976710655
Index_length: 1021292544
  Data_free: 0
Auto_increment: NULL
Create_time: 2009-04-27 13:08:31
Update_time: 2009-04-27 14:34:36
 Check_time: NULL
  Collation: latin1_swedish_ci
   Checksum: NULL
Create_options:
    Comment:
1 row in set (0.00 sec)

Query the Data

Simply use the MBRContains fuction, and pass it a bounding box to cull results too far away. Sort by distance and take the closest result.


mysql> SET @center = GeomFromText('POINT(37.372241 -122.021671)');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @radius = 0.005;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @bbox = GeomFromText(CONCAT('POLYGON((',
-> X(@center) - @radius, ' ', Y(@center) - @radius, ',',
-> X(@center) + @radius, ' ', Y(@center) - @radius, ',',
-> X(@center) + @radius, ' ', Y(@center) + @radius, ',',
-> X(@center) - @radius, ' ', Y(@center) + @radius, ',',
-> X(@center) - @radius, ' ', Y(@center) - @radius, '))')
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> select zip, prefix, street, type, from_addr, to_addr, astext(line_segment), Distance(@center,line_segment) as dist
FROM reverse_geocode where MBRContains(@bbox, line_segment) order by dist limit 1;
+-------+--------+----------+------+-----------+---------+---------------------------------------------------------+------+
| zip   | prefix | street   | type | from_addr | to_addr | astext(line_segment)                                    | dist |
+-------+--------+----------+------+-----------+---------+---------------------------------------------------------+------+
| 94086 | S      | Fairoaks | Ave  |       323 |     331 | LINESTRING(37.375141 -122.020671,37.372241 -122.021671) |    0 |
+-------+--------+----------+------+-----------+---------+---------------------------------------------------------+------+
1 row in set (0.00 sec)

Now for my project, this was good enough; knowing the closest block of the street produced the results I wanted.

For more accuracy, there are still a few issue that need to be solved:

1. For the even/odd side of each block the same line segment is used. In this case I only keep one side of the street, however the extraction script would have to be modified a little bit if you wanted both sides. It's probably most effective to add two more from/to address columns in the table to store the even/odd numbers.

2. You would have to use some basic geometry to figure out which side of the street the point is on, then use that to choose the even or odd numbers.

3. Using some more geometry, you could also find an approximate address along the line. Essentially you would interpolate a value, which may or may not be a valid address.

LOAD DATA INFILE Gotcha

2009-03-12T16:54:00.000-07:00

I was recently asked about errors in a LOAD DATA INFILE statement. Everything seemed straightforward enough except it was throwing warnings for some columns and not loading the correct values.

Given a simple set of data like this in a text file:
\N \N
18000 \N
18900 18900
546680 546680

and a table schema like this:

CREATE TABLE numbers (a int , b int );

Why would you get a result like this?
mysql> LOAD DATA INFILE 'c:\\test.txt' INTO TABLE numbers
Query OK, 4 rows affected, 2 warnings (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
' for column 'b' at row 1 | integer value: 'N
' for column 'b' at row 2 | integer value: 'N
+---------+------+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM numbers;
+--------+--------+
| a | b |
+--------+--------+
| NULL | 0 |
| 18000 | 0 |
| 18900 | 18900 |
| 546680 | 546680 |
+--------+--------+
4 rows in set (0.00 sec)

Notice it only freaked out on the NULL values and the end of the line!

The answer is one that's come up before, but for some reason I had to break out the hex editor before I remembered: The file was created on a windows machine, and had \r\n at the end of each line instead of just \n.

The solution is simple:
LOAD DATA INFILE 'c:\\test.txt' INTO TABLE numbers LINES TERMINATED BY '\r\n';

Why can't you use subqueries on temp tables?

2009-03-10T18:14:00.001-07:00

So recently I advised someone to do a subquery like:

SELECT distance FROM table WHERE distance < ( SELECT MAX(distance)/2 FROM table)

The key part was the subquery. They came back and said it returned an error:

"Can't reopen table"

The problem is this was on a TEMPORARY table. The manual clearly states this as a restriction:

You cannot refer to a TEMPORARY table more than once in the same query. (http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html)

However I decided to do a bit of digging to find out why that's the case. So I found a nicely commented answer, straight from the source's mouth:

/*
We're trying to use the same temporary table twice in a query.
Right now we don't support this because a temporary table
is always represented by only one TABLE object in THD, and
it can not be cloned. Emit an error for an unsupported behavior
*/ from sql/sql_base.cc open_table

MySQL AES Encryption Compatability

2009-01-28T16:43:00.000-08:00

So what happens if you want to use the mysql functions AES_ENCRYPT / AES_DECRYPT to handle data encryption, but you might also need to write a tool that takes the encrypted data and accesses or updates it outside of the database? This operation can be hit or miss depending on your library's implementation of how it handles the encryption key passed to the actual encryption function. There is no standard on how this is handled.

AES only defines that it accepts an encryption key of AES_KEY_LENGTH/8 bytes. So for 128 bit encyption you need to give it a 16 byte key.

In mysql you can do:
AES_ENCRYPT('mykey','here is a string to encrypt');

But we just passed it a 5 character string, where does the 16 byte key come from? This is the part that can differ between libraries. MySQL takes your string and converts it like this:

static int my_aes_create_key(KEYINSTANCE *aes_key,
enum encrypt_dir direction, const char *key,
int key_length)
{
uint8 rkey[AES_KEY_LENGTH/8]; /* The real key to be used for encryption */
uint8 *rkey_end=rkey+AES_KEY_LENGTH/8; /* Real key boundary */
uint8 *ptr; /* Start of the real key*/
const char *sptr; /* Start of the working key */
const char *key_end=key+key_length; /* Working key boundary*/

bzero((char*) rkey,AES_KEY_LENGTH/8); /* Set initial key */

for (ptr= rkey, sptr= key; sptr <>
{
if (ptr == rkey_end)
ptr= rkey; /* Just loop over tmp_key until we used all key */
*ptr^= (uint8) *sptr;
}

if (direction == AES_DECRYPT)
aes_key->nr = rijndaelKeySetupDec(aes_key->rk, rkey, AES_KEY_LENGTH);
else
aes_key->nr = rijndaelKeySetupEnc(aes_key->rk, rkey, AES_KEY_LENGTH);
return 0;
}

The important bit is in green. That's the code from the mysql source, in mysys/my_aes.c . The algorithm just creates a 16 byte buffer set to all zero, then loops through all the characters of the string you provide and does an assignment with bitwise OR between the two values. If we iterate until we hit the end of the 16 byte buffer, we just start over from the beginning doing ^=. For strings shorter than 16 characters, we stop at the end of the string.

So for our sample password above, the resulting key would be this:

select hex('mykey\0\0\0\0\0\0\0\0\0\0\0');
+------------------------------------+
| hex('mykey\0\0\0\0\0\0\0\0\0\0\0') |
+------------------------------------+
| 6D796B65790000000000000000000000 |
+------------------------------------+

There are two important aspects of this method:

1. For any password you supply that is less than 16 bytes, the resulting AES key will simply be that value padded to 16 bytes with null zeros

2. For any password that is exactly 16 bytes, the resulting AES key is exactly the same as the supplied password.

*WHEW* That was quite a bit, but now lets see how that applies to our attempt to descrypt mysql's values from outside the database or create encrypted strings that mysql can decrypt. For this example lets look at java:

From: http://java.sun.com/developer/technicalArticles/Security/AES/AES_v1.html

       // Get the KeyGenerator
   KeyGenerator kgen = KeyGenerator.getInstance("AES");
   kgen.init(128); // 192 and 256 bits may not be available

   // Generate the secret key specs.
   SecretKey skey = kgen.generateKey();
   byte[] raw = skey.getEncoded();
   SecretKeySpec skeySpec = new SecretKeySpec(raw, "AES");

   // Instantiate the cipher
   Cipher cipher = Cipher.getInstance("AES");

   cipher.init(Cipher.ENCRYPT_MODE, skeySpec);

But wait, where is our password? Well this example they give you has you use KeyGenerator to create a key randomly. That's not very usefull if you ever hope to decrypt your data later. More commonly for password based encryption we would do something like:

byte[] keyBytes = new byte[] { 0x6D, 0x79, 0x6B, 0x65, 0x79, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };

SecretKeySpec key = new SecretKeySpec(keyBytes, "AES");

// Instantiate the cipher
Cipher cipher = Cipher.getInstance("AES");

cipher.init(Cipher.ENCRYPT_MODE, key);

In this keyBytes is simply a byte array defining our 16 byte encryption key. Notice the values, they're the same as our password above padded out with null zeros! In this example the encrypted output would exactly match what mysql would produce. Yay, compatable encryption. I'll leave it as an exercise to the reader to create a function in their language of choice to take a string as input and produce a 16 byte array that matches the result of the mysql source above.

Find out what process is connected to mysql

2008-11-26T19:19:00.000-08:00

This works when you have access to the connected machine -- I've done this on Redhat/CentOs, but don't know about other linux distros.

In the mysql processlist, you'll see the host/port connected:

192.168.10.100:60786

Connect to the machine shown and run:

> socklist | grep 60786
tcp   60786   51371094       0  17518   27  httpd

> find /proc/ -lname "*51371094*" -ls

this will give you the pid of the process that has the socket open to mysql by searching the file descriptor symlinks for the inode that coresponds to the socket on that port.