tessl/maven-org-flywaydb--flyway-mysql

MySQL database type plugin for Flyway database migration engine providing specialized MySQL and MariaDB support for schema migrations.

—

Pending

Overview

Eval results

Files

Utility Components

Name: tessl/maven-org-flywaydb--flyway-mysql
Author: tessl

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

Capabilities

MySQL Named Lock Template

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

/**
 * MySQL named lock template for coordinating concurrent operations
 * Uses MySQL's GET_LOCK() and RELEASE_LOCK() functions
 */
public class MySQLNamedLockTemplate {
    
    /**
     * Creates a named lock template instance
     * Package-private constructor used internally by connection classes
     * @param jdbcTemplate JDBC template for database operations
     * @param discriminator Unique identifier for generating lock name
     */
    MySQLNamedLockTemplate(JdbcTemplate jdbcTemplate, int discriminator);
    
    /**
     * Executes a callable within a MySQL named lock
     * Acquires lock before execution, releases after completion
     * @param <T> Return type of the callable
     * @param callable Operation to execute with lock protection
     * @return Result from callable execution
     * @throws Exception if callable throws exception or lock acquisition fails
     */
    public <T> T execute(Callable<T> callable) throws Exception;
    
    /**
     * Acquires the MySQL named lock with retry logic
     * @throws SQLException if lock acquisition fails permanently
     */
    private void lock() throws SQLException;
    
    /**
     * Attempts to acquire the lock once with timeout
     * @return true if lock acquired, false if timeout occurred
     * @throws SQLException if lock operation fails
     */
    private boolean tryLock() throws SQLException;
}

Fields:

// Lock management fields
private final JdbcTemplate jdbcTemplate;
private final String lockName;
}

Usage Examples:

// Named lock template creation (typically internal)
MySQLNamedLockTemplate lockTemplate = new MySQLNamedLockTemplate(jdbcTemplate, 12345);

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

// Exception handling example
try {
    lockTemplate.execute(() -> {
        // Operation that might fail
        updateSchemaHistory();
        return null;
    });
} catch (Exception e) {
    // Handle operation failure or lock acquisition failure
    LOG.error("Failed to execute with lock: " + e.getMessage());
}

MySQL Option File Reader

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

/**
 * MySQL option file reader for external authentication
 * Implements ExternalAuthFileReader interface for Flyway authentication system
 */
public class MySQLOptionFileReader implements ExternalAuthFileReader {
    
    /**
     * Creates a new MySQL option file reader instance
     */
    public MySQLOptionFileReader();
    
    /**
     * List of discovered MySQL option files
     * Populated by populateOptionFiles() method
     */
    public final List<String> optionFiles;
    
    /**
     * List of discovered encrypted MySQL option files (.mylogin.cnf)
     * Populated by populateOptionFiles() method
     */
    private final List<String> encryptedOptionFiles;
    
    /**
     * Returns contents of all discovered option files
     * Current implementation returns empty list
     * @return Empty list (implementation placeholder)
     */
    public List<String> getAllContents();
    
    /**
     * Populates the optionFiles list based on operating system
     * Discovers MySQL option files in standard locations
     */
    public void populateOptionFiles();
    
    /**
     * Adds option file to list if it exists on filesystem
     * @param optionFilePath Path to option file to check
     * @param encrypted Whether the file is encrypted (.mylogin.cnf)
     */
    private void addIfOptionFileExists(String optionFilePath, boolean encrypted);
}

Usage Examples:

// Option file reader creation
MySQLOptionFileReader reader = new MySQLOptionFileReader();

// Discover option files
reader.populateOptionFiles();

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

// Get file contents (currently returns empty)
List<String> contents = reader.getAllContents();

Named Lock Functionality

Lock Mechanism

MySQL named locks provide application-level coordination:

Lock Acquisition:

SELECT GET_LOCK('flyway_lock_12345', timeout_seconds)

Lock Release:

SELECT RELEASE_LOCK('flyway_lock_12345')

Lock Name Generation:

Base name: flyway_lock_
Discriminator: Unique identifier (typically connection hash)
Full name: flyway_lock_12345

Lock Behavior

Acquisition:

Returns 1 if lock acquired successfully
Returns 0 if timeout occurred
Returns NULL if error occurred

Timeout Handling:

Configurable timeout (typically 10 seconds)
Automatic retry logic for transient failures
Error handling for permanent failures

Release:

Returns 1 if lock released successfully
Returns 0 if lock was not held by connection
Returns NULL if lock did not exist

Concurrency Control

Named locks enable coordination for:

Schema History Updates:

lockTemplate.execute(() -> {
    // Update flyway_schema_history table
    // Ensures only one Flyway instance updates at a time
    updateSchemaHistoryTable();
    return null;
});

Critical Database Operations:

lockTemplate.execute(() -> {
    // Operations requiring exclusive access
    performSchemaCleanup();
    recreateIndexes();
    return "Cleanup completed";
});

Multi-Instance Coordination:

Prevents concurrent schema migrations
Coordinates database initialization
Manages shared resource access

