0
# Error Handling and Exceptions
1

2
Comprehensive exception hierarchy following DB-API 2.0 standards, with specific error types for different failure scenarios and custom error handling capabilities.
3

4
## Capabilities
5

6
### Exception Hierarchy
7

8
Complete exception hierarchy for both mysql.connector and mysqlx modules.
9

10
```python { .api }
11
class Error(Exception):
12
    """
13
    Base exception class for all MySQL errors.
14
    
15
    All MySQL-specific exceptions inherit from this base class,
16
    providing common attributes and methods for error handling.
17
    """
18
    
19
    def __init__(self, msg=None, errno=None, values=None, sqlstate=None):
20
        """
21
        Initialize error with message and optional details.
22
        
23
        Parameters:
24
        - msg (str): Error message
25
        - errno (int): MySQL error number
26
        - values (tuple): Message format values
27
        - sqlstate (str): SQL state code
28
        """
29
    
30
    @property
31
    def errno(self):
32
        """MySQL error number"""
33
    
34
    @property
35
    def sqlstate(self):
36
        """SQL state code"""
37
    
38
    @property
39
    def msg(self):
40
        """Error message"""
41

42
class Warning(Error):
43
    """
44
    Exception for important warnings.
45
    
46
    Raised for significant conditions that are not errors but should
47
    be brought to the application's attention.
48
    """
49

50
class InterfaceError(Error):
51
    """
52
    Interface-related errors.
53
    
54
    Raised for errors related to the database interface rather than
55
    the database itself. Examples: connection parameter errors,
56
    programming interface misuse.
57
    """
58

59
class DatabaseError(Error):
60
    """
61
    Database-related errors.
62
    
63
    Base class for errors that are related to the database operation
64
    itself rather than interface issues.
65
    """
66

67
class DataError(DatabaseError):
68
    """
69
    Data processing errors.
70
    
71
    Raised for errors due to problems with the processed data:
72
    numeric value out of range, division by zero, invalid date, etc.
73
    """
74

75
class OperationalError(DatabaseError):
76
    """
77
    Database operation errors.
78
    
79
    Raised for errors related to database operation and not necessarily
80
    under the control of the programmer: connection lost, database name
81
    not found, transaction could not be processed, memory allocation error.
82
    """
83

84
class IntegrityError(DatabaseError):
85
    """
86
    Relational integrity violations.
87
    
88
    Raised when the relational integrity of the database is affected:
89
    foreign key check fails, duplicate key, constraint violations.
90
    """
91

92
class InternalError(DatabaseError):
93
    """
94
    Internal database errors.
95
    
96
    Raised when the database encounters an internal error: cursor is
97
    not valid anymore, transaction is out of sync, etc.
98
    """
99

100
class ProgrammingError(DatabaseError):
101
    """
102
    Programming errors.
103
    
104
    Raised for programming errors: table not found, error in SQL syntax,
105
    wrong number of parameters specified, etc.
106
    """
107

108
class NotSupportedError(DatabaseError):
109
    """
110
    Unsupported feature errors.
111
    
112
    Raised when using a database method or feature that is not supported
113
    by the database version or configuration.
114
    """
115

116
class PoolError(Error):
117
    """
118
    Connection pooling errors.
119
    
120
    Raised for errors specific to connection pool operations:
121
    pool full, invalid pool configuration, pool connection errors.
122
    """
123
```
124

125
### Custom Error Exceptions
126

127
Create custom exceptions for specific MySQL server errors.
128

129
```python { .api }
130
def custom_error_exception(error=None, exception=None):
131
    """
132
    Define custom exceptions for MySQL server errors.
133
    
134
    Allows mapping specific MySQL error codes to custom exception classes
135
    for more granular error handling.
136
    
137
    Parameters:
138
    - error (dict): Error code to exception class mapping
139
    - exception (Exception): Custom exception class
140
    
141
    Returns:
142
    dict: Current error mappings
143
    
144
    Example:
145
    # Map specific error to custom exception
146
    custom_error_exception({1050: MyTableExistsError})
147
    
148
    # Define custom exception for error code
149
    class MyDuplicateKeyError(IntegrityError):
150
        pass
151
    
152
    custom_error_exception({1062: MyDuplicateKeyError})
153
    """
154
```
155

