Best way to perform application level high availability authentication

Apologies for the late reply, I forgot to subscribe to the thread and just thought no one replied.. lol.

If you just provide the app and not the hosting environment I personally would not bother with such a case. If there is no room in the budget to make a simple database replication or even a backup for that matter I would guess there is no budget in the project for such a function request (basic login / tickets via SQLite). If there is room in the budget I would advice to spend it on a cheap VPS for MySQL replication or something. You could then even use the 2nd db as a fall-back connection so customers can access everything, not just the login/ticket part.

I would use the database error page to inform the customers that it is currently offline and that they should call a number or send an email or something. Be sure to mention that it is a hosting related error and that they should call there hosting provider to have it fixed ;-)

But if you are really worried about MySQL going down by itself why not migrate the whole project to SQLite? That would reduce that single point of failure (although technically you would create another one).

This wont be on MY back end, im creating the application, and its for sysadmins to download/install on their own server. Yes, they should probably setup some sort of HA, but its also for small businesses, or even just individuals, so id like the application to do as much as it can. This wont be for the WHOLE app, just for the accounts.

If you're looking at doing this on the CI back-end, and that portion of the code is basically new code, I would recommend creating a sort of data abstraction layer (DAL) which simply maintains two database library configurations (mysqli and sqlite3, most likely) and writes all of the data to both, then reads from the first available. If one is unavailable during a write, you can cache the write(s) until it becomes available.

I wouldn't need ALL data to write to both, just the account data, such as username/email/password/status/account_id/role(s) (Basically, anything related to the login, nothing more). And the application wouldnt really write directly to the backup (sqlite) database, it would write to the primary (mysql) database, which would replicate the necessary data to the sqlite DB (probably via a cron job or something simple)

However, you probably won't be able to use auto-incremented keys in a setup like this (as they may become out of synch, especially if the connection to MySQL is unreliable). If you wanted to continue using simple integer keys (like most auto-incremented keys), you would probably have to create them in the DAL itself.

I dont see how this would be an issue. I wouldnt need auto-incremented keys in the backup database, as 100% of the data is replicated from the mysql accounts table, including the account ID/primary key. And when the mysql database is offline, CI will read from the sqlite database, not no writes will be done.

You will probably also have to make other concessions to what can be supported by both platforms at once, but this is much easier to do at the start of a project than as an afterthought.

Then you just use the DAL to perform all of your interaction with the database instead of calling $this->db directly, and you're in the clear. You may run into problems beyond those I've outlined above, but you only have to solve them in the DAL, not every model in your application, and you don't have to limit your users when MySQL isn't available.

Of course, if an existing DAL can be found which supports these features and can be adapted for use with CodeIgniter (or already works with CodeIgniter), that would be even better than rolling your own (and I'd certainly be interested in hearing about it, as I'd like to implement a DAL in Bonfire if I can get the right set of features).

Again, not sure any of the above is fully necessary, since this isn't going to be a round robin setup, more so just replicating the accounts login information, and anything needed for very basic actions (which right now, is planned to just be sending a ticket)

I believe that the problem you will end up having, if you were to fallback on sqlite, is that for most applications you have so many MySQL joins and complex queries that you'd end up with a website that doesn't work unless you were to replicate the entire database, not just the user authentication. So you just want to let users submit trouble tickets, but if the entire website is down then it's pretty obvious you're going to be busy getting it back up, and having users submitting trouble tickets is going to be a side issue in that case. I'd be more worried about getting the whole website up, not dealing with the less important trouble ticket.

The SQLite database wont look anything like the mysql database, no joins will be done,

---

Perhaps I wasn't completely clear about what my intention was.

My goal is not to have the database completely setup as HA, I don't plan on using SQLite to provide the same functionality as when the MySQL database is being used.

My goal is to have any authentication related data replicated to the SQLite database, and when CodeIgniter notices that the primary MySQL Database is offline, to switch authentication over to the SQLite database, and allow users who login to basically just submit a support ticket, view some logs, and any other basic diagnostic tasks (that don't require heavy SQL queries, or queries NOT related to the authentication)

I haven't tried it yet, but it seems like I can basically just setup SQLite as a backup database in the CodeIgniter database settings, then use the CI Hooks to somehow look at what database is being connected to, and if its the SQLite database, then do something such as restrict access to a controller that can be used for support. Should work right? Theres gotta be a way to check what database is being connected to.

Administrators are going to download and install the application on their own infrastructure, so I have no way of knowing how stable/unstable it may be, and instead of just showing something like "Oh No! Your database is down!" Or something generic, id like to still allow them to login and have access to some functionality that may help remedy the situation (diagnostics), or at least submit a support ticket from the applications interface.

My question was just to see if anyone either had a better solution for this, or to get some opinions from some experienced developers.

I hope that was a little more clear, thanks for the replies anyways though!