Lock Limitations

Session Lifetime:

Locks are released when connection closes
Not persistent across connection failures
Require active database connection

Database Scope:

Locks are database-scoped, not global
Different databases can have same lock names
No cross-database coordination

Error Conditions:

Network failures release locks
Connection timeouts affect lock state
Database restarts clear all locks

MySQL Option File Support

Standard Option Files

MySQL looks for option files in standard locations:

Linux/Unix:

/etc/mysql/my.cnf
/etc/my.cnf
~/.my.cnf
~/.mysql/my.cnf

Windows:

%PROGRAMDATA%\MySQL\MySQL Server X.Y\my.ini
%WINDIR%\my.ini
C:\my.cnf
%APPDATA%\MySQL\.mylogin.cnf

macOS:

/usr/local/mysql/etc/my.cnf
/opt/local/etc/mysql/my.cnf
~/.my.cnf

Option File Format

Standard MySQL option file format:

[client]
user=myuser
password=mypassword
host=localhost
port=3306
database=mydb

[mysql]
default-character-set=utf8mb4

[flyway]
# Flyway-specific options
user=flyway_user
password=flyway_password

Authentication Integration

Option file integration with Flyway authentication:

// Automatic integration in MySQLDatabaseType
@Override
public Properties getExternalAuthProperties(String url, String username) {
    MySQLOptionFileReader reader = new MySQLOptionFileReader();
    reader.populateOptionFiles();
    
    if (!reader.optionFiles.isEmpty()) {
        // Log availability of option files
        LOG.info("MySQL option file detected for authentication");
    }
    
    return super.getExternalAuthProperties(url, username);
}

Encrypted Option Files

Support for MySQL encrypted login files:

Login Path Utility:

# Create encrypted credentials
mysql_config_editor set --login-path=flyway --host=localhost --user=flyway_user --password

# Use in connection string
jdbc:mysql://localhost:3306/mydb?useLoginPath=flyway

Benefits:

Encrypted credential storage
No plaintext passwords in configuration
Operating system user-specific access
Integration with MySQL utilities

Error Handling and Diagnostics

Named Lock Errors

Lock Acquisition Failures:

// Timeout scenarios
if (lockResult == 0) {
    throw new FlywayException("Failed to acquire named lock within timeout");
}

// Error scenarios  
if (lockResult == null) {
    throw new FlywayException("Error occurred while acquiring named lock");
}

Lock Release Issues:

// Lock not owned
if (releaseResult == 0) {
    LOG.warn("Attempted to release lock not owned by this connection");
}

// Lock not found
if (releaseResult == null) {
    LOG.warn("Attempted to release non-existent lock");
}

Option File Errors

File Access Issues:

Permission denied for option files
Option files not found in standard locations
Malformed option file syntax
Encrypted file decryption failures

Configuration Problems:

Invalid MySQL option syntax
Conflicting option values
Missing required options
Character encoding issues

Diagnostic Information

Lock Status Queries:

-- Check active locks
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES 
WHERE PRIVILEGE_TYPE = 'LOCK';

-- Check lock status (MySQL 8.0+)
SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE 
FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'USER LEVEL LOCK';

Option File Discovery:

// Log discovered option files
for (String optionFile : reader.optionFiles) {
    LOG.debug("Discovered MySQL option file: " + optionFile);
}

// Log encryption status
for (String encryptedFile : reader.encryptedOptionFiles) {
    LOG.debug("Discovered encrypted option file: " + encryptedFile);
}

Integration with Core Components

Connection Integration

Named locks integrate with connection management:

// Connection provides lock template access
MySQLConnection connection = database.getConnection();
if (connection.canUseNamedLockTemplate()) {
    MySQLNamedLockTemplate lockTemplate = connection.getNamedLockTemplate();
    // Use lock template for coordination
}

Database Type Integration

Authentication integration in database types:

// MySQLDatabaseType automatically checks for option files
@Override
public Properties getExternalAuthProperties(String url, String username) {
    // Option file detection and integration
    return enhancedProperties;
}

Configuration Integration

Utility components enhance Flyway configuration:

Authentication: Option file credential discovery
Concurrency: Named lock coordination setup
Environment: Cloud and cluster environment detection
Monitoring: Enhanced logging and diagnostics

Performance Considerations

Named Lock Performance

Lock Overhead:

Minimal CPU overhead for lock operations
Network round-trip for each lock operation
Database server lock table management

Optimization Strategies:

Reuse lock templates where possible
Minimize lock hold time
Batch operations within lock scope
Monitor lock contention

Option File Performance

File System Access:

Option file discovery involves file system checks
Caching of discovered file locations
Lazy loading of file contents
Efficient parsing of configuration data

Memory Usage:

Minimal memory footprint for configuration data
Temporary storage during option file processing
Cleanup of sensitive credential information

Install with Tessl CLI

npx tessl i tessl/maven-org-flywaydb--flyway-mysql

docs

connection-management.md

database-operations.md

utility-components.md

tile.json

tessl/maven-org-flywaydb--flyway-mysql