Better Recovery from Mysql/SQLAlchemy error 2006

Issue #318 resolved
Ton Plomp created an issue

As seen <<issue 139>> sqlalchemy throws an error after a night of non-operation. I''ve changed the ini file to the following contents:

===================

sqlalchemy.db1.url = mysql://rhodecode:*@localhost/rhodecode

sqlalchemy.db1.echo = false

sqlalchemy.db1.pool_recycle = 180

sqlalchemy.convert_unicode = true

====================

Even this setting of 3! minutes does not prevent an error 500 on the (web) client. Here's an excerpt from my error.log:

====================

Error - <class 'sqlalchemy.exc.OperationalError'>: (OperationalError) (2006, 'MySQL server has gone away') 'SELECT users.user_id AS users_user_id, users.username AS users_username, users.password AS users_password, users.active AS users_active, users.admin AS users_admin, users.name AS users_name, users.lastname AS users_lastname, users.email AS users_email, users.last_login AS users_last_login, users.ldap_dn AS users_ldap_dn, users.api_key AS users_api_key \nFROM users \nWHERE users.username = %s' ('default',)

Error - <class 'sqlalchemy.exc.OperationalError'>: (OperationalError) (2006, 'MySQL server has gone away') 'SELECT users.user_id AS users_user_id, users.username AS users_username, users.password AS users_password, users.active AS users_active, users.admin AS users_admin, users.name AS users_name, users.lastname AS users_lastname, users.email AS users_email, users.last_login AS users_last_login, users.ldap_dn AS users_ldap_dn, users.api_key AS users_api_key \nFROM users \nWHERE users.username = %s' ('default',)

Error - <class 'sqlalchemy.exc.StatementError'>: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT users.user_id AS users_user_id, users.username AS users_username, users.password AS users_password, users.active AS users_active, users.admin AS users_admin, users.name AS users_name, users.lastname AS users_lastname, users.email AS users_email, users.last_login AS users_last_login, users.ldap_dn AS users_ldap_dn, users.api_key AS users_api_key \nFROM users \nWHERE users.username = %s' [immutabledict({})]

2011-12-06 07:33:04.474 WARNI [rhodecode.lib.auth] Permission denied for <AuthUser('id:1:default|True')> @ access admin main page

2011-12-06 07:33:06.078 INFO [paste.httpserver.ThreadPool] kill_hung_threads status: 5 threads (0 working, 5 idle, 0 starting) ave time N/A, max time 0.00sec, killed 0 workers

2011-12-06 07:33:06.904 INFO [rhodecode.lib.auth] user ton authenticated correctly

=======================

The first two errors are from a mercurial client trying to execute 'incoming', those resulted in 'error 500'. Only reconnecting through a webbrowser resolved the issue.

I'm not sure if it is possible to better recover from this specific error, for instance a reconnect in the code?

Comments (23)

  1. Marcin Kuzminski repo owner

    Each web-call is wrapped in Session cleanup call at the end of each request `meta.Session.remove()` The GIT and HG middlewares doesn't have this. Proposed attached patch fixes that by adding it to each call for git or hg protocol middlewares

    Can you apply patch to your codes, and check ?

  2. Ton Plomp reporter

    I'm running the site with version 1.2.3, and couldn't easliy use the patch for that branch, can I copy the changed files form the beta-branch-with-patch to the codebase of 1.2.3?

    And I see two 318-2 patches, which should I choose?

  3. Marc Sanfa├žon

    BTW, we still have this issue happening almost everyday. I will apply the patch and see if this fixes it.

    Thanks, Marc

  4. Matt Zuba

    We're having this issue as well. I get us updated to the beta branch tonight and apply the patch and report back in a few days.

  5. Matt Zuba

    Wasn't able to successfully upgrade tonight. The code that upgrades the database for v1.3.0 isn't complete so the database isn't updated causing v1.3.0 to not work when upgrading from v1.2.3. Step_4 is missing from the upgrade class, as is code that updates the schema and adds new tables.

  6. Marc Sanfa├žon

    I also tried to apply the patch to the 1.2.3 code and it did not work. I tried to apply it manually, but the code is too different.

    1- Do you have the patch for 1.2.3? 2- When do you plan to release 1.3.0?

    Thanks

  7. Marcin Kuzminski repo owner

    @mattzuba migrations are not done, i do this always before relesing next major thing as a last step

    @striker69 not ETA on 1.3 depends on many things patch for 1.2.3 i'll see what i can do

  8. Matt Zuba

    Would the attached patch for v1.2.3 work as a quick fix to test if the base idea of wrapping the call in a session cleanup helps? All of the other changes I can see in the patch you posted are long term type fixes, but to test if it really works, I think the attached should be sufficient.

  9. Marcin Kuzminski repo owner

    @mattzuba yes the patch provided by you does the same thing that mine changes. It can be used for testing.

  10. Ton Plomp reporter

    I've installed the fix on our 1.2.3 installation. Do I need to compile the .py files?

    If not I can hopefully confirm this friday that is works like expected.

  11. Matt Zuba

    After sitting for the holiday weekend and testing yesterday morning and this morning, we have not had the issue re-occur.

  12. Log in to comment