1

I encountered a weird situation and I need your help. I am developing a Restful API using the Python 3.7 with Flask and SQLAlchemy. The application is hosted using AWS EC2 and database in AWS RDS (MySQL).

I also have an application hosted using Raspberry PI which will call the API and communicate with the EC2 Server.

Sometimes, I encountered a long transaction time between Raspberry and my API server, most of the time, I will kill the process in Raspberry PI and try to restart the process again and debug to see where goes wrong. However, when I restart the process I will see an error message related to my database. Then when I check my database, I notice all my tables are gone, nothing left. I am pretty sure that no drop tables in my codes and I have no idea why this occurred.

Is there anyone encountered the same situation? If yes, please tell me the root cause and the solution for this issue.

By the way, there is no error message recorded in MySQL log nor my RestAPI.

Thank you and good day.

steven.ang
  • 21
  • 2
  • "an error message related to my database" - can you [edit] the question to include this error message (and any other relevant error output)? – snakecharmerb Jul 03 '20 at 20:24
  • There is no error message in the database log nor my application. If yes, then I might be able to debug by myself. – steven.ang Jul 03 '20 at 20:45

1 Answers1

0

To my eye this looks like magic and there is to much guessing involved to point a finger properly.

But there is an easy workaround so it does not happen in the future. A good practice is to separate the admin user (can do anything, including schema migrations) from the connect user (can do insert, update, delete, select, but may not run any DDL scripts). Only the connect user may be used by the applications. In this case no table drop would be performed even if the application is running berserk.

Enabling logging might help too: How to log PostgreSQL queries?

Marek Puchalski
  • 3,286
  • 2
  • 26
  • 35
  • 1
    Exactly, it looks like magic for me too. I tried to check the logs in AWS RDS however it doesn't give me any clue on this issue. In my code, there is a part in my DAO that the session will be rollback if there is any error occurred. However, CREATE TABLE statement should not be affected and be undo by this rollback call. However, thanks for the advice. I will change the rights assigned to the EC2 and see if this can be prevent. – steven.ang Jul 03 '20 at 20:35