gilfster

Generating "Random" Data using rand() and sub selects

2009-04-14T14:45:00.004Z

I'm developing a demonstration site for new system we are about to unleash on the world, so far it's going great but one of the worst things about demo sites is the lack of real world data at hand. I don't want to give too much away but the site is vehicle sales based and I need to generate a bunch of data. The problem I needed to solve was that the table with the cars in didn't have associated dealer data and the pages looked very bare when there wasn't any as much of the data comes from the dealers table.

I didn't fancy going in to the table and amending 165 cars by hand, but I also didn't want to run the update in blocks as I wanted an a pretty even spread of the different cars between the 12 dealers. Take for example this data set...


mysql> select id, Make, dealer from cars_cars limit 5;

+----+------------+-----------+
| id | Make       | dealer    |
+----+------------+-----------+
|  1 | Land Rover | Aldershot |
|  2 | Land Rover |           |
|  3 | Land Rover |           |
|  4 | Land Rover |           |
|  5 | Land Rover |           |
+----+------------+-----------+
5 rows in set (0.00 sec)

Only one of the vehicles has a dealer attached. What I want is to populate that dealer column with a real dealer.

I always run updates in select mode first, to see what the final out put is like. First I want a way to get a random dealer, we can use rand() in the order by to generate a random number which MySQL will sort on.


mysql> select name from cars_dealers 
       order by rand() limit 1;
+------------+
| name       |
+------------+
| Birmingham |
+------------+
1 row in set (0.00 sec)

mysql> select name from cars_dealers 
       order by rand() limit 1;
+--------+
| name   |
+--------+
| Oldham |
+--------+
1 row in set (0.00 sec)

Now I can include that in my original select like so...


mysql> select id, Make, 
             (select name from cars_dealers 
                     order by rand() limit 1) as dealer 
              from cars_cars limit 5;
+----+------------+-------------+
| id | Make       | dealer      |
+----+------------+-------------+
|  1 | Land Rover | Oxford      |
|  2 | Land Rover | Aldershot   |
|  3 | Land Rover | Coventry    |
|  4 | Land Rover | Southampton |
|  5 | Land Rover | Birmingham  |
+----+------------+-------------+
5 rows in set (0.00 sec)

Great, so I now have a random dealer against each vehicle, all I need now is to turn that into an update and we are good to go.


mysql> update cars_cars 
       set dealer = 
           (select name from cars_dealers 
                   order by rand() limit 1)
       where id > 0;

Query OK, 163 rows affected (0.01 sec)
Rows matched: 163  Changed: 163  Warnings: 0

I now have "random" data in my table. I put random in quotes because it is random within a set range of values not totally random as in totally unpredictable. Just to prove it really did work.


mysql> select id, Make, dealer from cars_cars limit 5;
+----+------------+---------------+
| id | Make       | dealer        |
+----+------------+---------------+
|  1 | Land Rover | Aldershot     |
|  2 | Land Rover | Basingstoke   |
|  3 | Land Rover | Wolverhampton |
|  4 | Land Rover | Aldershot     |
|  5 | Land Rover | Wolverhampton |
+----+------------+---------------+
5 rows in set (0.00 sec)

* I used where id > 0 because as mentioned in my previous post I have safe update mode turned on.

I am a Dummy

2009-04-09T14:29:00.003Z

Way back when I started this blog just under 4 years ago I wrote a post called 'An Oracle Developers Journey', in the early days the blog focused on the transition from Oracle to MySQL. The irony was that after a year or so professionally I move back the other way and for the last 3 years or so it's been all Oracle for me.

We all make mistakes, I know I have made some massive ones in the past, some were saved by a decent back up others were not so easy to recover from. It's been a while since I made anything like a big mistake (certainly not one that was noticed ;), once bitten twice shy tends to make you think about the consequences a little more.

But having now switched back to MySQL again I find myself really worried I'm going to make a mistake, in Oracle it's UPDATE/INSERT or more worryingly DELETE and then a COMMIT, but in MySQL (at least the way we are running it) there is no commit stage. I'm currently working on development systems but I just know that one day I'm going to delete or update something I really don't want to.

Thankfully MySQL is clever enough to make up for my itchy delete finger and includes the --safe-updates startup option. But I love the alias which is --i-am-a-dummy. I'm man enough to admit to being a dummy, well at least prone to doing something dum from time to time and that's why the first thing I did when I switched back to MySQL was to include --i-am-a-dummy in my startup alias.

So hands up who else is a dummy?

p.s. if you don't know --safe-updates sets the following session system variables.

SET sql_safe_updates=1,
sql_select_limit=1000,
sql_max_join_size=1000000;

sql_safe_updates is the key as it stops you running an update or delete without some sort of where clause.

Adventures in Web Performance

2009-03-26T10:17:00.003Z

I mentioned in my previous post that recently I have been working with web performance. Often when working with clients they want fast performing web sites but then proceed to give you all manner of other requirements which slow the site down to a crawl. Convincing a client to give you the time to look at performance isn't easy, while the gains in speed are there for all to see attributing some monetary value to that is close to impossible. In the longer term you might see an up turn in the traffic levels or a higher percentage of people taking a longer user journey on your site but whether that was a result of higher performance is very difficult to prove.

Thankfully one of our larger clients could see the benefit of having a dedicated resource looking at performance issues, all be it as a result of such poor performance that the sites became difficult to use during peek times. When a client keeps throwing stuff at you it's easy to take your eye off the performance issue and one thing that became clear was that while the tendency was to blame the database many of the things we looked at were nothing to do with that area.

Over the next few posts I'll discuss some of the things we looked at, how we did that and how we radically improved performance on some of our sites with some minimal changes.

Such a long time....

2009-03-26T10:10:00.003Z

Wow, is it really that long since I updated this thing. I can't believe that it's been close to 3 years since I updated it regularly, but I suppose it is as I have been in my current job for that long and it was a transition from MySQL back to Oracle (and a lot more work) that meant I stopped posting in the first place.

The good news is I'm moving back to working with MySQL after 3 years away and that should give me some more opportunities to update the blog. While there have been some highlights over the 3 years much of the work I have been doing has been maintenance and the normal daily grind of work so it didn't offer that much that was interesting. Of late I have been doing some really cool stuff with web performance but I've been out of the blogging for so long I never really thought about sharing it.

Of course any regular readers will be long gone but the blog still ranks pretty high in Google so maybe some folks will stop by.

MySQL on a PS3

2007-06-22T09:30:00.000Z

I work in IT for 2 reasons, firstly it pays well but secondly and most importantly it's fun. When I look back on jobs I had before I started working in IT, while it was never a problem getting up in the morning and the social aspects were great I would never have said that the job it self was fun.

It's been sometime since I last updated the blog, 6 moths or so and almost a year since I did on anything like a regular basis, the main reason as I have mentioned in the past is that if you have nothing to say then why bother saying it. Therefore I doubt anybody is listening any longer, but I suppose that's not really an issue.

I have been prompted to write because I have been doing some work on a site in MySQL, in the day job I use Oracle (but do some admin on MySQL sites from time to time) so I no longer have the experiences to blog about. But I recently needed to upgrade a family members web site which required some MySQL work. As it happens this coincided with a recent hardware upgrade, I used to have an X-Box 360 but it recently decided to die on me so I took the plunge to get a Playstation 3. As games consoles go it's pretty decent and with the addition of a Blue-Ray drive it means I can take full advantage of my new HD TV.

Getting back to the point, Sony allow you to format the PS3 hard drive in such a way that it can have two operating systems, the standard console one and also Linux. The install process looked easy and given that Sony fully supports this I thought I would give it a go. The PS3 allows the connection of a USB Keyboard and mouse so it's easy enough to get going. The install was painless enough (if a little long winded) but once it was done I now had a PS3 running Linux, of course one of the first things I did was have a go at running MySQL which of course runs like a dream.

So now my development machine is a Sony PS3 :)

Mysqldevelopment.com emails are no more.

2006-12-20T12:17:00.000Z

When I transferred the content of mysqldevelopment.com over to the MySQL Forge site I always intended to continue updating this blog. Unfortunately due to work commitments I simply haven't had the time and because I haven't been working with MySQL so much I also haven't had the quality of content I once had.

Rather unsurprisingly then I haven't been receiving much in the way of email to my email address andrew_gilfrin@mysqldevelopment.com. I have however been getting plenty of spam so I have decided to drop that email address, therefore if you wish to contact me you can do so at my hotmail address (andrew_gilfrin at homtail.com).

Spotlight on MySQL

2006-07-18T14:45:00.000Z

One of my "part time" roles is a sysop on the Quest MySQL Pipelines, as I have mentioned on here before we don't get many posts unfortunately and I can only assume that's because MySQL have such great forums of their own.

Anyway, Quest produce a product called Spotlight on MySQL which is a rather cool looking monitoring tool for MySQL. Personally I haven't had the chance to use it but the development team are currently looking for feedback on Spotlight on MySQL, with particular interest in if there is need for replication support.

So if you have anything to add pop over to the forum and let the guys know in the Spotlight on MySQL section.

http://pipetalk.quest-pipelines.com/default.asp?boardid=mysql

Regular Expressions in Oracle 10g - Part 1

2006-07-12T16:11:00.000Z

Before my current role I generally worked in a windows environment, I would occasionally copy a file or log on to a Unix box to does something only when absolutely necessary. But at my last job and this current one I have had to spend most of my time connected to a Unix server, for as long as I could I'd edit in Windows and copy across to do what I needed but it soon became apparent that I'd need to get with the times ;) and take the plunge into Unix.

I have found Unix to be really powerful and it didn't take anywhere near as long to get used to vi as I feared. One of the things I have most useful is regular expression, now I know that they are available in Windows in various places but in all my time with computers I had only a minimal amount of exposure to them.

This then leads on to today's blog, In Oracle 10g a number of new functions were introduced that allow you to search and update using regular expressions. Over the next few days I'll be looking at what can be done but lets start with some simple examples. Let's take the following data set.


SQL> select * from reg_test;

VAL_1
------------------------------
My name is Andrew
My name is Dave
My name is dave
His name is dave
His name is dave.
He dave me andrew

Before 10g we had to use like to search on sections of a column, this wasn't necessarily a problem but often involved using other functions to format the data in a way that we could search on. For example lets say we wanted to get a count of all of the records with Dave at the end, including those which were upper or lower case. Using like we would do something like this.


SQL> select count(*) from reg_test 
     where upper(val_1) like ('%DAVE');

  COUNT(*)
----------
         3

That's fine but it requires us to covert the whole of the val_1 to upper case so we can pick out the required fields. Also we have a count of 3 when realistically speaking we have 4, one of the rows has a full stop. We could to this..


SQL> select count(*) from reg_test 
     where upper(val_1) like ('%DAVE%');

  COUNT(*)
----------
         5

Now we are getting 5, because we have added the second %. To get round this in 10g we can now use REGEXP_LIKE, this allows us to use regular expressions as the comparison.

I'll assume some knowledge of regular expressions (given you possibly know more about them than I do).


SQL> select count(*) from reg_test 
     where regexp_like(val_1,'[Dd]ave\.*$');

  COUNT(*)
----------
         4

This time we get the right result.

Adventures in PHP and Oracle

2006-07-10T15:56:00.000Z

Recently we have been asked to develop some of our future sites in PHP. Traditionally we have worked in an in house language which fits in nicely with HTML but there is pressure from our clients to use something a little more "available" so that they can customise their sites themselves.

The in house language connects to Oracle and we have a large library of Oracle Packages designed to get our information from the database in a structured way. One thing we do a lot is return data in collections rather than via ref cursors. The problem is that the current version of the Oracle Call Interface doesn't support returning arrays (collections) which have been defined within the header of a package.

This means either rewriting some 500 packages/procedures or adding another layer of complexity to convert the returns into a data type PHP can handle.

MySQLDevelopment.com

2006-05-27T20:32:00.000Z

MySQLDevelopment.com is no more, All requests to the site will now be served from http://forge.mysql.com/wiki/Category:MySQLDevelopment.

Thanks for all those who visited the site over the last year.

MySQLDevelopment.com Update

2006-05-12T11:23:00.000Z

I recently I announced that MySQLDevelopment was going to close, at the time I wasn't sure what would be happening to the content but I hoped that it would be able to live on in some form.

I had a number of requests and suggestions with regard to the site and I felt the best option was to hand it over to MySQL for inclusion in the new MySQL Forge Wiki. My timing was a little off as the site was due to close during the MySQL conference but with that now out of the way the content of the site has been transfered and is available now at..

http://forge.mysql.com/wiki/Category:MySQLDevelopment

I will be passing over the domain name to MySQL also but that may take some time to be up and running so MySQLDevelopment.com will still be available in the mean time.

Now it's finally happening it's a little sad for me but this way I know the content can continue to grow and members of the community can contribute in real time.

MySQL Stored Procedure Programming (The Book) - 3

2006-04-28T13:49:00.000Z

I have mentioned a couple of times about the upcoming release of MySQL Stored Procedure Programming by Guy Harrison and Steven Feuerstein. It was scheduled for release in March but the release date slipped a couple of times, I pre-ordered and the good news is that the book arrived this morning, so it's out now (in the UK at least).

It weighs in at 636 pages so while it's not the door stopper that's often related to computer books there is a lot of information in there especially given the relatively recent addition of stored procedures to MySQL. Jay Pipes and Michael Kruckenberg did a great job with their stored procedure chapters in Pro MySQL but given that it wasn't an exclusive development book it didn't go into detail about using stored procedures from other languages, this new book goes into great detail on the subject with dedicated chapters on using stored procedures with PHP, Perl, Python, Java and .Net.

Having only received the book today I haven't had much of a chance to read it but first impressions look good and as I said previously if it's half as good as Steven Feuerstein's Oracle related books it will be well worth the money.

Opportunities

2006-04-27T11:12:00.000Z

It's been well mentioned by myself that of late the time I have been able to devote to MySQL has become restricted. Part of this was due to me taking an exciting new job just before Christmas. When I took the decision to close MySQLDevelopment.com down this was in part due to the fact I was using Oracle rather than MySQL and my exposure to real world situations was limited.

However the day after I made the decision and announced it here my boss asked me to take over the running of a MySQL/PHP based website. The good news at least is that it means it might give me something to actually comment on with regard to the blog, even if it doesn't afford me the time to continue with the site.

But it's not just good news for me it's also good news for you out there too, the company I work for are looking to take on some new developers. We are looking ideally for people with PHP and Oracle experience but are willing to train good PHP people with limited or no Oracle experience, if you have MySQL skills that would also be a great bonus. The company is based in SW London (England) very close to both over ground rail and tube stations, the atmosphere is relaxed, friendly and informal.

I personally can't recommend it enough as a place to work and it would be great to hear from you if you are currently looking for a role in the London area and have the skills we are looking for.

You can use the following link to get some more formal details or you can contact me directly for a more informal chat about the role.

Click here to see more details of the role...

Fun with dates

2006-04-26T09:30:00.000Z

In response to Kai Voigt's post about unxepected results from dates here is what is going wrong.

To add and subtract time using + and - you need to use the INTERVAL keyword either before or after the + or - like so..


mysql> select now() - interval 2 day;
+------------------------+
| now() - interval 2 day |
+------------------------+
| 2006-04-24 07:31:02    |
+------------------------+
1 row in set (0.00 sec)

In Kai's example it's valid to use - on it's own but that handles the date simply as an integer and subtracts the number specified. In small cases as in the example this can seem as if it's the seconds being subtracted but if for example you subtract 100 it simply reduces the number by that amount and not by 1 minute 40 seconds as it sould for a date type.


mysql> select now() - 100 day, now();
+----------------+---------------------+
| day            | now()               |
+----------------+---------------------+
| 20060426073122 | 2006-04-26 07:32:22 |
+----------------+---------------------+
1 row in set (0.00 sec)

You can see above the seconds portion of the first "date" is the same where it should be different if it were dealing with it as a time. You can also see that any date formating has been removed (suggesting something odd is going on).

The second problem is that while you might think that not including the interval would case a problem it becomes a valid SQL statement because it treats the DAY simply as a heading in the output. To change the heading of a column we can either use AS column_name or simply column_name after our column definiton, this can be seen like so..

mysql> select now() - 100 day, now();
+----------------+---------------------+
| day            | now()               |
+----------------+---------------------+
| 20060426073122 | 2006-04-26 07:32:22 |
+----------------+---------------------+
1 row in set (0.00 sec)

mysql> select now() - 100 as "anything you fancy", now();
+--------------------+---------------------+
| anything you fancy | now()               |
+--------------------+---------------------+
|     20060426073738 | 2006-04-26 07:38:38 |
+--------------------+---------------------+
1 row in set (0.00 sec)

So to add and remove time from dates you need to make sure you use the interval. Something to watch out for when you get unexpected results is the column heading, that might give a clue that all is not right with your SQL.

MySQLDevelopment.com gets a stay of execution

2006-04-25T10:35:00.000Z

When I posted a few weeks ago about the end of MySQLDevelopment.com it seems the timing was a little off. I was planning to close things down on 30th April simply as it was the end of a month, what I hadn't taken into account was that this would coincide with the MySQL user conference. I won't say too much at the moment but there are plans to use the content on another site which will have some involvment with MySQL themselves, however because of the user conference the people involved won't be able to transfer the content until after the 30th.

Our visitor numbers are still growing the figures for March were twice those of January so to avoid any distruption of service I won't be pulling the plug until I can make a seemless switch.

MySQLDevelopment Update.

2006-04-07T09:15:00.000Z

I have had a great response from people with regards to the (possible) closure of MySQLDevelopment. It seems people want to keep the site alive in one format or another and I have had a lot of communication from MySQL themselves with regard to keeping the content alive.

First let me say sorry to all the people who did respond, as suggested in my last post I am really busy at the moment and it's difficult to find the time to reply in a timely fashion. I will be contacting you all individually this weekend.

Whatever happens it seems that while the site may change a little as from the 1st May MySQLDevelopment will still be available in some form. I'll keep you posted.

Calling it a day.

2006-03-31T11:15:00.000Z

I have been blogging since last June (is it really that long) and www.mysqldevelopment.com has been going for over a year now. But as the frequency of posts on this blog and updates on the site will testify recently I just haven't had the time I used to have with regards to MySQL. One of the good and bad things with the web is that it's relatively cheap to just leave stuff hanging around where in traditional media, books for example, the costs of continual production mean things go out of print never to be seen again. This means that the web is full of sites and pages which are left untouched for months or years. Most of the time that isn't a problem but often, especially with IT related sites the information can be way off the mark.

It's with that in mind that as from 1st May 2006 www.mysqldevelopment will no longer exist and this will be my last MySQL related blog entry. I no longer have the time or energy to commit to it and with out that the site won't move forward. Of course if somebody wanted to take it on that would be great but privately I have been looking at ways to move the site forward without success.

I want to say thanks to all the people who took the time to read my blog and to visit MySQLDevelopment.com and definately a huge thank you to those who contributed to the site over the last year.

So long and thanks for all the fish.

Debugging Stored Procedures in MySQL

2006-03-24T15:46:00.000Z

I have recently seen a couple of posts on the MySQL forums with regard to debugging stored procedures in MySQL. The people asking have been Oracle developers who like most people developing stored procedures in Oracle have been using an Oracle built in package called DBMS_OUTPUT. The package essentially accepts text which is then inserted into a buffer which can be viewed after a procedure has been executed (or even during using the correct tools). It got me thinking how easy it would be to add a similar debug method to MySQL so I came up with the following.

I created a new database called debug, this isn't strictly necessary but I like the idea of having a set area for the debug constructs to reside. I then created a table to hold the output, to keep it simple I simply gave it an id column so we could use the debug across a number of procedures at once, a text column to hold out debug statements and an auto increment column so I could order the results with some certainty.


DROP TABLE IF EXISTS debug.debug;
CREATE TABLE  debug.debug (
  id varchar(100) default NULL,
  debug_output text,
  line_id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (line_id)
)

I then created three procedures, debug_on to turn logging on, debug_insert to insert debugging messages and then finally debug_off to stop debugging, display the results and then clear the debug table of records for that id.


DROP PROCEDURE IF EXISTS `debug`.`debug_on` $$
CREATE PROCEDURE `debug_on`(in p_proc_id varchar(100))
begin
  call debug.debug_insert(p_proc_id,concat('Debug Started :',now()));
end $$

CREATE PROCEDURE `debug_insert`(in p_proc_id varchar(100),in p_debug_info text)
begin
  insert into debug (proc_id,debug_output)
  values (p_proc_id,p_debug_info);
end $$

CREATE PROCEDURE `debug_off`(in p_proc_id varchar(100))
begin
  call debug.debug_insert(p_proc_id,concat('Debug Ended :',now()));
  select debug_output from debug where proc_id = p_proc_id order by line_id;
  delete from debug where proc_id = p_proc_id;
end $$

I can now call these from my stored procedures to get debug information like so.


CREATE PROCEDURE test.test_debug()
begin
declare l_proc_id varchar(100) default 'test_debug';
  call debug.debug_on(l_proc_id);
  call debug.debug_insert(l_proc_id,'Testing Debug');
  call debug.debug_off(l_proc_id);
end $$

mysql> call test.test_debug();
+------------------------------------+
| debug_output                       |
+------------------------------------+
| Debug Started :2006-03-24 16:10:33 |
| Testing Debug                      |
| Debug Ended :2006-03-24 16:10:33   |
+------------------------------------+
3 rows in set (0.20 sec)

Query OK, 3 rows affected (0.23 sec)

Not rocket science but I'm sure all you Oracle users will be happy, you could of course call the DB DBMS_OUTPUT to make you feel really at home :).

Do you really want to comment out that code?

2006-03-16T15:51:00.000Z

Yet again I have been on the MySQL forums and yet again it's given me something to write about. The question was pretty standard simply asking how you comment out code or add comments to MySQL stored routines. However the wording of the question got me thinking. In the past when I have been writing complex stored procedures in Oracle it can be difficult to see where an error is coming from, not necessarily which line is raising the error but which section of code cause the problem. One of the methods I have used in the past is to use the /* */ multi line comment syntax to exclude blocks of code on mass to rule them out quickly, this has proved a good way to narrow down where the root of an error comes from.

But as you may know MySQL simply removes any comments from the code if it's entered via the command line, it makes no distinction between comments you are adding and code which has been commented out. This isn't a problem when you write procedures in script files and upload them but if you use the command line directly (either typing them in or copy and paste) it means the commented code will be lost forever.

Yet another example of why you don't want to write stored procedures directly into the command line.

Event Scheduler on Mac OS X

2006-03-15T11:14:00.000Z

One of the things I learnt a year ago when I first started working with MySQL stored procedures was that you really need to have the latest version of MySQL running. By the time you have identified a possible bug and noted down the specifics a new version was available which invariably fixed the problem.

This is also true with release 5.1, I have recently started writing content on the event scheduler over at www.mysqldevelopment.com and found a few bugs early on. Given that 5.1 is still very much in development I tend not to raise bugs these days without first checking on the forums and then making sure I have the very latest release to test against.

One of the biggest issues I had was that one off events worked fine, but events that fired at intervals didn't. I say they didn't but one of the big problem with testing events is that you have to wait a long time to prove something. I was using 1 minute intervals which didn't work, I also tried 5 and 10 minute intervals also. Testing hour intervals was just about practical but any more than that was just too time consuming. I checked on the forum but there doesn't seem to be a great deal of activity in the event scheduler section at present, so I downloaded a newer version of 5.1 (in this case going from 5.1.6 to 5.1.7), testing again proved positive and it seems whatever the problem had been was now fixed.

I think the event scheduler is a great new feature and I can't wait to get the content finished, unfortunately it's taking a bit longer than I hoped due to other commitments and also the fact it takes a fair amount of time to testing things.

Spread the word

2006-03-08T15:34:00.000Z

As you may or may not know I'm a sysop over at the Quest Pipelines MySQL forum. We don't get a great deal of visitors in the MySQL forums there, due in part I guess to the fact the forums on the MySQL website are so good.

However in addition to running the forums Quest produce a monthly database newsletter which is sent to over 28,000 database professionals. Subjects vary but the general format is an article on Oracle, SQL Server, DB2 and for the last year or so MySQL. For me this is a great way to spread the MySQL word to other database users but unfortunately I'm insanely busy with the day job so I can't submit anything this month.

So if you have something written or would like to contribute then let the newsletter team know at

newsletter@quest-pipelines.com

Or have a look at the latest addition over at the MySQL area of the pipelines site.

http://www.quest-pipelines.com/pipelines/mysql/index.asp

The MySQL Prompt

2006-02-28T12:07:00.000Z

Following on from my previous post about tips I have been picking up from the MySQL certification study guide I have another which I think is worth mentioning.

Personally I had never noticed that the MySQL prompt changes in relation to what went before it. How often have you done something similar to the following.


mysql> select * from emps where emp_name = 'john ;
    '> ;
    '> ';
Empty set (0.01 sec)

You type in a select statement only to open a quote but never close it, personally I always stick in another ; before I realise the mistake and terminate the quote and end the statement correctly. But what I didn't realise before reading the study guide (and making me very unobservant) was that the mysql> prompt changes in response to the currently open quote. In the above example this isn't obvious because it just looks like a continuation but in fact the prompt has been changed to show us that it is expecting a closing single quote. Take for example the following.


mysql> select * from emps
    -> where emp_name = 'john';

In this case it gives us the standard -> continuation prompt. Firstly I never even noticed this and secondly even if I did I doubt I would have seen the significance, generally I'm more focused on why the five semi-colons I entered have failed to be recognised. The certification study guide points out that there are in fact 5 prompts.


