Rails MySQL Stored Procedure Fun

Hi All.

I have a question about calling MySQL stored procedures that is a little
different from the usual.

I know stored procedures are not really the ‘Rails way’ but in this case
there is no other way to accomplish the same task and I have used them
successfully.

Background:
I am using a query to produce a real-time status of items flowing
through a factory.
The query must trawl through thousands of records working out the first
time an item hit a certain stage, this data is then further rolled up to
produce a count of items processed at each stage each hour. The results
are then generated as a cross-tab (again using a MySQL stored procedure)
and displayed in a web-page. This data cannot be precalculated as
scanning an item at a stage that has been scanned there previously
should not register in the count.

This query is quite simple to write in MySQL 5 (running on Ubuntu) as a
stored procedure and runs very quickly. I have designed the results page
in Rails (using model calls to find_by_sql to get results) and tested
successfully on Webrick on a windows box. I previously had a problem
with returning data from a MySQL stored procedure (due to Activerecord’s
MySQL connection adapter not passing the CLIENT_MULTI_STATEMENTS and
CLIENT_MULTI_RESULTS flags in the connection options) but overcame this
by hacking the ActiveRecord MySQL connection adapter and adding the
required options to be passed to the mysql_real_connect call.

I ported the application to a production Apache / Ubuntu server which
runs Rails MySQL sites without problems (in fact in the past it was able
to access MySQL stored procedures as well via the connection adapter
hack(I think!))

Unfortunately the app is unable to retrieve data from MySQL’s stored
procedures. I get the ‘cannot return a result set in the given context’
error message which I initially had before I hacked the MySQL connection
adapter on windows. I have checked the connection adapter on the Ubuntu
box and it still has the hack in (I ensured that the adapter I was
editing was the latest gem on the box (1.13.2). I have tried running the
app in Webrick on Ubuntu and get the same results. I have even written a
small ruby script that uses ActiveRecord to call the stored procedure
and get the same error message.

Has anyone any idea as to why the app behaves differently on Linux and
Windows. Both have similar Rails versions, Ruby (1.8.2) and Gem
installs.

Is it possible one or the other is not using the C MySQL shared object
vs. the ruby one (or something at a lower level that the connection
adapter which is ignoring the extra connection options)? Where could I
look to see if I can differentiate between the two installs to see if
there is something mismatched? The fact that one system is win32 and the
other *nix doesnâ??t help.

Thanks and Regards,

Paulie

On May 5, 2006, at 6:42 AM, Paulie wrote:

other *nix doesn?t help.
The pure-Ruby MySQL driver likely doesn’t support for the feature you
rely on.

$ ./script/console
Loading development environment.

require ‘mysql’
=> true

Native bindings:

Mysql.client_info
=> “5.0.19”

Pure-Ruby driver bundled with Rails:

Mysql.client_info
=> “4.0-ruby-0.2.5”

If you consider this feature widely useful, please consider
contributing a patch to Active Record (http://dev.rubyonrails.org)

Best,
jeremy_______________________________________________
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Jeremy K. wrote:

On May 5, 2006, at 6:42 AM, Paulie wrote:

other *nix doesn?t help.
The pure-Ruby MySQL driver likely doesn’t support for the feature you
rely on.

$ ./script/console
Loading development environment.

require ‘mysql’
=> true

Native bindings:

Mysql.client_info
=> “5.0.19”

Pure-Ruby driver bundled with Rails:

Mysql.client_info
=> “4.0-ruby-0.2.5”

If you consider this feature widely useful, please consider
contributing a patch to Active Record (http://dev.rubyonrails.org)

Best,
jeremy_______________________________________________
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Hi Jeremy, thanks for taking the time to reply.

I have checked the Ubuntu box and it seems that it is using the non-ruby
driver, but it reports its version as 4.0.24. This may be the cause of
my problems.

I have libmysqlclient version 12, 14 and 15 installed and it looks like
rails is using an older one, can I safely remove the older libs (I do
not need mysql clients other than for rails access) or can I specify
which to use in the environment somewhere. I’m just concerned that if
Rails is using the older client and I remove it, it may stop working.

As to the patch for using stored procedures from Rails, I’ll tidy up the
change and submit as you suggest.

Thanks and regards,

paulie