Using activerecord with mysql

[Rob Mauchel]

Hi All,

I've successfully been using Ruby on one machine for quite some time and
now, on another machine, after:


installing Ruby 1.86-27 rc2 (using the one click installer for Windows
obtained from www.ruby-lang.org, with the 'Enable RubyGems' option
checked)

installing the additional gems activerecord and mysql (using gem install
...)

installing mysql 5.1.40 (from mysql.com), using the typical install
option
running the mysql server instance configuration wizard, selecting
'standard configuration', 'install as windows service', 'launch the
mysql server automatically', and not changing the root password

copying libmysql.dll into c:\ruby\bin

creating a new database 'test' (default charset and collation) with
table 'a' containing integer field 'x'


I find that the following Ruby code:


require 'active_record'
class User < ActiveRecord::Base
establish_connectionadapter => 'mysql', :host => 'localhost',
:username => 'root', assword => '', :database => 'test')
puts(connected?())
set_table_name('a')
end

user = User.new("x" => 5)
user.save()


generates 'false' for the return value from 'connected?()' and the
following exception on user.save() that confirms the lack of a
connection:


Mysql::Error: query: not connected: INSERT INTO `a` (`x`) VALUES(5)
(ActiveRecord::StatementInvalid)


Note that there is definitely communication going on with the database
server as changing any of the above parameters to establish_connection
generates an exception, as expected, while the above parameters do not.

Can anyone advise as to why activerecord is unable to successfully
connect (not to mention why it doesn't generate an exception indicating
why)?

Any assistance would be greatly appreciated!

 

[Thufir]

Dunno if this helps, but the following works for me:

thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$ nl query.rb
1 require 'rubygems'
2 require 'activerecord'
3 require 'yaml'
4 require 'item'
5 require 'open-uri'
6 require 'pp'


7 db = YAML::load(File.open('database.yml'))

8 ActiveRecord::Base.establish_connection(
9 :adapter => db["development"]["adapter"],
10 :host => db["development"]["host"],
11 :username => db["development"]["username"],
12 assword => db["development"]["password"],
13 :database => db["development"]["database"])


14 items = Item.findall)

15 items.each do |item|
16 pp "************************"
17 pp "************************"
18 pp "************************"
19 pp "************************"
20 pp "************************"
21 pp "************************"
22 pp "************************"
23 pp item.url
24 end
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$ nano query.rb
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$ nl query.rb
1 require 'rubygems'
2 require 'activerecord'
3 require 'yaml'
4 require 'item'
5 require 'open-uri'
6 require 'pp'


7 db = YAML::load(File.open('database.yml'))

8 ActiveRecord::Base.establish_connection(
9 :adapter => db["development"]["adapter"],
10 :host => db["development"]["host"],
11 :username => db["development"]["username"],
12 assword => db["development"]["password"],
13 :database => db["development"]["database"])


14 items = Item.findall)

15 items.each do |item|
16 pp item.url
17 end
thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$ nl database.yml
1 development:
2 adapter: mysql
3 host: 127.0.0.1
4 database: rss2mysql
5 username: ruby
6 password: password

7 test:
8 adapter: mysql
9 host: 127.0.0.1
10 database: rss2mysql
11 username: ruby
12 password: password
thufir@ARRAKIS:~/projects/rss2mysql$



you can always try with sqlite first, which requires little to no
configuration. Also, see:

http://blog.aizatto.com/2007/05/21/activerecord-without-rails/



HTH,


Thufir

 

[Rob Mauchel]

Thanks for your input, Thufir, but I've tried to keep the example that
illustrates the problem down to bare essentials. I'm afraid introducing
additional objects (yaml, item, open-uri, pp) won't help - i.e. if my
example doesn't work for me, there's no reason yours will.

An interesting additional clue to the problem is that using the mysql
gem directly (with the same query above for which activerecord generated
an exception) as shown below, DOES work:


require 'mysql'

db = Mysql.new('localhost', 'root', '', 'test')
rs = db.query('INSERT INTO `a` (`x`) VALUES(5)')


Is there some kind of incompatibility between activerecord gem v2.3.4
and mysql gem v2.8.1 (or some other gem activerecord requires to
properly use the mysql gem)?

Again, any clues would be greatly appreciated!

 

[Thufir]

Could still be a db problem.

You don't have to use yml, try something like:

thufir@ARRAKIS:~/projects/rss2mysql$
thufir@ARRAKIS:~/projects/rss2mysql$ nl mysql.rb
1 require 'rubygems'
2 require 'activerecord'
3 require 'pp'

4 ActiveRecord::Base.establish_connection(
5 :adapter => 'mysql',
6 :host => '127.0.0.1',
7 :username => 'ruby',
8 assword => 'password',
9 :database => 'rss2mysql')

10 class Item < ActiveRecord::Base
11 has_one age
12 end

13 items = Item.findall)

14 items.each do |item|
15 pp item.url
16 end
thufir@ARRAKIS:~/projects/rss2mysql$

Of course, modify for your configuration.

At first, I thought maybe mysql wasn't installed/running properly for
you, but it does appear to be working. you can always log into mysql
and manually create the db and populate a table with data, then query
from ruby. From mysql, can you describe the table?



HTH,

Thufir

 

[Rob Mauchel]

It doesn't matter whether new data is being written to the db or
existing data is being read from it. Either way, activerecord generates
the aforementioned "Mysql::Error: query: not connected" exception.

As described in the first post, the table for this simple illustrative
example is named 'a' and has one field/column named 'x'.

As also mentioned in the first post, if I call establish_connection with
an invalid username, password, or database name, the invalidity is
caught, as expected.

If however all of these are correct, no error occurs from attempting to
establish the connection. Instead, the aforementioned "Mysql::Error:
query: not connected" exception is generated when activerecord tries to
execute the SQL query associated with the operation being performed
(read or write - as I said, doesn't matter which).

I've confirmed from debugging now that the connection is being made on
line 585 of activerecord's file mysql_adapter.rb (call to
@connection.real_connect - not actually hit until my statement above,
'user = User.new("x" => 5)', is performed).

Unfortunately, it's not possible to step into the above call to
real_connect to see why no connection actually occurs - presumably
because it's in a binary library (libmysql.dll). But the parameters
passed in are as expected.

 

[Thufir]

It doesn't matter whether new data is being written to the db or
existing data is being read from it. Either way, activerecord generates
the aforementioned "Mysql::Error: query: not connected" exception.

hmm. Dunno on that one. Personally, I would still try a SQLite for
comparison. You could try raw SQL if it's just AR acting oddly (it seems
unlikely to be a bug, but that's always possible).

As described in the first post, the table for this simple illustrative
example is named 'a' and has one field/column named 'x'.

Ah, ok, you can go into mysql and describe somedb.a, and can insert data
into that db logged in as whatever user you're trying from ruby? Pardon.

[...]

If however all of these are correct, no error occurs from attempting to
establish the connection. Instead, the aforementioned "Mysql::Error:
query: not connected" exception is generated when activerecord tries to
execute the SQL query associated with the operation being performed
(read or write - as I said, doesn't matter which).

Could that be a mysql permissions problem? Else, I dunno.

I've confirmed from debugging now that the connection is being made on
line 585 of activerecord's file mysql_adapter.rb (call to
@connection.real_connect - not actually hit until my statement above,
'user = User.new("x" => 5)', is performed).

Unfortunately, it's not possible to step into the above call to
real_connect to see why no connection actually occurs - presumably
because it's in a binary library (libmysql.dll). But the parameters
passed in are as expected.

Well, it may not be MySQL nor ruby, per se. Personally, I'd try both a
different db and maybe a different box if the code and db seem fine.
Other than that, I really don't know.




That's all I got, hope it helps,


Thufir

 

[Rob Mauchel]

I found the problem!

activerecord 2.3.4 doesn't work with mysql 5.1.

Reverting back to the mysql 5.0 did the trick!

Thanks for trying to help nevertheless, Thufir!

-Rob

 

[Thufir]

I found the problem!

activerecord 2.3.4 doesn't work with mysql 5.1.

Reverting back to the mysql 5.0 did the trick!

Thanks for trying to help nevertheless, Thufir!

-Rob

Oh, odd. I wouldn't think there'd be a difference between mysql 5.0
and 5.1 which would effect AR, but I guess there is a bug of some
kind. Makes me leery of updating my system (well, not really).


-Thufir

 

[Kevin Williams]

I found the problem!

activerecord 2.3.4 doesn't work with mysql 5.1.

Reverting back to the mysql 5.0 did the trick!

Thanks for trying to help nevertheless, Thufir!

-Rob

It's not activerecord, it's the mysql gem for windows. So far it only
support mysql 5.0.

 

[Luis Lavena]

Oh, odd.  I wouldn't think there'd be a difference between mysql 5.0
and 5.1 which would effect AR, but I guess there is a bug of some
kind.  Makes me leery of updating my system (well, not really).

The original user having problems with MySQL was running on Windows.

The MySQL/Ruby binary bindings were built against 5.0.x of MySQL, thus
is going to fail when executed against 5.1.x version of MySQL.

This has been documented in the release notes of the gem:

http://rubyforge.org/frs/shownotes.php?group_id=1598&release_id=38245

And also several times in this mailing list.

 

[Rob Mauchel]

I don't question any of your points about the mysql gem but, as I
pointed out in my second post, I nevertheless had no problem using it
directly to read or write to a db hosted by a 5.1 server. It was only
activerecord that was generating exceptions.

 

[Luis Lavena]

I don't question any of your points about the mysql gem but, as I
pointed out in my second post, I nevertheless had no problem using it
directly to read or write to a db hosted by a 5.1 server. It was only
activerecord that was generating exceptions.

The issue is actually using a libmysql.dll different than the one used
to build the gem.

Just placing the correct libmysql.dll version will allow you speak to
different versions of MySQL remotely.

Now, ActiveRecord MySQL adapter perhaps is evaluating the version of
MySQL used and incorrectly use the local one instead of the remote/
server one, thus generating the errors you're seeing.

 

[Rob Mauchel]

I've only ever been working with a local database and in each case (5.0
server and 5.1 server) placed a copy of that installation's libmysql.dll
into ruby/bin.