Lenz Grimmer's blog

I admit it — I'm a fan of simulation software, particularly flight simulators. Probably the best Open Source Flight Simulator out there is FlightGear — it provides an impressive level of reality and you can download and install many additional plane models and terrains. There are packages of FlightGear 1.0.0 in the games repository of the openSUSE Build Service, which works quite well and I have been enjoying it a lot. However, the FlightGear project released version 1.9.x quite a while ago (1.9.1 was published in January 2009) and I was itching on giving the new version a try (just take a look at the screenshots and you know what I mean). However, building FlighGear on Linux is quite a complex task with many dependencies, and so held off from doing it myself, waiting for someone else to perform the update...

Well, this weekend I finally bit the bullet and did it myself - FlightGear 1.9.1 has now been added to my home:LenzGr build repository. I based my packages on the ones included in the games repository, but I plan on cleaning them up a bit and splitting them into separate packages (currently the FlightGear source RPM contains SimGear and fgrun as well). I also "borrowed" the OpenSceneGraph sources and spec file from the PackMan repository, in order to have a functional build. Unfortunately FlightGear currently only builds on a very limited list of distributions so far (namely OpenSUSE 11.0, just what I needed) — I haven't had time to adapt the spec files for FlightGear and OpenSceneGraph to match the appropriate build dependencies for the other distributions yet and "02-check-gcc-output" gives me some grief on platforms where it actually builds but generates compiler warnings (but patches are welcome!)...

Last week I gave a MySQL University Presentation about how to contribute code to MySQL. This time DimDim did not fail to record the session, even though there is a funky overlap of audio from Stefan Hinz (the moderator) and myself at the beginning. I had a bit of a slow start into the presentation, because of a very nasty headache that plagued me that day. But we had a lively discussion at the end and I hope it was useful to the participants.

In case you have missed it, you can now watch the playback or download the session slides:

• Presentation slides: MySQL Code Contributions (PDF)
• Session recording (Adobe Flash Player required)
• Chat transcript

Shortly after I created the initial packages of embedded InnoDB on the OpenSUSE Build Service, Oracle/Innobase released an updated version (1.0.3.5325). In addition to many improvements and bug fixes, they slightly changed the versioning scheme to better indicate what version of the InnodDB plugin their code is based on (see Vasil's posting on the InnoDB Forums for more information).

I've now updated my InnoDB packages on the Build Service to this version as well - please note that the naming scheme of the shared library package has been changed from "embedded_innodb1" to "libinnodb2" — RPM will take care of replacing the old package during update, even though the name has changed.

Oracle/InnoBase announced the availability of the embedded version of InnoDB at this year's MySQL Conference & Expo, but I have not seen a lot of comments or reviews about it so far. Which surprises me, because I think this is a very interesting piece of technology!

In my opinion it might actually hit the sweet spot for application developers seeking an alternative embedded database solution. SQLite is nice and popular, but it seems to have concurrency issues when used in multi-threaded applications. An embedded MySQL server would be an alternative - this is what the Amarok developers decided to go with, for example. But this approach has its issues, too, especially the lack of a shared library version of libmysqld poses some challenges when distributing binaries.

This is where I think the embedded version of InnoDB might have an edge. It's pretty lightweight in comparison to a full-blown MySQL server, provides excellent crash-recovery (which is essential for desktop applications), transactions (useful in environments with high concurrency) and foreign key constraints. I'm not sure how important these are for embedded use cases, it probably depends on the complexity of the data to be stored. On the downside, Embedded InnoDB does not "speak" SQL. In order to store and retrieve values, you need to use the InnoDB API. See the chapter Concepts and Architecture for more details and an overview.

Another possible reason for the low popularity might be that it's currently not part of any Linux distribution (yet) and that Oracle only provides binary tarball packages for Linux and a Windows binary for download from the web site.

Therefore I've now created a spec file to build RPMs of Embedded InnoDB and added it to my repository on the openSUSE Build Service, which now provides Embedded InnoDB packages for a wide range of RPM-based Linux distributions. I hope that the spec file will be included in the next source distribution. I've posted it (and a patch to fix a few problems with the examples) to the newly created InnoDB mailing list, but to be sure I added a note to the Embedded InnoDB Forum as well.

After a long hiatus, I am happy to announce that mylvmbackup version 0.12 has now been released. This release includes a large number of improvements, minor code cleanups, as well as some new functionality. In particular, I would like to thank Matthew Boehm, Tim Stoop, Baron Schwartz, Ville Skyttä and Ronald Bradford for their contributions.

Some notable highlights from the ChangeLog:

• Removed the absolute path names to external tools (make sure $PATH is correct)
• Added --log-err to the startup options of the recovery instance to avoid cluttering the server's error log
• Added support for hooks written as Perl Modules. (Matthew Boehm)
• Added support for date/time-formatted path names for backupdir and mountdir (Matthew Boehm)
• Backupdir and mountdir are now created automatically (Matthew Boehm)
• Added new hook "logerr" when an error is logged. (Matthew Boehm)
• Added Option --keep-mount... (Tim Stoop)
• Removed the bind mount, now requires LVMv2
• Support reading login/password from ~/.my.cnf (Baron Schwartz)
• Documentation fixes and improvements (Ville Skyttä) (Bug #302144)

Giuseppe stumbled over this website and recommended it to me as a potential useful service for organizing the registrations of the OpenSQL Camp: Eventbrite is a Python/MySQL-powered web site (judging from their job openings) that provides the following:

Eventbrite is the leading provider of online event management and ticketing services. Eventbrite makes it easy for anyone to hold a successful event of any type and size. Eventbrite is free if your event is free. If you sell tickets to your event, Eventbrite collects a small fee per ticket. So just like you, Eventbrite wants your event to be a big success.

The Eventbrite service includes many features and tools intended to let you perform three basic tasks really well:

1. Publish: Everything you need to create and easily personalize a custom web page for your event.
2. Promote: The tools you need to spread the word about your event and maximize attendance.
3. Sell: The immediate power to sell tickets and collect money online.

I won't use it for the OpenSQL Camp (as we will be able to piggyback on FrOSCon's event infrastructure), but it seems like a service worthwhile checking out, if you're looking for a way to organize your next MySQL meetup (and you're based in the US - currently Eventbrite seems to assume USD as the only available currency). I've added a note about it to the suggestions on the how to run a MySQL User Group page on the MySQL Forge Wiki. And if you do use it, please make sure to add your group to the list of MySQL User Groups! Thanks.

