Rails - Mar 2008 - Specifying user to run specific query through connection object

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

>  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 :)

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
http://lindsaar.net/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Mar 10, 5:47 pm, "Mikel Lindsaar"
<raasd...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> >  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 :)
Not feeling bad about it because I don''t really have a choice in this
case - thanks for the encouragement though.
> 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.
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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---