Error Messages

Error Messages

This section lists descriptions and background for common error messages and warnings raised or emitted by SQLAlchemy.

SQLAlchemy normally raises errors within the context of a SQLAlchemy-specific exception class. For details on these classes, see Core Exceptions and ORM Exceptions.

SQLAlchemy errors can roughly be separated into two categories, the programming-time error and the runtime error. Programming-time errors are raised as a result of functions or methods being called with incorrect arguments, or from other configuration-oriented methods such as mapper configurations that can’t be resolved. The programming-time error is typically immediate and deterministic. The runtime error on the other hand represents a failure that occurs as a program runs in response to some condition that occurs arbitrarily, such as database connections being exhausted or some data-related issue occurring. Runtime errors are more likely to be seen in the logs of a running application as the program encounters these states in response to load and data being encountered.

Since runtime errors are not as easy to reproduce and often occur in response to some arbitrary condition as the program runs, they are more difficult to debug and also affect programs that have already been put into production.

Within this section, the goal is to try to provide background on some of the most common runtime errors as well as programming time errors.

Legacy API Features

select() construct created in “legacy” mode; keyword arguments, etc.

The select() construct has been updated as of SQLAlchemy 1.4 to support the newer calling style that will be standard in SQLAlchemy 2.0. For backwards compatibility in the interm, the construct accepts arguments in both the “legacy” style as well as the “new” style.

The “new” style features that column and table expressions are passed positionally to the select() construct only; any other modifiers to the object must be passed using subsequent method chaining:

# this is the way to do it going forward
stmt = select(table1.c.myid).where(table1.c.myid == table2.c.otherid)

For comparison, a select() in legacy forms of SQLAlchemy, before methods like Select.where() were even added, would like:

# this is how it was documented in original SQLAlchemy versions
# many years ago
stmt = select([table1.c.myid], whereclause=table1.c.myid == table2.c.otherid)

Or even that the “whereclause” would be passed positionally:

# this is also how it was documented in original SQLAlchemy versions
# many years ago
stmt = select([table1.c.myid], table1.c.myid == table2.c.otherid)

For some years now, the additional “whereclause” and other arguments that are accepted have been removed from most narrative documentation, leading to a calling style that is most familiar as the list of column arguments passed as a list, but no further arguments:

# this is how it's been documented since around version 1.0 or so
stmt = select([table1.c.myid]).where(table1.c.myid == table2.c.otherid)

The document at select() no longer accepts varied constructor arguments, columns are passed positionally describes this change in terms of 2.0 Migration.

Migrating to SQLAlchemy 2.0

The <some function> in SQLAlchemy 2.0 will no longer <something>

SQLAlchemy 2.0 is expected to be a major shift for a wide variety of key SQLAlchemy usage patterns in both the Core and ORM components. The goal of this release is to make a slight readjustment in some of the most fundamental assumptions of SQLAlchemy since its early beginnings, and to deliver a newly streamlined usage model that is hoped to be significantly more minimalist and consistent between the Core and ORM components, as well as more capable.

Introduced at Migrating to SQLAlchemy 2.0, the SQLAlchemy 2.0 project includes a comprehensive future compatibility system that is to be integrated into the 1.4 series of SQLAlchemy, such that applications will have a clear, unambiguous, and incremental upgrade path in order to migrate applications to being fully 2.0 compatible. The RemovedIn20Warning deprecation warning is at the base of this system to provide guidance on what behaviors in an existing codebase will need to be modified. An overview of how to enable this warning is at SQLAlchemy 2.0 Deprecations Mode.

See also

Migrating to SQLAlchemy 2.0 - An overview of the upgrade process from the 1.x series, as well as the current goals and progress of SQLAlchemy 2.0.

SQLAlchemy 2.0 Deprecations Mode - specific guidelines on how to use “2.0 deprecations mode” in SQLAlchemy 1.4.

A bind was located via legacy bound metadata, but since future=True is set on this Session, this bind is ignored.

The concept of “bound metadata” is being removed in SQLAlchemy 2.0. This refers to the MetaData.bind parameter on the MetaData object that in turn allows objects like the ORM Session to associate a particular mapped class with an Engine. In SQLAlchemy 2.0, the Session must be linked to each Engine directly. That is, instead of instantiating the Session or sessionmaker without any arguments, and associating the Engine with the MetaData:

engine = create_engine("sqlite://")
Session = sessionmaker()
metadata = MetaData(bind=engine)
Base = declarative_base(metadata=metadata)
class MyClass(Base):
    # ...
session = Session()
session.add(MyClass())
session.commit()

The Engine must instead be associated directly with the sessionmaker or Session. The MetaData object should no longer be associated with any engine:

engine = create_engine("sqlite://")
Session = sessionmaker(engine)
Base = declarative_base()
class MyClass(Base):
    # ...
session = Session()
session.add(MyClass())
session.commit()

In SQLAlchemy 1.4, this 2.0 style behavior is enabled when the Session.future flag is set on sessionmaker or Session.

Connections and Transactions

QueuePool limit of size <x> overflow <y> reached, connection timed out, timeout <z>

This is possibly the most common runtime error experienced, as it directly involves the work load of the application surpassing a configured limit, one which typically applies to nearly all SQLAlchemy applications.

The following points summarize what this error means, beginning with the most fundamental points that most SQLAlchemy users should already be familiar with.

The SQLAlchemy Engine object uses a pool of connections by default - What this means is that when one makes use of a SQL database connection resource of an Engine object, and then releases that resource, the database connection itself remains connected to the database and is returned to an internal queue where it can be used again. Even though the code may appear to be ending its conversation with the database, in many cases the application will still maintain a fixed number of database connections that persist until the application ends or the pool is explicitly disposed.
Because of the pool, when an application makes use of a SQL database connection, most typically from either making use of Engine.connect() or when making queries using an ORM Session, this activity does not necessarily establish a new connection to the database at the moment the connection object is acquired; it instead consults the connection pool for a connection, which will often retrieve an existing connection from the pool to be re-used. If no connections are available, the pool will create a new database connection, but only if the pool has not surpassed a configured capacity.
The default pool used in most cases is called QueuePool. When you ask this pool to give you a connection and none are available, it will create a new connection if the total number of connections in play are less than a configured value. This value is equal to the pool size plus the max overflow. That means if you have configured your engine as:
```
engine = create_engine("mysql://u:p@host/db", pool_size=10, max_overflow=20)
```
The above Engine will allow at most 30 connections to be in play at any time, not including connections that were detached from the engine or invalidated. If a request for a new connection arrives and 30 connections are already in use by other parts of the application, the connection pool will block for a fixed period of time, before timing out and raising this error message.

In order to allow for a higher number of connections be in use at once, the pool can be adjusted using the create_engine.pool_size and create_engine.max_overflow parameters as passed to the create_engine() function. The timeout to wait for a connection to be available is configured using the create_engine.pool_timeout parameter.
The pool can be configured to have unlimited overflow by setting create_engine.max_overflow to the value “-1”. With this setting, the pool will still maintain a fixed pool of connections, however it will never block upon a new connection being requested; it will instead unconditionally make a new connection if none are available.

However, when running in this way, if the application has an issue where it is using up all available connectivity resources, it will eventually hit the configured limit of available connections on the database itself, which will again return an error. More seriously, when the application exhausts the database of connections, it usually will have caused a great amount of resources to be used up before failing, and can also interfere with other applications and database status mechanisms that rely upon being able to connect to the database.

Given the above, the connection pool can be looked at as a safety valve for connection use, providing a critical layer of protection against a rogue application causing the entire database to become unavailable to all other applications. When receiving this error message, it is vastly preferable to repair the issue using up too many connections and/or configure the limits appropriately, rather than allowing for unlimited overflow which does not actually solve the underlying issue.

What causes an application to use up all the connections that it has available?

The application is fielding too many concurrent requests to do work based on the configured value for the pool - This is the most straightforward cause. If you have an application that runs in a thread pool that allows for 30 concurrent threads, with one connection in use per thread, if your pool is not configured to allow at least 30 connections checked out at once, you will get this error once your application receives enough concurrent requests. Solution is to raise the limits on the pool or lower the number of concurrent threads.
The application is not returning connections to the pool - This is the next most common reason, which is that the application is making use of the connection pool, but the program is failing to release these connections and is instead leaving them open. The connection pool as well as the ORM Session do have logic such that when the session and/or connection object is garbage collected, it results in the underlying connection resources being released, however this behavior cannot be relied upon to release resources in a timely manner.

A common reason this can occur is that the application uses ORM sessions and does not call Session.close() upon them one the work involving that session is complete. Solution is to make sure ORM sessions if using the ORM, or engine-bound Connection objects if using Core, are explicitly closed at the end of the work being done, either via the appropriate .close() method, or by using one of the available context managers (e.g. “with:” statement) to properly release the resource.
The application is attempting to run long-running transactions - A database transaction is a very expensive resource, and should never be left idle waiting for some event to occur. If an application is waiting for a user to push a button, or a result to come off of a long running job queue, or is holding a persistent connection open to a browser, don’t keep a database transaction open for the whole time. As the application needs to work with the database and interact with an event, open a short-lived transaction at that point and then close it.
The application is deadlocking - Also a common cause of this error and more difficult to grasp, if an application is not able to complete its use of a connection either due to an application-side or database-side deadlock, the application can use up all the available connections which then leads to additional requests receiving this error. Reasons for deadlocks include:
- Using an implicit async system such as gevent or eventlet without properly monkeypatching all socket libraries and drivers, or which has bugs in not fully covering for all monkeypatched driver methods, or less commonly when the async system is being used against CPU-bound workloads and greenlets making use of database resources are simply waiting too long to attend to them. Neither implicit nor explicit async programming frameworks are typically necessary or appropriate for the vast majority of relational database operations; if an application must use an async system for some area of functionality, it’s best that database-oriented business methods run within traditional threads that pass messages to the async part of the application.
- A database side deadlock, e.g. rows are mutually deadlocked
- Threading errors, such as mutexes in a mutual deadlock, or calling upon an already locked mutex in the same thread

Keep in mind an alternative to using pooling is to turn off pooling entirely. See the section Switching Pool Implementations for background on this. However, note that when this error message is occurring, it is always due to a bigger problem in the application itself; the pool just helps to reveal the problem sooner.

See also

Connection Pooling

Working with Engines and Connections

Can’t reconnect until invalid transaction is rolled back

This error condition refers to the case where a Connection was invalidated, either due to a database disconnect detection or due to an explicit call to Connection.invalidate(), but there is still a transaction present that was initiated by the Connection.begin() method. When a connection is invalidated, any Transaction that was in progress is now in an invalid state, and must be explicitly rolled back in order to remove it from the Connection.

This connection is on an inactive transaction. Please rollback() fully before proceeding

This error condition was added to SQLAlchemy as of version 1.4. The error refers to the state where a Connection is placed into a transaction using a method like Connection.begin(), and then a further “marker” transaction is created within that scope; the “marker” transaction is then rolled back using Transaction.rollback() or closed using Transaction.close(), however the outer transaction is still present in an “inactive” state and must be rolled back.

The pattern looks like:

engine = create_engine(...)
connection = engine.connect()
transaction1 = connection.begin()
# this is a "sub" or "marker" transaction, a logical nesting
# structure based on "real" transaction transaction1
transaction2 = connection.begin()
transaction2.rollback()
# transaction1 is still present and needs explicit rollback,
# so this will raise
connection.execute(text("select 1"))

Above, transaction2 is a “marker” transaction, which indicates a logical nesting of transactions within an outer one; while the inner transaction can roll back the whole transaction via its rollback() method, its commit() method has no effect except to close the scope of the “marker” transaction itself. The call to transaction2.rollback() has the effect of deactivating transaction1 which means it is essentially rolled back at the database level, however is still present in order to accommodate a consistent nesting pattern of transactions.

The correct resolution is to ensure the outer transaction is also rolled back:

transaction1.rollback()

This pattern is not commonly used in Core. Within the ORM, a similar issue can occur which is the product of the ORM’s “logical” transaction structure; this is described in the FAQ entry at “This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar).

The “subtransaction” pattern is to be removed in SQLAlchemy 2.0 so that this particular programming pattern will no longer be available and this error message will no longer occur in Core.

DBAPI Errors

The Python database API, or DBAPI, is a specification for database drivers which can be located at Pep-249. This API specifies a set of exception classes that accommodate the full range of failure modes of the database.

SQLAlchemy does not generate these exceptions directly. Instead, they are intercepted from the database driver and wrapped by the SQLAlchemy-provided exception DBAPIError, however the messaging within the exception is generated by the driver, not SQLAlchemy.

InterfaceError

Exception raised for errors that are related to the database interface rather than the database itself.