MySQL: InnoDB vs. MyISAM: how and why to change (Amazon RDS)?

Question

I didn't set up my database and noticed that some of my tables are innoDB whereas others are MyISAM. The reason I'm interested in changing them is because Jurg van Vliet (who writes O'Reilly books on AWS) says that Amazon RDS's automatic database backup ability hinges on all tables being innoDB tables.

What's the command to convert all tables in a database to InnoDB?

Weatherby

Answer 1

Here's a stored procedure to convert all MyISAM table in the current database to InnoDB:

DROP PROCEDURE IF EXISTS convert_all_tables_to_innodb;

DELIMITER //

CREATE PROCEDURE convert_all_tables_to_innodb()
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT ''
proc: BEGIN
    DECLARE b_not_found     BOOL DEFAULT FALSE;
    DECLARE v_table_name VARCHAR(255);
    DECLARE cur1 CURSOR FOR
SELECT
table_name
FROM
information_schema.tables
WHERE
table_schema = DATABASE() AND
table_type = 'BASE TABLE' AND
engine = 'MyISAM'
ORDER BY
1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b_not_found = TRUE;

    OPEN cur1;

    SET b_not_found = FALSE;

    loop1: LOOP
        FETCH cur1 INTO v_table_name;
        IF b_not_found THEN
            LEAVE loop1;
        END IF;

        SET @sql = CONCAT('ALTER TABLE ', v_table_name, ' ENGINE=InnoDB');

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DROP PREPARE stmt;
    END LOOP;

    CLOSE cur1;
END;
//

DELIMITER ;

To run it, simply execute:

CALL convert_all_tables_to_innodb();

Postpaid

-@Ross thanks, is there no simple SQL command for this? Also, can you explain why you switched from MyISAM to innoDB? – Weatherby

Sorry, but there is no single command to convert all the tables, which is what you specified in your question. I offered this code to answer your question. – Postpaid

-@Ross thanks, did you use this stored_procedure yourself? If so, would you mind sharing some context as to why you used it? – Weatherby

Tim, no I have not used this procedure as written. I have used similar prodedures to ANALYSE, or OPTIMIZE all tables. – Postpaid

-@Ross, just wanted to let you know i called this stored procedure and it worked great, all my tables are now innoDB, thanks! – Weatherby

Great to hear, and glad to help! – Postpaid