Convert WHMCS to use innodb / transactions

[ffeingol]

Hello,

WHMCS really needs to be converted to innodb and use transactions so it's ACID (http://en.wikipedia.org/wiki/ACID). On numerous occasions (for a lot of different reasons) WHMCS has 'blow up' and then the database is left in an inconsistent state. The easiest example is that the invoice generation process blew up. You then end up with an invoice without all the invoice lines or an invoice with the header and all the lines but the next due date incorrect. If WHMCS used innodb and transactions anything that 'blew up' would get backed out and your database would be consistent and you could correct the issue and then re-process.

Yes, I do understand that this would be a huge change to WHMCS but something as important as a billing system needs to use proper transaction management.

[ckh]

Do a google search for ibdata1 and you'll learn that innodb can be a pain to maintain. I vote keep it the same.

[mylove4life]

me tooo, it's no fun...

[ffeingol]

To be honest, I don't really care what storage engine is used, I really care about transactions (and innodb is the only one I know that supports transactions).

[mylove4life]

I'd like to port it to MS SQL, then we could do some cool stuff, never see it happing tho

[laszlof]

It sounds to me like a database server issue, and not something with WHMCS. Sure, a bandaid would be to enable innodb and transactions, but perhaps you should figure out what is causing your database to "blow up."

Are you using a dedicated server to host WHMCS? Is the database on its own box? Has mysql been tuned for the specific installation?

[ffeingol]

The last issue we had was a bug in WHMCS. Yes, we run on dedicated hardware. Even if it was a database or server issue, WHMCS should still be using transactions. Transactions insure that "something" is completely done or completely backed out. I don't want to sound arrogant / know it all but I have 10+ years as a DBA for a fortune 200 company running very large Sybase/Oracle databases. Any transcriptional application (which WHMCS is) should use database transactions.

When I refer to 'inconsistent' I mean logically inconsistent, not MySQL messed up. Things like invoice headers with no invoice lines (I'm not using the actual table names here).

[ditto]

Also innodb is the default engine in MySQL 5.5.x

[Matt]

The easiest example is that the invoice generation process blew up. You then end up with an invoice without all the invoice lines or an invoice with the header and all the lines but the next due date incorrect. If WHMCS used innodb and transactions anything that 'blew up' would get backed out and your database would be consistent and you could correct the issue and then re-process.

Certainly for the scenarios you describe here this really should not be necessary. WHMCS is designed in such a way that if a line item was to be created for a product, but the process ends before the invoice generates, then it would just generate the invoice on the next cron run. And similarly if the invoice didn't include all due products for a client, then the next cron run would create an invoice for any that remain due but not invoiced. So an invoice would never get "missed" due to something unexpected causing the invoice gen routine to terminate prematurely, and next due dates increment only after payment as opposed to invoicing.

Matt

[ffeingol]

Hello Matt,

You can look at support ticket 945646. Believe me that it did leave the database in an inconsistent state. Using database transactions just make sense in a transcriptional system like WHMCS.

[laszlof]

Hello Matt,

You can look at support ticket 945646. Believe me that it did leave the database in an inconsistent state. Using database transactions just make sense in a transcriptional system like WHMCS.

It really doesn't, and just adds headache's from a management standpoint. The way the queries are designed, a failure in a query will not cause inconsistent data. They do not need to be transactional and it just opens a whole new can of worms for the thousands of hosting companies using it.