0
# Error Handling
1

2
Structured error handling with detailed SQL error information, error codes, and connection state management for robust application development. The MariaDB connector provides comprehensive error reporting and recovery mechanisms.
3

4
## Capabilities
5

6
### SqlError Interface
7

8
Main error class for all database-related errors with detailed diagnostic information.
9

10
```typescript { .api }
11
interface SqlError extends Error {
12
  /** Error name (always 'SqlError') */
13
  name: string;
14
  
15
  /** Human-readable error message */
16
  message: string;
17
  
18
  /** Original SQL error message from server */
19
  sqlMessage: string | null;
20
  
21
  /** Deprecated alias for sqlMessage */
22
  text: string | null;
23
  
24
  /** SQL command that caused the error */
25
  sql: string | null;
26
  
27
  /** MySQL/MariaDB error number */
28
  errno: number;
29
  
30
  /** SQL state code (SQLSTATE) */
31
  sqlState?: string | null;
32
  
33
  /** Error code string */
34
  code: string | null;
35
  
36
  /** Whether error is fatal to connection */
37
  fatal: boolean;
38
}
39
```
40

41
### SqlError Constructor
42

43
Constructor for creating SqlError instances with detailed error information.
44

45
```typescript { .api }
46
interface SqlErrorConstructor extends ErrorConstructor {
47
  new (
48
    msg: string,
49
    sql?: string,
50
    fatal?: boolean,
51
    info?: { threadId?: number },
52
    sqlState?: string | null,
53
    errno?: number,
54
    additionalStack?: string,
55
    addHeader?: boolean,
56
    cause?: unknown
57
  ): SqlError;
58
  
59
  readonly prototype: SqlError;
60
}
61

62
declare const SqlError: SqlErrorConstructor;
63
```
64

65
### Error Types and Categories
66

67
Common error categories and their characteristics:
68

69
**Connection Errors:**
70
- `ER_CONNECTION_ALREADY_CLOSED` (45001) - Connection was already closed
71
- `ER_SOCKET_UNEXPECTED_CLOSE` (45007) - Socket closed unexpectedly
72
- `ER_SOCKET_TIMEOUT` (45009) - Socket operation timed out
73
- `ER_PING_TIMEOUT` (45010) - Connection ping timed out
74

75
**Authentication Errors:**
76
- `ER_AUTHENTICATION_PLUGIN_NOT_SUPPORTED` (45016) - Auth plugin not supported
77
- `ER_AUTHENTICATION_BAD_PACKET` (45017) - Bad authentication packet
78
- `ER_AUTHENTICATION_PLUGIN_ERR` (45018) - Authentication plugin error
79

80
**Pool Errors:**
81
- `ER_POOL_ALREADY_CLOSED` (45011) - Pool was already closed
82
- `ER_GET_CONNECTION_TIMEOUT` (45012) - Connection acquisition timeout
83
- `ER_POOL_CONNECTION_CLOSED` (45013) - Pool connection was closed
84

85
**Protocol Errors:**
86
- `ER_UNEXPECTED_PACKET` (45019) - Unexpected packet received
87
- `ER_BAD_PARAMETER_VALUE` (45020) - Invalid parameter value
88
- `ER_LOCAL_INFILE_DISABLED` (45022) - LOAD LOCAL INFILE disabled
89

90
### Error Handling Patterns
91

92
```typescript
93
// Basic try-catch error handling
94
try {
95
  const result = await connection.query("SELECT * FROM users WHERE id = ?", [userId]);
96
  return result;
97
} catch (error) {
98
  if (error instanceof SqlError) {
99
    console.error('SQL Error:', {
100
      code: error.code,
101
      errno: error.errno,
102
      sqlState: error.sqlState,  
103
      message: error.sqlMessage,
104
      sql: error.sql,
105
      fatal: error.fatal
106
    });
107
    
108
    // Handle specific error types
109
    if (error.errno === 1062) { // Duplicate entry
110
      throw new Error('User already exists');
111
    } else if (error.errno === 1054) { // Unknown column
112
      throw new Error('Invalid column name in query');
113
    }
114
  }
115
  throw error;
116
}
117
```
118

