Error during DB migration

[Марат Сибгатулин]

Hi.

While trying to upgrade NetBox 2.3.7 to 2.4.3 I got an error during DB migration.

After ./upgrade.sh or python3 manage.py migrate:

Operations to perform:
  Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization
Running migrations:
  Applying auth.0009_alter_user_last_name_max_length...Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.IntegrityError: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, auth, 0009_alter_user_last_name_max_length, 2018-08-21 10:36:01.703745+00).


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/commands/migrate.py", line 200, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/executor.py", line 250, in apply_migration
    self.recorder.record_applied(migration.app_label, migration.name)
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/recorder.py", line 71, in record_applied
    self.migration_qs.create(app=app, name=name)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 417, in create
    obj.save(force_insert=True, using=self.db)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/base.py", line 729, in save
    force_update=force_update, update_fields=update_fields)
  File "/usr/local/lib/python3.5/dist-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 "/usr/local/lib/python3.5/dist-packages/django/db/models/base.py", line 842, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/base.py", line 880, in _do_insert
    using=using, raw=raw)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1125, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/compiler.py", line 1285, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, auth, 0009_alter_user_last_name_max_length, 2018-08-21 10:36:01.703745+00).




 I tried several times by restoring DB and checking if NetBox works normally. So it doesn't look like DB corruption.





• Python version: Python 3.5.2
• NetBox version: Example: 2.4.3
• Postgres is locally installed on the host.
  
  

[Марат Сибгатулин]

Are there any ideas? 

I am completely confused by the error.

вторник, 21 августа 2018 г., 17:25:14 UTC+3 пользователь Марат Сибгатулин написал:

[Matthew Yauch]

Check:

select id,app,name from django_migrations where app='auth';

Maybe a migration in there has a bad id. It sounds like it's either looking at django_migrations or adding this migration to it. I would think it would just increment for the next id, so it's strange that it's having issues with a null id. It really seems like it's applying the migration and trying to add it to the list of completed ones (the django_migrations table), but it can't add it. The migration itself is from base Django, not specifically NetBox.

https://github.com/django/django/blob/master/django/contrib/auth/migrations/0009_alter_user_last_name_max_length.py

[Марат Сибгатулин]

> select id,app,name from django_migrations where app='auth';

netbox=> select id,app,name from django_migrations where app='auth';
 id | app  |                   name
----+------+------------------------------------------
  2 | auth | 0001_initial
  6 | auth | 0002_alter_permission_name_max_length
  7 | auth | 0003_alter_user_email_max_length
  8 | auth | 0004_alter_user_username_opts
  9 | auth | 0005_alter_user_last_login_null
 10 | auth | 0006_require_contenttypes_0002
 11 | auth | 0007_alter_validators_add_error_messages
 12 | auth | 0008_alter_user_username_max_length
(8 rows)

The strange thing happen when I drop all tables (by select 'drop table ' || tablename || ' cascade;' from pg_tables;)

It successfully pass this step (while anyway stuck in another).

vagrant@ubuntu-xenial:/opt/netbox/netbox$ python3 manage.py migrate

Operations to perform:
  Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization
Running migrations:

  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying taggit.0001_initial... OK
  Applying taggit.0002_auto_20150616_2121... OK
  Applying tenancy.0001_initial... OK
  Applying dcim.0001_initial...Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 83, in _execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: relation "dcim_poweroutlet" already exists

The above exception was the direct cause of the following exception:


Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.5/dist-packages/django/core/management/commands/migrate.py", line 200, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)

  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/migration.py", line 122, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.5/dist-packages/django/db/migrations/operations/models.py", line 92, in database_forwards
    schema_editor.create_model(model)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/base/schema.py", line 314, in create_model
    self.execute(sql, params or None)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/base/schema.py", line 133, in execute
    cursor.execute(sql, params)

  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from

exc_value
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 83, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: relation "dcim_poweroutlet" already exists


vagrant@ubuntu-xenial:/opt/netbox/netbox$ psql -h localhost -U netbox netbox
WARNING: password file "/home/vagrant/.pgpass" has group or world access; permissions should be u=rw (0600) or less
Password for user netbox:
psql (9.5.14)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.


netbox=> select id,app,name from django_migrations where app='auth';
 id | app  |                   name
----+------+------------------------------------------
  2 | auth | 0001_initial
  6 | auth | 0002_alter_permission_name_max_length
  7 | auth | 0003_alter_user_email_max_length
  8 | auth | 0004_alter_user_username_opts
  9 | auth | 0005_alter_user_last_login_null
 10 | auth | 0006_require_contenttypes_0002
 11 | auth | 0007_alter_validators_add_error_messages
 12 | auth | 0008_alter_user_username_max_length
 13 | auth | 0009_alter_user_last_name_max_length
