Connection timed out - mysql

[Marcello]

Hello,

I'm having "Connection timed out" to mysql server.

I'm creating a sheet from some queries. The function is in a module, and running it from console.

Sometimes, it goes OK, but sometimes I get the error (see below).

I'm using EC2 and the database is in RDS.

Thanks for any help...

Marcello

-----

Traceback (most recent call last):

  File "/home/tecno2/web2py/gluon/shell.py", line 206, in run

    execfile(startfile, _env)

  File "applications/segundarj/private/diario.py", line 2, in <module>

    planilha.roda(True)

  File "applications/segundarj/modules/planilha.py", line 64, in roda

    print "aba: %s, total: %s" % (aba,processos.count())

  File "/home/tecno2/web2py/gluon/dal.py", line 7573, in count

    return self.db._adapter.count(self.query,distinct)

  File "/home/tecno2/web2py/gluon/dal.py", line 1339, in count

    self.execute(self._count(query, distinct))

  File "/home/tecno2/web2py/gluon/dal.py", line 1392, in execute

    return self.log_execute(*a, **b)

  File "/home/tecno2/web2py/gluon/dal.py", line 1386, in log_execute

    ret = self.cursor.execute(*a, **b)

  File "/home/tecno2/web2py/gluon/contrib/pymysql/cursors.py", line 108, in execute

    self.errorhandler(self, exc, value)

  File "/home/tecno2/web2py/gluon/contrib/pymysql/connections.py", line 182, in defaulterrorhandler

    raise Error(errorclass, errorvalue)

Error: (<class 'socket.error'>, error(110, 'Connection timed out'))

Traceback (most recent call last):

  File "web2py.py", line 20, in <module>

    gluon.widget.start(cron=True)

  File "/home/tecno2/web2py/gluon/widget.py", line 868, in start

    import_models=options.import_models, startfile=options.run)

  File "/home/tecno2/web2py/gluon/shell.py", line 210, in run

    if import_models: BaseAdapter.close_all_instances('rollback')

  File "/home/tecno2/web2py/gluon/dal.py", line 432, in close_all_instances

    getattr(instance, action)()

  File "/home/tecno2/web2py/gluon/dal.py", line 1357, in rollback

    return self.connection.rollback()

  File "/home/tecno2/web2py/gluon/contrib/pymysql/connections.py", line 571, in rollback

    self.errorhandler(None, exc, value)

  File "/home/tecno2/web2py/gluon/contrib/pymysql/connections.py", line 182, in defaulterrorhandler

    raise Error(errorclass, errorvalue)

gluon.contrib.pymysql.err.Error: (<class 'socket.error'>, error(32, 'Broken pipe'))

[AbrahamLinksys]

I get these broken pipe errors almost daily now, usually in the wee hours of the morning (between 1-5am). I'll get one or two, or sometimes like 5, but probably not more than say 10/day (on one page that pulls four images from the DB, i will get 4 at once), and it doesn't happen every day.  

I am storing images (<100KB) in the database, but I'm not sure if that's it. I'm also hosting this on VMs (one for web2py, one for mysql DB server), and a few months ago I had never seen this error. It might be something to do with the VM setup but I'm really not sure. 

From what I understand, broken pipe happens at the OS level and is something to do with transferring information from the DB server... sound right?

Also, (separate issue, possibly related?) sometimes the website becomes unresponsive, and I get errors to do with base64 decoding and assertion errors:

 File "/var/www/web2py/gluon/dal.py", line 5481, in select
    return self.db._adapter.select(self.query,fields,attributes)
  File "/var/www/web2py/gluon/dal.py", line 1192, in select
    return self.parse(rows,self._colnames)
  File "/var/www/web2py/gluon/dal.py", line 1421, in parse
    colset[fieldname] = base64.b64decode(str(value))
  File "/usr/lib64/python2.6/base64.py", line 76, in b64decode
    raise TypeError(msg)
TypeError: Incorrect padding

and : 

Error: (<type 'exceptions.AssertionError'>, AssertionError('Result length not requested length:\nExpected=97.  Actual=31.  Position: 299.  Data Length: 330',))

The odd thing is that I can do these queries via mysql command line, even when running mysql on the webserver to connect to the DB host, and they return instantly... although I guess I'm not sure if the assertion error comes from MySQL or is part of web2py's DB adapter?

We used wireshark to determine that the TCP packets were being incessantly retransmitted ... but only sometimes. The Padding/Assertion Errors seem to be related to VMs since migrating the machine to a different physical host and back resolves the issue immediately. 

Also, restarting httpd tends to lessen the amount of Broken Pipe errors I see, so perhaps it's some sort of stale connection to the DB?

-Abe

[Massimo Di Pierro]

The Assertion error comes from pymysql which ships with web2py. I just upgraded trunk to a newer version. Give it a try. It may (or my not) resolve the problem. You may want to ask the pymysql mailing list for an explanations of the error.

[Marcello Parra]

My "Connection timed out" problem continues with trunk also.

I also testes the same queries using Django. No problem.

I tested many times.

[Massimo Di Pierro]

To narrow down the problem. Django uses mysqldb, not pymysql. Could you try switch to mysqldb with web2py? 

import mysqldb

from gluon.dal import MySQLAdapter

MySQLAdapter.driver = mysqldb

db=DAL('mysql://....')

Anyway, it is hard to know for sure without looking at your code. For example web2py does connection pooling and may have different settings. It could be a threading issue since you use it in modules. 

Which version of web2py are you using?

Massimo

[Marcello Parra]

Massimo,

Yes. You are right.

With mysqldb worked fine.

Is there any problem to use mysqldb ??

[Massimo Di Pierro]

No but this should be reported to the pysqldb folks.

Would you mind opening an issue there?

massimo

[Marcello Parra]

No.

I'll do that.

Thanks again.