Archive for September, 2010

Vigor2820n 3.3.4 firmware upgrade stops you accessing the “external ip” from inside

Thursday, September 30th, 2010

I recently upgraded the firmware on my Draytek Vigor 2820n ADSL router to version 3.3.4 from 3.3.3. One thing that surprised me was that the change stopped me being able to access my public IP from behind the router. That is I have an internal LAN with RFC1918 addresses such as 192.168/16 and could access my public ip address which gets routed back by the router to an internal host. The 3.3.4 firmware upgrade stops that working. That makes it a bit of a pain to test connectivity to sites like my web site from inside compared to from outside and it took me a while to figure out what had happened. In the end I’ve had to implement split DNS so that internal references to the “external sites” I provide resolve to internal ip addresses internally but the public see my external ip address. A lot of work for a home network but otherwise things just don’t work properly.

So if you have a Vigor 2820n then be aware of the change in behaviour from the previous version of the firmware.

Found a nasty COALESCE() related bug in 5.5.6-rc

Thursday, September 30th, 2010

Seeing as it looks like 5.5 is shortly about to go GA I thought I’d give it a run and see how well it works. The only way really to test it is to give it a bit of load and look for things which break. That I did with the 5.5.6-rc community rpms, compared to the 5.1 advanced rpms I usually run.

My colleagues, Ben and Peter, found a horrible problem which means that I can’t use this even for any real usage on my real servers. See: bug#57095 for all the gory details. Thanks to them both for finding the problem and then digging down and figuring out the real cause. Sometimes developers work a long way from the database so their errors don’t translate into something I can really look at in the database. They delved into the problem and then found the cause and a nice easy test case which I could report to MySQL.

After a couple of days of running this was the only thing that turned up. It may not catch many people but it might catch you.

I hope this gets resolved quickly so I can try and find the next bug. 5.5 does look interesting and I’m looking forward to it becoming GA. Once that happens I have to decide the upgrade plan again.  That is a surprisingly long procedure to run from start to finish. I do not want to chase MySQL and it’s newest release but performance changes in 5.5 make it very interesting. I remember when I tested 5.4 last year it was clear that 5.4 was much better than 5.1 performancewise, but it was not GA. Had I jumped on the 5.4 bandwagon to get these extra features or speed I would probably have paid for that. So it does pay to be patient.

Getting temporal configuration values into date-range value equivalents

Tuesday, September 7th, 2010

I collect a lot of configuration values from my database servers and most of these values are stored by date.

So often I end up with values such as:


config_date config_value
2010-09-01  value_1
2010-09-02  value_1
2010-09-03  value_2
2010-09-04  value_3
2010-09-05  value_3
2010-09-06  value_3
2010-09-07  value_3
2010-09-08  value_4
2010-09-09  value_4
2010-09-10  value_1
2010-09-11  value_5
2010-09-12  value_5
2010-09-13  value_5
I´ve been unsuccessfully been trying to figure out how to convert this in SQL into something like the following:


config_from config_to   config_value
2010-09-01  2010-09-02  value_1
2010-09-03  2010-09-03  value_2
2010-09-04  2010-09-07  value_3
2010-09-08  2010-09-09  value_4
2010-09-10  2010-09-10  value_1
2010-09-11  2010-09-13  value_5

The second format is often much shorter if configuration changes are not that frequent. While this is straight forward to do in a programming language in a single pass once you have the config_date ordered data, I can’t figure out how to do this with SQL and no programming. Celko´s books haven’t enlightened me either.

So do you know how to do this?

Careful how you monitor MySQL

Tuesday, September 7th, 2010

I was recently struck by a problem which is unusual. In order to keep an eye on the database server I use nagios, cacti, merlin and some local scripts to monitor the database instance and ensure that it is working properly.  That normally works fine.  The different monitor processes do various things, one of which is to monitor the replication status of a slave, and warn me if the replication is not working or if it’s behind. This is done with the command SHOW SLAVE STATUS.

The server I was looking at runs some large local batch jobs aggregating data. Unfortunately, I was experiencing that replication was interfering with these batch jobs so decided to see if things would perform better if I stopped replication and let the batch jobs complete, restarting replication afterwards.

So the command STOP SLAVE was sent to the server, and this took some time. In the meantime SHOW SLAVE STATUS hangs. The STOP SLAVE command waits for the SQL replication thread to finish it’s task but that thread was waiting on the tables being used by the batch process, which was already running. The consequence of this was that STOP SLAVE waited, all calls to SHOW SLAVE STATUS blocked and with nagios, cacti, merlin and the local scripts all doing periodic SHOW SLAVE STATUS commands which would hang I ended up with the mysqld running out of user connections.  Nasty!

While I see if MySQL can do something about the behaviour of stopping the slave and showing the slave status I’m going to implement grants which limit the monitor users so they are only allowed to have a user concurrent connections open.  This is done using the syntax

GRANT whatever ON whereever TO some_user WITH MAX_USER_CONNECTIONS 5;

5 looks like a good number for nagios, merlin and the local scripts and 10 is probably sufficient as I collect a lot of graph data for the mysql server and the cacti requests are often done in parallel.

So if you monitor MySQL it may be worth you applying some sort of connection limit on your monitoring scripts, if not on other database users.

XS4ALL offer IPv6 connectivity to retail customers

Sunday, September 5th, 2010

Good news. I was told by a colleague that the Dutch ISP XS4ALL is offering IPv6 connectivity to its retail customers. You can see here although the comments are in Dutch.  They also provide a list of ADSL routers which should work for their service. The Cisco name may not be surprising but this is good publicity for Draytek and AVM FRITZ!box for their products. Hopefully it will also stimulate other SOHO router providers into the act to get their names on the list.  Let us hope that more ISPs start to offer this sort of service to their customers.

I’m not sure if there’s a list of residential ISPs in each country which provide IPv6 connectivity. If not it might be worth making one and updating it as new providers off this service.

I checked my ISP, Jazztel, and was not really surprised to see that neither technical support or the sales staff really new what I was talking about when I asked if they were planning on offering IPv6 support. That’s unfortunate, but I don’t think any other Spanish ISP is any better.  That is any other residential ISP in Spain. I expect some of the larger bigger ISPs are likely to off this to business customers.