0
# Database Operations
1

2
Database operation classes provide MySQL and MariaDB-specific implementations for database connections, version detection, and specialized behavior for different MySQL environments including Percona XtraDB Cluster, WSREP clusters, and GTID consistency modes.
3

4
## Capabilities
5

6
### MySQL Database
7

8
Core MySQL database implementation with environment detection and specialized behavior for various MySQL deployments.
9

10
```java { .api }
11
/**
12
 * MySQL-specific database implementation
13
 * Handles MySQL-specific behavior, version detection, and cluster environments
14
 */
15
public class MySQLDatabase extends Database<MySQLConnection> {
16
    
17
    /**
18
     * Creates a new MySQL database instance
19
     * @param configuration Flyway configuration
20
     * @param jdbcConnectionFactory JDBC connection factory
21
     * @param statementInterceptor Statement interceptor for monitoring
22
     */
23
    public MySQLDatabase(Configuration configuration, JdbcConnectionFactory jdbcConnectionFactory, StatementInterceptor statementInterceptor);
24
    
25
    /**
26
     * Returns WSREP status for cluster environments
27
     * @return true if WSREP_ON=ON (for MariaDB clusters)
28
     */
29
    public boolean isWsrepOn();
30
    
31
    /**
32
     * Extracts MySQL version from SELECT VERSION() output
33
     * @param selectVersionOutput Raw version string from database
34
     * @return Parsed MigrationVersion
35
     * @throws FlywayException if version cannot be determined
36
     */
37
    public static MigrationVersion extractMySQLVersionFromString(String selectVersionOutput);
38
    
39
    /**
40
     * Extracts MariaDB version from SELECT VERSION() output
41
     * Handles both standard MariaDB and MaxScale proxy scenarios
42
     * @param selectVersionOutput Raw version string from database
43
     * @return Parsed MigrationVersion
44
     * @throws FlywayException if version cannot be determined
45
     */
46
    public static MigrationVersion extractMariaDBVersionFromString(String selectVersionOutput);
47
    
48
    /**
49
     * Detects if running in Percona XtraDB Cluster with strict mode
50
     * @param jdbcTemplate JDBC template for queries
51
     * @return true if PXC strict mode is ENFORCING or MASTER
52
     */
53
    public static boolean isRunningInPerconaXtraDBClusterWithStrictMode(JdbcTemplate jdbcTemplate);
54
    
55
    /**
56
     * Detects if WSREP is enabled (for MariaDB Galera clusters)
57
     * @param jdbcTemplate JDBC template for queries
58
     * @return true if @@GLOBAL.WSREP_ON is ON
59
     */
60
    static boolean isWsrepOn(JdbcTemplate jdbcTemplate);
61
    
62
    /**
63
     * Detects if GTID consistency is enforced
64
     * @param jdbcTemplate JDBC template for queries
65
     * @return true if @@GLOBAL.ENFORCE_GTID_CONSISTENCY is ON
66
     */
67
    public static boolean isRunningInGTIDConsistencyMode(JdbcTemplate jdbcTemplate);
68
    
69
    /**
70
     * Returns the constraint name for MySQL primary keys
71
     * @param tableName Name of the table
72
     * @return Quoted constraint name in format `tableName_pk`
73
     */
74
    protected String getConstraintName(String tableName);
75
    
76
    /**
77
     * Creates MySQL-specific connection instance
78
     * @param connection Raw JDBC connection
79
     * @return MySQLConnection instance
80
     */
81
    @Override
82
    protected MySQLConnection doGetConnection(Connection connection);
83
    
84
    /**
85
     * Determines database version from SELECT VERSION() output
86
     * Handles both MySQL and MariaDB version detection
87
     * @return Parsed MigrationVersion
88
     */
89
    @Override
90
    protected MigrationVersion determineVersion();
91
    
92
    /**
93
     * Ensures MySQL/MariaDB version is supported by Flyway
94
     * @param configuration Flyway configuration for edition checks
95
     */
96
    @Override
97
    public void ensureSupported(Configuration configuration);
98
    
99
    /**
100
     * Returns current database user
101
     * @return Username portion from USER() function
102
     * @throws SQLException if query fails
103
     */
104
    @Override
105
    protected String doGetCurrentUser() throws SQLException;
106
    
107
    /**
108
     * MySQL does not support DDL transactions
109
     * @return false (MySQL auto-commits DDL)
110
     */
111
    @Override
112
    public boolean supportsDdlTransactions();
113
    
114
    /**
115
     * Returns MySQL boolean true representation
116
     * @return "1"
117
     */
118
    @Override
119
    public String getBooleanTrue();
120
    
121
    /**
122
     * Returns MySQL boolean false representation
123
     * @return "0"
124
     */
125
    @Override
126
    public String getBooleanFalse();
127
    
128
    /**
129
     * Returns MySQL identifier quote character
130
     * @return "`" (backtick)