(9 rows)

среда, 5 сентября 2018 г., 19:53:03 UTC+3 пользователь Matthew Yauch написал:

[Brian Candler]

> The strange thing happen when I drop all tables

And what if you delete and recreate the entire database?

One possibility is that your source tree isn't clean: that is, there are some spurious source files lying around.  For example, this could happen you untarred a new release over an old release.

(However if you had a git checkout and did "git pull" to update, then it should automatically remove any files which need removing)

 

One way to test:

- unpack a fresh tarball in a new directory (or make a fresh git checkout in a new directory)

- create an entirely new database (e.g. "netbox2" instead of "netbox") and add the credentials to configuration.py

- apply migrations

Although since you've already dropped tables, just destroying and recreating your existing netbox database would be just as good.

If it still doesn't work then the problem is elsewhere on your system - e.g. the version of python or python libraries installed.

[Марат Сибгатулин]

Yeah, I have done just like you said: 

- wget tarball with of new version to separate directory

- copied configuration.py to that folder

- run upgrade.sh.

- met the issue.

It's quite a big deal to recreate DB just because a lot of changes were made manually. So, the only option is to upgrade schema or somehow move old data to new DB.

суббота, 8 сентября 2018 г., 14:11:06 UTC+3 пользователь Brian Candler написал:

[Brian Candler]

Then I don't understand what you were saying about dropping tables.

I also don't understand what you mean by "a lot of changes were made manually".  If this means you have been manually modifying the schema, then I'm afraid you're on your own.  Migrations only work to get the schema from one official state to another official state.

[Марат Сибгатулин]

Probably I has not explained situation quite clear.

What I mean is

1) upgrade.sh fails with that error

2) If I drop all tables from DB, upgrade.sh works normally. Therefore, there is problem with content which influences on migration.

3) I added data to DB manually, so installing new NetBox and adding it again is not an option. 

4) For sure I didn't change the schema.

воскресенье, 9 сентября 2018 г., 0:46:43 UTC+3 пользователь Brian Candler написал:

[Brian Candler]

Well, something is very odd.  The error message said:

psycopg2.IntegrityError: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, auth, 0009_alter_user_last_name_max_length, 2018-08-21 10:36:01.703745+00).




And yet there is no file or migration in Netbox with that name:

$ grep -R '0009_alter' .

$ find . -name '*0009_alter*'
$ find . -name '*alter_user*'

$ find . -name '*0009_*'

./netbox/circuits/migrations/0009_unicode_literals.py

./netbox/dcim/migrations/0009_site_32bit_asn_support.py

./netbox/extras/migrations/0009_topologymap_type.py

./netbox/ipam/migrations/0009_ipaddress_add_status.py

$

Aha... with a bit of googling I found it.  It's part of django's own auth module.

https://github.com/django/django/blob/master/django/contrib/auth/migrations/0009_alter_user_last_name_max_length.py

But why it fails, I don't know.  What versions of Django packages do you have?  Here I see:

root@netbox:~# pip3 list | grep -i django

Django (2.0.8)

django-cors-headers (2.4.0)

django-debug-toolbar (1.9.1)

django-filter (1.1.0)

django-js-asset (1.1.0)

django-mptt (0.9.1)

django-tables2 (1.21.2)

django-taggit (0.22.2)

django-taggit-serializer (0.1.7)

django-timezone-field (2.1)

djangorestframework (3.8.1)

Maybe this issue should be taken up on the Django list.

Also, check you don't have both python2 and python3 versions of packages installed.  On my Netbox VM (Ubuntu 16.04) I get the following:

root@netbox:~# pip list

The program 'pip' is currently not installed. You can install it by typing:

apt install python-pip

root@netbox:~# pip3 list

asn1crypto (0.24.0)

bcrypt (3.1.4)

blinker (1.3)

... etc