mysql> 
    ->
    '>
    ">
    `>

This becomes much more relevant when we are entering large complex SQL statements where it might not be obvious where the mistake has occurred. Take this SQL for example.


mysql> select e.emp_id, e.emp_name, d.dept_name
    -> from emps e, dept d
    -> where e.dept_id = d.dept_id
    -> and e.emp_name = 'john
    '> and e.dept_id = 1;
    '>

While this isn't as complex as it could be it’s easy to see in this instance where the problem occurred because our prompt changes from -> to '> on line 5.

We can use this in combination with the \c tip I mentioned earlier.


mysql> select e.emp_id, e.emp_name, d.dept_name
    -> from emps e, dept d
    -> where e.dept_id = d.dept_id
    -> and e.emp_name = 'john
     '> '\c

Study and You Shall Learn

2006-02-28T11:45:00.000Z

Originally this blog started as a reference for people moving from Oracle to MySQL, over the course of the 5 or so months it's been going I have at times moved away from that and on to other topics. I have tended however to keep it technical and avoided commenting on anything related to the business side of MySQL. Since Christmas my MySQL activities have decreased somewhat but over the last few weeks I've been doing more and more, be that attempting to update this blog or work on new content on www.mysqldevelopment.com. One of the things I have decided to do is take the MySQL certification exams, this isn't some great announcement and I'm a little wary of doing so publicly because I don't want to set a time limit on doing so.

Anyway on to the point, I have been developing with databases from the very first day of my IT career, mostly this has been with Oracle but also with Microsoft SQL Server, FoxPro, DB2 and Ingress. This has meant that I have picked up a lot of knowledge about databases and also SQL. On the whole this has meant that moving to MySQL has been painless, the few things that I needed to know specifically for MySQL have been easy to pick up. But having started to read the certification study guide I can see that there are gaps in my knowledge. Most of these are just small things that while are good to know are not a problem in the day to day operation of MySQL. However there are some really useful things that I have picked up, things which I just wouldn't have known or thought about had I not read the guide or been told by somebody in the know. I'm sure I'll be posting about more this in the future but here is a small tip I picked up.

Firstly it's \c, that may be something you use all the time but having worked with Oracle for so long I just got used to entering some rubbish to make the client give me a fresh line. If you like me are unfamiliar with \c then all it does is cancel and clear the command line if you make a mistake while entering a statement. In the old days I would have done something like this....


mysql> selec t * from emps
    -> where ;
ERROR 1064 (42000): You have an error in your SQL 
syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to 
use near 'selec t * from emps where' at line 1
mysql>

While I was on the second line I noticed that I had incorrectly typed the SELECT keyword so I terminated the statement by simply entering the ; delimiter. While this doesn't do any harm it does produce an error. Using \c we can do this gracefully like so.

mysql> selec t * from emps
    -> where \c
mysql>

MySQL Getting More Popular?

2006-02-22T17:15:00.000Z

My web hosting company provide some great statistics for me to look at with relation to my site www.mysqldevelopment.com. It's been interesting watching visitor and hit numbers grow over the last year since the site went live. A year ago for the month of February we had just 251 visits, this year however we have had 3446 in February as of about 5 minutes ago, and there are still 6 days to go in the month.

But this figure is up drastically from January 2006 and late last year. Each month last year was better than the previous one but growth was steady and we peaked in terms of traffic in November when the site had a lot of activity due to the release of 5.0 and me winning the first week of the 5.0 challenge. But visitor numbers only reached 2847 for November and were around 2500 for the months either side.

So I was just wondering if this was something thats happened just on www.mysqldevelopment.com or if it was a trend other people in the community were seeing.

WiFi Woes.

2006-02-18T16:15:00.000Z

I have a small network at home running a Windows XP laptop, a Windows desk top and a Mac Mini. I recently move the Mac over to the TV so I could connect it up and watch TV programs I saved using a digital TV gizmo I purchased for my Mac, it's great because it has pretty much made the video machine redundant and recording is a breeze.

However this meant that the Mac was too far away from the ADSL gateway to be connected via a cable. Given that it was a first generation Mac Mini it didn't come with a wireless card installed. I decided that given that an Airport card would have cost almost twice as much as a windows wireless card and that the Mac Mini at least seems difficult to upgrade yourself, I choose to move the gateway closer to the Mac and get a wireless card for the PC.

This seemed great for a while, but I have been having plenty of problems with the wireless connection going down which needs a reboot to fix, this as you can imagine is a real pain especially given that my CVS server and MySQL database are on the Mac. This seems to only be a problem with the desk top as the laptop doesn't seem to have the same issues.

I was hoping to write a fair amount of content for www.mysqldevelopment.com today as I have been a little lax at updating it of late. But the connection has dropped a total of 3 times already. So as I sit here typing this I have the gateway back over near the Windows desktop connected via a cable. This means I'm not able to connect to the Mac but does allow me to vent spleen.

It's tempting to think that the more complex things become the more problems we have, but then I think back to when I had a spectrum as a kid and you would have to sit through 8 minutes of yellow and blue lines and binary sounds coming out of the tape recorder only to find that at the end it didn't recognise a section of that 48K of code and failed to load that game of Daley Thompsons Decathlon, or the cat decided sitting on the break button was a good idea when you left the thing on all night because you didn't have the energy to finish Jet Set Willy the night before.

Index

2006-02-18T13:29:00.000Z

I got an email from Peter Mescalchin a few weeks ago asking me to check a problem he was having with a select statment. He has two fairly large tables one of 14,000 rows and another of 79,994 he is joining these two tables together using a column on the second which has an index. With the index in place the query executes quickly (0.19 seconds on my machine) but when the index is removed his machine hangs and on mine a P4 Hyperthread 2.8Mhz machine one of the CPU threads is kept at 70-80%, it finally completed 1 Hour, 16 Minutes, 2.16 seconds later. A similar thing happened on the Mac but the connect timed out before it returned.

I'm not a performance expert so I wasn't able to give Peter much of an answer other than to confirm what he was seeing. Anybody have any idea why there is such a dramatic difference?

For those looking to test here are the details Peter was using.


Two tables:

 CREATE TABLE `first` (
   `id` smallint(5) unsigned NOT NULL,
   `name` varchar(20) collate utf8_unicode_ci NOT NULL,
   PRIMARY KEY  (`id`)
 ) ENGINE=MyISAM DEFAULT 
   CHARSET=utf8 COLLATE=utf8_unicode_ci;

 CREATE TABLE `second` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `firstid` smallint(5) unsigned NOT NULL,
   PRIMARY KEY  (`id`),
   KEY `firstid` (`firstid`)
 ) ENGINE=MyISAM DEFAULT 
   CHARSET=utf8 COLLATE=utf8_unicode_ci;

Table `first` holds 14,000 rows, table `second` holds 79994 with
`second.firstid` a forgien key to `first.id`. Now to execute a query
to return rows from `first` that are not referenced in `second`.


 SELECT `first`.*
 FROM `first` LEFT JOIN `second` 
 ON (`first`.id = `second`.firstid)
 WHERE (`second`.firstid IS NULL)

Then delete the index on second.firstid.

5.1.6 On Mac OS X

2006-02-18T07:40:00.000Z

I have finally found sometime this weekend to update www.mysqldevelopment.com. So in the spirit of the site I'm looking forward and adding content on some of the new features in 5.1, first up it's the event scheduler.

I have been using my Mac less and less recently simply because I have been using it as a media centre and it now lives over by the TV, I generally connect remotely via putty or VNC. So given that I was on my Windows PC this morning I decided to install the latest release of 5.1 on there.

The download and install went perfectly and I started up the MySQL command line ready to have a play with the event scheduler. So I ran


mysql> SHOW GLOBAL VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

Great off to a good start, next up I have to set the event_scheduler variable to ON....


ERROR 2013 (HY000): Lost connection to MySQL server during query

Oops.. memories of the early work I did on mysqldevelopment.com with 5.0.1 came flooding back, having worked with recent versions of MySQL 5.0 which are totally stable it's easy to forget that 5.1.6 is still in the development phase. I remembered Markus Popp mentioning that the event scheduler had problems on windows and it seems it does.

So I connected to the Mac via VNC and downloaded 5.1.6 for the Mac OS X 10.3 (yep behind the times at bit on that as well). In the past I have had plenty of problems upgrading MySQL on the Mac, I'll be honest and say that was more to do with my lack of experience of the Darwin command line than anything else, but it seems I must have picked something up as this time it was really simple. I tend to make things easy for myself in that I just to clear out the old system and install the new one right on top, I don't have anything worth keeping on the Mac's MySQL database so that makes things relatively easy.

So I rebooted, went into the terminal and typed MySQL....


~ $ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.6-alpha

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Then I tried to turn on the event scheduler


mysql> set global event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

So that’s it, I'm all set ready to rock and roll with 5.1.6 and ready to test the event scheduler.

Cartesian Joins are Useful (Honest)

2006-02-09T15:00:00.000Z

For those that don't know a cartesian join is one that produces a cartesian product. A cartesian product is the result of joining two sets of data in such a way that all rows in one set are joined with all rows in the other. In MySQL this happens when we have two tables in an SQL statement but no join statement. For example...


mysql> select * from emp_dept;
+--------+---------+
| emp_id | dept_id |
+--------+---------+
|      1 |       1 |
|      2 |       1 |
|      3 |       2 |
|      4 |       2 |
+--------+---------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+---------+------+
| dept_id | name |
+---------+------+
|       1 | IT   |
|       2 | HR   |
+---------+------+
2 rows in set (0.00 sec)

Normally these two tables would be joined using a join condition on dept_id so that we return only the rows where a match is found.


mysql> select emp_id, name 
       from emp_dept e join dept d 
       on e.dept_id = d.dept_id;
+--------+------+
| emp_id | name |
+--------+------+
|      1 | IT   |
|      2 | IT   |
|      3 | HR   |
|      4 | HR   |
+--------+------+
4 rows in set (0.26 sec)

However if we remove the join each row in emp_dept is joined with dept if a match exists or not.


mysql> select emp_id, name from emp_dept, dept;
+--------+------+
| emp_id | name |
+--------+------+
|      1 | IT   |
|      1 | HR   |
|      2 | IT   |
|      2 | HR   |
|      3 | IT   |
|      3 | HR   |
|      4 | IT   |
|      4 | HR   |
+--------+------+
8 rows in set (0.01 sec)

This is a cartesian product. In most cases this isn't a great idea, I had a problem last year where the assumption was that there was a 1 to 1 relationship in a table join, the introduction of some code broke this rule and it resulted in a select returning 3 million rows where once it only returned 150 or so. The users were not happy when their application took 5 minutes to load when previously it took seconds.

Result sets can get large quickly because the amount of data in the select is the number of rows in Table A * the number of rows in Table B, if you have more than two tables this multiplies at an alarming rate.

But there are times when we can use this to our advantage. I'm working on a site which needs some combinations stored for a game engine, there are 6561 different combinations and I didn't fancy having to type them in by hand. So what I did was create a dummy table with all values I needed in each position, I then used a cartesian join by having a copy of the table for as many columns as I needed.

So lets take a simplified example, if we want all the combinations of 1,2 and 3 using 2 columns we can do this like so.


mysql> select * from dummy_table;
+------+
| nums |
+------+
| 1    |
| 2    |
| 3    |
+------+
3 rows in set (0.00 sec)

mysql> select concat(d1.nums,d2.nums) 
       from dummy_table d1, dummy_table d2;
+-------------------------+
| concat(d1.nums,d2.nums) |
+-------------------------+
| 11                      |
| 21                      |
| 31                      |
| 12                      |
| 22                      |
| 32                      |
| 13                      |
| 23                      |
| 33                      |
+-------------------------+
9 rows in set (0.00 sec)

More in More Out

2006-02-08T15:21:00.000Z

I've been busy of late, most of my time seems to be taken up with fixing layout bugs in my web applications introduced when I spend all day testing in FireFox which works perfectly, then only to have a quick test on I.E. to notice that there is a tiny portion of the page which is out and in the most ugly way possible. I then tend to mess the whole page up trying to track down where it might have come from. I suppose I should have learnt to test in I.E. early in the process but that means leaving the debugger and CSS edit facilities of FireFox behind.

Anyway back to the point, this means I have less and less time to devote to MySQL these days, but that doesn't stop me popping into the MySQL forums a couple of times a day (for no other reason than to stop me going crazy messing around with I.E.). I've posted a few times here recently on the need to be as descriptive as possible but one thing that seems to be becoming very important when answering is version information.

Firstly there are many people keen to try the new features of MySQL such as stored procedures, triggers and views, as we know they are only available from version 5 (triggers only from 5.0.3) so if people included the version number when asking a question related to the subject it would be immediately obvious they were using the wrong version. If nothing else it may even prompt the person asking the question to consider if the feature they are using is available in their version.

Secondly there are plenty of questions which relate to fairly complex SQL statements, many of those can be quickly answered using a sub select but as you may know this is only available from version 4.1, many of the people using the forums are using MySQL via their web hosting account and many of those are still on version 4.0 and below. The obvious thing to do in such cases is to give an answer for both 4.0 and 4.1 versions but often I don't have enough time to answer for one version let alone two, especially when the query is complex and needs a bit of care to give a full answer with an example.

This leads to me having to filter the questions based on how much information the user has given or more of a problem the first answer is simply a request for version information, with the way the site operates this means people in different time zones can wait up to a day for a decent reply.

I can see one of two ways to deal with this, try and educate users of the forums to include as much information as possible including the version of MySQL they are using or add a selection box on the forum page to specify the version number prior to asking the question, make this mandatory (with a unknown option, and also a default option in your profile) so that it prompts people to at least consider the question may be version related.

The problem of course with the first is that many people asking questions only do so once, they get the answer to their question and they never come back. The problem with the second is that this is something only MySQL AB themselves can control.

Keeping it short.

2006-02-03T16:26:00.000Z

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 programming time typing these things out in full (which also included typos on a regular basis). The development team could have used unique_id or even uid and everybody would still have know what the columns were used for but they went for the fully monty.

Don't get me wrong I'm definitely into using descriptive names but there comes a time when you need to think about how practical it's going to be when you have to type 60 extra characters per SQL statement.

I had a meeting with one of the original development team and he informed me the decision was taken because Visual FoxPro allowed 32 character column names where FoxPro 2.0 only ever allowed 8 characters, he said it was a decision that they wish the had never taken.

Improving your SQL skills

2006-01-25T09:15:00.000Z

I used to be almost exclusively a database developer, writing complex queries and stored procedures in Oracle and MySQL. Of late I've been doing more and more web based work, HTML, JavaScript, CSS, PHP and PERL, this means of course that I'm doing less and less SQL especially the more complex stuff that I would have been doing in the past.

Personally I've been using SQL long enough that while it might not flow from my fingers as it did previously with a bit of thinking it comes back fairly quickly. I tend to think of it like fitness, if you go to the gym regularly your fitness levels improve quickly, slack off and they drop back down almost as fast.

To keep my SQL skills "fit" I visit another sort of gym, the MySQL Forums, to be exact the Newbie forum. In that forum there are plenty of people offering you the chance of a mental work out at all skill levels, because of it's very nature there are all sorts of questions which often need a clever solution. While it’s a pain to most people the lack of support for sub-queries in MySQL 4.0 is great for a work out as it often makes you think a little harder about how to tackle a problem you might normally throw a sub-query at.

If you ever become too fit for the Newbie forum there are plenty of others, just like a gym has different equipment to work different areas of your body, the MySQL Forum has different sections to raise you SQL fitness levels further, the General and Performance being good ones to put mass on those SQL muscles.

The great thing about these questions is that during your normal working year you might never get to use some of the features needed to answer them, there is nothing worse that your boss dumping a set of reports requirements on your desk and having no idea how to even start constructing the SQL. But if you have been working out at the SQL gym you’re likely to have come across a few methods to get started.

So why not do what the government health departments keep telling us and spent 30 minutes a day in the gym.

XML - Using MySQL with AJAX (part 2)

2006-01-20T08:54:00.000Z

When I suggested MySQL had a big part to play in the X of AJAX it was more of a case throwing ideas into the ether than any solid or contrived plan. My initial thoughts were simply to allow the web server to talk directly with MySQL and return the XML needed for a give select statement. The goal was to avoid the normal middle stage of using some sort of scripting to accept the statement, call a standard MySQL select and process this into an XML format. Something along the lines of MySQL being an XML factory where you pass the select and MySQL directly passes back the XML.

Some great ideas have been suggested, mainly around using --xml to format the output directly from the command line, which would interact with Apache by a module. I've been able to knock up a Perl program which is capable of doing this but it's raised more concerns than solid answers.

When I looked at this more closely the immediate thing was how unsecured the whole thing would be, not so much unsecured as completely open in fact. The original idea was to essentially call MySQL directly from JavaScript (via the web server) but that would mean having the select statement, or at least the table and column names visible in the JavaScript. There would be no way to secure that without some level in between.

The original post in fact wasn't suggesting that we interface directly with MySQL as it currently is, it was more of a look into the future to see where for example stored procedures could be taken. The idea would have been to use stored procedures to accept parameters which were then turned in select statements and XML returned, that way we could at least avoid the potential of SQL injection attacks, or people simply calling SQL statements at will.

Don't get me wrong the ideas have been great and I'm still confident MySQL can be central in an AJAX system in the way suggested, but maybe not with the minimal number of levels I might have first envisaged.

WiFi Access

2006-01-19T13:32:00.000Z

An article on the BBC News website today suggests that WiFi is "slow to enthuse consumers". The report by Toshiba suggests that only 11% of people used WiFi away from home, while at home over 50% used WiFi to access the internet.

Personally I don't think for a second that it's a case of lack of enthusiams by consumers which is stopping people getting on line on their laptops when away from home. I'd love to be able to logon when I'm out and about especially since I just got a WiFi enabled mobile phone. I did a little bit of research using the WiFi alliance web site which lists WiFi hot spots in the UK (as well as the rest of the world). Within a 2 mile radius of my home there are 6 hotspots, all of which in pubs. While I enjoy going to the pub it's the last place I want to take my £1000 laptop for the evening. Extending the search to 20 a mile radius and I'm up to 251 and this time there are a few coffee shops but few in a place I'm actually likely to be.

Be here comes the point, I then check for free hotspots, there are just 2 in a 20 miles radius. Checking the price plans for the pay services reveal that it's going to cost me cira £30 a month to get connected. Add on top of that the £2 for a coffee or £3 for a pint of beer I'll have to pay to sit in the pub of cafe while surfing and that soon starts to add up. Add that on to the £20 I'm already paying for internet access at home and thats a hell of a lot of money.

Perhaps the headline should have been....

"Cost of WiFi fails to enthuse consumers"

BBC News : Wi-fi slow to enthuse consumers

MySQL Stored Procedure Programming (The Book) - 2

2006-01-19T11:45:00.000Z

This is the second time* I've mentioned the O'Reilly MySQL Stored Procedure Programming book on this blog. I'm mentioning it again because I suddenly realised that the release date of March 2006 isn't anywhere near as far away as I thought it was, when your in 2005 any date in 2006 seems like a bit of time away but on reflection and now being half way through the first month of the year it seems to be creeping up with even increasing speed.

With that in mind I placed my order with Amazon.co.uk today, it's avaliable on pre order and with a 30% discount on the cover price.

Click here to view the book on Amazon.co.uk

I have all 7 of Steven Feuerstein's Oracle books and as I said previously if it's half as good as those it will be a great book.

*I have no vested interest or association with the book, O'reilly or the authors. I just know it's going to be a great book.

Don't be afraid to get it wrong.

2006-01-18T15:18:00.000Z

While it may not be a great thing to admit but some of my best lessons have come at the result of initial failure, taking a backup before deleting all the data in a table comes to mind. When I first started this blog and even more so when it was added to PlanetMySQL I made a decision that not being 100% sure about something wasn't a reason not to post it. Don't get me wrong I always make every effort to check my facts and in the Tom Kyte mode I'll always (or at least try) and give real world examples to prove my point.

But there are often times when I'll post something which is inaccurate or just plain wrong. There are broadly two things you can do in such circumstances when people correct you (and believe me they will), you can accept you made a mistake, correct any errors and learn from it. Or you can just give up and never post again for fear of looking a total fool. The fact I've made mistakes and I'm still blogging will give you some indication of which group I fit into.

This slightly confessional blog is in response to a post I made a few days ago with regard to XML and MySQL. The central theme was a vision of MySQL as a key component in AJAX where rather than the added complexity of a language such as PHP, PERL etc MySQL took on the role of serving the XML directly from the web server. In it I pointed to Oracles SET MARKUP HTML command and longed for a MySQL equivalent, or at least an equivalent XML version. If you read the comments on that post you will see that MySQL does support such a feature, it's not exactly what I was talking about but it's close enough to have been mentioned. I'll say that I was at least in the past aware of the feature but when I was checking out my facts prior to posting I couldn't find mention of it on the MySQL site, I just thought I was mistaken and it was a feature of some other software.

The point is that you shouldn't sit in the shadows fearful of getting something wrong, if you have been worried about blogging or answering questions on forums for fear of being wrong you won't get very far.

Yesterdays mistake makers may well be tomorrows experts*

*Providing they learn from those mistakes.

XML - Using MySQL with AJAX

2006-01-16T15:36:00.000Z

There has been a few posts on PlanetMySQL recently with regard to an XML, firstly the new XML functions for extracting information from XML stored in standard tables and more recently an XML storage engine.

It got me thinking, I've posted recently about working with AJAX and I've been developing some AJAX functionality for our clients. It seems to me that part of the goal with AJAX is to give a better user experience but also to reduce the amount of traffic to the server by only loading what's needed, when it's needed. Not as in our case a refresh of the page (along with the associated images). There is a balancing act between making the page functional and also keeping the amount of Javascript down, it's easy to shift alot of HTML from the initial page only to replace it with complex JavaScript functionality to manipulate XML which is returned in the xmlHttpRequest calls.

I've used Oracle's HTML DB which is an Apache Module which calls Oracle stored procedures and returns the output to the requesting browser, this allows people who are comfortable with PL/SQL to develop web applications from within Oracle, no need for PHP,PERL etc. In fact this is the very reason I became involved with MySQL, looking for an equivalent technology in MySQL.

What would be great is if we could use an Apache module to return the contents of a MySQL stored procedure, this procedure could accept parameters and return an XML document straight from Apache, no additional PHP layer just an HTTP response directly from the webserver interacting with MySQL.

Another great feature of Oracle is SQL*Plus' ability to export in HTML, using the command SET MARKUP HTML ON any subsequent query is output on HTML table format. Something I think would benefit MySQL would be the possiblity of outputting data in XML format, that way we could even remove the stored procedure layer in the above example.

That would fit fantastically into AJAX. Something like the following cold have uses far and above just AJAX...,


mysql> select * from emps;
+-------+---------+
| empno | ename   |
+-------+---------+
| 1     | Dave    |
+-------+---------+
| 2     | John    |
+-------+---------+
2 rows in set (0.00 sec)

mysql> set output xml 

mysql> select * from emps;

<emps>
  <row>
   <empno>1</empno>
   <ename>Dave</ename>
  </row>
  <row>
   <empno>2</empno>
   <ename>John</ename>
  </row>
</emps>
2 rows in set (0.00 sec)

Event Scheduler

2006-01-16T09:33:00.000Z

Release 5.1 of MySQL comes with a new event scheduler, this is great news and further moves the MySQL database away from just a data store, something which began with the introduction of Stored Procedures and Triggers in 5.0.

The event scheduler will allow you to run an SQL command at a specific time, this is great for maintenance purposes. It's possible to run the command a single time or set up and interval and run it multiple times. What's also great is that you can use DDL, DML and even compound statements which allows SQL/PSM syntax to handle processing as part of the command.

I'll admit to not having downloading and installing 5.1 as yet but this is definitely something I'll be using. As an Oracle user I've been using Oracle's version DBMS_JOB for a while and this looks to be as good, if not better due to the easy method of adding events.

Having said all this often in Oracle I have often preferred to use a combination of SQL*Plus and CRON due to the ability to process the output in Unix/Linux after the job has run. For those using a MySQL version other than 5.1 CRON does offer a great way of doing a similar job to the new event scheduler.

You can see an introduction to the event scheduler here.

http://dev.mysql.com/tech-resources/articles/event-feature.html

Blue Screen of Death and other Myths.

2006-01-16T09:02:00.000Z

I was half listening to a radio show yesterday about the Consumer Electronics Show, the general feeling that this year there wasn't really a wow moment and nothing earth shatteringly new had been shown. One of the guests was even rather dismissive of Apples new Intel based laptop range (which while a major shift from Apple isn't actually a major step forward in general). But one of the things that was said was about the unreliablility of Windows, the dreaded Blue Screen of Death (BSoD) was mention and how often it happens.

It got me thinking, is that really true? I've been running windows (in it's various forms) since the early 90's and while it crashed with regularity in the earlly days I personally haven't had many problems for years. At work I ran NT for almost 3 years without even turning the PC off and only twice did I get the BSoD, after a quick restart everything was fine.

At home with XP it's just as good, while the PC doesn't stay on for days I've never had a BSoD and from what I recall never had to hold the off button down to clear a crash. I got a Mac last year and while that has a great reputation for stability I have had to hold the off button on a couple of occassions, am I to infer that Macs are buggy and unreliable?

AJAX, iPods and the MySQL Guild

2006-01-09T10:27:00.000Z

I took a little break over the Christmas period and avoided using the computer as much as possible, so this is my first blog of the year. I mentioned AJAX in my last blog and in the time since then my demo was completed (after a few teething problems). I gave a demo to my boss and a couple of clients on Friday and it went down really well, it's often difficult with clients as people are so used to computers these days people have a certain level of expectation and while us as technical people can see the benefits of something like AJAX it's often lost in translation somewhere along the line. Thankfully in this case they were really keen and we are in the process of setting up a test to see if it improves the clients business.

Last week I recieved my iPod Nano, the prize from the MySQL 5.0 competition. It had been a while in coming due to me not being home when the delivery company called before Christmas. It's small, very small but sounds great, it's also black (my prefered colour) and the 4Gb version which I was suprised and greatfull to MySQL for.

Finally I have had the great honour of being added to the MySQL Guild, as both a writer and expert. It's great to have my work recognised over the last year and this is the icing on the cake. You can see a list of all the guild members here http://dev.mysql.com/guilds/

AJAX

2005-12-28T16:38:00.000Z

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

2005-12-22T13:47:00.000Z

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.

2005-12-22T12:07:00.000Z

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.

2005-12-21T17:06:00.000Z

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

2005-12-21T10:51:00.000Z

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

2005-12-20T08:59:00.000Z

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)

2005-12-19T16:22:00.000Z

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

2005-12-16T15:40:00.000Z

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

2005-12-12T14:55:00.000Z

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?

2005-12-02T08:47:00.000Z

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.

Formating Output of SQL

2005-11-18T12:28:00.000Z

I mentioned a few posts ago about Oracle's SQL*Plus client program and how great it was at formatting output in different and novel ways, providing you know the commands to do it. One of the biggest problems is that the default linesize (the width of the screen buffer used to display rows of data in a table) is small and only actually displays something if you have small amount of columns. About 95% of the SQL I write breaks this default and displays something which is totally unfathomable.

The answer of course is to change the size of the linesize parameter, it's easily done and in fact with SQL*Plus you can set up a file to set this on log in, but I'm often connecting to servers where this file doesn't exist.

Anyway getting to the point I found a nifty way to format data in MySQL into a more readable format, this isn't some great secret but I wasn't aware of it until recently. Let's take a normal SQL statement and result from the MySQL command line.


mysql> select * from emps where emp_id = 1;
+--------+----------+---------+--------+--------+
| emp_id | emp_name | dept_id | salary | bonus  |
+--------+----------+---------+--------+--------+
|      1 | Paul     |       1 | 100.00 | 100.00 |
+--------+----------+---------+--------+--------+
1 row in set (0.08 sec)

MySQL actually does a very good job of formatting the data in a readable format, but if we have a table with a large number of columns things get a little crowded, we have to keep scrolling left to right to see which data at one end relates to the other.

But help is at hand, in MySQL you can format the returned results to be returned vertically rather than horizontally. This makes it easy to see data which is relevant to a single row only. To do this you simply append \G to the end of your select statement like so..


mysql> select * from emps where emp_id = 1\G
****************** 1. row *****************  
  emp_id: 1
emp_name: Paul
 dept_id: 1
  salary: 100.00
   bonus: 100.00
1 row in set (0.00 sec)

Check Constraints in MySQL 5.0?

2005-11-16T12:46:00.000Z

First let me point out MySQL doesn't support check constraints, I just wanted to point that out up front before you run off looking for them. When I was writing content on Triggers for my site www.mysqldevelopment.com, I was looking at ways to use triggers in the real world. People were asking me why they might want to use them. I came up with a few but one I mentioned just didn't seem to work. That was implementing check constraints via triggers. A check constraint is a constraint on a column in a table which limits what the column will accept, Oracle for example allows the use of check constraints when creating a table. MySQL itself has constraints such as primary key and unique but the features are very much limited to what MySQL wants you to do. A check constraint allows more flexibility, you can dictate what you want to allow in the column.

For me it was obvious that triggers would be a great place to implement such a thing but the more I looked the less happy I was about implementing them. The first reason was that prior to 5.0.10 you couldn't access SQL from within a trigger. One of the things I would have needed to do was raise an error in the trigger to stop processing the insert/update, MySQL SQL/PSM doesn't currently allow you to raise an error manually, one way to do that would be to artificially raise one but because of the lack of support for SQL in triggers this wasn't possible without doing some potentially damaging things to the database.

However come release 5.0.10 I still wasn't happy, the sort of error messages we could raise didn't relate to the actual error at all. So I gave up on the idea in the short term.

But then I got an email from Scott Maxwell who came up with a suggestion on how this might be done. He's written a short article on the subject which can be seen here....

Emulating Check Constraints using Triggers.

Writing SQL Results to a File

2005-11-10T14:42:00.000Z

Having been somewhat concerned with MySQL 5.0 in recent posts (and the iPod now being won :)), today I'm going to be going back to looking at some of the differences between MySQL and Oracle.

Today it's the turn of output to a file from an SQL statement. Many of us old skool Oracle developers still prefer to use the good old SQL*Plus command line client, on of the reasons being that no matter where we are, be it on our desk top, on a clients site or even remotely via an ssh client we can be pretty sure a version is available. The great thing about SQL*Plus is that like the MySQL Command Line tool it's consistent across platforms, however one of the difficult things is that in it's standard mode you need to remember all manner of commands to get information to display the way you want.

But the great thing about SQL*Plus is it's easy to save the results of your SQL to a file. All you need to do is issue the following command.


SPOOL location/filename

From then on any command issued in SQL*Plus is exported to that file. This is great but also at times rather annoying, for example the standard page size setting in SQL*Plus is 14, this means the column headers are reprinted after every 14 rows returned, the standard line size is also only 80 characters wide. It also displays the SQL command you used and a summary of the rows you returned.

All these things are great when you need them but not so when you don't. SQL*Plus is very powerfull when it comes to reporting but most of the time my boss just wants a list of all the products which are out of stock, he doesn't care what the column name is (and certaily doesn't want it every 14 rows), he's not concerned with the SQL I used to get the data, and in many cases doesn't want to know how many rows it returned. So I then have to remember any number of SQL*Plus settings to suppress the information.

To be honest I'm making a meal of it, it's not that hard. Once you have run your SQL and stored the results in the SPOOL file we then just issue the following command to tell SQL*Plus to stop sending output to the file.


SPOOL OFF

That's all there is to it.

So what about MySQL, well it's a lot easier to get what we want with MySQL than Oracle. We can embed our file output requirements right inside our SQL. This is done using the INTO OUTFILE command like so.


mysql> select emp_id, emp_name from emps 
       into outfile 'c:/test.txt';
Query OK, 4 rows affected (0.03 sec)

This simply produces the file without any fuss, each column output as is in the table.

With SPOOL we can manipulate the file by simply formatting the output of the SQL, so if we wanted to output each column in the table with a comma delimiter we would have to concatenate the values together in the SQL to produce the required result. In MySQL there are a number of options we can use with INTO OUTFILE to change the way data is written to the file. These are FIELDS ESCAPED BY, FIELDS ENCLOSED BY, FIELDS TERMINATED BY and LINES TERMINATED BY. So if we wanted our output to be enclosed in double quotes, with comma delimiters and a new line termination for each row we would use...


SELECT emp_id, emp_name 
INTO OUTFILE 'c:/result.text'
FIELDS TERMINATED BY ','  ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM emps;

This would produce the following file.


"0","Barry"
"1","Paul"
"2","John"
"3","Alan"

There are of course some advantages to both, you can't for example continue to output to the same file across multiple results when using INTO OUTFILE, which you can using spool, but when using INTO OUTFILE you can concentrate on what you want selected and let MySQL deal with the formatting of the output rather than having to write it long hand when using SPOOL.

Note: The MySQL documentation shows the INTO OUTFILE as coming after the column list and before the table clause (as with SELECT INTO when using SQL/PSM) but it seems to work just as well if you have it after the from clause as I did in my first example.

What hope for MySQL 5.0 Hosting

2005-11-02T12:09:00.000Z

One of the great things about MySQL is its support from web hosting companies, many people use MySQL as a backend database for their websites, there are literally hundreds of web hosting companies offering MySQL and PHP and there are dozens of PHP/MySQL books on the market.

I've written a fair amount about the new features of MySQL 5.0 and in particular Stored Procedures and Views. But there's nothing worse than people telling you to do one thing and doing another themselves. My site www.mysqldevelopment.com runs on PHP 4 and MySQL 4.0, simply because that's what my hosting company offers, they are cheap and offer a great service. I recently asked them what their plans were for upgrading accounts to MySQL 5.0, given that they are still on 4.0 I didn't hold out much hope. I was right they don't have any plans to upgrade in the short or medium term and given they didn't even know MySQL 5.0 was about to be released I wouldn't expect much to happen in the long term either.

I'm about to start a new project, a simple site for me and a few friends and I thought it would be a great way to use 5.0 and also to include my observations in this very blog. But where could I get 5.0 hosting, I looked around the web and found nothing, well nothing apart from people offering to build be a server just the way I wanted and to be honest this site isn't anywhere near important enough for me to spend any money on it.

Then I hit apon an idea, you may already be aware of db4free.net, they offer access to MySQL 5.0 which is hosted on their server, it's not web hosting but it's possible to remotely connect and therefore use your existing host to serve the pages.

So all I need to do is use my hosting account to serve the PHP but connect remotely to my account on db4free.net.....

... but alas even that doesn't work, because to connect to MySQL 5.0, as least to use stored procedures you need to use the MySQLi extensions, these need to be compiled specifically under PHP4.

I suppose it's back to do as I say and not do as I do.

Oracle Database XE

2005-10-31T14:10:00.000Z

Latest news from the Oracle world is that they have just released a new beta version of their database product, Oracle 10g Express Edition (Oracle Database XE). What's so special about this release? Well it's free, Oracle have offered their databases for download for free to developers for as long as I can remeber but the important difference with XE is that you can use it in production environments without any charge also. This seems in direct competition with the current "free" databases on the market such as MySQL.

The database is limited in that it can only be installed on single processor machines with 4Gb of disk memory and 1Gb of RAM. Andrew Mendelsohn, senior vice president of Oracle's server technologies division has said

"There is definitely a market there (for low-end databases) and a demand. And we want them to be using Oracle and not MySQL or SQL Server Express"

The theory is that students will be able use XE for free which will hopefully lead them on to the full Oracle database products. I suppose the question is, is it all a little too late, MySQL already has a great reputation in the area Oracle are targeting and IBM has, and Microsoft are, looking to release free lite versions of their database offerings, DB2 Express and SQL Server Express.

Versions for Windows and Linux are available for download from the Oracle website

http://www.oracle.com

Where did my comments go?

2005-10-25T09:58:00.000Z

Following on from post about the MySQL forums, I answered a question this morning which might be of use to others. It relates to comments in MySQL Stored Routines (SQL/PSM).

I suppose a quick mention of how to write comments in SQL/PSM would be usefull. There are two types of comments in SQL/PSM firstly there is a comment characteristic value we can use to store general comments about the routine, we will look at that later, then there are in-line comments, thesw will be similar to comments you may have used in other procedural languages or programming in general.

The first is a single line comment, when ever you wish to simply show a single line comment you can use the following syntax


-- This is a comment.

MySQL will ignore anything after the -- when it compiles the SQL/PSM code. The second type of in-line comment is the /* combination. This can be used to make comments on multiple lines of code like so.


/* This is the first line of the commnet 
   and this the second line. */

MySQL will ignore any text between the /* and */, you must make sure that if you use multiple line comments that a closing */ is present. It's up to you which style you use, personally I tend to use -- as it's a habit I picked up years ago, I also tend to add ++ after the first comment character to denote the start of a section of comments, but thats very much a style choice on my point and doesn't offer anything interms of compilation.


 --++ This is the first line of the commnet 
 --   and this the second line

OK so we know how to write comments in SQL/PSM now on to the problem. Let's create a very simple SQL/PSM procedure.


create procedure CommentTest() 
begin 
   -- this is a comment
   select 'Comment Test';
end;
//
Query OK, 0 rows affected (0.00 sec)

Now here comes the issue, we can issue a command to view the SQL/PSM code stored in the database like so.


mysql> show create procedure commenttest//
+-------------+------------------------------------+
| Procedure   | Create Procedure                   |
+-------------+------------------------------------+
| commenttest | CREATE PROCEDURE `commenttest`()   |
|             | begin                              |
|             |                                    |
|             |   select 'Comment Test';           |
|             | end                                |
+-------------+------------------------------------+
1 row in set (0.00 sec)

The SQL/PSM code is shown but there are no comments, this can also be seen if you are
using the MySQL Administrator, when you click the "Edit Stored Proc" button the code is shown, but no comments are displayed.

Where have they gone? well mysql removes any inline comments when the procedure is loaded into the database. This isn't such a problem for somebody like me who creates scripts to create procedures as I have the comments retained in the script, but if you create your procedures in something like MySQL administrator then those comments will be gone for good.

Unfortunatley there isn't anything that can be done about this at present. The comments have passed on! They are no more! They have ceased to be! they have expired and gone to meet 'their maker! Bereft of life, they rest in peace! their metabolic processes are now 'istory! off the twig! they kicked the bucket, they shuffled off 'their mortal coil, run down the curtain and joined the bleedin' choir invisibile!! THEY ARE EX-COMMENTs!!

The MySQL Forum

2005-10-25T09:39:00.000Z

Every now and again I browse the MySQL forums, it's a great way to help others, hone your own skills and also to learn something new. It's also a great inspiration for this blog, many of the topics I mention are directly related to message I see posted. I don't refer to messages directly as I wouldn't want to point out peoples mistakes publicly, which is often the reason behind a blog entry, I see a lot of the same sort of problems or assumptions and so write about it here to try and spread the knowledge around a bit.

Making mistakes or assumptions is never something to be ashamed of, as you well know I make them all the time, it's obvious people are coming to MySQL from other databases and trying to do things the same way they have in the past. Rather than worry about them we should embrace them, use them as a method for learning. The hard part comes in actually asking for help, it can often be a little difficult asking other people to look at your code, we want people to help with the immediate problem but we don't want to expose our code to the eyes of others.

But if you do need help with a MySQL related problem, be it installation, simply SQL or stored procedures do checkout the MySQL forums, http://forums.mysql.com/. But don't let that be your only reason for visiting, get involved, have a go at answering questions yourself.

I for example know a fair amount about stored procedures but I know very little about performance issues. So I visit the performance forum from time to time to see the sort of thing people are asking about, this gives me a chance to look at things I might never have seen before.

So give it a go, get involved.

Finding (another) name for MySQL Stored Procedures

2005-10-25T07:36:00.000Z

If you checked my blog yesterday you may have seen a post titled "Finding a name for MySQL Stored Procedures". The general theme was trying to find a smaller and more snappy method of refering to stored procedures within MySQL. I got a fair few comments from people about the blog which were generally positive and welcomed the idea.

However, I recieved an email from MySQL AB requesting that we didn't use the name I had decided apon. The reason begin that it included a My prefix and MySQL AB feel that doing so dilutes the trademark. This can be seen in action for example in the renaming of MyODBC to Connector/ODBC.

I of course don't have a problem with this, the intention of giving stored procedures a name was more to do with keeping things short and also removing the ambiguity of the word procedure when actually talking about functions.

The big question is do MySQL stored procedures actually need a name? They conform to the ANSI Standards, or at least a subset of it, so perhaps when refering to stored procedures we can use the ANSI title of SQL/PSM (Persistant Stored Modules).

A MySQL Mantra

2005-10-24T07:55:00.000Z

Having been in a previous incarnation an Oracle developer I kept a close eye on a site called AskTom. The Tom of AskTom is Tom Kyte who is an Oracle employee with a great passion for helping people with their Oracle problems. He's also an author of some great Oracle books which I recommned anybody developing with Oracle either directly via PL/SQL or with Oracle as the backend database read before they type a single line of code.

Tom has a mantra with regard to developing with Oracle. It goes like this.

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as little PLSQL as you can.
o if you cannot, because plsql cannot do it, try some java (eg: sending an email
with an attachment)
o if you cannot do it in java (too slow, or you have some existing 3gl code
already) do it in C as an extproc.

This is great advice, at least for Oracle developers, in that it's making the most of each method at the appropriate stage. An SQL statement for example is far superior for data manipulation than say PL/SQL because Oracle has been built from the ground up to be as fast as possible when using SQL. But it's also true that the feature set is limited when it comes to decision making, this means there are often times when you need to revert to PL/SQL to get the job done. The third stage is somewhat particular to Oracle in that it has a JVM built into the database, therefore you can call Java as you would a stored procedure and execute this right inside the database.

One thing to be clear on here is we are not talking about developing whole applications in Oracle, we are talking about manipulating data, this means that we really won't be getting much passed stage 2 in 99% of the situations we might come across. The only time we might need to move on to the Java stage is if we need to interact with the outside world, Tom's email example being the obvious case (having said that in Oracle there are PL/SQL packages available to send email).

So how does this tie in with MySQL, it got me thinking about what a suitable Mantra would be for MySQL. I've come up with the following, based very much on Tom's Oracle Mantra, but it's by no means something set in stone, I'd really appreciate feedback on what people think.

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as small a stored procedure as you can.
o if you cannot, because a stored procedure cannot do it, try a UDF
o if you cannot, because a UDF isn't suitable take the code out of the database

I should say with regard to the last stage it's likely that if you have exhausted the first three stages then you're most likely no longer dealing with data exclusively and therefore the code may live more comfortably in your application layer.

MySQL 5.0 Stored Procedures : Part 4 (update)

2005-10-19T22:00:00.000Z

As long ago as last week I said that on the following day I'd post a new blog entry on Stored Procedures, yet again the day job has beaten me. It's now a week later and I still haven't found a spare 20-30 minutes to devote enough time to write the next instalment.

So as way of an update I'll just briefly go over the homework I set. I asked "what happens if you don't use INTO to pass the value into a variable", I gave a clue or at least mentioned that in Oracle trying to load a procedure with a select statement without an into clause results in a compile error (PLS-00428 to be exact).

But MySQL exhibits some interesting behaviour, I'm not exactly sure if it's by design but when issuing a 'normal' select statement in a procedure rather than an error MySQL returns the result set as if you have issued the command directly from the command line.

IE Problems

2005-10-19T21:50:00.000Z

It's seems as if this blog was having problems displaying correctly in IE, thanks to Roland for pointing that out. It was to do with the code samples and my use of <pre> to format them in a consistent way.

As a Mac user I tend to write this blog using Safari and when I view from work it's done via FireFox so the problems didn't show up for me. In future I'll test on IE just to make sure everythings OK, a new layout may be in order to address the problem going forward.

MySQL 5.0 Stored Procedures : Part 4

2005-10-12T11:09:00.000Z

OK, it's been nearly 2 weeks since the last item on stored procedures, so sorry to those who had been following along, I'm sure by this point you will have gone elsewhere for your Stored Procedure fix, but I'll pickup where I stopped with Part 3.

Today we will be looking at selecting records within a stored procedure. I'm sure if you looking at MySQL 5.0.13 you may have been using MySQL 4 for sometime or at least have some experince of writing SQL statements with other database technologies. Therefore I'll assume you know what one looks like and how to construct one using your database tables.

I'll be using a table called emps in this example..

mysql> desc emps;
+----------+---------------+------+-----+---------+
| Field    | Type          | Null | Key | Default |
+----------+---------------+------+-----+---------+
| emp_id   | int(11)       | NO   | PRI |         |
| emp_name | varchar(30)   | YES  |     | NULL    |
| dept_id  | int(11)       | YES  |     | NULL    |
| salary   | decimal(5,2)  | YES  |     | NULL    |
| bonus    | decimal(12,2) | YES  |     | NULL    |
+----------+---------------+------+-----+---------+
5 rows in set (1.72 sec)

In pervious procedure examples we used a parameter to pass in a name to include in our output string, what might be more usefully from an application persepctive is to be able to pass in an employee's ID number (in our example table this is the emp_id column) and then display the associated employee's name.

So how do we do that in a procedure, in a normal command line environment we would just issue a select statement as follows

mysql> select emp_name from emps where emp_id = 1;
+----------+
| emp_name |
+----------+
| Paul     |
+----------+
1 row in set (0.14 sec)

But in our procedure we want to use the name as part of our output string, to do this we need to use INTO in our select statement. INTO passes the value of the select into a variable, "Hold on" you say "what's all this variable buisness?". In general and simplistic terms a variable is a data store, a named area of memory we can use to hold values during the execution of a procedure

You can find more information on variables here...

http://mysql.gilfster.com/page.php?parent_id=1.1&page_id=1.1.5

To keep things short I'll let you look into variables if you need to, but I'll press on. So the first thing we need to do is create the variable, then we issue the select statement passing the output into the variable using INTO, then we reference the variable in much the same way as we referenced the parameter in previous examples.

mysql> delimiter //
mysql> create procedure 
    -> hello_employee(IN p_emp_id INT)
    -> begin
    -> declare p_name varchar(30);
    -> select emp_name into p_name from emps 
    -> where emp_id = p_emp_id;
    -> select concat('Hello ',p_name);
    -> end;
    -> //
Query OK, 0 rows affected (0.30 sec)

mysql> call hello_employee(1)//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Paul              |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Great, so we can now see how to select values from a table in our procedures.
Have a go at constructing your own select statements, any select statement that's valid on the command line is valid in a procedure.

For homework, what happens if you don't use INTO to pass the value into a variable. Those of you who have worked with another procedural language such as Oracle's PL/SQL might expect it to fail, in fact not even compile, what happens in MySQL? I'll discuss what happens and the implications of this tomorrow.

Wow...

2005-10-11T12:53:00.000Z

I got back from my vacation at 7:00 am UK time yesterday, I had a quick shower and it was straight back to work, things are busy here in the office as we just went live on a rather large ecommerce site, it's looking great so far no major problems but plenty of little things to keep us all busy over the first few weeks I'm sure.

All of this meant that I didn't have a chance to catch up with my personal email until I got home last night. I logged on expecting the odd email (I don't get many to that address) but I had loads. But of course the important one was the email from Arjen Lentz telling me I'd won an iPod Nano for my work on this blog and also over at www.mysqldevelopment.com.

I was both surprised and honored to be receiving the first weeks prize, it real was unexpected. When I started the short series on using stored procedures a few weeks a go there was a genuine desire on my part to impart at least a little knowledge to people reading this blog so they could go off and start trying to find those bugs, write blogs and win those iPods.

So thanks to MySQL, Arjen and everybody who's been following this blog or the site. There are still 7 (soon to be 6) iPods up for grabs and also those 3 Grand Prize of a pass to the 2006 MySQL Users Conference, so keep blogging and looking for those bugs.

http://dev.mysql.com/mysql_5_contest.html

Sorry

2005-10-10T13:59:00.000Z

I seem to spend every few blogs issuing an apology. This week I'm saying sorry for not keeping you guys posted with regards to my absence over the last week. I'd got off to a good start with the blog entries for getting started with Stored Procedures and then I realised I had other commitments coming up fast, first a client going live at the weekend and secondly a weeks holiday in Jamaica.

Well both were a great success, I left the go live in the very capable hands of my work colleagues, but took the holiday myself. The Hotel had an internet room but I resisted the temptation and went the whole week without using a computer.

So if you have been checking back for an update to the stored procedure items, I'll be back on those very soon.

MySQL 5.0 Stored Procedures : Part 3

2005-09-30T10:26:00.000Z

SO far we have looked at creating a simple procedure, then we added a couple of more lines to make it into a multiline procedure, but after all that our procedure is still a little useless. So let's look at how to make it a little more dynamic by passing in a value (or values) via a parameter.

Again I'll keep things simple, if you want to know more about parameters in programming in general pretty much any book on the subject of programming will have some sort of explanation so I'll leave you to do the research on that. But in essence parameters allow us to pass information in (and in the case of procedures, out) of our stored routines. "Hey, hold on" you all say.... "What's all this routine buisness". I suppose now is as good a time as any to introduce the word routine, so far we have been using the term "Stored Procedure" to mean program elements stored permanently in MySQL. But it's possible to use both functions and procedures, therefore it's good to have a term which covers both and make a distinction when talking explicitly about a procdure rather than routines in general.

...anyway back to parameters. So let's take our "hello world" example a stage further , lets say we wanted to say hello to anybody we chose. We could of course create a procedure for all our users, but a much better solution would be to pass in the name of the person we want to say hello to. I glossed over the () placed at the end of the procedure name in the first part, but it's this that we use to pass the parameters in and out of our routine. By default parameters are classed as IN parameters, this means the procedure will accept values into them, but we can also define them as OUT or even INOUT.

All we need to do is give the parameter a name and a datatype, the datatype can be any valid MySQL datatype that you would use for a column datatype in a table. Once it's defined we can then reference the parameter in our procedure using it's name.

mysql> delimiter //
mysql> create procedure 
    -> testParam(p_name varchar(30))
    -> begin
    ->    select concat('Hello ',p_name);
    -> end
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql> call testParam('Bob')//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Bob               |
+-------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

That's all there is to it. We can now just call the procedure using any name we like.

mysql> call testParam('Sue')//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Sue               |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

OK, now for your home work.... This page has information on the different types of parameters.

http://mysql.gilfster.com/page.php?parent_id=1.1&page_id=1.1.6

In the next blog entry I'll be looking at interacting with the database using SQL inside the procedure. I promise that this is going somewhere usefull :)

MySQL 5.0 Stored Procedures : Part 2

2005-09-29T09:30:00.000Z

In theb last blog entry I looked at how to create a very simple stored procedure. While it demonstrated how quick and easy it was to create a stored procedure it wasn't particuarly usefull.

So today I'll be showing you two additional things (spread over two entries), you can do to expand on yesterdays example. First up it's creating a multiline procedure. On first consideration this might not seem to be something you need to deal with, why can't you just keep adding lines to the procedure. The problem with that is that MySQL needs to firstly know where the procedure code starts and ends (in our first example it wasn't a problem because it was a single line of code) and also it needs to know when a particular command ends.

Take for example the MySQL command line tool, when you enter an SQL statement you need to add a semicolon ; to the end of the SQL command to tell MySQL that there is no more to enter and to begin processing the command. This is the same for stored procedures, you need to tell MySQL where the individual commands in the procedure end. However MySQL uses the same line delimiter for stored procedures as it does for normal command line operations, this means that when entering multiline procedures we need to change the default delimiter character. Doing this is trivial but it does mean you have to get yourself into "different delimiter mode" in that if your anything like me you will be constantly wondering why MySQL hasn't processed that select.

mysql> delimiter //

Personally I use the // as the new delimiter, your free to choose one you prefer but // seems to work well and not clash with MySQL. So how does this effect MySQL, where we would have used ; we now need to use the new delimiter.

mysql> select 'Test of Delimiter'//
+-------------------+
| Test of Delimiter |
+-------------------+
| Test of Delimiter |
+-------------------+
1 row in set (0.00 sec)

So now the question is how does this relate to stored procedures. Let's create a similar procedure to our last, this time rather than using a literal in the select we can first set a user variable and then select that. The second thing we need to do when creating multiline stored procedures is enclose the body of the procedure (the actual code but not the head) within a begin and an end block. So it looks like this...

mysql> create procedure testProc()
    -> begin
    -> set @a = 'HelloWorld';
    -> select @a;
    -> end;
    -> //
Query OK, 0 rows affected (0.28 sec)

You can see that the two lines are enclosed within the begin and end and that each one is terminated with the ; delimiter. Because we have changed the MySQL command line default delimiter to get MySQL to process the create procedure statement we need to enter the new delimiter after the procedure statement.

So to run the procedure we just call it as we did with our first example.

 
mysql> call testProc()//
+------------+
| @a         |
+------------+
| HelloWorld |
+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Great we have created a multiline procedure, but it still doesn't do a great deal.

MySQL 5.0 Stored Procedures : Getting Started

2005-09-27T11:34:00.000Z

In response to MySQL's call to arms (or should that be call to keyboards) with regard to MySQL 5.0 going to the RC stage, over the next few days I'll be giving you a brief introduction to Stored Procedures within MySQL. Hopefully this should give you enough of a heads up to start seriously testing this new aspect of MySQL.

Stage one of course is to download and install the lastest version of MySQL, that's currently 5.0.13 and is available here http://dev.mysql.com/downloads/mysql/5.0.html

OK, once you're all installed let's start up a MySQL command line session and select a database to develop against. Let's keep it really simple to start with, I won't go into too much detail during these tutorials, if you need more info I'll point you in the right direction at the end.

To start we will use the ubiquitous HelloWorld program to simply display some text on the MySQL command line. To do this all we need to do is call a select statment. To create a procedure we just need to type the keywords CREATE PROCEDURE give the procedure a name, add a set of brackets (more on those tomorrow) and then enter our select command.

mysql> create procedure helloword() 
     -> select 'Hello World';
Query OK, 0 rows affected (0.30 sec)

Great, we can tell by the message that the procedure was created (there were no errors or warnings reported). All we need to do now is run our procedure, this is just as simple as writing it in the first place. Just type CALL, then our procedure name followed by those brackets again.

mysql> call helloword();
+-------------+
| Hello World |
+-------------+
| Hello World |
+-------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Congratulations thats your first MySQL Stored Procedure. You may be saying to yourself that we haven't done anything you could have done straight from the command line (and you would be correct), but the thing with stored procedures is we can run the same thing again and again and agian.... and so on. I'll explain more about why thats good later but just to test the theory try and run the procedure again.

OK before you get a call from your MySQL DBA I better tell you how to remove your procedure. It's just as easy to get rid of one as it is to create one.

mysql> drop procedure helloword;
Query OK, 0 rows affected (0.20 sec)

Thats all there is to it. Next time we will look at creating a procedure which has more than one line of code and how to pass values into our procedure and adapt the output accordningly.

If you keen to learn more and don't want to wait for the next installment check out the following resources.

http://www.mysqldevelopment.com
http://dev.mysql.com/doc/mysql/en/stored-procedures.html
http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

MySQL on the Mac

2005-09-23T14:00:00.000Z

I read with interest Zack Urlocker's blog entry for today on his experience with a new Intel based Mac MySQL have recently got their hands on. I was looking for a new PC at the start of the year, maily because of some problems with my current windows box having problems communicating with various USB devices. I've always liked the look of Macs but the expense has always put me off, I'm not made of money after all.

But lucky for me (and apple), my looking for a new PC coincided with Steve Jobs announcment of the Mac Mini. Anyway I'm sure you're not interested in my shopping habits so on to the important part.

I purchased the Mac Mini to develop my website www.mysqldevelopment.com (shamless plug) on, that meant I needed some html writing software, an FTP client and of course a MySQL database. One of the great things which became apparent straight away was that the MySQL command line client is identical to the Windows version that I had been using before, this meant that there was zero downtime waiting for me to get up to speed with using MySQL on the Mac. The other great thing was that all the scripts I produced were immediately portable to the windows (or Linux) versions of MySQL.

To try and tie this in to the theme of this blog (a guide to MySQL for Oracle users) it's the same with Oracle's SQL*Plus, it's the same on any platform Oracle supports (which is pretty much everything).

So if the switch to Intel by apple has put the seed in your head, don't worry for a second about running MySQL, it works just the way you like it.

Here ends the Mac/MySQL plug.

5.0.13 Coming to a MySQL installation soon

2005-09-16T09:34:00.000Z

I've taken a great deal of interest in version 5 of MySQL, mainly because of my background in procedural extensions for other databases. It's been pretty exciting watching the development of version 5 and as somebody who has been keen to exploit the features I've been running as current a version as possible (it's currently at 5.0.12), this has meant at times a bi-weekly installation of the latest version.

One of the biggest requests from people on the stored procedures forum over on the MySQL website has been for information on using prepared statements, I'll gloss over the fact that for 90% of these situations prepared statements are not the way to go. MySQL Stored Procedures did infact support prepared statements for a while but this support was removed in version 5.0.9, this caused all manner of confusion and fist waving by the masses and resulted in a flood of angry forum entires asking where they had gone and why MySQL had pulled the plug....

...the great news is that the latest release notes for MySQL 5.0.13 tell us they are back. For the moment they will only be supported in Stored Procedures and not Functions or Triggers. The bad news is that 5.0.13 hasn't been released yet (at least not at the time of writing) so all you prepared statement fans need to hold on just a little longer.

Now all we need to do is to convince people that a monster stored procedure with 25 parameters to build all their SQL calls dynamically isn't the best use of stored procedures ;)

Sorry

2005-09-07T08:45:00.000Z

It seems the day job has finally caught up with me. I've been so busy that I haven't had time to update this blog in a few weeks. But please do keep checking back as we should be back to normal service soon.

DUAL

2005-08-23T14:49:00.000Z

Most of the time when you are using SQL you will be selecting against a table in your database. But there are the odd occassions where you don't actually want to select something from a table but still display information.

In Oracle this is handled by the DUAL table. DUAL is a single row all purpose table which allows you to issue a select statement on values which are not stored in an actual table. Take for example the sysdate function which returns the current date.


SQL> select sysdate from dual;

SYSDATE
---------
23-AUG-05

MySQL has full support for the DUAL table also.


mysql> select curdate() from dual;
+------------+
| curdate()  |
+------------+
| 2005-08-23 |
+------------+
1 row in set (0.00 sec)

However MySQL doesn't really need the DUAL table to return values. You can simply issue a select statement that doesn't have a from clause. It's perfectly legal and in fact the most common way to select values that don't belong in a table.


mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2005-08-23 |
+------------+
1 row in set (0.00 sec)

You can use this method to return information about the MySQL server or perform calculations. It's especially good for testing SQL you are going to use in the select section of an SQL statement.

Warning : It should be noted that you shouldn't issue anything other than select statements against the DUAL table, issuing INSERTS, UPDATES and worst of all DELETES against DUAL can have at least undesirable results.

Wizzy Colours

2005-08-23T14:47:00.000Z

You may have noticed in today's entry the SQL was shown in red and orange. I just wanted to point out if it wasn't obvious from now on any SQL or Code shown in red relates to Oracle and anything in Orange relaters to MySQL.

Outer Joins

2005-08-23T09:06:00.000Z

Depending on the amount of experience you have with databases in general it's likely that as an Oracle user you would use the Oracle sytle join syntax. From version 9i Oracle has supported the ANSI join syntax but many users and many examples of Oracle SQL on the web still use the Oracle syntax.

Essentially the Oracle syntax uses the where clause to specifiy the join condition simply by comparing a column in one table with the other.


SQL> select e.emp_id, d.description from emp e, dept d
  2  where e.dept_id = d.dept_id;

    EMP_ID DESCRIPTION
---------- ------------------------------
         1 HR
         2 HR
         3 IT
         4 ADMIN

The good news is that this also works in MySQL. MySQL fully supports ANSI join conditions but it also allows (in part) the joining of tables via the where clause.


mysql> select e.emp_name, d.description from emps e, dept d
    -> where e.dept_id = d.dept_id;
+----------+-------------+
| emp_name | description |
+----------+-------------+
| Paul     | IT          |
| John     | HR          |
| Alan     | IT          |
+----------+-------------+
3 rows in set (0.02 sec)

The bad news is that you can't use the Oracle outer join syntax. In a normal join, know as an inner join only rows where the join matches exactly are returned. This means that in our example above if an employee record does not have a dept_id (or at least one which isn't in the dept table) the record will not be displayed. If we want to display all the records in a table regardless of the fact there may not be a match we can use an outer join.

In Oracle to use an outer join we can simply use (+) against the table we are joining against, so for example.


SQL> select e.emp_id, d.description from emp e, dept d
  2  where e.dept_id = d.dept_id(+);

    EMP_ID DESCRIPTION
---------- ------------------------------
         1 HR
         2 HR
         3 IT
         4 ADMIN
         5

We can now see a new record that wasn't shown in the first select. In Oracle we can simply move the (+) to either column the join is used against to specify which table to perform the outer join on.


SQL> select e.emp_id, d.description from emp e, dept d
  2   where e.dept_id(+) = d.dept_id;

    EMP_ID DESCRIPTION
---------- ------------------------------
         1 HR
         2 HR
         3 IT
         4 ADMIN
           PR

As mentioned MySQL doesn't support (+) for outer joins. You therefore need to use the ANSI join syntax. We won't go into the full syntax here, but to use an outer join you need to use the left outer join keywords between the table definitions. The biggest difference is that the join condition becomes part of the from clause.


mysql> select e.emp_name, d.description 
       from emps e left outer join dept d 
       on e.dept_id = d.dept_id;

+----------+-------------+
| emp_name | description |
+----------+-------------+
| Barry    | NULL        |
| Paul     | IT          |
| John     | HR          |
| Alan     | IT          |
+----------+-------------+
4 rows in set (0.11 sec)

Using the ANSI standard you can change which table is treated as the outer table by changing the LEFT keyword to RIGHT.


select e.emp_name, d.description 
       from emps e right outer join dept d 
       on e.dept_id = d.dept_id;

+----------+-------------+
| emp_name | description |
+----------+-------------+
| Paul     | IT          |
| Alan     | IT          |
| John     | HR          |
+----------+-------------+
3 rows in set (0.00 sec)

Metadata (a very brief introduction)

2005-08-19T09:24:00.000Z

One of the great features of Oracle is the amount of Metadata available. For those that don't know metadata is essentially data about data, in Oracles case this is large number of tables which detail every aspect of the database objects.

In general there are 2 groups, the user tables and the all tables. The user tables give information about the current users database objects and the all tables which detail information for every user (schema).

The metadata is stored in tables, which can be accessed using SQL in the same way your normal tables are accessed.

For example lets say you want to see all of the tables owner by your user.


SQL> select table_name from user_tables where rownum < 10;

TABLE_NAME
------------------------------
ADMIN_CHART
ADMIN_CHART_ITEM
.
.
.
SUBCATEGORY
SYSTEM_PARAMETER
ZENDOR_STOCK_FEED

63 rows selected.

If we want to see all of the tables for all of the users we can use the all_tables view (the metadata tables are infact views on far more complex metadata tables).


SQL> select table_name from all_tables where rownum < 10;

TABLE_NAME
------------------------------
SEG$
CLU$
OBJ$
.
.
.
WISHLIST
WISHLIST_EVENT_NAME
WISHLIST_ITEM

831 rows selected.

So how does MySQL handle metadata? It depends on your version, most people will be running verison 4.1 or under, In these versions metadata is accessed using the show command. The show command consists of 22 different types of information about the database. The full list can be found here.
http://dev.mysql.com/doc/mysql/en/show.html

The biggest difference between show and Oracles approach is that the show command offers very limited selection requirements, it's pretty much all or nothing. In our examples above we limited our selection to show the table name only, but there are over 40 columns we can use. MySQL's show command does not allow use to pick and choose which of the columns we would like to see. There is support for a like keyword on some of the show commands but this is limited to say the table name or session variable name.

To see a list of tables in MySQL we can issue the SHOW TABLES command.


mysql> show tables;
+----------------+
| Tables_in_pers |
+----------------+
| clientdata     |
| dept           |
.
.
.
| test_letters   |
| test_order     |
| test_select    |
+----------------+
14 rows in set (0.14 sec)

From version 5.0 (not currently a full release) MySQL have introduced the information_schema. This replicates the functionality of the metadata tables of Oracle. MySQL users can now use SQL statements against these new tables to select particular columns, use where clauses and group by. A list of the information schema tables cab be found here.
http://dev.mysql.com/doc/mysql/en/information-schema-tables.html

So we can see a list of tables using the information_schema like so.


mysql> select table_name from information_schema.tables;
+---------------------------------------+
| table_name                            |
+---------------------------------------+
| SCHEMATA                              |
| TABLES                                |
| COLUMNS                               |
.
.
.
| test_text                             |
| ts_data                               |
+---------------------------------------+
63 rows in set (1.74 sec)

New Blog

2005-08-18T09:38:00.000Z

Roland Bouman has started a new blog available here.

http://rpbouman.blogspot.com/

Roland is a very active member of the MySQL community especailly in the areas of Stored Procedures, Triggers and Views. He is also written some great work with the new information schema available in version 5 of MySQL. I'll admit to some bias as Roland has worked with me on www.mysqldevelopment.com

You can see some of Rolands work here.
http://mysql.gilfster.com/page.php?parent_id=6&page_id=6.0.1

SOUNDEX

2005-08-16T13:59:00.000Z

If you have read any Oracle SQL Manuals or books you may have come across the SOUNDEX function. The function returns a value based on the phonetic representation of a string you supplied, this can then be used to compare with another word which sounds the same.

Soundex is available in MySQL also and operates in exactly the same way.


mysql> select * from sound_test
       where soundex(word) = soundex('to');
+------+
| word |
+------+
| to   |
| too  |
+------+
2 rows in set (0.00 sec)

However MySQL also offers a much easier way of using and remembering the functionality. All you need to do is use the words sounds like as the comparison to perform the same functionality.


mysql> select * from sound_test 
       where word sounds like 'to';
+------+
| word |
+------+
| to   |
| too  |
+------+
2 rows in set (0.05 sec)

Concat

2005-08-16T09:34:00.000Z

Often when dealing with text in an SQL statements it's desirable to join two or more strings together. For example we might want to join a persons title and surname to produce a suitable heading for an address block.

This joining of words is called concatenation and in Oracle is achieved using the pipe character twice like so ||

 
SQL> select title||' '||surname from person;

TITLE||''||SURNAME
-----------------------------------------
Mr Miller

In MySQL concatenation is achieved using a function called concat. All we need to do is call the concat function from the SQL statement passing in the columns or text we wish to concatenate.


mysql> select concat(title,' ',surname) from person
+---------------------------+
| concat(title,' ',surname) |
+---------------------------+
| Mr Miller                 |
+---------------------------+
1 row in set (0.02 sec)

However we can use an SQL Server Mode parameter to allow us to use the pipe character as we would in Oracle. SQL Server Modes are settings which change the behaviour of the server for an individual client. To use pipes for concatenation we can use the PIPES_AS_CONCAT option. This is set as follows.

 
set sql_mode = 'PIPES_AS_CONCAT';

So we can see this in action like so.


mysql> select title||' '||surname from person;
+---------------------+
| title||' '||surname |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.02 sec)

mysql> set sql_mode = 'PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql> select title||' '||surname from person;
+---------------------+
| title||' '||surname |
+---------------------+
| Mr Miller           |
+---------------------+
1 row in set (0.00 sec)

You may have noticed that the first call using the pipe characters does not cause an error, this is because the double pipe is used as a synonum for OR in MySQL. You should use PIPES_AS_CONCAT with caution for this reason.

CREATE TABLE LIKE

2005-08-15T12:29:00.000Z

As with Oracle MySQL offers the ability to create a table based on the definition of another. This can be done in two ways, one which will be familiar to Oracle users and another which is only available in MySQL.

The first way is to use a select statement in the create table command like so.


mysql> create table emps2 as select * from emps;
Query OK, 3 rows affected (0.20 sec)
Records: 3  Duplicates: 0  Warnings: 0

This creates a table with the same structure as the table we based the select statement on. We can add or remove columns from the table by explicitly specifying the column names in the select like so.


mysql> create table emps3 as select emp_id, emp_name from emps;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc emps3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id   | int(11)     | NO   |     | 0       |       |
| emp_name | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

The good thing about using this method is that it populates the table with the data from the select statement also. This makes it a great way to backup tables before running potentially damaging SQL against them. If you want the new table to be empty once it's created you can simply specify a where clause that returns no rows. Personally I use WHERE 1=2 as unless the rules of mathmatics change will always be false and hence return no rows.


mysql> create table emps4 as select emp_id, emp_name from emps where 1=2;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emps4;
Empty set (0.00 sec)

One downside to this approach on both MySQL and Oracle is that it doesn't make an exact replica of the table, for example any index against the table will not be receated for the new table.

MySQL however offers a solution to this with the LIKE keyword. This can be used to create a table based on the definition of another. This is done as follows.


mysql> create table emps5 like emps;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from emps5;
Empty set (0.00 sec)

The downside of this method is that the data isn't copied across in the way it is using an SQL statement in the create. But it's easy to do that using a command like follows.


mysql> insert into emps5 select * from emps;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

It's Official.....

2005-08-15T12:17:00.000Z

...seems some people are actually reading my Blog, it's difficult to know sometimes as there isn't the sort of statistics available for me to find out. However I have received a couple of emails about the blog over the last few days.

One thing I didn't realise is that to leave a comment you need a blogger account, I have changed the setting so you can now leave comments without having to sign up for an account.

I was also asked about a feed, this is available at

http://gilfster.blogspot.com/atom.xml

Sorry

2005-08-12T16:11:00.000Z

I've been extremely busy this week so the blog hasn't been updated in a few days. If you are following the blog we would love to hear from you.

I'll update the blog over the weekend with 2 new articles.

Temporary Tables

2005-08-10T12:08:00.000Z

In Oracle it's possible to create temporary tables. The name is a little missleading in that the table itself is not temporary but the data inside is. There are 2 basic types of temporary tables, those that clear the information in them apon a commit and those which preserve the rows when a commit takes place. However the data is always cleared from the table when the user ends their session.

The other feature of temporary tables in Oracle is that the data is available to the current session. Therefore 2 users can use the table without fear of causing problems in other sessions.

MySQL also supports temporary tables, but the big difference is that they are temporary. This means that they are only available during the current session, once the session has ended the table is dropped automatically. This means they do not exists as an object in the database and therefore need to be recreated each time they are to be used.

Like Oracle the data contained within them is available to the current session only, however unlike Oracle users can create temporary tables with the same name and a completely different structure.

Why switch from Oracle to MySQL at all.

2005-08-09T10:57:00.000Z

I got an email from Curt Monash, the president of Monash Information Services this morning. He raised a good point, why would you want to switch to MySQL from Oracle? You can check out his blog here http://www.dbms2.com/ which just a quick look will give you an indication on why he's asking.

This site isn't really meant to be a MySQL properganda page. I became interested in MySQL development mainly because I wanted to try and implement something along the lines of Oracle's PL/SQL Web tool kit and that took me along a path of looking at MySQL's new Version 5.0 and it's implementation of Stored Procedures.

But what I did find was that despite the fact Oracle and MySQL are supposed to support ANSI standard SQL there are enough differences to annoy your average Oracle developer. This blog is aimed at those people who have been using Oracle for some time but just want to get to know those little tricks they used with Oracle but in MySQL.

So back to the question of why you might want to switch. The first and most obvious answer is that for most purposes MySQL is free. You can download and install Oracle as a developer with out cost but to use it in a production environment is expensive. Secondly and somewhat related to the first point is that a large majority of basic web hosting packages provide support for MySQL. As a database Oracle and MySQL are pretty comparable, but it's some of the advance features that might make a large corporation go for Oracle, but do you need them. I've worked with Oracle for a number of years and used only a few of the more advanced features, you just don't need them most of the time.

The final thing to say is that the staff at MySQL are extremely responsive both in terms of contact with reagards to bugs and feature requests and also on a personall level. I have written a number of white papers and web content on MySQL 5.0, and without any prompting on my part I have had a lot of communication with MySQL staff on the subject. It seems they appreciate what the community at large is doing and see that as a central part of their future plans.

So in summary, it's free in most cases, it's well supported by web hosting companies, it has all the features you need for a small to medium database (if not a large one) and MySQL are open, friendly and a joy to deal with.

Case Sensitivity in MySQL

2005-08-05T11:46:00.000Z

Oracle is case sensitive when dealing with equality in select statements. So if we enter names with a capital in the first character position we need to specify the exact case to get a match.


SQL> select * from test_case where name  = 'dave';

no rows selected

SQL> select * from test_case where name  = 'Dave';

NAME
----------
Dave

However MySQL is case insensitive in the same scenario.


mysql> insert into test_case values ('Dave');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_case where name  = 'dave';
+------+
| name |
+------+
| Dave |
+------+
1 row in set (0.03 sec)

To force MySQL to check for an exact case we can use the binary keyword. This is simply placed before the comparison we want to evaluate.


mysql> select * from test_case where binary name  = 'dave';
Empty set (0.01 sec)

mysql> select * from test_case where binary name  = 'Dave';
+------+
| name |
+------+
| Dave |
+------+
1 row in set (0.00 sec)

However you can make a column case senstive by adding the binary keyword to the create table creation statement.


mysql> create table test_case (name varchar(30) binary);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test_case values ('Dave');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_case where name  = 'dave';
Empty set (0.02 sec)

Changing the MySQL command line tool

2005-08-04T09:26:00.000Z

There are a number of GUI's for use with MySQL but being from an Oracle background I use the command line. I think it was Ford Prefect in Hitch Hikers Guide to the Galaxy that said always know where your SQL*Plus is (I think it was Towel actually). This is also true of MySQL which every OS version you're using you should be able to use the MySQL command line in exactly the same way on each machine.

While the command line doesn't offer the same powerfull reporting abilities as SQL*Plus it is possible to change it's behaviour somewhat.

Take for example the SQL*Plus command


SET HEADING OFF

Which turns off column headings during select statement execution like so...


SQL> select 'hello' as test from dual;

TEST
-----
hello

SQL> set heading off
SQL> select 'hello' as test from dual;

hello

SQL>

This can be replicated in mysql using the -N switch when starting the command line tool.


C:\Documents and Settings\Andrew>mysql -u root -p -N
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 5.0.7-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use pers
Database changed
mysql> select * from emps;
+---+------+------+--------+
| 1 | Paul | 1    | 999.99 |
| 2 | John | 2    | 999.99 |
| 3 | Alan | 1    | 999.99 |
+---+------+------+--------+
3 rows in set (0.16 sec)
mysql>

Another common modification people make is to change the command line prompt in SQL*Plus this is done in MySQL using the --prompt switch or simply by using the word prompt on the MySQL command line.


mysql> prompt MyPrompt >
PROMPT set to 'MyPrompt >'
MyPrompt >

However MySQL offers a number of dynamic elements that you can use in the prompt. So for example lets say we wanted to see which database we are connected to we simply use \d when defining out prompt.


mysql> prompt \d >
PROMPT set to '\d >'
pers >

This means we don't need to keep changing the prompt when connecting to different databases, this sort of prompt in SQL*Plus is achieved using a login script, it then means using another script when changing connect, buy in MySQL this is all automatic.


pers >use information_schema
Database changed
information_schema >

There are a number of options that can be used, a full list can be found here...

http://dev.mysql.com/doc/mysql/en/mysql-commands.html

Sequences in MySQL

2005-08-02T09:03:00.000Z

In Oracle sequences are often used to maintain a unique series of numbers for an ID field. Sequences are independant of any table and can be used to keep a value unique across a number of tables. In fact they are not even restricted to use in tables.

MySQL doesn't currently support sequences. However it does have an auto increment value which can be applied to a primary key of a table. This is done during the table creation.


mysql> create table seq_test
      (id int primary key auto_increment
      ,name varchar(30));
Query OK, 0 rows affected (0.06 sec)

As the name suggests the value is assigned automatically, this is in contrast to Oracle where we have to call the nextval function of the sequence to return the value when we need it.

So if we perform an insert against the table but do not specifiy a value for the auto_increment column it's assigned automatically.


mysql> insert into seq_test (name) values ('Dave');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+----+------+
| id | name |
+----+------+
|  1 | Dave |
+----+------+
1 row in set (0.03 sec)

However we can override the assignment using a value of our choosing.


mysql> insert into seq_test (id,name) values (100,'John');
Query OK, 1 row affected (0.05 sec)

mysql> select * from seq_test;
+-----+------+
| id  | name |
+-----+------+
|   1 | Dave |
| 100 | John |
+-----+------+
2 rows in set (0.00 sec)

The sequence will then start from this new higher point.


mysql> insert into seq_test (name) values ('Penny');
Query OK, 1 row affected (0.02 sec)

mysql> select * from seq_test;
+-----+-------+
| id  | name  |
+-----+-------+
|   1 | Dave  |
| 100 | John  |
| 101 | Penny |
+-----+-------+
3 rows in set (0.00 sec)

We can insert records with values lower than the current highest value but these will be subject to the normal rules for a primary key (no duplicates) and will not effect the next number assigned automatically.

If all of the records are deleted from a table the sequence is not reset.


mysql> delete from seq_test;
Query OK, 3 rows affected (0.01 sec)

mysql> insert into seq_test (name) values ('garry');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+-----+-------+
| id  | name  |
+-----+-------+
| 103 | garry |
+-----+-------+
1 row in set (0.02 sec)

To do this you need to truncate the table like so.


mysql> truncate table seq_test;
Query OK, 1 row affected (0.03 sec)

mysql> insert into seq_test (name) values ('garry');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+----+-------+
| id | name  |
+----+-------+
|  1 | garry |
+----+-------+
1 row in set (0.00 sec)

Alternatively you can reset the sequence using an alter table command.

 
mysql> alter table seq_test auto_increment = 100;
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

This method could also be used to assign a higher number to start the sequence rather than starting with 1 by calling the alter table straight after the table creation.

So in summary auto_increment offers a great way of assigning a unique value automatically to a table. However what it doesn't allow when compared with an Oracle Sequence is different increment values, ability to use across a number of tables and the option to assign in a reverse order.

Transactions in MySQL

2005-08-01T13:36:00.000Z

In Oracle transactions are "turned on" by default, in other words DML statements (update, insert, delete) need to be commited or rolledback explicitly. It's possible to set your client, for example SQL*Plus, to auto commit for you but many Oracle developers (Myself included) are simply used to having to commit and rollback (and many times appreciate the fact when we forget to include a where clause on that delete statements).

One of the great features of MySQL is it's ability to use different storage engines. The defualt storage engine is InnoDB which supports transactions, however the command line client and Query Browser will commit and DML automatically by default.

If you need to use transactions you can do one of the following,

explictily start a transaction using the following command,


mysql > START TRANSACTION;

This will then continue the transaction until a commit or rollback takes place. To start a new transaction you need to issue the start transaction statment again.

If you would rather work in an environment like SQL*Plus where each and every statement needs to commited or rolled back you can use the SET command like so.


mysql > SET AUTOCOMMIT = 0;

A few points to note, when using transactions DDL does not perform a commit in the back ground, this is the case in Oracle. However issuing a lock tables command will issue a commit, even if the table with the changes is not part of the lock tables command.

Connecting to remote MySQL installations

2005-07-29T10:19:00.000Z

A question that seems to have poped up a number of time this week over on the MySQL forums is how to connect to MySQL installations on a remote machine, be it on the same network or across the internet.

If you have used Oracle the way to do this is to set up an entry in your tnsnames.ora file, for those that don't know this is a configuration file containing all of the information Oracle needs to connect to database either locally or remotely.

If we are using the SQL*Plus command line client for Oracle we can connect to another server using the following command.


SQLPlus user/password@database

The database portion is in fact a reference to an entry in the tnsnames.ora file which contains the machine name or IP address of the machine and database SID we want to connect to.

In MySQL we don't need to use an additional file to connect to another instance, we simnply add a -h parameter to our call to mysql.


mysql -h hostname -u root -p

This then connects us to the remote database. The -h can be a machine name, webserver or IP address.

Using IFNULL ( )

2005-07-28T15:31:00.000Z

NVL() is a great function in Oracle to convert NULL columns into a more useable format, for example it's often the case on reports to convert NULL numeric columns to 0 for better readability.

In MySQL you can use IFNULL() in the same way.


mysql> select emp_id, salary, bonus from salary;
+--------+---------+-------+
| emp_id | salary  | bonus |
+--------+---------+-------+
|      1 | 2000.00 |  NULL |
+--------+---------+-------+
1 row in set (0.00 sec)

mysql> select emp_id, salary, ifnull(bonus,0) from salary;
+--------+---------+-----------------+
| emp_id | salary  | ifnull(bonus,0) |
+--------+---------+-----------------+
|      1 | 2000.00 |            0.00 |
+--------+---------+-----------------+
1 row in set (0.00 sec)

Multi Row inserts

2005-07-28T09:37:00.000Z

One of the features I like best in MySQL is the ability to insert mulitple rows of data using a single insert statement. In Oracle you need to write an insert statement for each row including the list of columns each time, but in MySQL you only need to list the column names you need once and then list the values clause multiple times.

So in Oracle we have


insert into emps (emp_id, name, salary) 
values (1,'Dave',2000.00);

insert into emps (emp_id, name, salary) 
values (2,'John',2100.00);

insert into emps (emp_id, name, salary) 
values (3,'Barry',1900.00);

But in MySQL this can be done like so.


insert into emps (emp_id, name, salary)
values (1,'Dave',2000.00),(2,'John',2100.00),(3,'Barry',1900.00)

It's shorter, tidy and more logical.

IF in SQL statements

2005-07-26T12:46:00.000Z

In older versions of Oracle you can use DECODE to implement conditional logic within SQL statements, from 8i you can also use CASE. In MySQL the decode functionality is replicated using IF.

IF is infact a function call rather than a part of the SQL syntax but it works pretty well in most cases. For example...


mysql> select if(emp_id=1,'X','Y') as test, emp_id from emps;
+------+--------+
| test | emp_id |
+------+--------+
| X    |      1 |
| Y    |      2 |
| Y    |      3 |
+------+--------+
3 rows in set (0.06 sec)

group_concat

2005-07-25T13:12:00.000Z

One great function I came across a few days ago is group_concat. Many times I've been asked to produce a query which pivots records in a table to take data which is presented in column format into a single row. In Oracle it can get pretty complicated either using a number of subqueries (which presents it's own problems) or analytical functions (which I'll admit I haven't used anywhere near enough).

Sitting on my desk is a timesheet, lets say the boss wants the reporting flexibility of adding in data one day at a time, fine we just create a table like so...


mysql> create table ts_data(emp_id int, week_no int, work_date date, hours numeric(4,2));
Query OK, 0 rows affected (0.11 sec)

I'll insert some data so we get the following data set.


mysql> select * from ts_data;
+--------+---------+------------+-------+
| emp_id | week_no | work_date  | hours |
+--------+---------+------------+-------+
|      1 |       1 | 2005-07-18 |  8.00 |
|      1 |       1 | 2005-07-19 |  8.00 |
|      1 |       1 | 2005-07-20 |  8.00 |
|      1 |       1 | 2005-07-21 |  8.00 |
|      1 |       1 | 2005-07-22 |  8.00 |
|      1 |       2 | 2005-07-23 |  8.00 |
|      1 |       2 | 2005-07-24 |  8.00 |
+--------+---------+------------+-------+
7 rows in set (0.00 sec)

So let's say the boss wants to see one row for each week with all of the hours worked side by side. That's where group_concat comes in. All we need to do is set up our grouping rules, in this case group by emp_id and week_no then specify the data we want to concatenate on a single row.


mysql> select emp_id, week_no, group_concat(work_date,' - ', hours) from ts_data group by emp_id, week_no;
+--------+---------+-------------------------------------------------------------------------------------------+
| emp_id | week_no | group_concat(work_date,' - ', hours)                                                      |
+--------+---------+-------------------------------------------------------------------------------------------+
|      1 |       1 | 2005-07-18 - 8.00,2005-07-19 - 8.00,2005-07-20 - 8.00,2005-07-21 - 8.00,2005-07-22 - 8.00 |
|      1 |       2 | 2005-07-23 - 8.00,2005-07-24 - 8.00                                                       |
+--------+---------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

An Oracle Developers Journey

2005-07-25T13:05:00.000Z

If you have visited this blog before you may notice I have deleted all of the previous posts (I'd be suprised if anybody was looking however as I was pretty laxed about updating it).

This year I have been working with MySQL, building my site www.mysqldevelopment.com and working with various people in the MySQL community both on the MySQ forums and over at the Quest MySQL forums. I'm more of an Oracle developer, having worked with it for a number of years prior to looking into MySQL in a bit more detail late last year. I've been looking for something to do with the blog since I started it, it became pretty clear my private life is nowhere near interesting enough to maintain a blog on a daily basis.

So as from today I'm going to be writing about MySQL, and in particular little tips and tricks I have picked up recently that seem to make MySQL more usable and easy to live with.

MySQL 5.0 Beta Challenge

2005-06-16T08:36:00.000Z

You may have stumbled upon this blog via my site http://www.mysqldevelopment.com. You will therefore be aware of my work on the new features of MySQL 5.0.

AB the owners of MySQL have just announced a MySQL 5.0 Beta challenge to try and promote and also get people testing the latest version of MySQL. I've been in contact with Edwin De Souza over at AB for a few weeks with regard to pushing the new features and I'll be adding more content to my site soon.

But they need more help, they need you guys to test, write content and get involved with the latest release. Check out the associated press release

http://dev.mysql.com/tech-resources/articles/evaluating-mysql-5.0.html

If you don't have the ability to install MySQL on one of your servers check out a new site which gives you free access to MySQL databases http://www.db4free.net