Posted on

Importing a file dumped from MySQL with mysqldump into drizzle

As a big fan of new technology, we try to keep up to date with what’s happening in the industry. As such, I decided to start using drizzle on my development machine since they announced GA this week.
First exercise: import a file dumped from a MySQL server I don’t have access to into drizzle. Normally, you can use drizzledump on the mysql server and make it dump a drizzle compatible file. Not in this case, so I decided to sed my way through the various errors. Not pretty, and I hope that at some point we’ll have a tool that can convert a mysqldump into a drizzle compatible file, but it works for now.
Here’s what I had to do. Note that this is by no means complete or comes with any guarantees, it’s just a starting point.
# This file started by setting a SQL_MODE. That doesn't exist in 
# drizzle, so we comment it out
sed -i "s/^SET SQL_MODE/#SET SQL_MODE/g" mysqldump.sql 

# The create database statement set a default character set. 
# Everything in drizzle is UTF8, so let's lose it!
sed -i "s/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci//g" mysqldump.sql 

# The table definitions mentioned a default character set. 
# Everything in drizzle is UTF8, so let's lose it!
sed -i 's/DEFAULT CHARSET=utf8//g' mysqldump.sql 

# No MyISAM except for temporary tables, so away with it.
sed -i 's/ENGINE=MyISAM//g' mysqldump.sql 

# Invalid timestamps are not accepted in drizzle, so this should be a null 
# value. Since some of the columns in this file are actually NOT NULL defined, 
# for now I just set those dates to 1970. UGLY, but works for me. Don't do this 
# on anything that will ever go anywhere near production though!
sed -i "s/'0000-00-00/'1970-01-01/g" mysqldump.sql 

# tinyint doesn't exist anymore, so just replace with integer. Note that you'll 
# have to do this for all data types that no longer exist in drizzle
sed -i "s/tinyint(.*)/integer/g" mysqldump.sql
Hope this helps others!
Posted on

Friendlist Graph Module for Drupal

At DrupalSouth 2010 (Wellington) after LCA2010, Peter and I implemented a Drupal module as a practical example of how the OQGRAPH engine can be used to enable social networking trickery in any website. The friendlist_graph module (available from GitHub) extends friendlist, which implements basic functionality of friends (2-way) and fans (1-way) for Drupal users.

The friendlist_graph module transposes the friendlist data using an OQGRAPH table, allowing you to query it in new and interesting ways. By adding some extra Drupal Views, it allows you to play Six Degrees of Kevin Bacon with your Drupal users or find out how two arbitrary users are connected. It can find a path of arbitrary length near-instantly. Previously, you’d just avoid doing any such thing as it’s somewhere between impossible/limited/slow/painful in a regular relational schema.

Now think beyond: retrieve/share connections using Open Social, FOAF, Twitter/, logins with OpenID, and you “instantly” get a very functional social networking enabled site that does not rely on localised critical mass!

We tested with about a million users in Drupal (and approx 3.5 million random connections), which worked fine – the later demo at the DrupalSouth stuffed up because I hadn’t given the demo VM sufficient memory.

Naturally, you could do the same in Joomla! or another CMS or any site for that matter, we just happened to be at DrupalSouth so a Drupal module was the obvious choice. Take a peek at the code, it’s pretty trivial. Just make sure you run a version of MySQL that has the OQGRAPH engine, for instance 5.0.87-d10 (Sail edition!) from OurDelta.

Posted on

OpenSQL Camp Portland OR, 14-15 Nov 2009

OpenSQL Camp Portland 2009 is coming up on the 14th and 15th of November. Eric Day (of the Drizzle project) is the lead organiser this time around.

I went to the first edition in Charlottesville VA last year which was organised by Baron Schwartz (Percona). It was a great event, like other unconferences but with specific focus on database technologies. Monty (MySQL), Brian (Drizzle), Richard (SQLite), Jim (Interbase/Firebird/Falcon), Bruce (PostgreSQL) were all these, as were various storage engine builders. Very interesting, and lots of informal fun. If you’re anywhere near, do go!

Even though noone from our gang is able to make it to this one, Open Query is sponsoring this event – for all the above reasons. It rocks and deserves every support.

Posted on

EU probes Oracle’s bid to buy Sun

It appears that little MySQL has just become a disproportionally big player in the Oracle-Sun takeover deal…. article by Associated Press: EU probes Oracle’s bid to buy Sun notes:

EU Competition Commissioner Neelie Kroes said Thursday that regulators needed to examine the effect of a deal “when the world’s biggest proprietary database company proposes to take over the world’s leading open-source database company.”

Ah, Neelie Kroes. Dutch lady from the liberal (that’s seriously right-wing in NL, my American friends 😉 party, formerly minister for infrastructure in NL, long time ago.

So what can happen now? The EU can (and I’m skipping a few steps for brevity here) force the MySQL part of Sun to be auctioned separately, to allow the remainder of the detail to go through. One thing is fairly predictable, the price is probably not going to be $1 bln. As far as it wasn’t overpriced back then, a fair amount of talent and activity is not actually inside Sun any more. Less predictable, who might buy what is now there?

And on a side note, where will Drizzle fit… would be regarded as part of the MySQL bundle as it uses its IP for its foundation? If MySQL goes, and Drizzle stays, then Sun(/Oracle) will have a project for which it does not own the core IP. That can be perfectly fine, but that’s not what it’s been aiming for: Drizzle accepts contributions under BSD license, which means that the core IP owner (currently Sun) is actually able to dual-license it just like MySQL. Not saying that’s what it intends with Drizzle, but the arrangement currently makes Drizzle a potential net asset rather than merely a cool/useful project.

There’s plenty of independent interest (not just intellectual but business/money) in MySQL and Drizzle. I for one prefer that angle in the ecosystem now, it might be better off without core IP ownership. Dual licensing was ok for a time in MySQL’s history, but it’s fairly irrelevant and mainly a nuisance.

In any case… who would have thought, that little database originally written by Monty in Helsinki, causing so much trouble 😉