Searching the service for "MySQL" revealed that the MySQL Dublin Meetup actually uses it for organizing their upcoming meeting (June 24, but already sold out)!

Just spotted the following posting from Paul DuBois on the internals mailing list:

I'm engaged in a project (WL#3403) to compile information that will provide better information about our errors and error messages:

• What an error means
• Likely causes of the error
• How to rectify or work around the error

The general idea is to provide our users something more than a list of error codes and the messages from errmsg.txt, such as the manual currently includes here:

http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html
http://dev.mysql.com/doc/refman/5.1/en/error-messages-client.html

To that end, and to give lucky YOU a chance to participate in this exciting (!!) project, I'd like to solicit your knowledge on a case by base basis. To that end, I'll post individual messages for different errors, indicating what I know about them and asking for additional comments. I hope in this way to take advantage of the knowledge that different developers have about errors that pertain to their particular areas of expertise.

I think this an excellent initiative, as some of the error messages are quite confusing/misleading!

And without further ado, Paul already started with his first post: Error of the day: ER_HASHCHK - which according to Kostja is not actually used...

XtraBackup is an Open Source online (non-blockable) backup solution for the InnoDB and XtraDB storage engines. It works with both MySQL 5.0 and 5.1 (and possibly 5.4 as well) and is distributed under the GPLv2.

Some weeks ago Vadim announced the availability of xtrabackup-0.7, stating that they consider it stable enough now to label this version a "Release Candidate". I've been maintaining RPM packages of xtrabackup on the fine openSUSE Build Service for quite some time now, RPMs of 0.7 for a number of distributions are now available for download. Please report any bug reports via the bug tracker on Launchpad.

The OpenSQL Camp 2009 web site is now ready for business, I've updated various pages and added some more information about the call for papers. I've also set up a Twitter account (no way without one nowadays, right?), which might also play an important role in the voting/rating of talks later on (Giuseppe came up with an interesting proposal for that).

So we're now seeking your input! Let me quote from the web pages directly:

OpenSQL Camp is a free conference of, by, and for the open-source database community of users and developers. The first OpenSQLCamp 2008 took place in Charlottesville, Virginia, USA, November 14, 15, and 16 2008.

The OpenSQLCamp 2009, European Edition will take part in parallel to the Free and Open Source Conference 2009 (FrOSCon) on Saturday 22nd and Sunday 23rd August in St. Augustin, Germany. St. Augustin is located close to Bonn and Cologne.

Attendees of this conference are mostly open source developers and end users/open source enthusiasts. The FrOSCon organizers agreed to provide us with a "Developer Room" for both days, which allows us to organize our own subconference about Open Source Databases and related technologies. The goal of this event is to spread the word about the vibrant communities and large ecosystems around Open Source Databases and to educate the attendees about what alternatives exist to commercial databases. It is a place where people come to learn, to participate and to contribute. In other words, it's a great conference, and if you attend, it will be better.

We are seeking talks related to Open Source Databases of all kind, not just relational databases! Submission about tools and technologies related to OSS databases (e.g. connectors/APIs) are also welcome.

