Kryptronic Software Support Forum

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.

#1 01-28-2011 13:39:03

Design_Wholesale
Member
From: England!
Registered: 11-21-2008
Posts: 1104
Website

myisamchk or innodb? ...And How To Use One Over The Other?

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

 

#2 01-28-2011 13:58:14

Dave
Member
Registered: 07-05-2003
Posts: 11233

Re: myisamchk or innodb? ...And How To Use One Over The Other?

Simply change any tables that you want to use that storage engine using:

Code:

ALTER TABLE `table_name_here` ENGINE = InnoDB

Offline

 

#3 01-28-2011 14:29:55

Design_Wholesale
Member
From: England!
Registered: 11-21-2008
Posts: 1104
Website

Re: myisamchk or innodb? ...And How To Use One Over The Other?

Ah, perfect! - Thank-you. - If only the MySQL documentation was as clear... smile

Offline

 

#4 01-28-2011 15:52:42

Dave
Member
Registered: 07-05-2003
Posts: 11233

Re: myisamchk or innodb? ...And How To Use One Over The Other?

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 smile

Offline

 

#5 02-07-2011 22:57:28

Design_Wholesale
Member
From: England!
Registered: 11-21-2008
Posts: 1104
Website

Re: myisamchk or innodb? ...And How To Use One Over The Other?

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.

Code:

<?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

 

Board footer