Skip to content

New openGIS functionality in MySQL available for testing

While MySQL already provides some functionality to store and operate on geospatial data, the functionality leaves quite a lot to be desired and is far from providing full OpenGIS compatibility. Most notably is that all functions that query spatial data only operate on MBRs (minimum bounding rectangles), to simplify the operations.

Thanks to my colleague Alexey "Holyfoot" Botchkov from Izhevsk, Russia, some of the spatial relation functions like INTERSECTS and WITHIN now work in the way they are described by OpenGIS and not by using MBR's as it used to be. He has been working on improving the GIS functionality as a side project and the work has now reached a level at which he is ready to give it some public testing and solicit feedback about it.

Currently these new features have not been slated for inclusion in any upcoming MySQL major release. But the more feedback and testing this code receives, the faster it will reach a maturity level that makes it a potential feature candidate for new versions of MySQL.

In addition to improving already existing functionality, Holyfoot also implemented some new precise geospatial functions - the following functions are available now and use precise operations instead of MBRs:

  • BUFFER(g1 geometry, d numeric) returns a Geometry defined by buffering a distance d around g1 where d is the distance units for the Spatial Reference of g1
  • DIFFERENCE(g1 geometry, g2 geometry) returns a Geometry that is the closure of the set difference of g1 and g2
  • DISTANCE(g1 geometry, g2 geometry) returns distance between g1 and g2
  • INTERSECTION(g1 geometry, g2 geometry) returns a Geometry that is the set intersection of g1 and g2
  • SYM_DIFFERENCE(g1 geometry, g2 geometry) returns a Geometry that is the closure of the set symmetric difference of g1 and g2 (logical XOR of space)
  • UNION(g1 geometry, g2 geometry): returns a Geometry that is the set union of g1 and g2
This work is documented as WorkLog task #1326 "Precise spatial operations", which is a subtask of WL#2377 "Add all missing GIS features to MySQL" - feel free to review and comment on these specifications and make sure to test the new functionality!

The source tree (based on the MySQL 5.1 code base) is now available from our public BitKeeper trees at http://mysql.bkbits.net/ - please consult the reference manual for more information on how to build a MySQL binary from a source tree.

To discuss the new GIS functionality, please join our GIS Forum and make sure to report bugs at our public Bug Database!

Comparing Web2.0 with Open Source

This thought has been floating around my head for quite some time now and I finally bit the bullet and released it from the draft state it had been sitting in for too long: there are quite many similarities between Open Source Software (OSS) projects and most of today's popular Web 2.0 sites, but there is also one odd difference that I wonder about.

For both worlds, the concept of collaboration, participation and giving more power to their users is a key component. OSS projects need contributors for patches and bug reports, but also for feedback, translations, artwork, advocacy in order to be popular and healthy. The project's developers need to be open for suggestions, listening to their user base on where the project should be heading. They also usually strive for open standards and APIs, allowing easy combination and interaction with other Open Source Software so that other projects can build their work on top of theirs or compliment it. A successful OSS application gains popularity by the snowball effect: users of the product find it useful and recommend it to other users seeking for similar functionality. These too start to contribute to the project in various ways, making it in turn more useful and attractive for even more users and so on.

The same is true for Web 2.0 sites: they depend on a user base that creates and provides content that makes their site useful and popular, e.g. blog postings, pictures, articles and other content, but also by providing feedback and bug reports about the site itself and recommending it to others. A successful Web2.0 site needs to be as open to user feedback as a regular OSS project in order to remain attractive and maintain a loyal user base. So it's a lot about participation and empowering the users to contribute and control the content - the part that actually makes a site useful and attractive. Many of these Web2.0 sites also provide public APIs (e.g. SOAP, REST, XML-RPC or RSS, just to name a few acronyms) that allow others to access their services and functionality and reuse or combine them with their own work. And by using technologies like AJAX, most of these sites actually look and feel like regular desktop applications already.

But there is a strikingly difference: even while most Web2.0 sites are based on Open Source technology (e.g. the LAMP stack or Ruby on Rails), only very few ones actually carry on the OSS philosophy and make their own code available as Open Source as well. Not that they would have to do so, as there is this so-called ASP loophole in the GPL and most other OSS licenses: web sites are not actually distributing their application code, so they are not required to disclose the source of their applications.

