You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.
I received a reply to a query I made from my webhost a couple of days ago advising me to use innodb in place of myisamchk for checking and repairing our database tables.
Well great, - innodb is included in MySQL as of version 5.2., I believe, and is detailed in the file if you are not sure about the version you have installed, but how do I use it? - I spent the whole of yesterday evening reading the documentation and every last part of it is enabled by default in the more recent versions of MySQL. In fact, the innodb configuration setting that does not seem to be enabled by default is ...and the documentation makes that look like a total "kill or cure" last ditch option (so I definitely do not want to enable that on of the six available levels unless/until I can find out more about innodb versus myisamchk).
Can anyone here clarify the situation with this?
Last edited by Design_Wholesale (01-28-2011 13:40:40)
Offline
Simply change any tables that you want to use that storage engine using:
ALTER TABLE `table_name_here` ENGINE = InnoDB
Offline
Ah, perfect! - Thank-you. - If only the MySQL documentation was as clear...
Offline
It's actually shown in the create table syntax on page 788 in the documentation (section 13.1.5) which is referenced by the alter table section 13.1.2. I found it quickly by doing it using phpMyAdmin on a test table
Offline
The script shown below appears to work for altering every table in the database. - Our webstore is still working, but could someone here please confirm whether I should, after converting all the tables to InnoDB, have a .frm extension for each and every one of them? - If so, then this script works as intended.
<?php include '/var/wetsocks.php'; mysql_pconnect("$s", "$u", "$p") or die(mysql_error()); mysql_select_db("$d") or die(mysql_error()); $sql = "SHOW TABLES FROM $d LIKE '%'"; $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { foreach ($row as $key => $table) { mysql_query("ALTER TABLE `$table` ENGINE = InnoDB"); echo $key . " => " . $table . " successfully altered...<br />"; } // End of foreach statement. } // End of while statement. if (!$result) { echo "DB Error, could not list tables\n"; echo 'MySQL Error: ' . mysql_error(); exit; } // End of if statement. ?>
Offline