Maybe this is obvious, but I post it anyway, just to remind myself should I need it again.

Recently I had to change a table that I had not completely thought through when I first created it. The structure was so simple, I did not think I could do anything wrong with it:

CREATE TABLE `parent` (
  `par_id` bigint(20) NOT NULL,
  `somevalue` varchar(20) default NULL,
  PRIMARY KEY  (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `child` (
  `x_parid` bigint(20) default NULL,
  `value` bigint(10) default NULL,
  KEY `fk_parid` (`x_parid`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`x_parid`) REFERENCES `parent` (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There is a 1:0..* relationship between parent and child. Some sample data:

mysql> select * from parent;
+--------+--------------+
| par_id | somevalue    |
+--------+--------------+
|      1 | Parent No. 1 | 
|      2 | Parent No. 2 | 
|      3 | Parent …

Maybe this is obvious, but I post it anyway, just to remind myself should I need it again.

Recently I had to change a table that I had not completely thought through when I first created it. The structure was so simple, I did not think I could do anything wrong with it:

CREATE TABLE `parent` (
  `par_id` bigint(20) NOT NULL,
  `somevalue` varchar(20) default NULL,
  PRIMARY KEY  (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `child` (
  `x_parid` bigint(20) default NULL,
  `value` bigint(10) default NULL,
  KEY `fk_parid` (`x_parid`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`x_parid`) REFERENCES `parent` (`par_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There is a 1:0..* relationship between parent and child. Some sample data:

mysql> select * from parent;
+--------+--------------+
| par_id | somevalue    |
+--------+--------------+
|      1 | Parent No. 1 | 
|      2 | Parent No. 2 | 
|      3 | Parent …

Note: this "script" is a one liner really and not something meant for general purpose use. It is very specific to my environment so I take no responsibility of any kind. It is very raw so use at YOUR OWN risk.

Last year at the MySQL camp, I heard about Paul Tuckfield's script that pre-fetches relay logs to speed up replication. Since then I got really interested in implementing it but never got the time. This year at MySQL conference Paul's script got a lot of attention again. Since it hasn't been released and I really need to start testing this on my test server. So I started hacking on creating a shell script to dig deeper into what's involved. Once I have that I may work on porting it to other languages.

To make the process of writing this script easier for others in their favorite languages I thought I will go through what's involved in creating this script on my blog. That way when you would like to write an …

I've written before about how to make MySQL replication reliable. One thing I think you need to do to make statement-based replication reliable is eliminate temporary tables. I found an elegant way to replace temporary tables with real tables in the systems I maintain. This article explains how.

A few people have asked me how fast MySQL Table Checksum is. As with so many other things, it depends. This article shows how long it takes to checksum real data on a production server I help manage, which might give you a rough idea of how long it’ll take on your servers. The server and workload This server is a replication master running MySQL 5.0.38. It is a Dell Poweredge 1800 series with dual Xeon 3.

By Tim O'Reilly

In today's article about new open source releases from Sun, Microsoft, and Adobe, Wired News quoted our very own Nat Torkington:

"Microsoft is in a new era. The Bill Gates cutthroat 'we win by killing' days are passing," says Nat Torkington, co-chair of the O'Reilly Open Source Convention. "Microsoft exists by making users happy. And sometimes -- just sometimes -- you make Microsoft users happy by giving them a technology that wasn't invented at Microsoft."

Nat sure is quotable (and you should definitely read the full text of his comments), but I'm not sure that he's entirely right, at least not about the industry as a whole. Yes, in an era of open standards and open source software, it's easier for users to …

So I have been doing quite a bit of benchmarking and while most of my tests have been amazingly simple, I thought I would share some of my findings. The following is the total time it took to run a series of simple inserts over various threads and engines:

You’ve heard me on the MySQL Podcast at http://www.technocation.org, now come work with me, the “She”-BA!

The company I work for is an online social networking/dating site. Our main product is for men seeking men in 87 countries throughout the world. We’re looking for another MySQL DBA, as designing schemas maintaining data integrity for our 1 million users (and growing fast!). The salary is dependent upon experience of course, but the company I work for pays on the high side of the industry standard for the Boston area.

Application Instructions
Please send cover letter, résumé and sample schema to work@online-buddies.com, with “MySQL DBA” as your subject. The
sample schema should reflect your abilities, so if you send along a schema you would like to see improved, include a description of what you would love to do to that schema to make it better.

…

This may be obvious to some but it wasn't to me so I thought I'd write about it.  Recently I discovered a problem with our implementation of DbConnectionStringBuilder.  Whenever you changed a field that took a string (i.e. character set or server), the connection string property would not update.  I know the changes were applied because I developed a test application that queried the connection string.  It was correct even though the UI of the property grid was not. 

After an embarrassingly long time working on this I emailed a friend of mine who I felt would know the answer.  Turns out he didn't but he knew someone who did and not more than 30 minutes after talking with him I had my answer.  Each property of MySqlConnectionStringBuilder needs to be decorated with the RefreshPropertiesAttribute.  This causes the property grid to refresh itself when they change.

Welcome to the 44th edition of Log Buffer, the weekly compendium of database blogs. This one’s a grab-bag. Let’s start with some Oracle stuff this week. Christian Shay notes that Oracle 10g for Windows Vista, is available for download. That is sure to please Oracle doyen and fervent Vista booster Doug Burns, who also has [...]