While I was at the FOSDEM 2006 in Brussels, I got approached by the folks from Source21.nl about performing an interview about MySQL with them. But as David Axmark (who is one of the co-founders of MySQL) was around as well, I quickly directed them to talk with him instead. The vidcast (length is ~10 minutes, the WMV is ~30MB) is now available online in various formats (OGG, MP4 and WMV) from their web site. Have fun.

ApacheCon Europe is the official European conference of the Apache Software Foundation (ASF). This outstanding event creates a unique platform for the Open Source community in Europe to come together to gain deep insight into techniques and methodologies critical to the advancement of Open Source technologies, and gain skills to optimize the power and versatility of Apache software. The event also provides a forum to discuss key issues facing the community and to hear about developments to come in 2006.

At ApacheCon Europe, you can experience first-hand what the ASF technologies and development communities do for you and your enterprise. You will meet the big players, project leaders, inside experts and independent innovators at this annual gathering of all things Apache. Come share your knowledge, hit upon new ideas, find solutions and connect with your peers at this educational, fun-filled gathering of users, developers, and vendors …

Continued work on the backup API. I'm pretty much done with the logic; now I need to look at interfacing, which could take longer. Set up a test server with debug tracing enabled to investigate what happens when the client issues specific commands:

• Build a tree (in this case, the most recent 5.1).

• === grog@eucla (/dev/pts/1) /home/MySQL/5.1-Online-Backup 7 -> cd mysql-test/
  === grog@eucla (/dev/pts/1) /home/MySQL/5.1-Online-Backup/mysql-test 8 -> ./mysql-test-run \
  --skip-ndb --start-and-exit --debug
  Logging: ./mysql-test-run --skip-ndb --start-and-exit --debug
  Installing Test Databases
  Removing Stale Files
  Installing Master Databases
  running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=.
  --datadir=./var/master-data --skip-innodb --skip-ndbcluster --skip-bdb
  --language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/
  Installing Slave Databases
  running  ../sql/mysqld …

The German translation of the MySQL 5.1 Reference Manual is complete.

Today I faced with a problem that could arise when dealing with replication.
I had on the master host a table with 534,587 rows and the replicated one on one of the slave hosts with 534,603 rows (16 rows more).
This happened because of some old script that made INSERTs connecting directly to that slave. Unfortunately the replication didn't stop because new INSERTs made by the master host never duplicated entries. I discovered this situation just few days ago.

I wanted to find out how this happened.
I was faced to the problem to determine the IDs of the sixteen phantom-rows on the slave table.

Instead of writing a Perl script (as I would have done a year ago) to read all IDs from master and then find mismatching IDs reading the same values from the slave, I created a Federated table on a non-replicating database (binlog-ignore-db=test in the my.cnf file):

CREATE …

Using stored procedures is a good thing to reduce complexity of PERL scripts.
Since my company switched to MySQL 5 (last month) I started to write some SPs.
But a problem arised.

DBD::MySQL for Perl has a problem dealing with multiple rows statements like simple SELECTs in SP.
So, I was not able to grab results from routines.

As you can see here the problem is well known and a patch was proposed , but at the moment there's no solution. My DBD::MySQL version is 3.0003

So, the only way you can grab the SP's results in a perl script is to store them into OUT parameters and read them through user variables.

Let's see a simple example.

Suppose you have the SP below to count the number of published news articles …

The MySQL code base is quite portable - we do build it on a wide variety of compilers, operating systems and architectures, sometimes just to test if it actually builds and passes the test suite. In fact, some bugs only surface under certain environments, so maintaining this diversity helps us to catch problems quickly. But of course there are still many platforms that are not directly supported or no longer maintained. For example, we removed support for OS/2 from our code base some time ago.

Just recently, somebody from Stratus posted patches to make MySQL build on the Stratus OpenVOS environment to our …

Sometimes I see people thinking about buffers as "larger is always better" so if "large" MySQL sample configuration is designed for 2GB and they happen to have 16, they would simply multiply all/most values by 10 and hope it will work well.

Obviously it does not. The least problem would be wasting memory, allocating a lot of memory for operations which do not need it. Allocating 10MB to sort 10 rows will not make it faster than allocating 1MB, both are more than enough.

However not only it may cause memory being wasted but you may see some of queries actually performing slower, and not because the system starts to swap. Generally you want buffers and other values to be sized "just right" - working with smaller data structures would improve cache locality, will make it easier for OS to manage memory as well as cold provide quite unexpected improvements.

sort_buffer_size - recently I worked with case which …

I meant to post something about this months ago, but I came across it again in back issue of SD Times I was reading while travelling to Europe.  Coverity did a study of open source technology using their code analysis tools and demonstrated that the LAMP stack has fewer software defects than open source in general.  On average, the LAMP stack had 0.290 defects per thousand of lines of code, compared to nearly twice that across 32 open source projects.  MySQL and Perl had the lowest defect rates in the LAMP stack, whereas Python and PHP had higher rates of defects.  Last year, Coverity did an analysis of MySQL showing that its defect rate was …

I'm very excited that MySQL implements this feature in version 5.1.x, all the new features are bridging the gap between open source and propietary RDMS like Oracle, this feature particularly makes MySQL suitable for Data warehouse systems, not just OLTP systems as it's well known.

There are a lot of benefits in the use of table partitioning like:

- Eases the administration of the tables
- Reduces time taken in large amount of DML
- Improves the response of some queries

Table partitioning allows the DBA to define how the table and the data cointained could be stored phisically, while remaining the logical structure the same. For example a table with data of 5 years (theorically a large table) could be splitted (depending on the needs) in 5 partitions, reducing the time taken by some queries that may fit into a particular year, easing the administration of the table when the data from a year …