Converting DB to InnoDB from MYISAM

[Mike Harmon]

I am using 1.5.6.c
I recently had issues with database that caused spoiradic outages and errors.
When i used Check Databasein server cpanal I got the following errors/display:
[ltimatep_156c.address_book] OK
[address_format] 2 clients are using or haven't closed the table properly
[admin] OK
[admin_activity_log] 2 clients are using or haven't closed the table properly
[admin_menus] 37 clients are using or haven't closed the table properly
etc.......................
The above is not actual because as soon as I closed the check database page and went back., all table were OK without me having done a repair or other function.

Anyway after googling the "2 clients are using or haven't closed the table properly" error i found a possible cause and solution.: https://dba.stackexchange.com/questi...-get-corrupted


MyISAM indexes tend to get corrupted when the server crashes (eg, power failure) or is otherwise rudely terminated (kill -9). You did the 'right' thing by running CHECK and REPAIR.

The datafile size is a bit worrisome; that is worse than the usual (just index issue). The crash may have happened between "writing" and "flushing", since MyISAM is not careful about syncing.

Changing to InnoDB is the 'right' thing to do. For each of your tables, do

ALTER TABLE table_name ENGINE=InnoDB;

Caution: The disk footprint for your tables will double or triple; check that you have enough disk space.

InnoDB automatically recovers after power failure and virtually any other crash.

I also found some mysql query that would convert the MYISAM Db to InnoDB format [will check farther to make sure query is up to date] :

ALTER TABLE my_table ENGINE = InnoDB;

MY QUESTION IS: WILL ZENCART WORKWITH INNODB DATABASE AND IF SO. WHAT CHANGES IF ANY NEED TO BE MADE????

[mprough]

There are some tables which running InnoDB will mess up. However, even just switching your bigger or commonly having overhead tables to InnoDB will significantly increase your website speed. I never switch sessions, configuration, configuration_group, paypal_session, db_cache & admin_profiles. You may have other created tables which can give you issues... test test test =)

~Melanie

[simon1066]

There are some tables which running InnoDB will mess up. However, even just switching your bigger or commonly having overhead tables to InnoDB will significantly increase your website speed. I never switch sessions, configuration, configuration_group, paypal_session, db_cache & admin_profiles. You may have other created tables which can give you issues... test test test =)

~Melanie

I've just tried this on a number of my tables - still testing but doing so seems to have knocked 0.5 sec off the parse time and halved the query time 2 sec down to 1 sec, thank you for suggesting this. This is on my local machine so probably not entirely representative of real-world performance,

I never switch sessions, configuration, configuration_group, paypal_session, db_cache & admin_profiles...

BTW: on two sites I've recently upgraded to ZC v1.5.6c, the Sessions table runs InnoDB (all others MyISAM) - maybe a v1.5.6c thing as a v1.5.5f site has it as MyISAM. Mind you I've touched the dbases a lot over the years so no telling what I might have done in the past!

[simon1066]

BTW: on two sites I've recently upgraded to ZC v1.5.6c, the Sessions table runs InnoDB (all others MyISAM) - maybe a v1.5.6c thing as a v1.5.5f site has it as MyISAM. Mind you I've touched the dbases a lot over the years so no telling what I might have done in the past!

Yes, I see that mysql_upgrade_zencart_156.sql did change sessions to InnoDB