Прерывистые проблемы с подключением при работе с колбами и MySQL на Apache
У меня странная проблема, которую мне не удается отладить должным образом.
- У меня есть сценарий Python3, который использует флак-restless, чтобы выставить части базы данных MySQL на другом сервере в виде JSON
- Он отлично работает при вызове локального тестирующего веб-сервера во Flask, но при развертывании под Apache время от времени выдает ошибки (около 50% попыток). Сообщение в Apache error.log: (полная трассировка стека ниже)
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available. [SQL: 'SELECT count(*) AS count_1 \\nFROM gppt_submissions'] [parameters: [{}]]
Насколько я понимаю, экземпляру SQLalchemy не удается подключиться к серверу MySQL, но я не понимаю, почему - тем более, что ошибка не согласована. И я не понимаю, почему приведенный выше оператор SQL вызывается.
У меня вопрос, если кто-то с большим опытом из этого видел подобную ошибку, и какие действия по разрешению вы предприняли?
Мой скрипт ниже
import logging
FORMAT = '%(asctime)s - %(name)s - %(levelname)s - %(message)s'
logging.basicConfig(filename="PhoenixAPI.log", format=FORMAT, level=logging.DEBUG)
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import flask_restless
from credentials import DBcreds
import simplejson as json
def postprocessor(data):
json.dumps(data, use_decimal=True)
api_app = Flask(__name__)
logging.info("Flask app created")
# Create connection string, using credentials in DBcreds.py in folder credentials
connstring_gppt = 'mysql+mysqlconnector://' + \
DBcreds.gppt_db['username'] + ':' + \
DBcreds.gppt_db['password'] + '@' + \
DBcreds.gppt_db['host'] + ':' + \
'3306' + '/' + \
DBcreds.gppt_db['database']
connstring_csi = 'mysql+mysqlconnector://' + \
DBcreds.csi_db['username'] + ':' + \
DBcreds.csi_db['password'] + '@' + \
DBcreds.csi_db['host'] + ':' + \
'3306' + '/' + \
DBcreds.csi_db['database']
# Connect to database and hook SQLAlchemy to it
api_app.config['SQLALCHEMY_DATABASE_URI'] = connstring_csi # Default bind referred to as 'None'
api_app.config['SQLALCHEMY_BINDS'] = {'gppt_db': connstring_gppt}
api_app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(api_app)
logging.info("Connection to database established")
# Create metadata from existing database schema
db.reflect(bind='gppt_db')
logging.info("GPPT Database metadata reflected")
db.reflect(bind=None)
logging.info("CSI database metadata reflected")
# Define GPPT submission model
class GPPTSubmission(db.Model):
__bind_key__ = 'gppt_db'
__tablename__ = 'gppt_submissions'
logging.info("Model for gppt_submissions created")
# Define CSI project model
class Project(db.Model):
__bind_key__ = None
__tablename__ = 'projects'
logging.info("Model for CSI projects created")
# Define CSI answer model
class Answer(db.Model):
__bind_key__ = None
__tablename__ = 'answers'
logging.info("Model for CSI answers created")
# Define CSI ratings model (midway table)
class Rating(db.Model):
__bind_key__ = None
__tablename__ = 'ratings'
logging.info("Model for CSI ratings created")
# Apply model and instantiate ORM
db.create_all(bind='gppt_db')
db.create_all(bind=None)
logging.info("Database models applied")
# Create GPPT API and delimit transferring the actual binary file data
manager = flask_restless.APIManager(api_app, flask_sqlalchemy_db=db)
manager.create_api(GPPTSubmission, methods=['GET'], exclude_columns=['Attachment_Binary'])
logging.info('Endpoint "<base_uri>/api/gppt_submissions" set up')
# Create CSI API and select fields to be exposed
csi_project_fields = ['projectId', 'region', 'office', 'customerName', 'subProjectNo', 'pmName', 'pmLastName',
'question', 'dateUpload']
csi_answers_fields = ['ratingId', ' answerId', 'dateAnswer', 'questionId', 'answersNumeric', 'answersText']
csi_ratings_fields = ['ratingId', 'projectId']
manager.create_api(Project, methods=['GET'], include_columns=csi_project_fields)
manager.create_api(Answer, methods=['GET'], include_columns=csi_answers_fields)
manager.create_api(Rating, methods=['GET'], include_columns=csi_ratings_fields)
logging.info('Endpoints "<base_uri>/api/projects", "<base_uri>/api/answers", "<base_uri>/api/ratings" set up')
Apache error.log stacktrace:
[Mon Nov 13 21:34:47.974438 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 821, in create_cursor
[Mon Nov 13 21:34:47.974452 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return self._dbapi_connection.cursor()
[Mon Nov 13 21:34:47.974454 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 926, in cursor
[Mon Nov 13 21:34:47.974455 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return self.connection.cursor(*args, **kwargs)
[Mon Nov 13 21:34:47.974457 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 809, in cursor
[Mon Nov 13 21:34:47.974459 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] raise errors.OperationalError("MySQL Connection not available.")
[Mon Nov 13 21:34:47.974461 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] mysql.connector.errors.OperationalError: MySQL Connection not available.
[Mon Nov 13 21:34:47.974463 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port]
[Mon Nov 13 21:34:47.974464 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] The above exception was the direct cause of the following exception:
[Mon Nov 13 21:34:47.974466 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port]
[Mon Nov 13 21:34:47.974468 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] Traceback (most recent call last):
[Mon Nov 13 21:34:47.974469 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1982, in wsgi_app
[Mon Nov 13 21:34:47.974471 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] response = self.full_dispatch_request()
[Mon Nov 13 21:34:47.974473 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1614, in full_dispatch_request
[Mon Nov 13 21:34:47.974475 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] rv = self.handle_user_exception(e)
[Mon Nov 13 21:34:47.974476 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1517, in handle_user_exception
[Mon Nov 13 21:34:47.974478 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] reraise(exc_type, exc_value, tb)
[Mon Nov 13 21:34:47.974480 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/_compat.py", line 33, in reraise
[Mon Nov 13 21:34:47.974482 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] raise value
[Mon Nov 13 21:34:47.974483 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1612, in full_dispatch_request
[Mon Nov 13 21:34:47.974485 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] rv = self.dispatch_request()
[Mon Nov 13 21:34:47.974487 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1598, in dispatch_request
[Mon Nov 13 21:34:47.974489 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return self.view_functions[rule.endpoint](**req.view_args)
[Mon Nov 13 21:34:47.974491 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask_restless/views.py", line 157, in decorator
[Mon Nov 13 21:34:47.974492 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return func(*args, **kw)
[Mon Nov 13 21:34:47.974494 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/mimerender.py", line 244, in wrapper
[Mon Nov 13 21:34:47.974499 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] result = target(*args, **kwargs)
[Mon Nov 13 21:34:47.974501 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/views.py", line 84, in view
[Mon Nov 13 21:34:47.974503 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return self.dispatch_request(*args, **kwargs)
[Mon Nov 13 21:34:47.974505 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask/views.py", line 149, in dispatch_request
[Mon Nov 13 21:34:47.974506 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return meth(*args, **kwargs)
[Mon Nov 13 21:34:47.974508 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask_restless/views.py", line 189, in wrapped
[Mon Nov 13 21:34:47.974510 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return func(*args, **kw)
[Mon Nov 13 21:34:47.974512 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask_restless/views.py", line 1239, in get
[Mon Nov 13 21:34:47.974513 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return self._search()
[Mon Nov 13 21:34:47.974515 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask_restless/views.py", line 1194, in _search
[Mon Nov 13 21:34:47.974517 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] result = self._paginated(result, deep)
[Mon Nov 13 21:34:47.974519 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask_restless/views.py", line 963, in _paginated
[Mon Nov 13 21:34:47.974520 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] num_results = count(self.session, instances)
[Mon Nov 13 21:34:47.974522 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/flask_restless/helpers.py", line 594, in count
[Mon Nov 13 21:34:47.974524 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] num_results = session.execute(counts.order_by(None)).scalar()
[Mon Nov 13 21:34:47.974526 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/scoping.py", line 157, in do
[Mon Nov 13 21:34:47.974528 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return getattr(self.registry(), name)(*args, **kwargs)
[Mon Nov 13 21:34:47.974529 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/session.py", line 1139, in execute
[Mon Nov 13 21:34:47.974531 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] bind, close_with_result=True).execute(clause, params or {})
[Mon Nov 13 21:34:47.974533 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
[Mon Nov 13 21:34:47.974535 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return meth(self, multiparams, params)
[Mon Nov 13 21:34:47.974536 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
[Mon Nov 13 21:34:47.974538 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return connection._execute_clauseelement(self, multiparams, params)
[Mon Nov 13 21:34:47.974543 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
[Mon Nov 13 21:34:47.974545 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] compiled_sql, distilled_params
[Mon Nov 13 21:34:47.974547 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1121, in _execute_context
[Mon Nov 13 21:34:47.974548 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] None, None)
[Mon Nov 13 21:34:47.974550 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
[Mon Nov 13 21:34:47.974552 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] exc_info
[Mon Nov 13 21:34:47.974554 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
[Mon Nov 13 21:34:47.974555 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] reraise(type(exception), exception, tb=exc_tb, cause=cause)
[Mon Nov 13 21:34:47.974557 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
[Mon Nov 13 21:34:47.974559 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] raise value.with_traceback(tb)
[Mon Nov 13 21:34:47.974561 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1116, in _execute_context
[Mon Nov 13 21:34:47.974563 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] context = constructor(dialect, self, conn, *args)
[Mon Nov 13 21:34:47.974564 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 598, in _init_compiled
[Mon Nov 13 21:34:47.974566 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] self.cursor = self.create_cursor()
[Mon Nov 13 21:34:47.974568 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 821, in create_cursor
[Mon Nov 13 21:34:47.974570 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return self._dbapi_connection.cursor()
[Mon Nov 13 21:34:47.974571 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 926, in cursor
[Mon Nov 13 21:34:47.974573 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] return self.connection.cursor(*args, **kwargs)
[Mon Nov 13 21:34:47.974575 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 809, in cursor
[Mon Nov 13 21:34:47.974577 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] raise errors.OperationalError("MySQL Connection not available.")
[Mon Nov 13 21:34:47.974581 2017] [wsgi:error] [pid 25620:tid 140701890746112] [remote IP:port] sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available. [SQL: 'SELECT count(*) AS count_1 \\nFROM gppt_submissions'] [parameters: [{}]]