But why do they break the chain here? One could argue that the obvious reason for that is that most web sites are created and maintained by companies, that are doing this for business reasons and want to maintain their competitive edge over others. However, many sites started as a hobby project by an individual or a small group and were converted into commercial entitities once they became successful. Even in these early stages it's rare that sites provide their source code. But isn't Web2.0 mostly about the users and content and not so much about the technology behind it? Would it really make a difference if the source code of a site is freely available?

In the OSS world, users could just fork off a project, if they don't feel the project is going the right direction or it has been abandoned by the original author. This sometimes leads to much better applications than what the original project came up with. This also leads to a healthy competition and to much better software with less duplication of work in the long run. And this is being recognized by more and more software companies as well - there is a large and growing number of OSS applications that are not maintained by a group of volunteers, but by a commercial entity.

In the Web2.0 world, people start creating new sites from scratch again and again, resulting in a vast amount of sites that serve a similar purpose, all with their own warts and deficiencies, splitting up the user base and available content. This causes fragmentation and makes it much harder to create one useful resource. And they are all on their own in maintaining and improving their sites, to fulfill the requests of their user base and keeping them happy and loyal.

I wonder if and when Web2.0 developers will embrace and extend the concept of sharing their code as well, to let others help them to become even more powerful and popular. Once a site has gained a certain popularity, there is plenty of new features that their users will ask for, or bugs and deficiencies that are in need of fixing. Making the source code available would allow others to jump in here, opening yet another channel of possible contributions and community-building. The Open Source community provides numerous lessons to learn from. True, others may then be able to take the code and create a site that provides the same services and experience. But they still would have to gain traction by creating a community and content before they would become a serious competition.

One great example of how this could be done is Wikipedia - they maintain Mediawiki (the application that powers their site) as a regular OSS project, which has made it one of the most popular and powerful Wiki applications around and has allowed others to create Wiki sites about all kinds of topics on their own.

And while it's not really true Web2.0 company, I also welcome Linden Lab's decision to open up the Second Life client source code. I am confident that the experiences they make with this will encourage them to consider opening the sources of the server code as well at some point...

But they both have grasped that it's not so much about the technology and software, but much more about the users and the content created by the community that makes them popular and successful. This is something one can't simply take and create a competing offering, even though the source code is available.

Announcing the 7th MySQL Hamburg Meetup

Just a quick reminder for those of you located near Hamburg, Germany: on Monday, June 4th at 19:00 there will be our 7th MySQL Meetup. As usual, we will gather at the Chinese Restaurant "Ni Hao". This time, Sönke Ruempler will give a talk about the PHP-ORM-Framework "Propel". If you'd like to join, don't hesitate to RSVP via meetup.com or Xing.com right away! Thanks - see you there!

PlanetMySQL now available in French

It was long overdue, but now it has happened: Planet MySQL now provides a section to aggregate french blogs about MySQL! Thanks a lot to Jay for updating the code and Pascal Borghino for the localization. If you are a MySQL enthusiast located in France (or any other french-speaking country) and you enjoy writing about your passion in your native language, submit your feed now!

Announcing mylvmbackup 0.5

Eric Bergen from Proven Scaling (which I had the pleasure to meet in person during the MySQL Conference & Expo in Santa Clara last month) was kind enough to send me a patch for the mylmbackup tool, which justifies a new release:
Attached is a patch file for mylvmbackup that adds the ability to use
lvm version 2 and perform innodb recovery on the snapshot prior to
creating a tar ball. The option is named --innodb-recover.

I've also fixed a bug with default value handling for command line
options. In version 0.4 if a config file was specified default values
in the script were all changed to blank. This means that the config
file had to supply values for every variable instead of just the
values that need to be changed from default.

This is a very useful addition, as it significantly reduces the time required to recover a MySQL server from a snapshot backup. Thank you, Eric! Note that you need to use LVM2, as LVM1 does not support the required writing to snapshot volumes.

Version 0.5 is now available for download from the mylvmbackup project page.

I also would like to announce that I have set up a dedicated mailing list for this tool: if you want to discuss the usage/future of mylvmbackup, propose patches or ask for help, there now is a mailinglist, hosted on FreeLists.org. To subscribe, either enter your email address on the mylvmbackup list information page or send an email with the subject "subscribe" to mylvmbackup-request@freelists.org. The list is archived here. See you there!

Storing and streaming large binary objects using the Scalable BLOB Streaming Architecture for MySQL

