2

I am attempting to transfer a database from sqlite to mysql.

I've googled this error and found Stack Overflow matches, but havent seen how to debug/identify the offending "0 value AutoField" fields. I've tried skirting the issue by dumping/loading different tables, but all seem to generate the same error.

I've attempted appending -e contenttypes, --natural-foreign, and --natural-primary to my datadump command, e.g.,

python manage.py dumpdata -e contenttypes --natural-foreign --natural-primary --indent=4 > datadump_3-7-18.json

After running python manage.py loaddata --traceback datadump_3-7-18.json

It produces the traceback error:

(venv) ➜  bikerental git:(additional-features-march) ✗ python manage.py loaddata --traceback datadump_3-7-18.json
Traceback (most recent call last):
  File "manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/rentals/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/rentals/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/rentals/venv/lib/python3.6/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/rentals/venv/lib/python3.6/site-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/rentals/venv/lib/python3.6/site-packages/django/core/management/commands/loaddata.py", line 72, in handle
    self.loaddata(fixture_labels)
  File "/rentals/venv/lib/python3.6/site-packages/django/core/management/commands/loaddata.py", line 113, in loaddata
    self.load_label(fixture_label)
  File "/rentals/venv/lib/python3.6/site-packages/django/core/management/commands/loaddata.py", line 177, in load_label
    obj.save(using=self.using)
  File "/rentals/venv/lib/python3.6/site-packages/django/core/serializers/base.py", line 205, in save
    models.Model.save_base(self.object, using=using, raw=True, **kwargs)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/base.py", line 759, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/base.py", line 842, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/base.py", line 880, in _do_insert
    using=using, raw=raw)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/query.py", line 1125, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1280, in execute_sql
    for sql, params in self.as_sql():
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1233, in as_sql
    for obj in self.query.objs
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1233, in <listcomp>
    for obj in self.query.objs
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1232, in <listcomp>
    [self.prepare_value(field, self.pre_save_val(field, obj)) for field in fields]
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1172, in prepare_value
    value = field.get_db_prep_save(value, connection=self.connection)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/fields/__init__.py", line 767, in get_db_prep_save
    return self.get_db_prep_value(value, connection=connection, prepared=False)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/models/fields/__init__.py", line 940, in get_db_prep_value
    value = connection.ops.validate_autopk_value(value)
  File "/rentals/venv/lib/python3.6/site-packages/django/db/backends/mysql/operations.py", line 163, in validate_autopk_value
    raise ValueError('The database backend does not accept 0 as a '
ValueError: Problem installing fixture '/rentals/bikerental/datadump_3-7-18.json': The database backend does not accept 0 as a value for AutoField.

I've noticed this seems to have something to do with Foreignkey values, so I'll post the one I have:

bike = models.ManyToManyField(Bike, blank=True)

Any way to more easily identify where in the database I assume this is coming from?

fish
  • 134
  • 2
  • 9
  • I've solved the problem by **manually** editing the data dump. In the table to which the ManyToManyField refers, there was a record whose ID was **0**. This was due to my initial manually entered set of records, where I began the increment at 0. Removing this record, and removing references to it in the ManyToManyField allowed the loaddata command to work without error. For the record, I would just like to state that error handling could/should be improved here by being more explicit, for it had me scratching my head for nearly an entire work day. – fish Mar 08 '18 at 03:16
  • You should add that comment as an answer - it's interesting. – Shadow Mar 08 '18 at 04:29
  • @Shadow Ah, yes. I shall. Thank you. – fish Mar 08 '18 at 07:17

1 Answers1

2

I've solved the problem by manually editing the data dump. In the table to which the ManyToManyField refers, there was a record whose ID was 0. This was due to my initial manually entered set of records, where I began the increment at 0. Removing this record, and removing references to it in the ManyToManyField allowed the loaddata command to work without error. For the record, I would just like to state that error handling could/should be improved here by being more explicit, for it had me scratching my head for nearly an entire work day.

For illustration in datadump_3-7-18.json:

{                                 <----- I GOT DELETED  
    "model": "inventory.bike",    <----- I GOT DELETED  
    "pk": 0,                      <----- I GOT DELETED  
    "fields": {                   <----- I GOT DELETED   
       ...                        <----- I GOT DELETED  
    }                             <----- I GOT DELETED  
},                                <----- I GOT DELETED  
{
    "model": "inventory.bike",
    "pk": 1,
    "fields": {
       ...
    }
},
{
    "model": "inventory.bike",
    "pk": 2,
    "fields": {
       ...
    }
},

And later on in datadump_3-7-18.json, the records containing the 0 ManyToManyField foreignkey:

{
    "model": "reservations.reservation",
    "pk": 55,
    "fields": {
       ...
        "bike": [
            0,      <----- I GOT DELETED   
            1,
            2
        ]
    }
},
{
    "model": "reservations.reservation",
    "pk": 28,
    "fields": {
        ...
        "bike": [
            0,      <----- I GOT DELETED  
            1,
            2,
            3
        ]
    }
},
fish
  • 134
  • 2
  • 9