156
**Usage Example:**
157

158
```python
159
import mysql.connector
160
from mysql.connector import Error, IntegrityError, ProgrammingError
161

162
# Custom exception for duplicate keys
163
class DuplicateKeyError(IntegrityError):
164
    def __init__(self, msg=None, errno=None, values=None, sqlstate=None):
165
        super().__init__(msg, errno, values, sqlstate)
166
        self.duplicate_key = True
167

168
# Register custom exception
169
mysql.connector.custom_error_exception({1062: DuplicateKeyError})
170

171
try:
172
    connection = mysql.connector.connect(
173
        user='myuser',
174
        password='mypassword',
175
        host='localhost',
176
        database='mydatabase'
177
    )
178
    
179
    cursor = connection.cursor()
180
    cursor.execute("INSERT INTO users (email) VALUES (%s)", ("john@example.com",))
181
    
182
except DuplicateKeyError as err:
183
    print(f"Duplicate key error: {err}")
184
    print(f"Error code: {err.errno}")
185
    print(f"SQL state: {err.sqlstate}")
186
    
187
except IntegrityError as err:
188
    print(f"Integrity constraint violation: {err}")
189
    
190
except ProgrammingError as err:
191
    print(f"Programming error: {err}")
192
    
193
except Error as err:
194
    print(f"MySQL error: {err}")
195
    
196
finally:
197
    if 'connection' in locals() and connection.is_connected():
198
        cursor.close()
199
        connection.close()
200
```
201

202
### Error Code Constants
203

204
MySQL server error codes for specific error handling.
205

206
```python { .api }
207
class errorcode:
208
    """
209
    MySQL server error codes.
210
    
211
    Constants for common MySQL error conditions to enable
212
    specific error handling based on error codes.
213
    """
214
    
215
    # Connection errors
216
    CR_CONNECTION_ERROR: int = 2003
217
    CR_CONN_HOST_ERROR: int = 2005
218
    CR_SERVER_GONE_ERROR: int = 2006
219
    CR_SERVER_LOST: int = 2013
220
    
221
    # Authentication errors  
222
    ER_ACCESS_DENIED_ERROR: int = 1045
223
    ER_BAD_DB_ERROR: int = 1049
224
    
225
    # Data integrity errors
226
    ER_DUP_ENTRY: int = 1062
227
    ER_NO_REFERENCED_ROW: int = 1216
228
    ER_ROW_IS_REFERENCED: int = 1217
229
    ER_NO_REFERENCED_ROW_2: int = 1452
230
    ER_ROW_IS_REFERENCED_2: int = 1451
231
    
232
    # Schema errors
233
    ER_BAD_TABLE_ERROR: int = 1051
234
    ER_NO_SUCH_TABLE: int = 1146
235
    ER_TABLE_EXISTS_ERROR: int = 1050
236
    ER_BAD_FIELD_ERROR: int = 1054
237
    ER_DUP_FIELDNAME: int = 1060
238
    
239
    # Syntax errors
240
    ER_PARSE_ERROR: int = 1064
241
    ER_WRONG_VALUE_COUNT_ON_ROW: int = 1136
242
    
243
    # Lock errors
244
    ER_LOCK_WAIT_TIMEOUT: int = 1205
245
    ER_LOCK_DEADLOCK: int = 1213
246
    
247
    # Storage errors
248
    ER_DISK_FULL: int = 1021
249
    ER_OUT_OF_RESOURCES: int = 1041
250
```
251

252
**Usage Example:**
253

