{"id":345,"date":"2012-04-25T12:22:30","date_gmt":"2012-04-25T12:22:30","guid":{"rendered":"http:\/\/www.plugged.in\/?p=345"},"modified":"2012-04-25T12:22:30","modified_gmt":"2012-04-25T12:22:30","slug":"how-to-change-storage-engine-to-innodb-in-mysql","status":"publish","type":"post","link":"https:\/\/www.veriteknik.net.tr\/en\/how-to-change-storage-engine-to-innodb-in-mysql\/","title":{"rendered":"How to change storage engine to InnoDB in MySQL ?"},"content":{"rendered":"

You’ve designed a database with MyISAM and suddenly realized that you need ACID\u00a0(atomicity,\u00a0consistency,\u00a0isolation,\u00a0durability<\/em>) compliant features and transactions.<\/p>\n

Right, you should use InnoDB<\/a>. But how to change storage engine for your previous tables ?<\/p>\n

As far as i know, changing the storage engine for tables is easy by alter command.<\/p>\n

But there’s one flaw. You should do it one by one.<\/p>\n

On the other hand, you could either write complex SQL queries, or you could write a php code for bulk changes.<\/p>\n

Today i’m gonna show you how to change your storage engine with a tiny code.<\/p>\n

Please notice that if you decide to use my code it is your responsibility.<\/span><\/p>\n

$db = ‘your_database_name_here’;<\/p>\n

$dsn = “mysql:host=localhost;dbname=$db”;<\/p>\n

$username = “your_MySQL_username”;<\/p>\n

$password = “your_password”;<\/p>\n

$current = ‘MyISAM’; \/\/ Current storage engine<\/p>\n

$target = ‘INNODB’; \/\/ Target storage engine<\/p>\n

 <\/p>\n

try {<\/p>\n

$pdo = new PDO($dsn, $username, $password);<\/p>\n

}<\/p>\n

catch(PDOException $e) {<\/p>\n

die(“Could not connect to the database\\n”);<\/p>\n

}<\/p>\n

$result = $pdo->query(“SELECT TABLE_NAME\u00a0FROM information_schema.TABLES\u00a0WHERE TABLE_SCHEMA = ‘$db’\u00a0AND ENGINE = ‘$current'”);<\/p>\n

foreach($result as $row) {<\/p>\n

$success = $pdo->exec(“ALTER TABLE {$row[‘TABLE_NAME’]} ENGINE = $target”);<\/p>\n

if($success) {<\/p>\n

echo “{$row[‘TABLE_NAME’]} – success\\n”;<\/p>\n

}\u00a0else {<\/p>\n

$info = $pdo->errorInfo();<\/p>\n

echo “{$row[‘TABLE_NAME’]} – failed: $info[2]\\n”;<\/p>\n

}<\/p>\n

}<\/p><\/blockquote>\n

That’s all.<\/p>\n

If you see “failed” messages, there’s probably a permission issue with information_schema table.<\/span><\/p>\n

In that case you should enter MySQL command line, and repeat the procedure there.<\/p>\n

To list storage engines for tables, you should execute;<\/p>\n

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘your_database_name’;<\/p><\/blockquote>\n

This will show you which tables are using which storage engine.<\/p>\n

After that you should execute;<\/p>\n

USE your_database_name;<\/p><\/blockquote>\n

When you see “Database changed” message, you should execute;<\/p>\n

ALTER TABLE your_table_name ENGINE = INNODB;<\/p><\/blockquote>\n

Have a nice life with your magnificent database storage engine InnoDB !<\/p>\n

 <\/p>\n

Please check out other articles about InnoDB and Transactions.<\/p>\n","protected":false},"excerpt":{"rendered":"

You’ve designed a database with MyISAM and suddenly realized that you need ACID\u00a0(atomicity,\u00a0consistency,\u00a0isolation,\u00a0durability) compliant features and transactions. Right, you should use InnoDB. But how to change storage engine for your previous tables ? As far as i know, changing the storage engine for tables is easy by alter command. But there’s one flaw. You should […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","footnotes":""},"categories":[363,364],"tags":[],"yst_prominent_words":[],"class_list":["post-345","post","type-post","status-publish","format-standard","hentry","category-databases","category-mysql-databases"],"jetpack_featured_media_url":"","uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Ahmet Polat","author_link":"https:\/\/www.veriteknik.net.tr\/en\/author\/ahmet-polat\/"},"uagb_comment_info":0,"uagb_excerpt":"You’ve designed a database with MyISAM and suddenly realized that you need ACID\u00a0(atomicity,\u00a0consistency,\u00a0isolation,\u00a0durability) compliant features and transactions. Right, you should use InnoDB. But how to change storage engine for your previous tables ? As far as i know, changing the storage engine for tables is easy by alter command. But there’s one flaw. You should…","_links":{"self":[{"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/posts\/345","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/comments?post=345"}],"version-history":[{"count":0,"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/posts\/345\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/media?parent=345"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/categories?post=345"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/tags?post=345"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/www.veriteknik.net.tr\/en\/wp-json\/wp\/v2\/yst_prominent_words?post=345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}