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

Schema Management

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

Schema management classes provide MySQL-specific implementations for database schema and table operations. They handle MySQL-specific behavior for schema existence checks, table operations, and database object management.

Capabilities

MySQL Schema

MySQL-specific schema implementation providing schema operations and table management.

/**
 * MySQL-specific schema implementation
 * Handles MySQL schema operations and table management
 */
class MySQLSchema extends Schema<MySQLDatabase, MySQLTable> {
    
    /**
     * Creates a new MySQL schema instance
     * Package-private constructor used internally by MySQLDatabase
     * @param jdbcTemplate JDBC template for database operations
     * @param database MySQL database instance
     * @param name Schema name
     */
    MySQLSchema(JdbcTemplate jdbcTemplate, MySQLDatabase database, String name);
    
    /**
     * Gets a table instance for the specified table name
     * @param tableName Name of the table
     * @return MySQLTable instance for the specified table
     */
    public Table getTable(String tableName);
    
    /**
     * Checks if the schema exists in the database
     * @return true if schema exists
     */
    @Override
    protected boolean doExists();
    
    /**
     * Checks if the schema is empty (contains no tables)
     * @return true if schema contains no tables
     */
    @Override
    protected boolean doEmpty();
    
    /**
     * Creates the schema in the database
     * Executes CREATE SCHEMA statement
     */
    @Override
    protected void doCreate();
    
    /**
     * Drops the schema from the database
     * Executes DROP SCHEMA statement
     */
    @Override
    protected void doDrop();
    
    /**
     * Cleans the schema by dropping all contained objects
     * Removes all tables, views, procedures, functions, etc.
     */
    @Override
    protected void doClean();
    
    /**
     * Returns all tables in the schema
     * @return Array of MySQLTable instances for all tables
     */
    @Override
    protected MySQLTable[] doAllTables();
    
    /**
     * Returns DROP statements for all events in the schema
     * @return List of DROP EVENT statements
     * @throws SQLException if query fails
     */
    private List<String> cleanEvents() throws SQLException;
    
    /**
     * Returns DROP statements for all routines (procedures/functions) in the schema
     * @return List of DROP PROCEDURE/FUNCTION statements
     * @throws SQLException if query fails
     */
    private List<String> cleanRoutines() throws SQLException;
    
    /**
     * Returns DROP statements for all views in the schema
     * @return List of DROP VIEW statements
     * @throws SQLException if query fails
     */
    private List<String> cleanViews() throws SQLException;
    
    /**
     * Returns DROP statements for all sequences in the schema (MariaDB 10.3+)
     * @return List of DROP SEQUENCE statements
     * @throws SQLException if query fails
     */
    private List<String> cleanSequences() throws SQLException;
}

Usage Examples:

// Schema access via connection
MySQLConnection connection = database.getConnection();
MySQLSchema schema = (MySQLSchema) connection.getSchema("myschema");

// Check schema existence
if (schema.exists()) {
    System.out.println("Schema exists");
}

// Check if schema is empty
if (schema.empty()) {
    System.out.println("Schema has no tables");
}

// Get table instance
MySQLTable table = (MySQLTable) schema.getTable("users");

// Get all tables
Table[] allTables = schema.getAllTables();
for (Table table : allTables) {
    System.out.println("Table: " + table.getName());
}

MySQL Table

MySQL-specific table implementation providing table operations and locking support.

/**
 * MySQL-specific table implementation
 * Handles MySQL table operations, existence checks, and locking
 */
class MySQLTable extends Table<MySQLDatabase, MySQLSchema> {
    
    /**
     * Creates a new MySQL table instance
     * Package-private constructor used internally by MySQLSchema
     * @param jdbcTemplate JDBC template for database operations
     * @param database MySQL database instance
     * @param schema MySQL schema containing this table
     * @param name Table name
     */
    MySQLTable(JdbcTemplate jdbcTemplate, MySQLDatabase database, MySQLSchema schema, String name);
    
    /**
     * Drops the table from the database
     * Executes DROP TABLE statement with MySQL-specific options
     */
    @Override
    protected void doDrop();
    
    /**
     * Checks if the table exists in the schema
     * @return true if table exists
     */
    @Override
    protected boolean doExists();
    
    /**
     * Locks the table for exclusive access
     * Uses MySQL-specific table locking mechanisms
     */
    @Override
    protected void doLock();
}

Usage Examples:

// Table access via schema
MySQLSchema schema = (MySQLSchema) connection.getSchema("myschema");
MySQLTable table = (MySQLTable) schema.getTable("users");

// Check table existence
if (table.exists()) {
    System.out.println("Table exists");
}

// Lock table for exclusive access
table.lock();
try {
    // Perform operations requiring exclusive access
    performCriticalTableOperations();
} finally {
    // Lock is automatically released
}

// Drop table
table.drop();

