Transient query execution failures when CI runs overlap

[laughingman7743]

Problem

Parallel CI runs across pull requests share one AWS account. #786 / #803 make the metadata API path tolerate throttling by answering from Glue. The query execution path has no equivalent, and it still fails transiently when several runs overlap.

Observed in run 35889837530 (PR #803 at ec384d2, running at the same time as the docs/790-notice run):

ERROR tests/sqlalchemy/test_suite.py::DateHistoricTest_awsathena+aiorest_async::test_select_direct
pyathena.error.OperationalError: Amazon Athena experienced an internal error while executing this query.
Please try submitting the query again and if the issue reoccurs, contact AWS support for further assistance.
[SQL: DELETE FROM date_table]

That was 1 of the 15 jobs; the other 14 passed. The query reached a terminal FAILED state. execute() reports that as OperationalError and does not resubmit, because only API-call errors are retried (RetryConfig).

.github/workflows/test.yaml also records a second class of overlap failure: the account's concurrent-query quota (TooManyRequestsException on StartQueryExecution). The default RetryConfig retries it with backoff, but the retry budget is shared with every other overlapping run.

Questions to settle before a change

1. Which terminal query failures are safe to resubmit, and for which statement types?
   • Athena's message explicitly asks for resubmission.
   • A resubmitted DML or DDL statement may not be idempotent.
   • The query's AthenaError (ErrorCategory / ErrorType / Retryable) may classify the failure. This is not yet verified for this error.
2. Should resubmission be a library feature, opt-in or default, or only a test-suite fixture concern?
3. For the concurrent-query quota: is the remaining failure mode the retry budget, or the number of workers across overlapping runs? This needs measuring, as Investigate persistent metadata throttling during SQLAlchemy reflection #780 did for metadata throttling.

Context

• Investigate persistent metadata throttling during SQLAlchemy reflection #780: metadata throttling measurements.
• get_table_comment and get_table_options have no throttling fallback #786 / Answer throttled metadata requests from Glue in the cursor #803: Glue fallback for throttled metadata requests. It is not applicable to query execution.

[laughingman7743]

Another occurrence, this time on DDL: run 35938390780, test / run (3.14), PR #803 at c277aa0.

FAILED tests/pyathena/sqlalchemy/test_base.py::TestSQLAlchemyAthena::test_create_table
pyathena.error.OperationalError: [ErrorCode: INTERNAL_ERROR_SERVICE] Amazon Athena experienced an internal error while executing this query. Please contact AWS support for further assistance. You will not be charged for this query.
[SQL: CREATE EXTERNAL TABLE pyathena_test_<id>.test_create_table (col STRING) LOCATION 's3://.../test_create_table/']

The job had 1,740 tests pass and this one fail. This time the error carries an ErrorCode (INTERNAL_ERROR_SERVICE) in the message, which may help with question 1 (classifying resubmittable failures). The earlier DELETE failure's message had no code.

[laughingman7743]

Findings from the Athena query history

I looked up the failed CI queries with GetQueryExecution (account used by CI, us-west-2). Most suites run in the primary workgroup.

Query	Run	Statement	AthenaError	Service processing time
acf43671-55a3-4676-a9ad-36f5e36f9851	#803, 2026-09-23 17:03Z	DELETE FROM date_table (the failure in the report above)	Category 1, Type 206, Retryable: false, empty message	364 s
0a4c8ec7…, 93aff02e…, 231d83fd…, cc3723bd…	2026-09-23 16:33Z (an overlapping run)	SQLAlchemy-suite INSERT / DELETE	Category 1, Type 206, Retryable: false, empty message	326–358 s
7840caab-6b1d-4c2c-a642-92d5f9fcfaae	2026-09-24 00:12Z	INSERT INTO some_table …	Category 2, Type 1000, Retryable: false, GENERIC_INTERNAL_ERROR: Operation timed out (Service: AWSGlue; … OperationTimeoutException)	—
ad7ee780-36c9-4620-b145-7981d581d1ba	#803, 2026-09-24 00:34Z	CREATE EXTERNAL TABLE … test_create_table (the comment above)	Category 1, Type 206, Retryable: false, "Query timeout"	65 s (61 ms queued)
c12b6407-40f8-4c63-8f28-a9da526c5d74	#808, 2026-09-24 13:10Z	Iceberg CREATE TABLE no_implicit_returning (fresh location)	Category 2, Type 1100, Retryable: false, "Invalid S3 request"	0.9 s total
aa692b79-127c-47fa-8ed6-393e67f7b642	#808, 2026-09-24 13:15Z	Iceberg INSERT INTO t (x) VALUES (NULL)	Category 2, Type 1403, Retryable: false, INTERNAL_ERROR_QUERY_ENGINE	16.8 s total

Observations:

• Every failure found has Retryable: false. This includes the DELETE whose message asks to try again.
• The INSERT failure message says a data manifest may have been written and that Athena will not delete the data. A resubmitted DML statement can therefore duplicate data.
• A table-location collision between runs does not explain these. The SQLAlchemy follower schemas and table locations are unique per worker, and the Iceberg CREATE that failed used a fresh location.
• The pattern is consistent with shared-account or S3/Glue pressure while runs overlap: statements hanging for 5–6 minutes, a DDL timing out after 65 s, Glue OperationTimeoutException, and S3 request errors. It cannot be confirmed after the fact, because the bucket has no S3 request metrics and there is no CloudTrail data-event trail.

Answers to the questions

1. Safe to resubmit? Not inside the library. Athena classifies these failures as non-retryable, and DML can be partially applied. Callers can already read the classification through result_set.error_category, error_type, and retryable. The synchronous cursors' OperationalError carries only StateChangeReason, so exposing AthenaError there would be a separate feature request.
2. Library or test suite? Test suite only. Rerun tests once on Athena service-side query failures #811 reruns a failed test once when its message is an Athena internal error or Invalid S3 request, and it shows the first attempt's traceback. On its own CI run, no rerun was needed.
3. Concurrent-query quota: no TooManyRequestsException failure appeared in the runs examined, so this remains unmeasured.

The Glue OperationTimeoutException failure is not covered by #811. It surfaces as GENERIC_INTERNAL_ERROR, which also covers genuine errors, so matching it would need a narrower pattern (for example OperationTimeoutException) if it recurs.

The library needs no change for this issue, so I propose closing it as completed by #811.

[laughingman7743]

Correction to answer 1 above: callers of the synchronous cursors can already read the AthenaError classification without a library change. The on_poll callback (Query polling callback) is also invoked on the final poll that observes FAILED, before execute() raises OperationalError. Its AthenaQueryExecution argument has error_category, error_type, and retryable. The sync and asyncio cursors behave the same way (pyathena/common.py, pyathena/aio/common.py).

So exposing AthenaError on the exception itself would only be a convenience, and no separate feature request is planned.