0
# Error Handling
1
2
Comprehensive exception hierarchy for robust error handling and debugging, providing specific error types that match MySQL server and client error conditions.
3
4
## Exception Hierarchy
5
6
### Base Exception Classes
7
8
```python { .api }
9
class Error(Exception):
10
"""
11
Base exception class for all MySQL Connector errors.
12
13
Attributes:
14
msg: Error message
15
errno: MySQL error number
16
sqlstate: SQL state code
17
"""
18
19
def __init__(self, msg: Optional[str] = None, errno: Optional[int] = None, sqlstate: Optional[str] = None) -> None:
20
pass
21
22
@property
23
def msg(self) -> str:
24
"""Error message."""
25
pass
26
27
@property
28
def errno(self) -> Optional[int]:
29
"""MySQL error number."""
30
pass
31
32
@property
33
def sqlstate(self) -> Optional[str]:
34
"""SQL state code."""
35
pass
36
37
class Warning(Exception):
38
"""
39
Warning exception class for non-critical issues.
40
Raised for MySQL warnings when raise_on_warnings is True.
41
"""
42
pass
43
```
44
45
### Interface and Connection Errors
46
47
```python { .api }
48
class InterfaceError(Error):
49
"""
50
Interface-related errors.
51
Problems with the database interface rather than the database itself.
52
"""
53
pass
54
55
class DatabaseError(Error):
56
"""
57
Database-related errors.
58
Base class for errors related to database operations.
59
"""
60
pass
61
```
62
63
### Database Error Subclasses
64
65
```python { .api }
66
class DataError(DatabaseError):
67
"""
68
Data processing errors.
69
Problems with data types, values, or format.
70
"""
71
pass
72
73
class OperationalError(DatabaseError):
74
"""
75
Operation-related errors.
76
Connection issues, query execution problems, server errors.
77
"""
78
pass
79
80
class IntegrityError(DatabaseError):
81
"""
82
Database constraint violations.
83
Foreign key, unique constraint, check constraint violations.
84
"""
85
pass
86
87
class InternalError(DatabaseError):
88
"""
89
Internal database errors.
90
MySQL server internal errors.
91
"""
92
pass
93
94
class ProgrammingError(DatabaseError):
95
"""
96
Programming errors.
97
SQL syntax errors, parameter issues, API misuse.
98
"""
99
pass
100
101
class NotSupportedError(DatabaseError):
102
"""
103
Unsupported operation errors.
104
Features not supported by MySQL or connector version.
105
"""
106
pass
107
```
108
109
### Connection Pool Errors
110
111
```python { .api }
112
class PoolError(Error):
113
"""
114
Connection pooling errors.
115
Pool configuration, exhaustion, and management issues.
116
"""
117
pass
118
```
119
120
### Timeout Errors
121
122
```python { .api }
123
class ConnectionTimeoutError(OperationalError):
124
"""Connection timeout errors."""
125
pass
126
127
class ReadTimeoutError(OperationalError):
128
"""Read operation timeout errors."""
129
pass
130
131
class WriteTimeoutError(OperationalError):
132
"""Write operation timeout errors."""
133
pass
134
```
135
136
## Error Utilities
137
138
### Custom Error Creation
139
140
```python { .api }
141
def custom_error_exception(error: Dict[str, Any] = None, **kwargs) -> Type[Error]:
142
"""
143
Create custom error exceptions.
144
145
Args:
146
error: Error information dictionary
147
**kwargs: Additional error parameters
148
149
Returns:
150
Appropriate Error subclass instance
151
"""
152
pass
153
154
def get_mysql_exception(packet: bytes) -> Error:
155
"""
156
Extract MySQL exception from error packet.
157
158
Args:
159
packet: MySQL error packet bytes
160
161
Returns:
162
Appropriate Error subclass instance
163
"""
164
pass
165
166
def get_exception(packet: bytes) -> Error:
167
"""
168
Get appropriate exception from error packet.
169
170
Args:
171
packet: Error packet from MySQL server
172
173
Returns:
174
Appropriate Error subclass instance
175
"""
176
pass
177
```
178
179
## Common Error Codes
180
181
### Server Error Codes (ER_*)
182
183
Common MySQL server errors that map to specific exception types:
184
185
```python { .api }
186
# Connection errors (OperationalError)
187
ER_ACCESS_DENIED_ERROR = 1045 # Access denied for user
188
ER_BAD_HOST_ERROR = 1042 # Can't get hostname for your address
189
ER_CON_COUNT_ERROR = 1040 # Too many connections
190
ER_HOST_IS_BLOCKED = 1129 # Host is blocked
191
ER_HOST_NOT_PRIVILEGED = 1130 # Host not privileged to connect
192
193
# Syntax errors (ProgrammingError)
194
ER_PARSE_ERROR = 1064 # SQL syntax error
195
ER_BAD_FIELD_ERROR = 1054 # Unknown column
196
ER_BAD_TABLE_ERROR = 1051 # Unknown table
197
ER_NO_SUCH_TABLE = 1146 # Table doesn't exist
198
ER_BAD_DB_ERROR = 1049 # Unknown database
199
200
# Constraint violations (IntegrityError)
201
ER_DUP_ENTRY = 1062 # Duplicate entry for key
202
ER_NO_REFERENCED_ROW = 1216 # Foreign key constraint fails (insert/update)
203
ER_ROW_IS_REFERENCED = 1217 # Foreign key constraint fails (delete/update)
204
ER_NO_DEFAULT_FOR_FIELD = 1364 # Field doesn't have default value
205
206
# Data errors (DataError)
207
ER_BAD_NULL_ERROR = 1048 # Column cannot be null
208
ER_DATA_TOO_LONG = 1406 # Data too long for column
209
ER_TRUNCATED_WRONG_VALUE = 1292 # Truncated incorrect value
210
ER_WARN_DATA_OUT_OF_RANGE = 1264 # Out of range value for column
211
212
# Lock errors (OperationalError)
213
ER_LOCK_WAIT_TIMEOUT = 1205 # Lock wait timeout exceeded
214
ER_LOCK_DEADLOCK = 1213 # Deadlock found when trying to get lock
215
216
# Server errors (InternalError)
217
ER_DISK_FULL = 1021 # Disk full
218
ER_OUT_OF_MEMORY = 1037 # Out of memory
219
ER_SERVER_SHUTDOWN = 1053 # Server shutdown in progress
220
```
221
222
### Client Error Codes (CR_*)
223
224
MySQL client library errors:
225
226
```python { .api }
227
# Connection errors
228
CR_CONNECTION_ERROR = 2003 # Can't connect to MySQL server
229
CR_CONN_HOST_ERROR = 2005 # Unknown MySQL server host
230
CR_SERVER_GONE_ERROR = 2006 # MySQL server has gone away
231
CR_SERVER_LOST = 2013 # Lost connection to MySQL server during query
232
233
# Protocol errors
234
CR_COMMANDS_OUT_OF_SYNC = 2014 # Commands out of sync
235
CR_UNKNOWN_ERROR = 2000 # Unknown MySQL error
236
CR_MALFORMED_PACKET = 2027 # Malformed packet
237
238
# SSL errors
239
CR_SSL_CONNECTION_ERROR = 2026 # SSL connection error
240
```
241
242
## Usage Examples
243
244
### Basic Error Handling
245
246
```python
247
import mysql.connector
248
from mysql.connector import Error, OperationalError, ProgrammingError
249
250
try:
251
connection = mysql.connector.connect(
252
host='localhost',
253
user='myuser',
254
password='mypassword',
255
database='mydatabase'
256
)
257
258
cursor = connection.cursor()
259
cursor.execute("SELECT * FROM users WHERE id = %s", (123,))
260
result = cursor.fetchone()
261
print(result)
262
263
except Error as err:
264
print(f"Database error: {err}")
265
print(f"Error code: {err.errno}")
266
print(f"SQL state: {err.sqlstate}")
267
268
finally:
269
if 'cursor' in locals():
270
cursor.close()
271
if 'connection' in locals():
272
connection.close()
273
```
274
275
### Specific Error Type Handling
276
277
```python
278
import mysql.connector
279
from mysql.connector import (
280
Error, InterfaceError, DatabaseError, OperationalError,
281
ProgrammingError, IntegrityError, DataError, PoolError
282
)
283
284
def handle_database_operation():
285
try:
286
connection = mysql.connector.connect(
287
host='localhost',
288
user='myuser',
289
password='wrongpassword', # Intentional error
290
database='mydatabase'
291
)
292
293
cursor = connection.cursor()
294
cursor.execute("INSERT INTO users (email) VALUES (%s)", ('duplicate@example.com',))
295
connection.commit()
296
297
except InterfaceError as err:
298
print(f"Interface error: {err}")
299
print("Check connector installation or configuration")
300
301
except OperationalError as err:
302
if err.errno == 1045: # Access denied
303
print("Authentication failed - check username/password")
304
elif err.errno == 2003: # Can't connect
305
print("Connection failed - check host/port")
306
elif err.errno == 1042: # Bad host
307
print("Host resolution failed")
308
else:
309
print(f"Operational error: {err}")
310
311
except ProgrammingError as err:
312
if err.errno == 1064: # Syntax error
313
print(f"SQL syntax error: {err}")
314
elif err.errno == 1054: # Unknown column
315
print(f"Column not found: {err}")
316
elif err.errno == 1146: # Table doesn't exist
317
print(f"Table not found: {err}")
318
else:
319
print(f"Programming error: {err}")
320
321
except IntegrityError as err:
322
if err.errno == 1062: # Duplicate entry
323
print(f"Duplicate key violation: {err}")
324
elif err.errno == 1216: # Foreign key constraint
325
print(f"Foreign key constraint failed: {err}")
326
else:
327
print(f"Integrity constraint violation: {err}")
328
329
except DataError as err:
330
if err.errno == 1048: # Column cannot be null
331
print(f"Required field missing: {err}")
332
elif err.errno == 1406: # Data too long
333
print(f"Data exceeds column length: {err}")
334
else:
335
print(f"Data error: {err}")
336
337
except DatabaseError as err:
338
print(f"General database error: {err}")
339
340
except Error as err:
341
print(f"MySQL Connector error: {err}")
342
343
handle_database_operation()
344
```
345
346
### Connection Pool Error Handling
347
348
```python
349
import mysql.connector
350
from mysql.connector import PoolError, Error
351
import time
352
353
def handle_pool_operations():
354
try:
355
# Create small pool for demonstration
356
config = {
357
'host': 'localhost',
358
'user': 'myuser',
359
'password': 'mypassword',
360
'database': 'mydatabase',
361
'pool_name': 'test_pool',
362
'pool_size': 2,
363
'pool_timeout': 5 # 5 second timeout
364
}
365
366
# Get connections to exhaust pool
367
connections = []
368
for i in range(3): # Try to get more than pool size
369
try:
370
conn = mysql.connector.connect(**config)
371
connections.append(conn)
372
print(f"Got connection {i+1}")
373
except PoolError as err:
374
print(f"Pool error: {err}")
375
break
376
377
except Error as err:
378
print(f"Database error: {err}")
379
380
finally:
381
# Return connections to pool
382
for conn in connections:
383
conn.close()
384
385
handle_pool_operations()
386
```
387
388
### Transaction Error Handling
389
390
```python
391
import mysql.connector
392
from mysql.connector import Error, IntegrityError, OperationalError
393
394
def transfer_with_error_handling(from_account: int, to_account: int, amount: float):
395
connection = None
396
try:
397
connection = mysql.connector.connect(
398
host='localhost',
399
user='myuser',
400
password='mypassword',
401
database='mydatabase'
402
)
403
404
connection.start_transaction()
405
cursor = connection.cursor()
406
407
# Check source account balance
408
cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (from_account,))
409
result = cursor.fetchone()
410
if not result:
411
raise ValueError(f"Account {from_account} not found")
412
413
current_balance = result[0]
414
if current_balance < amount:
415
raise ValueError(f"Insufficient funds: {current_balance} < {amount}")
416
417
# Perform transfer
418
cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
419
(amount, from_account))
420
cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
421
(amount, to_account))
422
423
# Commit transaction
424
connection.commit()
425
print(f"Successfully transferred {amount} from {from_account} to {to_account}")
426
427
except ValueError as err:
428
print(f"Business logic error: {err}")
429
if connection:
430
connection.rollback()
431
432
except IntegrityError as err:
433
print(f"Database constraint violation: {err}")
434
if connection:
435
connection.rollback()
436
437
except OperationalError as err:
438
if err.errno == 1205: # Lock timeout
439
print("Transaction timed out waiting for lock - try again later")
440
elif err.errno == 1213: # Deadlock
441
print("Deadlock detected - transaction aborted")
442
else:
443
print(f"Operational error: {err}")
444
if connection:
445
connection.rollback()
446
447
except Error as err:
448
print(f"Database error during transfer: {err}")
449
if connection:
450
connection.rollback()
451
452
finally:
453
if connection:
454
cursor.close()
455
connection.close()
456
457
# Test transfer with error handling
458
transfer_with_error_handling(1, 2, 100.0)
459
```
460
461
### Warning Handling
462
463
```python
464
import mysql.connector
465
from mysql.connector import Warning
466
467
def handle_warnings():
468
try:
469
connection = mysql.connector.connect(
470
host='localhost',
471
user='myuser',
472
password='mypassword',
473
database='mydatabase',
474
get_warnings=True, # Automatically fetch warnings
475
raise_on_warnings=False # Don't raise exceptions for warnings
476
)
477
478
cursor = connection.cursor()
479
480
# Execute statement that may generate warnings
481
cursor.execute("INSERT INTO users (age) VALUES (%s)", (150,)) # Age out of normal range
482
483
# Check for warnings
484
if connection.warning_count > 0:
485
warnings = connection.get_warnings()
486
for level, code, message in warnings:
487
print(f"Warning {code}: {message} (Level: {level})")
488
489
connection.commit()
490
491
except Warning as warn:
492
print(f"Warning raised as exception: {warn}")
493
494
except Error as err:
495
print(f"Database error: {err}")
496
497
finally:
498
cursor.close()
499
connection.close()
500
501
handle_warnings()
502
```
503
504
### Custom Error Handling with Logging
505
506
```python
507
import mysql.connector
508
from mysql.connector import Error
509
import logging
510
511
# Configure logging
512
logging.basicConfig(level=logging.ERROR, format='%(asctime)s - %(levelname)s - %(message)s')
513
logger = logging.getLogger(__name__)
514
515
def execute_with_logging(query: str, params: tuple = None):
516
"""Execute query with comprehensive error logging."""
517
connection = None
518
cursor = None
519
520
try:
521
connection = mysql.connector.connect(
522
host='localhost',
523
user='myuser',
524
password='mypassword',
525
database='mydatabase'
526
)
527
528
cursor = connection.cursor()
529
cursor.execute(query, params)
530
531
if cursor.with_rows:
532
results = cursor.fetchall()
533
logger.info(f"Query returned {len(results)} rows")
534
return results
535
else:
536
logger.info(f"Query affected {cursor.rowcount} rows")
537
return cursor.rowcount
538
539
except Error as err:
540
# Log error with context
541
logger.error(f"MySQL Error {err.errno}: {err.msg}")
542
logger.error(f"SQL State: {err.sqlstate}")
543
logger.error(f"Query: {query}")
544
logger.error(f"Parameters: {params}")
545
546
# Log specific error type
547
error_type = type(err).__name__
548
logger.error(f"Error Type: {error_type}")
549
550
# Re-raise for caller to handle
551
raise
552
553
except Exception as err:
554
logger.error(f"Unexpected error: {err}")
555
raise
556
557
finally:
558
if cursor:
559
cursor.close()
560
if connection:
561
connection.close()
562
563
# Usage with error logging
564
try:
565
results = execute_with_logging("SELECT * FROM users WHERE id = %s", (123,))
566
print(results)
567
except Error as err:
568
print(f"Database operation failed: {err}")
569
```
570
571
### Retry Logic with Exponential Backoff
572
573
```python
574
import mysql.connector
575
from mysql.connector import Error, OperationalError
576
import time
577
import random
578
579
def execute_with_retry(query: str, params: tuple = None, max_retries: int = 3):
580
"""Execute query with retry logic for transient errors."""
581
582
# Error codes that should trigger retry
583
RETRYABLE_ERRORS = {
584
1205, # Lock wait timeout
585
1213, # Deadlock
586
2006, # Server has gone away
587
2013, # Lost connection during query
588
}
589
590
for attempt in range(max_retries + 1):
591
try:
592
connection = mysql.connector.connect(
593
host='localhost',
594
user='myuser',
595
password='mypassword',
596
database='mydatabase'
597
)
598
599
cursor = connection.cursor()
600
cursor.execute(query, params)
601
602
if cursor.with_rows:
603
results = cursor.fetchall()
604
else:
605
results = cursor.rowcount
606
607
connection.commit()
608
cursor.close()
609
connection.close()
610
611
return results
612
613
except OperationalError as err:
614
cursor.close() if 'cursor' in locals() else None
615
connection.close() if 'connection' in locals() else None
616
617
if err.errno in RETRYABLE_ERRORS and attempt < max_retries:
618
# Calculate exponential backoff with jitter
619
backoff = (2 ** attempt) + random.uniform(0, 1)
620
print(f"Attempt {attempt + 1} failed with retryable error {err.errno}. "
621
f"Retrying in {backoff:.2f} seconds...")
622
time.sleep(backoff)
623
continue
624
else:
625
print(f"Non-retryable error or max retries exceeded: {err}")
626
raise
627
628
except Error as err:
629
cursor.close() if 'cursor' in locals() else None
630
connection.close() if 'connection' in locals() else None
631
print(f"Non-retryable database error: {err}")
632
raise
633
634
# Usage with retry logic
635
try:
636
result = execute_with_retry("UPDATE accounts SET balance = balance + 100 WHERE id = 1")
637
print(f"Update successful, {result} rows affected")
638
except Error as err:
639
print(f"Operation failed after retries: {err}")
640
```