Archive for January, 2009

MySQL’s stored procedure language could be so much more Useful

Wednesday, January 28th, 2009

MySQL has a stored procedure language. People have told me how bad it is, how inefficient, how it had to be there to make MySQL appear more professional. Yes, it’s far from perfect, it’s not pre-compiled so not necessarily any faster than running single statements by hand. However, it works and you can do many useful things with it. The complaints however miss the point of stored procedure languages as used inside a database. They are generally used for 3 things:

  1. To hide the implementation from the database user, so that the implementation can change but the interface remains stable.
  2. To simplify the DBAs day to day tasks of maintaining the database.
  3. To speed up complex queries.

While MySQL doesn’t excel at point 3, it can be used in the other two cases. I’ve used Sybase for quite a while and many maintenance tasks could be fully programmed inside the database, just as you would write a script to do a sequence of events. So you might need to do something like (very made up incorrect example):

@count = 1 -- to get into loop
WHILE @count > 0
BEGIN
    SELECT @var = id FROM some_table WHERE column_name IS NULL LIMIT 1
    SELECT @count = @@rowcount
    IF @count > 0 THEN
        UPDATE other_table set other_column = 'not defined' WHERE some_table_id = @var
    END
END

The above psuedo-code could probably be written in a single statement. Make the logic more complex (add a delay between each iteration, update another table conditionally based on some values) and you might have trouble. The point is you can do this in Sybase. You can do this in Oracle, but you can’t in MySQL, not as an anonymous block of code to be executed immediately from the command line. You would have to write this as a stored procedure, run that stored procedure once and finally drop it afterwards.  Why don’t they allow us to do this directly from the command line?

mysql database_name <<EOF
DELIMITER $$
BEGIN
    statement1;
    statement2;
    -- ...
END
$$
DELIMITER ;
EOF

MySQL 5.0 can’t do this and as far as I know neither 5.1 nor 6.0. Yet creating a stored procedure involves using EXACTLY the same syntax.  Parsing wouldn’t be an issue as the parser is there for the stored functions or procedures. It’s just they need to simply allowing multi-statement input directly from the command line. This would avoid the need for shell or perl scripts just to do mundane tasks and keeps the job within MySQL. Ad-hoc scripting like this would be very useful. Creating a stored procedure to execute a statement block once is just pointless and probably the reason why most people don’t use the stored procedure language that much at the moment.

There are other issues with MySQL’s implementation of stored procedures. In a replication environment you can’t replicate both the CREATE and DROP stored procedure or function calls to the slaves which means that you can’t execute a stored procedure on the master and expect it to run on the slave unless you have set up the slave with the same routines. That’s tedious with one or two slaves but unworkable if the number of slaves increases unless you have special custom tools. It does like look MySQL is addressing this problem which is good news for those of us who could make use of replication to distribute stored procedures to the slaves.

In any case the best way to get people to use the stored procedure language more frequently is to make it accessible directly from the mysql command line.  I’d certainly love to be able to use the language this way. Would you?

Looking for an economic multi-disk NAS for home usage

Friday, January 23rd, 2009

I’m looking for an economic multi-disk NAS box for home usage. Ideally one that will take more than 2 disks and supports NFS. Of course there are some nice more expensive models costing around $1000 or more, but I’m looking for something a little less expensive. I’ve recently come across the Edge10 NAS400 which sounds quite nice, and has a nice price too. Unfortunately there are few external reviews I can find about this device. I’ve been given a test report from the manufactures which I’m reading now. I’ll let you know what I think.

Do you have any other suggestions?

postfix-2.3.16 RPMs available

Monday, January 19th, 2009

I’ve just updated my RPMs to include the latest 2.3 version of postfix. Source and binary RPMs can be found at the following location http://ftp.WL0.org/official/2.3/ or on one of my mirrors.

I think with these new 2.3, 2.4 and 2.5 RPMs my website is up to date again.

postfix-2.4.10 RPMs available

Saturday, January 17th, 2009

I’ve just updated my RPMs to include the latest 2.4 version of postfix. Source and binary RPMs can be found at the following location http://ftp.WL0.org/official/2.4/ or on one of my mirrors.

Who has a VoIP PABX at home?

Saturday, January 17th, 2009

The “techies” who have been using Internet for ages do a wide variety of things. It just seems to me that few people use VoIP except for the simple stuff. Is that really so?

I’ve been using Asterisk for some time. I currently live in Spain. My parents live abroad, and for five years I was living in the Netherlands. So even if national calls aren’t expensive saving costs on International calls was something I was interested in. I’ve still got a couple of DID numbers in other countries which make it easier for me to be reached by family and friends. I use both IAX and SIP, the latter being more of a pain to configure for home use behind a NAT router with one ip. My own landline is linked to Asterisk too.

When I talk about VoIP to other friends and colleagues I seem to be the only one using VoIP in this way. I’m not sure why that is. Those who I know that have played with VoIP have a single account, not connected to their telephone line, and they use that mainly for cheaper outgoing calls. Few seem to incorporate their own phone line into the system and just use the PABX (Asterisk in this case) for all calling.

So am I really that odd, or is VoIP too hard at the moment even for most techies? If so that’s a real shame.