Remote MySQL database.yml problem

Hello,

First of all I am not a Ruby programmer. The reason I am writing this post is because I took over the administration of a CentOS-server running a Ruby-application because the former administrator is no longer available in the company. I am novice in both Linux and Ruby so bear with me.

As stated, we have a CentOS server running Apache and Ruby 1.8.7. We have a Ruby application with a database.yml pointint to two adapters. One mysql for the application itself to store data, and one adapter for reading data. Both of them are pointing towards localhost.

Now we need to move the reading database from localhost to remote.host.local since we have a new mysql server in the network.

When i change the host: localhost to host: remote.host.local i get 400 Internal server error when i start my application.

I have verified the connection from the CentOS server, and to the remote server. Iptables is configured to pass through data on port 3306 The remote MySQL server is accepting data from user@ip-adress-of-CentOS. Privileges is configured to GRANT ALL on the database from the remote user.

When i run mysql -h remote.host.local -u user -p i get a valid connection.

Is there anywhere else but the database.yml file i need to specify something? I have verified with the developer of the application that he uses only database.yml to connect to the MySQL server.

My hair is turning gray because of this problem... Thank you in advance. David.

Hello,

First of all I am not a Ruby programmer. The reason I am writing this post is because I took over the administration of a CentOS-server running a Ruby-application because the former administrator is no longer available in the company. I am novice in both Linux and Ruby so bear with me.

As stated, we have a CentOS server running Apache and Ruby 1.8.7. We have a Ruby application with a database.yml pointint to two adapters. One mysql for the application itself to store data, and one adapter for reading data. Both of them are pointing towards localhost.

Now we need to move the reading database from localhost to remote.host.local since we have a new mysql server in the network.

When i change the host: localhost to host: remote.host.local i get 400 Internal server error when i start my application.

I have verified the connection from the CentOS server, and to the remote server. Iptables is configured to pass through data on port 3306 The remote MySQL server is accepting data from user@ip-adress-of-CentOS. Privileges is configured to GRANT ALL on the database from the remote user.

When i run mysql -h remote.host.local -u user -p i get a valid connection.

Is there anywhere else but the database.yml file i need to specify something? I have verified with the developer of the application that he uses only database.yml to connect to the MySQL server.

Have a look in the rails log (log/development.log or log/production.log in the rails project folder dependent on which mode you are using) to see if there is more info there.

Colin

Colin Law wrote in post #1042264:

One mysql for the application itself to store data, and one adapter for server. Iptables is configured to pass through data on port 3306 The remote MySQL server is accepting data from user@ip-adress-of-CentOS. Privileges is configured to GRANT ALL on the database from the remote user.

When i run mysql -h remote.host.local -u user -p i get a valid connection.

Is there anywhere else but the database.yml file i need to specify something? I have verified with the developer of the application that he uses only database.yml to connect to the MySQL server.

Have a look in the rails log (log/development.log or log/production.log in the rails project folder dependent on which mode you are using) to see if there is more info there.

Colin

To post to this group, send email to rubyonrails-talk@googlegroups.com. To unsubscribe from this group, send email to

rubyonrails-talk+unsubscribe@googlegroups.com.

For more options, visit this group at

http://groups.google.com/group/rubyonrails-talk?hl=en.

-- gplus.to/clanlaw

Hello and thank you for the fast answer.

I am not in my office at the moment so I cannot see the files you posted.

Just to verify (because i am novice) are those files located in the application itself or inside the Ruby installation folder? Is there a way inside the application to se where logs are to be written? David

They are in the log folder within the rails application (so that is a folder log at the same level as the config folder that has database.yml in it).

Colin

Colin Law wrote in post #1042268:

Hello,

The log says: Mysql::Error - Can't connect to MySQL server on 'remote.host.local' (13):

But why... When I run mysql -h remote.host.local -u user -p form the server it works.

Could you run this on your database.yml file (from your Rails root) and check if it looks like you expect? Subtle space/tab errors may yield an invalid database.yml file.

$ irb 1.9.3p0 :001 > require ‘yaml’; YAML::load(File.open(‘config/database.yml’)) => {“development”=>{“adapter”=>“postgresql”, “encoding”=>“unicode”, “pool”=>5, “username”=>“peterv”, “password”=>nil, “min_messages”=>“log”, “database”=>“billing_engine_development”}, …}

HTH,

Peter

