Solved

Using MySQL with Ruby on Rails

Posted on 2008-10-03
29
2,660 Views
Last Modified: 2013-11-13
I downloaded the latest version of Ruby which now uses sqlite 3 for its default database.  I need to use MySQL.  I created a folder for my first app and then ran the following command:

appfolder>rails first

This created a folder in my appfolder called first.  I then created a new controller named Hello like this in the command line:

appfolder>first>ruby script/generate controller Hello

I then started up WEBrick and navigated my browser to /localhost:3000/hello.  I was supposed to see Unknown action: no action responded to index.  I instead got an error.  The title was "MissingSourceFile in HelloController#index"  with an error message "no such file to load -- sqlite3".

This is when I realized that I needed to change the default somehow.  I changed my database.yml file by running    $rails -d mysql temp     and copying and pasting the yml file in the temp folder into the yml file in my "first" folder.  I then got this error message.

Access denied for user 'root'@'localhost' (using password: YES)

Which used to say (using password: NO) until I change my password to NO.  The database.yml for MySQL states to gem install MySQL, which I did, and then "put its /bin directory on your path", but I'm not sure what path that is.

One more thing, the Wrox book I'm using to learn Ruby states that controllers directory is here:

appfolder\first\controllers

When its actually here:

appfolder\first\app\controllers

Finally, I cannot get to mysql from my shell.  This:

shell> mysql -u root test

returns this:

'mysql' is not recognized as an internal or external command, operable program or batch file.

I can only access mysql from the command line by using the MySQL command line client.

How do I connect to MySQL using Rails????

0
Comment
Question by:CMKool
  • 14
  • 11
  • 2
  • +1
29 Comments
 
LVL 24

Expert Comment

by:fridom
Comment Utility
The configuration for Rails is no ok. Check for the config.yml file and adapt  it to for MySQL

If you can access mysql by command line than it will work for Rails also, just check the config files.

Regards
Friedrich
0
 

Author Comment

by:CMKool
Comment Utility
Are you talking about the database.yml?  Because I've searched my files and I cannot find a file called config.yml.

If you are speaking about the database.yml, I've already attempted to adapt it as I stated in the question...

Please let me know if I am in error.  I attached the code in my database.yml file.
# MySQL.  Versions 4.1 and 5.0 are recommended.

#

# Install the MySQL driver:

#   gem install mysql

# On Mac OS X:

#   sudo gem install mysql -- --with-mysql-dir=/usr/local/mysql

# On Mac OS X Leopard:

#   sudo env ARCHFLAGS="-arch i386" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

#       This sets the ARCHFLAGS environment variable to your native architecture

# On Windows:

#   gem install mysql

#       Choose the win32 build.

#       Install MySQL and put its /bin directory on your path.

#

# And be sure to use new-style password hashing:

#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html

development:

  adapter: mysql

  encoding: utf8

  database: myapp_development

  username: root

  password: 

  host: localhost
 

# Warning: The database defined as "test" will be erased and

# re-generated from your development database when you run "rake".

# Do not set this db to the same as development or production.

test:

  adapter: mysql

  encoding: utf8

  database: myapp_test

  username: root

  password: 

  host: localhost
 

production:

  adapter: mysql

  encoding: utf8

  database: myapp_production

  username: root

  password: 

  host: localhost

Open in new window

0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
> Access denied for user 'root'@'localhost' (using password: YES)

First, did you create your database?  Do you have a password for your root user?

> and then "put its /bin directory on your path"

This is the mysql executable.  It needs to be accessible from the command-line so that it can be called from rake tasks.

> One more thing, the Wrox book I'm using to learn
>  Ruby states that controllers directory is here:
> appfolder\first\controllers

That's wrong.  It should be in the app folder like you said.

> Finally, I cannot get to mysql from my shell.  This:
> shell> mysql -u root test

How'd you install mysql?  What OS are you on, anyway?  I'm assuming Win32 by the instructions you mentioned.

