Skip to content

Instantly share code, notes, and snippets.

@pangkalizer

pangkalizer/convert_all_tables_to_innodb.sql

Created May 19, 2016 07:22
Show Gist options
  • Save pangkalizer/a6f6812eb3e9543922b42a4225b7ba0f to your computer and use it in GitHub Desktop.
Save pangkalizer/a6f6812eb3e9543922b42a4225b7ba0f to your computer and use it in GitHub Desktop.
Download ZIP
convert MEMORY tables to InnoDB
Raw
convert_all_tables_to_innodb.sql
DELIMITER $$
DROP DATABASE IF EXISTS `test` $$
CREATE DATABASE `test` $$
DROP PROCEDURE IF EXISTS `test`.`convert_all_tables_to_innodb` $$
CREATE PROCEDURE `test`.`convert_all_tables_to_innodb`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE alter_sql VARCHAR(5000);
DECLARE cur1 CURSOR FOR
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=innodb, ALGORITHM=copy;') as alter_sql
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE') AND (ENGINE IN ('MEMORY')
AND TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO alter_sql;
IF done THEN
LEAVE read_loop;
END IF;
SELECT alter_sql;
SET @c = alter_sql;
PREPARE stmt1 FROM @c;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END LOOP;
CLOSE cur1;
END $$
DELIMITER ;
USE `test`;
CALL convert_all_tables_to_innodb();
DROP DATABASE IF EXISTS `test`;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment