Author Topic: ....to use the MyISAM database engine. CiviCRM requires InnoDB  (Read 5131 times)

bubbasheeko

  • Guest
....to use the MyISAM database engine. CiviCRM requires InnoDB
« on: February 11, 2009, 10:55:22 am »
Now, by default my MySQL is using MyISAM.  Now CiviCRM is installed along side with Joomla 1.5. 

I obviously received this message in CiviCRM:

Quote
Your database is configured to use the MyISAM database engine. CiviCRM requires InnoDB. You will need to convert any MyISAM tables in your database to InnoDB. Using MyISAM tables will result in data integrity issues. This will be a fatal error in CiviCRM v2.1.

Do I need to convert all tables in my database for Joomla to InnoDB?  If so, how will this affect Joomla?  Or will it?

Offline Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5697
  • Karma: 225
    • My CiviCRM Blog
Re: ....to use the MyISAM database engine. CiviCRM requires InnoDB
« Reply #1 on: February 11, 2009, 04:11:09 pm »
I'm pretty sure you can just convert the civicrm_* tables to INNODB. (Try this on a test copy of the site first if this is a production site.)
Protect your investment in CiviCRM by  becoming a Member!

bubbasheeko

  • Guest
Re: ....to use the MyISAM database engine. CiviCRM requires InnoDB
« Reply #2 on: February 11, 2009, 06:16:09 pm »
Hi Dave,

Excellent.  I am testing Joomla 1.5 for an upgrade that I am working on...so this is the perfect time to discover the database item ;)

Thanks for letting me know!

bubbasheeko

  • Guest
Re: ....to use the MyISAM database engine. CiviCRM requires InnoDB
« Reply #3 on: February 11, 2009, 06:19:15 pm »
Well I was excited for the response :P

What would be the easiest and fastest way to convert the civicrm_* tables to InnoDB?

Offline Sunil

  • I post frequently
  • ***
  • Posts: 131
  • Karma: 23
  • The community around a product more important than the product itself?
    • CiviCRM
Re: ....to use the MyISAM database engine. CiviCRM requires InnoDB
« Reply #4 on: February 11, 2009, 08:40:41 pm »
 Dump the database with mysqldump

The first step is to dump the existing database using the mysqldump utility. The dump provides a complete backup of the database in case something goes wrong, and is also used to restore it later in the InnoDB format. Make sure the application is not in use while performing the conversion.

Here is syntax use:

mysqldump --user=user --password=password --add-drop-table --databases db1 > db1.sql

Change TYPE=ISAM to TYPE=INNODB

The second step is to edit the db1.sql dump file with a text editor and Replace the table type to InnoDB. Make of copy of the dump file before you edit it in case you need to restore it later.

and for more info check this http://www.linux.com/feature/46370
OR
you can use this
Code: [Select]
<?php
$link 
=  mysql_connect("localhost""mysql_user""mysql_password");
if (!
$link) {
    die(
'Could not connect: ' mysql_error());
}

$result   mysql_list_tables("DatabaseName");
$num_rows mysql_num_rows($result);
$tablearr = array();

for (
$i 0$i $num_rows$i++) {
    
$tablearr[] = mysql_tablename($result$i);
}

foreach(
$tablearr as $key => $table)  {
     echo 
"ALTER TABLE $table ENGINE = INNODB;<BR>";
}

mysql_free_result($result);

?>
it will generate the mysql command for all civicrm tables
sunil
« Last Edit: February 11, 2009, 09:50:50 pm by Sunil »
The community around a product more important than the product itself?

Rae

  • Guest
Re: ....to use the MyISAM database engine. CiviCRM requires InnoDB
« Reply #5 on: March 06, 2009, 02:06:24 pm »
Do you have a method on how to do this using phpmyadmin?

Best wishes,
Rae

Offline Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5697
  • Karma: 225
    • My CiviCRM Blog
Re: ....to use the MyISAM database engine. CiviCRM requires InnoDB
« Reply #6 on: March 06, 2009, 02:48:27 pm »
Did a quick google search and found this link which might help:

http://rackerhacker.com/2007/10/03/convert-myisam-tables-to-innodb/
Protect your investment in CiviCRM by  becoming a Member!

Offline Deepak Srivastava

  • Ask me questions
  • ****
  • Posts: 672
  • Karma: 65
Re: ....to use the MyISAM database engine. CiviCRM requires InnoDB
« Reply #7 on: April 20, 2009, 10:02:27 am »
If its just civicrm db, the another simpler way (IMO) is to -

1. create a new db with structure from civicrm_codebase/sql/civicrm.mysql.
2. import only data from your old db to the new one (created in step 1).
Found this reply helpful? Contribute NOW and help us improve CiviCRM with the Make it Happen! initiative.