119
### Error Classification and Handling
120

121
```typescript
122
class DatabaseService {
123
  private isRetryableError(error: SqlError): boolean {
124
    // Network/connection errors that can be retried
125
    const retryableCodes = [
126
      'ECONNRESET',
127
      'ENOTFOUND', 
128
      'ETIMEDOUT',
129
      'ECONNREFUSED'
130
    ];
131
    
132
    const retryableErrno = [
133
      2013, // Lost connection during query
134
      2006, // MySQL server has gone away
135
      1205, // Lock wait timeout
136
      1213  // Deadlock found
137
    ];
138
    
139
    return retryableCodes.includes(error.code || '') || 
140
           retryableErrno.includes(error.errno);
141
  }
142
  
143
  private isConnectionError(error: SqlError): boolean {
144
    return error.fatal || 
145
           error.errno >= 45001 && error.errno <= 45010;
146
  }
147
  
148
  async executeWithRetry<T>(operation: () => Promise<T>, maxRetries = 3): Promise<T> {
149
    let lastError: SqlError;
150
    
151
    for (let attempt = 1; attempt <= maxRetries; attempt++) {
152
      try {
153
        return await operation();
154
      } catch (error) {
155
        lastError = error as SqlError;
156
        
157
        if (!this.isRetryableError(lastError) || attempt === maxRetries) {
158
          throw lastError;
159
        }
160
        
161
        console.warn(`Attempt ${attempt} failed, retrying:`, lastError.message);
162
        await this.delay(Math.pow(2, attempt) * 1000); // Exponential backoff
163
      }
164
    }
165
    
166
    throw lastError!;
167
  }
168
  
169
  private delay(ms: number): Promise<void> {
170
    return new Promise(resolve => setTimeout(resolve, ms));
171
  }
172
}
173
```
174

175
### Connection Error Recovery
176

177
```typescript
178
class ConnectionManager {
179
  private connection: Connection | null = null;
180
  private pool: Pool;
181
  
182
  constructor(config: PoolConfig) {
183
    this.pool = mariadb.createPool(config);
184
    
185
    // Handle pool errors
186
    this.pool.on('error', (err: SqlError) => {
187
      console.error('Pool error:', err);
188
      
189
      if (this.isPoolFatalError(err)) {
190
        this.recreatePool();
191
      }
192
    });
193
  }
194
  
195
  private isPoolFatalError(error: SqlError): boolean {
196
    return error.errno === 45011; // ER_POOL_ALREADY_CLOSED
197
  }
198
  
199
  private async recreatePool(): Promise<void> {
200
    try {
201
      await this.pool.end();
202
    } catch (error) {
203
      console.warn('Error closing old pool:', error);
204
    }
205
    
206
    this.pool = mariadb.createPool(this.pool.config);
207
  }
208
  
209
  async getConnection(): Promise<PoolConnection> {
210
    try {
211
      return await this.pool.getConnection();
212
    } catch (error) {
213
      if (error instanceof SqlError && error.errno === 45012) {
214
        // Connection acquisition timeout
215
        throw new Error('Database is overloaded - try again later');
216
      }
217
      throw error;
218
    }
219
  }
220
}
221
```
222

223
### Transaction Error Handling
224

225
```typescript
226
async function safeTransaction<T>(
227
  connection: Connection,
228
  operation: (conn: Connection) => Promise<T>
229
): Promise<T> {
230
  const savepoint = `sp_${Date.now()}_${Math.random().toString(36).substring(2)}`;
231
  
232
  try {
233
    await connection.beginTransaction();
234
    
235
    // Create savepoint for nested error handling
236
    await connection.query(`SAVEPOINT ${savepoint}`);
237
    
238
    const result = await operation(connection);
239
    
240
    await connection.commit();
241
    return result;
242
    
243
  } catch (error) {
244
    try {
245
      if (error instanceof SqlError) {
246
        if (error.errno === 1213) { // Deadlock
247
          console.warn('Deadlock detected, rolling back transaction');
248
          await connection.rollback();
249
          throw new Error('Transaction failed due to deadlock - please retry');
250
        } else if (error.errno === 1205) { // Lock timeout
251
          console.warn('Lock timeout, rolling back to savepoint');
252
          await connection.query(`ROLLBACK TO SAVEPOINT ${savepoint}`);
253
          throw new Error('Transaction timed out waiting for lock');
254
        } else {
255
          // General rollback
256
          await connection.rollback();
257
        }
258
      } else {
259
        await connection.rollback();
260
      }
261
    } catch (rollbackError) {
262
      console.error('Error during rollback:', rollbackError);
263
      // Connection may be in bad state
264
      if (connection.isValid()) {
265
        try {
266
          await connection.reset();
267
        } catch (resetError) {
268
          console.error('Error resetting connection:', resetError);
269
        }
270
      }
271
    }
272
    
273
    throw error;
274
  }
275
}
276
```
277

278
### Logging and Monitoring Integration
279

280
```typescript
281
class DatabaseLogger {
282
  private logger: any; // Your logging library
283
  
284
  constructor(logger: any) {
285
    this.logger = logger;
286
  }
287
  
288
  logError(error: SqlError, context?: any): void {
289
    const errorData = {
290
      type: 'sql_error',
291
      code: error.code,
292
      errno: error.errno,
293
      sqlState: error.sqlState,
294
      message: error.sqlMessage,
295
      sql: error.sql,
296
      fatal: error.fatal,
297
      context: context
298
    };
299
    
300
    if (error.fatal || this.isCriticalError(error)) {
301
      this.logger.error('Critical database error', errorData);
302
      // Send alert to monitoring system
303
      this.sendAlert('critical_db_error', errorData);
304
    } else {
305
      this.logger.warn('Database error', errorData);
306
    }
307
  }
308
  
309
  private isCriticalError(error: SqlError): boolean {
310
    const criticalErrorNumbers = [
311
      1040, // Too many connections
312
      1203, // User has more than max_user_connections
313
      1226, // User has exceeded max_updates_per_hour
314
      2006, // MySQL server has gone away
315
      2013  // Lost connection during query
316
    ];
317
    
318
    return criticalErrorNumbers.includes(error.errno);
319
  }
320
  
321
  private sendAlert(type: string, data: any): void {
322
    // Integration with monitoring system
323
    // e.g., Datadog, New Relic, custom alerting
324
  }
325
}
326
```
327

328
### Custom Error Classes
329

330
```typescript
331
// Application-specific error classes
332
class DatabaseError extends Error {
333
  constructor(
334
    message: string,
335
    public readonly originalError: SqlError,
336
    public readonly operation: string
337
  ) {
338
    super(message);
339
    this.name = 'DatabaseError';
340
  }
341
  
342
  get isRetryable(): boolean {
343
    return [1205, 1213, 2006, 2013].includes(this.originalError.errno);
344
  }
345
  
346
  get isConnectionIssue(): boolean {
347
    return this.originalError.fatal || 
348
           this.originalError.errno >= 45001 && this.originalError.errno <= 45010;
349
  }
350
}
351

352
class ValidationError extends DatabaseError {
353
  constructor(originalError: SqlError, field?: string) {
354
    const message = field 
355
      ? `Validation failed for field '${field}': ${originalError.sqlMessage}`
356
      : `Validation failed: ${originalError.sqlMessage}`;
357
    
358
    super(message, originalError, 'validation');
359
    this.name = 'ValidationError';
360
  }
361
}
362

363
class DuplicateEntryError extends DatabaseError {
364
  constructor(originalError: SqlError, duplicateKey?: string) {
365
    const message = duplicateKey
366
      ? `Duplicate entry for key '${duplicateKey}'`
367
      : 'Duplicate entry detected';
368
    
369
    super(message, originalError, 'insert');
370
    this.name = 'DuplicateEntryError';
371
  }
372
}
373

374
// Error factory
375
function createApplicationError(sqlError: SqlError, operation: string): DatabaseError {
376
  switch (sqlError.errno) {
377
    case 1062: // Duplicate entry
378
      return new DuplicateEntryError(sqlError);
379
    case 1452: // Foreign key constraint fails
380
    case 1048: // Column cannot be null
381
    case 1406: // Data too long for column
382
      return new ValidationError(sqlError);
383
    default:
384
      return new DatabaseError(sqlError.sqlMessage || sqlError.message, sqlError, operation);
385
  }
386
}
387
```
388

