We have a custom-built CRM system that's deployed on AWS RDS. Our FE and BE systems are JavaScript, and the database prohibits the JS devs from directly manipulating the database. They must go through predefined APIs or a controlled query system. It works beautifully and the devs have never managed to hurt the database :)
UPDATE: The controlled query system enforces JSON constructs as the only method the front-end and back-end systems have to update, insert and delete records. The Database monitors the JSON constructs and processes them accordingly, which includes stripping and denying data and requests that don't meet specific guidelines.
The Database has its own internal error reporting system that has worked very well, mainly because the JS devs often don't report errors from stored procedure calls that fail (grr). So the Database reports these errors in a tracking table. That way we can see what bad payloads the JS devs are sending or catch the occasional error in our SQL.
UPDATE: The internal error reporting system is exactly that; any time a stored procedure triggers an unhandled error, it writes the error out to an error table that is monitors by the Database team. It exists at the core database level and has nothing to do with the query control system.
The problem is we've deployed a Read Replica of our database, and now the internal error reporting system is rendered useless on RR calls that fail.
Is there a technique or method where the RR can somehow send write-out calls to the master? Or do I have to switch over to a log file system? If I have to do that, it pretty much requires us to abandon our master error table tracking...
So I guess the core question is: What internal MySQL error reporting system and technique should we consider for an enterprise-scale CRM database that utilizes Read Replicas?
Thanks!
The RDS read replica cannot send a query to the source instance.
What internal MySQL error reporting system and technique should we consider for an enterprise-scale CRM database that utilizes Read Replicas?
Detect and report invalid payloads in the back-end code that processes requests, not in stored procedures.
Your code can log the bad requests to whichever instance you want. I mean, you could log an error to the source instance even though the request (once validated) would have resulted in executing a stored procedure on the read replica instance.
But I would recommend logging bad requests to some other logging system, not to the database. The bad requests do not relate to any other data in the database, and it just adds more storage load and query load to be logging bad requests in the database.
AWS currently provides a service called Centralized Logging with OpenSearch. This sounds like a likely candidate to which to send reports of bad requests, but I have not used this AWS service so I can't report any experience with it. It's just an example of the kind of thing I'm talking about.
Logging systems are generally simpler and less expensive than using a full-blown RDBMS for that task.