When migrating databases or fixing storage engine issues like those documented in MySQL bugs #1341 and #1287, you often need to convert multiple tables to InnoDB simultaneously. Manually executing ALTER TABLE statements for each table becomes impractical in production environments with hundreds of tables.

The most efficient method uses dynamic SQL generation from the information_schema:

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') 
FROM information_schema.tables 
WHERE table_schema = 'your_database_name' 
AND engine = 'MyISAM';

Here's a complete stored procedure that handles the conversion:

DELIMITER //
CREATE PROCEDURE convert_to_innodb(IN db_name VARCHAR(64))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl_name VARCHAR(64);
  DECLARE cur CURSOR FOR 
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = db_name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tbl_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('ALTER TABLE ', db_name, '.', tbl_name, ' ENGINE=InnoDB');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

-- Execute the procedure
CALL convert_to_innodb('your_database_name');

For those preferring command-line solutions:

mysql -u username -p -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') 
FROM information_schema.tables 
WHERE table_schema = 'your_database'" | tail -n +2 > alter_tables.sql

mysql -u username -p your_database < alter_tables.sql

• Backup your database before running mass ALTER operations
• Large tables may take significant time to convert
• Consider using pt-online-schema-change for minimal locking
• Verify foreign key constraints after conversion

When working with legacy MySQL databases, you might encounter tables still using the older MyISAM storage engine. Converting them to InnoDB becomes essential for:

• ACID compliance and transaction support
• Better crash recovery
• Row-level locking
• Foreign key constraints

Here's a complete solution to convert all tables in a database to InnoDB:

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') 
FROM information_schema.tables 
WHERE table_schema = 'your_database_name' 
AND ENGINE <> 'InnoDB';

This generates all the necessary ALTER TABLE statements which you can then execute.

For a more automated approach, use this stored procedure:

DELIMITER //
CREATE PROCEDURE convert_to_innodb(IN db_name VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = db_name AND ENGINE <> 'InnoDB';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ', db_name, '.', table_name, ' ENGINE=InnoDB;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- Usage:
CALL convert_to_innodb('your_database_name');

When converting tables, consider these factors:

-- Check for FULLTEXT indexes (not supported by InnoDB before MySQL 5.6)
SELECT table_name, index_name 
FROM information_schema.statistics 
WHERE table_schema = 'your_database_name' 
AND index_type = 'FULLTEXT';

-- Check table sizes before conversion
SELECT table_name, 
       round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.tables 
WHERE table_schema = "your_database_name";

For those preferring command-line solutions:

#!/bin/bash
DB="your_database_name"
USER="your_username"
PASS="your_password"

mysql -u$USER -p$PASS -e "SELECT table_name FROM information_schema.tables WHERE table_schema = '$DB' AND ENGINE <> 'InnoDB';" | \
grep -v "table_name" | \
while read table; do
    echo "Converting $table..."
    mysql -u$USER -p$PASS -e "ALTER TABLE $DB.$table ENGINE=InnoDB;"
done

After running the conversion, verify all tables are now InnoDB:

SELECT table_name, ENGINE 
FROM information_schema.tables 
WHERE table_schema = 'your_database_name' 
AND ENGINE <> 'InnoDB';

This should return an empty result set if all conversions were successful.