Re: MySQL accepts 0 for a primary key but Django does not

Hello Derek,

Not sure of your custom module structure but you'll want to point ENGINE to 'custom'

and not 'custom.base' just like you were pointing to 'django.db.backends.mysql' and

not 'django.db.backends.mysql.base'.

Cheers,

Simon

Le dimanche 30 décembre 2018 04:01:59 UTC-5, Derek a écrit :

Hi Simon

Much appreciated.

However, I am obviously doing something wrong.  If I save that code into a file called "base.py" stored in my 'custom' app, and then change my settings file to have:

DATABASES = {

    'default': {

        'ENGINE': 'custom.base',  # WAS: 'django.db.backends.mysql',

        'NAME': DB_NAME,

        'USER': DB_USER,

        'PASSWORD': DB_PWORD,

        'HOST': '',

        'PORT': '',

    }

}

Then I get this error on startup:

Traceback (most recent call last):

  File "manage.py", line 14, in <module>

    execute_from_command_line(sys.argv)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 399, in execute_from_command_line

    utility.execute()

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 392, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/core/management/base.py", line 242, in run_from_argv

    self.execute(*args, **options.__dict__)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/core/management/base.py", line 280, in execute

    translation.activate('en-us')

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/utils/translation/__init__.py", line 130, in activate

    return _trans.activate(language)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/utils/translation/trans_real.py", line 188, in activate

    _active.value = translation(language)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/utils/translation/trans_real.py", line 177, in translation

    default_translation = _fetch(settings.LANGUAGE_CODE)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/utils/translation/trans_real.py", line 159, in _fetch

    app = import_module(appname)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/utils/importlib.py", line 40, in import_module

    __import__(name)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/massadmin/__init__.py", line 28, in <module>

    from django.contrib import admin

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/contrib/admin/__init__.py", line 6, in <module>

    from django.contrib.admin.sites import AdminSite, site

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/contrib/admin/sites.py", line 4, in <module>

    from django.contrib.admin.forms import AdminAuthenticationForm

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/contrib/admin/forms.py", line 6, in <module>

    from django.contrib.auth.forms import AuthenticationForm

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/contrib/auth/forms.py", line 17, in <module>

    from django.contrib.auth.models import User

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/contrib/auth/models.py", line 48, in <module>

    class Permission(models.Model):

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/db/models/base.py", line 96, in __new__

    new_class.add_to_class('_meta', Options(meta, **kwargs))

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/db/models/base.py", line 264, in add_to_class

    value.contribute_to_class(cls, name)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/db/models/options.py", line 124, in contribute_to_class

    self.db_table = truncate_name(self.db_table, connection.ops.max_name_length())

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/db/__init__.py", line 34, in __getattr__

    return getattr(connections[DEFAULT_DB_ALIAS], item)

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/db/utils.py", line 198, in __getitem__

    backend = load_backend(db['ENGINE'])

  File "/home/gamesbook/.virtualenvs/s2s/local/lib/python2.7/site-packages/django/db/utils.py", line 131, in load_backend

    raise ImproperlyConfigured(error_msg)

django.core.exceptions.ImproperlyConfigured: 'custom.base' isn't an available database backend.

Try using 'django.db.backends.XXX', where XXX is one of:

    u'mysql', u'oracle', u'postgresql_psycopg2', u'sqlite3'

Any idea what I have missed?

Thanks

Derek

On Friday, 28 December 2018 16:20:33 UTC+2, Simon Charette wrote:

Hello Derek,

There's no setting but this is controlled by the allows_auto_pk_0 feature flag [0].

Database feature are set to the default database configurations so Django can

minimized the number of introspection queries required to work appropriately

each time a connection is opened.

In order to override it I suggest you create a subclass of django.db.backends.mysql.base.DatabaseWrapper in

a custom foo.bar.base module and set feature_class to a .features.DatabaseFeatures[1]

subclass that sets the allows_auto_pk_0 flag to True.

e.g.

#foo/bar/base.py

from django.db.backends.mysql.base import DatabaseWrapper as BaseDatabaseWrapper

from django.db.backends.mysql.features import DatabaseFeatures as BaseDatabaseFeatures

class DatabaseFeatures(BaseDatabaseFeatures):

    allows_auto_pk_0 = True

class DatabaseWrapper(BaseDatabaseWrapper):

    features_class = DatabaseFeatures

Then you'll want to point your DATABASES.ENGINE to 'foo.bar' instead of 'django.db.backends.mysql'.

Keep in mind that Django expects

1. A .base submodule [2]

2. A class named DatabaseWrapper [3]

The foo.bar module path can be renamed to whatever you want.

Cheers,

[0] https://github.com/django/django/blob/e817ae74da0e515db31907ebcb2d00bcf7c3f5bc/django/db/backends/mysql/features.py#L27

[1] https://github.com/django/django/blob/e817ae74da0e515db31907ebcb2d00bcf7c3f5bc/django/db/backends/mysql/base.py#L184

[2] https://github.com/django/django/blob/e817ae74da0e515db31907ebcb2d00bcf7c3f5bc/django/db/utils.py#L110

[3] https://github.com/django/django/blob/e817ae74da0e515db31907ebcb2d00bcf7c3f5bc/django/db/utils.py#L203

Le vendredi 28 décembre 2018 07:47:31 UTC-5, Derek a écrit :

I am working with some legacy data and need to preserve the existing record ID's which, in some tables, include the value of 0 for their auto-increment field.

I have updated the configuration file for MySQL such that the sql_mode is set to include 'NO_AUTO_VALUE_ON_ZERO'.  

I can check that this works by running the following test inside the MySQL shell:

create table number (number int not null auto_increment primary key); insert into number(number) values (0); select * from number; drop table number;

+--------+

| number |

+--------+

|      0 |

+--------+

1 row in set (0,00 sec)

However, when I try this approach with one of my existing models (in the Django shell) I get an error:

from myapp.models import MyModel

MyModel.objects.create(pk=0, direction=1, code='UNK')

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/manager.py", line 157, in create

    return self.get_queryset().create(**kwargs)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/query.py", line 322, in create

    obj.save(force_insert=True, using=self.db)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/base.py", line 545, in save

    force_update=force_update, update_fields=update_fields)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/base.py", line 573, in save_base

    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/base.py", line 654, in _save_table

    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/base.py", line 687, in _do_insert

    using=using, raw=raw)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/manager.py", line 232, in _insert

    return insert_query(self.model, objs, fields, **kwargs)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/query.py", line 1514, in insert_query

    return query.get_compiler(using=using).execute_sql(return_id)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 902, in execute_sql

    for sql, params in self.as_sql():

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 860, in as_sql

    for obj in self.query.objs

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/fields/__init__.py", line 350, in get_db_prep_save

    prepared=False)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/models/fields/__init__.py", line 607, in get_db_prep_value

    value = connection.ops.validate_autopk_value(value)

  File "/home/gamesbook/,virtualenvs/proj/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 336, in validate_autopk_value

    raise ValueError('The database backend does not accept 0 as a '

ValueError: The database backend does not accept 0 as a value for AutoField.

Is there a setting I need to change such that Django does not raise this error and allows the PK to be zero?

Thanks

Derek

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9244a674-6097-416c-b40a-e4227ec675e7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.