ActiveRecord and Lost Connection


#1

This isnt totally a rails question; I am using ActiveRecord for storing
some simple values into a database. My problem; I keep gettign MySQL
Errors. On the Win32 machine it was off and on. Now on the linux
machine (ubuntu) I can’ t connect at all. I have all the password
settings correct.

My only thought, I am working with Threads and thought that might cause
the issue, but I removed the threading out of it, so no dice there.

Word: a Count: 270
ERR: invalid process URL state
Mysql::Error: Lost connection to MySQL server during query: SELECT *
FROM numbers_feed_links WHERE (
parsed = ‘0’) ORDER BY created_on desc

Here is kind of a unit-test

Berlin B.

Used to test active record connection

Run with:

ruby connect_db.rb

require ‘active_record’
require ‘logger’;

Use the following to reformat the logging message:

class Logger; def format_message(severity, timestamp, msg, progname)

“#{msg}\n” end; end

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
:adapter => “mysql”,
:host => “localhost”,
:username => “USER_NAME”,
:password => “PASSWORD”,
:database => “botverse_development”
)

@logger = Logger.new(STDOUT)

Continue

Active Record Class setup ---------------

class NumbersLink < ActiveRecord::Base
end

Main Entry Point

def ruby_main
@logger.info “Connecting.”

Create a dummy record

link = NumbersLink.new()
link.main_url = “http://www.yahoo.com
link.url_title = “The Title”
link.created_on = Time.now

if link.save
@logger.info(“Record was successfully created.”)
else
@logger.info(“ERR: Could not create record”)
end

@logger.info “Done.”
end

ruby_main

Once the record is saved, you will get the following message:

Record was successfully created.

End of File

Database Schema (MySQL)

– Create the simple user blog
– Users have user-links
CREATE TABLE numbers_links (
id int(11) NOT NULL auto_increment,
main_url varchar(255) NOT NULL,
url_title varchar(128) NOT NULL,
url_description varchar(255),
source varchar(255),
keywords varchar(255),
created_on DATETIME DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (id)
);

-------- New Report being run Wednesday 05/03/2006 03:52 AM


Berlin B.
(ramaza3 on freenode)
http://www.newspiritcompany.com
http://www.newspiritcompany.com/newforums
also checkout alpha version of botverse:
http://www.newspiritcompany.com:8086/universe_home


#2

Mysql::Error: Lost connection to MySQL server during query: SELECT * FROM
numbers_feed_links WHERE (
parsed = ‘0’) ORDER BY created_on desc

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

look for wait_timeout… that might be it.


#3

Philip H. wrote:

Word: a Count: 270

:host => “localhost”,
class NumbersLink < ActiveRecord::Base
link.main_url = “http://www.yahoo.com
end

PRIMARY KEY (id)
http://www.newspiritcompany.com
Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

Yea, thanks that might be it. I also found this:

When the MySQL server is configured with wait_timeout larger than
2147483,
client connections are lost if they are idle for more than ~0.5 seconds.
Same
behaviour with both JDBC client and mysql client.
If queries are executed repeatedly without any delay between them, the
connection seems to stay alive.


Berlin B.
(ramaza3 on freenode)
http://www.newspiritcompany.com
http://www.newspiritcompany.com/newforums
also checkout alpha version of botverse:
http://www.newspiritcompany.com:8086/universe_home


#4

Hi!

On May 3, 2006, at 8:00 AM, Berlin B. wrote:

dice there.
look for wait_timeout… that might be it.

ActiveRecord::Base.establish_connection(

Create a dummy record

keywords varchar(255),
Berlin B.

seconds. Same
behaviour with both JDBC client and mysql client.
If queries are executed repeatedly without any delay between them, the
connection seems to stay alive.

Not sure if this is exactly your problem or not but it might help. I

had a few low traffic sites that would get the same mysql error after
about 8 hours of idle time. This from _why saved the day:

“”"
Just raising the interactive_timeout doesn’t really solve the problem
(while it may appear to.) I would suggest setting the
verification_timeout for ActiveRecord. Set it lower than the
interactive_timeout in MySQL.

ActiveRecord::Base.verification_timeout = 14400

This will force Rails to reconnect to the database before MySQL can get
a chance to timeout.

_why
“”"

Cheers-
-Ezra


#5

Ezra Z. wrote:

all the password settings correct.

Use the following to reformat the logging message:

)

Main Entry Point

if link.save

id int(11) NOT NULL auto_increment,

Yea, thanks that might be it. I also found this:

verification_timeout for ActiveRecord. Set it lower than the


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

Thanks, yea; I did things suggested in the links above. I set the
wait_timeout in MySQL lower and that didnt do much. also added a
reference into the hosts.allow. I will do what you have suggested.

Where do I place that code?


Berlin B.
(ramaza3 on freenode)
http://www.newspiritcompany.com
http://www.newspiritcompany.com/newforums
also checkout alpha version of botverse:
http://www.newspiritcompany.com:8086/universe_home


#6

Ezra Z. wrote:

all the password settings correct.

Use the following to reformat the logging message:

)

Main Entry Point

if link.save

id int(11) NOT NULL auto_increment,

Yea, thanks that might be it. I also found this:

verification_timeout for ActiveRecord. Set it lower than the


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

I am also thinking that it might be a TCP/IP setting within MySQL that I
am missing. I have bind-address?


Berlin B.
(ramaza3 on freenode)
http://www.newspiritcompany.com
http://www.newspiritcompany.com/newforums
also checkout alpha version of botverse:
http://www.newspiritcompany.com:8086/universe_home


#7

Berlin B. wrote:

ERR: invalid process URL state

Run with:

ActiveRecord::Base.logger = Logger.new(STDOUT)

Continue

end

End of File

source varchar(255),
keywords varchar(255),
created_on DATETIME DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (id)
);

-------- New Report being run Wednesday 05/03/2006 03:52 AM

Strange, I was working with Ubuntu and the libmysql-ruby wasnt
installed, just a heads up. What a strange error.


Berlin B.
(ramaza3 on freenode)
http://www.newspiritcompany.com
http://www.newspiritcompany.com/newforums
also checkout alpha version of botverse:
http://www.newspiritcompany.com:8086/universe_home