AJAX

Depending on your education or where you live AJAX can mean one of at least four things, if you studied classics you will known Ajax from Homer's Iliad, if your in to Football then you might know Ajax of Amsterdam and if you live in the UK and are over 30 you will know Ajax toilet cleaner. For me fitting in all three categories it summons up visions of a football playing Greek hell bent on cleaning the toilets after the match.

Anyway, I recently started a new job as a web/DB developer, the first few weeks have been heavy on the database side, working with some large data sets trying to load and then extract information for a bulk email system (boo hiss you all say). Anyway, I've now been asked to investigate AJAX, which as you may know stands for Asynchronous JavaScript and XML. AJAX isn't a language but a web development technique for building sites which don't need constant refreshing of the sites pages, instead small sections of the pages are updated using specific calls to the server to return XML which is then processed by Java to update the page.

This means web sites can behave much more like client server apps, so that list can populated when another value on the page changes, validation can take place almost instantly and so on.

I'm building a prototype of an exisitng web site we run, for this I'm using PHP and MySQL, this seems a great choice but the thing with AJAX is that the language is unimportant. One thing I have found however is that while there is plenty of buzz around on the web about AJAX, examples seem to be hard to come by, it may be that there are plenty of examples around but that it's swamped by all the blog and magazine articles giving us just a taste. I've managed however to knock something up, it needs a bit of work but it's looking good so far.

Anyway I'll keep you posted and if it works out I'll provide a URL of the prototype.

Read the ******* Manual

First let me say I'm not a prude, but neither do I have a mouth like a toilet. But I do find the acronym RTFM incredibly offensive, it may be that some people using it don't actually know what it means and it can be said the F stands for something other than the obvious but either way I don't like it. I've nothing wrong with acronyms but what I don't like about it is the idea that you're swearing at somebody you don't even know, who in their ignorance for one reason or other didn't read a manual. Would you for example say to somebody who asks you the time "Buy a ******* watch". The other problem is often people don't actually tell you which manual it is your supposed to go and ******* read.

Anyway the point of this little rant was to tell you to go and read a manual. Well not just any manual but the MySQL Reference manual. From time to time I'll have a browse to see what's in there and from time to time I pickup some great tips. For example, I've mentioned many times (in today’s earlier blog for example) that creating script files for stored procedures and running them using source filename is a great idea, what I didn't know is that you can also use \. filename to do the same thing.

It's not the most amazing thing you can do with MySQL, in fact it wouldn’t allow me to do anything I couldn't do before but it expands my knowledge of MySQL which can only be of benefit.

A Warning about warnings.

Somebody recently asked a question about warnings in the MySQL Stored Procedure forum, the question its self was pretty easy but it raised another issue.

The question was "How can I see warnings when MySQL reports them". The easy answer is to simply type "SHOW WARNINGS;" on the MySQL command line, however the show warnings command only shows the last error message, thats fine if you actually type "SHOW WARNINGS;" immediately after the statement that had the problem but if you make a mistake or wait for something else to go wrong then the warning message is removed and replaced by something else.

The answer of course is not to make a mistake, but for people like me who come from a different background, in my case Oracle, the commands are different. I'm forever typing "SHO WARNINGS;" or "SHOW ERROR;" both of which result in another error and hence I lose the original message.

But here's a tip I used when working with stored procedures with Oracle. Because of their nature I'd write stored procedures in a file and then load them using the @ command, this can be recreated in MySQL using the source command. I'd also, in the early days, make a lot of mistakes, well not alot but enough that I had to write the SHOW ERRORS command on a regular basis. Rather than type it each time I added the SHOW ERRORS command to all of my procedure creation scripts, if there was an error it was displayed and if there wasn't I'd get a rather good message simply saying No Errors.

So my advice is if you're creating procedures or complex SQL just add a SHOW WARNINGS; command to the end of your script. Then you can just run the command from the script rather than the command line, if you do get any warnings they will display once the SQL has been run.... just don't make any mistakes writing show warnings in you file :).

You get out what you put in.

I spend a fair amount of time on the MySQL forums and also over on the Quest Pipelines answering questions. I've mentioned on a few occasions about how I think this benefits me as much as the person asking questions, but one thing I've noticed a lot recently is the amount of, for the want of a better word, bad questions.

In reality there are no bad or wrong questions, we all have to start somewhere, but what appears to be a problem is people only putting in the minimum of effort when asking, which often results in an equal amount of effort when people answer. Despite the rewarding nature of answering, a person is far more likely to help if they can answer the question easily or at least is given enough information to be able to answer fully. One of the problems with forums is that it doesn't lend its self to a conversational style, especially given the international nature of the web, I'm often dealing with questions from people around the world* where I'm answering one day and don't get a response until the next.