389
### Error Recovery Strategies
390

391
```typescript
392
class ResilientDatabaseService {
393
  private pool: Pool;
394
  private circuitBreaker: CircuitBreaker;
395
  
396
  constructor(config: PoolConfig) {
397
    this.pool = mariadb.createPool(config);
398
    this.circuitBreaker = new CircuitBreaker({
399
      failureThreshold: 5,
400
      resetTimeout: 30000
401
    });
402
  }
403
  
404
  async query<T>(sql: string, values?: any): Promise<T> {
405
    return this.circuitBreaker.execute(async () => {
406
      try {
407
        return await this.pool.query(sql, values);
408
      } catch (error) {
409
        if (error instanceof SqlError) {
410
          // Transform to application error
411
          throw createApplicationError(error, 'query');
412
        }
413
        throw error;
414
      }
415
    });
416
  }
417
  
418
  async healthCheck(): Promise<boolean> {
419
    try {
420
      await this.pool.query('SELECT 1');
421
      return true;
422
    } catch (error) {
423
      console.error('Database health check failed:', error);
424
      return false;
425
    }
426
  }
427
}
428

429
// Simple circuit breaker implementation
430
class CircuitBreaker {
431
  private failures = 0;
432
  private lastFailureTime = 0;
433
  private state: 'closed' | 'open' | 'half-open' = 'closed';
434
  
435
  constructor(
436
    private options: {
437
      failureThreshold: number;
438
      resetTimeout: number;
439
    }
440
  ) {}
441
  
442
  async execute<T>(operation: () => Promise<T>): Promise<T> {
443
    if (this.state === 'open') {
444
      if (Date.now() - this.lastFailureTime > this.options.resetTimeout) {
445
        this.state = 'half-open';
446
      } else {
447
        throw new Error('Circuit breaker is OPEN');
448
      }
449
    }
450
    
451
    try {
452
      const result = await operation();
453
      this.onSuccess();
454
      return result;
455
    } catch (error) {
456
      this.onFailure();
457
      throw error;
458
    }
459
  }
460
  
461
  private onSuccess(): void {
462
    this.failures = 0;
463
    this.state = 'closed';
464
  }
465
  
466
  private onFailure(): void {
467
    this.failures++;
468
    this.lastFailureTime = Date.now();
469
    
470
    if (this.failures >= this.options.failureThreshold) {
471
      this.state = 'open';
472
    }
473
  }
474
}
475
```
476

477
### Common Error Codes Reference
478

