A couple of weeks ago, a friend asked about replication on MySQL 4.1.7. I’ve worked with replication in the past, just a quick and dirty job on MySQL 5, and soon forgot about it. This time, I wanted to do it on MySQL 4, and make sure I take good notes for my own benefit. If it can help somebody else, all the better. The official documentation is here. It took a little time to wade through it.

The process below is used to replicate all databases on the master to the slave(s). I will talk about replicating only certain selected databases in a future post.

1. At the master server, check if binary logging is on. Use show variables like ‘%bin%’ to check if binary logging is on or not. If it is not on, turn it on by adding log-bin under [mysqld] section in …

Ok…I’ve been crazy busy with job and home stuff. Doesn’t mean I stop thinking about stuff.

• Do we really need another text editor/Personal Information Manager/MP3 player? I monitor Gnomefiles and Portable Freeware RSS feeds and I’m constantly seeing these types of apps being created, promising to be a better than anything out there with whiz-bang features not seen anywhere else. Got news for you, you aint showing us nuttin’ new.
• The First Bank of Delaware needs to DIAF for offering loans at 99.25% interest. That’s right, 99.25%! It’s done through their CashCall marketing arm. Yes. The one offered up by has-been Gary Coleman. They’re preying on people who can’t manage their money.
• MySQL-Proxy is …

Been a while since I wrote anything…not for lack of anything to write, just real busy. I’m finding myself having to force myself away from the computer to maintain my sanity.

On the home front, I’m going to be installing a ceiling fan in the only bedroom that doesn’t have one. I had to go buy a metal junction box because all the boxes in this house are the blue plastic ones. That’s fine and dandy but I’m not going to hang a ceiling fan from one.  Don’t know if I’ll need it but I also bought a brace so I can mount the box to a rafter if it isn’t close enough to one already. After that, I’m going to start ripping down the ceiling in the basement in preparation for remodeling it. It’ll be a nice mess.

Then I’m off to California for a week - business related. I fly out Sunday morning at 7:30 and fly back Saturday.

Attended a MySQL webinar (man, do I hate that word) on replication and scaling out …

Yesterday I was asked for help concerning a replication problem with one of our test systems. My colleague had already installed a fresh dump he had created with mysqldump ... --master-data. The dump looked ok and contained a master configuration statement:

...
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000127',MASTER_LOG_POS=4462223;
...

The slave was provided with the correct user, password and host name for this master. Nevertheless issuing a START SLAVE did not work. In the slave .err file we found this:

 
...
050327 20:54:51 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)
050327 20:54:51 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
...

After some fiddling around and searching the net for …

Yesterday I was asked for help concerning a replication problem with one of our test systems. My colleague had already installed a fresh dump he had created with mysqldump ... --master-data. The dump looked ok and contained a master configuration statement:

...
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000127',MASTER_LOG_POS=4462223;
...

The slave was provided with the correct user, password and host name for this master. Nevertheless issuing a START SLAVE did not work. In the slave .err file we found this:

 
...
050327 20:54:51 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)
050327 20:54:51 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
...

After some fiddling around and searching the net for …

When we tried to clean up a rather large (4.500.000 rows, 20GB) InnoDB table some days ago, we were astonished by the time MySQL took to complete the task. We had already LIMITed the transaction size, but every single chunk still took minutes to execute. The table itself contains some number columns, including a numeric primary key, and a blob. The delete condition was mainly based on the primary key (being smaller than a predefined value) and status field. After some mails between the support crew and us an optimizer bug was identified: MySQL Bug #28554.

The problem is that in some cases the optimizer makes a bad choice concerning which index to use. It will pick a secondary index that can be used to cover a WHERE indexed_column=<constant> condition, even though it will cause way more data to be scanned than necessary. The primary key for …

When we tried to clean up a rather large (4.500.000 rows, 20GB) InnoDB table some days ago, we were astonished by the time MySQL took to complete the task. We had already LIMITed the transaction size, but every single chunk still took minutes to execute. The table itself contains some number columns, including a numeric primary key, and a blob. The delete condition was mainly based on the primary key (being smaller than a predefined value) and status field. After some mails between the support crew and us an optimizer bug was identified: MySQL Bug #28554.

The problem is that in some cases the optimizer makes a bad choice concerning which index to use. It will pick a secondary index that can be used to cover a WHERE indexed_column=<constant> condition, even though it will cause way more data to be scanned than necessary. The primary key for …

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 …

Closing keynote of the 2007 MySQL Conference is Pasha Sadri from Yahoo! talking about pipes.

Pasha starts with a story about looking for an apartment near a park (this happened to him a few years ago). It was very tedious to go looking on Craigslist every hour clicking on every apartment and looking to see if the apartment was near a park.

Made a 50-line Perl script to merge database between Craigslist and Yahoo! local to get data sets. This turned into Yahoo! pipes which provides a visual editor to take data sources and create a set of controls over that data source and output the data in a certain way.

If you haven't seen pipes it is hard to describe, but very cool.

For design, pipes relied heavily on the idea of Unix pipes. The goal to …