Even though MySQL is used to power a lot of web sites and applications that handle large binary objects (BLOBs) like images, videos or audio files, these objects are usually not stored in MySQL tables directly today. The reason for that is that the MySQL Client/Server protocol applies certain restrictions on the size of objects that can be returned and that the overall performance is not acceptable, as the current MySQL storage engines have not really been optimized to properly handle large numbers of BLOBs. To work around these limitations, these projects usually just store a reference to the object (e.g. a path name in a regular file system). This approach works around the limitations applied by the MySQL Server, but results in a disconnection and potential source of inconsistency between the database and the file system content. There was an interesting discussion about that topic on Sheeri's blog some time ago, with an excellent reply by Kristian, if you are curious to read more about this.

Thanks to the pluggable storage engine architecture of MySQL 5.1, we now see many independent storage engines emerging, one of them being PBXT by Paul McCullagh from SNAP Innovation. One of its strengths is going to be the handling of large binary objects in a performant way, so data objects can be stored in the database along with the other information related to it. To compliment this feature and to work around the limitations applied by the Client/Server Protocol, SNAP has now launched the Scalable BLOB Streaming Architecture for MySQL project, which intends to develop an architecture that accompanies the MySQL Client/Server protocol by providing an API to access and stream BLOBs stored in a MySQL database. See their press release (PDF) for the official announcement..

Paul will hold a BOF about this at the MySQL Conference and Expo this Tuesday at 19:30 in room San Thomas. So if storing and streaming large binary objects out of a MySQL Database is an essential requirement for your project, make sure to attend this session and provide Paul with your feedback and ideas!

MySQL Conference and Expo 2007 for Perl developers

Jay has written an excellent summary of sessions at the MySQL Conference & Expo that will be particularly interesting for developers using MySQL to power their applications or web sites. I noticed that he did not explicitely mentioned the Perl scripting language, even though there actually are a few sessions that will be of interest for the Perl Mongers amongst us (I would not dare to claim I am one myself, I just hack on it in my spare time):
I am sure I missed some relevant sessions, as Perl is usually taken as a given in many of today's implementations and applications and may therefore not be considered worthwhile to mention anymore. It's probably more hip to work with Python or Ruby these days... Let me know, if I missed one!

In closing I'd also like to add another shameless plug for the DotOrg Pavilion here: Perl is going to be present there, too: the very popular XAMPP application stack of course includes it, and Bugzilla is an entire application written in this scripting language. So make sure to stop by at the exhibition area and let them know, if you are a Perl geek as well!

Presenting the DotOrg Pavilion exhibitors of the MySQL Conference & Expo 2007

In two weeks from now, the MySQL Conference & Expo 2007 will take place in Santa Clara, California. Our team has been very busy with working with the conference management team from O'Reilly to prepare for this event, especially Jay who is in charge of organizing and arranging the more than 110 sessions that will be held in several parallel tracks. The conference program looks very exciting, I am impressed by the wide range of topics and quality of speakers that we have lined up.

In addition to the presentations and tutorials, there will also be an Expo, where partner companies will exhibit their products related to MySQL. You will notice a number of well-known names in the industry there, e.g. hardware vendors like Dell, Dolphin Interconnect, HP or Sun as well as software companies like Alfresco, BitRock, Google, Pentaho, Red Hat and many more.

