sqlite3.OperationalError: too many SQL variables during bulk_create_with_history

[ntouran]

Describe the bug Running bulk_create_with_history with just ~~200~~ 50 objects causes the following exception when running on a sqlite3 backend.

This is more problematic for Sqlite3 < 3.32.0 (https://www.sqlite.org/limits.html) where the limit is less, but may still be a problem for larger bulk creations on later versions.

Traceback (most recent call last):
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 423, 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 "/srv/atom/live/documents/tests/test_doc_section_import.py", line 35, in test_import_doc_sections
    make_sections_from_xlsx(
  File "/srv/atom/live/documents/utils/make_sections_from_xlsx.py", line 89, in make_sections_from_xlsx
    docs = bulk_create_with_history(
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/simple_history/utils.py", line 132, in bulk_create_with_history
    list(
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/models/query.py", line 262, in __len__
    self._fetch_all()
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/models/query.py", line 1324, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/models/query.py", line 51, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/srv/venvs/atom39/lib64/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 423, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: too many SQL variables

To Reproduce Steps to reproduce the behavior:

1. Install sqlite3 < 3.32.0
2. Bulk create 50 objects that have ~10 fields/columns each
3. See error

Expected behavior Bulk create should work in this scenario without failing.

Screenshots If applicable, add screenshots to help explain your problem.

Environment (please complete the following information):

• OS: RHEL 8
• Browser (if applicable): N/A
• Django Simple History Version: 3.3.0
• Django Version: 3.2
• Database Version: Sqlite3 3.26.0

Additional context This problem started after upgrading to 3.3.0 from 3.1.0. It is coming from this commit https://github.com/jazzband/django-simple-history/commit/6325659c950c201f05d72aece06a83d8a47b2771 from https://github.com/jazzband/django-simple-history/pull/975, specifically by building up a very large SQL query while building the obj_when_list:

https://github.com/jazzband/django-simple-history/blob/a768673e0dc5e34e1a81481b0a0d3bfe09a38f8a/simple_history/utils.py#L132-L135

Through the debugger, I printed out that list and it has N When clauses (where N is 200 for creating 200 objects, each of which has M SQL commands where M is the number of fields in the model. This ends up making a SQL command that is N*M long, which seems pretty big. Maybe too big since users may want to bulk_create millions of rows at once, and this will eventually cause issues for any database backend.

@twolfson can you take a look at this by chance? Looks to me like having multiple SQL queries would be preferable to having fewer but unbounded large ones.

[twolfson]

I sadly don't have a lot of time to look into this =/ Feel free to rollback the problematic code, though I feel like the n+1 performance was pretty egregious ._.

Maybe there's some compromise where it only disables for sqlite3? Or uses some batched queries based on maximum statement length?

[amy-mac]

I am also running into this exact same issue with mssql. Same line is causing the problem, and it's because of the 2100 limit.

[muneeb706]

I was able to reproduce this issue with sqlite3 version 3.43.2. I tried creating 990 objects of model with 12 fields with bulk_create_with_history method and got django.db.utils.OperationalError: Expression tree is too large (maximum depth 1000)

Reason By default, SQLite allows a maximum of 999 variables in a query for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0. It's mentioned in Maximum Number Of Host Parameters In a Single SQL Statement section of https://www.sqlite.org/limits.html#max_variable_number.

Possible Solution Split the bulk_create_with_history operation into smaller chunks. e-g

chunk_size = 100
for i in range(0, len(model_objects), chunk_size):
    bulk_create_with_history(model_objects[i:i+chunk_size], ModelName)

[caot]

It's possible to fix the issue through upgrading sqlite >= 3.34. I ran into the same issue with sqlite 3.26 in Redhat 7, however, the issue is gone with sqlite 3.34.1-7.el9.x86_64