Internal server error when database connection fails

[pavish]

Description

When the user updates an existing connection, and the requests fail after that, the server returns a 500 without a structured error message.

Environment:


Request Method: GET
Request URL: http://localhost:8000/db/mathesar_tables/

Django Version: 3.1.14
Python Version: 3.9.17
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'whitenoise.runserver_nostatic',
 'django.contrib.staticfiles',
 'rest_framework',
 'django_filters',
 'django_property_filter',
 'drf_spectacular',
 'mathesar']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'whitenoise.middleware.WhiteNoiseMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'mathesar.middleware.CursorClosedHandlerMiddleware',
 'mathesar.middleware.PasswordChangeNeededMiddleware',
 'django_userforeignkey.middleware.UserForeignKeyMiddleware',
 'django_request_cache.middleware.RequestCacheMiddleware']



Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3240, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

The above exception (connection to server at "mathesar_dev_db" (172.23.0.2), port 5433 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
) was the direct cause of the following exception:
  File "/usr/local/lib/python3.9/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.9/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/contrib/auth/decorators.py", line 21, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "/code/mathesar/views.py", line 333, in schemas
    'common_data': get_common_data(request, database, None)
  File "/code/mathesar/views.py", line 176, in get_common_data
    'schemas': get_schema_list(request, database),
  File "/code/mathesar/views.py", line 37, in get_schema_list
    return schema_serializer.data
  File "/usr/local/lib/python3.9/site-packages/rest_framework/serializers.py", line 745, in data
    ret = super().data
  File "/usr/local/lib/python3.9/site-packages/rest_framework/serializers.py", line 246, in data
    self._data = self.to_representation(self.instance)
  File "/usr/local/lib/python3.9/site-packages/rest_framework/serializers.py", line 663, in to_representation
    return [
  File "/usr/local/lib/python3.9/site-packages/rest_framework/serializers.py", line 664, in <listcomp>
    self.child.to_representation(item) for item in iterable
  File "/usr/local/lib/python3.9/site-packages/rest_framework/serializers.py", line 502, in to_representation
    attribute = field.get_attribute(instance)
  File "/usr/local/lib/python3.9/site-packages/rest_framework/fields.py", line 457, in get_attribute
    return get_attribute(instance, self.source_attrs)
  File "/usr/local/lib/python3.9/site-packages/rest_framework/fields.py", line 97, in get_attribute
    instance = getattr(instance, attr)
  File "/code/mathesar/models/base.py", line 177, in name
    schema_name = schema_utils.get_schema_name_from_oid(
  File "/code/db/schemas/utils.py", line 7, in get_schema_name_from_oid
    schema_info = reflect_schema(engine, oid=oid, metadata=metadata)
  File "/code/db/schemas/operations/select.py", line 31, in reflect_schema
    pg_namespace = get_pg_catalog_table("pg_namespace", engine, metadata=metadata)
  File "/code/db/utils.py", line 92, in warning_ignored_func
    return f(*args, **kwargs)
  File "/code/db/utils.py", line 99, in get_pg_catalog_table
    table = sqlalchemy.Table(table_name, metadata, autoload_with=engine, schema='pg_catalog')
  File "<string>", line 2, in __new__
    <source code not available>
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 607, in __new__
    metadata._remove_table(name, schema)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 602, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 677, in _init
    self._autoload(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 710, in _autoload
    insp = inspection.inspect(autoload_with)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/inspection.py", line 64, in inspect
    ret = reg(subject)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 182, in _engine_insp
    return Inspector._construct(Inspector._init_engine, bind)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 117, in _construct
    init(self, bind)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 128, in _init_engine
    engine.connect().close()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/future/engine.py", line 419, in connect
    return super(Engine, self).connect()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3194, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3273, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3243, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2097, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3240, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

Exception Type: OperationalError at /db/mathesar_tables/
Exception Value: (psycopg2.OperationalError) connection to server at "mathesar_dev_db" (172.23.0.2), port 5433 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

(Background on this error at: https://sqlalche.me/e/14/e3q8)

Expected behavior

• The server should return a valid structured error message.

To Reproduce

• Update an existing connection with a different port value.
• Open the database page, notice the above Internal server error.

[mathemancer]

@pavish While I agree that we need to properly format and handle the error, and we could add a Mathesar-specific error code to the error JSON, I don't think there's anything more appropriate than 5xx for the actual HTTP response code. I.e., "the server can't connect to an underlying DB" seems to belong in the 5xx range. Given that, I don't think there's really a more appropriate response code than 500 itself.

[pavish]

@mathemancer I understand.

I was mainly concerned about differentiating errors thrown directly by connectivity issues with our application server vs errors thrown due to user database connectivity/user configurations. I assume 5xx should be fine for both cases. I've updated the issue description to reflect that.