Installing Netbox in a container or VM is likely to minimise the problems you have.  (A python virtualenv would probably be OK, but the Netbox installation instructions don't show how to do that)

Finally, if you run the following commands, how does your output compare with mine? (This is 2.4.4 though)

root@netbox:~# echo "select * from django_migrations where id is null" | su -c 'psql netbox' postgres

could not change directory to "/root": Permission denied

 id | app | name | applied

----+-----+------+---------

(0 rows)

root@netbox:~# echo "select * from django_migrations where app='auth'" | su -c 'psql netbox' postgres

could not change directory to "/root": Permission denied

 id  | app  |                   name                   |            applied

-----+------+------------------------------------------+-------------------------------

   2 | auth | 0001_initial                             | 2017-09-15 09:20:16.864576+00

   6 | auth | 0002_alter_permission_name_max_length    | 2017-09-15 09:20:17.396394+00

   7 | auth | 0003_alter_user_email_max_length         | 2017-09-15 09:20:17.513436+00

   8 | auth | 0004_alter_user_username_opts            | 2017-09-15 09:20:17.58913+00

   9 | auth | 0005_alter_user_last_login_null          | 2017-09-15 09:20:17.674405+00

  10 | auth | 0006_require_contenttypes_0002           | 2017-09-15 09:20:17.702926+00

  11 | auth | 0007_alter_validators_add_error_messages | 2017-09-15 09:20:17.819744+00

  12 | auth | 0008_alter_user_username_max_length      | 2017-09-15 09:20:17.942834+00

 122 | auth | 0009_alter_user_last_name_max_length     | 2018-08-08 17:08:46.185336+00

(9 rows)

[Марат Сибгатулин]

Yeah. It is a part of django's own module.

I tried on this versions:

vagrant@ubuntu-xenial:~$ pip3 list | grep -i django
Django                   2.0.8
django-cors-headers      2.4.0
django-debug-toolbar     1.9.1
django-filter            1.1.0
django-js-asset          1.1.0
django-mptt              0.9.1
django-tables2           1.21.2
django-taggit            0.22.2
django-taggit-serializer 0.1.7
django-timezone-field    2.1
djangorestframework      3.8.1

I have only python3 installed

vagrant@ubuntu-xenial:~$ pip --version
pip 18.0 from /usr/local/lib/python3.5/dist-packages/pip-18.0-py3.5.egg/pip (python 3.5)

To compare:

netbox=> select * from django_migrations where id is null;

 id | app | name | applied
----+-----+------+---------
(0 rows)

netbox=> select * from django_migrations where app='auth';
 id | app  |                   name                   |            applied
----+------+------------------------------------------+-------------------------------
  2 | auth | 0001_initial                             | 2018-05-07 13:00:40.909734+00
  6 | auth | 0002_alter_permission_name_max_length    | 2018-05-07 13:00:41.211846+00
  7 | auth | 0003_alter_user_email_max_length         | 2018-05-07 13:00:41.256434+00
  8 | auth | 0004_alter_user_username_opts            | 2018-05-07 13:00:41.285459+00
  9 | auth | 0005_alter_user_last_login_null          | 2018-05-07 13:00:41.330958+00
 10 | auth | 0006_require_contenttypes_0002           | 2018-05-07 13:00:41.351068+00
 11 | auth | 0007_alter_validators_add_error_messages | 2018-05-07 13:00:41.381616+00
 12 | auth | 0008_alter_user_username_max_length      | 2018-05-07 13:00:41.429842+00
(8 rows)

I don't think it is pip or django issue, just because migration works for empty DB.

Probably I have to create 0009_alter_user_last_name_max_length manually?

воскресенье, 9 сентября 2018 г., 21:37:40 UTC+3 пользователь Brian Candler написал:

[Марат Сибгатулин]

ok, I really tried it and it proceeded. Just till the next error of the same kind:

psycopg2.IntegrityError: null value in column "id" violates not-null

constraint
DETAIL:  Failing row contains (null, taggit, 0001_initial, 2018-09-12 10:02:25.908032+00).

So, this records were really absent in my DB and by some reason migration tool can't add it.

Actually it can't allocate id, because if I manually insert record without ID it throws the error:

netbox=> insert into django_migrations (app, name) values ('auth', '0009_alter_user_last_name_max_length');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, auth, 0009_alter_user_last_name_max_length, null).

So, what is hell is going on? Is it limitation of my postgres installation or what?

среда, 12 сентября 2018 г., 13:07:49 UTC+3 пользователь Марат Сибгатулин написал:

[Марат Сибгатулин]

Guys, I solved it.

Sorry, it was completely my fault. I pg_dumped it from postgres10 and restored to postgres9.5.

There were some logic changes in sequences.

среда, 12 сентября 2018 г., 13:18:31 UTC+3 пользователь Марат Сибгатулин написал:

[Matthew Yauch]

Woohoo! Grats and glad you solved it.

[Brian Candler]

Nice one.  Thanks for the solution!