One of my tasks for this conference is to arrange the DotOrg Pavilion, an exhibition area inside the expo hall that is reserved for Open Source Community projects that are based on or support MySQL in some way. The conference web page does not yet list them all, therefore I'd like to give you a full rundown of all the OSS projects that will be present there:
  • Bugzilla is an open source bug tracker used by thousands of businesses to track defects and enhancements. Bugzilla, the standard in open source bug tracking, is a versatile and powerful web based system with a minimal footprint, proven to support groups of any size from small projects to multinational enterprises.
  • dotProject is a Web-based project management framework that includes modules for companies, projects, tasks (with Gantt charts), forums, files, a calendar, contacts, tickets/helpdesk, multi-language support, user/module permissions, and themes. It is translated into 17 languages and has a modular design that allows extra modules (such as time sheets and inventory) to be added in easily.
  • Drupal is a powerful, scalable and full-featured content management system that allows an individual or a community of users to easily publish, manage and organize content and run an online community. Tens of thousands of people and organizations use Drupal to power scores of different web sites. A large, healthy and diverse community of developers, users and consultants drives a rapid pace of innovation and a wide variety of feature development.
  • Eventum is user friendly and extensible issue tracking software from MySQL that can be used by a support department to track incoming technical support requests, a software development team to quickly organize tasks and bugs or anyone else looking to organize their work.
  • HeidiSQL is an easy-to-use interface for developers using MySQL, allowing simple management of their databases and tables. Previously known as MySQL-Front, the name has been switched in 2006 by Ansgar Becker, the first developer, that put his old sources into a new open source project at SourceForge. Currently this tool is available on Windows and Linux.
  • Joomla! is one of the most outstanding content management systems in the world. This is not only because of his approach of providing one of the most well structured and simple user interfaces for managing your media, text, image and other public information materials in the web. With Joomla! you are able to provide a huge information base for your professional audience as simple as you build up your own family community website. The new refactored content framework gives you the confidence that your site is based on a reliable, flexible, future proven and easy to manage plattform.
  • LinuxQuestions.org is a free, friendly and active Linux Community with over 180,000 members and over 1,7000,000 posts. Founded in 2000 LQ offers forums, reviews, a Linux hardware compatibility list, a Linux knowledge base in wiki format, Linux tutorials, a free Linux download site, a Linux podcast, a Linux radio show and more. LQ has forums for everything from Linux Newbies to Linux in the Enterprise and has over 20 officially recognized Linux distribution forums.
  • OTRS is an Open source Ticket Request System (also well known as trouble ticket system) with many features to manage customer telephone calls and e-mails. The system is built to allow your support, sales, pre-sales, billing, internal IT, helpdesk, etc. department to react quickly to inbound inquiries. Do you receive many e-mails and want to answer them with a team of agents? You're going to love the OTRS!
  • Phorum, is the original PHP/MySQL message board software. Started in 1997 by Brian Moon, Phorum has been used by sites such as Zend.com and currently is the message board of choice for MySQL.com. Phorum's developers focus on speed and expandability through its flexible module system.
  • phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the Web. Currently it can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges,export data into various formats and is available in 50 languages.
  • SnapLogic is an Open Source project that enables developers to easily and rapidly integrate applications and other data sources through a Data Integration Network. SnapLogic embraces the simplicity and scalability of the Web to collapse the complexity of data integration for Internet-scale integration.
  • Sphinx is fast and powerful full text search engine designed with indexing database content in mind. It offers high performance, good scalability (with multi-server distributed searching), great search quality, and other advanced features such as efficient filtering, sorting and grouping. Both native search APIs and a Pluggable MySQL Storage Engine search client are available, which makes Sphinx easy to integrate into existing applications.
  • WordPress is a state-of-the-art semantic personal publishing platform with a focus on aesthetics, web standards, and usability. What a mouthful. WordPress is both free and priceless at the same time. More simply, WordPress is what you use when you want to work with your blogging software, not fight it.
  • XAMPP is a free software package containing the Apache web server, MySQL database and necessary tools to use the PHP and Perl programming languages. Currently, XAMPP is available for Windows, Linux, Solaris and Mac OS X (the X in its name could stand for any one of these operating systems).
I hope I have gathered an interesting mix of projects and look forward to meeting with these people in person soon! If you attend the conference, please make sure to stop by the DotOrg pavilion and have a chat with the representatives from these projects!

Report and pictures from the 6th Hamburg MySQL Meetup

Yesterday we had our sixth MySQL Meetup here in Hamburg - we had a nice crowd of 21 people. Erick Dennis and Michael Otto from epublica gave a presentation about their experiences with MySQL and scaling it to keep up with the growth of the Xing.com web site. The talk was a rehearsal for their presentation at the upcoming MySQL Conference and Expo in Santa Clara - it went very well and we had good discussions and comments during and after the session. I took some pictures during the meeting, which are now available on my picture gallery. The meeting was rounded up with excellent chinese food, thanks again for the folks at the Ni Hao for hosting us! I look forward to our next meeting, which will take place some time in May. If you have a suggestion for a presentation, please let me know!

Linux and OSS print magazines in Germany

I took this picture at Hamburg Airport while waiting for Kaj to arrive for our meeting last week. I was browsing through the magazines in a news shop in the arrival area and was pretty impressed about the amount of available magazines about Linux and Open Source Software. It amazes me how they all manage to produce enough content to fill a magazine on a frequent basis. I take this as a very positive sign - Linux has become a mainstream OS. I wonder how many people consider switching to Linux instead of going with MS Vista - the numerous CDs/DVDs that are included in these magazines significantly reduce the acceptance threshold for new users. Live CDs make it easy to give Linux a try without having to touch the installed operating system. The support and automatic detection and configuration of hardware in Linux has become much better nowadays, too. Granted, there is still a lot of room for improvement (especially when it comes to video card and WLAN chipset support, it seems) - but Linux distributions have reached a level of maturity that make them a viable alternative to Mac OS X or MS Vista for the most common use cases. And all the required applications are already included out of the box and are free (as in beer)! So what is missing to world domination?

