The latest from the MySQL community

In openSUSE we’ve got currently MySQL Community Server, MariaDB and MySQL Cluster. From all of these we have even multiple versions. Although these packages are different, they are quilte similar. So I’m handling them in a little bit special way.
When I was adding MariaDB I knew that packaging will be quite similar to the MySQL Community Server. So I took some parts of .spec file away into separate files so I can sync them easily and left only package dependent parts in .spec files. Later on, I created special git repository and few scripts to handle patches and patch sharing among these variants. And lately I automatized tre rest of the manual syncing I was diong. So today I want to present how do I do MySQL packaging today. And that is also some tutorial on how you can modify these packages easily or even create packages for other variants like Percona
Everything starts with …

Last time I described how to contribute quite to any package in openSUSE Build Service. But I left out the most important part. I haven’t shown how to change anything. This time I want to show you, how to create patches, if you need them, easily. Let’s start start with package we checked out from obs. Creating patch for anything is different only in first few steps.
First we got to the directory where do we have the package checked out. We run

quilt setup

This command will parse the .spec file, unpack tarball and prepare all quilt stuff. Now is time for patching, so let’s enter the newly created directory and try following command

quilt push

You know that in .spec there might be some patches. quilt push will take first patch that is not applied yet …

I promised that I’ll write a post about how you can contribute. There are several ways how to contribute to MySQL, but most of it means modifying packages. And as everything in openSUSE is built using openSUSE Build service, first post will be actually pretty general obs and osc howto. In the next posts, I’ll go deeper into specific details of MySQL packaging.

Find the package

If you want to play with any package in openSUSE Build Service, you need to have a Novell login and preferably the osc command line client for obs. You can do most of the stuff from web as well, but this way is more comfortable So let’s say that we want to play with MariaDB. First we have to find package we want to update. This can be easily done on the web. Just take a look at packages at server:database repository. mariadb is …

Earlier today I asked on this blog and everywhere else people to fill in a simple survey of how do they use MySQL, which one and what should I focus on. I already have more than 30 answers (don’t stop answering, keep providing more) so I will have to publish the results and do something with them. But first I’ll let the survey run for at least a week Oh, and even though I write MySQL, this post is general about all MySQL variants (MariaDB, MySQL Cluster, … (not counting Drizzle)).
Now what is behind this survey? Well, I have a lot of stuff to do and MySQL is not my only responsibility. And I know that sometimes it can use some extra hands. Sometimes I get lucky and we have new beta packaged same day as it …

This is not really a blog post. I just would like to know you opinion on state of MySQL at openSUSE and openSUSE Build Service. But I’m interested in your feedback even if you are not using openSUSE or even if you are not using packages I provide. As my TODO is quite long and I can’t do everything, I would like to know what should I pay the most attention to. Later I will publish a post about the current state of MySQL and how you can participate

Earlier Peter at Percona posted an interesting article about MySQL upgrades. As a database consultant, it’s not uncommon for me to have enterprise-level customers that still run MySQL 4.0 or 4.1  - for a number of reasons that I will enumerate later, this kind of migration can be tricky. I  just finished such a major upgrade so I will share my opinion on the subject.

The Methodology

If you are in a replicated environment you may want to migrate one of your slaves first as a prototype. Dumping and reloading is the recommended way because of many storage file format changes between 4.0 and 5.0 versions. For heavy databases I usually choose the parallel dump approach to save some precious time, either maatkit’s mk-parallel-dump or domas mituzas’ mydumper tools will do the trick.

Note that binary …

I wrote about my MySQL wishlist on November 14th 2007 and now it's time for an update. I will copy-paste the old entry. The original text will be in italics.

1. Per user and/or per database quota
Would very useful in setups for shared hosting. This would also prevent one database from bringing down the whole server. Separate tablespaces on different mountpoint can ease the pain, but I consider that a nasty hack.
No update. Still problematic

2. External authentication
I've seen numerous scripts which fetch the authentication info from ldap, a file, another database or some other authentication store. This should be integrated into mysql. The mysql grant tables should be pluggable so it is possible to write a custom authentication plugin. We already have plugable engines and function (UDF) so this shouldn't be that hard …

Earlier I reported about two crashes related to MySQL 5.0.22 on Ubuntu 6.06 LTS.

I think those bugs show a lack of testing on the side of Cannonical/Ubuntu. And for MySQL there is a quite good test suite available, so it's not rocketsience.

There are multiple reasons why you could use the MySQL Test Framework:
1. Test if bug you previously experienced exists in the version you are using or planning to use.
2. Test if configuration changes have a good or bad result on the stability of mysqld.
3. Test if important functions still return the correct results (especially importand for financial systems)

$ echo "SELECT @@version;" > version.test
$ cp version.test version.result
$ mysql < version.test >> version.result
$ mysqltest --result-file=version.result …

1. Set this variable
thread_stack = 265K

2. Execute this query
mysql> SELECT 0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0
+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+
0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0
+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+
0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0
+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+
0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0
+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+
0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0
…

I found a new crasher in the MySQL 5.0 version which ships with Ubuntu 6.06 LTS.

> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY mu.user) a;
ERROR 2013 (HY000): Lost connection to MySQL server during query

The bug report: LP392236

On MySQL 5.0.51 on Debian stable it returns this error (like it should):
ERROR 1054 (42S22): Unknown column 'mu.user' in 'order clause'

The correct query should be like this (Using culumn a number):
> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY 1) a;