Friday, November 28, 2008

Yet another replication trap


replication and engines

When I filed Bug#39197 replication breaks with large load with InnoDB, flush logs, and slave stop/start, I genuinely thought that it was a serious problem. I was a bit puzzled, to tell the truth, because the scenario that I was using seemed common enough for this bug to be found already.
Anyway, it was verified independently, but there was a catch. The script in the master was using SET storage_engine=InnoDB to create the tables necessary for the test. That looked good enough to me. The script was indeed creating InnoDB tables on the master. The trouble was that the "SET" command is not replicated. Thus the slaves were using the default storage engine (i.e. MyISAM). That reduces the scenario to a case were I was splitting a transaction and trying to apply it to a non transactional table. Not a bug, as Andrei Elkin and Sveta Smirnova correctly found out, but not an easy error to spot either.
According to the manual, using the storage_engines variable allows for a certain degree of flexibility in replication. The non-replication of this instruction that affects DDL commands is described as a feature. In my experience, it's a bug, by design even, but this is my personal opinion.

Wednesday, November 26, 2008

Using MySQL 5.1 in production for over a year


Adam Donnison

Lenz has just published an interview to Adam Donnison, Senior Web Developer in the MySQL web team. Under his watch, the site at mysql.com has been powered by MySQL 5.1 for one and half years.
In production! Talk about eating your own dog food!
In addition to the database server, Adam has spearheaded the test of Enterprise tools and the Query Analyzer. Excellent example, Adam!

Monday, November 17, 2008

Meeting MySQL community in Paris - November 18, 2008


MySQL Proxy

I will meet the MySQL community in Paris on November 18, 2008 at 19:30.
I will speak about MySQL Proxy Wizardry, and discuss with the community about any subject that will come up.
The place:
1 rue royale, 227 Bureaux de la Colline
92210 Saint-Cloud
Bâtiment D 9ème étage
Tel : 01 78 15 24 00

Sunday, November 16, 2008

OpenSQLCamp - Good Job, Baron!

opensqlcamp - Good job, Baron!
Kudos to Baron Schwartz, who has shown everyone that you can organize a conference from scratch, without the logistics of a company. He has motivated other people in the community and received spontaneous help, which in turn became a web site, sponsor donations, free food for the participants, T-shirt, bags, internet connection, and a glorious feeling of camaraderie throughout the event.
Well done, Baron!

The picture shows a surprised Baron holding a T-shirt covered with "thank you Baron"s, secretly signed by all participants and finally awarded to him in public.

Saturday, November 15, 2008

OpenSQLCamp - Pictures from the first day

The OpenSQLCamp started yesterday in Charlottesville, VA.
As expected, the gathering is impressive. There are many well known names from the open source database world. MySQL is probably overrepresented, but that's fair, considering its wide adoption.

Yesterday was an informal "meet anyone and let's see what we do tomorrow". The schedule, as you can see, is very dynamic.
OpenSQLCamp
OpenSQLCamp - flexible schedule

Friday, November 14, 2008

At the Open SQL Camp


Just arrived to Charlottesville, VA, with Dups.
We drove from Baltimore, MD, where we had an enjoyable MySQL meetup yesterday. The room was small, but packed to capacity with open source enthusiasts.
Now waiting for the "opening ceremony" at 6pm, when we will fine tune tomorrow's schedule. The list of attendees is impressive. More than 100 people are registered. It seems to be a very interesting gathering.

OpenSQL Camp 2008

Wednesday, November 12, 2008

circular replication - Will you use it?


MySQL Circular replication
Three unrelated facts reminded me of a popular article about MySQL replication that I wrote long time ago.
  • A reader of that article told me that he used information I wrote to set up a circular replication in production, and it is still working!
  • A new page on circular replication in MySQL manual was just published.
  • During yesterday's meetup in New York, an attendee asked advice about broken replication in a circular scheme, and I suggested reading all the caveats that I listed in my article.

I recall reading in High Performance MySQL 2nd edition that circular replication is not recommended, and I agree on the reasons. I wrote that article as it was the result of my research, but I don't recommend circular replication either.
All of the above lead to the questions:
  • Who is using circular replication in production?
  • Who prefers circular replication to more robust methods, and why?

Baltimore MySQL meetup - November 13




The Baltimore MySQL Meetup is scheduled for November 13th at 6pm
The place:
Lotame Solutions, Inc.
6085 Marshalee Drive
Suite 210 Elkridge, MD 21075

We'll talk about MySQL Proxy, and anything else that looks suitable, depending on the crowd's requests.
Thanks to Greg Haase for organizing.

Tuesday, November 11, 2008

New ideas about MySQL Sandbox at the Boston meetup


MySQL Sandbox