Reminder: 6th Hamburg MySQL Meetup coming up on Monday, 2nd of April

It's time for another Meetup of the Hamburg MySQL User Group! As for previous events, we'll meet on Monday, 2nd of April at 19:00 in the Chinese restaurant Ni Hao in Hamburg-Wandsbek.

This time there will be a presentation about "Xing/OpenBC growing pains", held by Erick Dennis und Michael Otto from epublica, the company behind Xing.com.

The talk will be held in english and is a rehearsal for their presentation at our upcoming MySQL Conference and Expo, which will take place from April 23rd-26th in Santa Clara, California. Please subscribe to our mailing list and RSVP, if you plan to attend the Meetup. Thank you!

To start or not start the MySQL server during the RPM installation?

So far, the MySQL Server RPM packages as provided by MySQL AB used to automatically start the mysqld process after the package has been installed. It has been like that since the very beginning and we think of it as a convenience for our users when they want to get up and running quickly.

However, Kristian raised an interesting point in BUG#27072 where he points out that automatically starting mysqld during the RPM installation might not always be the desired behaviour, especially in automatic installation environments or during a fresh installation (where the system might not be fully configured yet). Therefore he proposes to change this behaviour to not start mysqld as part of the installation.

While I personally agree with his proposal, this is of course a tricky decision: our users are familiar with this behaviour and it's never a good idea to cause surprises. Therefore this change could only be done in future versions of MySQL, where they can be properly announced and documented and don't cause too much confusion.

Then again, there are several options here. Should we just disable it for the Enterprise Linux RPMs (RHEL/SLES) and keep it enabled in the "generic" RPMs? This would be inconsistent and harder to document/explain. Should the server never be started at all, or should we keep the behaviour for updates at least (when the server was already running before the package update)?

We would like to get your take on this. Please post your comments and suggestions to the bug report (preferred) or leave a comment on my blog, so we can get an impression on what you would expect to be the correct behaviour here. Thanks!

FrOSCon 2007 Call for Papers now open!

I just stumbled over Sebastian's blog entry:

The second Free and Open Source Conference "FrOSCon" takes place on August, 25th and 26th 2007 in Sankt Augustin, near Bonn, Germany. The conference is once again hosted by the faculty of computer science of the University of Applied Sciences Bonn-Rhein-Sieg in collaboration with the student body and the Linux/Unix User Group Sankt Augustin.

I attended FrOSCon last year and found it a very pleasant conference to be at. I gave two sessions about MySQL Backup and Security and the MySQL Business model, which both had a good audience and went quite well. I will definitely consider going there again this year, still pondering about the topics of my talks... The CfP ends on June, 4th, so make sure to turn in your suggestions in time!

If you are looking for a very well organized OSS event with lots of good technical content in a relaxed atmosphere, consider visting FrOSCon this year! I look forward to being there again.

Several new feeds added to Planet MySQL

Due to a small mail filtering glitch we did not notice a number of new feed submissions for Planet MySQL that had been sent to us via the submission form since the beginning of the year. Doh!

Today I discovered them in a separate mail archive and have now added all the ones that were not spam or unrelated to MySQL.

So if you recently submitted your RSS feed and you don't see it aggregated on Planet MySQL by now, please resend your submission. Thanks and we're sorry for the delay and inconvenience!

MySQL FLOSS License Exception updated

Long time no blogging! I hope PlanetMySQL finally picks up my posts again, I really miss the additional audience :-)

We recently received a request from the OpenISR project about adding the Eclipse public license to our FLOSS License Exception. As it turns out, it is almost identical to the Common Public License (CPL), which was already included in the exception. So the decision was fairly easy - version 0.6 of the FLOSS License Exception now also covers the Eclipse public license.

What is the FLOSS License exception all about anyway? Our intent:

We want specified Free/Libre and Open Source Software applications to be able to use specified GPL-licensed MySQL client libraries despite the fact that not all FLOSS licenses are compatible with version 2 of the GNU General Public License.
In other words, we want to make it possible for more non-GPL applications that are licensed under any of the listed OSI-approved licenses to link against the MySQL client libraries. The term exception may be misleading here, what it really does is widen the scope of the client license and make it more compatible to applications that are not under the GPL.
tweetbackcheck