Milimetric has uploaded a new change for review.
https://gerrit.wikimedia.org/r/75114
Change subject: solves the stale connection error by pinging
......................................................................
solves the stale connection error by pinging
(OperationalError) (2006, 'MySQL server has gone away')
A new manual test allows you to check the fix.
Change-Id: Ib10a50f15e6942c2034234631f5b598b0d45195c
---
A tests/cohort_csvs/curious_fails.csv
A tests/cohort_csvs/lowercase_names.csv
A tests/manual/connection_survives_server_restart.py
M wikimetrics/config/db_config.yaml
M wikimetrics/database.py
5 files changed, 314 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/wikimetrics
refs/changes/14/75114/1
diff --git a/tests/cohort_csvs/curious_fails.csv
b/tests/cohort_csvs/curious_fails.csv
new file mode 100644
index 0000000..b304897
--- /dev/null
+++ b/tests/cohort_csvs/curious_fails.csv
@@ -0,0 +1,10 @@
+Dejaun ,commonswiki
+Faux ,commonswiki
+Johnmac1952 ,commonswiki
+Lupicinema ,commonswiki
+Pedro J Pacheco ,commonswiki
+Pymouss ,commonswiki
+Santoan31 ,commonswiki
+Amitoon ,commonswiki
+Revital9 ,commonswiki
+عارف عالي العتيبي ,commonswiki
diff --git a/tests/cohort_csvs/lowercase_names.csv
b/tests/cohort_csvs/lowercase_names.csv
new file mode 100644
index 0000000..d2ca2af
--- /dev/null
+++ b/tests/cohort_csvs/lowercase_names.csv
@@ -0,0 +1,263 @@
+agamitsudo,commonswiki
+christopher.akiki,commonswiki
+Dejaun ,commonswiki
+Faux ,commonswiki
+adius,commonswiki
+akampfer,commonswiki
+alexgyx,commonswiki
+alharbi 111,commonswiki
+angelmed20,commonswiki
+anhkaikei,commonswiki
+arnaudko,commonswiki
+auditoryelements,commonswiki
+benstitch,commonswiki
+biedelcw,commonswiki
+botzzim,commonswiki
+buivanthang,commonswiki
+ccivil,commonswiki
+clarity101,commonswiki
+codybrom,commonswiki
+cygnusloop99,commonswiki
+dano45,commonswiki
+degriec2,commonswiki
+dimacius,commonswiki
+diplo566,commonswiki
+drkcaey,commonswiki
+dungbui,commonswiki
+duydn19,commonswiki
+elipatwood,commonswiki
+eorjs82,commonswiki
+ergelsirt,commonswiki
+fallacy,commonswiki
+federicoiseo,commonswiki
+fjherrero,commonswiki
+gemini79,commonswiki
+glmfe1,commonswiki
+gnurt,commonswiki
+grahama2,commonswiki
+gunhaxx,commonswiki
+harshil2222,commonswiki
+howief,commonswiki
+hvsk,commonswiki
+iggymwangi,commonswiki
+jaumeilla,commonswiki
+jeanlouiszimmermann,commonswiki
+Johnmac1952 ,commonswiki
+josh mcfadden495,commonswiki
+jpaltor,commonswiki
+jvleo,commonswiki
+jwhyb,commonswiki
+k8alberto,commonswiki
+kakature,commonswiki
+kjnopanda,commonswiki
+ktr101,commonswiki
+lcsafar,commonswiki
+lijijinaraj,commonswiki
+lorenzomicciulla,commonswiki
+loristewis,commonswiki
+Lupicinema ,commonswiki
+mahanga,commonswiki
+manishprabhune,commonswiki
+mijns,commonswiki
+mikebaird,commonswiki
+mikkelkrogsholm,commonswiki
+montehurd,commonswiki
+nano nous,commonswiki
+ndainsworth,commonswiki
+ngophuongit,commonswiki
+nguyennhattu,commonswiki
+nicabb,commonswiki
+olivari,commonswiki
+omphteliba,commonswiki
+paoloc77,commonswiki
+Pedro J Pacheco ,commonswiki
+psubhashish,commonswiki
+Pymouss ,commonswiki
+qubex,commonswiki
+rafa espada,commonswiki
+raydela,commonswiki
+Santoan31 ,commonswiki
+scheimann,commonswiki
+selcuki,commonswiki
+snipermatze,commonswiki
+soncdcp,commonswiki
+suarez52,commonswiki
+tdathuynh,commonswiki
+ter-burg,commonswiki
+tfinc,commonswiki
+thanhtuyen371968,commonswiki
+trinhdalat,commonswiki
+uniquex,commonswiki
+vbgoz,commonswiki
+wiDaki,commonswiki
+wikishang,commonswiki
+wrakky,commonswiki
+yukichi99,commonswiki
+zacearly,commonswiki
+zjoppe,commonswiki
+zonnebelt,commonswiki
+zunjine,commonswiki
+rara456852,commonswiki
+victorgrigas,commonswiki
+juancho 250,commonswiki
+vuanhduc bbn,commonswiki
+tvol,commonswiki
+cablegreen,commonswiki
+harynaga,commonswiki
+chrisrickard,commonswiki
+kippelboy,commonswiki
+asalini,commonswiki
+lmrsaigon,commonswiki
+carlourossi,commonswiki
+authueil,commonswiki
+miszamisik,commonswiki
+sry85,commonswiki
+Amitoon ,commonswiki
+moietmonlego,commonswiki
+luankrvip,commonswiki
+voyageur85,commonswiki
+moboHamm,commonswiki
+prwasbw,commonswiki
+diago,commonswiki
+xxrevsxx,commonswiki
+daankemp,commonswiki
+todrobbins,commonswiki
+urashimataro,commonswiki
+kecco69,commonswiki
+hungsalat,commonswiki
+rudegar,commonswiki
+chrijung,commonswiki
+quadell,commonswiki
+mattinbgn,commonswiki
+brclarke,commonswiki
+suddenci,commonswiki
+nilsohman,commonswiki
+groeselt,commonswiki
+grobubu,commonswiki
+phishlite,commonswiki
+flaviusggs,commonswiki
+baglio,commonswiki
+mpalumbopy,commonswiki
+glwx,commonswiki
+robinanninajennifer,commonswiki
+mkckim,commonswiki
+nafoooo,commonswiki
+speedexe,commonswiki
+enigmaticien,commonswiki
+txcrude,commonswiki
+grassi.yannick,commonswiki
+riotluck,commonswiki
+rlldll,commonswiki
+k9taylor,commonswiki
+paxn,commonswiki
+azoma,commonswiki
+wouterkloos,commonswiki
+nsegard,commonswiki
+marmadukewilson,commonswiki
+filipplenoir,commonswiki
+bongilles,commonswiki
+marantoni,commonswiki
+metal.lunchbox,commonswiki
+vjonker,commonswiki
+lacphotodesign,commonswiki
+svantassel,commonswiki
+stonyrun,commonswiki
+shaimon,commonswiki
+biancagazzingan,commonswiki
+nmathey,commonswiki
+oaurelien,commonswiki
+hugolingeman,commonswiki
+qtipcutie,commonswiki
+etnoboris,commonswiki
+ifly6,commonswiki
+darekgt,commonswiki
+gunslingerjh,commonswiki
+mhrini,commonswiki
+joshhhjonesss,commonswiki
+dronir,commonswiki
+jpp44345,commonswiki
+wesfloyd,commonswiki
+mentabolism,commonswiki
+ot,commonswiki
+manticorp,commonswiki
+nickluethi,commonswiki
+unique.creator,commonswiki
+idunius,commonswiki
+stefaniegygax,commonswiki
+jaredzimmerman,commonswiki
+hthai72,commonswiki
+kjetil r,commonswiki
+franckleray,commonswiki
+drdak,commonswiki
+dwaipayanc,commonswiki
+jamourous,commonswiki
+hmanes,commonswiki
+rakuberry,commonswiki
+chuongVU,commonswiki
+jeremy886,commonswiki
+mohamedalhamed,commonswiki
+thecleversheep,commonswiki
+domlav,commonswiki
+ashstar01,commonswiki
+kgbo,commonswiki
+Revital9 ,commonswiki
+coco9nyc,commonswiki
+aaaaalex0180,commonswiki
+onlineprathamesh,commonswiki
+joaocamara,commonswiki
+ambo100,commonswiki
+rockcitytim,commonswiki
+hynuza,commonswiki
+kariinaam,commonswiki
+uaefujmylove,commonswiki
+aveo,commonswiki
+eclectiqus,commonswiki
+danielwilliamleach,commonswiki
+azor shams,commonswiki
+fo2grafen,commonswiki
+guriha,commonswiki
+fotoholic,commonswiki
+maxronnersjo,commonswiki
+senapa,commonswiki
+brianrmurray,commonswiki
+zhangchengyin,commonswiki
+ovidiuboc,commonswiki
+ragesoss,commonswiki
+castlemate,commonswiki
+danzainab,commonswiki
+misterkong,commonswiki
+jdse10,commonswiki
+muratin,commonswiki
+curryme,commonswiki
+mlhurd,commonswiki
+talmoryair,commonswiki
+tjej,commonswiki
+omonchic,commonswiki
+isma90,commonswiki
+jdgs18,commonswiki
+rowanwindwhistler,commonswiki
+kaekae99,commonswiki
+jballeis,commonswiki
+lucypg123,commonswiki
+culinary123,commonswiki
+jemawi,commonswiki
+homonihilis,commonswiki
+judytuna,commonswiki
+padaguan,commonswiki
+anthere,commonswiki
+bewzazz,commonswiki
+conanxin,commonswiki
+jonathanrcarter,commonswiki
+عارف عالي العتيبي ,commonswiki
+falkirks,commonswiki
+quentinmanuel2510,commonswiki
+energizer110,commonswiki
+poterok,commonswiki
+lenbys,commonswiki
+dial6titan,commonswiki
+andrewrabbott,commonswiki
+padlifadly,commonswiki
+masimoes,commonswiki
+arundpt,commonswiki
+carulja,commonswiki
+amanjeev,commonswiki
diff --git a/tests/manual/connection_survives_server_restart.py
b/tests/manual/connection_survives_server_restart.py
new file mode 100644
index 0000000..6c7cd6e
--- /dev/null
+++ b/tests/manual/connection_survives_server_restart.py
@@ -0,0 +1,15 @@
+from wikimetrics.configurables import db
+
+db.get_session().close()
+db.get_session().close()
+db.get_session().close()
+
+# pool size is now three.
+
+print "Restart the server"
+raw_input()
+
+for i in xrange(10):
+ c = db.get_session()
+ print c.execute("select 1").fetchall()
+ c.close()
diff --git a/wikimetrics/config/db_config.yaml
b/wikimetrics/config/db_config.yaml
index c483730..dfd0624 100644
--- a/wikimetrics/config/db_config.yaml
+++ b/wikimetrics/config/db_config.yaml
@@ -1,3 +1,5 @@
SQL_ECHO : False
WIKIMETRICS_ENGINE_URL : 'sqlite:///test.db'
+# For testing with mysql locally (useful for manual connection survival tests)
+#WIKIMETRICS_ENGINE_URL :
'mysql://wikimetrics:wikimetrics@localhost/wikimetrics'
MEDIAWIKI_ENGINE_URL_TEMPLATE : 'sqlite:///{0}.db'
diff --git a/wikimetrics/database.py b/wikimetrics/database.py
index a73617b..dc514aa 100644
--- a/wikimetrics/database.py
+++ b/wikimetrics/database.py
@@ -11,6 +11,9 @@
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy import exc
+from sqlalchemy import event
+from sqlalchemy.pool import Pool
__all__ = [
'Database',
@@ -151,3 +154,24 @@
project_host_map = json.load(open(cache_name))
return project_host_map
+
+
[email protected]_for(Pool, "checkout")
+def ping_connection(dbapi_connection, connection_record, connection_proxy):
+ """
+ Pings the connection on checkout, making sure that it hasn't gone stale.
+ This prevents error (OperationalError) (2006, 'MySQL server has gone
away').
+ This fix can be tested with
tests/manual/connection_survives_server_restart.py
+ """
+ cursor = dbapi_connection.cursor()
+ try:
+ cursor.execute("SELECT 1")
+ except:
+ # optional - dispose the whole pool
+ # instead of invalidating one at a time
+ # connection_proxy._pool.dispose()
+
+ # raise DisconnectionError - pool will try
+ # connecting again up to three times before raising.
+ raise exc.DisconnectionError()
+ cursor.close()
--
To view, visit https://gerrit.wikimedia.org/r/75114
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ib10a50f15e6942c2034234631f5b598b0d45195c
Gerrit-PatchSet: 1
Gerrit-Project: analytics/wikimetrics
Gerrit-Branch: master
Gerrit-Owner: Milimetric <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
