error connecting to remote mysql database

[Richard de Koning]

I'm pretty new using sqlalchemy and I will probably ask some smart and
a lot of silly questions.

At the moment I'm having problems with connecting to remote MySQL
databases. If I run the updates on a local database everything works
fine.

When I try to connect to a remote database I keep getting the
following errors:

File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 142, in
connect
return _ConnectionFairy(self).checkout()
File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 304, in
__init__
rec = self._connection_record = pool.get()
File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 161, in
get
return self.do_get()
File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 639, in
do_get
con = self.create_connection()
File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 122, in
create_connection
return _ConnectionRecord(self)
File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 198, in
__init__
self.connection = self.__connect()
File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 261, in
__connect
connection = self.__pool._creator()
File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/strategies.py",
line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
OperationalError: (OperationalError) (2003, "Can't connect to MySQL
server on 'xxx.xxx.xxx.xxx' (111)") None None

I checked the userrights. Tried with root and installed sqlalchemy and
python-bindings for mysql on the server. I also tried on several
servers with different OS-versions, but I can't see anything wrong.
Manual logins with mysql-client to the servers work.

Can anybody give any pointers how to solve this?

[Richard de Koning]

I've done some troubleshooting and these are my preliminary
conclusions.

A ssh-tunnel is used to reach the remote MySQL database server that
only runs on 127.0.0.1 (localhost).
Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
port 3306.

When I connect with MySQLdb.connect and create an normal SQL-
statement from a Python-script it works as it should.
sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
lname)

So the problem must be somewhere in Sqlalchemy. Maybe it has anything
to do with 'localhost' somewhere in the code . When I initially ran
the query on port 3307 the data was inserted in the local development-
database that was running on the standard MySQL 3306 port. I'll use
the normal SQL as a workaround, but I would prefer to do it in SA.

Is this a known issue or should I report this as a bug?

[Mariano Mara]

Excerpts from Richard de Koning's message of Thu Mar 25 15:50:45 -0300 2010:

> I've done some troubleshooting and these are my preliminary
> conclusions.
>
> A ssh-tunnel is used to reach the remote MySQL database server that
> only runs on 127.0.0.1 (localhost).
> Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
> port 3306.
>
> When I connect with MySQLdb.connect and create an normal SQL-
> statement from a Python-script it works as it should.
> sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
> lname)
>
> So the problem must be somewhere in Sqlalchemy. Maybe it has anything
> to do with 'localhost' somewhere in the code . When I initially ran
> the query on port 3307 the data was inserted in the local development-
> database that was running on the standard MySQL 3306 port. I'll use
> the normal SQL as a workaround, but I would prefer to do it in SA.
>
> Is this a known issue or should I report this as a bug?
>

I'm developing a little pylons application and I'm using a mysql
database. Like you, I'm using a ssh tunnel with port forwarding. My
connection is working ok with SqlAlchemy.

This is my connection string (I would start looking from there):
sqlalchemy.url =
mysql://user:pass...@127.0.0.1:63333/database

As you can see I'm using port 63333 instead of 3307 as you mentioned.

Mariano

[Richard de Koning]

That's strange. I have the same connection-string, but for me it isn't
working after that.

In the original script I initially connect to another database,
transform the data and then create a new session to insert it in
another database. I close the 1th session before creating another.

>
> I'm developing a little pylons application and I'm using a mysql
> database. Like you, I'm using a ssh tunnel with port forwarding. My
> connection is working ok with SqlAlchemy.
>
> This is my connection string (I would start looking from there):
> sqlalchemy.url =

> mysql://user:passw...@127.0.0.1:63333/database

[Michael Bayer]

Richard de Koning wrote:
> I've done some troubleshooting and these are my preliminary
> conclusions.
>
> A ssh-tunnel is used to reach the remote MySQL database server that
> only runs on 127.0.0.1 (localhost).
> Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
> port 3306.
>
> When I connect with MySQLdb.connect and create an normal SQL-
> statement from a Python-script it works as it should.
> sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
> lname)
>
> So the problem must be somewhere in Sqlalchemy. Maybe it has anything
> to do with 'localhost' somewhere in the code . When I initially ran
> the query on port 3307 the data was inserted in the local development-
> database that was running on the standard MySQL 3306 port. I'll use
> the normal SQL as a workaround, but I would prefer to do it in SA.
>
> Is this a known issue or should I report this as a bug?

SQLAlchemy calls MySQLdb.connect directly with the parameters you give it,
so nothing on SQLA end. you would have to share with us the exact
parameters you pass to MySQLdb.connect, versus those you pass to
create_engine(), and the difference will be apparent.

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Conor]

Richard de Koning wrote:
> I've done some troubleshooting and these are my preliminary
> conclusions.
>
> A ssh-tunnel is used to reach the remote MySQL database server that
> only runs on 127.0.0.1 (localhost).
> Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
> port 3306.
>
> When I connect with MySQLdb.connect and create an normal SQL-
> statement from a Python-script it works as it should.
> sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
> lname)
>
> So the problem must be somewhere in Sqlalchemy. Maybe it has anything
> to do with 'localhost' somewhere in the code . When I initially ran
> the query on port 3307 the data was inserted in the local development-
> database that was running on the standard MySQL 3306 port. I'll use
> the normal SQL as a workaround, but I would prefer to do it in SA.
>
> Is this a known issue or should I report this as a bug?
>
>

My experience has been that the MySQL client library interprets
'localhost' to use the local UNIX socket (e.g. /var/run/mysql/...)
instead of TCP/IP. I would recommend using '127.0.0.1' or equivalent as
the host.

-Conor

[Richard de Koning]

You were all right.

I created some functions and one of them creates a connect string from
parameters read from a config file. One of these is the database
port. When I checked the function in the external library I saw that
I didn't include it in the return.
mysql://user:pass...@127.0.0.1/database

Because I use it for a few months now without problems I assumed there
were no problems with it because I always used default ports. Fixed it
and it works like sunshine :-)

Thanks for all your input. I couldn't see the trees through the forest
anymore, as we say in my country.