So here are list of tips I'd give people wanting to ask questions on the various forums, so that you don't waste your time and people answering don't waste theirs.

1. Be as descriptive as possible, context is all important. Often a solution can vary greatly on the context in which it will be used. I often see people simply asking how do I do X, this often gets the standard read the manual response. If you tailor the question people are much more likely to tailor the answer.

2. When giving data structures and examples make sure it's all included. People often trim down their SQL examples to try and make it easier for people to answer, this often leads to relevant information being missed and an incorrect or less complete answer being given. I've often see perfectly good answers wasted by people responding that the solution doesn't work because they are using a join or something equally fundamental to how the SQL should be written.

3. What’s slow? Many times people ask questions about slow running queries, but what's slow exactly. As fantastic as MySQL and Oracle are sometimes it's simply not possible to reduce long running SQL statements down to seconds. Somebody who works in a data warehousing environments definition of slow might be very different to someone working on a transactional system for example.

4. Post it in the relevant place. Most forums have a category structure and often people will tend to answer in their area of expertise. The best possible chance of getting your question answered is to target these people. If your unsure there is normally a general area which is fine.

This is just a short list and I'd welcome comments from others about what they would like to see. But above all give as much detail as possible, if its important enough to ask then its important enough to spend just a little extra time on.

* The nature of the web means that often people are asking questions in a second tounge. I do realise that this can be difficult but people are pretty forgiving of grammer and spelling if the question is well structured. Its also worth mentioning that the language you're writting in is not your first as often people who seem to speak English for example actually speak another language.

Considering the Database

I've just started reading Pro MySQL by Jay Pipes and Michael Kruckenberg, Apress were kind enough to send me a free copy but what with the first copy going missing in the post and being busy it's only now I've had the chance to have more than a flick through. I have of course started with chapter one, Analyzing Business Requirements, you may be thinking why a book about MySQL starts with a chapter on a subject which is more related to application design, I don't think many people would have even noticed if it had been missing and the book kicked off with chapter two Index Concept. But this raises a really important point, and one which is often missed in the development process. The database is an essential part of most IT projects, it should be kept in mind from the very start.

In my experience of projects I've worked on the database has often been an after thought. Take for example a recent project I worked on, it was a large e-commerce site built on Linux, PHP and Oracle. There was a very detailed specification with endless screen shots and mock-ups of the site design but the database design was nothing more than a simple schema diagram, apart from the odd line joining tables there was next to no information about how the database was going to function, what referential integrity was to be included, no information about indexes and having read the specification it was clear there were large parts of the database which hadn't been included. What was significant was the amount of effort which had gone into the look and feel of the site and in contrast the lack of time that had been spent on the database.

As usual I came into the project at the coding stage, far too late to be able to spend time looking at the requirements and working out the impact on the database. The database evolved organically, the database schema was used to create the base tables but these changed on a daily if not hourly basis. I did my best to add the necessary referential integrity, add indexes where appropriate but as we were into the coding phase it was all a little bit of a fire fighting exercise. Despite the fact we had a detailed specification the nitty gritty of how some of the functionality was to be achieved was missing, the search functionality for example evolved over time and it became increasingly difficult to optimize the database to deal with this.

Things came to a head when we started to stress test the site, anything over 20 concurrent users and the site crashed. We identified some bottlenecks which were addressed and this fixed the immediate problem. But when the site went live we had other problems related to periods of high usage. This meant periods of panic where we had to analyse the site and find the problems, often this involved big changes to the database which in some cases had a serious impact on the site.

The site launch was a success and the performance problems we had didn't seem to cause a negative impact in terms of visitor numbers, but it would have been far better to have taken just a quarter of the time we took fixing problems retrospectively in designing the database in the first place and the subsequent problems we had could have been avoided.*

So I'm more than happy that Jay and Michael decided to kick of Pro MySQL with "Analyzing Business Requirements". The moniker Pro seems more than justified.

*I should say I had a great time working on the project, the client was extremely happy with result and the project was well run. I feel a little guilty about using it as an example but it was a classic case of where it could have been even more successful and stress free if time had been take to consider the database right from the very start.

Delimiter Woes

Hands up who's had an error message when they have issued an SQL statement with ; delimiter straight after they created a procedure, or had an error when they tried to create a procedure without first setting the delimiter. 1..2..3.."is that a hand or are you trying to swat a fly".. I imagine it's a fair few of you, in fact I know it's a fair few because over the past year or so it's one of the biggest things people have asked me about or have had when trying to create procedures. Not why have they made a mistake but why MySQL needs you to change the delimiter when creating stored routines (SQL/PSM).

