Forum: Ruby on Rails find_by_name -> Lost connection to MYSQL server during query

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Bontina C. (Guest)
on 2007-01-16 13:18
    *  [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:in `execute'
        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:in `select_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:in `find_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:in `send'
        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
David H. (Guest)
on 2007-01-16 19:52
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.
Bontina C. (Guest)
on 2007-01-17 03:00
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.
Bontina C. (Guest)
on 2007-01-17 03:38
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?
David H. (Guest)
on 2007-01-17 18:07
>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")
This topic is locked and can not be replied to.