Tcp provider error code 0x68 104

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

Comments

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.

Читайте также:  Error 720 что это

@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

Comments

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-сервера, не связанного с идентификатором пользовательского окна.

Читайте также:  Error 432 что это

Источник

Handling terminated db connections (on Azure) #4686

Comments

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:

Читайте также:  Failed to find updates with error code 80244010

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.

Источник

Smartadm.ru
Adblock
detector