Submitting your proposals

We will use FrOSCon's Pentabarf conference coordination system to collect talk submissions and perform the organizing and scheduling of the talks. Please create an account there, if you don't have one already. Once you have activated your account via the email address you provided, please log into the system and create a new event. Make sure to select track OpenSQLCamp for your submission!

The deadline for submitting your proposal is Sunday, July 19th, 2009!

We will try to synchronize our schedule and speaking slots with the main conference program, to allow easy switching between sessions in the Developer Rooms and the main conference. So your talk should be put into the "Lecture" format and will last one hour (incl. Q&A).

We will try to perform the review and voting about the sessions in public, so the community and potential audience will have a say about which sessions they want to listen to. The details of how this will be done are still under discussion.

A number of database-related talks have already been submitted to the general FrOSCon program. The FrOSCon organizers will evaluate if some of these talks would be more suitable for the OpenSQLCamp track, but stated that they would be interested to keep some of the submitted sessions as part of the main conference program.

Some ideas and suggestions for submissions

• An introduction/overview about a certain database project/product or related tool
• A deeply technical and developer-centric session about some project's internals or an API to connect to a database
• Providing "best practices" information for administrators

Any submission is welcome, as long as it has technical content and it's not a vendor pitch for a commercial program! Open Source is a prerequisite. The conference languages are German and English, so your talk could be of either language.

You should also read Giuseppe's advice on how to get your proposal accepted (it was aimed for the MySQL Conference, but the overall message still applies).
Baron Schwartz and Colin Charles wrote about the same topic - please keep these recommendations in mind when writing up your proposal! This makes it much easier for us to rate and review your submission.

Suggested projects

Some database projects and related technologies that we would like to be present at OpenSQLCamp include the following (in alphabetical order, without claim to completeness):

• Axion
• Berkeley DB
• CouchDB
• Apache Derby / Sun JavaDB
• Drizzle
• Firebird
• Hadoop
• Infobright
• HSQLDB
• Ingres
• MySQL
• Perst
• memcached
• Pentaho
• PostgreSQL
• SQLite
• Continuent Sequoia/Tungsten Replicator

We plan to approach and invite these communities directly to participate and contribute.

As you may have heard, we're switching to a new release model with the upcoming MySQL 5.4 release.

If you are curious to learn more about what will change in the way in which future versions MySQL will be developed and released, make sure to attend our next MySQL University session about The New MySQL Release Model on Thursday, 11th of June, 14:00 UTC. Tomas Ulin, our director of MySQL server development will go through the planned changes and would also like to get your input and feedback on these changes.

We're using DimDim for broadcasting this session, which allows you to listen to the audio while watching the slides with your web browser. You can comment and discuss via a chat function, too! We're looking forward to your input. To attend, point your browser to this address (Adobe flash player required).

The session will be recorded and posted on the MySQL Forge Wiki, so you can watch the presentation later as well. You can also provide your feedback on the release model by posting on the MySQL Internals mailinglist.

Today I received a confirmation that I will be giving a talk about "Working for a virtual company" in the main conference track of the Free and Open Source Conference (FrOSCon) in St. Augustin, Germany (August 22nd+23rd). Yay! I've been giving talks at every FrOSCon since its inception in 2006, so I am happy that I will be able to continue this tradition. FrOSCon is really a gem among the various Linux and Open Source Conferences in Germany — it takes place at a nice venue, the weather is usually warm and sunny and the conference organization is just great. And they of course always have a good lineup of speakers and OSS projects! As for the last years we (Sun/MySQL) will support the event by sponsoring and we will likely have a booth there as well. My colleague Joerg Moellenkamp also received his confirmation, it's quite likely that he'll be speaking about Solaris/OpenSolaris, as that's his home turf

In addition to that, the organizers agreed on providing us with a "Developer Room" for both days, which we would like to use to set up a subconference about Open Source Databases (there will also be a dedicated Java Subconference this year). Dubbed the "OpenSQLCamp 2009, European Edition", we plan to organize two days of talks and presentations to spread the word about the vibrant communities and large ecosystems around Open Source Databases, and to educate the attendees about what alternatives exist to commercial databases. So this will by no means be limited to MySQL only! The more variety, the better. I've set up a page on the OpenSQLCamp.org Wiki with some more details. More information will follow in the coming days. If you're interested to contribute, submit a talk or to know more, please also join the opensqlcamp discussion group! I'd like to thank Sheeri Cabral and Baron Schwartz for giving me a hand with the infrastructure - your help is appreciated!

My last post about Basic MySQL Security generated a number of interesting comments, thanks for all your feedback! I'd like to address a few points that were mentioned there:

