Archive for August, 2008

Fighting Spam takes up more and more CPU on our poor little web-facing OSX server, and I need to offload this task to another server. This can be done in several ways – but installing a SMTP proxy server on a separate computer may be the way to go to disrupt the existing server as little as possible – so I found this:

The Anti-Spam SMTP Proxy (ASSP) Server project aims to create an open source platform-independent SMTP Proxy server which implements whitelists, Bayesian, and basic anti-virus filtering to rid the planet of the blight of unsolicited email (UCE).

[From Anti-Spam SMTP Proxy Server]

And here are some hints from MacOsX hints on use of the same package.

We went out kayaking on the Wey again today – even though it was raining.

And I was impressed by the number of people we met on and alongside the river – also disregarding the rain.

There were kayakers, people in rowing boards, canoes, small boats with electric motors and in narrowboats.

We were out for almost 3 hours – a extremely peaceful setting, and it gave me some much needed exercise as well.

My new £8 wetshoes worked very well – better than my old sandals anyway.

Nuelectronics has just published 2 more examples on how to use their EtherShield for the Arduino as a Web Client – extremely interesting, and much more appropriate than the web-server examples:

Since I published ethernet Shield web-server project note a few months ago [link], I’ve got a few emails about how to use the Ethernet shield as a web client. In a sense, a web client application is more appropriate for a small device such as the Ethernet shield on Arduino.

Based on the TCP/IP protocol, it is the web client (such as a web browser) who initializes the TCP connections, requests data from or sends data to the web server. Therefore the ethernet shield running in web client mode can be used as a distributed sensor node in the network. It can send periodic or interrupt driven sensor data to a web server. The web server can then record, process and show the sensor data from one or multiple web client.

In this project, a web client application is devloped for the ethernet shield, which can send periodic data (such as temperature reading) or spontaneous data (such as infrared sensor data or switch press) to a webserver. PHP scripts that used to save and show the data received on the websever are also developed.

[From Projects & news from :: Ethernet Shield for Arduino – a Web client example :, Arduino Freeduino projects]

Another interesting post from the MySQL mailing lists – monitoring replication has always been less than easy:

If you are using MySQL replication, you know how hard is to monitor it properly.
You have a wide choice of commercial and free tools, all of which check the health of your replication system from the outside.
A few years ago, I wrote an article advocating a self-monitoring and self-healing replication system, using new features in MySQL 5.1. At the time, there were some missing technology pieces to make this project feasible. Now the pieces exist, and you can create your own self monitoring replication system.

Hartmut rules!

It started during FrOSCon, when I discussed with Hartmut Holzgraefe the practical application of a plugin that he wrote some time ago. One of the missing pieces for the internal replication monitoring is the ability of reading replication values into variables. Currently, you can see the replication status with SHOW MASTER STATUS and SHOW SLAVE STATUS. Unfortunately, you can’t manipulate the values from these commands inside a stored routines. And thus there is no way of measuring the health of a replication system without the help of external programming languages.
This means that, even if you manage to detect a master failure, you can’t create a CHANGE MASTER STATUS with the right parameters.
During our FrOSCon talks, Hartmut released a small Launchpad project, the MySQL replication status INFORMATION_SCHEMA plugin, which implements two INFORMATION_SCHEMA tables for MASTER and SLAVE STATUS. Using these extensions, you can get single values inside a stored routine.

The Replication Monitor is born

From this starting point, the Replication Monitoring Tools become possible.
The project is young, and it only contains a proof-of-concept monitoring tool (see below), but given enough time and help, it can be expanded into a full fledged system.
The roadmap has a long list of possible features, some of which are quite ambitious

The project is open. I need ideas, manpower, testers, to implement all the intended features. This is a project from the community to the community.
The principle that you have seen several times when downloading MySQL server fully applies. If you have time, you can save money and help yourself and the community at the same time. If you want to save time, then forget this project and check what MySQL Enterprise offers.

Replication Monitor in action