479
```typescript
480
// MySQL/MariaDB Standard Error Codes
481
const MYSQL_ERRORS = {
482
  // Constraint violations
483
  DUPLICATE_ENTRY: 1062,
484
  FOREIGN_KEY_CONSTRAINT: 1452,
485
  COLUMN_CANNOT_BE_NULL: 1048,
486
  DATA_TOO_LONG: 1406,
487
  
488
  // Connection issues
489
  TOO_MANY_CONNECTIONS: 1040,
490
  ACCESS_DENIED: 1045,
491
  UNKNOWN_DATABASE: 1049,
492
  TABLE_DOESNT_EXIST: 1146,
493
  COLUMN_DOESNT_EXIST: 1054,
494
  
495
  // Lock and transaction issues
496
  LOCK_WAIT_TIMEOUT: 1205,
497
  DEADLOCK_FOUND: 1213,
498
  
499
  // Server issues  
500
  SERVER_GONE_AWAY: 2006,
501
  LOST_CONNECTION: 2013,
502
  SERVER_SHUTDOWN: 1053
503
} as const;
504

505
// MariaDB Connector Specific Error Codes (45001-45062)
506
const CONNECTOR_ERRORS = {
507
  // Connection Management (45001-45010)
508
  CONNECTION_ALREADY_CLOSED: 45001,
509
  MYSQL_CHANGE_USER_BUG: 45003,
510
  CMD_NOT_EXECUTED_DESTROYED: 45004,
511
  ADD_CONNECTION_CANCELLED: 45005,
512
  CONN_PVT_KEY_ENCRYPTED: 45006,
513
  SOCKET_UNEXPECTED_CLOSE: 45007,
514
  SOCKET_BAD_PORT: 45008,
515
  SOCKET_TIMEOUT: 45009,
516
  PING_TIMEOUT: 45010,
517
  
518
  // Pool Management (45011-45013)
519
  POOL_ALREADY_CLOSED: 45011,
520
  GET_CONNECTION_TIMEOUT: 45012,
521
  POOL_CONNECTION_CLOSED: 45013,
522
  
523
  // File Operations (45014-45015)
524
  FILE_NOT_EXISTS: 45014,
525
  FILE_EMPTY: 45015,
526
  
527
  // Authentication (45016-45018)
528
  AUTHENTICATION_PLUGIN_NOT_SUPPORTED: 45016,
529
  AUTHENTICATION_BAD_PACKET: 45017,
530
  AUTHENTICATION_PLUGIN_ERR: 45018,
531
  
532
  // Protocol Errors (45019-45062)
533
  UNEXPECTED_PACKET: 45019,
534
  BAD_PARAMETER_VALUE: 45020,
535
  BUFFER_PARAMETER_CONVERSION: 45021,
536
  LOCAL_INFILE_DISABLED: 45022,
537
  LOCAL_INFILE_NOT_READABLE: 45023,
538
  PARAMETER_UNDEFINED: 45024,
539
  PARAMETER_NOT_DEFINED_STMT: 45025,
540
  PARAMETER_NOT_DEFINED_BULK: 45026,
541
  NOT_IMPLEMENTED_FORMAT: 45027,
542
  WRONG_PARAMETER_FORMAT: 45028,
543
  NOT_SUPPORTED_AUTH: 45029,
544
  SOCKET_DESTROYED: 45030,
545
  SOCKET_NOT_WRITABLE: 45031,
546
  AUTHENTICATION_SWITCH_PLUGIN: 45032,
547
  AUTHENTICATION_WRONG_METHOD: 45033,
548
  AUTHENTICATION_WRONG_RESPONSE: 45034,
549
  AUTHENTICATION_EMPTY_RESPONSE: 45035,
550
  AUTHENTICATION_FAIL_RESPONSE: 45036,
551
  CHANGE_USER_RESET_PREPARE: 45037,
552
  AUTHENTICATION_TRY_OTHER: 45038,
553
  SOCKET_NO_CONNECTION: 45039,
554
  HANDSHAKE_UNEXPECTED_PACKET: 45040,
555
  HANDSHAKE_FAIL_SSL: 45041,
556
  CONN_PVT_KEY_INCORRECT_PASSPHRASE: 45042,
557
  STREAM_PARAMETER_NOT_READABLE: 45043,
558
  STREAM_PARAMETER_NOT_ITERABLE: 45044,
559
  COMMAND_NOT_EXECUTABLE: 45045,
560
  COMMAND_CLOSED: 45046,
561
  EMPTY_PASSWORD: 45047,
562
  AUTHENTICATION_KERBEROS_PRINCIPAL: 45048,
563
  AUTHENTICATION_KERBEROS_SERVICE: 45049,
564
  AUTHENTICATION_KERBEROS_TARGET: 45050,
565
  AUTHENTICATION_KERBEROS_TIMEOUT: 45051,
566
  QUERY_INTERRUPTED: 45052,
567
  QUERY_TIMEOUT: 45053,
568
  MAX_ALLOWED_PACKET: 45054,
569
  BATCH_WITH_NO_VALUES: 45055,
570
  BATCH_BULK_NO_BATCH: 45056,
571
  BATCH_WRONG_PARAMETER_NUMBER: 45057,
572
  BATCH_VALIDATION_ERROR: 45058,
573
  BATCH_WITHOUT_PARAMETERS: 45059,
574
  CLUSTER_NO_NODES: 45060,
575
  CLUSTER_NODE_NOT_FOUND: 45061,
576
  CLUSTER_ALL_NODES_FAILED: 45062
577
} as const;
578
```