0
# Error Handling
1
2
Complete exception hierarchy following DB-API 2.0 specification for handling database errors, operational issues, and programming mistakes.
3
4
## Capabilities
5
6
### Exception Hierarchy
7
8
CyMySQL implements the standard Python DB-API 2.0 exception hierarchy for consistent error handling across database operations.
9
10
```python { .api }
11
class MySQLError(Exception):
12
"""
13
Base exception for all MySQL-related errors.
14
15
Attributes:
16
- errno (int): MySQL error number (-1 if not applicable)
17
- errmsg (str): Error message description
18
"""
19
20
def __init__(self, *args):
21
"""
22
Initialize MySQL error.
23
24
Parameters:
25
- args: Error number and message, or just message
26
"""
27
28
class Warning(Warning, MySQLError):
29
"""
30
Exception raised for important warnings like data truncations
31
while inserting, etc.
32
33
Used for non-fatal issues that don't prevent operation completion
34
but indicate potential problems.
35
"""
36
37
class Error(MySQLError):
38
"""
39
Exception that is the base class of all other error exceptions
40
(not Warning).
41
42
Base class for all serious database errors.
43
"""
44
45
class InterfaceError(Error):
46
"""
47
Exception raised for errors that are related to the database
48
interface rather than the database itself.
49
50
Examples:
51
- Connection parameter errors
52
- Protocol violations
53
- Client library issues
54
"""
55
56
class DatabaseError(Error):
57
"""
58
Exception raised for errors that are related to the database.
59
60
Base class for all database-related errors.
61
"""
62
63
class DataError(DatabaseError):
64
"""
65
Exception raised for errors that are due to problems with the
66
processed data like division by zero, numeric value out of range, etc.
67
68
Examples:
69
- Data truncation warnings
70
- Invalid data format
71
- Out of range values
72
"""
73
74
class OperationalError(DatabaseError):
75
"""
76
Exception raised for errors that are related to the database's
77
operation and not necessarily under the control of the programmer,
78
e.g. an unexpected disconnect occurs, the data source name is not
79
found, a transaction could not be processed, a memory allocation
80
error occurred during processing, etc.
81
82
Examples:
83
- Connection lost
84
- Database access denied
85
- Lock deadlock
86
- Server shutdown
87
"""
88
89
class IntegrityError(DatabaseError):
90
"""
91
Exception raised when the relational integrity of the database
92
is affected, e.g. a foreign key check fails, duplicate key, etc.
93
94
Examples:
95
- Duplicate key violations
96
- Foreign key constraint failures
97
- NOT NULL constraint violations
98
"""
99
100
class InternalError(DatabaseError):
101
"""
102
Exception raised when the database encounters an internal
103
error, e.g. the cursor is not valid anymore, the transaction is
104
out of sync, etc.
105
106
Examples:
107
- Cursor state errors
108
- Transaction state errors
109
- Internal server errors
110
"""
111
112
class ProgrammingError(DatabaseError):
113
"""
114
Exception raised for programming errors, e.g. table not found
115
or already exists, syntax error in the SQL statement, wrong number
116
of parameters specified, etc.
117
118
Examples:
119
- SQL syntax errors
120
- Table/column doesn't exist
121
- Wrong number of parameters
122
- Database doesn't exist
123
"""
124
125
class NotSupportedError(DatabaseError):
126
"""
127
Exception raised in case a method or database API was used
128
which is not supported by the database, e.g. requesting a
129
.rollback() on a connection that does not support transaction or
130
has transactions turned off.
131
132
Examples:
133
- Unsupported SQL features
134
- Disabled functionality
135
- Version compatibility issues
136
"""
137
```
138
139
### Error Information Functions
140
141
Functions for extracting and processing MySQL error information from server responses.
142
143
```python { .api }
144
def raise_mysql_exception(data):
145
"""
146
Parse MySQL error packet and raise appropriate exception.
147
148
Parameters:
149
- data (bytes): Raw error packet data from MySQL server
150
151
Raises:
152
Appropriate MySQL exception based on error code
153
"""
154
155
def _get_error_info(data):
156
"""
157
Extract error information from MySQL error packet.
158
159
Parameters:
160
- data (bytes): Raw error packet data
161
162
Returns:
163
tuple: (errno, sqlstate, errorvalue)
164
"""
165
166
def _check_mysql_exception(errinfo):
167
"""
168
Map MySQL error code to appropriate exception class and raise it.
169
170
Parameters:
171
- errinfo (tuple): Error information (errno, sqlstate, errorvalue)
172
173
Raises:
174
Appropriate MySQL exception class
175
"""
176
```
177
178
## Usage Examples
179
180
### Basic Error Handling
181
182
```python
183
import cymysql
184
from cymysql import (
185
OperationalError, ProgrammingError, IntegrityError,
186
DataError, InternalError, InterfaceError
187
)
188
189
try:
190
conn = cymysql.connect(
191
host='localhost',
192
user='invalid_user',
193
password='wrong_password',
194
db='nonexistent_db'
195
)
196
except OperationalError as e:
197
print(f"Connection failed: {e}")
198
print(f"Error code: {e.errno}")
199
print(f"Error message: {e.errmsg}")
200
except InterfaceError as e:
201
print(f"Interface error: {e}")
202
```
203
204
### SQL Execution Error Handling
205
206
```python
207
import cymysql
208
from cymysql import ProgrammingError, IntegrityError, DataError
209
210
conn = cymysql.connect(host='localhost', user='root', password='', db='test')
211
cursor = conn.cursor()
212
213
try:
214
# SQL syntax error
215
cursor.execute("SELCT * FROM users") # Typo in SELECT
216
except ProgrammingError as e:
217
print(f"SQL syntax error: {e}")
218
219
try:
220
# Table doesn't exist
221
cursor.execute("SELECT * FROM nonexistent_table")
222
except ProgrammingError as e:
223
print(f"Table not found: {e}")
224
225
try:
226
# Duplicate key error
227
cursor.execute("INSERT INTO users (id, email) VALUES (1, 'test@example.com')")
228
cursor.execute("INSERT INTO users (id, email) VALUES (1, 'duplicate@example.com')")
229
except IntegrityError as e:
230
print(f"Integrity constraint violation: {e}")
231
232
try:
233
# Data too long
234
cursor.execute("INSERT INTO users (name) VALUES (%s)", ('x' * 1000,))
235
except DataError as e:
236
print(f"Data error: {e}")
237
238
cursor.close()
239
conn.close()
240
```
241
242
### Connection State Error Handling
243
244
```python
245
import cymysql
246
from cymysql import OperationalError, InternalError
247
248
conn = cymysql.connect(host='localhost', user='root', password='', db='test')
249
250
try:
251
# Simulate connection loss
252
cursor = conn.cursor()
253
254
# Check if connection is alive
255
conn.ping()
256
257
cursor.execute("SELECT SLEEP(1)")
258
result = cursor.fetchone()
259
260
except OperationalError as e:
261
print(f"Connection lost: {e}")
262
# Try to reconnect
263
try:
264
conn.ping(reconnect=True)
265
print("Reconnection successful")
266
except OperationalError:
267
print("Reconnection failed")
268
269
except InternalError as e:
270
print(f"Internal database error: {e}")
271
272
finally:
273
try:
274
cursor.close()
275
conn.close()
276
except:
277
pass
278
```
279
280
### Transaction Error Handling
281
282
```python
283
import cymysql
284
from cymysql import OperationalError, IntegrityError, InternalError
285
286
conn = cymysql.connect(host='localhost', user='root', password='', db='test')
287
cursor = conn.cursor()
288
289
try:
290
# Start transaction
291
conn.autocommit(False)
292
293
# Series of operations
294
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
295
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
296
297
# Commit transaction
298
conn.commit()
299
print("Transaction completed successfully")
300
301
except IntegrityError as e:
302
print(f"Constraint violation: {e}")
303
conn.rollback()
304
305
except OperationalError as e:
306
print(f"Database operation failed: {e}")
307
conn.rollback()
308
309
except Exception as e:
310
print(f"Unexpected error: {e}")
311
conn.rollback()
312
313
finally:
314
conn.autocommit(True)
315
cursor.close()
316
conn.close()
317
```
318
319
### Comprehensive Error Handling Wrapper
320
321
```python
322
import cymysql
323
from cymysql import MySQLError, OperationalError, ProgrammingError
324
import logging
325
import time
326
327
class DatabaseManager:
328
def __init__(self, **conn_params):
329
self.conn_params = conn_params
330
self.conn = None
331
self.max_retries = 3
332
self.retry_delay = 1
333
334
def connect(self):
335
"""Connect with retry logic."""
336
for attempt in range(self.max_retries):
337
try:
338
self.conn = cymysql.connect(**self.conn_params)
339
logging.info("Database connection established")
340
return
341
except OperationalError as e:
342
logging.warning(f"Connection attempt {attempt + 1} failed: {e}")
343
if attempt < self.max_retries - 1:
344
time.sleep(self.retry_delay)
345
else:
346
raise
347
348
def execute_query(self, query, params=None):
349
"""Execute query with error handling."""
350
if not self.conn:
351
self.connect()
352
353
cursor = None
354
try:
355
cursor = self.conn.cursor()
356
cursor.execute(query, params)
357
358
if query.strip().upper().startswith('SELECT'):
359
return cursor.fetchall()
360
else:
361
self.conn.commit()
362
return cursor.rowcount
363
364
except ProgrammingError as e:
365
logging.error(f"SQL programming error: {e}")
366
raise
367
except OperationalError as e:
368
logging.error(f"Database operational error: {e}")
369
# Try to reconnect for connection issues
370
if "connection" in str(e).lower():
371
self.conn = None
372
self.connect()
373
raise
374
except MySQLError as e:
375
logging.error(f"MySQL error: {e}")
376
self.conn.rollback()
377
raise
378
finally:
379
if cursor:
380
cursor.close()
381
382
def close(self):
383
"""Close connection safely."""
384
if self.conn:
385
try:
386
self.conn.close()
387
logging.info("Database connection closed")
388
except Exception as e:
389
logging.warning(f"Error closing connection: {e}")
390
391
# Usage
392
db = DatabaseManager(host='localhost', user='root', password='', db='test')
393
394
try:
395
result = db.execute_query("SELECT COUNT(*) FROM users")
396
print(f"User count: {result[0][0]}")
397
398
db.execute_query(
399
"INSERT INTO users (name, email) VALUES (%s, %s)",
400
('John Doe', 'john@example.com')
401
)
402
403
except MySQLError as e:
404
print(f"Database error occurred: {e}")
405
finally:
406
db.close()
407
```
408
409
### Async Error Handling
410
411
```python
412
import asyncio
413
import cymysql.aio
414
from cymysql import OperationalError, ProgrammingError
415
416
async def async_error_handling_example():
417
conn = None
418
try:
419
conn = await cymysql.aio.connect(
420
host='localhost',
421
user='root',
422
password='',
423
db='test'
424
)
425
426
async with conn.cursor() as cursor:
427
try:
428
await cursor.execute("SELECT * FROM nonexistent_table")
429
result = await cursor.fetchall()
430
except ProgrammingError as e:
431
print(f"Table not found: {e}")
432
433
try:
434
# Connection timeout test
435
await cursor.execute("SELECT SLEEP(30)")
436
except OperationalError as e:
437
print(f"Query timeout: {e}")
438
439
except OperationalError as e:
440
print(f"Connection error: {e}")
441
except Exception as e:
442
print(f"Unexpected error: {e}")
443
finally:
444
if conn:
445
conn.close()
446
447
asyncio.run(async_error_handling_example())
448
```
449
450
### Error Code Mapping
451
452
```python
453
import cymysql
454
from cymysql.constants import ER
455
456
# Common MySQL error codes
457
ERROR_CODES = {
458
ER.ACCESS_DENIED_ERROR: "Access denied",
459
ER.BAD_DB_ERROR: "Unknown database",
460
ER.BAD_TABLE_ERROR: "Unknown table",
461
ER.DUP_ENTRY: "Duplicate entry",
462
ER.NO_SUCH_TABLE: "Table doesn't exist",
463
ER.SYNTAX_ERROR: "SQL syntax error",
464
ER.LOCK_DEADLOCK: "Deadlock found"
465
}
466
467
def handle_mysql_error(e):
468
"""Handle MySQL error with specific error code logic."""
469
if hasattr(e, 'errno') and e.errno in ERROR_CODES:
470
print(f"Known error: {ERROR_CODES[e.errno]}")
471
472
# Specific handling for different error types
473
if e.errno == ER.LOCK_DEADLOCK:
474
print("Deadlock detected - retry transaction")
475
elif e.errno == ER.DUP_ENTRY:
476
print("Duplicate key - check unique constraints")
477
elif e.errno == ER.ACCESS_DENIED_ERROR:
478
print("Check user permissions")
479
else:
480
print(f"Unknown MySQL error: {e}")
481
482
# Usage in exception handler
483
try:
484
# Database operation
485
pass
486
except cymysql.MySQLError as e:
487
handle_mysql_error(e)
488
```
489
490
## Error Handling Best Practices
491
492
1. **Always use specific exception types** - Catch specific exceptions rather than generic `Exception`
493
2. **Check error codes** - Use `errno` attribute for specific error handling logic
494
3. **Implement retry logic** - For transient errors like connection loss or deadlocks
495
4. **Log errors appropriately** - Include error codes and messages in logs
496
5. **Clean up resources** - Use try/finally or context managers to ensure cleanup
497
6. **Handle connection loss** - Implement reconnection logic for long-running applications
498
7. **Validate input early** - Check parameters before database operations
499
8. **Use transactions** - Wrap related operations in transactions with proper rollback
500
9. **Monitor error patterns** - Track recurring errors for system health monitoring
501
10. **Provide user-friendly messages** - Don't expose internal error details to end users