Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 5
Can someone clarify: MyISAM or InnoDB
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 

I came across a thread which suggested that all tables should be InnoDB. I checked my install (1.1.8), and almost all my tables are MyISAM. I don’t experience any issues, so should I be concerned?  If everything should be InnoDB, is it safe for me to convert all my tables on a live store?  I don’t know why they were in there as MyISAM in the first place.. I started with 1.1.5 and just upgraded with each new version.

Edit: ok, so I am experiencing 1 issue, probably as a result of this.  I can’t use the Google Base feature, it throws this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`databasename/googlebase_types`, CONSTRAINT `GOOGLEBASE_TYPES_ATTRIBUTE_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE)

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

My guess is that your hosting provider does not allow InnoDB tables. The reason for this is because then they could not count your database toward your disk allotment.

You should NEVER, EVER run magento with MyISAM tables

Trust me, it will cause you numerous headaches later on. The reason for this is because magento depends on foreign_keys and I doubt you are using them with MyISAM (it is planned in a later release of mysql. Dont think it is available yet).

So, lets say you delete a product. This is how it works:

INNODB:

Product and all releated data is deleted (attributes, images, relationships, etc) Your database is clean

MYISAM:

Product is deleted from catalog_product_entity. However, attributes, relationships, etc, etc is still present in database collecting dust.

The above is just a simple example. There are even worse things that can happen by using MyISAM tables with Magento.

 
Magento Community Magento Community
Magento Community
Magento Community
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 

OK now you’re scaring me. (but thank you for responding).  Can I literally just change them all to InnoDB with no ill effects, assuming PHPMyAdmin lets me?  I have a dedicated server and I’m on my own if anything gets majorly hosed..

 
Magento Community Magento Community
Magento Community
Magento Community
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 

I’m attaching the sql script in case anyone else has this same issue. The tables in the script are current as of 1.1.8.

Edit: I went ahead and converted all my tables. All seems to be well, and my Google Base error went away.  cheese

Edit #2: The attached script was first posted by a Magento team member many months ago; it’s been modified to be current.

File Attachments
innodb.sql  (File Size: 14KB - Downloads: 829)
 
Magento Community Magento Community
Magento Community
Magento Community
 
radders
Sr. Member
 
Total Posts:  162
Joined:  2008-03-23
 

Good idea!
Btw for those running locally using xampp, it doesn’t by default allow innodb so running this sql will not have any effect until you enable innodb support in the ‘my.cnf’ file.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Michae1
Enthusiast
 
Total Posts:  826
Joined:  2007-08-31
 

You should never install and run Magento without InnoDB support. This might cause a lot of unexpected issues with no easy way to track what’s going on. It might also cost you a few hours of hard work to fix DB data integrity once you have your store going live.

The script posted above won’t really help, as it misses the main point of using InnoDB in Magento - foreign keys. It’s not enough to change table engine type to InnoDB - you should also recreate missing foreign keys.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Crucial Web Host
Guru
 
Avatar
Total Posts:  364
Joined:  2007-11-08
Phoenix, AZ
 

Say Michael ~ Do you think this could have anything to do with the many hosting problems that shared hosters seem to encounter?

My guess is that most Magento clients would have very little concept of myisam -vs- innodb.  The installer doesnt care, why should they?

If its such a big deal, don’t you think that the installer should not let Magento install on myisam?  I would think so.

We see this a lot - and now that Magento is available via Installatron and other automated installers it’s leaving even more people very disappointed.

There is a better place than this to learn this lesson.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Crucial Web Host
Guru
 
Avatar
Total Posts:  364
Joined:  2007-11-08
Phoenix, AZ
 

Google Magento Performance MySQL slow table

Michael - 25 December 2008 03:46 PM

You should never install and run Magento without InnoDB support. This might cause a lot of unexpected issues with no easy way to track what’s going on.

Worth repeating.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Michae1
Enthusiast
 
Total Posts:  826
Joined:  2007-08-31
 

Yes, we started to look if there’s a way to determine InnoDB support presence from PHP. If there’s a reliable way that wouldn’t give false negative results, we’ll add it in 1.2 installer and in magento-check.php script.

 
Magento Community Magento Community
Magento Community
Magento Community
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 
Michael - 25 December 2008 03:46 PM

The script posted above won’t really help, as it misses the main point of using InnoDB in Magento - foreign keys. It’s not enough to change table engine type to InnoDB - you should also recreate missing foreign keys.

Would you care to clarify? Or is that something that would only be resolved in a paid support call?  hmmm
Also I would like to mention that I didn’t choose MyISAM, it did it by default. I installed Magento back when 1.1.5 was out and have upgraded with each version. MyISAM was always there. I’m concerned now about these foreign keys you’re talking about. Whatever they are, I want to get them back. I didn’t even know they were missing.  long face

 
Magento Community Magento Community
Magento Community
Magento Community
 
Michae1
Enthusiast
 
Total Posts:  826
Joined:  2007-08-31
 
turbo1 - 26 December 2008 08:41 AM

Michael - 25 December 2008 03:46 PM

The script posted above won’t really help, as it misses the main point of using InnoDB in Magento - foreign keys. It’s not enough to change table engine type to InnoDB - you should also recreate missing foreign keys.

Would you care to clarify?

Sure, just let me hold it up until 1.2.0 is released.

 
Magento Community Magento Community
Magento Community
Magento Community
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 

No worries, I don’t seem to be having any ill effects so I can wait. I appreciate that Magento team members are coming onto the forums and helping, it’s good to see you guys again.  cool smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
xactrecords
Sr. Member
 
Total Posts:  80
Joined:  2008-09-14
 

I am having the same issue as turbo1.  I have moved to a dedicated server so I can have innodb and converted my myisam database.  However I am having issues when trying to remove a product / category relationship.  It shows that product no longer resides in that category and I do a full cache clear and deletion of var/cache yet the front end still shows the product in the category.  I assume this is the type of issues that can happen with the foreign keys.

What is a way to fix this sorta thing?

 
Magento Community Magento Community
Magento Community
Magento Community
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 

@xactrecords
I’m also anxiously awaiting michael’s reply.  I think we have some issues with foreign keys - I ran into a whole host of problems when trying to upgrade to 1.2.0, which never happened before I switched everything to InnoDB.  I’m kinda wishing I had left it as MyISAM .

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

xactrecords,

Yeah, that is the sort of thing that can happen. Now that your database is unclean there is not really an easy way to go back. You cannot simply just add foreign keys to your tables now because the foreign keys will fail. For example, you may have a table named abc_table that has some_id that points to some_table:some_id. Lets say the value of abc_table:some_id is 13. Well, because you don’t have foreign keys anymore you don’t know if there is a corresponding some_table:some_id that equals 13. If you were to just add foreign keys and some_table:some_id with a value of 13 did not exist then the foreign key would fail.

So, you would first have to run a script that cleaned the database and deleted orphan records prior to re-adding the foreign keys.

It would be nice if the magento install script checked table type and refused install if the table was not set to InnoDB. Running it not as InnoDB is a terrible option.

Another example of what can go wrong without innodb

Lets assume someone is making an order. The order model is being saved, but there is an error in the save process. In InnoDB the changes are rolled back, so it is like nothing is saved. With MyIsam nothing is rolled back so you now have an order placed that should never of been placed.

There are countless other problems that can happen because MyIsam cannot rollback changes.

 
Magento Community Magento Community
Magento Community
Magento Community
 
xactrecords
Sr. Member
 
Total Posts:  80
Joined:  2008-09-14
 

So now I feel like I have 3 months worth of adding product and doing changes down the drain.  I really wish I could fix this.  Even when I add new products I can make them go into categories but I can’t remove them.

Whenever I use the product / category association section and hit “save” I get the following error;

Expected response code 200, got 404

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 5