0
# Utility Components
1

2
Utility components provide additional functionality for MySQL environments including named lock coordination for concurrent operations and MySQL option file authentication support.
3

4
## Capabilities
5

6
### MySQL Named Lock Template
7

8
Provides MySQL named lock functionality for coordinating concurrent operations across multiple Flyway instances or database connections.
9

10
```java { .api }
11
/**
12
 * MySQL named lock template for coordinating concurrent operations
13
 * Uses MySQL's GET_LOCK() and RELEASE_LOCK() functions
14
 */
15
public class MySQLNamedLockTemplate {
16
    
17
    /**
18
     * Creates a named lock template instance
19
     * Package-private constructor used internally by connection classes
20
     * @param jdbcTemplate JDBC template for database operations
21
     * @param discriminator Unique identifier for generating lock name
22
     */
23
    MySQLNamedLockTemplate(JdbcTemplate jdbcTemplate, int discriminator);
24
    
25
    /**
26
     * Executes a callable within a MySQL named lock
27
     * Acquires lock before execution, releases after completion
28
     * @param <T> Return type of the callable
29
     * @param callable Operation to execute with lock protection
30
     * @return Result from callable execution
31
     * @throws Exception if callable throws exception or lock acquisition fails
32
     */
33
    public <T> T execute(Callable<T> callable) throws Exception;
34
    
35
    /**
36
     * Acquires the MySQL named lock with retry logic
37
     * @throws SQLException if lock acquisition fails permanently
38
     */
39
    private void lock() throws SQLException;
40
    
41
    /**
42
     * Attempts to acquire the lock once with timeout
43
     * @return true if lock acquired, false if timeout occurred
44
     * @throws SQLException if lock operation fails
45
     */
46
    private boolean tryLock() throws SQLException;
47
}
48
```
49

50
**Fields:**
51

52
```java { .api }
53
// Lock management fields
54
private final JdbcTemplate jdbcTemplate;
55
private final String lockName;
56
}
57
```
58

59
**Usage Examples:**
60

61
```java
62
// Named lock template creation (typically internal)
63
MySQLNamedLockTemplate lockTemplate = new MySQLNamedLockTemplate(jdbcTemplate, 12345);
64

65
// Execute critical section with lock protection
66
String result = lockTemplate.execute(() -> {
67
    // Critical section - only one thread/connection can execute this
68
    performCriticalDatabaseOperation();
69
    return "Operation completed";
70
});
71

72
// Exception handling example
73
try {
74
    lockTemplate.execute(() -> {
75
        // Operation that might fail
76
        updateSchemaHistory();
77
        return null;
78
    });
79
} catch (Exception e) {
80
    // Handle operation failure or lock acquisition failure
81
    LOG.error("Failed to execute with lock: " + e.getMessage());
82
}
83
```
84

85
### MySQL Option File Reader
86

87
Provides MySQL option file authentication support, reading MySQL configuration files for connection credentials and settings.
88

89
```java { .api }
90
/**
91
 * MySQL option file reader for external authentication
92
 * Implements ExternalAuthFileReader interface for Flyway authentication system
93
 */
94
public class MySQLOptionFileReader implements ExternalAuthFileReader {
95
    
96
    /**
97
     * Creates a new MySQL option file reader instance
98
     */
99
    public MySQLOptionFileReader();
100
    
101
    /**
102
     * List of discovered MySQL option files
103
     * Populated by populateOptionFiles() method
104
     */
105
    public final List<String> optionFiles;
106
    
107
    /**
108
     * List of discovered encrypted MySQL option files (.mylogin.cnf)
109
     * Populated by populateOptionFiles() method
110
     */
111
    private final List<String> encryptedOptionFiles;
112
    
113
    /**
114
     * Returns contents of all discovered option files
115
     * Current implementation returns empty list
116
     * @return Empty list (implementation placeholder)
117
     */
118
    public List<String> getAllContents();
119
    
120
    /**
121
     * Populates the optionFiles list based on operating system
122
     * Discovers MySQL option files in standard locations
123
     */
124
    public void populateOptionFiles();
125
    
126
    /**
127
     * Adds option file to list if it exists on filesystem
128
     * @param optionFilePath Path to option file to check
129
     * @param encrypted Whether the file is encrypted (.mylogin.cnf)
130
     */
131
    private void addIfOptionFileExists(String optionFilePath, boolean encrypted);
132
}
133
```
134

135
**Usage Examples:**
136

137
```java
138
// Option file reader creation
139
MySQLOptionFileReader reader = new MySQLOptionFileReader();
140

141
// Discover option files
142
reader.populateOptionFiles();
143

144
// Check discovered files
145
List<String> optionFiles = reader.optionFiles;
146
for (String file : optionFiles) {
147
    System.out.println("Found MySQL option file: " + file);
148
}
149

150
// Get file contents (currently returns empty)
151
List<String> contents = reader.getAllContents();
152
```
153

154
## Named Lock Functionality
155

156
### Lock Mechanism
157

158
MySQL named locks provide application-level coordination:
159

160
**Lock Acquisition:**
161
```sql
162
SELECT GET_LOCK('flyway_lock_12345', timeout_seconds)
163
```
164

165
**Lock Release:**
166
```sql
167
SELECT RELEASE_LOCK('flyway_lock_12345')
168
```
169

170
**Lock Name Generation:**
171
- Base name: `flyway_lock_`
172
- Discriminator: Unique identifier (typically connection hash)
173
- Full name: `flyway_lock_12345`
174

175
### Lock Behavior
176

177
**Acquisition:**
178
- Returns 1 if lock acquired successfully
179
- Returns 0 if timeout occurred
180
- Returns NULL if error occurred
181

182
**Timeout Handling:**
183
- Configurable timeout (typically 10 seconds)
184
- Automatic retry logic for transient failures
185
- Error handling for permanent failures
186

187
**Release:**
188
- Returns 1 if lock released successfully
189
- Returns 0 if lock was not held by connection
190
- Returns NULL if lock did not exist
191

192
### Concurrency Control
193

194
Named locks enable coordination for:
195

196
**Schema History Updates:**
197
```java
198
lockTemplate.execute(() -> {
199
    // Update flyway_schema_history table
200
    // Ensures only one Flyway instance updates at a time
201
    updateSchemaHistoryTable();
202
    return null;
203
});
204
```
205

206
**Critical Database Operations:**
207
```java
208
lockTemplate.execute(() -> {
209
    // Operations requiring exclusive access
210
    performSchemaCleanup();
211
    recreateIndexes();
212
    return "Cleanup completed";
213
});
214
```
215

216
**Multi-Instance Coordination:**
217
- Prevents concurrent schema migrations
218
- Coordinates database initialization
219
- Manages shared resource access
220

221
### Lock Limitations
222

223
**Session Lifetime:**
224
- Locks are released when connection closes
225
- Not persistent across connection failures
226
- Require active database connection
227

228
**Database Scope:**
229
- Locks are database-scoped, not global
230
- Different databases can have same lock names
231
- No cross-database coordination
232

233
**Error Conditions:**
234
- Network failures release locks
235
- Connection timeouts affect lock state
236
- Database restarts clear all locks
237

238
## MySQL Option File Support
239

240
### Standard Option Files
241

242
MySQL looks for option files in standard locations:
243

244
**Linux/Unix:**
245
- `/etc/mysql/my.cnf`
246
- `/etc/my.cnf`
247
- `~/.my.cnf`
248
- `~/.mysql/my.cnf`
249

250
**Windows:**
251
- `%PROGRAMDATA%\MySQL\MySQL Server X.Y\my.ini`
252
- `%WINDIR%\my.ini`
253
- `C:\my.cnf`
254
- `%APPDATA%\MySQL\.mylogin.cnf`
255

256
**macOS:**
257
- `/usr/local/mysql/etc/my.cnf`
258
- `/opt/local/etc/mysql/my.cnf`
259
- `~/.my.cnf`
260

261
### Option File Format
262

263
Standard MySQL option file format:
264

265
```ini
266
[client]
267
user=myuser
268
password=mypassword
269
host=localhost
270
port=3306
271
database=mydb
272

273
[mysql]
274
default-character-set=utf8mb4
275

276
[flyway]
277
# Flyway-specific options
278
user=flyway_user
279
password=flyway_password
280
```
281

282
### Authentication Integration
283

284
Option file integration with Flyway authentication:
285

286
```java
287
// Automatic integration in MySQLDatabaseType
288
@Override
289
public Properties getExternalAuthProperties(String url, String username) {
290
    MySQLOptionFileReader reader = new MySQLOptionFileReader();
291
    reader.populateOptionFiles();
292
    
293
    if (!reader.optionFiles.isEmpty()) {
294
        // Log availability of option files
295
        LOG.info("MySQL option file detected for authentication");
296
    }
297
    
298
    return super.getExternalAuthProperties(url, username);
299
}
300
```
301

302
### Encrypted Option Files
303

304
Support for MySQL encrypted login files:
305

306
**Login Path Utility:**
307
```bash
308
# Create encrypted credentials
309
mysql_config_editor set --login-path=flyway --host=localhost --user=flyway_user --password
310

311
# Use in connection string
312
jdbc:mysql://localhost:3306/mydb?useLoginPath=flyway
313
```
314

315
**Benefits:**
316
- Encrypted credential storage
317
- No plaintext passwords in configuration
318
- Operating system user-specific access
319
- Integration with MySQL utilities
320

321
## Error Handling and Diagnostics
322

323
### Named Lock Errors
324

325
**Lock Acquisition Failures:**
326
```java
327
// Timeout scenarios
328
if (lockResult == 0) {
329
    throw new FlywayException("Failed to acquire named lock within timeout");
330
}
331

332
// Error scenarios  
333
if (lockResult == null) {
334
    throw new FlywayException("Error occurred while acquiring named lock");
335
}
336
```
337

338
**Lock Release Issues:**
339
```java
340
// Lock not owned
341
if (releaseResult == 0) {
342
    LOG.warn("Attempted to release lock not owned by this connection");
343
}
344

345
// Lock not found
346
if (releaseResult == null) {
347
    LOG.warn("Attempted to release non-existent lock");
348
}
349
```
350

351
### Option File Errors
352

353
**File Access Issues:**
354
- Permission denied for option files
355
- Option files not found in standard locations
356
- Malformed option file syntax
357
- Encrypted file decryption failures
358

359
**Configuration Problems:**
360
- Invalid MySQL option syntax
361
- Conflicting option values
362
- Missing required options
363
- Character encoding issues
364

365
### Diagnostic Information
366

367
**Lock Status Queries:**
368
```sql
369
-- Check active locks
370
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES 
371
WHERE PRIVILEGE_TYPE = 'LOCK';
372

373
-- Check lock status (MySQL 8.0+)
374
SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE 
375
FROM performance_schema.metadata_locks 
376
WHERE OBJECT_TYPE = 'USER LEVEL LOCK';
377
```
378

379
**Option File Discovery:**
380
```java
381
// Log discovered option files
382
for (String optionFile : reader.optionFiles) {
383
    LOG.debug("Discovered MySQL option file: " + optionFile);
384
}
385

386
// Log encryption status
387
for (String encryptedFile : reader.encryptedOptionFiles) {
388
    LOG.debug("Discovered encrypted option file: " + encryptedFile);
389
}
390
```
391

392
## Integration with Core Components
393

394
### Connection Integration
395

396
Named locks integrate with connection management:
397

398
```java
399
// Connection provides lock template access
400
MySQLConnection connection = database.getConnection();
401
if (connection.canUseNamedLockTemplate()) {
402
    MySQLNamedLockTemplate lockTemplate = connection.getNamedLockTemplate();
403
    // Use lock template for coordination
404
}
405
```
406

407
### Database Type Integration
408

409
Authentication integration in database types:
410

411
```java
412
// MySQLDatabaseType automatically checks for option files
413
@Override
414
public Properties getExternalAuthProperties(String url, String username) {
415
    // Option file detection and integration
416
    return enhancedProperties;
417
}
418
```
419

420
### Configuration Integration
421

422
Utility components enhance Flyway configuration:
423

424
- **Authentication**: Option file credential discovery
425
- **Concurrency**: Named lock coordination setup
426
- **Environment**: Cloud and cluster environment detection
427
- **Monitoring**: Enhanced logging and diagnostics
428

429
## Performance Considerations
430

431
### Named Lock Performance
432

433
**Lock Overhead:**
434
- Minimal CPU overhead for lock operations
435
- Network round-trip for each lock operation
436
- Database server lock table management
437

438
**Optimization Strategies:**
439
- Reuse lock templates where possible
440
- Minimize lock hold time
441
- Batch operations within lock scope
442
- Monitor lock contention
443

444
### Option File Performance
445

446
**File System Access:**
447
- Option file discovery involves file system checks
448
- Caching of discovered file locations
449
- Lazy loading of file contents
450
- Efficient parsing of configuration data
451

452
**Memory Usage:**
453
- Minimal memory footprint for configuration data
454
- Temporary storage during option file processing
455
- Cleanup of sensitive credential information