When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the information_schema database, but it’s scattered through several different tables.

Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.

In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.

CREATE DATABASE IF NOT EXISTS dba;
USE dba;

DROP PROCEDURE IF EXISTS `dba`.`get_objects`;
DELIMITER $$
CREATE …

In Brooks Johnson's blog posting, Database Science: Are MySQL stored procedures slow?, he mentioned how much slower MySQL's stored procedures are and then compared it with a small piece of .Net code.

Using Perl, a stored procedure which counts to the same value is obviously not going to be as fast as bytecode languages with JIT compilers but it is a lot faster than MySQL's native SQL stored procedures. These perl stored procedures are able to perform dynamic SQL using the familiar DBD::mysql driver without any risk of self-deadlock.

Of course, you can also write stored procedures in Java for many databases but I haven't yet written the neccessary Type 2 JDBC driver to perform a in-thread connection back into the database server to be able to do sophisticated work with MySQL.

 …

Less than 48 hours after starting to scratch this itch, I have table functions working in my WL820 repository on Launchpad.It is pretty nifty:mysql> INSTALL PLUGIN Deep_Thought SONAME 'psm_example.so';Query OK, 0 rows affected (0.10 sec)mysql> CREATE FUNCTION test.FooAnswer() -> RETURNS TABLE(answer TEXT) -> NO SQL LANGUAGE Deep_Thought EXTERNAL NAME 'compute';Query OK, 0 rows affected (

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides …

I have merged the code from the mysql-5.1 launchpad repository which should put this repository up to the recent 5.1.28-rc version. You can examine the source repository here.On a related note, had a brief email exchange with Eric Herman and so I hope that there will be time for him to do some work to complete the support for Java stored routines.

Yesterday, I became a Google employee.I know that there has been quite a hiatus in my MySQL related activities but it wasn't all without good reason. The first proper holiday I have had in a few years.Tomorrow afternoon, I hope to spend some time with Mark Callaghan ... It would be a nice break from the new-employee orientation and tutorials and hopefully a chance to meet more of my new

I have updated the Forge page for the External Language Stored Procedures, adding a PDF for the slides used in the presentation and a link to a video of the presentation for those people who missed it. It was quite unfortunate that at the Conference, there were two presentations occurring at the same time which discussed the topic of UDF/Stored Procedures so I am sure that there are people who

That week at the conference was a busy week for me - as busy as any developer meeting I have had with MySQL in recent years. I had a great time seeing old faces again and it was much like old times talking, chatting and coding with them. In particular, I spent much of my time with Patrick Galbraith, Eric Herman and Arjen Lentz.Great progress was made: I worked with Patrick getting my ancient

The MySQL User's conference will be held in less than a month from now!!!

This year there is quite a good number of sessions on adding your own functions and procedures, such as:

• 
  
• Advanced Stored Procedures
  
• A Tour of External Language Stored Procedures for MySQL
  
• Code Generators for MySQL Plugins and User Defined Functions (UDFs)
  
• …

I found myself thinking this evening about how someone could set about writing aggregate functions using Perl and on an idle Google search, came across this webpage entitled User-defined Aggregate Functions in DB2 Universal Database.I wondered if a similar technique could be applied for our implementation of External Language Stored Procedures for MySQL. It turns out that the answer is: