How to reduce entries of ucs-school web import database

Knowledge Base Community
, , ,
1

How to reduce entries of ucs-school web import database

Environment

  1. UCS@school UMC module userimport
  2. Necessary if Module will not be loaded and a timeout (504) occurs
  3. Happened in UCS 4.4-5 ucsschool 4.4 v6 but could happen prior and later. Re-verified on UCS-5.0
  4. Please check status of bug 52173

Diagnostic

Howto verify the problem

Loading of the module takes a long time and ends with an internal server error.

Watch the gunicorn_error.log
tail -f /var/log/univention/ucs-school-import/gunicorn_error.log

during opening the module userimport. If there is still log activity after the 504 error raised the loading of the last import list takes to long.

Solution

Step 1: Background information

The module loads the last import information from the django database. You can dump the content by

root@dc0:~ # python3 -m ucsschool.http_api.manage dumpdata --indent 4 import_api.UserImportJob

To delete entries you can use ipython as follows.

Step 2: Start IPython

root@dc0:~ # python3 -m ucsschool.http_api.manage shell
Python 2.7.13 (default, Aug 22 2020, 10:03:02) 
[GCC 6.3.0 20170516] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>>

Step 3: Choose database

In [1]: from ucsschool.http_api.import_api.models import UserImportJob

Step 4: Get last entry number

In [2]: UserImportJob.objects.last()
Out[2]: <UserImportJob: UserImportJob 321 | dummy9999 (Finished)>

Your result will differ

Step 5: Entry count (optional)

In [3]: UserImportJob.objects.count()
Out[3]: 321

Your result (321) will differ.

Step 6: Delete entries:

You must adapt the number 280 to your environment

In [4]: UserImportJob.objects.filter(id__lt=280).delete()
Out[4]: (279, {u'import_api.UserImportJob': 279})

Step 7: Verify the result in the database (optional)

In [5]: UserImportJob.objects.count()
Out[5]: 42

You close the interactive python shell by pressing Ctrl-D

Step 8: Verify result in module

Now open the module userimport, it should be displayed again

Optional Step: Delete old import jobs

For a faster response and to fix a timeout issue of the import module, you could cleanup all older import jobs and just keep the last 3 of them, with the following python3 script.

clean_user_import_jobs.py (1.1 KB)

Example:

root@dc0:~/univention-support# python3 clean_user_import_jobs.py
Lösche Job: UserImportJob 6 | mejneschool2 (Finished)
Lösche Job: UserImportJob 5 | mejneschool2 (Finished)
Lösche Job: UserImportJob 4 | mejneschool2 (Aborted)
Lösche Job: UserImportJob 3 | mejneschool2 (Finished)
Lösche Job: UserImportJob 2 | mejneschool2 (Finished)
Lösche Job: UserImportJob 1 | mejneschool2 (Finished)
6 Jobs gelöscht, die letzten 3 Jobs werden behalten.

Source Code:

#!/usr/bin/env python3

import os
import sys
import django

# Set up the correct environment
sys.path.append('/usr/lib/python3/dist-packages')  # Überprüfe, ob dies benötigt wird
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'ucsschool.http_api.app.settings')

# Django initialisieren
django.setup()

# Importiere das Model nach dem Django-Setup
from ucsschool.http_api.import_api.models import UserImportJob

# Anzahl der zu behaltenden Jobs
jobs_to_keep = 3

# Alle Jobs nach Erstellungsdatum sortiert abrufen
all_jobs = UserImportJob.objects.all().order_by('-date_created')

# Jobs, die gelöscht werden sollen (alle außer den neuesten `jobs_to_keep` Jobs)
jobs_to_delete = all_jobs[jobs_to_keep:]

# Überprüfen, ob es Jobs zum Löschen gibt
if jobs_to_delete:
    for job in jobs_to_delete:
        try:
            print(f"Lösche Job: {job}")
            job.delete()
        except Exception as e:
            print(f"Fehler beim Löschen von Job {job}: {e}")

    print(f"{len(jobs_to_delete)} Jobs gelöscht, die letzten {jobs_to_keep} Jobs werden behalten.")
else:
    print(f"Keine Jobs zu löschen, die letzten {jobs_to_keep} Jobs werden behalten.")