131
     */
132
    @Override
133
    public String getOpenQuote();
134
    
135
    /**
136
     * Returns MySQL identifier quote character
137
     * @return "`" (backtick)
138
     */
139
    @Override
140
    public String getCloseQuote();
141
    
142
    /**
143
     * MySQL treats database as schema
144
     * @return true
145
     */
146
    @Override
147
    public boolean catalogIsSchema();
148
    
149
    /**
150
     * Determines if single connection should be used
151
     * @return false for PXC strict mode, true otherwise
152
     */
153
    @Override
154
    public boolean useSingleConnection();
155
    
156
    /**
157
     * Detects cloud hosting environment
158
     * @return "AWS RDS" if detected, otherwise default hosting
159
     */
160
    @Override
161
    public String getDatabaseHosting();
162
    
163
    /**
164
     * Determines if CREATE TABLE AS SELECT is allowed
165
     * @return false for PXC strict mode and GTID consistency, true otherwise
166
     */
167
    protected boolean isCreateTableAsSelectAllowed();
168
    
169
    // Package-private methods for internal use
170
    boolean isMySQL();
171
    boolean isMariaDB();
172
    boolean isPxcStrict();
173
}
174
```
175

176
- **eventSchedulerQueryable** (package-private field): Boolean indicating if the event scheduler table can be queried
177

178
**Usage Examples:**
179

180
```java
181
// Automatic creation via DatabaseType
182
MySQLDatabaseType dbType = new MySQLDatabaseType();
183
MySQLDatabase database = (MySQLDatabase) dbType.createDatabase(configuration, jdbcFactory, interceptor);
184

185
// Environment detection
186
boolean isCluster = database.isWsrepOn();
187
boolean hasGtid = MySQLDatabase.isRunningInGTIDConsistencyMode(jdbcTemplate);
188

189
// Version parsing
190
String versionOutput = "8.0.28-0ubuntu0.20.04.3";
191
MigrationVersion version = MySQLDatabase.extractMySQLVersionFromString(versionOutput);
192

193
String mariaVersionOutput = "10.6.7-MariaDB-2ubuntu1.1";
194
MigrationVersion mariaVersion = MySQLDatabase.extractMariaDBVersionFromString(mariaVersionOutput);
195
```
196

197
### MariaDB Database
198

199
MariaDB-specific database implementation extending MySQL database with MariaDB-specific behavior.
200

201
```java { .api }
202
/**
203
 * MariaDB-specific database implementation
204
 * Extends MySQLDatabase with MariaDB-specific behavior
205
 */
206
public class MariaDBDatabase extends MySQLDatabase {
207
    
208
    /**
209
     * Creates a new MariaDB database instance
210
     * @param configuration Flyway configuration
211
     * @param jdbcConnectionFactory JDBC connection factory
212
     * @param statementInterceptor Statement interceptor for monitoring
213
     */
214
    public MariaDBDatabase(Configuration configuration, JdbcConnectionFactory jdbcConnectionFactory, StatementInterceptor statementInterceptor);
215
    
216
    /**
217
     * Returns constraint name format for MariaDB
218
     * MariaDB returns empty string for constraint names
219
     * @param tableName Name of the table
220
     * @return Empty string (MariaDB-specific behavior)
221
     */
222
    @Override
223
    protected String getConstraintName(String tableName);
224
    
225
    /**
226
     * Ensures MariaDB version is supported
227
     * Checks minimum version requirements and edition compatibility
228
     * @param configuration Flyway configuration for edition checks
229
     */
230
    @Override
231
    public void ensureSupported(Configuration configuration);
232
}
233
```
234

235
**Usage Example:**
236

237
```java
238
// Automatic creation via MariaDBDatabaseType
239
MariaDBDatabaseType dbType = new MariaDBDatabaseType();
240
MariaDBDatabase database = (MariaDBDatabase) dbType.createDatabase(configuration, jdbcFactory, interceptor);
241

242
// MariaDB-specific behavior
243
String constraintName = database.getConstraintName("my_table"); // Returns ""
244
```
245

246
## Environment Detection
247

248
### Percona XtraDB Cluster Detection
249

250
Detects PXC strict mode which affects certain operations:
251

252
```java
253
// Check for PXC strict mode
254
boolean isPxcStrict = MySQLDatabase.isRunningInPerconaXtraDBClusterWithStrictMode(jdbcTemplate);
255