When you think about it it's rather obvious, because lines are terminated with a semi-colon in SQL/PSM the MySQL command line client gets confused about what your trying to do. The delimiter needs to be changed so MySQL can tell the difference between straight SQL and SQL/PSM.

So what's the problem? Well there are two things, firstly the MySQL Stored Procedure documentation mentions that you need to change the delimiter but only ever mentions //. This is a problem because the MySQL Query Browser (or at least the version I and others have been using) and some other interfaces don't like // as a delimiter, secondly a lot of people I have been in contact with are coming from other databases, in many cases Oracle and on Oracle you don't need to set the delimiter to something different. There is also the issue of people writing code examples that use other delimiters (which isn't a problem in it's self) and this again has caused confusion.

With this in mind I have raised a feature request (#15880) asking if MySQL could deal with SQL/PSM without having to change the delimiter. I know it seems like a minor issue but it's the little things that often make all the difference.

MySQL Stored Procedure Programming (The Book)

On a recent visit to O'Reilly I noticed a list of up coming books. There were the usual second edition of this and third edition of that, but among those was a book that looks like it will become a classic. MySQL Stored Procedure Programming is being written by Steven Feuerstein and Guy Harrison, I'll admit to not knowing much about Guy but I'm fully aware of Steven. For those that don't know Steven he's possible the worlds number one expert on Oracle's PL/SQL, having written a number of books which are seen as the standard by which we should all aim for.

I have all of Steven's Oracle books and if this MySQL book is even half as good as those it's going to be a great book. But it's not due for release until March 2006 and as we all know these things tend to have a habit of slipping a bit. But at $39.99 that at least gives us the time to save up the pocket money.

Quest Pipelines

I've been an Oracle developer for around 8 years. For the first few years I was on my own, a PL/SQL book and Metalink was all I had to find my way in the Oracle darkness. As my experience grew the books became less of an aid and Metalink is often like looking for a needle in a haystack. So I had a look around at the various forums around, they were all much of a muchness, but one just seemed to be much more of a community than the rest. That was Quests Pipelines, what was great about it was that 1. people answered the questions quickly and 2. there were a lot of people answering. I've always found that you get just as much by answering questions than you do asking them, most of our time as developers is spent on mundane repetitive tasks or at least on stuff that doesn't push the boundaries of SQL too much. Maybe once or twice we need to do something a little more complex, something we haven't done before. But on a forum there are literally hundreds of people having these once or twice a year moments on a daily basis, by being involved in a forum you can tackle these sort of problems on a regular basis, his improves your skills by exposing you to things you might never have looked at.

The reason I mention this is that Lenz Grimmer mentioned the pipelines in his blog earlier today. In addition to the Oracle pipelines (one each for DBA's and Developers) there is also a MySQL Pipeline, the pipeline contains a forum, a regular monthly tip and articles. The MySQL pipeline has been open for about a year and I've been a sysop almost from the start, however visitor numbers have been low, I think that's in part to the great forums run by MySQL themselves. But I think there is room for more than one MySQL forum on the web so come on over and say high.

MySQL Connector/J

It seems I only ever post these days to shamelessly plug MySQLDevelopment.com :) However recently my conscience has been clear because I've actually been plugging content written by people rather than myself.

Roland Bouman has done a great job on his blog recently (even winning the grand prize in the MySQL 5 competition), but he has still found time to kindly write content for MySQLDevelopment.com, this weekend we put the final touches to his introduction to the Call Level Interface and Connector/J articles. They are available now at the following URL


http://mysql.gilfster.com/page.php?parent_id=3a&page_id=3a.0.1

Another Week Off?

I've recently noticed my name dropping further and further down the most active list over at Planet Mysql. That's hardly a surprise given that I haven't added a blog in well over 2 weeks, but rather than just simply being too lazy this time I've had other excuses.

First off I changed jobs, I was working on a short term contract which came to an end on 19th November, I had a week "off" (more on that in a second) and then started my new full time position this week.

In my week "off" I spent most of the time working on www.mysqldevelopment.com, I've had a good response to my call for more help (we still want loads more) and people have been kind enough to donate ideas and content of their own for the site. That meant I was busy formating, updating and loading content for at least part of the week. People are also working on some great content based around the CLI and it's looking really good, it's something I've wanted to add to the site for some time but work pressures have played their part in slowing me down.

Also congratulations to Roland, Markus and Beat on winning the MySQL 5.0 grand prize, as you all know they have produced some brilliant content on their blogs, along with plenty of work testing and bug reporting on the features of 5.0. But things should'nt stop there, the big push to get MySQL 5.0 released as been great but there is still a need for more info, more blogs and more bug reports to really push 5.0 forward.