Specifying user to run specific query through connection object

Rails
1

Hello Folks,

I am trying to get my app up and running on a dedicated virtual server
running on CentOS 5. I am using Ruby 1.8.5 and rails 1.2.3.

I can access/update my MySql tables through normal interaction with
the pages without any problem. I am assuming the user/pwd specified in
database.yml under the db_production is being picked up for this.

However at one point in my code I have to execute a stored procedure -
I know it goes against the philosophy of rails but I have to. To do
that I make a call as follows from my model object
connnection.select_all(“call <my_stored_proc(parameters)>”)

However, when I try to load that page that runs that query, I get a
failure in my logs saying

Mysql::Error: There is no ‘root’@‘localhost’ registered: call
<my_stored_proc(parameters)>

Now my mongrel instances are running under a “mongrel” user name and
my db access is specified in the database.yml to be some other user.
So why is the system trying to use root@localhost (which I don’t have
defined in db) and why is it not picking the username and password
specified in the database.yml file?

Thanks for any pointers/thoughts.

-S

2

Hello Folks,
However at one point in my code I have to execute a stored procedure -
I know it goes against the philosophy of rails but I have to. To do
that I make a call as follows from my model object
connnection.select_all(“call <my_stored_proc(parameters)>”)
Mysql::Error: There is no ‘root’@‘localhost’ registered: call
<my_stored_proc(parameters)>

I use stored procedures a lot with Rails due to the database backend I
am using (pre existing). It works fine and actually can give a
performance boost in certain situations… so don’t feel too bad :slight_smile:

The connection object you are calling select_all on is the same
connection that your other AR models use for normal talking to the
database, it is specified in your database.yml as you suspect.

Your mongrels etc have nothing to do with it.

Easy way to check if your SQL syntax is correct is to run the request,
look in the log and copy the exact SQL statement that rails made to
the database.

Then log into mysql from the command line using the username and
password in your database.yml file.

Then run that exact query and see what you get back.

You might also have a permissions problem, stored procedures need to
be granted just like tables. I would check this too.

Regards

Mikel

3

On Mar 10, 5:47 pm, “Mikel L.” [email protected] wrote:

performance boost in certain situations… so don’t feel too bad :slight_smile:
Not feeling bad about it because I don’t really have a choice in this
case - thanks for the encouragement though.

Then log into mysql from the command line using the username and
password in your database.yml file.

Then run that exact query and see what you get back.

You might also have a permissions problem, stored procedures need to
be granted just like tables. I would check this too.

It turns out that even though I had execute permission for everything
on the database for the database user that didn’t help.
‘root’@‘localhost’ was the DEFINER of the stored proc and I had to
give root execute permission on my stored procs and tables where data
is inserted for it to work.

Don’t yet understand why does the stored proc run under root instead
of the username and password specified in database.yml file. But at
least I am not stuck there anymore.

Regards

Mikelhttp://lindsaar.net/

Thanks Mikel.