256
// PXC strict mode affects:
257
// - CREATE TABLE AS SELECT operations (not allowed)
258
// - Single connection usage (required)
259
```
260

261
**PXC Strict Mode Values:**
262
- `ENFORCING` - Strict mode enabled
263
- `MASTER` - Master strict mode
264
- Other values - Not in strict mode
265

266
### WSREP Cluster Detection
267

268
Detects MariaDB Galera cluster environments:
269

270
```java
271
// Check for WSREP (MariaDB Galera)
272
boolean isWsrepOn = MySQLDatabase.isWsrepOn(jdbcTemplate);
273

274
// WSREP affects various operations and cluster behavior
275
```
276

277
### GTID Consistency Detection
278

279
Detects MySQL GTID consistency enforcement:
280

281
```java
282
// Check for GTID consistency
283
boolean hasGtidConsistency = MySQLDatabase.isRunningInGTIDConsistencyMode(jdbcTemplate);
284

285
// GTID consistency affects:
286
// - CREATE TABLE AS SELECT operations (not allowed)
287
// - Transaction handling
288
```
289

290
## Version Detection and Parsing
291

292
### MySQL Version Patterns
293

294
Supports various MySQL version formats:
295

296
```java
297
// Standard MySQL versions
298
String version1 = "8.0.28";
299
String version2 = "5.7.36-log";
300
String version3 = "8.0.28-0ubuntu0.20.04.3";
301

302
MigrationVersion v1 = MySQLDatabase.extractMySQLVersionFromString(version1); // 8.0
303
MigrationVersion v2 = MySQLDatabase.extractMySQLVersionFromString(version2); // 5.7
304
MigrationVersion v3 = MySQLDatabase.extractMySQLVersionFromString(version3); // 8.0
305
```
306

307
### MariaDB Version Patterns
308

309
Handles MariaDB-specific version formats:
310

311
```java
312
// Standard MariaDB
313
String mariaVersion1 = "10.6.7-MariaDB";
314
String mariaVersion2 = "10.5.15-1-MariaDB-enterprise";
315

316
// MaxScale proxy scenario
317
String maxScaleVersion = "10.6.7-2 2.5.11-maxscale";
318

319
MigrationVersion mv1 = MySQLDatabase.extractMariaDBVersionFromString(mariaVersion1); // 10.6
320
MigrationVersion mv2 = MySQLDatabase.extractMariaDBVersionFromString(mariaVersion2); // 10.5
321
MigrationVersion mv3 = MySQLDatabase.extractMariaDBVersionFromString(maxScaleVersion); // 10.6
322
```
323

324
## Database Features and Limitations
325

326
### Transaction Support
327

328
```java
329
// MySQL does not support DDL transactions
330
database.supportsDdlTransactions(); // Returns false
331
```
332

333
### Quotation Handling
334

335
```java
336
// MySQL uses backticks for identifiers
337
String openQuote = database.getOpenQuote();   // Returns "`"
338
String closeQuote = database.getCloseQuote(); // Returns "`"
339
```
340

341
### Boolean Values
342

343
```java
344
// MySQL boolean representation
345
String trueValue = database.getBooleanTrue();   // Returns "1"
346
String falseValue = database.getBooleanFalse(); // Returns "0"
347
```
348

349
### Schema Behavior
350

351
```java
352
// MySQL treats catalog as schema
353
boolean catalogIsSchema = database.catalogIsSchema(); // Returns true
354
```
355

356
### Connection Usage
357

358
```java
359
// Connection usage depends on environment
360
boolean useSingleConnection = database.useSingleConnection();
361
// Returns false for PXC strict mode, true otherwise
362
```
363

364
## Cloud Environment Detection
365

366
### AWS RDS Detection
367

368
```java
369
// Check if running on AWS RDS
370
String hosting = database.getDatabaseHosting();
371
// Returns "AWS RDS" if detected, otherwise default hosting
372
```
373

374
Detection is based on:
375
- Connection's AWS RDS detection
376
- URL patterns containing RDS identifiers
377

378
## Table Creation Scripts
379

380
The database classes provide specialized table creation scripts:
381

382
```java
383
// Get raw CREATE TABLE script for history table
384
Table historyTable = schema.getTable("flyway_schema_history");
385
String createScript = database.getRawCreateScript(historyTable, false);
386

387
// For baseline scenarios
388
String baselineScript = database.getRawCreateScript(historyTable, true);
389
```
390

391
**Features:**
392
- Tablespace support (MySQL 5.5+)
393
- Baseline data insertion
394
- CREATE TABLE AS SELECT optimization (when allowed)
395
- Constraint and index creation
396
- Environment-specific adaptations