Opened 8 years ago
Closed 20 months ago
#27397 closed Bug (fixed)
Querying with an integer larger than SQLite supports crashes with OverflowError
Reported by: | Ramin Farajpour Cami | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.10 |
Severity: | Normal | Keywords: | SQLite, Error Handling |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Hi,
i don't know, you accept this behavior Overflow on SQLite or there is max length here?
Step :
http://127.0.0.1:8000/admin/auth/user/1111111111111111111111/change/
Error :
OverflowError at /admin/auth/user/1111111111111111111111/change/ Python int too large to convert to SQLite INTEGER Request Method: GET Request URL: http://127.0.0.1:8000/admin/auth/user/1111111111111111111111/change/ Django Version: 1.10.2 Exception Type: OverflowError Exception Value: Python int too large to convert to SQLite INTEGER Exception Location: C:\Python27\lib\site-packages\django\db\backends\sqlite3\operations.py in _quote_params_for_last_executed_query, line 129 Python Executable: C:\Python27\python.exe Python Version: 2.7.11 Python Path: ['E:/Programmer Language/NodeJS/untitled1', 'C:\\Users\\RaminFP\\.IntelliJIdea14\\config\\plugins\\python\\helpers\\pydev', 'C:\\Python27\\lib\\site-packages\\six-1.10.0-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\pisa-3.0.33-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\sqlacodegen-1.1.6-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\inflect-0.2.5-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\0x10c_asm-0.0.2-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\jpype1-0.6.1-py2.7-win32.egg', 'C:\\Python27\\lib\\site-packages\\simpleaes-1.0-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\celery-3.1.23-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\kombu-3.0.35-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\anyjson-0.3.3-py2.7.egg', 'C:\\Python27\\lib\\site-packages\\pythonnet-2.1.0.dev1-py2.7-win32.egg', 'E:\\Programmer Language\\NodeJS\\untitled1', 'C:\\Windows\\SYSTEM32\\python27.zip', 'C:\\Python27\\DLLs', 'C:\\Python27\\lib', 'C:\\Python27\\lib\\plat-win', 'C:\\Python27\\lib\\lib-tk', 'C:\\Python27', 'C:\\Python27\\lib\\site-packages', 'c:\\python27\\lib\\site-packages'] Server time: Fri, 28 Oct 2016 11:43:05 +0000
Attachments (2)
Change History (37)
comment:1 by , 8 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:2 by , 8 years ago
Summary: | OverflowError at /admin/auth/user/<.id>/change → Querying with an integer larger than SQLite supports crashes with OverflowError |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:3 by , 8 years ago
Summary: | Querying with an integer larger than SQLite supports crashes with OverflowError → OverflowError at /admin/auth/user/<.id>/change |
---|---|
Triage Stage: | Accepted → Unreviewed |
I have reproduced this error. This is an error on SQLite side as the integer is too large to be handled by SQLite.
You will see this error as above when you search for User ID 1111111111111111111111
The max integer is 9223372036854775807. Using this will give you the following error:
Page not found (404)
Request Method: GET
Request URL: http://127.0.0.1:8000/admin/auth/user/9223372036854775807/change/
Raised by: django.contrib.admin.options.change_view
Out of interest, if you use the user ID 9223372036854775808, you will get the same error you saw.
Was not able to recreate the server crashing on my side. It held up fine and simply throws a Server Error when running under production (Debug = False).
comment:6 by , 8 years ago
comment:7 by , 8 years ago
Replying to nickparkin:
Looking into if this happens on Postgres now to see if Django is able to handle of these errors.
query can not check max length of valid integer? if check raise on excpetion
Page not found (404)
Request Method: GET
Request URL: http://127.0.0.1:8000/admin/auth/user/9223372036854775807/change/
Raised by: django.contrib.admin.options.change_view
check range int after execute query on SQLLite ,
comment:8 by , 8 years ago
Have confirmed this error by Django handled in Postgres: e.g. http://127.0.0.1:8000/admin/auth/user/922337203685477582000000000000000000/change/ This does not throw the same Overflow error and rather gives a 404 not found.
As such, keeping this open as this could be handled for SQLLite.
comment:9 by , 8 years ago
Keywords: | SQLite Error Handling added |
---|
comment:10 by , 8 years ago
Summary: | OverflowError at /admin/auth/user/<.id>/change → Querying with an integer larger than SQLite supports crashes with OverflowError |
---|---|
Triage Stage: | Unreviewed → Accepted |
i test on mysql with many id integer nothing of overflow
comment:11 by , 8 years ago
Owner: | changed from | to
---|
by , 8 years ago
Attachment: | excep.diff added |
---|
comment:14 by , 8 years ago
Assuming a 404 should be raised wherever an OverflowError
is raised doesn't seem correct. A fix at the query level seems more correct. Tests are also needed.
comment:15 by , 8 years ago
Which query change? (please give me python file this query)
max length range define on sqllite
wherever return OverflowError
,
comment:16 by , 8 years ago
For example, I was thinking that QuerySet.filter()
with a filter parameter value that overflows (and hence can't match anything) might return zero results.
comment:17 by , 8 years ago
i think you means this , this patch is a very easy Tim, and best way,
$ git diff HEAD diff --git a/django/contrib/admin/options.py b/django/contrib/admin/options.py index 499d27d..58049ed 100644 --- a/django/contrib/admin/options.py +++ b/django/contrib/admin/options.py @@ -668,7 +668,7 @@ class ModelAdmin(BaseModelAdmin): try: object_id = field.to_python(object_id) return queryset.get(**{field.name: object_id}) - except (model.DoesNotExist, ValidationError, ValueError): + except (model.DoesNotExist, ValidationError, ValueError, OverflowError): return None def get_changelist_form(self, request, **kwargs):
view :
Page not found (404) Request Method: GET Request URL: http://127.0.0.1:8000/admin/auth/user/1111111111111111111111111111/change/ Raised by: django.contrib.admin.options.change_view user object with primary key u'1111111111111111111111111111' does not exist.
Overflow Code ;
return queryset.get(**{field.name: object_id})
comment:18 by , 8 years ago
i'm missing for use which test folders tests ?
please give me sample python file tests object_id
, ready for new PR ,
Thanks,
Ramin
comment:19 by , 8 years ago
The issue affects all queries, not just those from the admin. A proper fix my go somewhere in the SQLite database backend (django/db/backends/sqlite3) but I'm not sure.
comment:20 by , 8 years ago
are you sure "The issue affects all queries, not just those from the admin"? where is issue? i think just happen object_id
change_view _ _
i test patch is working (http://127.0.0.1:8000/admin/auth/user/1111111111111111111111/change/),
i need more information a fix,what your means "A proper fix my go somewhere in the SQLite database backend (django/db/backends/sqlite3)"
comment:21 by , 8 years ago
still i'm not sure because i test http://127.0.0.1:8000/admin/auth/group/11111111111111111111111111111111111111111/change/ nothing error of OverflowError,
comment:23 by , 8 years ago
The idea is to fix things so that Question.objects.filter(id=10000000000000000000)
doesn't crash. Doing so should also fix the admin.
comment:24 by , 8 years ago
>CREATE TABLE numbers (i INTEGER, r REAL, t TEXT, b BLOB); >INSERT INTO numbers VALUES (9223372036854775807, 9223372036854775807, 9223372036854775807, 9223372036854775807); > SELECT * FROM numbers; i r t b -------------------- -------------------- -------------------- -------------------- 9223372036854775807 9.22337203685478e+18 9223372036854775807 9223372036854775807 > SELECT typeof(i),typeof(r),typeof(t),typeof(b) FROM numbers; typeof(i) typeof(r) typeof(t) typeof(b) ---------- ---------- ---------- ---------- integer real text integer > SELECT i+1,r+1,t+1,b+1 FROM numbers; i+1 r+1 t+1 b+1 -------------------- -------------------- -------------------- -------------------- -9223372036854775808 9.22337203685478e+18 -9223372036854775808 -9223372036854775808
you see shift +1 and -9223372036854775808
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
That is, you can only store values from 2**63
to (2**63-1)
Be careful when you need to store large numbers in SQLite. If you really need to support unsigned 64-bit numbers, ?? but it makes a SQLite bad choice for developing
please look :
https://github.com/python/cpython/blob/master/Modules/_sqlite/util.c#L129&L153
comment:25 by , 8 years ago
Tim, i see https://docs.djangoproject.com/en/dev/ref/models/fields/#bigintegerfield ,
do you think change fieldid
int
to biginteger
or BigAutoField
is good?
comment:26 by , 8 years ago
final patch
diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py index 66ad278..36c1351 100644 --- a/django/db/backends/sqlite3/base.py +++ b/django/db/backends/sqlite3/base.py @@ -325,8 +325,11 @@ class SQLiteCursorWrapper(Database.Cursor): if params is None: return Database.Cursor.execute(self, query) query = self.convert_query(query) - return Database.Cursor.execute(self, query, params) - + try: + return Database.Cursor.execute(self, query, params) + except OverflowError: + return None + def executemany(self, query, param_list): query = self.convert_query(query) return Database.Cursor.executemany(self, query, param_list) diff --git a/django/db/backends/sqlite3/operations.py b/django/db/backends/sqlite3/operations.py index 47a26b5..6404ea6 100644 --- a/django/db/backends/sqlite3/operations.py +++ b/django/db/backends/sqlite3/operations.py @@ -122,6 +122,8 @@ class DatabaseOperations(BaseDatabaseOperations): # Native sqlite3 cursors cannot be used as context managers. try: return cursor.execute(sql, params).fetchone() + except OverflowError: + return None finally: cursor.close()
usage :
>>> User.objects.get(id=1111111111111111111111111111111111111111111111111111111111111111111111111111111111) Traceback (most recent call last): File "<console>", line 1, in <module> File "C:\Python27\lib\site-packages\django\db\models\manager.py", line 85, in manager_method return getattr(self.get_queryset(), name)(*args, **kwargs) File "C:\Python27\lib\site-packages\django\db\models\query.py", line 385, in get self.model._meta.object_name DoesNotExist: User matching query does not exist. >>> User.objects.get(id=111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111) Traceback (most recent call last): File "<console>", line 1, in <module> File "C:\Python27\lib\site-packages\django\db\models\manager.py", line 85, in manager_method return getattr(self.get_queryset(), name)(*args, **kwargs) File "C:\Python27\lib\site-packages\django\db\models\query.py", line 385, in get self.model._meta.object_name DoesNotExist: User matching query does not exist. >>> User.objects.filter(id=1111111111111111111111111111111111111111111111111111111111111111111111111111111111) <QuerySet []> >>>
comment:27 by , 8 years ago
Has patch: | set |
---|
by , 8 years ago
Attachment: | Overflow_handel.diff added |
---|
comment:28 by , 8 years ago
this change is not important i will remove code,
diff --git a/django/contrib/admin/options.py b/django/contrib/admin/options.py index 499d27d..58049ed 100644 --- a/django/contrib/admin/options.py +++ b/django/contrib/admin/options.py @@ -668,7 +668,7 @@ class ModelAdmin(BaseModelAdmin): try: object_id = field.to_python(object_id) return queryset.get(**{field.name: object_id}) - except (model.DoesNotExist, ValidationError, ValueError): + except (model.DoesNotExist, ValidationError, ValueError, OverflowError): return None def get_changelist_form(self, request, **kwargs):
do you confirm fix and patch for PR?
https://github.com/django/django/blob/master/tests/queries/modles.py class Ticket27397(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=10) https://github.com/django/django/blob/master/tests/queries/tests.py def test_ticket27397(self): self.assertEqual(len(Ticket27397.objects.filter(id=111111111111111111111111111111111111)),0)
comment:29 by , 8 years ago
Needs tests: | set |
---|
comment:30 by , 8 years ago
I'm not sure if the fix is the cleanest one, but I'd have to look at the issue in more detail. I guess you can create a pull request at this point. You should be able to reuse an existing model for the test.
comment:31 by , 8 years ago
Needs tests: | unset |
---|---|
Patch needs improvement: | set |
As noted on the PR, the proposal doesn't work correctly for QuerySet.exclude()
.
comment:32 by , 4 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:33 by , 21 months ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:34 by , 21 months ago
Patch needs improvement: | unset |
---|
Submitted a patch that will effectively only address the SQLite issue reported here when a patch for #34370 lands.
comment:35 by , 20 months ago
Patch needs improvement: | set |
---|
comment:36 by , 20 months ago
Patch needs improvement: | unset |
---|
comment:37 by , 20 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
I'm not sure how to fix that, but it would be nice not to crash if possible.