This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.

MySQL Query Browser

The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and "Create Stored Routite..." This shall help you do easily modify, edit and create procedures.

Command Line

Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.

• In your ~/.bashrc add the following...
  

  export EDITOR=vim VISUAl=vim
  

• Create / Edit ~/.vimrc file, and add the following...
  

  set noai
  set nonu
  

• Log into the MySQL CLI, and set the following command
  

  delimiter //

…

If you count the lines of code in the MySQL Cluster (NDB) test suite (mysql-5.1/storage/ndb/test - and exclude the old ODBC stuff) you come up with about 104000 lines of code. This is in contrast to the approximate other 350,000 lines of code for the NDB engine (excluding the handler, which is an additional 12,000 lines - this isn’t tested much by the NDB test suite… mysql-test-run.pl is meant to take care of a lot of that).

If you go and check the MyISAM tree, it’s only 40545 lines of code - for the entire engine. That’s right, the MySQL Cluster test suite is about 2.5 times the size of MyISAM.

If you look at mysql-test-run.pl tests, which are just lists of SQL commands with static data, it comes up at 250,000 lines (that excludes result files). The NDB tests do things programmatically - so can generate large amounts of data and different loads quite easily.

The architecture of the NDB tests (commonly referred to …

This week I talk about the MySQL Query Cache.

Direct play the podcast here:
http://tinyurl.com/22ju9q

Subscribe to the podcast by clicking:
http://phobos.apple.com/WebObjects/MZStore.woa/wa/viewPodcast?id=206806301

You can Direct download all the oursql podcasts at:
http://technocation.org/podcasts/oursql/

Show notes:

Listener Feedback:
http://dev.mysql.com/doc/refman/4.1/en/mysql-commands.html

News:
Daylight Savings Time and how to check your system:
…

Thanks to Guilhem Bichot, we are adding some support for Query Cache to Prepared Statements. Should become public in one of the future 5.1 versions. It took only 3 years of procrastination and a few days of diligence to do it.

A couple of buddies pointed me toward Sys-Con Media's Top 150 Information Technology Heroes, and I have to admit it was fun (once you click past the obstructing ad anyway). While my first reaction was to want to point out omissions (see below), what's not to like about a list that mixes Dennis Ritchie, Luca Cardelli, Edsger Dijkstra, and Charles Babbage?

However, you have to get a bit cranky with the fact that Jim Gray was left out. (His omission is even more surprising given recent events should have kept him on the authors' mind.) Jim's work on transactions was foundational for both relational databases and transaction processing (TP) monitors. Moreover, the whole web/Java application server category (WebLogic, WebSphere, .NET, ...) is really just a TP monitor rearchitected for web processing. (By the way, the reason it was not called a web/Java TP monitor in the first …

As many of you have already discovered, Connector/Net 5.0.5 does not work correctly with Tools for Visual Studio.  The symptom is that when the plugin tries to enumerate all the tables in the database, it reports that it gets back an improper data type.  The cause of this problem is somewhat involved so I thought I would blog on it.

Since the early days of the connector, binary data was not always handled correctly.  Specifically, columns that were reported to the connector as CHAR and VARCHAR were returned as System.String.  This is, of course, not always correct.  The case that prompted the changes was a bug report that SELECT _binary "foo" would return foo as a System.String instead of System.Byte[] as it should.

The fix is to always respect the binary markers that the server sends, which 5.0.5 does.  The problem is that MySQL incorrectly reports some columns as binary such as the name column from …

While reading "Crossing the Chasm" by Jeffrey Moore, I discovered that I never actually questioned the features we added in the recent releases.
I came to MySQL with a background similar to Peter Zaitsev's, we worked together in Spylog. MySQL was used there as storage for an in-house scale-out application that served TBytes of data per month and hundreds of thousands of requests per day.
The thing is, almost none of the features implemented in the recent years I would use for that sort of appliance.
Top things on my list would be:
- integrated inverse indexes/full-text search, both InnoDB and MyISAM. And these indexes would need to scale well to give low response times for at least 500 MB tables.
- scaling to multi-core CPUs and high amount of memory (64GB and more)
- better and faster networking that would not spawn off a thread for every connection, so that the server can actually handle a connection per …

a mere seventeen months since the last release, we finally managed to release version 3.51.14 of connector/odbc. while much of the development energy around connector/odbc is going into the rewrite from scratch, there?s a few of us that have been working on getting the old workhorse of 3.51 back into shape and releasable.

kent from the build team has constructed a new build tool that lets him crank out builds (and test them) on too many platforms, bogdan and tonci (and others) from the support team have really dug in and solved some annoying problems our customers have run across, and georg and i have been getting into the code and cleaning up some of the other issues. there were also a number of fixes from peter …

Although a few posts have already been made about this, I thought I’d post a nice quick article by Peter Gulutzan and Dmitri Lenev on what to do with the upcoming daylight savings time changes and MySQL. This is in case you aren’t using the OS for your time zone info. You can find this out by issuing the following query:


SELECT @@global.time_zone;


If you get back ?SYSTEM?, then the MySQL server is looking to the OS for timezone data, which is the default setting.

DAYLIGHT SAVING TIME CHANGES
By Peter Gulutzan and Dmitri Lenev

This year many American states and Canadian provinces will switch to daylight saving time on March 11 (the second Sunday of March) instead of switching on the first Sunday of April as happened during previous years.

If you populated the mysql.time_zone_transition according to the instructions in the MySQL Reference Manual, “ …

Long time no blogging! I hope PlanetMySQL finally picks up my posts again, I really miss the additional audience

We recently received a request from the OpenISR project about adding the Eclipse public license to our FLOSS License Exception. As it turns out, it is almost identical to the Common Public License (CPL), which was already included in the exception. So the decision was fairly easy - version 0.6 of the FLOSS License Exception now also covers the Eclipse public license.

What is the FLOSS License exception all about anyway? Our intent:

We want specified Free/Libre and Open Source Software applications to be able to …