deletecontent command on a large topic + force_delete option can fail with "too many SQL variables" error

[jonboiser]

Originally posted by @radinamatic in #7669 (comment)

Observed behavior

On Windows 7 VM running Kolibri 0.14.4-rc1, if you attempt to delete the "Khan Academy > Arithmetic" topic and also select the "Delete everywhere" option on the modal. The task will eventually fail. The traceback indicates that it might be due to the "Delete everywhere" (aka force deletion) option, which causes the SQL query to become too large.

Traceback from logs:

ERROR 2020-11-17 14:15:33,731 kolibri.core.tasks.worker Job b1ee8580831c42f6b76b2f646fb3ed74 raised an exception: Traceback (most recent call last):
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\backends\sqlite3\base.py", line 328, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: too many SQL variables

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\python36\lib\site-packages\kolibri\core\tasks\worker.py", line 72, in handle_finished_future
    result = future.result()
  File "c:\python36\lib\concurrent\futures\_base.py", line 425, in result
    return self.__get_result()
  File "c:\python36\lib\concurrent\futures\_base.py", line 384, in __get_result
    raise self._exception
  File "c:\python36\lib\concurrent\futures\thread.py", line 56, in run
    result = self.fn(*self.args, **self.kwargs)
  File "c:\python36\lib\site-packages\kolibri\core\tasks\worker.py", line 217, in wrap
    return f(*args, **kwargs)
  File "c:\python36\lib\site-packages\kolibri\core\tasks\job.py", line 194, in y
    result = func(*args, **kwargs)
  File "c:\python36\lib\site-packages\kolibri\dist\django\core\management\__init__.py", line 131, in call_command
    return command.execute(*args, **defaults)
  File "c:\python36\lib\site-packages\kolibri\dist\django\core\management\base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "c:\python36\lib\site-packages\kolibri\core\tasks\management\commands\base.py", line 119, in handle
    return self.handle_async(*args, **options)
  File "c:\python36\lib\site-packages\kolibri\core\content\management\commands\deletecontent.py", line 123, in handle_async
    channel, node_ids, exclude_node_ids, force_delete
  File "c:\python36\lib\site-packages\kolibri\core\content\management\commands\deletecontent.py", line 49, in delete_metadata
    propagate_forced_localfile_removal(unused_files)
  File "c:\python36\lib\site-packages\kolibri\core\content\utils\annotation.py", line 666, in propagate_forced_localfile_removal
    ContentNode.objects.filter(files__in=files).update(available=False)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\models\query.py", line 650, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\models\sql\compiler.py", line 1204, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\models\sql\compiler.py", line 899, in execute_sql
    raise original_exception
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\models\sql\compiler.py", line 889, in execute_sql
    cursor.execute(sql, params)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "c:\python36\lib\site-packages\kolibri\dist\django\utils\six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "c:\python36\lib\site-packages\kolibri\dist\django\db\backends\sqlite3\base.py", line 328, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: too many SQL variables

Different versions of this scenario don't produce this result, namely

1. Deleting KA > Arithmetic without the "delete everywhere option"
2. Deleting the entire channel

Expected behavior

Ideally, no errors deleting large topics with the "delete everywhere" option

User-facing consequences

…

Errors and logs

daemon.txt
kolibri.txt

Steps to reproduce

…

Context

[nucleogenesis]

This "too many SQL variables" is dependent on the version and compilation flags used when creating the SQLite application.

Ultimately, the only solution in code would probably be to chunk queries - possibly by monkey patching "c:\python36\lib\site-packages\kolibri\dist\django\db\backends\sqlite3\base.py", line 328, in execute

[rtibbles]

We shouldn't go around monkey patching sqlite3 - we should just properly batch our queries. We do some similar things here: https://github.com/learningequality/kolibri/blob/release-v0.14.x/kolibri/core/content/utils/annotation.py#L183 it's just about propagating this across all the places we work with potentially large numbers of SQL parameters.

[iharshit009]

Hi @rtibbles, There are two methods to solve this issue.

1. By reducing the SQL host parameters.
   In SQLite, 1000 is a limit on no of variables per query exceeding this gives an operational error.
2. Deleting in batches

I will say 2nd works better as we could increase the limit on variables but that may arise major issues during the new installation or any other SQL operations.

P.S. I am new to this, please let me know if I am going on the correct path or not.

[rtibbles]

Yes - deleting in batches is the correct approach, the parameter limit is not changeable because it's a compile time flag on SQLite.

Because Django uses SQL parameters for every query it does (to prevent injection attacks) we can't do a generic approach to this, and it has to be done on a query by query basis to ensure that we are batching the correct parameters.

In this case, it is probably because localfiles_list parameter in

kolibri/kolibri/core/content/utils/annotation.py

Line 664 in 68f33c3

def propagate_forced_localfile_removal(localfiles_list):

is too long, so we should batch that into 250 at a time or so, and do the file updates over those batches.

[iharshit009]

Hi @rtibbles, Is there any other way to checkout/reproduce this issue error, or just as @jonboiser suggested?

[rtibbles]

Yes, I think this is the most reliable way to reproduce it.

[iharshit009]

I am getting an issue deleting the resources as suggested by @jonboiser above. The resource isn't deleted and I get a waiting status for a too long time.

Would like to know why is this happening and what can I do to make it work out. Due to this, I could not work on this issue too.
Where will I find the log file?

[rtibbles]

@iharshit009 it looks like the task runner is not running - what command did you use to start Kolibri?

[iharshit009]

I used kolibri manage --debug run server -- 0.0.0.0:5500, before that I generated static files using yarn run build. I used to try to use yarn run devserver, but that makes my system too slow.

[rtibbles]

Need to also run the task runner using the 'services' command https://github.com/learningequality/kolibri/blob/release-v0.14.x/package.json#L23