Not able to connect with Remote DB

[amanattrish]

Bug Report

Stuck in infinite loop while running migrations command

I'm trying to use MySQL over AWS RDS. So I removed the DATABASE_URL and added the NAME, HOST, USERNAME, PASSWORD and PORT in horilla/settings.py file. Then while running migration command python3 manage.py migrations I get following

Steps to Reproduce

1. In docker-compose.yml file, comment out the pgsql part and dependency of server in pgsql
2. Add mysqlclient as given in database setup docs - https://www.horilla.com/docs/database-setup/
3. run the docker compose up command

Expected Behavior

It should run without any problem

Actual Behavior

Getting Execution of job "block_unblock_disciplinary (trigger: interval[0:00:10], next run at: 2024-08-25 17:35:45 IST)" skipped: maximum number of running instances reached (1)

Screenshots

[If applicable, provide screenshots illustrating the issue.]

Environment

• Django Version: [Django version you're using, e.g., 3.2.1]
• Python Version: [Python version, e.g., 3.9]
• Operating System: [Your OS, e.g., Windows 10, Ubuntu 20.04]
• Browser: [If applicable, specify the browser and version you're using.]

Additional Information

[Any other relevant information, logs, error messages, or context that might help in understanding and fixing the issue.]

Possible Solution

[If you have any ideas or suggestions on how to fix the issue, feel free to provide them here. This is optional.]

Labels

[If your project uses labels, suggest any labels that might apply to this issue, such as 'bug', 'needs investigation', etc.]

Priority

[Specify the priority level for this issue, such as 'high', 'medium', 'low', etc.]

Assignees

[If you want to suggest an assignee or tag a specific person to look into this issue, mention their GitHub username here.]

Related Issues

[If there are any related issues or pull requests, mention them here. This is optional.]

Note: Remember to search through existing issues before submitting a new one to ensure that the issue hasn't been reported already. Provide as much information as possible to help the maintainers understand and address the problem effectively.

[amanattrish]

If we run it long enough, it throws following error

Job "block_unblock_disciplinary (trigger: interval[0:00:10], next run at: 2024-08-25 18:06:48 IST)" raised an exception django.db.utils.ProgrammingError: (1146, "Table 'horilla.employee_disciplinaryaction' doesn't exist")

[amanattrish]

Update:

The same problem is happening when I try to connect with PgSQL. But when I try to connect spin a pgsql docker container using docker-compose, it works fine.

[amanattrish]

Update:

It all seems like db connection error, I tried same credentials in Dbeaver, TablePlus, Terminal and other connectivity softwares, connection to database is fine, its just horilla code does not connect.

Is there a way I can test connection from code?

[horilla-opensource]

Hi @amanattrish , For time being the below warning can be neglected. Execution of job "block_unblock_disciplinary (trigger: interval[0:00:10], next run at: 2024-08-25 17:35:45 IST)" skipped: maximum number of running instances reached (1) It's coming from an scheduler job which is running in the background. Once we comment out the scheduler, that warning is missed. But the connection to RDS is not getting executed is what I think the issue in your case. Can you test whether the connection to RDS is not being blocked? Can you confirm with the security group if the requests from your server IP is accepted ?

With Regards, Team Horilla

[amanattrish]

Hi @horilla-opensource

Thanks for reply. Yes it is Db connection issue.

I tested the connection using 1. command line, 2. Different Django project, 3. Dbeaver (a DB tool) and 4. TablePlus (a DB tool). The connection to AWS RDS PostgreSQL as well as AWS RDS MySQL are completely fine.

Moreover, I see the localhost db is also connecting without any problem. But its just remote DB which is unable to connect.

Local DB configs

DATABASES = {
    "default": {
        "ENGINE": 'django.db.backends.postgresql',
        "NAME": 'my_db_name',
        "USER": 'my_user',
        "PASSWORD": 'my_password',
        "HOST": 'localhost',
        "PORT": '5432',
    }
}

Remote DB configs

DATABASES = {
    "default": {
        "ENGINE": 'django.db.backends.postgresql',
        "NAME": 'my_db_name',
        "USER": 'my_user',
        "PASSWORD": 'my_password',
        "HOST": 'xxxxx.xxxxxx.us-east-2.rds.amazonaws.com',
        "PORT": '5432',
    }
}

[amanattrish]

I even tried django.db.backends.postgresql_psycopg2

as mentioned in here - https://stackoverflow.com/questions/57984929/django-app-is-not-connecting-with-remote-database.

For your kind information, I'm using Macbook air with M2 chip.

[horilla-opensource]

@BhuviTheDataGuy Do you have any suggestions on what can be the issue causing this to fail ? Any kind of help or suggestion is much appreciated.

With Regards, Team Horilla

[amanattrish]

Any updates @horilla-opensource @BhuviTheDataGuy?

[BhuviTheDataGuy]

@amanattrish

The error you mentioned in the previous comment is

django.db.utils.ProgrammingError: (1146, "Table 'horilla.employee_disciplinaryaction' doesn't exist")

Is it coming from the RDS database(your remote DB)?

If yes, then I don't think it is a connection error, After connecting to the database, it was not able to find the employee_disciplinaryaction table. Here the database connection was fine.

Could you please help me to understand this, The connection to AWS RDS PostgreSQL as well as AWS RDS MySQL are completely fine, But its just remote DB which is unable to connect. -- Are you referring these AWS RDS PostgreSQL/MySQL as the remote DB or something else?

What else you are seeing in the error log related to database?

[amanattrish]

Hi @BhuviTheDataGuy Thanks for your reply.

1. The error came because migrations did not run at all and this error comes from disciplinary-action job. I suppose it is responsibility of migrations to create all those tables in DB. Strange part is, in local DB these errors don't come.

   django.db.utils.ProgrammingError: (1146, "Table 'horilla.employee_disciplinaryaction' doesn't exist")

2. Yes I'm referring these AWS RDS PostgreSQL/MySQL as the remote DB. If you want I can share the PgSQL creds of a public DB I have so that you can also test from your end.

[BhuviTheDataGuy]

Thanks for the clarifications.

Are you able to see anything related to DB connection error in the log file? That would help to identify the root cause of this issue.

If possible, could you please create the container again and share the compose log file.

[amanattrish]

Hi @BhuviTheDataGuy

Its this

Terminal Screenshot

Docker desktop screenshot

logs.txt

The job is not able to run because of connection I guess, if I comment out the job code, it does not do anything.

[horilla-opensource]

Hi @amanattrish , To avoid the warning , please comment out the below line in employee module https://github.com/horilla-opensource/horilla/blob/6f6a19e0ba59821f2acf6ae58c98f4190a3de108/employee/scheduler.py#L137 and try again.

[amanattrish]

its just stuck here

For your kind information, Running makemigrations... is the log I added inentrypoint.sh file

[amanattrish]

@horilla-opensource

Can somebody from your team try to connect to PgSQL or MySQL in RDS? I have asked my friend to do the same, it is a problem in his machine as well.

CC: @BhuviTheDataGuy

[amanattrish]

Any updates @BhuviTheDataGuy @horilla-opensource ?

[horilla-opensource]

Hi @amanattrish , We were able to reproduce the issue at our end and the team is working on it . Will provide you an update once we were able to find a solution for it.

With Regards, Team Horilla

[amanattrish]

Thanks @horilla-opensource for the updates.

[amanattrish]

@horilla-opensource I know the team might be busy, just wanted to know if there is any time expectation to have it resolved?

[horilla-opensource]

Hi @amanattrish , We get your concern on the delay. The team is working on the issue and will be able to provide you the solution mostly by EOD tomorrow.

With Regards, Team Horilla

[horilla-opensource]

Hi @amanattrish , We apologize for the delay being caused. We still find some difficulty in identifying the issue with the RDS connection. We'll update with once we have the solution or walk around for the same.

With Regards, Team Horilla

[amanattrish]

No problem @horilla-opensource

Thanks for update. I really appreciate it.

[amanattrish]

Hi @horilla-opensource

Any luck in finding out a resolution or walk around for rds db connection?

[horilla-opensource]

Hi @amanattrish , Can you implement the system currently with a on premise database till we find a solution to this? Once fixed, we can take the backup from the local and update it to the RDS.

[amanattrish]

Sure Thanks

[centaurusgod]

Hi @BhuviTheDataGuy

Its this

Terminal Screenshot

Docker desktop screenshot

logs.txt

The job is not able to run because of connection I guess, if I comment out the job code, it does not do anything.

Don't know if this information will help but.. I had encountered the exact same issue (block_unblock_disciplinary) under these two conditions a. Using MySQL server (Fixed by switching to MariaDB) b. Nginx and Gunicorn were running in the background while I was trying to access the Django server through the public IP. python manage.py runserver 0.0.0.0:8000 (No errors after stopping those)

This issue was absent when running Django in local server and only occurred after I migrated the application to Lightsail instance.

[horilla-opensource]

Hi @amanattrish , Were you test the method suggested by @centaurusgod ?

With Regards, Team Horilla

[amanattrish]

Hi @horilla-opensource

1. We have MySQL and PgSQL instances. Using MariaDB just for 1 more thing may not be best solution for us.
2. We don' have Nginx and Gunicorn running in background. Unlike @centaurusgod we are getting error in local server and AWS ECS both.

There is one thing I wanted to point out, if I create new Django project and use the same DB creds (be it MySQL, PgSQL), the db connection works perfectly fine be it local server or AWS ECS.

[amanattrish]

Hi @horilla-opensource

Any luck in finding resolution?

[horilla-opensource]

Hi @amanattrish , We are not able to correctly identify what's causing the connection problem. The connection was getting refused all the time, and eventually the ip getting blocked by AWS after regular tries (both in local and aws). We have allocated a separate team for looking into this. They are working on this now. Apologize for the delay.

With Regards, Team Horilla

[amanattrish]

Thanks for update. Really appreciate it.