MySQL to Drizzle character set considerations

Drizzle supports one character set (unless you include binary) which is UTF8.  It is the character set used by most of the web and supporting many different character sets can lead to complications.  That is not to say that there are not advantages of supporting many different types of character sets like MySQL does, but more care is needed when using them.

As an example of this, a new Drizzle user came online today saying that drizzledump's MySQL to Drizzle conversion was turning 'è' to 'è'.  When drizzledump connects to a MySQL server it sets the connection to UTF8 so that the dump output is compatible with Drizzle.  After a bit of discussion it was discovered that the user's table was latin1 and connection was latin1 (PHP does this by default) but they were storing and retrieving UTF8 data.  Essentially their data was getting mangled but it happened to work.  The problem came when telling MySQL to export this data as UTF8, it was effectively doing a double UTF8 conversion of the data.

With this in mind we have added a new option to drizzledump so that it stops setting the character set for the connection in these situations, '--my-data-is-mangled'.

drizzle.org site updates

We have done a few modifications to the drizzle.org website today, most of it is to do with layout reorganisation but there are a couple of new features:

• A DrizzleDB twitter feed box (not quite live, we will flip the switch on that part soon)


• Binary download links for RPMs and DEBs for Drizzle.

Please let us know what you think about the changes.

Also, please visit and sign-up for the Drizzle Developer Day on April 15th.  The venue is TBC but will be in or around the Hyatt Regency in Santa Clara.

Change of Drizzle logo

Way back in 2008 (which is a long time ago in Drizzle's history) Zak Greant created a logo for the Drizzle project (as seen above).  For some reason which has been lost through the ages we switched from this to using the rain cloud logo which was part of the Tango project.

After a lot of discussion it was found that most of us preferred the old logo, not only does it actually have the name in it but in my opinion it is more stylish than the rain cloud.  As a result we have switched back to the old logo and are making this the official Drizzle logo.  We will be rolling it out over the various Drizzle related sites shortly.

It is licensed under a Creative Commons Attribution ShareAlike 3.0 License and an SVG of it is available here.

Building Drizzle in RedHat Enterprise Linux 6 and derivatives

Over this weekend I have been playing with a test release of Scientific Linux 6 which is a binary-compatible rebuild of the source for RedHat Enterprise Linux 6 with a few additions, very similar to CentOS (CentOS 6 should be out in the next few weeks).  Specifically I have been testing Drizzle in it to see if we can compile it and that it will pass our regression suite.  The good news is 'yes' to both.  Here is how to do it.

First of all you need some pre-requisites installed.  Almost all are available from the operating system's repositories apart from one which I will come to in a minute.  You need to install the following packages using yum or any other package manager:

• bzr


• boost-devel


• autoconf


• automake


• gcc


• gcc-c++


• libtool


• gperf


• libuuid-devel


• zlib-devel


• pcre-devel


• readline-devel


• flex


• bison

Now, the key thing missing from the above list is Google's Protocol Buffers.  Unfortunately, unlike Fedora, this does not seem to be in the RedHat repositories so we need to roll our own.  To do this:

1. Install the following packages using yum or any other package manager:
   
   
   
   • rpm-build
   
   
   
   
   
   
   • python-devel
   
   
   
   
   
   
   • python-setuptools
   
   
   
   


2. Download the protobuf source package from here.


3. Run the following (as root):
   

   rpmbuild --rebuild protobuf-2.2.0-3.el5.src.rpm

   
   


4. Install the protobuf packages as follows (again as root):
   

   rpm -Uvh /root/rpmbuild/RPMS/x86_64/*

   
   or if you are using 32bit:
   

   rpm -Uvh /root/rpmbuild/RPMS/i686/*

   
   

You are now good to go.  You should be able to compile Drizzle in the normal way.

TIMESTAMP with microseconds

Back in 2005 a user requested on the MySQL bug tracker that the TIME/DATE based data types store microseconds.  I personally don't think this is an unreasonable request and judging by the many posts to the bug report by users between then and now this is something quite a few people would like to see.

In Drizzle we asked 'What if...' and Brian came up with the answer.  We now (in trunk and in next week's release) have TIMESTAMP and NOW() with microsecond precision.

To create a TIMESTAMP column that uses microseconds you simply need to specify TIMESTAMP(6) in your table definition, for example:

CREATE TABLE `t1` (
 `a` INT DEFAULT NULL,
 `b` TIMESTAMP(6) NULL DEFAULT NULL
) ENGINE=InnoDB

You can then use the following (note that ON DEFAULT/UPDATE CURRENT_TIMESTAMP works with microseconds as well):

drizzle> insert into t1 values (1, '2010-01-10 07:32:43.234567');
Query OK, 1 row affected (0.07 sec)

drizzle> select * from t1;
+------+----------------------------+
| a    | b                          |
+------+----------------------------+
|    1 | 2010-01-10 07:32:43.234567 |
+------+----------------------------+
1 row in set (0 sec)

What if...

I was looking back at Drizzle blog posts today, and noticed the first couple announcing to the world that Drizzle exists are entitled 'What if'.  These are two powerful words which can often drive innovation.  I was suddenly reminded of a UK advert for Honda called 'OK Factory' where one worker decides to go against the grain and ponder 'What if':



(for those who have problems with the embedded Daily Motion a YouTube version is available here)

In Feburary we aim to show you what these two powerful words can produce.  At the very least, I hope the Drizzle project inspires others to ponder 'What if...'

Year 7DB!

OK, yes, I was kinda sad enough at the end of December to convert 2011 to hex to find it is 7DB.  Which I think is appropriate as for me at least this will be the year of Drizzle7 DBMS.

It may be the winter holiday season but many of us Drizzle developers haven't taken much of a break.  I have been working hard to keep our bug count down and will be working on some really cool new features this month.  Brian Aker has also announced things that he has been working on in this mailing list post.

So, what can you expect from the Drizzle project in year 7DB?  Here is a sneak preview of a few things off the top of my head (I really hope I haven't missed anyone here):

1. Completion of replication support (thanks to David Shrewsbury, Joe Daly and everyone else involved there)


2. Many bugs killed (thanks as always to Patrick Crews for making our jobs that much harder by finding the bugs :)  We have also have a bug killing week later this month.


3. Support for a few more data types (and probably improved support for current types)


4. Catalogs support


5. Improvements to system variables and options (thanks to Monty Taylor and Vijay Samuel)


6. InnoDB 1.1.4 (in fact, it has already been merged in, thanks to Stewart Smith)


7. Some really rocking documentation taking shape (thanks to our new documentation writer Marisa Plumb)


8. Some O'Reilly MySQL Conference and Expo talks (I will be there, details in another blog post soon)


9. Drizzle 7 GA release

And much of this will be done very early on this year. Development is happening at a very rapid pace and have made some great achievements so far.  As always we love to for you to try the source or our binaries and love to hear feedback.  We have had some fantastic feedback so far which has led to very rapid fixes and improvements in various areas.