Find_by_name -> Lost connection to MYSQL server during query


#1
*  [Instant Rails Manager] 1.4

* [Ruby 1.8.5 (from the [HTTP://rubyinstaller.rubyforge.org/

One-Click Ruby Installer] 1.8.5-21)

* [Ruby on Rails] 1.1.6

* [Apache] 1.3.33

* [MySQL] 4.1.9

* [MySQL/Ruby] 2.7 (native driver)

* [Mongrel] 0.3.13.3

* [phpMyAdmin] 2.6.1

Above is my setting.
I have no problem using rails with MYSQL until I’m using the
find_by_columnname
method.

This is the error message when I tested in the console.
ActiveRecord::StatementInvalid: Mysql::Error: Lost connection to MySQL
server du
ring query: SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT 1
from
C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib
/active_record/connection_adapters/abstract_adapter.rb:120:in log' from C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib /active_record/connection_adapters/mysql_adapter.rb:184:inexecute’
from
C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib
/active_record/connection_adapters/mysql_adapter.rb:336:in select' from C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib /active_record/connection_adapters/mysql_adapter.rb:175:inselect_all’
from
C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib
/active_record/base.rb:390:in find_by_sql' from C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib /active_record/base.rb:924:infind_every’
from
C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib
/active_record/base.rb:918:in find_initial' from C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib /active_record/base.rb:1103:insend’
from
C:/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib
/active_record/base.rb:1103:in `method_missing’
from (irb):26

Anyone knows how to fix it?
Thanks


#2

How does “SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT 1”
work when you run it in a MySQL environment with the same login
information that rails is using?

Most of the time when I get that error, it’s a permission problem.
Otherwise, I’ve had problems when I’ve used a MySQL reserved word by
mistake.

Just from glancing at the SQL, it looks fine.
And I don’t see any reserved words from:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

On a related note, in the newer MySQL ruby driver’s there’s a Reconnect
command, that automatically reconnects to the database if you lose your
connection.

I don’t think it’s been intergrated with rails yet, and I’m not sure how
you would turn it on though.


#3

David H. wrote:

How does “SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT 1”
work when you run it in a MySQL environment with the same login
information that rails is using?

SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT 1 in mysql no
response.
But in phpmyadmin, it works. In both environment they use root without
password.

Most of the time when I get that error, it’s a permission problem.
Otherwise, I’ve had problems when I’ve used a MySQL reserved word by
mistake.

Just from glancing at the SQL, it looks fine.
And I don’t see any reserved words from:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

On a related note, in the newer MySQL ruby driver’s there’s a Reconnect
command, that automatically reconnects to the database if you lose your
connection.

The newer version Ruby/MySQL is 1.8?
But I can still do find , just the find_by_xxx is not working. I suppose
I still have connection to MySQL though.

I don’t think it’s been intergrated with rails yet, and I’m not sure how
you would turn it on though.

Thanks for sharing your experience.


#4

Bontina C. wrote:
I found it’s the syntax the driver generated is wrong.
It should be “SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT 1”
without the single quotation. Do you know where can I modify the syntax
to the right version?

Very strange, the single quotes should reduce problems, not cause them.

Sounds like a naming conflict, I’d suggest changing the name of the
table.

Short of that try,

Myobject.find_by_sql(“my custom sql”)
or

List.find_by_sql(“SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT
1”)


#5

David H. wrote:

How does “SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT 1”
work when you run it in a MySQL environment with the same login
information that rails is using?

Most of the time when I get that error, it’s a permission problem.
Otherwise, I’ve had problems when I’ve used a MySQL reserved word by
mistake.

Just from glancing at the SQL, it looks fine.
And I don’t see any reserved words from:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

On a related note, in the newer MySQL ruby driver’s there’s a Reconnect
command, that automatically reconnects to the database if you lose your
connection.

I don’t think it’s been intergrated with rails yet, and I’m not sure how
you would turn it on though.

I found it’s the syntax the driver generated is wrong.
It should be “SELECT * FROM lists WHERE (lists.user_id = 1 ) LIMIT 1”
without the single quotation. Do you know where can I modify the syntax
to the right version?