Schema Operations

Schema Existence

MySQL schema existence is checked using INFORMATION_SCHEMA:

SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = ?

Behavior:

Returns true if schema exists
Case-sensitive comparison
Handles MySQL naming rules

Schema Creation

Creates schemas using MySQL syntax:

CREATE SCHEMA `schema_name`

Features:

Proper identifier quoting with backticks
Error handling for existing schemas
Character set and collation inheritance

Schema Emptiness Check

Determines if schema contains any tables:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'

Behavior:

Counts only base tables (not views)
Excludes system tables
Returns true for zero table count

Schema Cleaning

Comprehensive schema cleaning removes all objects:

Drop all tables (including foreign key dependencies)
Drop all views
Drop all stored procedures
Drop all functions
Drop all events (if event scheduler is queryable)
Drop all triggers (handled with table drops)

Order of Operations:

// Cleaning order for dependency resolution
1. Drop foreign key constraints
2. Drop tables
3. Drop views  
4. Drop routines (procedures/functions)
5. Drop events

Schema Dropping

Drops entire schema and all contents:

DROP SCHEMA `schema_name`

Features:

Cascades to all contained objects
Handles foreign key dependencies
Proper cleanup of MySQL-specific objects

Table Operations

Table Existence

Checks table existence using INFORMATION_SCHEMA:

SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND TABLE_TYPE = 'BASE TABLE'

Behavior:

Checks specific schema context
Excludes views and temporary tables
Case-sensitive table name matching

Table Locking

MySQL table locking for exclusive access:

LOCK TABLES `schema`.`table` WRITE

Features:

Exclusive write lock
Blocks other connections
Automatically released on connection close
Used for critical operations like schema history updates

Lock Types:

WRITE - Exclusive access for modifications
Session-level locking
Automatic cleanup on connection termination

Table Dropping

Drops tables with MySQL-specific handling:

DROP TABLE `schema`.`table`

Features:

Handles foreign key constraints
Cascade behavior for dependent objects
Proper cleanup of MySQL-specific table features

MySQL-Specific Behaviors

Catalog vs Schema

MySQL treats database names as schema names:

// MySQL behavior
database.catalogIsSchema(); // Returns true

// This means:
// - Database name = Schema name
// - No separate catalog concept
// - Schema operations work on database level

Identifier Quoting

All identifiers use MySQL backtick quoting:

-- Schema operations
CREATE SCHEMA `my schema`;
DROP SCHEMA `my schema`;

-- Table operations  
CREATE TABLE `my schema`.`my table` (...);
DROP TABLE `my schema`.`my table`;

Foreign Key Handling

MySQL foreign key constraints affect operation order:

During Cleaning:

Disable foreign key checks: SET foreign_key_checks = 0
Drop all tables
Restore foreign key checks: SET foreign_key_checks = 1

During Individual Drops:

Foreign key constraints may prevent table drops
Dependency resolution required
Proper error handling for constraint violations

Information Schema Integration

System Table Queries

Heavy use of INFORMATION_SCHEMA for metadata:

Schema Information:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = ?

Table Information:

SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ?

Routine Information:

SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = ?

Event Information:

SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = ?

Performance Considerations

Query Optimization:

Indexed queries on system tables
Minimal data transfer
Efficient filtering conditions

Caching Strategy:

Results cached where appropriate
Invalidation on schema changes
Connection-level caching

Error Handling

Common Schema Errors

Schema Already Exists:

ERROR 1007 (HY000): Can't create database 'schema'; database exists

Schema Not Found:

ERROR 1049 (42000): Unknown database 'schema'

Permission Denied:

ERROR 1044 (42000): Access denied for user 'user'@'host' to database 'schema'

Common Table Errors

Table Already Exists:

ERROR 1050 (42S01): Table 'table' already exists

Table Not Found:

ERROR 1146 (42S02): Table 'schema.table' doesn't exist

Foreign Key Constraint:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Error Recovery

Retry Strategies:

Temporary lock failures
Connection timeout issues
Transient permission problems

Fallback Approaches:

Alternative query methods
Reduced functionality modes
Graceful degradation

Integration with Connection Management

Transaction Behavior

MySQL schema operations and DDL:

// DDL operations auto-commit in MySQL
database.supportsDdlTransactions(); // Returns false

// This affects:
// - Schema creation/dropping
// - Table creation/dropping  
// - No rollback capability for DDL

Connection State Management

Schema operations preserve connection state:

Foreign key checks restoration
SQL safe updates restoration
User variable cleanup
Session-level settings preservation

Named Lock Integration

For concurrent schema operations:

// Use named locks for coordination
MySQLNamedLockTemplate lockTemplate = connection.getNamedLockTemplate();
lockTemplate.execute(() -> {
    // Schema operations protected by named lock
    schema.clean();
    return null;
});

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