InnoDB not supported by webhost. What now?

Question

I was developing a small WAMP web application on my laptop, where I have an instance of mySQL running and I chose InnoDB for my DB engine. After several weeks' development I wanted to make it available to the public and found out the database server provided by my web host does not support InnoDB, only MyISAM.

The create-and-populate script generated from the innoDB schema on my laptop, when executed against the live database, can manage to create individual TABLES but then runs into problems creating the VIEWs. Are views not supported in MyISAM? I know FOREIGN KEYs are not. That's very much why I made the choice of InnoDB... What are my chances of making my innoDB schema design work with myISAM?

Is there any straightforward way of converting the whole schema from one storage engine to the other? Should I look for another web host that does provide a mysql db that supports innoDB?

Answer 1

Your options:

1. Ask your web hosting provider if they can enable InnoDB on their servers (sometimes they will do it for you or move your account to a server that supports InnoDB).
2. Get a new web hosting provider with InnoDB support.
3. Take care of foreign key dependencies in PHP (for example you could use Zend_Db and Zend_Db_Table to work with the database, these classes can automatically take care of foreign key dependencies if you set them up correctly).
4. Convert your application to MyISAM tables.

Personally, I think number 4 is the worst solution. There are many web hosting providers that support InnoDB these days so it would be waste of your time to recode much of your application to work with MyISAM. This, of course, depends on how large and complex the application is. If it's relatively small, it might not be as bad idea.

I would probably look for a new web hosting provider.

Answer 2

it is not about InnoDB vs. MyISAM: Views are supported on both table types. i would think it is either a version issue (Views [...] are available in MySQL Server 5.0) or about the grant tables.

it would help if you would post the exact error messages you are getting.

Answer 3

Yes, but InnoDB has a number of other features like transactions and row-locking (rather than table locking).