Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Empty OperationalError on connect #417

Closed
Nerade opened this issue Mar 15, 2016 · 39 comments
Closed

Empty OperationalError on connect #417

Nerade opened this issue Mar 15, 2016 · 39 comments
Labels

Comments

@Nerade
Copy link

Nerade commented Mar 15, 2016

Hello,

I have noticed it is possible to optain an empty OperationalError while connecting to a PostgreSQL database. There is no information attached to this error, both pgerror and pgcode remain empty.

While beeing unsuccessful debugging this issue on client site with this error message looking to the server side reveals a log entry for "password incorrect". As far as I can see in your code there should be an error code explicit for this situation.

Using python 3.5 and PostgreSQL 9.3.

Thanks in advnace and best regards

Nerade

@dvarrazzo
Copy link
Member

I have noticed it is possible to optain an empty OperationalError while connecting to a PostgreSQL database.

How? Please provide a way to reproduce the issue.

@Nerade
Copy link
Author

Nerade commented Mar 15, 2016

On a remote windows (10.10.3.17) machine I have a PostgreSQL 9.3 (32-bit) installation. It is in the same local subnet 10.10.2.0/23. In the pg_hba.conf file my client (10.10.3.19) is implicit allowed on host via following config line:
host all all 10.10.2.0/23 md5

In my script I use a line similar to this:
conn = psycopg2.connect(database=database, user=user, host='10.10.3.17', password=password)
Catching the exception delivers my reported empty OperationalError.

On the remote machine in the actual pg_log file there is a logged authentication attempt from ip 10.10.3.19 with wrong password authentication error.

I hope this information is sufficient for you to replicate this issue.

@saaros
Copy link

saaros commented Mar 15, 2016

I saw the same issue in my application's log yesterday while it tried to connect in async mode to a DB that was just starting. pgerror was None, but I saw this in the traceback:

     Traceback (most recent call last):
       File "pg.py", line 219, in check_standby_status
         pg_conn = self.pg_admin_connect()
       File "pg.py", line 164, in pg_admin_connect
         wait_select(pg_conn)
       File "dbutils.py", line 12, in wait_select
         state = conn.poll()
     psycopg2.OperationalError: FATAL:  the database system is starting up

     During handling of the above exception, another exception occurred:

     Traceback (most recent call last):
       [...]
       File "pg.py", line 221, in check_standby_status
         if "database system is starting up" in ex.pgerror:
     TypeError: argument of type 'NoneType' is not iterable

I'll try to come up with a way to reproduce this.

I'm using python3-psycopg2-2.6.1-1.fc23.x86_64 and postgresql95-9.5.1-1PGDG.f23.x86_64

@Nerade
Copy link
Author

Nerade commented Mar 15, 2016

Using
conn = psycopg2.connect(database='Test', user='Test', host='10.10.3.17',password='asdf')
results in:
Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/usr/lib/python3.5/site-packages/psycopg2/__init__.py", line 164, in connect conn = _connect(dsn, connection_factory=connection_factory, async=async) psycopg2.OperationalError

Leaving out the password parameter results in a helpful error message of psycopg2.OperationalError: fe_sendauth: no password supplied

@dvarrazzo
Copy link
Member

@Nerade so your connection attempt is synchronous (whereas @saaros is async)?

@Nerade
Copy link
Author

Nerade commented Mar 16, 2016

@dvarrazzo if synchronous is the default connection type, yes

@dvarrazzo
Copy link
Member

I couldn't reproduce the issue. Tried PG 9.3, Py 3.5 on Linux:

>>> psycopg2.connect("host=192.168.56.50 port=54393 user=testpass dbname=psycopg_test password=wrong")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/psycopg/psycopg/build/lib.3.5/psycopg2/__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
psycopg2.OperationalError: FATAL:  password authentication failed for user "testpass"

The fact that pgerror and pgcode are empty is normal, because the error doesn't come from the server but from the client failing the connection. From your description it seems the text of the exception is empty though: I receive a message instead.

Your example is not enough to reproduce the issue to me: it is a normal installation. I can't reproduce it on windows as I don't have any available.

@Nerade
Copy link
Author

Nerade commented Mar 17, 2016

Ok so there are two factors left which can cause this issue: you have used the libpq string while I used kwargs approach. The other factor is windows. Since you are not able to test it your self how can I help you with this step? I'm not much into psycopg to debug it myself. Further I don't think I can give you more information about my setup since there isn't anymore to say...

@saaros
Copy link

saaros commented Mar 17, 2016

It looks like we don't get pgcode or pgerror for any connection errors. This appears to be a shortcoming of libpq which doesn't provide details for connection failures. See http://stackoverflow.com/questions/23349086/libpq-how-to-get-the-error-code-after-a-failed-pgconn-connection

@dvarrazzo
Copy link
Member

@Nerade what is your problem: that pgerror and pgcode are empty or that there is no error message?

To be safe I've also tested the keywords approach: there is no difference.

@Nerade
Copy link
Author

Nerade commented Mar 18, 2016

Since I figured out the reason for the error measure I got no pressure on this. I just thought you could be interested in this issue since other may come in same situation with no helpful error message.

@dvarrazzo
Copy link
Member

Can you confirm that with your setup:

try:
    psycopg2.connect(password="wrong")
except Exception as e:
    assert str(e) == ""

in this case I'll leave the bug open for further test.

If it's only a matter that pgerror and pgcode are None, that's sort of expected (not that it's perfect, but if it's what the libpq returns, we wouldn't make anything up) and the bug can be closed.

@Nerade
Copy link
Author

Nerade commented Mar 21, 2016

Since I figured out the reason for the error measure I got no pressure on this. I just thought you could be interested in this issue since other may come in same situation with no helpful error message.

Your code snippet passed without any message. So the str(e) is indeed empty.

@quasilyte
Copy link

quasilyte commented Jan 31, 2017

First of all, big thanks to maintainers.

Is this issue similar?
I have weird empty error on connection attempt with invalid data.
Did you tested it on windows with python3?
noerror

@dvarrazzo
Copy link
Member

@quasilyte any chance you can copy and paste the error instead of submitting a screenshot? You are not debugging a videogame, that's text.

Probably the error is the same, but I don't have a windows machine to test. It would be helpful compiling and testing psycopg in debug mode. Either some of you (or @jerickso - but it's sad he always has to do the grunt work) give a hand or I am not in the position to help anybody.

@quasilyte
Copy link

quasilyte commented Jan 31, 2017

@dvarrazzo posting a text is not a problem, but I see no reason.
Bug is not going to be fixed until someone will take a job.
It is not critical for my project either, just notified that error still exists and narrowed scope to windows.
Note that I was not asking anyone to fix it "for me" and hope no one is offended.

@dvarrazzo
Copy link
Member

@dvarrazzo posting a text is not a problem, but I see no reason.

Copying? Pasting? Selecting? Googling? Seeing the entire message instead of the bit you cropped half line? Whatever.

@quasilyte
Copy link

quasilyte commented Jan 31, 2017

You are welcome (btw, this thread already indexed and "googled" quite easilly).

$ cat psycopg_error.py
import psycopg2
try:
        db = psycopg2.connect("dbname='?' host='127.0.0.1' password='?' port='5432'")
except Exception as e:
        print("`" + str(e) + "'")
user@isharipov MINGW64 ~/Desktop/scripts
$ python psycopg_error.py
`'

user@isharipov MINGW64 ~/Desktop/scripts
$ cat psycopg_trace.py
import psycopg2
db = psycopg2.connect("dbname='?' host='127.0.0.1' password='?' port='5432'")

user@isharipov MINGW64 ~/Desktop/scripts
$ python psycopg_trace.py
Traceback (most recent call last):
  File "psycopg_trace.py", line 2, in <module>
    db = psycopg2.connect("dbname='?' host='127.0.0.1' password='?' port='5432'")
  File "C:\Users\user\AppData\Local\Programs\Python\Python36\lib\site-packages\psycopg2\__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
psycopg2.OperationalError

user@isharipov MINGW64 ~/Desktop/scripts
$

Note the empty string.

@jerickso
Copy link
Member

jerickso commented Feb 1, 2017

@quasilyte, I have not been able to duplicate the issue that you are seeing.

I believe the missing error message text is actually passed back from the server through the libpq connection, so for some reason the server is not passing the information/message back. What server version/distribution are you using? By chance have you changed the client_min_messages from NOTICE in the postgres.conf file? Or any other unusually changes to the postgres.conf file?

@quasilyte
Copy link

quasilyte commented Feb 2, 2017

I get the error message this ways:

  • with libpq (from 9.6.1) it is returned from server
  • psql also gives an error on connection with invalid password
  • postgresql driver for python also delivers error message

Only encoding related options are redefined in the config file, I do not think it counts as unusual change.

More information later (has no that laptop nearby at the moment).

@quasilyte
Copy link

quasilyte commented Feb 3, 2017

psycopg2 ver 2.6.2
postgres ver 9.6.1

Ok, I got it.
If I set something like:

lc_messages = 'en-US'
lc_monetary = 'en-US'
lc_numeric = 'en-US'
lc_time = 'en-US'	

Everything works as expected and I get the error:
psycopg2.OperationalError: FATAL: password authentication failed for user "x" // Not empty
But if I leave them empty OR set it to, say "ru-RU", issue is reproduced.

lc_messages = 'ru-RU'
lc_monetary = 'ru-RU'
lc_numeric = 'ru-RU'
lc_time = 'ru-RU'	

psycopg2.OperationalError // Empty

Note that on some weird systems like Windows, server could return result in cp1251
without auto encoding conversion even if client sends clien_encoding=UTF.
Why? Because error is received before this option is sent. So, first error message is kinda tricky to get right. You can detect it with sniffer or with frontend debug in libpq.

It is a pity that libpq gives no way to get SQLCODE on connection failure.
As a hack, we can inspect pg_conn->last_sqlcode.

Hope this helps.

@dvarrazzo
Copy link
Member

It helps a lot, thank you @quasilyte

@dvarrazzo dvarrazzo reopened this Feb 3, 2017
@dvarrazzo
Copy link
Member

Hi @quasilyte Haven't found a moment yet to play with this issue but I'd like to try. I just don't understand when you say that setting lc_messages = 'ru-RU' reproduces the error: what is lc_messages? An env var on the client? On the server? a postgres.conf setting? a parameter to initdb...?

@quasilyte
Copy link

Hello, @dvarrazzo.
It was postgresql.conf (the one listed with SHOW config_file).

@dvarrazzo
Copy link
Member

Thank you @quasilyte :)

@ShintaG3
Copy link

ShintaG3 commented Jul 4, 2019

Thanks for this post!
The same empty error happened to me. I use Windows 10.
My case is

lc_messages = 'japanese_japan.932'
lc_monetary = 'japanese_japan.932'
lc_numeric = 'japanese_japan.932'
lc_time = 'japanese_japan.932'

and I got empty error.
When I change them as follows, error messages appear!!

lc_messages = 'en-US'
lc_monetary = 'en-US'
lc_numeric = 'en-US'
lc_time = 'en-US'

Still helpful in 2019.
Thank you !

@LVMcode
Copy link

LVMcode commented Jul 15, 2019

I have a similar issue with psycopg2. When I try to connect with the database and there is a problem with the host, user, database or password I don't get any information from pgcode and pgerror (its value is None). I tried @quasilyte's solution, establish:
lc_messages = 'en-US'
lc_monetary = 'en-US'
lc_numeric = 'en-US'
lc_time = 'en-US'
But nothing changes, the only thing I get is an OperationalError.
I would like to know how I could specify the exact type of error: wrong IP, database name, user name or password.
Sorry for any error in my comment structure. This is my first one.
Thanks in advance

@daddydrac
Copy link

So whats the answer to fix?

My error:

Exception Value: | fe_sendauth: no password supplied
/usr/local/lib/python3.6/site-packages/psycopg2/__init__.py in connect, line 126

@dvarrazzo
Copy link
Member

@joehoeller there is no fix because nobody is writing a patch for it. I don't do development on Window.

If someone write a patch for the issue it will be applied and released.

@daddydrac
Copy link

daddydrac commented Jul 21, 2019 via email

@dvarrazzo
Copy link
Member

@joehoeller in this case you weren't affected by this bug. Or you have upgraded to a release in the future: you'll have to tell us what pip proxy you use.

@daddydrac
Copy link

daddydrac commented Jul 21, 2019 via email

@yonathancohen
Copy link

yonathancohen commented Aug 22, 2019

@quasilyte
@dvarrazzo

psycopg2 ver 2.6.2
postgres ver 9.6.1

Ok, I got it.
If I set something like:

lc_messages = 'en-US'
lc_monetary = 'en-US'
lc_numeric = 'en-US'
lc_time = 'en-US'	

Everything works as expected and I get the error:
psycopg2.OperationalError: FATAL: password authentication failed for user "x" // Not empty
But if I leave them empty OR set it to, say "ru-RU", issue is reproduced.

lc_messages = 'ru-RU'
lc_monetary = 'ru-RU'
lc_numeric = 'ru-RU'
lc_time = 'ru-RU'	

psycopg2.OperationalError // Empty

Note that on some weird systems like Windows, server could return result in cp1251
without auto encoding conversion even if client sends clien_encoding=UTF.
Why? Because error is received before this option is sent. So, first error message is kinda tricky to get right. You can detect it with sniffer or with frontend debug in libpq.

It is a pity that libpq gives no way to get SQLCODE on connection failure.
As a hack, we can inspect pg_conn->last_sqlcode.

Hope this helps.

Ok but where do you set those informations ?
I have the same problem on windows 10 with psycopg2 version 2.8.3 and postgreSQL 11.5-1
I can't work with postgreSQL DB because of this, if someone can help me please ...

Traceback (most recent call last):
File "C:\Users\Yonathan\Documents\Projets\Python\PostgreSQL - Python\PostgreSQL - Python\PostgreSQL___Python.py", line 60, in
create_table()
File "C:\Users\Yonathan\Documents\Projets\Python\PostgreSQL - Python\PostgreSQL - Python\PostgreSQL___Python.py", line 8, in create_table
conn=psycopg2.connect("dbname='DB1' user='postgres' password='shakD6yc' host='localhost' port='5432'")
File "C:\Users\Yonathan\Anaconda3\lib\site-packages\psycopg2_init_.py", line 126, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError

This is my error message.
Thank you very much for helping

@zero2cx
Copy link

zero2cx commented Aug 22, 2019

@yhecohen asks:

Ok but where do you set those informations ?

@quasilyte answered this on Apr 5, 2017:

It was postgresql.conf (the one listed with SHOW config_file).

@yonathancohen
Copy link

yonathancohen commented Aug 22, 2019

Worked for me, i also restarted the server. Thank you very much

@mhdbreakers
Copy link

Hi i have same problem.

`Traceback (most recent call last):
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\backends\base\base.py", line 217, in ensure_connection
self.connect()
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\db\backends\base\base.py", line 195, in connect
self.connection = self.get_new_connection(conn_params)
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\backends\postgresql\base.py", line 178, in get_new_connection
connection = Database.connect(**conn_params)
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\psycopg2_init_.py", line 126, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError

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

Traceback (most recent call last):
File "C:\Users\Mohamed Ali\Desktop\Python\matrix\matrix\manage.py", line 21, in
main()
File "C:\Users\Mohamed Ali\Desktop\Python\matrix\matrix\manage.py", line 17, in main
execute_from_command_line(sys.argv)
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\core\management_init_.py", line 381, in execute_from_command_line
utility.execute()
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\core\management_init_.py", line 375, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
self.execute(*args, **cmd_options)
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\core\management\base.py", line 364, in execute
output = self.handle(*args, **options)
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\core\management\base.py", line 83, in wrapped
res = handle_func(*args, **kwargs)
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\core\management\commands\migrate.py", line 87, in handle
executor = MigrationExecutor(connection, self.migration_progress_callback)
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\migrations\executor.py", line 18, in init
self.loader = MigrationLoader(self.connection)
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\db\migrations\loader.py", line 49, in init
self.build_graph()
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\migrations\loader.py", line 212, in build_graph
self.applied_migrations = recorder.applied_migrations()
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\db\migrations\recorder.py", line 73, in applied_migrations
if self.has_table():
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\migrations\recorder.py", line 56, in has_table
return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor())
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\db\backends\base\base.py", line 256, in cursor
return self.cursor()
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\backends\base\base.py", line 233, in _cursor
self.ensure_connection()
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\db\backends\base\base.py", line 217, in ensure_connection
self.connect()
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\utils.py", line 89, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\db\backends\base\base.py", line 217, in ensure_connection
self.connect()
File "C:\Users\MOHAME1\Envs\matrix\lib\site-packages\django\db\backends\base\base.py", line 195, in connect
self.connection = self.get_new_connection(conn_params)
File "C:\Users\MOHAME
1\Envs\matrix\lib\site-packages\django\db\backends\postgresql\base.py", line 178, in get_new_connection
connection = Database.connect(**conn_params)
File "C:\Users\MOHAME~1\Envs\matrix\lib\site-packages\psycopg2_init
.py", line 126, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
django.db.utils.OperationalError`

@muhalitas
Copy link

muhalitas commented Jan 17, 2020

It is quite interesting for package developers to not fix this issue with recent releases. Even it makes sense that connection failures are not returned by database but by client, this logic makes pgcode and pgerror for OperationalError meaningless.

It has been 4 years since a guy discovered this and package releases are done without fixing it. I am not sure that psycopg2 package is to be truly trusted in production.

@daddydrac
Copy link

daddydrac commented Jan 17, 2020 via email

@dvarrazzo
Copy link
Member

@muhalitas nobody in this thread has provided a consistent way to reproduce their issue. That's including you.

This bug has a title which attracts any sort of error, most of which are caused by unreliable server connections.

Said that I'm taking the following actions:

  • I am closing and locking this bug
  • I am blocking and reporting @muhalitas to github for harassment
  • whoever will open a bug on empty operational error has to provide a reproducible use case, in a docker image or downloadable VM and a connection to a server where the issue can be reproduced.

Over and out.

@psycopg psycopg locked as too heated and limited conversation to collaborators Jan 17, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Projects
None yet
Development

No branches or pull requests