Wednesday, August 10, 2011

ZeroMQ support in Drizzle

My little zeromq plugin just hit drizzle trunk, it is simply a replication plugin that publishes a multi-frame message on a ZeroMQ endpoint. The first frame contains the name of the schema the transaction was executed against and the second frame is the actual protobuf-serialized transaction.

Getting started

  1. Install zeromq
  2. Build & install Drizzle (it will pick up the zeromq libs automatically)
  3. Start drizzled with --plugin-add=zeromq (this binds a zeromq socket on tcp://*:9999 - change this by setting --zeromq.endpoint=[whatever])
  4. Profit
Try it out by running this small python script:
import zmq

ctx = zmq.Context()
s = ctx.socket(zmq.SUB)
s.setsockopt(zmq.SUBSCRIBE, '')
s.connect('tcp://localhost:9999')
i = 0
while True:
    i = i+1
    print s.recv_multipart()
    print i
You should see output on the form ['the_schema', *blob*].

Sunday, April 24, 2011

Drizzle JDBC 1.1 - SSL support and multi queries

Just pushed up Drizzle JDBC 1.1 with 2 quite big new features:
  • SSL support - add ?useSSL=true in the connection string (and do the usual ssl-in-java magic to make it work. Check the MySQL documentation for more information, it is set up exactly the same way here)
  • Multi queries - add ?allowMultiQueries=true to your connection string to be able to send several queries in one round trip to the server.
Note that these features are only supported when using the driver against a MySQL server - Drizzle does not have these features (yet).

Download it from the central maven repo (should be synced within an hour or so)

Monday, April 11, 2011

Drizzle JDBC 1.0

Finally got around to making a 1.0-release of Drizzle JDBC, after a while of not getting any bug reports (not at all claiming that it is bug free, but it is stable enough for several companies to use it in production).

Far those new to Drizzle JDBC, it is a BSD-licensed JDBC driver for Drizzle and MySQL.

Changelog

  • DatabaseMetaData fix - return column_type instead of data_type, contains more information (by Stephane Giron)
  • Detect if a blob is actually a CLOB by checking charset number (by Christian Peter)
  • Connect with UTF-8 charset default (bug reported by Marc Isambart)
  • Support for connection timeouts (by Marc Isambart) - add ?connectTimeout=X to have an X second connection timeout.
  • Properly support TIME and BOOLEAN datatypes in Drizzle
  • Move query timeout thread to the Connection class instead of having it in the statement class (duh) - now one extra thread per Connection instead of one per Statement. Is safe since only one statement can run in one connection at a time.
  • Generally shape up UTF8 handling throughout the driver (by Marc Isambart and me).
  • Update copyright headers, now includes the standard Simplified BSD license header.
  • Add an AUTHORS file

Whats next?

So, the general plan for the future is:
  • Make the driver more modular - it should be possible for users to change the behavior of almost every aspect of the driver
  • Support the optional javax.sql packages - XA handling etc. This will be built as a separate maven-published jar to keep size of the basic driver small
  • Support the SQL escape syntax (never used it, but i guess it should be there...)
  • ...

If any of the things above are more important for anyone, please let me know so that i know where to begin.

Thursday, April 7, 2011

Visualizing data in real time using drizzle and rabbitmq, DEMO!

A couple of weeks ago, when Mozilla released firefox 4, i watched glow.mozilla.org - a site made to visualize the firefox downloads in real time. I figured this could be quite a neat use case for my old drizzle to web socket replicator.

So, the compontents involved:

The basic flow is this:
  1. 10 inserts per second are executed in the drizzle database using drizzle-jdbc, these inserts contain a random IP address
  2. Drizzle does its magic and packages up each insert as a google protobuf transaction message
  3. The rabbitmq plugin picks up the message and publishes it to a RabbitMQ server
  4. RabbitReplication consumes the message from RabbitMQ and maps it onto an annotated java object. This gives alot of flexibility to the rabbitreplication user since you can manipulate the information any way you want, in this example i extract a longitude and latitude value from the IP address using MaxMind GeoLite City. Have a look at row the "setSomething()" method in the ExampleRepl.java file. This file is something a rabbitreplication end user would implement and provide
  5. The java object is serialized to json
  6. The json string is published to all connected clients
  7. A javascript on the page takes the lat/lng-values and maps these onto a google map
  8. The same javascript appends some of the raw data to a table

So, I'm hoping the little demo i'm linking below shows some of the extreme flexibility of the Drizzle replication. This, for me, is the unique selling point for Drizzle. Also, if anyone has an actual production use case for this, i'd be extremely happy to help implement it!

http://demo.rabbitreplication.org


(Thanks to Rackspace for hosting!)


Monday, March 28, 2011

Publish transactions to RabbitMQ in drizzled7

Since Drizzle went GA a couple of weeks ago, i figured i should update rabbitreplication to support it, quite a few changes have been done to the structure of the protobuf messages etc. First hurdle was to get drizzle to actually publish messages to rabbitmq, so here is a step-by-step tutorial to get started with that.

  1. Install a recent version of rabbitmq
  2. Get the latest version of librabbitmq:
    $ hg clone http://hg.rabbitmq.com/rabbitmq-codegen/
    $ hg clone http://hg.rabbitmq.com/rabbitmq-c/
    
  3. Build it (in the rabbitmq-c directory):
    $ autoreconf -f -i
    $ ./configure
    $ make
    $ make install
    
  4. Install Drizzle, follow these instructions:
    http://docs.drizzle.org/#compiling-and-installing. (During ./configure you should see that librabbitmq is found and that the plugin is getting built)
  5. Start Drizzle like this:
    $ sbin/drizzled7 --plugin-add rabbitmq,default-replicator --rabbitmq.use-replicator default
    
  6. To verify that it works, you can run a rabbitmq consumer from rabbitmq-c/examples, like this:
    ./amqp_listen localhost 5672 ReplicationExchange ReplicationRoutingKey
    Then execute an insert (or something, just not a select :) ) on your drizzle server, and you should see something like this in your console:
    Result 0
    Frame type 1, channel 1
    Method AMQP_BASIC_DELIVER_METHOD
    Delivery 1, exchange ReplicationExchange routingkey ReplicationRoutingKey
    ----
    00000000: 0A 17 08 01 10 87 36 18 : F0 FA D9 99 FA F1 A7 02  ......6.........
    00000010: 20 99 81 DA 99 FA F1 A7 : 02 12 40 08 01 10 F2 FA   .........@.....
    00000020: D9 99 FA F1 A7 02 18 FC : FA D9 99 FA F1 A7 02 2A  ...............*
    00000030: 17 0A 06 0A 01 62 12 01 : 61 12 06 08 04 12 02 69  .....b..a......i
    00000040: 64 12 05 08 01 12 01 74 : 32 11 08 01 10 01 1A 0B  d......t2.......
    00000050: 0A 01 32 0A 02 61 61 10 : 00 10 00 20 01 28 01     ..2..aa.... .(.
    0000005F:
    
In a couple of days I'll make a new release of rabbitreplication that supports the latest version of drizzle, stay tuned for that! I'll also update the drizzle rabbitmq plugin so that it is actually usable (support reconnect etc)

Monday, February 14, 2011

Drizzle JDBC 0.8

Just pushed up Drizzle-JDBC 0.8 to the central maven repository, list of changes is below. To use it, update your poms to include this:
<dependency>
   <groupId>org.drizzle.jdbc</groupId>
   <artifactId>drizzle-jdbc</artifactId>
   <version>0.8</version>
</dependency>

Or simply download the jar file and add it to the classpath. Further instructions on how to us it is available on the Drizzle JDBC Wiki

Drizzle JDBC is a BSD Licensed JDBC driver for MySQL and Drizzle.

Changlog for 0.8

  • Allow queries bigger than 16M (Stephane Giron)
  • Allow prepared statements with large parameters
  • Allow reading back big result sets
  • Implement cancel() on statements
  • Implement query timeouts
  • Java 5 support (Christian Peter)
  • Add option to create database if it does not exist (Stephan Giron) - add ?createDB=true to the connection string
  • Fixes to database metadata queries after drizzle got its data dictionary
  • Better UTF8 support and BLOB/CLOB fixes (Christian Peter)
  • Support for MySQL old passwords (Stephane Giron)
  • LOAD DATA LOCAL INFILE support (Stephane Giron)
  • Properly close socket (Gilles Rayrat)
  • Support BigDecimal in setObject
  • Move maven repositories to the Sonatype OSS repositories
  • Initial blob streaming (PBMS) support (http://developian.blogspot.com/2010/08/blob-streaming-pbms-support-in-drizzle.html)


Report any issues/feature requests to me, krummas@gmail.com or on http://launchpad.net/drizzle-jdbc