InnoDB

[tmv2]

Hi,

Can you tell me why Open Dental is using MyISAM engine instead of InnoDB for MySQL.

Here are some of the the CONs for MyISAM:
* no transaction support
* Table-locking instead of row-locking
* No crash recovery
* No support for foreign key constraints

I'm more worried about MyISAM not supporting ACID. If there is a crash, power outtage, etc.. during a DB write, there is big chance for data corruption in the tables and MySQL will not be able to recover and you will have data inconsistency in the database.

Have anyone tried converting all the tables to using InnoDB?

thanks.

[jordansparks]

There was one user whose IT person switched them over to InnoDB. It's fairly simple to do. They went along smoothly for a while, but then ran into trouble after about a year. They probably could have stayed on InnoDB if their IT person was still involved and willing to do the management. But it fell to us to do the management, and we prefer to have everyone the same for ease of management. The reason I think why we switched them back was that we were trying to work with their database on our end. But we prefer working with MyISAM files rather than with the monolithic InnoDB file. And there was some other minor problem with the way it behaved that I can't quite remember.

On the flip side, I don't really see those benefits that you talk about. We don't make use of transactions, so ACID is not as relevant. There should not be a power outage if you have a UPS. In my mind, there are two types of corruption: bad foreign keys, and actual file corruption. MyISAM nearly always recovers beautifully from file corruption. And we have a database maintenance tool that we use to keep bad foreign keys under control. The foreign key problem would have happened even if we were using InnoDB. It's a very mild form of corruption that's easy to fix with scripts. MyISAM is also faster. Foreign key constraints sounds like a good idea, but so did stored procedures which turned out to be a disaster. Moving intelligence into the db level would be fairly time consuming. The one benefit I can think of is that external reporting tools could have more power.

But the best reason we don't do it is that supporting multiple setups would be really expensive for us. We already support Oracle, which I frequently wish we had not gotten into. I have no desire at all to start messing with InnoDB for marginal benefits. Perhaps when we have our web version fully functional and deployed in very large multi-office setups, it might be time to start moving to InnoDB.

[tmv2]

My main concern is that myISAM is nontransactional and that's even true for a SINGLE statement like insert, update, etc.. Just last week I witnessed a demo where a single update into a myISAM table was interrupted and the data that got inserted into the DB was not in the expected range. Using InnoDB, it's guaranteed that an update like this will either succeed or fail and there is no in between situation where random data gets into the database.

Jordan, when you said "We don't make use of transactions" do you mean there is no feature at all in Open Dental that does multiple table updates. I haven't looked at the application close enough, but I would imagine features like electronic filing or scheduling an appointment would require multiple updates to many tables. Isn't there some feature where you do multiple writes and need them to be wrapped under a transaction so that they either all succeed or fail?

It's true that MyISAM is faster and easier to maintain/backup than InnoDB since it's very simple in structure. However for production deployment, I need data consistency. It's pretty easy to convert existing tables to InnoDB, so I'm going to give it a try. Of course, I need to maintain them down the road and convert any new tables making their way in new updates as well.

thanks.

[jordansparks]

There are very few places where we update multiple tables. Since we know that we're not using transactions, we avoid this. It works great for us. But let's say you are making an appointment, and the query to create the appoinment succeeds, but the queries to attach the procedures fail. So what. Go back and attach the procedures. And if it fails in a more annoying way, like attaching the procedures to a phantom appointment, then our database maintenance tool would clean it up. Transactions won't prevent bugs. And so the database maintenance tool would still be needed anyway.

You won't need to do anything when we add tables. We add tables using queries and the queries are just as valid when using InnoDB. But if we ever need a copy of the database in order to analyze or fix something, our current policy is to only accept myISAM files. We might require you to convert to that format for submission. Just warning you. And our techs won't know what they're looking at during general support calls, which could be a little dangerous for you. Probably not huge issues, but something to consider.