Peter Vandenabeele wrote in post #1042328:

$ irb 1.9.3p0 :001 > require ‘yaml’; YAML::load(File.open(‘config/database.yml’))

And check the output yourself (no need to copy, may contain passwords etc.).

It parses the database.yml file (which is obviously a YAML file). If you have formatting problems in that file, it may be visible in the result.

Peter

In case it is still not clear, Peter means you to open a terminal and cd to the top level of your rails projectexit. Then type irb and when it comes back with the prompt enter the require .... line and see what it says. You can then check the host, database, username and pwd are correct for the environment (development or production). To get back to the terminal prompt type exit

Colin

Peter Vandenabeele wrote in post #1042334:

Colin Law wrote in post #1042340:

>> (13): > 1.9.3p0 :001 > require 'yaml'; Do you want me to just copy and paste the code inside my database.yml

It parses the database.yml file (which is obviously a YAML file). If you have formatting problems in that file, it may be visible in the result.

In case it is still not clear, Peter means you to open a terminal and cd to the top level of your rails projectexit. Then type irb and when it comes back with the prompt enter the require .... line and see what it says. You can then check the host, database, username and pwd are correct for the environment (development or production). To get back to the terminal prompt type exit

Colin

Thank you.

This is my result. Does it look suspicious? I edited out some sensitive information...

irb(main):002:0* YAML::load(File.open('database.yml')) => {"projectname"=>{"username"=>"user", "adapter"=>"mysql", "database"=>"somedatabase", "host"=>"localhost", "password"=>"some_password"}, "projectname02"=>{"encoding"=>"utf8", "username"=>"user", "adapter"=>"mysql", "database"=>"somedatabase", "host"=>"remote.host.local", "password"=>"somepassword"}}

Colin Law wrote in post #1042340:

(13):

1.9.3p0 :001 > require ‘yaml’; Do you want me to just copy and paste the code inside my database.yml

It parses the database.yml file (which is obviously a YAML file). If you

have

formatting problems in that file, it may be visible in the result.

In case it is still not clear, Peter means you to open a terminal and

cd to the top level of your rails projectexit. Then type

irb

and when it comes back with the prompt enter the require … line and

see what it says. You can then check the host, database, username and

pwd are correct for the environment (development or production).

To get back to the terminal prompt type exit

Colin

Thank you.

This is my result. Does it look suspicious? I edited out some sensitive

information…

irb(main):002:0* YAML::load(File.open(‘database.yml’))

=> {“projectname”=>{“username”=>“user”, “adapter”=>“mysql”,

“database”=>“somedatabase”, “host”=>“localhost”,

“password”=>“some_password”}, “projectname02”=>{“encoding”=>“utf8”,

“username”=>“user”, “adapter”=>“mysql”, “database”=>“somedatabase”,

“host”=>“remote.host.local”, “password”=>“somepassword”}}

If I understand correctly, this means that the 2 “environments” for this system

are ‘projectname’ and ‘projectname02’.

You may try this to confirm that assumption:

$ RAILS_ENV=projectname rails console

(Note: take over the spaces exactly as I show above; no spaces around the ‘=’)

If that does something useful, than we confirmed that scenario.

Warning, it that command succeeds, you may be connected directly to your

production database, be careful.

If you wanted to connect to a development database in default

‘development’ mode, you would need a stanza in you config/database.yml

of the form:

development:

adapter: mysql2

host: localhost

username: user…

password: pass…

pool: 5

timeout: 5000

socket: /var/run/mysqld/mysqld.sock

database: dbname

^

These are all exactly 2 spaces (not tabs or anything else)

With that ‘development’ stanza in place, you could try:

$ rake db:create # assuming the development database does not exist yet

$ rake db:migrate

$ rails console

HTH,

Peter

David: Before doing any more, have you checked that you have an up to date backup of the database (if it is important)? If not then don't think about doing anything until you have a backup and have checked that you can restore it.

Secondly I notice in your database.yml you have mysql as the adapter rather than mysql2 which is what is used now. Does anyone know whether that might be an issue?

Colin

Colin

Warning, it that command succeeds, you may be connected directly to your

production database, be careful.

David: Before doing any more, have you checked that you have an up to

date backup of the database (if it is important)? If not then don’t

think about doing anything until you have a backup and have checked

that you can restore it.

Thanks for that correction. I should have stated that advise too, before

any other …

Peter