Joining the 10g time model to the traditional wait interface views and taking advantage of the wait_class data is something most of us have probably done. Here's my standard queries that do that thing....
Anyone who has used an automated unit testing framework such as Junit knows just how life-changing an automated test suite can be. Once you've experienced validating that recent changes have not broken old code, or discovering subtle bugs via junit that would otherwise have remained undetected , you naturally want to have this capability in all your programming environments.
Guisseppe Maxia has written a few stored procedure snippets to assist with automated unit testing of MySQL routines. Unfortunately, the MySQL stored procedure language itself does not have the necessary abilities to fully implement the sort of unit testing we would like. In particular, the inability for a stored procedure to capture the result sets generated by another stored procedure prevents a stored procedure from fully unit testing another.
So I decided that - for me - Junit offered the best solution. I created an extension to the Junit class that contains some assertions useful when unit testing stored procedure and extended my PlainSql JDBC wrapper classes to allow a stored procedure to return a single object that contains all its result sets and the values of OUT or INOUT parameters. This object can be made the target of the various assertions.
If you're not familiar with Java and/or, you might feel that this solution is not for you. However, the amount of java programming you need to do is very minimal and GUI environments such as Eclipse make it very easy to set up. The rest of this article is available here; it contains instructions, my Java classes and examples for setting up Junit test cases for MySQL stored procedures.
Ruby's getting an incredible amount of attention recently, largely as the result of Ruby on Rails. I've played a little with Ruby on Rails and it certainly is the easiest way I've seen so far to develop web interfaces to a back-end database.
At the same time, I've been shifting from perl to Java as my language of choice for any serious database utility development. But I still feel the need for something dynamic and hyper-productive when I'm writing something one-off or for my own use. I've been playing with Python, but if Ruby has the upper ground as a web platform then maybe I should try Ruby.
So seeing as how I've just finished the MySQL stored procedure book, first thing is to see if I can use Ruby for MySQL stored procedures.
Database - and MySQL - support for Ruby is kind of all over the place. There's a DBI option (similar to perl) which provides a consistent interface and there's also native drivers. For MySQL there are pure-ruby native drivers and drivers written in C. Since the DBI is based on the native driver, I thought I'd try the native driver first. The pure-ruby driver gave me some problems so I started with the C driver on Linux (RHAS4).
The main trick with stored procedures is that they might return multiple result sets. OUT or INOUT parameters can be an issue too, but you can always work around that using session variables.
If you try to call a stored procedure that returns a result set, you'll at first get a "procedure foo() can't return a result set in the given context error". This is because the CLIENT_MULTI_RESULTS flag is not set by default when the connection is created. Luckily we can set that in our own code:
dbh=Mysql.initThe "query" method returns a result set as soon as it is called, but I found it easier to retrieve each result set manually, so i set the query_with_result attribute to false:
dbh.real_connect("127.0.0.1", "root", "secret", "prod",3306,nil,Mysql::CLIENT_MULTI_RESULTS)
more_results methods are implemented in the Ruby MySql driver, but there's some weird things about the more_results C API call that causes problems in python and PHP. In Ruby, the
more_results call returns true whether or not there is an additional result. The only reliable way I found to determine if there is another result set is to try and grab the results and bail out if an exception fires (the exception doesn't generate an error code, btw);
rescue Mysql::Error => e
We can then call more_results at the end of each rowset loop. So here's a method that dumps all the result sets from a stored procedure call as XML using this approach (I'm know the Ruby is probably crap, it's like my 3rd Ruby program):
rescue Mysql::Error => e
printf("\t<resultset id=%d columns=%s>\n",result_no,colcount)
rs.each do |row|
printf "\t\t<row no=%d>\n",rowno
rs.fetch_fields.each_with_index do |col,i|
Whew! No need to hack into the C code. So you can use MySQL stored procedures in Ruby with the existing native C driver. The problem is that the C driver is not yet available as a binary on Windows yet and trying to compile it turns out to be beyond my old brain (and yes, I used minGW and all the other "right" things). Hopefully a copy of the MySQL binary driver it will be available in the one-click installer Ruby installer eventually.
The above code doesn't work using the pure-Ruby driver on windows by the way - there's an "out of sequence" error when trying to execute the stored proc. I might hack around on that later (at the moment I'm 35,000 ft with 15 minutes of battery left on the way to the MySQL UC). For now if you want to use MySQL stored procedures in a ruby program on windows I can't help.
Note that ruby seems to hit a bug that causes MySQL to go away if there are two calls to the same stored proc in the same session and the stored proc is created using server-side prepared statements. Fixed soon hopefully, but for now if you get a "MySQL server has gone away error" you might be hitting the same problem. Wez posted on this problem here.
I suppose the end of this investigation will probably be to see if there's any way to use stored procedure calls to maintain a Rails AcitveRecord object. Not that I think you'd necessarily want to, but it would probably be a good learning exercise.
Lot's of Java developers want to avoid writing SQL or even avoid directly accessing with a relational data store. I, on the other hand, want to use SQL in my Java programs (which are mostly database utilities) but I want it to be as easy to use SQL in Java as it is in Python, Perl or PHP.
Recently, I decided to use Java rather than Perl as my language of choice for database utilities. Perl is quick to write, but tends to be easier to write than to read. I played with Python for a while, but in the end decided that by using Java I would have an easier time distributing by stuff and it could more easily be re-used inside of my company, which has Java developers but not many Perl people. So developed a set of JDBC wrappers that would let me use SQL easily within my utilities.
My Design :
This was a bit of a learning experience - had not programmed in Java for quite a while and I tried to follow best Java practice such as creating Junit tests, JavaDoc and ant builds. If anyone's interested, here's the latest versions:
I don't really expect anyone else to use this, posting it was I guess one of the ways to enforce a bit of discipline on myself as regards quality. However, it will be embedded in most of my database utilities so I guess it will see some use in our internal benchmarking routines and the like.
I finally got around to working with the 5.1 scheduler. I wanted to have a simple but non-trivial example, and when I saw Brian Akers post on the new processlist table, I thought of a useful little application: I would submit an event that would summarize the users and their statuses at regular intervals so I could track user trends.
First off, I needed to enable the scheduler by adding the following line to my configuration file:
Now the scheduler is ready for action. So I created a table to hold my process list details:
CREATE TABLE processhistory (h_timestamp DATETIME,
And then the DML to create an event:
CREATE EVENT evt_process_history
ON SCHEDULE EVERY 2 MINUTE
INSERT INTO processhistory (h_timestamp,processcount,
SELECT NOW() AS h_timestamp,COUNT(*) AS processcount,
SUM(active) AS activecount ,
SUM(locked) AS lockedcount
FROM (SELECT CASE command WHEN 'Sleep' THEN 0 ELSE 1
END AS active ,
CASE state WHEN 'Locked' THEN 1 ELSE 0
END AS locked
FROM information_schema.`PROCESSLIST` P) Q;
CREATE EVENT evt_process_history
Every two minutes, the event summarizes the status of the sessions currently connected and stores them to the table. I could use various tools to analze this data, but for convenience I used Excel with the ODBC driver to create a chart of activity:
Cool! Now I can keep track of active sessions over time, which could be useful. On the test database, there is a little ruby program that locks up a table needed by my java TP simulation, so we see those spikes of lock activity. I'm hoping that MySQL expose the SHOW STATUS command as a table as well, since we can't get at the contents of SHOW STATUS from within the stored program language.
Last week my laptop crashed and while installing the new one I decided to update my perl versions. I mainly use the DBD::mysql and DBD::Oracle modules and although I'm confortable building them on Linux/Unix, like most people I use the Activestate binaries on windows.
However it turns out that Oracle licensing changes now prevent Activestate from distributing an Oracle binary, so I was forced to build them from source. It wasn't easy, but now both the Oracle and MySQL modules are working. Here's the procedure in case it helps anyone.
Firstly, you probably want to move to the pxperl windows binaries. Pxperl support the familiar CPAN system for updates. Get Pxperl at www.pxperl.com. The installation should be straight forward.
I installed into c:\tools\pxperl
You'll need a C compiler capable of building native windows binaries. I used the MinGW system. You can't use cygwin, although I believe that Cygwin might be capable of installing MinGW. Anyway, I got the MinGW system from http://www.mingw.org/. I couldn't use the auto-installer for firewall reasons, so I did a manual download and install.
Firstly, I unpacked the following .gz files into c:\tools\mingw:
You probably don't need all of these, and of course the version numbers might be different by the time you read this.
Then I ran the following two executables
...installing both into c:\tools\msys. You must make sure you provide the correct location for MinGW when prompted. Finally, MinGW installs it's own version of perl, so I removed that as well as the make.exe which is inferior.
I added both the bin directories to my path, which now starts something like this:
c:\mysql;c:\tools\msys\1.0\bin; c:\tools\mingw\bin; C:\tools\PXPerl\parrot\bin; C:\tools\PXPerl\bin
Now you can go into cpan (just type CPAN at the command line) and run "Install DBI". That worked OK for me.
Then I ran "install DBD::Oracle". That failed. I can't remember the exact error, but it turns out that a trailing backslash in the include directory for the DBI doesn't work on Windows. To fix that, run "configure_pxperl" and add an include for that directory in the "Include Directories" section. For me, the directory was
/tools/PXPerl/site/lib/auto/DBI , since I installed pxperl into the tools directory.
For some reason I thought this would be the easy part. But it actually was really difficult.
In the end, it turns out you need to create your own version of mysqlclient.lib and manually link to that. Check out MySQL Bugs: #8906, for some more details. Here's the steps that worked for me:
dlltool --input-def include/libmySQL.def --dllname lib/libmySQL.dll --output-lib lib/libmysqlclient2.a -k
And - voila! - you should be OK. The only think you might need to do now is add the top level MySQL directory to your path. DBD-Mysql wants to find "lib/mysql.dll" so you need to add the directory above that to your path. I moved all the libraries to c:\mysql\lib and include files to c:\mysql\include, so I added to my path like this:
Seems to be working OK now for both Oracle and MySQL. Much more difficult than installing the Activestate binaries but at least now that I'm working from source I can potentially fix bugs although having done it on Linux it's not for the faint hearted (or the incompentent in C++!)
Hopefully pxperl will gain in popularity and as it matures things will work as easily as on Linux. That would be great.