MySQL Error 1317 (ER_QUERY_INTERRUPTED) occurs when a long-running query is forcibly stopped by a KILL command, user interruption (Ctrl+C), or server shutdown. The error typically indicates an external interrupt rather than a query syntax or logic problem. Most cases resolve by allowing queries to complete or implementing proper timeout handling.
ERROR 1317 (SQLSTATE 70100) is returned when a query execution is forcibly terminated by an external event. Unlike other MySQL errors that indicate a problem with the query itself, Error 1317 signals that the query was interrupted mid-execution by: a KILL QUERY command from another session, a Ctrl+C interrupt from the client, server shutdown or restart, or MySQL deciding to terminate the query due to timeout or resource constraints. The error is particularly common in multi-user environments where administrative processes may terminate long-running queries, or in development where developers interrupt queries manually. When the error occurs inside a transaction, especially within InnoDB, the transaction may be rolled back depending on the query's progress. The key distinction is that Error 1317 is not a permanent error—the same query may succeed if allowed to complete without interruption.
Run a query to monitor active connections and identify if someone is killing your queries:
-- Show all current connections
SHOW PROCESSLIST;
-- Look for your query (or similar queries) and note the ID column
-- To see more details:
SHOW FULL PROCESSLIST;
-- To prevent future kills, note who has PROCESS privilege:
SELECT user, host FROM mysql.user WHERE Process_priv='Y';If you see many processes in the KILLED or interrupted state, or if your query repeatedly appears and disappears, someone may be running a KILL command. Coordinate with your database administrator to understand if this is intentional for query optimization or load management.
Check if the query is being killed due to timeout:
-- Check max_execution_time (MySQL 5.7.7+)
SHOW VARIABLES LIKE 'max_execution_time';
-- Check connect_timeout and read_timeout
SHOW VARIABLES LIKE '%timeout%';
-- If max_execution_time is very small, increase it:
SET SESSION max_execution_time=300000; -- 300 seconds
-- For persistent change, add to my.cnf:
-- max_execution_time=300000If your query legitimately needs more time, increase the timeout. However, extremely long queries may indicate inefficient SQL that should be optimized instead.
Error 1317 often results from queries that take too long. Use the slow query log to identify bottlenecks:
-- Enable slow query log if not already enabled
SET GLOBAL slow_query_log='ON';
SET GLOBAL long_query_time=5; -- Log queries taking > 5 seconds
-- Run your problematic query and check the slow query log
-- On Linux:
-- tail -50 /var/log/mysql/slow.log
-- Analyze the slow query
EXPLAIN SELECT ...;
-- Check for missing indexes
EXPLAIN FORMAT=JSON SELECT ...;Optimization strategies:
- Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses
- Break large aggregations into smaller batches
- Use partitioning for very large tables
- Consider caching results instead of recomputing them
If your query is timing out due to network or connection issues, stabilize the connection:
# If using MySQL CLI, avoid interrupts:
# - Do not close the terminal window
# - Do not press Ctrl+C
# - Disable SSH timeout by adjusting ServerAliveInterval
# For SSH connections:
ssh -o ServerAliveInterval=60 user@host
# For application code (example in Python):
# Increase connection timeout
connection = mysql.connector.connect(
host="localhost",
user="user",
password="password",
database="db",
connection_timeout=300,
autocommit=False
)For long-running operations, consider:
- Running them during off-peak hours
- Using background jobs or async processing instead of direct queries
- Implementing retry logic with exponential backoff
Handle Error 1317 gracefully in your application:
# Python example using mysql-connector-python
import mysql.connector
import time
def execute_with_retry(query, max_retries=3):
for attempt in range(max_retries):
try:
cursor = connection.cursor()
cursor.execute(query)
return cursor.fetchall()
except mysql.connector.Error as err:
if err.errno == 1317: # Query execution was interrupted
print(f"Query interrupted (attempt {attempt + 1}), retrying...")
time.sleep(2 ** attempt) # Exponential backoff
else:
raise
raise Exception("Query failed after max retries")For Node.js:
// Using mysql2
async function executeWithRetry(query, maxRetries = 3) {
for (let attempt = 0; attempt < maxRetries; attempt++) {
try {
return await connection.promise().query(query);
} catch (error) {
if (error.errno === 1317) {
console.log(`Query interrupted (attempt ${attempt + 1}), retrying...`);
await new Promise(resolve => setTimeout(resolve, 1000 * Math.pow(2, attempt)));
} else {
throw error;
}
}
}
throw new Error('Query failed after max retries');
}If this error occurs on a replica, investigate replication lag and query killing:
-- Show replica status
SHOW SLAVE STATUS\G
-- Look for:
-- - Seconds_Behind_Master: High values indicate lag
-- - Slave_SQL_Running: Should be YES
-- - Last_Error: May show Error 1317
-- Check replication filters that might be killing queries
SHOW VARIABLES LIKE 'slave_kill_allow_all';
-- View recent replication errors
SHOW ENGINE INNODB STATUS;
-- If replica is far behind, consider:
-- 1. Increasing slave_parallel_workers (MySQL 5.7+)
-- 2. Skipping problematic queries if safe
-- 3. Rebuilding the replica from a fresh backupFor replicas, Error 1317 often indicates the master killed a query that the replica is trying to replay. Ensure the replica has sufficient resources (CPU, memory, disk I/O) to keep up with the master.
In MySQL 5.7.7 and later, the max_execution_time hint allows per-query timeout control: SELECT /*+ MAX_EXECUTION_TIME(5000) */ ... This is preferable to global timeouts for flexibility.
For InnoDB tables, killing a query during a transaction may leave locks held temporarily. Monitor SHOW INNODB LOCKS to verify locks are released. If locks persist, the transaction may not have fully rolled back—restarting the connection usually clears them.
In Galera cluster environments, killing a query on one node may propagate to replicas differently depending on wsrep_on and certification process. Be cautious with KILL commands in cluster setups.
When using ProxySQL or other connection pools, error 1317 may occur if the proxy times out before MySQL. Ensure proxy timeout settings are higher than MySQL timeout settings: proxy_timeout > max_execution_time.
For very large bulk operations (LOAD DATA INFILE, etc.), consider disabling binary logging temporarily and increasing innodb_flush_log_at_trx_commit to reduce query interruption risk from I/O bottlenecks. Always test in a non-production environment first.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions