0
# Connection Management
1

2
Connection management classes handle MySQL-specific connection state, variable management, and AWS RDS detection. They ensure proper connection restoration and provide specialized functionality for MySQL environments.
3

4
## Capabilities
5

6
### MySQL Connection
7

8
MySQL-specific connection implementation with state management, variable restoration, and environment detection.
9

10
```java { .api }
11
/**
12
 * MySQL-specific connection implementation
13
 * Handles connection state, user variables, and AWS RDS detection
14
 */
15
public class MySQLConnection extends Connection<MySQLDatabase> {
16
    
17
    /**
18
     * Creates a new MySQL connection instance
19
     * Initializes connection state and detects environment capabilities
20
     * @param database MySQL database instance
21
     * @param connection Raw JDBC connection
22
     */
23
    public MySQLConnection(MySQLDatabase database, java.sql.Connection connection);
24
    
25
    /**
26
     * Detects if connection is to AWS RDS
27
     * @return true if connected to AWS RDS MySQL instance
28
     */
29
    public boolean isAwsRds();
30
    
31
    /**
32
     * Restores original connection state on connection close
33
     * Resets user variables and restores system variables
34
     * @throws SQLException if restoration fails
35
     */
36
    @Override
37
    protected void doRestoreOriginalState() throws SQLException;
38
    
39
    /**
40
     * Gets current schema name (database name in MySQL)
41
     * @return Current database name or null if none selected
42
     * @throws SQLException if query fails
43
     */
44
    @Override
45
    protected String getCurrentSchemaNameOrSearchPath() throws SQLException;
46
    
47
    /**
48
     * Changes current schema (database) to specified name
49
     * @param schema Schema name to switch to, or null to clear
50
     * @throws SQLException if schema change fails
51
     */
52
    @Override
53
    public void doChangeCurrentSchemaOrSearchPathTo(String schema) throws SQLException;
54
    
55
    /**
56
     * Gets current schema object
57
     * @return MySQLSchema instance for current database, or null if none
58
     * @throws SQLException if query fails
59
     */
60
    @Override
61
    protected Schema doGetCurrentSchema() throws SQLException;
62
    
63
    /**
64
     * Gets schema object for specified name
65
     * @param name Schema (database) name
66
     * @return MySQLSchema instance
67
     */
68
    @Override
69
    public Schema getSchema(String name);
70
    
71
    /**
72
     * Executes callable with table lock protection
73
     * Uses named locks when available, otherwise falls back to superclass
74
     * @param <T> Return type
75
     * @param table Table to lock
76
     * @param callable Operation to execute with lock
77
     * @return Result from callable
78
     */
79
    @Override
80
    public <T> T lock(Table table, Callable<T> callable);
81
    
82
    /**
83
     * Determines if named lock template can be used
84
     * Based on database version and user permissions
85
     * @return true if named locks are available
86
     */
87
    protected boolean canUseNamedLockTemplate();
88
}
89
```
90

91
**Constants and Fields:**
92

93
```java { .api }
94
// System table names for user variable queries
95
private static final String USER_VARIABLES_TABLE_MARIADB = "information_schema.user_variables";
96
private static final String USER_VARIABLES_TABLE_MYSQL = "performance_schema.user_variables_by_thread";
97
private static final String FOREIGN_KEY_CHECKS = "foreign_key_checks";
98
private static final String SQL_SAFE_UPDATES = "sql_safe_updates";
99

100
// Connection state fields
101
private final String userVariablesQuery;
102
private final boolean canResetUserVariables;
103
private final int originalForeignKeyChecks;
104
private final int originalSqlSafeUpdates;
105
private final boolean awsRds; // Accessible via isAwsRds() getter
106
```
107

108
**Usage Examples:**
109

110
```java
111
// Connection is typically created by MySQLDatabase
112
MySQLDatabase database = new MySQLDatabase(config, jdbcFactory, interceptor);
113
MySQLConnection connection = database.getConnection();
114

115
// Check AWS RDS status
116
boolean isRds = connection.isAwsRds();
117
if (isRds) {
118
    // Handle RDS-specific behavior
119
    System.out.println("Connected to AWS RDS MySQL");
120
}
121

122
// Check named lock capability
123
boolean canUseLocks = connection.canUseNamedLockTemplate();
124
if (canUseLocks) {
125
    // Named locks are available for concurrent operations
126
}
127
```
128

129
## Connection State Management
130

131
### Variable Restoration
132

133
The connection automatically manages and restores MySQL system variables:
134

135
**Foreign Key Checks:**
136
- Stores original `foreign_key_checks` value on connection
137
- Restores original value when connection is closed
138

139
**SQL Safe Updates:**
140
- Stores original `sql_safe_updates` value on connection  
141
- Restores original value when connection is closed
142

143
### User Variable Management
144

145
For supported MySQL/MariaDB versions and permissions:
146

147
**MySQL (5.7+):**
148
- Queries `performance_schema.user_variables_by_thread`
149
- Resets user variables to clean state
150

151
**MariaDB (10.2+):**
152
- Queries `information_schema.user_variables`
153
- Resets user variables to clean state
154

155
**Requirements:**
156
- Sufficient database version
157
- SELECT permissions on system tables
158
- User variable reset capability detection
159

160
```java
161
// User variable reset capability is automatically detected
162
// and used when available during connection restoration
163
```
164

165
## Environment Detection
166

167
### AWS RDS Detection
168

169
The connection detects AWS RDS environments using multiple indicators:
170

171
**Detection Methods:**
172
1. RDS admin user existence check
173
2. Connection metadata analysis
174
3. URL pattern matching (handled at database level)
175

176
```java
177
// AWS RDS detection example
178
if (connection.isAwsRds()) {
179
    // AWS RDS specific handling:
180
    // - Different privilege model
181
    // - Specific feature limitations
182
    // - Different monitoring approaches
183
}
184
```
185

186
### Version and Capability Detection
187

188
The connection performs capability detection during initialization:
189

190
**User Variable Reset:**
191
```java
192
// Capability detection logic
193
if (database.isMariaDB() && !database.getVersion().isAtLeast("10.2")) {
194
    // User variable reset disabled for older MariaDB
195
}
196
if (!database.isMariaDB() && !database.getVersion().isAtLeast("5.7")) {
197
    // User variable reset disabled for older MySQL
198
}
199
```
200

201
**Event Scheduler Access:**
202
```java
203
// Event scheduler queryability detection
204
boolean canQueryEvents = database.eventSchedulerQueryable;
205
if (canQueryEvents) {
206
    // Can query information_schema.events
207
} else {
208
    // Event scheduler is OFF or DISABLED (MariaDB)
209
}
210
```
211

212
## Named Lock Support
213

214
Named locks provide coordination for concurrent Flyway operations:
215

216
### Lock Template Usage
217

218
```java
219
/**
220
 * Creates named lock template when supported
221
 * @param jdbcTemplate JDBC template for operations
222
 * @param discriminator Unique identifier for lock name
223
 * @return MySQLNamedLockTemplate instance
224
 */
225
MySQLNamedLockTemplate lockTemplate = new MySQLNamedLockTemplate(jdbcTemplate, discriminator);
226

227
// Execute with lock protection
228
Object result = lockTemplate.execute(() -> {
229
    // Critical section protected by named lock
230
    return performCriticalOperation();
231
});
232
```
233

234
### Lock Requirements
235

236
Named locks are available when:
237
- Database supports `GET_LOCK()` and `RELEASE_LOCK()` functions
238
- Connection has necessary permissions
239
- Not running in environments that restrict locking
240

241
## Connection Properties and Configuration
242

243
### Default Properties
244

245
Connections are configured with MySQL-specific properties:
246

247
```java
248
// Set during connection establishment
249
props.put("connectionAttributes", "program_name:" + APPLICATION_NAME);
250
```
251

252
This helps identify Flyway connections in:
253
- MySQL process list (`SHOW PROCESSLIST`)
254
- Performance schema tables
255
- Database monitoring systems
256

257
### Connection Restoration
258

259
The connection implements automatic state restoration:
260

261
```java
262
// Restoration happens automatically on connection close
263
@Override
264
protected void doRestoreOriginalState() throws SQLException {
265
    // Reset user variables (if supported)
266
    resetUserVariables();
267
    
268
    // Restore system variables
269
    jdbcTemplate.execute(
270
        "SET foreign_key_checks=?, sql_safe_updates=?",
271
        originalForeignKeyChecks,
272
        originalSqlSafeUpdates
273
    );
274
}
275
```
276

277
## Error Handling and Diagnostics
278

279
### Connection Diagnostics
280

281
The connection provides diagnostic information for troubleshooting:
282

283
**Variable Access Issues:**
284
```java
285
// When user variable reset fails
286
LOG.debug("Disabled user variable reset as " + tableNamel + " cannot be queried " +
287
         "(SQL State: " + e.getSQLState() + ", Error Code: " + e.getErrorCode() + ")");
288
```
289

290
**Version Compatibility:**
291
```java
292
// When features are disabled due to version
293
LOG.debug("Disabled user variable reset as it is only available from MySQL 5.7 onwards");
294
LOG.debug("Disabled user variable reset as it is only available from MariaDB 10.2 onwards");
295
```
296

297
### Common Connection Issues
298

299
**Permission Problems:**
300
- Cannot query `performance_schema.user_variables_by_thread` (MySQL)
301
- Cannot query `information_schema.user_variables` (MariaDB)
302
- Missing `GET_LOCK()`/`RELEASE_LOCK()` permissions
303

304
**Version Issues:**
305
- MySQL versions below 5.7 have limited user variable support
306
- MariaDB versions below 10.2 have limited user variable support
307
- Different system table availability across versions
308

309
**Environment Issues:**
310
- AWS RDS has different permission models
311
- Some cloud environments restrict certain operations
312
- Proxy connections may affect capability detection
313

314
## Integration with Database Operations
315

316
The connection integrates with other database components:
317

318
**Schema Operations:**
319
```java
320
MySQLSchema schema = connection.getSchema("myschema");
321
// Connection provides MySQL-specific schema behavior
322
```
323

324
**Table Operations:**  
325
```java
326
MySQLTable table = schema.getTable("mytable");
327
// Connection enables MySQL-specific table operations
328
```
329

330
**Transaction Management:**
331
```java
332
// MySQL connections handle DDL auto-commit behavior
333
// No transaction support for DDL operations
334
boolean supportsDdl = connection.getDatabase().supportsDdlTransactions(); // false
335
```