Tcp provider error code 0x68 104

We are running an Flask application (based on flask_appbuilder) in production with mssql as the database backend. It would seem that the mssql backend is a bit unstable and leads to TCP errors such as

Due to back-ends instability, the CRUD application on the application continue to fail with following traceback:

2020-10-04 17:41:59,743:INFO:sqlalchemy.engine.base.Engine:('4',)
2020-10-04 17:41:59,744:INFO:sqlalchemy.pool.impl.QueuePool:Invalidate connection <pyodbc.Connection object at 0x7f3c8abe3110> (reason: OperationalError:('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)'))
2020-10-04 17:41:59,744:DEBUG:sqlalchemy.pool.impl.QueuePool:Closing connection <pyodbc.Connection object at 0x7f3c8abe3110>
2020-10-04 17:41:59,744:ERROR:app:Exception on /eclscenario/edit/6 [POST]
Traceback (most recent call last):
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/venvlib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/venvlib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/venvlib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/venvlib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/venvlib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/venvlib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/venvlib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
    return f(self, *args, **kwargs)
  File "/mnt/c/users/harshitgupta/source/repos2/MyAPP/MyAPP/app/views.py", line 34547, in edit
    widgets = self._edit_MyAPP(pk)
  File "/mnt/c/users/harshitgupta/source/repos2/MyAPP/MyAPP/app/views.py", line 34581, in _edit_MyAPP
    form = self.edit_form.refresh(request.form)
  File "/venvlib/python3.8/site-packages/flask_appbuilder/forms.py", line 329, in refresh
    form = self(obj=obj)
  File "/venvlib/python3.8/site-packages/wtforms/form.py", line 208, in __call__
    return type.__call__(cls, *args, **kwargs)
  File "/venvlib/python3.8/site-packages/flask_wtf/form.py", line 87, in __init__
    super(FlaskForm, self).__init__(formdata=formdata, **kwargs)
  File "/venvlib/python3.8/site-packages/wtforms/form.py", line 274, in __init__
    self.process(formdata, obj, data=data, **kwargs)
  File "/venvlib/python3.8/site-packages/wtforms/form.py", line 131, in process
    field.process(formdata)
  File "/venvlib/python3.8/site-packages/wtforms/fields/core.py", line 310, in process
    self.process_formdata(self.raw_data)
  File "/venvlib/python3.8/site-packages/flask_appbuilder/fields.py", line 76, in process_formdata
    self.data = self.datamodel.get(valuelist[0])
  File "/venvlib/python3.8/site-packages/sqlalchemy/orm/query.py", line 1018, in get
    return self._get_impl(ident, loading.load_on_pk_identity)
  File "/venvlib/python3.8/site-packages/sqlalchemy/orm/query.py", line 1135, in _get_impl
    return db_load_fn(self, primary_key_identity)
  File "/venvlib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 286, in load_on_pk_identity
    return q.one()
  File "/venvlib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3463, in one
    ret = self.one_or_none()
  File "/venvlib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3432, in one_or_none
    ret = list(self)
  File "/venvlib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3508, in __iter__
    return self._execute_and_instances(context)
  File "/venvlib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3533, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/venvlib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/venvlib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/venvlib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')
[SQL: SELECT [Master_Entity_Master].created_on AS [Master_Entity_Master_created_on], [Master_Entity_Master].changed_on AS [Master_Entity_Master_changed_on], [Master_Entity_Master].id AS [Master_Entity_Master_id], [Master_Entity_Master].from_date AS [Master_Entity_Master_from_date], [Master_Entity_Master].entity_name AS [Master_Entity_Master_entity_name], [Master_Entity_Master].entity_code AS [Master_Entity_Master_entity_code], [Master_Entity_Master].dimension_type_code AS [Master_Entity_Master_dimension_type_code], [Master_Entity_Master].parent_code AS [Master_Entity_Master_parent_code], [Master_Entity_Master].import_source AS [Master_Entity_Master_import_source], [Master_Entity_Master].created_by_fk AS [Master_Entity_Master_created_by_fk], [Master_Entity_Master].changed_by_fk AS [Master_Entity_Master_changed_by_fk]
FROM [Master_Entity_Master]
WHERE [Master_Entity_Master].id = ?]
[parameters: ('4',)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
2020-10-04 17:41:59,771:ERROR:app:Server Error: 500 Internal Server Error: The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.
2020-10-04 17:41:59,775:DEBUG:sqlalchemy.pool.impl.QueuePool:Connection <pyodbc.Connection object at 0x7f3c582282d0> being returned to pool
2020-10-04 17:41:59,780:DEBUG:sqlalchemy.pool.impl.QueuePool:Connection <pyodbc.Connection object at 0x7f3c582282d0> rollback-on-return, via agent
2020-10-04 17:41:59,780:INFO:sqlalchemy.engine.base.Engine:ROLLBACK

RETRY:

2020-10-04 17:44:02,753:ERROR:app:Exception on /eclscenario/edit/6 [POST]
Traceback (most recent call last):
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1202, in _execute_context
    conn = self._revalidate_connection()
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 469, in _revalidate_connection
    raise exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
    return f(self, *args, **kwargs)
  File "/venvMyAPP/MyAPP/app/views.py", line 34547, in edit
    widgets = self._edit_MyAPP(pk)
  File "/venvMyAPP/MyAPP/app/views.py", line 34581, in _edit_MyAPP
    form = self.edit_form.refresh(request.form)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask_appbuilder/forms.py", line 329, in refresh
    form = self(obj=obj)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/wtforms/form.py", line 208, in __call__
    return type.__call__(cls, *args, **kwargs)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask_wtf/form.py", line 87, in __init__
    super(FlaskForm, self).__init__(formdata=formdata, **kwargs)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/wtforms/form.py", line 274, in __init__
    self.process(formdata, obj, data=data, **kwargs)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/wtforms/form.py", line 131, in process
    field.process(formdata)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/wtforms/fields/core.py", line 310, in process
    self.process_formdata(self.raw_data)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/flask_appbuilder/fields.py", line 76, in process_formdata
    self.data = self.datamodel.get(valuelist[0])
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 1018, in get
    return self._get_impl(ident, loading.load_on_pk_identity)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 1135, in _get_impl
    return db_load_fn(self, primary_key_identity)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 286, in load_on_pk_identity
    return q.one()
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3463, in one
    ret = self.one_or_none()
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3432, in one_or_none
    ret = list(self)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3508, in __iter__
    return self._execute_and_instances(context)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3533, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1206, in _execute_context
    self._handle_dbapi_exception(
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1202, in _execute_context
    conn = self._revalidate_connection()
  File "/venvMyAPP/MyAPP/kvenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 469, in _revalidate_connection
    raise exc.InvalidRequestError(
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
[SQL: SELECT [Master_Entity_Master].created_on AS [Master_Entity_Master_created_on], [Master_Entity_Master].changed_on AS [Master_Entity_Master_changed_on], [Master_Entity_Master].id AS [Master_Entity_Master_id], [Master_Entity_Master].from_date AS [Master_Entity_Master_from_date], [Master_Entity_Master].entity_name AS [Master_Entity_Master_entity_name], [Master_Entity_Master].entity_code AS [Master_Entity_Master_entity_code], [Master_Entity_Master].dimension_type_code AS [Master_Entity_Master_dimension_type_code], [Master_Entity_Master].parent_code AS [Master_Entity_Master_parent_code], [Master_Entity_Master].import_source AS [Master_Entity_Master_import_source], [Master_Entity_Master].created_by_fk AS [Master_Entity_Master_created_by_fk], [Master_Entity_Master].changed_by_fk AS [Master_Entity_Master_changed_by_fk]
FROM [Master_Entity_Master]
WHERE [Master_Entity_Master].id = ?]
[parameters: [{'%(139897350575440 param)s': '4'}]]

From my reading of SQLAlchemy and flask_sqlalchemy documentation, i should implement methods provided under «Dealing with Disconnects» to be able to circumvent such errors (while i agreed database server is the culprit). Having tried such methods, i am unable to find a way w/o having to restart the application (i.e. re-create the connection again.) Any guidance on how to handle it

SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_size": 10,
    "pool_recycle": 120,
    "pool_pre_ping": True,
}

pdo_sqlsrv issue: TCP Provider: Error code 0x68 #809

PHP Driver

SQL Server version

• Microsoft SQL Server 2012 (SP1) — 11.0.3156.0 (X64)

Client operating system

PHP version

Microsoft ODBC Driver version

• ODBC Driver 17 for SQL Server (libmsodbcsql-17.1.so.0.1)

Problem description

When I connect using sqlcmd everything seems to work, but using sqlsrv_connect or the PDO driver (through laravel) I get the following error:

SQLSTATE[08S01]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68

Using sqlsrv_connect instead gives me the following error:

SQLSTATE: 08S01 Code: 104 Message: [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure

I am connecting to the sqlserver over a VPN, but seeing as sqlcmd works I don’t think that’s part of the issue. I have also tried using ODBC Driver 13.1, but that causes the same error.

The error occurs when I try to execute a query. Using the first php example here (the echo «Connected» one) doesn’t throw the Exception. only when I try to run queries.

The text was updated successfully, but these errors were encountered:

Hi @samtubbax , clearly, something is wrong with the communication or connection. When using sqlsrv_connect() and sqlsrv_query(), please print sqlsrv_errors() as shown in the example on this page.

Just so you know, @samtubbax , the error (0x68 or 104) normally happens when the ODBC driver is unable to send or receive data.

With sqlsrv_configure(«WarningsReturnAsErrors», 1); it still only shows

SQLSTATE: 08S01
Code: 104
Message: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68

SQLSTATE: 08S01
Code: 104
Message: [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure

Running the following code:

Hi @samtubbax I highly suspect it’s some communication problems, as I have added above what those error codes usually mean (unable to send or receive data). Please check your VPN settings or try it in a diff env.

@yitam is correct. I just ran into this issue with «[Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68»

In my case the server is pretty unresponsive, before this error; I was having login timeout issues to the server. So I think someone has a big job scheduled to run around this time. So I am waiting for the jobs to finish up, and attempt running my code.

Closing this issue due to inactivity, @samtubbax
Please feel free to reopen if you have other questions.

pyodbc.OperationalError: (’08S01′, ‘[08S01] [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)’)

I believe this is a case of transient error. One particular column, that I’m trying to push to Azure database has different characters from device snapshot, does anyone know if this a security policy by Azure team to prevent something like a sql injection? Is there a way to fix this?

@dev-pasa As you are using pyODBC, I would advise posting this issue at the pyODBC repository.

Hello
centos 7 system, this error is written in the logs. There are no problems with the connection,nor with the network.
MS Sql connect error: SQLSTATE: HYT00; code: 0; message: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
SQLSTATE: 08001; code: 258; message: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x102
SQLSTATE: 08001; code: 258; message: [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

@hourer please create a new issue and answer the questions to provide more details

Источник

Strange bug when upgrading to django 1.5.2 and Native Client 11.0 #2

When I upgrade to django 1.5.2 from 1.4.6, I got an strange bug that appears many hours after the server update, and once it appears it starts to fail in the 50% or more of the requests in a random and unpredictable way. It makes me think that somehow pyodbc is making native driver to explode. It doesn’t matter the entry point, any database call is potentially broken. If I downgrade to 1.4.6 it disappears. I did not found the error in SQL Azure error codes, but I found something here: http://www.pc-library.com/errors/error-code/104-0x68/ , what makes me suspect pyodbc makes Azure server to break with OS errors, as long as django installation and the native driver are under Ubuntu server.

Any idea? Thanks!

Here the details:

DatabaseError at /en/blog/2013/03/19/

(’08S01′, ‘[08S01] [Microsoft][SQL Server Native Client 11.0]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)’)
Request Method: GET
Request URL: http://xxxxxxxxxx/en/blog/2013/03/19/
Django Version: 1.5.2
Exception Type: DatabaseError
Exception Value:
(’08S01′, ‘[08S01] [Microsoft][SQL Server Native Client 11.0]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)’)
Exception Location: /var/www/infantium_portal/env/local/lib/python2.7/site-packages/sql_server/pyodbc/base.py in execute, line 394
Python Executable: /usr/bin/uwsgi-core
Python Version: 2.7.3
Python Path:
[‘/var/www/infantium_portal/env/local/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg’,
‘/var/www/infantium_portal/env/local/lib/python2.7/site-packages/pip-1.3.1-py2.7.egg’,
‘/var/www/infantium_portal/env/lib/python2.7’,
‘/var/www/infantium_portal/infantium’,
‘/usr/lib/python2.7’,
‘/usr/lib/python2.7/plat-linux2’,
‘/usr/lib/python2.7/lib-tk’,
‘/usr/lib/python2.7/lib-old’,
‘/usr/lib/python2.7/lib-dynload’,
‘/usr/local/lib/python2.7/dist-packages’,
‘/usr/lib/python2.7/dist-packages’,
‘/usr/lib/pymodules/python2.7’,
‘/var/www/infantium_portal/env/local/lib/python2.7/site-packages’]

The text was updated successfully, but these errors were encountered:

Источник

Как отладить проблемы с драйверами MSSQL из Ubuntu

Подобно другим парам вопросов, которые я видел, я нахожусь в темном месте, не имея другого выбора, кроме как подключиться к MSSQL из Django.

Я с перерывами (но около 50% времени, в остальном он отлично работает), получая ошибку;

django.db.utils.Error: (‘[08S01] [Microsoft] [драйвер ODBC 13 для SQL Server] Поставщик TCP: код ошибки 0x274c (10060) (SQLGetData)’)

Заметьте, я также иногда получаю это;

django.db.utils.Error: (’08S01′, ‘[08S01] [Microsoft] [драйвер ODBC 13 для SQL Server] Поставщик TCP: код ошибки 0x68 (104) (SQLGetData)’)

Я думаю, что это связано с сетью, я ранее пытался pyodbc версии pyodbc , меняя места между FreeTDS и драйвером Microsoft для unix и пытаясь использовать pyodbc и pyodbc-azure .

Рассматриваемые машины — это бранные коробки в частной сети с фиксированными IP-адресами (Ubuntu 16.04 и Windows 8), SQL Server — SQL Server Express 2016.

Я даже не могу понять, как найти более подробный журнал на стороне Windows, чтобы понять, почему/как он продолжает отменять/закрывать соединение. Примечание. Я просмотрел журналы событий SQL Server и Windows, и они, похоже, ничего не собирают.

1 ответ

Вот несколько полезных ссылок на основе кодов ошибок:

Произошла ошибка при установлении соединения с сервером. При подключении к SQL Server этот сбой может быть вызван тем, что при настройках по умолчанию SQL Server не разрешает удаленные подключения. (поставщик: поставщик TCP, ошибка: 0 — попытка подключения не удалась, потому что связанная сторона не ответила должным образом через какое-то время или не удалось установить соединение, потому что подключенный хост не смог ответить.) (Microsoft SQL Server, ошибка: 10060)

Как правило, это можно исправить, перейдя к экземпляру SQL Server и убедитесь, что удаленные подключения разрешены. Для этого в SSMS есть настройка конфигурации. Вы также хотите убедиться, что сервер настроен на использование безопасности в режиме интегрированного режима. т.е. — учетные данные windows/ad и учетные данные сервера sql. Вы можете определить пользователя SQL-сервера, не связанного с идентификатором пользовательского окна.

Источник

Handling terminated db connections (on Azure) #4686

Checklist

For long running jobs of the form:

1. query the db
2. run a long task
3. update/query the db

the second database access fails, causing the job to fail. This happens, for example, on Azure, which closes db connections after 30m of inactivity.

On the django side, setting CONNECTION_MAX_AGE=0, should cause the connections to be closed after every request. It does this using signal handlers sitting on request_started and request_finished, which check the connection status and close the connection as necessary.

In my case, I know if I explicitly close the connections before step 3., then the problem goes away. Thus, setting CONN_MAX_AGE=0 seems like an ideal solution, but it appears that the db requests within celery use a different mechanism, and do not honor the request_started/finished hooks.

Is there a way to do something similar within celery tasks?
Explicitly inserting closes before requests is error prone and ugly.

• [Yes] I have included the output of celery -A proj report in the issue.
  (if you are not able to do this, then at least specify the Celery
  version affected).

root@6cea834bb6e2:/var/app# celery -A config report
RDS_TYPE=pyodbc; RDS_DB_NAME=avdb-net, USE_AZURE_CLOUD=False

software -> celery:4.1.0 (latentcall) kombu:4.1.0 py:3.5.2
billiard:3.5.0.3 py-amqp:2.2.2
platform -> system:Linux arch:64bit, ELF imp:CPython
loader -> celery.loaders.app.AppLoader
settings -> transport:amqp results:rpc:///

worker_log_color: True
broker_url: ‘amqp://guest:********@rabbitmq:5672//’
result_backend: ‘rpc:///’
task_default_queue: ‘celery’

• [ No] I have verified that the issue exists against the master branch of Celery.

Using different versions and testing is a painful process. It takes a long time to reproduce the failures.

A similar problem was reported here:

Steps to reproduce

Need access to an azure db, etc.
I doubt anyone here will go through that trouble for this issue.
Mainly looking for someone that understands the code and, hopefully, knows the right hoooks to use.

Expected behavior

Actual behavior

The text was updated successfully, but these errors were encountered:

@c-w could you please triage this issue?

Hi @headdab. Which database service in Azure are you using? Managed MySQL, managed Postgres or something else? Do you have a minimal code example that I can use to reproduce this?

Under resource groups, it lists ‘SQL Server’, which I think means we’re using ‘Managed SQL’. Unfortunately, I don’t know enough about how celery and django handle connections. It’s my impression that pooling won’t help (as suggested by Microsoft), but I tried adding these statements to odbcinst.ini:

[ODBC]
Trace=Yes
TraceFile=/tmp/odbc.log
Pooling=No

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.4.0
UsageCount=1
CPTimeout=0

Coming up with a simple example may take me some time, but I’ll try.
If you have any debug advice, please let me know.
Note that all of this code ran for months without issues on AWS.

My current workaround is:
from django.db import connection
connection.close() # explicitly close connection
first = S3Video.objects.get(slug=AvURI(videos[0]).slug) # line 1052 from the traceback

I tried putting loggers on request_started and request_finished to debug things, but I see the django requests, but not the celery requests.

The error we get is:

Traceback (most recent call last):
File «/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py», line 65, in execute
return self.cursor.execute(sql, params)
File «/usr/local/lib/python3.5/dist-packages/sql_server/pyodbc/base.py», line 545, in execute
return self.cursor.execute(sql, params)
pyodbc.OperationalError: (’08S01′, ‘[08S01] [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)’)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File «/usr/local/lib/python3.5/dist-packages/celery/app/trace.py», line 374, in trace_task
R = retval = fun(*args, **kwargs)
File «/usr/local/lib/python3.5/dist-packages/celery/app/trace.py», line 629, in protected_call
return self.run(*args, **kwargs)
File «/var/app/apps/video/vpp.py», line 827, in concat2
update_event_assets(event_id) # FIXME exploit more parallelism, kick off video processing as videos are completed
File «/usr/local/lib/python3.5/dist-packages/celery/local.py», line 191, in call
return self._get_current_object()(*a, **kw)
File «/usr/local/lib/python3.5/dist-packages/celery/app/trace.py», line 630, in protected_call
return orig(self, *args, **kwargs)
File «/usr/local/lib/python3.5/dist-packages/celery/app/task.py», line 380, in call
return self.run(*args, **kwargs)
File «/var/app/apps/video/vpp.py», line 1262, in update_event_assets
if not update_video_assets(v.id, pretty, dryrun):
File «/usr/local/lib/python3.5/dist-packages/celery/local.py», line 191, in call
return self._get_current_object()(*a, **kw)
File «/usr/local/lib/python3.5/dist-packages/celery/app/trace.py», line 630, in protected_call
return orig(self, *args, **kwargs)
File «/usr/local/lib/python3.5/dist-packages/celery/app/task.py», line 380, in call
return self.run(*args, **kwargs)
File «/var/app/apps/video/vpp.py», line 1358, in update_video_assets
update_s3video(event_id, allow_create=False, **uv_args)
File «/var/app/apps/video/vpp.py», line 1052, in update_s3video
event = Event.objects.get(id=event_id)
File «/usr/local/lib/python3.5/dist-packages/django/db/models/manager.py», line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File «/usr/local/lib/python3.5/dist-packages/django/db/models/query.py», line 374, in get
num = len(clone)
File «/usr/local/lib/python3.5/dist-packages/django/db/models/query.py», line 232, in len
self._fetch_all()
File «/usr/local/lib/python3.5/dist-packages/django/db/models/query.py», line 1118, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File «/usr/local/lib/python3.5/dist-packages/django/db/models/query.py», line 53, in iter
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
File «/usr/local/lib/python3.5/dist-packages/django/db/models/sql/compiler.py», line 894, in execute_sql
raise original_exception
File «/usr/local/lib/python3.5/dist-packages/django/db/models/sql/compiler.py», line 884, in execute_sql
cursor.execute(sql, params)
File «/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py», line 65, in execute
return self.cursor.execute(sql, params)
File «/usr/local/lib/python3.5/dist-packages/django/db/utils.py», line 94, in exit
six.reraise(dj_exc_type, dj_exc_value, traceback)
File «/usr/local/lib/python3.5/dist-packages/django/utils/six.py», line 685, in reraise
raise value.with_traceback(tb)
File «/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py», line 65, in execute
return self.cursor.execute(sql, params)
File «/usr/local/lib/python3.5/dist-packages/sql_server/pyodbc/base.py», line 545, in execute
return self.cursor.execute(sql, params)
django.db.utils.OperationalError: (’08S01′, ‘[08S01] [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)’)

From Microsoft support:

In general, Azure SQL Database terminates connections that have been idle for 30 minutes or longer.

As workaround you can use connection pooling, the connection pooling increases code efficiency by reducing the number of times that new connections must be opened and closing the idle connections automatically. SQL Database terminates connections that have been idle for 30 minutes or longer. Implementing connection pooling in your application is beneficial as the connection pooler automatically removes a connection from the pool after it has been idle for a long time. Consider designing your application in a way that connections are opened late and closed early. For more information, see SQL Server Connection Pooling:

You confirmed that Azure SQL terminates connections after 30m — we have to figure out a robust way to deal with it.
I’m not that there’s much else you can do, unless there’s some database settings that might help.

I read about pooling in my environment, and recall that people suggested that it has its own problems, so i’m not sure that that’s a solution.

When connecting to a Azure SQL Database, idle connections may be terminated by a network component (such as a firewall) after a period of inactivity. There are two types of idle connections, in this context:

• Idle at the TCP layer, where connections can be dropped by any number of network devices.
• Idle by the SQL Azure Gateway, where TCP keepalive messsages might be occurring (making the connection not idle from a TCP perspective), but not had an active query in 30 minutes. In this scenario, the Gateway will determine that the TDS connection is idle at 30 minutes and terminate the connection.

To avoid dropping idle connections by a network component, the following registry settings (or their non-Windows equivalents) should be set on the operating system where the driver is loaded:

Other option is connection pooling which I have shared details earlier.

Источник

I’m using this code to sync my db with the clients:

    import pyodbc

    SYNC_FETCH_ARRAY_SIZE=25000

    # define connection + cursor
    connection = pyodbc.connect()
    cursor = connection.cursor()

    query = 'select some_columns from mytable'
    cursor.execute(query)

    while True:
        rows = cursor.fetchmany(SYNC_FETCH_ARRAY_SIZE) # <<< error here

        if not rows:
            break

        insert_to_our_db(rows)

    cursor.close()

I’m getting the below error intermitently:

File "....py", line 120, in ...
  rows = sg_cur.fetchmany(SYNC_FETCH_ARRAY_SIZE)
pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLGetData)')

How should i handle this error? Is this an error on the connection (thus i need to close and recreate the connection) or on the cursor and i simply need to add a retry?

I will add the below to retry (in place of the line throwing the error), is this enough to solve the issue? Will retries have any effect at all if im experiencing a tcp error?

MAX_RETRIES=5

def get_rows(retry_count=0):
    """
    Wrapper function to add retry functionality to fetchmany
    """
    try:
        rows = sg_cur.fetchmany(SYNC_FETCH_ARRAY_SIZE)

    except Exception as e:
        if retry_count >= MAX_RETRIES:
            raise ConnectionError(f'fetchmany caused the error: {e}')

        else:
            logger.debug(f'Error in get_rows: {e}, attempt: {retry_count}/{MAX_RETRIES}')
            retry_count += 1
            return get_rows(retry_count=retry_count)

    return rows

Edit:

There is an issue open on github for this. In the meantime what would be a viable workaround?

• Store the last read after insert_to_our_db in our db then restart from there in case of a bug
• just run the entire process again

Notes:

• The error is intermittent, thus hard to test
• I’m syncing a large table from a client’s db on a different host. Thus i need to update/insert all rows at once to be sure that the data is current
• I cant make changes to the client’s db

    import pyodbc

    SYNC_FETCH_ARRAY_SIZE=25000

    # define connection + cursor
    connection = pyodbc.connect()
    cursor = connection.cursor()

    query = 'select some_columns from mytable'
    cursor.execute(query)

    while True:
        rows = cursor.fetchmany(SYNC_FETCH_ARRAY_SIZE) # <<< error here

        if not rows:
            break

        insert_to_our_db(rows)

    cursor.close()

File "....py", line 120, in ...
  rows = sg_cur.fetchmany(SYNC_FETCH_ARRAY_SIZE)
pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLGetData)')

MAX_RETRIES=5

def get_rows(retry_count=0):
    """
    Wrapper function to add retry functionality to fetchmany
    """
    try:
        rows = sg_cur.fetchmany(SYNC_FETCH_ARRAY_SIZE)

    except Exception as e:
        if retry_count >= MAX_RETRIES:
            raise ConnectionError(f'fetchmany caused the error: {e}')

        else:
            logger.debug(f'Error in get_rows: {e}, attempt: {retry_count}/{MAX_RETRIES}')
            retry_count += 1
            return get_rows(retry_count=retry_count)

    return rows

3 ответа

sudo ifconfig eth0 mtu 1350

engine = create_engine(
    sql_connection_string,
    echo=False,
    connect_args={
        "autocommit": False,
        "connect_timeout": 30,
    },
    pool_pre_ping=True,
    pool_size=25,
    pool_recycle=3600,
)

Greetings, folks,

I’ve been making use of the sqlncli / msodbc drivers for unixODBC, and everything is running quite smoothly until a connection handle to the database times out for whatever reason (pretty much anything that causes a TCP connection timeout).  Both the
application that I am using this with (OpenSIPS) and even unixODBC’s isql command-line query program will report «TCP Provider: Error code 0x68» after the timeout occurs, and I see the following sequence of packets on the wire:

1.  From client to server — SYN
2.  From server to client — SYN, ACK
3.  From client to server — ACK
4.  From client to server — FIN, ACK
5.  From server to client — ACK
6.  From server to client — ACK
7.  From client to server — ACK

And that it is it.  No further packets, error 0x68, handle is invalid.  If instead I use FreeTDS 0.92.79 on my system, I see the following set of packets:

There are no symbols and everything is optimized out on frames 0-10, and the variables in frame 12 are valid, and work on initial connection just fine.

Server is Centos 6.4 x86_64 with 64 GB of RAM on it, and only OpenSIPS w/unixODBC+msodbc, and the OpenSIPS control panel application running on php under Apache HTTPd.

Any help would be appreciated, as this appears to be a bug to me at this time.  Please let me know if I’m leaving out any pertinent details.  Thanks!