The proof of concept implements a table on the master, where the slaves write their status, by means of federated tables.
Each slave uses a federated table to the master INFORMATION_SCHEMA.MASTER_STATUS to compare its status with the master,a nd a second federated table to write its status to the master table. The result is that you can read the global status of a replication system in the master, and in each slave (because the table is replicated).

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000001 | 82321 | IO+,SQL+,P+,E+ |
| 102 | mysql-bin.000001 | 82530 | IO+,SQL+,P+,E+ |
| 103 | mysql-bin.000001 | 82739 | IO+,SQL+,P+,E+ |
| 104 | mysql-bin.000001 | 82948 | IO+,SQL+,P+,E+ |

The status reads as follows:
IO+ means that the slave IO_thread is working (the opposite is io-.
SQL+ means that the SQL thread is working.
P+ means that the slave is reading from the latest binlog and that the position is equal or greater to the one shown by the master.
E+ means that the slave has executed all the statements received from the master.
Let’s try a small experiment. We stop slave 2 and see what happens.

slave2> stop slave;

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000001 | 91517 | IO+,SQL+,P+,E+ |
| 102 | mysql-bin.000001 | 91517 | io-,sql-,p- |
| 103 | mysql-bin.000001 | 91932 | IO+,SQL+,P+,E+ |
| 104 | mysql-bin.000001 | 92141 | IO+,SQL+,P+,E+ |

The monitor shows that slave 2 is not working. If we restart the slave, the situation is restored.

slave2> start slave;

select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000001 | 114894 | IO+,SQL+,P+,E+ |
| 102 | mysql-bin.000001 | 115104 | IO+,SQL+,P+,E+ |
| 103 | mysql-bin.000001 | 115314 | IO+,SQL+,P+,E+ |
| 104 | mysql-bin.000001 | 115524 | IO+,SQL+,P+,E+ |

We can do the same experiment while loading some heavy data, like the employee sample database.

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000001 | 134158 | IO+,SQL+,P+,E+ |
| 102 | mysql-bin.000001 | 134975 | IO+,SQL+,p- |
| 103 | mysql-bin.000001 | 133777 | IO+,SQL+,P+,E+ |
| 104 | mysql-bin.000001 | 1165155 | IO+,SQL+,P+,e- |

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000001 | 7343962 | IO+,SQL+,P+,e- |
| 102 | mysql-bin.000001 | 8374099 | IO+,SQL+,P+,e- |
| 103 | mysql-bin.000001 | 7343751 | IO+,SQL+,P+,e- |
| 104 | mysql-bin.000001 | 8374310 | IO+,SQL+,P+,e- |

Very rapidly (the experiment happens in a MySQL Sandbox, so all servers use the same disk and CPU), the slaves are left behind in execution.
If we stop a slave while loading, the situation is even more explicit.

slave3> stop slave;

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000001 | 45764491 | IO+,SQL+,P+,e- |
| 102 | mysql-bin.000001 | 45764703 | IO+,SQL+,P+,e- |
| 103 | mysql-bin.000001 | 42685103 | io-,sql-,p- |
| 104 | mysql-bin.000001 | 42685103 | IO+,SQL+,P+,e- |

Let’s introduce a new element of difference, and flush logs while still loading data.

master> flush logs;

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000002 | 2044673 | IO+,SQL+,P+,e- |
| 102 | mysql-bin.000002 | 3066965 | IO+,SQL+,P+,e- |
| 103 | mysql-bin.000001 | 42685103 | io-,sql-,p- |
| 104 | mysql-bin.000002 | 3067176 | IO+,SQL+,P+,e- |

Now the stopped slave is really far behind. Let’s see what happens when we put it back online.

slave3> start slave;

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000002 | 27604369 | IO+,SQL+,P+,e- |
| 102 | mysql-bin.000002 | 27603945 | IO+,SQL+,P+,e- |
| 103 | mysql-bin.000002 | 27604157 | IO+,SQL+,P+,E+ |
| 104 | mysql-bin.000002 | 25558385 | IO+,SQL+,P+,e- |

The restored slave was the first one to catch up in execution. This would not probably happen in a situation with separate hosts for each slave, but it’s interesting to test our system.
And finally, after a few seconds more, all slaves have caught up with the master, with both data fetching and execution.

master> select * from slaves_status ;
| server_id | binlog_file | binlog_pos | slave_status |
| 101 | mysql-bin.000002 | 104641288 | IO+,SQL+,P+,E+ |
| 102 | mysql-bin.000002 | 104641501 | IO+,SQL+,P+,E+ |
| 103 | mysql-bin.000002 | 104641714 | IO+,SQL+,P+,E+ |
| 104 | mysql-bin.000002 | 104641927 | IO+,SQL+,P+,E+ |

Getting started

There is a step-by-step set of instructions in MySQL Forge Wiki that explain how to build and install the necessary plugin.
After that, you should get the code from the bazaar repository and make the changes in the option files of master and slaves (check the sql/master_scripts and sql/slave_scripts directories). Then, you should load the initializing script in the master and in each slave, and you are in business.
Notice that this first version relies on a MySQL Sandbox running with MySQL 5.1.28, compiled from source. Further versions will lift this limitation, but for now, if you want to try it out, you need to follow my steps closely.

Next steps

What’s left? Ah, yes. A totally internal monitoring system is not much helpful. If the DBA needs to run a query to know what’s happening, then the system is almost worthless.
This is another challenge, because MySQL architecture forbids connection to operating system services such as mail applications. There is already a solution to this problem. We only need to integrate it with the rest of the replication monitoring system. More challenges are outlined in the roadmap.
If you think this is interesting, check the roadmap, and comment on it. Even better, if you want to implement parts of it, join the sandbox developers group and start coding! [From Introducing the MySQL community-driven Replication Monitoring Tools]

This looks interesting – automated Topic Maps!

Screenshot Wandora is a general purpose data extraction, management, and publishing application based on Topic Maps and Java. Wandora has a graphical user interface, layered presentation of knowledge, several data storage options, rich data extraction, import and export capabilities, and an embedded HTTP server that enables dynamic publication of Topic Maps. Wandora is well suited for rapid ontology construction and knowledge mashups.

License: GNU General Public License v3

New features include a Firefox plugin to extract directly from the Firefox Web browser, an OpenCalais classifier, an OpenCyc extractor, a SOM classifier, a Wikipedia extractor, the ability to export GraphXML, and Subject locator previews for MPEG videos and PDF documents.

[From Wandora 2008-08-28 (Default branch)]

I have now twice had my Time Machine backups stop with numerous error messages for a period of time up to a week, and then suddenly burst into life on its own.

I have no idea why this was the case – but I did nothing to clear the error, it just “auto corrected” itself?

This must be the coolest way to take aerial photos/movies yet :


(Click for larger image)

You can actually add your own camera, and follow the helicopters viewpoint with video-glasses! It’s even got GPS.

I recently upgraded to a 3G iPhone – and the first time I tried to use the WiFi from The Cloud I got the following message :


So the moral is to do this as quickly as possible – do not wait until you really, really need to use it..

I had not spotted the small – but significant – difference between IPSec and IPSec with PPP/L2TP, this explains a lot :

Mac OS X 10.3 or later includes a built-in virtual private network (VPN) feature. It uses IPSec with PPP/L2TP, not pure IPSec. So if you try to connect to a plain vanilla IPSec server, the attempt will fail, and your system.log might contain something like this: localhost racoon: ERROR: isakmp_inf.c:848:isakmp_info_recv_n(): unknown notify message, no phase2 handle found. localhost pppd[521]: L2TP cannot connect to the server The VPN server may report in its log: IKE Responder: IPSec proposal does not match (Phase 2) IKE Responder: Mode 2 – not tunnel mode [From What kind of VPN does Panther use?]

The first serious geocaching application I’ve seen on the Appstore – and it looks good. It does *not* display any navigation aids as such (i.e. a compass rose or similar) but read the FAQ for why this is not included (one reason is that the author has not mastered the graphics on the iPhone well enough – so 10 points for honesty!)

The iGeocacher is a small application that allows me (and you) to parse the data in a set of geocaching files in .loc or .gpx format and then display them via a web application in the safari browser on your iPhone. The lat/lon coordinates are coupled to the Google maps function so that you just touch the icon or link on the desired target and you’ll see it depicted on Google maps. Just touch the directions button and get detailed driving instructions to your waypoint

[From iGeocacher]