Re: [Django] #56: MySQL id columns should be UNSIGNED

Django

unread,

Aug 5, 2011, 8:30:27 PM8/5/11

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai | Owner:
<mail@…> | Status: new
Type: New | Component: Database layer
feature | (models, ORM)
Milestone: | Severity: Normal
Version: SVN | Keywords: mysql
Resolution: | Has patch: 1
Triage Stage: Accepted | Needs tests: 1
Needs documentation: 1 | Easy pickings: 0
Patch needs improvement: 1 |
UI/UX: 0 |
-------------------------------------+-------------------------------------
Changes (by zeraien):

* cc: zeraien (added)
* ui_ux: => 0
* easy: => 0

Comment:

+1

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:25>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Aug 9, 2011, 6:14:59 AM8/9/11

to django-...@googlegroups.com

Comment (by anonymous):

Replying to [comment:22 russellm]:
> However, talking this over with Malcolm some more, we think we've got a
way to do this. Lets add an 'unsigned=False' option to AutoField. By
default, it needs to be False for backwards compatibility; when 2.0 comes
around, we can change this to be unsigned by default.

This makes a lot of sence else there will be issues with developers
creating new models and finding all sorts of referencing issues. In the
next major release this can then be changed to unsigned by default and
will remove the need for the developer to set the flag by default.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:26>

Django

unread,

Feb 25, 2012, 2:48:56 PM2/25/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------

* needs_better_patch: 1 => 0
* stage: Accepted => Design decision needed

Comment:

Submitted patch with unsigned option for AutoField.
Patch includes rel_db_type_cleanup.diff from #13774

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:27>

Django

unread,

Feb 25, 2012, 2:50:19 PM2/25/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: SVN
(models, ORM) | Resolution:

Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 0
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by pzinovkin):

* stage: Design decision needed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:28>

Django

unread,

May 7, 2012, 2:20:41 PM5/7/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new

Component: Database layer | Version: master

Comment (by pzinovkin):

Made pull request https://github.com/django/django/pull/49

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:29>

Django

unread,

May 13, 2012, 8:04:55 AM5/13/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted

Keywords: mysql | Needs documentation: 0

Has patch: 1 | Patch needs improvement: 0
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by pzinovkin):

* needs_docs: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:30>

Django

unread,

Jul 16, 2012, 12:37:45 PM7/16/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by akaariai):

To me the alternate approach seems fine. It does need some tests though,
mainly to make sure the related field type matching works properly.

The alternate approach doesn't directly resolve this ticket's issue
though. Unsigned is different than bigint. I wonder if adding also
unsigned flag should be done?

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:31>

Django

unread,

Aug 26, 2012, 3:14:03 PM8/26/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by pzinovkin):

https://github.com/django/django/pull/308

There is no need to use unsigned field to increase id-space. It will only
introduce inconsistency between database engines.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:32>

Django

unread,

Aug 26, 2012, 3:14:41 PM8/26/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0

Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by pzinovkin):

* needs_tests: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:33>

Django

unread,

Oct 15, 2012, 3:10:17 PM10/15/12

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by tim@…):

I disagree with pzinovkin. Using unsigned integers basically doubles the
amount of id's that can be used. That has benefits no matter which integer
type is used. But in the case of an INT, 2 billion id's versus 4 billion
for the same cost is substantial. Different RDBMS' are not built the same
so trying to treat them all the same is a potential recipe for poor
scalability.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:34>

Django

unread,

May 19, 2013, 5:13:26 AM5/19/13

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------

Reporter: Manuzhai <mail@…> | Owner: gcc
Type: New feature | Status: assigned

Changes (by gcc):

* owner: => gcc
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:35>

Django

unread,

May 19, 2013, 7:06:52 AM5/19/13

to django-...@googlegroups.com

#56: MySQL id columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:

Type: New feature | Status: new

* owner: gcc =>
* status: assigned => new

Comment:

Created a [https://github.com/django/django/pull/1156 pull request] with
pzinovkin's patch and tests.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:36>

Django

unread,

May 19, 2013, 9:36:26 AM5/19/13

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED

-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:37>

Django

unread,

Apr 3, 2014, 6:03:29 PM4/3/14

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by kbcmdba@…):

MySQL 5.5.29 and up (plus other versions in other releases of MySQL) now
require AUTO_INCREMENT columns to be UNSIGNED. The return value of
LAST_INSERT_ID() had to change to BIGINT UNSIGNED because users who had
BIGINT UNSIGNED column types were unable to get the ID value when the ID
was greater than 9,223,372,036,554,775,807 (BIGINT MAX / 2 - 1 or about 9
quintillion) but still less than the highest possible BIGINT VALUE
(18,446,744,023,709,551,615 or about 18 quintillion).

It no longer makes sense to allow negative AUTO_INCREMENT values by
default in MySQL persistence layers. I strongly recommend changing the
default data type on auto_increment columns to explicitly become UNSIGNED,
especially when the persistence layer is known to be MySQL.

Here's the specific MySQL 5.5.29 release note:

* Incompatible Change: LAST_INSERT_ID(expr) did not work for expr values
greater than the largest signed BIGINT value. Such arguments now are
accepted, with some consequences for compatibility with previous versions:
LAST_INSERT_ID() now returns a BIGINT UNSIGNED value, not a BIGINT
(signed) value.
LAST_INSERT_ID(expr) now returns an unsigned integer value, not a
signed integer value.
'''For AUTO_INCREMENT columns, negative values are no longer
supported.'''
(Bug #20964, Bug #11745891)

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:38>

Django

unread,

Jul 8, 2014, 12:32:07 PM7/8/14

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql | Needs documentation: 0

Has patch: 1 | Patch needs improvement: 1

Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by timo):

* needs_better_patch: 0 => 1

Comment:

The `DatabaseCreation` classes are now deprecated, so the patch will need
to be refreshed to use `SchemaEditor` instead.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:39>

Django

unread,

Jul 27, 2014, 5:21:42 AM7/27/14

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by christopherdcunha):

* status: new => assigned

* owner: => christopherdcunha

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:40>

Django

unread,

Feb 5, 2015, 4:51:35 PM2/5/15

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:

| christopherdcunha

Type: New feature | Status: assigned

Component: Database layer | Version: master
(models, ORM) |

Changes (by collinanderson):

* cc: cmawebsite@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:41>

Django

unread,

Jun 6, 2015, 7:12:28 PM6/6/15

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:

| giuliettamasina

Changes (by giuliettamasina):

* owner: christopherdcunha => giuliettamasina
* cc: markus.magnuson@… (added)

Comment:

I've rewritten pzinovkin's patch based on the
[https://github.com/django/django/pull/1156 previous pull request] from
gcc:

https://github.com/alimony/django/compare/master...alimony:unsigned-
primary-keys

Did not open a pull request yet as it's not ready, most importantly the
tests are failing:

As far as I can see, the new `test_can_use_large_primary_keys` test can
never have passed on postgres. There is no concept of unsigned in
postgres, meaning a primary key bigger than 2147483648 only fits in a
bigint/bigserial. The latter is used for the `AutoField` itself in this
patch, but as someone pointed out, having a foreign key reference such
large primary key fails.

The updated `AutoField` in the patch
[https://github.com/alimony/django/compare/master...alimony:unsigned-
primary-keys#diff-bf776a3b8e5dbfac2432015825ef8afeR953 determines the
field type] of a foreign key that references itself like this:

{{{
#!python
def rel_db_type(self, connection):
db_type = 'PositiveIntegerField' if self.unsigned else 'IntegerField'
return self._internal_to_db_type(db_type, connection)
}}}

But both `PositiveIntegerField` and `IntegerField` resolve to `integer` in
the case of postgres.

Some ways it can ever do anything else, that I can think of now, are:

1. Set `related_fields_match_type` to `True` for postgres.
2. Make `PositiveIntegerField` mean a `bigint` for postgres.
3. Make the `rel_db_type` method of the new `AutoField` do some special
treatment to return e.g. `BigIntegerField` for postgres if `self.unsigned`
is true.

(The first two are of course out of the question.)

I want to write proper tests for this patch to make sure everything
behaves on every database, but I guess there needs to be some sort of
consensus first on how to solve the above. And people with far more
knowledge on Django than me will have to reach that consensus :)

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:42>

Django

unread,

Apr 3, 2016, 5:46:33 AM4/3/16

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner:

Type: New feature | Status: new

* owner: giuliettamasina =>

* status: assigned => new

Comment:

I'm not actively working on this, feel free to pick up where I left off,
anyone.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:43>

Django

unread,

Mar 15, 2017, 7:56:05 PM3/15/17

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------

Reporter: Manuzhai <mail@…> | Owner: (none)

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Collin Anderson):

* needs_better_patch: 1 => 0

Comment:

https://github.com/django/django/pull/8183

I just ran into a situation where I have a legacy mysql table that has a
`bigint(20) unsigned auto_increment` primary key that I want to point a
new `ForeignKey` to. Django is creating the foreign key as `bigint(20)`
rather than `bigint(20) unsigned`, and mysql won't allow a foreign key
constraint between the two.

So basically, I need the legacy table primary key to be a
`PositiveBigAutoField`, and the `ForeignKey` needs to be a
`PositiveBigIntegerField`.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:44>

Django

unread,

Apr 2, 2017, 4:20:22 PM4/2/17

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:45>

Django

unread,

Apr 8, 2017, 2:51:26 PM4/8/17

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Collin Anderson):

* needs_better_patch: 1 => 0

Comment:

Patch should address the latest feedback.

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:46>

Django

unread,

Jun 29, 2017, 9:38:15 PM6/29/17

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:47>

Django

unread,

Jun 4, 2019, 9:23:32 AM6/4/19

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------

Reporter: Manuzhai <mail@…> | Owner: Caio
| Ariede

Type: New feature | Status: assigned

Changes (by Caio Ariede):

* owner: (none) => Caio Ariede

* status: new => assigned

Comment:

I'll try to revive this ticket

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:48>

Django

unread,

Oct 10, 2019, 8:56:00 AM10/10/19

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner: Caio
| Ariede
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Caio Ariede):

PR [https://github.com/django/django/pull/11900]

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:49>

Django

unread,

Apr 7, 2021, 4:10:53 AM4/7/21

to django-...@googlegroups.com

#56: Primary key columns should be UNSIGNED
-------------------------------------+-------------------------------------
Reporter: Manuzhai <mail@…> | Owner: Caio
| Ariede

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: assigned => closed

* needs_better_patch: 1 => 0

* resolution: => wontfix
* stage: Accepted => Unreviewed

Comment:

I don't think it's necessary anymore to change the default behavior. We
have `PositiveBigIntegerField` in Django 3.1 (#30987) and a way to change
the default `AutoField` in Django 3.2+ (#31007). As a workaround folks can
use `DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField'` or an explicit
primary key `id = models.PositiveBigIntegerField(primary_key=True)`, see
[https://groups.google.com/g/django-developers/c/VFXZpHnuEJc the mailing
list discussion].

--
Ticket URL: <https://code.djangoproject.com/ticket/56#comment:50>

Reply all

Reply to author

Forward