MYSQL 5.1/InnoDB general question

Data reliability issue

[RandyPatterson]

I am needing a web based transactional database so my obvious choose was MySQL InnoDB. The company that I have my resellers hosting account with only supports MySQL 5.0.81. Upon submitting a support ticket regarding upgrading to 5.1 and allowing the InnoDB tables, I received the following reply;

<quote>
#*$!#*$! has tested InnoDB in the past but does not support it because it creates a serious risk to our clients data. InnoDB uses log files on the server to maintain everything. As a result if there is a file system error, you could potentially loose your latest changes to your database. More importantly if a database is corrupt, their is no way to repair it (at least from what we've seen). You have to roll back to any backups you have and would lose your data.

Additionally, our servers use at highest 5.0.81 at this time for mysql as 5.1 is not yet recommended with CPanel.
</quote>

Is there validity in this opinion?

Thanks,
Randy Patterson

[lammert]

First of all welcome to WebmasterWorld!

The InnoDB storage engine is supported in MySQL since version 3.23 and was switched on by default since version 4.0. I have no idea why you are not able to use it with your current configuration. The hosting provider probably has switched it of deliberately. If you have access to the MySQL database with an interface like MyPhpAdmin, you may be able to set the InnoDB storage engine for individual tables. I don't know if this is possible with other front-ends (it should be).

InnoDB offers the main functionality for data integrity like transactions and foreign keys, which lack in the default MyISAM engine.

The problem with the log files is not an InnoDB problem, but a general problem. If the file system gets corrupt you will loose any database, including Oracle, MS-SQL and others. It would be great news to hear when database software exists which could survive file system corruption or disk crashes. I would be the first to use it :) Regular backups--or a system for syncing data with another server--are always needed.

[webdoctor]

does not support it because ...

InnoDB has significantly higher resource requirements - perhaps the ISP would prefer you to use MyISAM instead because it means less load on their servers = they can put more clients on the same hardware?

Just a thought...

[RandyPatterson]

>> First of all welcome to WebmasterWorld!

Thanks

>> If you have access to the MySQL database
>> with an interface like MyPhpAdmin, you may be
>> able to set the InnoDB storage engine for individual >> >> tables.

They are using MyPhpAdmin within CPanel. I have several options when creating the tables but InnoDB is not one of them. :-( I guess my only option is to find another host provider for this project. Up to now my database experience has been with somewhat simple databases so MyISAM has always been enough.

Thanks for you input!
Randy Patterson