Sounds like you need to put "c:\mysql\bin" into your PATH.  That's what the documentation was trying to explain, but it wasn't very clear.  If you can't run "mysql" from the command-line, it's not on your PATH.

c:\ set PATH=%PATH%;c:\mysql\bin
(from the command line).
Or, you can go into environment variables and add it there permanently.

Your database.yml file looks fine, so let's get mysql working from the dos-prompt first, then we'll see if something else doesn't work right.
0
 

Author Comment

by:CMKool
Comment Utility
O.K. so I have it in the PATH and I can access mysql from the shell.  I entered my password into the database.yml file.

I set up a simple text field application by doing this:

\rails -d mysql textfields

I set up an input.html file in:

textfields\public directory

I added this method to the look_controller.rb:

def at   @data = params[:text1]  end

text1 was the name variable I assigned in the input.html file

I also set up an at.rhtml file in:

textfields\app\views\look directory

So then I opened up WEBrick and navigated my browser to:

//localhost:3000/input.html

I was able to view the html file and input text, however, once I submited it, the browser told me this again:

 Access denied for user 'root'@'localhost' (using password: NO).  Sorry to give you so much info. I just want to make it clear what I am doing just in case one of my steps is incorrect.

Thanks


0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
What version of rails are you using?

Here's an easier way to test the connection (code snippet below). By referencing the connection, it instantiates it, which tests the username and password.  You have to exit and relaunch the console for each test, because it reads the .yml file on startup.

Can you connect with this command (from the prompt)?
mysql -u root -D my_database_development -p
It should prompt you for your password and then should give you a mysql prompt.  Type exit to get out.

Double check your database, username and password in the database.yml file. The rails stuff is working okay, it's the database connection that doesn't work right.
ruby script/console
 

# Loading development environment (Rails 2.1.0)

# >> 
 

ActiveRecord.Base::connection
 

# Mysql::Error: Access denied for user 

# 'root'@'localhost' (using password: YES)
 

Successful connection will look like:
 

>> ActiveRecord::Base.connection

=> #<ActiveRecord::ConnectionAdapters::MysqlAdapter:0x22038a0

Open in new window

0
 

Author Comment

by:CMKool
Comment Utility
>Here's an easier way to test the connection (code snippet below). By referencing the connection, it >instantiates it, which tests the username and password.  You have to exit and relaunch the console >for each test, because it reads the .yml file on startup.

O.K.  I'll try it.

>Can you connect with this command (from the prompt)?
>mysql -u root -D my_database_development -p
>It should prompt you for your password and then should give you a mysql prompt.  Type exit to get out.

I tried and when I entered my password, I got this:

ERROR 1049 942000):  Unknown database 'my_database_development'

>Double check your database, username and password in the database.yml file. The rails stuff is >working okay, it's the database connection that doesn't work right.

Yeah, I checked and everything seems to be good.  I agree that I think it all seems to be coming down to the database connection.  

I've got to run, for probably the rest of the evening.  You around for the weekend?  Probably pick this back up tomorrow afternoon.

Thanks for the help so far.  I feel like it's almost there!!
0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
Change 'my_database_development' to the name of your database when you test it on your system.
0
 
LVL 24

Expert Comment

by:fridom
Comment Utility
At  first you must get the Mysql command line working. You should  not use root for accessing Database.
Create a user named e.g rails and use this account for setting up MySQL please check the docs:
http://dev.mysql.com/doc/refman/5.1/en/installing.html

Especially interesting is the user setup and modes of database access, and encoding of the tables. You proabably need some extra flags to make UTF-8 the default.

After that you'll  have a working access than just fill in the proper credentials

Regards
Friedrich
0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
I use root for developing locally, but you shouldn't do it in a production environment.
0
 

Author Comment

by:CMKool
Comment Utility
Alright, so I put this into the command line:

mysql -u root -D first_production -p

It then asked for my password...I entered it and got this now:

Access denied for user 'root'@'localhost' (using password: NO)

So it appears it recognizes the db, but is still struggling with the password.  I attached the database.yml file to look at.

I'm currently reading the MySQL docs that Friedrich recommended and hopefully there will be some answers there.  It really seems to all be coming down to the username and password.  I'm going to try and create a new username, just need to figure out how to do that.
# MySQL.  Versions 4.1 and 5.0 are recommended.

#

# Install the MySQL driver:

#   gem install mysql

# On Mac OS X:

#   sudo gem install mysql -- --with-mysql-dir=/usr/local/mysql

# On Mac OS X Leopard:

#   sudo env ARCHFLAGS="-arch i386" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

#       This sets the ARCHFLAGS environment variable to your native architecture

# On Windows:

#   gem install mysql

#       Choose the win32 build.

#       Install MySQL and put its /bin directory on your path.

#

# And be sure to use new-style password hashing:

#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html

development:

  adapter: mysql

  encoding: utf8

  database: first_development

  username: root

  password: password

  host: localhost
 

# Warning: The database defined as "test" will be erased and

# re-generated from your development database when you run "rake".

# Do not set this db to the same as development or production.

test:

  adapter: mysql

  encoding: utf8

  database: first_test

  username: root

  password: password

  host: localhost
 

production:

  adapter: mysql

  encoding: utf8

  database: first_production

  username: root

  password: password

  host: localhost

Open in new window

0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
This still doesn't have anything to do with your database.yml file.

You've got the wrong root password for mysql.  You can't really create another user without it.

How'd you set up mysql?  If you used the installer, it may have asked you to provide a password.

Try it without the password:
mysql -u root -D first_production
(the -p tells it to prompt you for the password...)
0
 

Author Comment

by:CMKool
Comment Utility
Yeah, I used the installer and gave it a password.  Which works with

>mysql -u root -p

Tried:

mysql -u root -D first_production

and still got access denied.  I've spend days on this and have no idea which way to go, its starting to get very frustrating.

I even tried to use the default sqlite3, but cannot gem install it because it is not compatible with mswin32.  Tried to install an older version (1.2.3) as suggested in forums, but all that does is return errors.  There has got to be an easier way to get started with RoR.
0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
If this works:
mysql -u root -p
and this doesn't:
mysql -u root -D first_production
then you should probably use the password:
mysql -u root -D first_production -p
0
 

Author Comment

by:CMKool
Comment Utility
Yeah, so that got my in on the command line.

However, when i navigate to

localhost:3000/hello/there

I still get:

Access denied for user 'root'@'localhost' (using password: NO)

hello was the controller I set up in the controllers directory and added the there method to:

def there
end

I then created a there.rhtml file and placed it in the:

app\views\hello directory

ran WEBrick, navigated as stated above and was give the access denied prompt in the browser.

I attached a screen shot of what I'm seeing in the browser.  I also pasted the framework trace into the Code Snippet
C:/Ruby/lib/ruby/1.8/mysql.rb:453:in `read'

C:/Ruby/lib/ruby/1.8/mysql.rb:130:in `real_connect'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb:527:in `connect'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb:186:in `initialize'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb:85:in `new'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb:85:in `mysql_connection'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/abstract/connection_specification.rb:292:in `send'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/abstract/connection_specification.rb:292:in `connection='

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/abstract/connection_specification.rb:260:in `retrieve_connection'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/abstract/connection_specification.rb:78:in `connection'

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/query_cache.rb:8:in `cache'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/caching/sql_cache.rb:12:in `perform_action'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/base.rb:529:in `send'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/base.rb:529:in `process_without_filters'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/filters.rb:568:in `process_without_session_management_support'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/session_management.rb:130:in `process'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/base.rb:389:in `process'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/dispatcher.rb:149:in `handle_request'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/dispatcher.rb:107:in `dispatch'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/dispatcher.rb:104:in `synchronize'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/dispatcher.rb:104:in `dispatch'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/dispatcher.rb:120:in `dispatch_cgi'

C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.1.1/lib/action_controller/dispatcher.rb:35:in `dispatch'

C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.1.1/lib/webrick_server.rb:112:in `handle_dispatch'

C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.1.1/lib/webrick_server.rb:78:in `service'

C:/Ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service'

C:/Ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:162:in `start'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:162:in `start_thread'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:95:in `start'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:92:in `each'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:92:in `start'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:23:in `start'

C:/Ruby/lib/ruby/1.8/webrick/server.rb:82:in `start'

C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.1.1/lib/webrick_server.rb:62:in `dispatch'

C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.1.1/lib/commands/servers/webrick.rb:66

C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'

C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'

C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.1.1/lib/active_support/dependencies.rb:510:in `require'

C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.1.1/lib/active_support/dependencies.rb:355:in `new_constants_in'

C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.1.1/lib/active_support/dependencies.rb:510:in `require'

C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.1.1/lib/commands/server.rb:39

C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'

C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'

script/server:3

Open in new window

controller-error.doc
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
Access denied for user 'root'@'localhost' (using password: NO)
The "NO" means mysql didn't get a password in the login request, but you have a password for your root user, so that's why it doesn't connect.

Do you have the password in the correct environment in your database.yml file?
Or, remove your password.
0
 

Author Comment

by:CMKool
Comment Utility
Would you be able to expand a little bit on what you mean by having the password in the correct environment...

I've tried it with my password in place and removed from the database.yml file and both return the same thing (Access denied for user 'root'@'localhost' (using password: NO)
0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
Your database.yml file has sections for development, test, and production.  If you run without specifying an environment, you get development. So, if you were putting your password in the production section, it wouldn't be available for the development section and wouldn't show up when you tried to access the database.

So, make sure you're putting your password under the development section.
0
 

Author Comment

by:CMKool
Comment Utility
O.K.  So now I'm getting:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

Found some info on this here:

http://dev.mysql.com/doc/refman/5.0/en/old-client.html

but I'm not sure this is the same problem. MY MySQL version is 5.0 and I'm not getting this in the command line, only in the browser.  What do you think?
0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
Hey, now we're getting somewhere!

gem update mysql

Or, remove your password so it doesn't have to mess with the authentication protocols.
0
 

Author Comment

by:CMKool
Comment Utility
O.K.  I did the gem update mysql, it stated "nothing to update"

I removed my password from the database.yml file, but I still got the same:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

When you stated to remove the password, were you talking about the database.yml or something else?
0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
Well, you have to do it in both database.yml and mysql.

Login to mysql:
mysql -u root -p
Enter password

At mysql prompt:
mysql> SET PASSWORD FOR root@localhost = '';
  Query OK, 0 rows affected (0.00 sec)
mysql> exit

Remove the password from database.yml and try to connect.
0
 

Author Comment

by:CMKool
Comment Utility
O.K. so I did.  Check out what I got in the attached screen shot.  I'm not sure what it means and don't want to screw anything up we've already done.
SET-PASSWORD.doc
0
 

Author Comment

by:CMKool
Comment Utility
Bueller?  
0
 

Author Comment

by:CMKool
Comment Utility
Hello, I had been having the above back and forth with Wes and it seems to have stalled.  Having the answer to this question is not dire, however, I would like to resolve this issue.  Is there anyone that can help?
0
 

Author Comment

by:CMKool
Comment Utility
Please disregard my last post, I got it to work.
0
 
LVL 14

Expert Comment

by:wesgarrison
Comment Utility
Sorry, just had my first kid and I'm learning to juggle everything!   Glad you got it working.
0
 

Author Comment

by:CMKool
Comment Utility
No worries.  I forgot a " mark was all.   Congratulations!
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PHP MySQL and joining two table results 4 55
MySQL Error 3 34
Clean text to insert in database 9 39
html input clean up 3 28
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now