254
```python
255
import mysql.connector
256
from mysql.connector import errorcode
257

258
try:
259
    connection = mysql.connector.connect(**config)
260
    cursor = connection.cursor()
261
    cursor.execute("SELECT * FROM nonexistent_table")
262
    
263
except mysql.connector.Error as err:
264
    if err.errno == errorcode.ER_NO_SUCH_TABLE:
265
        print("Table doesn't exist")
266
    elif err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
267
        print("Access denied")
268
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
269
        print("Database doesn't exist")
270
    else:
271
        print(f"Error {err.errno}: {err}")
272
```
273

274
### Context Manager Support
275

276
Automatic resource cleanup using context managers.
277

278
```python { .api }
279
class MySQLConnection:
280
    """Connection with context manager support"""
281
    
282
    def __enter__(self):
283
        """Enter context manager"""
284
        return self
285
    
286
    def __exit__(self, exc_type, exc_value, traceback):
287
        """Exit context manager and close connection"""
288
        self.close()
289

290
class MySQLCursor:
291
    """Cursor with context manager support"""
292
    
293
    def __enter__(self):
294
        """Enter context manager"""
295
        return self
296
    
297
    def __exit__(self, exc_type, exc_value, traceback):
298
        """Exit context manager and close cursor"""
299
        self.close()
300
```
301

302
**Usage Example:**
303

304
```python
305
import mysql.connector
306

307
# Connection context manager
308
with mysql.connector.connect(**config) as connection:
309
    with connection.cursor() as cursor:
310
        cursor.execute("SELECT * FROM users")
311
        results = cursor.fetchall()
312
        
313
        for row in results:
314
            print(row)
315
    # Cursor automatically closed
316
# Connection automatically closed
317

318
# Handle exceptions with context manager
319
try:
320
    with mysql.connector.connect(**config) as connection:
321
        with connection.cursor() as cursor:
322
            cursor.execute("INSERT INTO users (email) VALUES (%s)", ("test@example.com",))
323
            connection.commit()
324
            
325
except mysql.connector.IntegrityError:
326
    print("Integrity constraint violation")
327
except mysql.connector.Error as err:
328
    print(f"Database error: {err}")
329
```
330

331
### X DevAPI Error Handling
332

333
Error handling for mysqlx module operations.
334

335
```python { .api }
336
# mysqlx module exceptions (same hierarchy as mysql.connector)
337
class Error(Exception):
338
    """Base exception for X DevAPI operations"""
339

340
class Warning(Error):
341
    """Warning exception for X DevAPI"""
342

343
class InterfaceError(Error):
344
    """Interface error for X DevAPI"""
345

346
class DatabaseError(Error):
347
    """Database error for X DevAPI"""
348

349
class DataError(DatabaseError):
350
    """Data processing error for X DevAPI"""
351

352
class OperationalError(DatabaseError):
353
    """Operational error for X DevAPI"""
354

355
class IntegrityError(DatabaseError):
356
    """Integrity constraint violation for X DevAPI"""
357

358
class InternalError(DatabaseError):
359
    """Internal error for X DevAPI"""
360

361
class ProgrammingError(DatabaseError):
362
    """Programming error for X DevAPI"""
363

364
class NotSupportedError(DatabaseError):
365
    """Unsupported feature error for X DevAPI"""
366
```
367

368
**Usage Example:**
369

370
```python
371
import mysqlx
372

373
try:
374
    session = mysqlx.get_session({
375
        'host': 'localhost',
376
        'port': 33060,
377
        'user': 'myuser',
378
        'password': 'mypassword'
379
    })
380
    
381
    schema = session.get_schema('mydb')
382
    collection = schema.get_collection('users')
383
    
384
    # This might raise IntegrityError if document violates constraints
385
    collection.add({
386
        'email': 'duplicate@example.com',
387
        'user_id': 123
388
    }).execute()
389
    
390
except mysqlx.InterfaceError as err:
391
    print(f"Connection or interface error: {err}")
392
    
393
except mysqlx.IntegrityError as err:
394
    print(f"Document violates constraints: {err}")
395
    
396
except mysqlx.OperationalError as err:
397
    print(f"Database operation failed: {err}")
398
    
399
except mysqlx.Error as err:
400
    print(f"X DevAPI error: {err}")
401
    
402
finally:
403
    if 'session' in locals():
404
        session.close()
405
```
406

