Welcome Guest, Not a member yet? Register   Sign In
Best way to perform application level high availability authentication
#1

Im creating an app thats using CI for the back end. This question isnt specifically for CI, more for applications in general, but I figured this might be a good place to ask.

The app stores the accounts in a MySQL database that it uses for authentication. You can login to the app and submit support tickets, view logs, etc etc.

What I wanted to do, is allow people who have have the application installed, to be able to still login to their app and submit a ticket, even if the MySQL database isnt reachable (Say an update went bad, or a plugin broke something and the app can no longer connect to the database...).

What I was thinking, is in the PHP, whenever accounts are created/updated/deleted, just mimic the actions to a SQLite file. Then when the MySQL isnt reachable, fail over to the SQLite file for authentication. and in the PHP, just do some logic to see if the auth was done to the SQLite file, if so, only allow certain features.

What do you guys think about this idea? If you dont like it, is there a better idea?

Since im only providing the application, I have no way of making sure MySQL is HA or even backed up, but id like the customers to have the ability to atleast login and perform some basic actions, for "emergencies".
Reply
#2

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).
Reply
#3

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.

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.

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).
Reply
#4

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.
Reply
#5
Brick 
(This post was last modified: 09-19-2015, 05:01 PM by jLinux.)

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

(09-10-2015, 12:38 AM)Diederik Wrote: 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.

(09-10-2015, 07:05 AM)mwhitney Wrote: 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)

(09-10-2015, 07:05 AM)mwhitney Wrote: 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.

(09-10-2015, 07:05 AM)mwhitney Wrote: 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)

(09-10-2015, 08:29 PM)skunkbad Wrote: 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!
Reply
#6

I think the issue is that most of us just see something like this as an opening to receive an avalanche of support tickets telling you the system won't let them do what they logged in to do in the first place (for those with less of a grasp of what's going on) and telling you the MySQL database is down (for those with a better grasp).

I don't see any reason why you can't do what you're considering, though, depending on the nature of the loss of connectivity to the database, they could be waiting a while just to find out the majority of the system is unavailable. Something in the back of my head is nagging me about security concerns, but I don't have any specific suggestions other than making sure your data synchronization methods are secure. In fact, it should be fairly straightforward to just redirect someone to a specific page if they were authenticated via the backup database, once you've gotten over the hurdles of determining the MySQL database is unavailable, connected to the backup, and performed your authentication there.
Reply
#7

It might not be necessarily for tickets, but maybe just so if you're in a specific group (Admins, moderators, devs, etc), it would show some logs and/or diagnostic info, or any of the PHP errors that may be causing the issue.

Idk, I just thought it might be a useful feature.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB