Experienced MySQL DBAs know that temp tables cause major problems for MySQL replication.  It turns out the converse is also true:  replication can cause major problems for temporary tables.

In a recent customer engagement we enabled Tungsten Replicator with a MySQL application that originally ran on a server that did not use replication.  QA promptly discovered reports that previously ran in 10 seconds were now running in as many minutes.  It turned out that the reports used temp tables to assemble data, and these were being written into the master binlog.  This created bloated binlogs and extremely slow reports.  We fixed the problem by enabling row replication (i.e., binlog-format=row in my.cnf).

A common DBA response to temp table problems is to try to eliminate them completely, as suggested in the excellent …

This is the second and final part of my notes from the MySQL conference. In this part I'll focus on the technical substance of talks I saw, and didn't see.

More than ever before I was a contributor rather than attendee at this conference. Looking back, this resulted in seeing less talks than I would have wanted to, since I was speaking or preparing to speak myself. Sometimes it was worse than speaking, for instance I spent half a day picking up pewter goblets from an egnravings shop... (congratulations to all the winners again :-) Luckily, I can make up for some of that by going back and browse their slides. This is especially important whenever 2 good talks are scheduled in the same slot, or in the same slot when I was to speak. So I have categorized topics here along various axes, but also along the "things I did see" versus "things I missed" axis.

My own talks

…

I have finally recovered from my trip to Santa Clara enough that I can scribble down some notes from this year's MySQL Conference. Writing a travel report is part of the deal where my employer covers the travel expense, so even if many people have written about the conference, I need to do it too. And judging from the many posts for instance from Pythian's direction, Nokia is perhaps not the only company with such a policy?

Baron's keynote

There has usually always been something that can be called a "soft keynote". Pirate Party founder Rick Falckvinge speaking at a database conference is a memorable example (I still keep in touch with him, having met him at the Hyatt Santa Clara). This year there was one less day, and therefore less keynotes. The soft keynote was therefore taken care of by Baron using some time out of Peter's opening keynote. Baron's talk was an ode to the conference itself, underscoring the meaning of the …

I wanted to evaluate MariaDB's virtual column and see if it could store business rules next to table data. With virtual columns this could be done and if you specify as 'virtual', would not even take up space on disk.

The imaginary use case is that there is a sales team in a company and you would like to evaluate if a particular salesperson is eligible for a bonus. In order to get a bonus, you need to sell above the average for the day and be in the top 5 amongst the salespeople.

So here is what I did:

MariaDB [test]> create table salespeople (id int unsigned not null auto_increment primary key, salesperson_id int unsigned not null , `date` datetime not null default 0, sold decimal(15,2) not null default 0, day_avg decimal(15,2) not null default 0,  above_avg char(1) as …

I’d had some difficulty manually creating my own windows service for MariaDB (worked fine from the installer), but it was due to the way I was creating it, so I wanted to share the proper approach:

Old Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\"
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\""
DisplayName= "Maria55" start= "auto"

New Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\"
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\" maria55"
DisplayName= "Maria55" start= "auto"

The key is adding the name, maria55, after the –defaults-file=.. option, but still within the “” that belong to “binpath”.

This extra parameter exists so that mysqld knows whether or not it was started as a service or not.

Without it, the server does not know, and …

On Friday last week, after the intensive days of the conference, Ars Technica wrote and published a nice article about MariaDB including many of the messages we had been delivering during the conference, http://arstechnica.com/business/news/2012/04/mysql-founders-latest-mariadb-release-takes-enterprise-features-open-source.ars.

MariaDB seals

Last year, when it became clear that O’Reilly wasn’t going to arrange the MySQL user conference in the future, there was a lot of discussion on who should arrange it. In the end Percona was pretty fast informing everyone that they had booked the convention center in Santa Clara to arrange the conference this year. Now with the …

I am very pleased to say that earlier today, SkySQL announced it has raised $4 Million in Series A Round Funding.

Let me post the main part of the press release here:

SAN JOSE – April 18, 2012 – SkySQL, the first choice in affordable database solutions for the MySQL® and MariaDB® databases in the enterprise and the cloud, today announces that the company has raised $4 million in Series A funding from a number of investors, including OnCorps, an elite peer-based community of veteran technology investors and advisors committed to bringing better, cost-disruptive technologies into the mainstream. Also funding the round are European investors including Finnish Industry Investment Ltd., Spintop Ventures and Open Ocean Capital.

SkySQL will primarily use the investment to fund growth in its new product development, including adding critical positions. This is the …

Thanks to all of those who came by our booth and to see Leif’s presentation on Read Optimization, and to my Lightning Talk on OLTP and OLAP at the Percona MySQL Conference and Expo. It was an incredible week and a great place to launch TokuDB v6.0 from! A big thanks to Percona for a great event, to …

Well, as you or may not have heard, MariaDB 5.5 (5.5.23) was declared GA last week!

It was only about 6-ish weeks ago that MariaDB 5.5 had been released as alpha, so the fact it’s already GA is excellent news for all MariaDB users (and MySQL 5.5 users looking to migrate).

Some of the 5.5 enhancements include:

• Significantly more efficient thread pool, comparable in functionality to the closed source feature in MySQL Enterprise.
• Non-blocking client API Library (MWL#192)
• @@skip_replication option (MWL#234)
• SphinxSE updated to version 2.0.4.
• “extended keys” support for XtraDB and InnoDB
• New INSTALL SONAME statement
• New LIMIT ROWS EXAMINED optimization.
• mysql_real_connect() Changes
  In MySQL, and in MariaDB versions before 5.5.21, mysql_real_connect() …

Today I tried to make incremental backups of a MariaDB instance in a MySQL sandbox with Percona XtraBackup.
I used the recently released XtraBackup 2.0. And of course there is documentation about making incremental backups. 

MySQL sandbox makes it easy to run many different MySQL versions on one machine. It does this by changing the port number, data directory, UNIX socket location and a whole lot more.

So I first started with a full backup and after that I used that backup as a base for the incremental backups. To do that I had to specify the port number which is 5522 and the username and password for the msandbox account. As MySQL uses a UNIX socket instead …