407
### Logging and Debugging
408

409
Enable logging for troubleshooting connection and query issues.
410

411
```python { .api }
412
import logging
413

414
# Enable MySQL Connector logging
415
logger = logging.getLogger('mysql.connector')
416
logger.setLevel(logging.DEBUG)
417
handler = logging.StreamHandler()
418
formatter = logging.Formatter(
419
    '%(asctime)s - %(name)s - %(levelname)s - %(message)s'
420
)
421
handler.setFormatter(formatter)
422
logger.addHandler(handler)
423
```
424

425
**Usage Example:**
426

427
```python
428
import logging
429
import mysql.connector
430

431
# Configure logging
432
logging.basicConfig(level=logging.DEBUG)
433
logger = logging.getLogger('mysql.connector')
434

435
try:
436
    connection = mysql.connector.connect(
437
        user='myuser',
438
        password='mypassword',
439
        host='localhost',
440
        database='mydatabase'
441
    )
442
    
443
    cursor = connection.cursor()
444
    cursor.execute("SELECT COUNT(*) FROM users")
445
    result = cursor.fetchone()
446
    print(f"User count: {result[0]}")
447
    
448
except mysql.connector.Error as err:
449
    logger.error(f"Database error: {err}")
450
    
451
finally:
452
    if 'connection' in locals() and connection.is_connected():
453
        cursor.close()
454
        connection.close()
455
```
456

457
### Utility Functions
458

459
Helper functions for error handling and configuration management.
460

461
```python { .api }
462
def custom_error_exception(error=None, exception=None):
463
    """
464
    Define custom exceptions for MySQL server errors.
465
    
466
    This function defines custom exceptions for MySQL server errors and
467
    returns the current set customizations. If error is a MySQL Server 
468
    error number, then you have to pass also the exception class.
469
    
470
    Parameters:
471
    - error (int|dict): MySQL error number or dictionary mapping errors to exceptions
472
    - exception (Exception): Exception class to raise for the error
473
    
474
    Returns:
475
    dict: Current set of custom error mappings
476
    
477
    Raises:
478
    ValueError: Invalid error number or exception type
479
    
480
    Examples:
481
    # Map specific error to custom exception
482
    mysql.connector.custom_error_exception(1028, mysql.connector.DatabaseError)
483
    
484
    # Map multiple errors using dictionary
485
    mysql.connector.custom_error_exception({
486
        1028: mysql.connector.DatabaseError,
487
        1029: mysql.connector.OperationalError,
488
    })
489
    
490
    # Reset all customizations
491
    mysql.connector.custom_error_exception({})
492
    """
493

494
def read_option_files(*args, **kwargs):
495
    """
496
    Read MySQL option files for connection configuration.
497
    
498
    Parameters:
499
    - *args: Option file paths
500
    - **kwargs: Configuration options including 'option_files' key
501
    
502
    Returns:
503
    dict: Configuration dictionary with values from option files
504
    
505
    Example:
506
    config = mysql.connector.read_option_files(
507
        option_files=['~/.my.cnf', '/etc/mysql/my.cnf']
508
    )
509
    connection = mysql.connector.connect(**config)
510
    """
511
```
512

513
## Types
514

515
```python { .api }
516
ErrorMapping = dict[int, type[Exception]]
517

518
ExceptionInfo = {
519
    'errno': int,
520
    'sqlstate': str, 
521
    'msg': str,
522
    'values': tuple
523
}
524
```