So, you want some maintenance to run on MySQL, but you hate cron. Have no fear, events are here!

Here’s an example that came up in a work IRC channel for inserting a row on the last Tuesday of every month:

DELIMITER // 

CREATE EVENT x ON SCHEDULE EVERY 1 DAY DO  BEGIN   
  IF DAYOFWEEK(CURRENT_DATE)=3
     AND MONTH(CURRENT_DATE)<>MONTH(CURRENT_DATE + INTERVAL ‘7′ DAY)   
  THEN     
    INSERT INTO t VALUES (120);   
  END IF;
END//

You could also set it for EVERY 1 WEEK and omit the step of checking if it is Tuesday.

There is an article on events at http://dev.mysql.com/tech-resources/articles/event-feature.html

So what’s the catch? You need MySQL …

A recent post in the MySQL Stored Procedure forum reminded me of the old days, the post was with regard to a problematic stored procedure which had a rather long name. The name had nothing to do with the problem but it got me thinking about naming conventions and documenting code to make it more readable.

I'm all for naming things with an appropriate name but there are times when it can be taken too far. One particular example was a system I worked on a few years ago, every table had two columns called unique_identifier and parent_identifier. Every record had a unique identifying number and the ability to store the unique identifier of it's parent, I'll ignore the fact this wasn't the greatest way to design the database for now.

The problem was that these columns were used on a regular basis, pretty much in every SQL statement that joined two tables in the system. This meant that the consulting team spent most of their …

Colin Charles (MySQL Community Engineer) and David Axmark (MySQL AB co-founder) will be visiting Linux Asia in New Delhi next week (8-10 February 2006). If you’re there, be sure to talk to them about MySQL 5.0 & 5.1, MySQL Cluster, our Users Conference in California in April, and about whichever way we can help you make more out of MySQL!

Red Hat Magazine this month (well, okay, last month, I’m clearing out my tabs), has a feature on Linux and Asia. Of interest were:

• What does open source mean in India? - an interview with Javed Tapia (Director, Red Hat India), showing why India finds OSS important (software costs too much), how localisation works, and a bit about Red Hat India.
• Asia, the questions we ask - a great read, written by Michael Tiemann about his experiences in Asia. A question of interest: “What will be Singapore’s role in the technology industry of the 21st century?” I think thats the question most countries want the answer to, be it Malaysia or Australia. Its …

Today, I received a message from a friend who is also a MySQL DBA and recently upgraded to MySQL 4 from MySQL 3.23:

I thought this might be useful for you:

I had two fields, say "a" and "b", both of which were unsigned int. For one calculation, I need to subtract b from a. Before upgrading, if b>a, the result would be negative as expected. After upgrading, if b>a, the result is something like 18446744073709551615. Of course, this sort of thing happens in c, but MySql apparently used to automatically cast so that the result of such calculations would come out as expected. Of course, changing the fields to signed int fixed the problem. Interesting, eh? Seems like this kind of thing could cause huge problems for some people when upgrading.

My question to MySQL gurus here: Is this a bug in MySQL 4? Why is MySQL 4 not "casting" it properly?

Thanks
Frank

Hey Jeremy,
Today was the third time I tried to comment on your blog (about Amr's post) but was redirected to a "DSBL: Listing Data" page.

Since I don't have a dedicated IP at home, it seems you have my ISP (Alltel.net) blocked.

Just wanted to say please open up your comments to legitimate commenters like me and please don't make us pay for someone who has used this IP in the past.

IP: 151.213.158.103
State: Listed
Listed in unconfirmed (unconfirmed.dsbl.org): yes
Listed in singlehop (list.dsbl.org): yes
Listed in multihop (multihop.dsbl.org): no
Reverse DNS identifies server as: h103.158.213.151.ip.alltel.net

Can you manually whitelist me or give me a way to comment on your blog? Thanks

Frank
softwareengineer99 at yahoo dot com

I have been reading a lot about Innodb and MyISAM recently. MyISAM offers speed whereas Innodb offers reliability. MyISAM is fast but with no transactions support, Innodb has certain, but very important limitations.

I hate to ask it like this, but what do you prefer for a high volume application with heavy inserts/updates/selects? In your regular job (and projects), do you go for the speed or the reliability? or do you try to find a balance between both by having tables of both kinds?

I am just interested in hearing your thoughts (and igniting a discussion) as to which one is better in the long run?

I have personally used MyISAM and outsourced everything else to my application so far.

Thanks
Frank

Yesterday morning Brian flew on from Brisbane to Adelaide, presenting at the inaugural meeting of the local MySQL Users Group there. Brian has taken lots of pictures and written interesting short stories about his trip, which haven't shown up on Planet MySQL because of the way the filter works (no MySQL-specific content). But it's still interesting stuff if you want to know what went on at linux.conf.au, learn about the large spiders and other wildlife in my back yard that keep intriguing intercontinental visitors, Australian airport security, and that on the main square in Brisbane you're more likely to meet an ibis than a pigeon. See http://krow.livejournal.com/ for details!