MySQL loses connection, strange problem, will pay for help

benjohnson · June 23, 2006, 3:43am

I have searched the forums, searched google, etc for the past 3 days and
have not found a solution that will work. Any help is greatly
appreciated and at this point I have no problem paying for help. I have
a paypal account.

I was developing my rails project on a server that had mysql 5. We
finished development and needed to move it over to the client server,
their server has mysql 4.1. Anyways, everything was installed properly
on this server: ruby, ruby gems, rails, mysql, etc.

I copied over my rails project directory to the new server, reset up my
database.yml file and ran all of my tests to make sure everything was
working. I got a ton of errors that all said:
“ActiveRecord::StatementInvalid: Mysql::Error: Lost connection to MySQL
server during query:”

The usernames and passwords in the database.yml file were correct, I
checked them 3-4 times. When I run “mysql -u root -p” in my console I
successfully log in. I can run any mysql query I wish. When I use those
same usernames and passwords in the database.yml file I lose connection.

Here is the strange part:
I edited the root mysql user and removed the password. I then removed
the password from database.yml. That fixed it, no errors or anything. I
know my password wasn’t wrong because I checked it numerous times and
could successfully log in with the same username and password doing
mysql -u root -p. So providing a password breaks everything.

I read on the internet that mysql 4.1 and mysql 5 had different password
structures, so I’m assuming that’s the problem here, but I have no idea
how to fix this. Since I developed my rails app on a server with mysql 5
and then moved it to a server that has mysql 4.1 does rails think I have
5? The ONLY thing I copied over from the old server was my rails project
directory, the single directory that contains your app, config, db, etc
directories.

Once again any help is greatly appreciated. I am clueless as to what is
causing this and will pay for help if need be.

Thanks for your time.

benjohnson · June 23, 2006, 3:49am

Also I have been trying to do:

gem install mysql

From the options I will select #2 since my new server is a RedHat
server.

I get this error when trying to install the gem:

Attempting local installation of ‘mysql’
Local gem file not found: mysql*.gem
Attempting remote installation of ‘mysql’
Select which gem to install for your platform (x86_64-linux)

mysql 2.7.1 (mswin32)
mysql 2.7 (ruby)
mysql 2.6 (ruby)
mysql 2.5.1 (ruby)
Cancel installation

2
Building native extensions. This could take a while…
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers. Check the mkmf.log file for more
details. You may need configuration options.

Provided configuration options:
–with-opt-dir
–without-opt-dir
–with-opt-include
–without-opt-include=${opt-dir}/include
–with-opt-lib
–without-opt-lib=${opt-dir}/lib
–with-make-prog
–without-make-prog
–srcdir=.
–curdir
–ruby=/usr/local/bin/ruby
–with-mysql-config
–without-mysql-config
–with-mysql-dir
–without-mysql-dir
–with-mysql-include
–without-mysql-include=${mysql-dir}/include
–with-mysql-lib
–without-mysql-lib=${mysql-dir}/lib
–with-mysqlclientlib
–without-mysqlclientlib
–with-mlib
–without-mlib
–with-mysqlclientlib
–without-mysqlclientlib
–with-zlib
–without-zlib
–with-mysqlclientlib
–without-mysqlclientlib
–with-socketlib
–without-socketlib
–with-mysqlclientlib
–without-mysqlclientlib
–with-nsllib
–without-nsllib
–with-mysqlclientlib
–without-mysqlclientlib
ERROR: While executing gem … (RuntimeError)
ERROR: Failed to build gem native extension.
Gem files will remain installed in
/usr/local/lib/ruby/gems/1.8/gems/mysql-2.7 for inspection.
ruby extconf.rb install mysql\nchecking for mysql_query() in
-lmysqlclient… no
checking for main() in -lm… yes
checking for mysql_query() in -lmysqlclient… no
checking for main() in -lz… yes
checking for mysql_query() in -lmysqlclient… no
checking for main() in -lsocket… no
checking for mysql_query() in -lmysqlclient… no
checking for main() in -lnsl… yes
checking for mysql_query() in -lmysqlclient… no

Results logged to
/usr/local/lib/ruby/gems/1.8/gems/mysql-2.7/gem_make.out

benjohnson · June 23, 2006, 4:11am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The problem is most likely the version of the mysql client on your new
Red Hat server. The new Red Hat server probably doesn’t have a compled
mysql binding installed (no mysql.so) and it is probably loading the
default pure-ruby version that comes with Rails.

The default version that comes with Rails is for Mysql 4.0.x. Mysql
drastically changed it’s password formats from 4.0 to 4.1, however you
can still force MySQL to store passwords in the “old password” format.

Your Mysql 5 box uses the new password formats and you probably have a
mysql.so file, so all was well. But the RH box probably doesn’t. So you
need to fix this.

You can test this theory by running irb:

irb> require ‘mysql’

if this fails you don’t have a global mysql.so file

irb> require ‘rubygems’
irb> require ‘mysql’

if this fails you don’t have a mysql.so file installed via rubygems

if it passes or says “true” or “false” then it loaded the mysql file

now check it’s version.

irb> Mysql
=> Mysql
irb> Mysql.client_info
=> “4.1.15”

You want it to be 4.1.x. If it says “4.0.x” then you need to upgrade
your mysql client.

If you still have issues with this tomorrow (after 11am EST) feel free
to email me privately and we can setup a chat or call to go over this. I
can always install a RH box same as your server if I don’t already have
one up and send you a precompiled mysql binding. Other people on the
list may be able to send you one as well.

Zach
Michigan Ruby U. Groups (formerly Grand Rapids Ruby U. Group)
http://www.gr-ruby.org

Ben J. wrote:

I copied over my rails project directory to the new server, reset up my
Here is the strange part:
5? The ONLY thing I copied over from the old server was my rails project
directory, the single directory that contains your app, config, db, etc
directories.

Once again any help is greatly appreciated. I am clueless as to what is
causing this and will pay for help if need be.

Thanks for your time.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEm01UMyx0fW1d8G0RApTRAJwJ0s4ZThvNxZZBsz3Yx8Y5wVsG+wCfUz7E
c1xZkKJbCpr7FhtD/aArmLU=
=QNjK
-----END PGP SIGNATURE-----

benjohnson · June 23, 2006, 4:17am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

zdennis wrote:

mysql.so file, so all was well. But the RH box probably doesn’t. So you

if it passes or says “true” or “false” then it loaded the mysql file

now check it’s version.

irb> Mysql
=> Mysql
irb> Mysql.client_info
=> “4.1.15”

If you ruby hasn’t found “mysql” yet then you are using the default
running with rails, you can load this by doing:

irb> require ‘rubygems’
=> true
irb require ‘active_record/vendor/mysql’
=> true
irb>Mysql.client_info
=> “4.0-ruby-0.2.5”

See what I mean, the default with Rails is 4.0.x…

You want it to be 4.1.x. If it says “4.0.x” then you need to upgrade
your mysql client.

Zach
Michigan Ruby U. Groups (formerly Grand Rapids Ruby U. Group)
http://www.gr-ruby.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEm058Myx0fW1d8G0RAoaQAJ42ASwNnPItYOyYwWgRSgcLAeCiMgCfYzBY
MNJTg/9VoneH2oP123VvlgM=
=AFZu
-----END PGP SIGNATURE-----

benjohnson · June 23, 2006, 4:27am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ben J. wrote:

Did I just find a work around or is that a legit fix?

That is a legit fix, the – denotes that you are passing the rest of the
options to the build process (extconf.rb) and not to the gem program.

This is like downloading mysql ruby binding sources files and running
the following to do the build:
ruby extconf.rb --with-mysql-config

but instead you did it through gems. Glad it’s up and working! Now go
pay yourself and have a nice cold Iced Tea.

Zach
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEm1EkMyx0fW1d8G0RAiSbAJ9CHDYEej1POjLGG7LAcD+pdu/+SACfetAZ
BHU973PvJkt9IXiVg0QQQEE=
=5Tvk
-----END PGP SIGNATURE-----

benjohnson · June 23, 2006, 4:22am

The strangest thing:

I did this:

sudo gem install mysql – --with-mysql-config

It installed and all is fine now.

One last question:

Did I just find a work around or is that a legit fix?

benjohnson · June 23, 2006, 4:39am

Thanks a lot for your help, this was probably one of the biggest pains
in the ass. I hope this thread helps other people having the same
problem.

benjohnson · June 23, 2006, 7:34am

On Jun 22, 2006, at 9:22 PM, Ben J. wrote:

Did I just find a work around or is that a legit fix?
It’s both. Stick with the native mysql bindings for superior
compatibility and speed.

jeremy

benjohnson · June 23, 2006, 8:28am

Jeremy K. wrote:

On Jun 22, 2006, at 9:22 PM, Ben J. wrote:

Did I just find a work around or is that a legit fix?
It’s both. Stick with the native mysql bindings for superior
compatibility and speed.

jeremy

I tried everything and the only thing that fixed this was installing
that gem. It was using a different style of password than my mysql was
expecting.