Presenting a topic to a new audience is a two ways learning experience. The attendees learn what the presenter came prepared to say, and in presence of an attentive audience, the presenter can learn something on the spot.
During today's Boston MySQL meetup, I got two interesting ideas aboou how to improve MySQL Sandbox.

  • Using the Sandbox as a test for the future production environment is useful. But it would be even more useful if there were a simple way of transferring the contents of a well tested Sandbox to the main server, or transferring the setup of a successfully tuned replication scheme to several production servers. Currently, this can't be done easily, but I have a few ideas worth checking.

  • The Sandbox can provide one or more servers for development and tests. Every server can be created by a knowledgeable administrator. It would be useful to have a sandbox provider that can create a sandbox on the spot, with an automatic check about the port being available. So the tester can simply ask for another server of a given version, and the Sandbox will be created with a non-conflicting port. There are several ways of implementing this idea. Perhaps the easiest one, suggested by an attendee, is to register the used ports.

Both these ideas are worth exploring. If you happen to care about them and have a good idea on their implementation, please contact me, or simply comment to this post. Thanks to all the ones who have brainstormed about these issues!

Monday, November 10, 2008

New York MySQL meetup - November 11




The New York MySQL Meetup is scheduled for November 11th at 6pm
The place: Suspenders Restaurant
111 Broadway
at Thames St New York, NY 10006
212-732-5005

We'll talk about MySQL Proxy, and anything else that looks suitable, depending on the crowd's requests.
Thanks to Philip Antoniades for the organization.

Sunday, November 09, 2008

Boston MySQL meetup - November 10




The Boston MySQL meetup is held on November 10th at 7pm. The place is
MIT Building E51
4 Amherst St
Room 372 Cambridge, MA 02142
857 205 9786

We'll talk about MySQL Sandbox, and about any relevant MySQL topic.

On the road with Dups




Now that Dups is the Community Relations Manager for North America, we'll start working together in the most active way. Next week, we'll be busy at meetups in New York and Baltimore, and then it will be full immersion in Charlottesville for the Open SQL Camp.
OpenSQL Camp 2008

Saturday, November 08, 2008

Italians strike back





What follows is the text of my "Swedish" blog.
Look mom, I am blogging in Swedish!
I am starting a blog in Swedish. Now, why should I do such a thing? I usually blog in English, and I don't even bother blogging in my native Italian. Most importantly, I do not speak Swedish at all. So, why bother?
My friend and colleague Kaj Arnö had the gall of starting a blog in Italian, a fact that surprised me and made me envious at the same time.

He started talking about Italy, a country he visits often and he loves. I have little experience of Finland, having visited only once, but where I enjoyed warm hospitality.
I Visited Kaj in his homeland, in Nagu, a charming island West of Turku. The weather was not warm, but friendship and good food was a suitable compensation.

I am choosing to blog in in Swedish because Kaj's mother tongue is Swedish, although he lives in Finland. During my visit to Nagu and other island of the Finnish archipelago, I heard little Finnish and plenty of Swedish.

This experiment wants to show that anyone can blog in (mostly) any language, given the right tools. Hello Kaj!

Monday, November 03, 2008

A quick usability hack with partitioning

A few days ago I was describing a common grievance when using partitions.
When you care at a table, like the following
CREATE TABLE t1 ( d DATE ) 
PARTITION by range (to_days(d))
(
partition p001 VALUES LESS THAN (to_days('2001-01-01'))
, partition p002 VALUES LESS THAN (to_days('2001-02-01'))
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);

Then you have the problem of finding out the original values. SHOW CREATE TABLE doesn't help.
show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002 VALUES LESS THAN (730882) ENGINE = MyISAM, PARTITION p003 VALUES LESS THAN (730910) ENGINE = MyISAM) */

I spent some time writing a MySQL Proxy module that, among other things, gives you the original values with SHOW CREATE TABLE. However, I was mostly reinventing the wheel in this case, because there is some useful data in the information_schema. Look:

select
PARTITION_NAME,
PARTITION_DESCRIPTION
from
information_schema.PARTITIONS
where
table_schema ='test' and table_name='t1';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p001 | 730851 |
| p002 | 730882 |
| p003 | 730910 |
+----------------+-----------------------+

This is as informational as the output of SHOW CREATE TABLE, but having the data in a table, allows us to do the trick.

select
PARTITION_NAME ,
from_days(PARTITION_DESCRIPTION) AS original_value
from
information_schema.PARTITIONS
where
table_schema ='test' and table_name='t1';
+----------------+----------------+
| PARTITION_NAME | original_value |
+----------------+----------------+
| p001 | 2001-01-01 |
| p002 | 2001-02-01 |
| p003 | 2001-03-01 |
+----------------+----------------+

More on this subject when I finish working on my Proxy partition helper.