While the problem seems to be a non-issue on Linux, Keith Murphy stated that the password might still be visible on other Unix operating systems (e.g. Solaris), as described in Bug#11952 in our bug database. According to the bug report, it depends on the implementation of "ps" — there seems to be a BSD variant (/usr/ucb/ps) as well as a SysV implementation (/usr/bin/ps).

However, on my tests on OpenSolaris (2008.11), both still displayed the password! So be aware of this when working on non-Linux systems and better double check the behaviour on your particular platform. The bug report provides a few more details about this issue, apparently it cannot be fixed for all platforms.

I also pointed out that the password will end up in your shell history and Jay Pipes emphasized this in his comment. As I wrote, you need to make sure that your shell history file is properly protected against access by other users! Usually, a chmod 600 ~/.bash_history will fix this. Most shells create these files with appropriate permissions automatically or can be configured to do so (check your shell's manual page with man `basename $SHELL`).

But there are more potential password leaks that I would like to mention, while we're on the topic: the mysql command line client maintains a history file of its own, that you should be aware of. The history is convenient for easily going back in your list of previous SQL statements by using the Up/Down cursor keys or searching for a particular query by using the CTRL+R shortcut. However, the MySQL client stores all your SQL statements in a file ~/.mysql_history in your home directory by default, similar to how your unix shell does it. So if you are adding new MySQL user accounts using the GRANT ... IDENTIFIED BY PASSWORD... statement, the user's password will be written to the history file in plain text, visible to everyone who has the appropriate file system privileges. Keep that in mind when performing administrative tasks on a MySQL server and make sure to restrict access to that file! By default, the client creates the file with only read and write permissions for the user (600), but if you want to be on the safe side you can of course remove it after you entered passwords on the MySQL command line. As an alternative, you can start the MySQL command line client by using the "-q / --quick" option, which skips using the history file for this particular session. If you can live without a command line history in general, you could simply replace that history file with a symbolic link to /dev/null:

$ ln -fs /dev/null ~/.mysql_history

Alternatively you can set the environment variable $MYSQL_HISTFILE to point to either a different file name or to /dev/null directly. By the way, all of this is documented in the mysql(1) man page as well as in the Reference Manual.

Another attack vector for local users to obtain MySQL passwords are the MySQL server log files — anyone with file system access to the binary log files can extract possible GRANT statements from there using the mysqlbinlog command! So you need to make sure that these files are properly secured from being accessed by regular users as well.

In general, the best approach is to not allow regular users to log into your MySQL Server system in the first place. Shell access should be restricted to the system's admin accounts, access to the MySQL server should strictly take place via the MySQL Client/Server protocol. Which, by the way, is not using encryption by default — make sure to use SSL or an SSH tunnel when accessing a MySQL server through an untrusted network. Otherwise you may also reveal confidential information like user passwords to unauthorized entities...

Reading through the comments in Ronald's second post about More Basic MySQL Security, I noticed that there seems to be a misunderstanding about the implications of providing passwords to the mysql command line client via the "-p" option:

Jaka Jančar wrote:

What’s more insecure is passing password as an argument to MySQL, like you’ve written (-p[password]), since that can really be seen by anyone.

Shlomi Noach wrote:

While Linux security is often considered good, an astonishing weakness is “ps aux”, where every user can see every process running. Therefore, even user “games” can see that user root is running “mysql -pmypassword”. I find this a much higher risk than putting the MySQL’s root password in file, where a user need to gain access to machine’s “root”

Well, this isn't actually the case! Try it for yourself and start the MySQL command line client by providing a users's password via the "-p" option:

$ mysql -u root -p<somepassword>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34 SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:(none) >

Now, open a second shell and check the process list:

$ ps aux | grep "mysql -u"
lenz     19357  0.0  0.0   7868  2884 pts/4    S+   12:30   0:00 mysql -u root -px xxxxx

As you can see, the password has been obfuscated by replacing the password with "x" characters. This action is performed by the mysql client after parsing the -p option — let's take a look at the sources:

  case 'p':
    if (argument == disabled_my_option)
      argument= (char*) "";     // Don't require password
    if (argument)
    {
      char *start= argument;
      my_free(opt_password, MYF(MY_ALLOW_ZERO_PTR));
      opt_password= my_strdup(argument, MYF(MY_FAE));
      while (*argument) *argument++= 'x';   // Destroy argument
      if (*start)
  start[1]=0 ;
      tty_password= 0;
    }

In theory, there is a very short window in which the password can be seen in plaintext (after the mysql process has started up until it has performed the obfuscation), but capturing this information takes really good timing.

But it's of course true that this information also gets stored in the user's shell history file, e.g. ~/.bash_history, where it potentially could be seen by other users, if the file permissions are not set up correctly. So always make sure that you entire home directory (or at least the history file) are protected against being read by other users (using chmod/chown appropriately)!