[PDB Tech] Problem doing initial 'peeringdb sync', foreign key constraint fails?

Fri May 15 10:55:07 PDT 2020

Hi.  Does restarting with a fresh/empty local database help at all?

Also, what does the following show?:

peeringdb --version
django-admin --version
pip freeze

Thanks,
Chris

On Fri, 15 May 2020, Brian Dickson wrote:
> Arnold Nipper said to send to this list.
> 
> (This is kind of urgent for me, my goal is to get a snapshot of the data, so any alternative workaround would be helpful in the
> meantime.)
> 
> I'm in the process of setting up a local copy, and have run into a problem after getting the peeringdb-py stuff set up.
> 
> When I do peeringdb sync (with the correct mysql database UTF8 stuff and everything,), I am currently getting a persistent error
> which aborts the sync with no data in the tables:
> 
> (Apologies in advance for the long error output text.)
> Syncing to https://www.peeringdb.com/api
> Updating resources: org fac net ix ixfac ixlan ixpfx netfac netixlan poc
> Fetching & updating all: org
> Updates to be processed: 5
> Ignoring object updated after sync began: (org-26015)
> Ignoring object updated after sync began: (org-26052)
> Ignoring object updated after sync began: (org-17918)
> Ignoring object updated after sync began: (org-26053)
> Fetching & updating all: fac
> Updates to be processed: 0
> Fetching & updating all: net
> Updates to be processed: 18800
> Ignoring object updated after sync began: (net-1356)
> Ignoring object updated after sync began: (net-3684)
> Ignoring object updated after sync began: (net-7924)
> Ignoring object updated after sync began: (net-10733)
> Ignoring object updated after sync began: (net-13084)
> Ignoring object updated after sync began: (net-14581)
> Ignoring object updated after sync began: (net-15702)
> Traceback (most recent call last):
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/backends/utils.py",
> line 84, in _execute
>     return self.cursor.execute(sql, params)
>   File
> "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line
> 71, in execute
>     return self.cursor.execute(query, args)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 209, in
> execute
>     res = self._query(query)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 315, in
> _query
>     db.query(q)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/MySQLdb/connections.py", line
> 226, in query
>     _mysql.connection.query(self, query)
> MySQLdb._exceptions.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails
> (`peeringdb`.`peeringdb_network`, CONSTRAINT `peeringdb_network_org_id_404d6106_fk_peeringdb_organization_id` FOREIGN KEY
> (`org_id`) REFERENCES `peeringdb_organization` (`id`))')
> 
> The above exception was the direct cause of the following exception:
> 
> Traceback (most recent call last):
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/bin/peeringdb", line 8, in <module>
>     sys.exit(main())
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/cli.py", line 62, in
> main
>     return handler(config=cfg, **vars(options))
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/commands.py", line 20,
> in _wrapped
>     r = func(*a, **k)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/commands.py", line 219,
> in handle
>     client.update_all(rs)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/_update.py", line 66,
> in update_all
>     self._atomic_update(lambda: ctx.sync_resource(r, since=since))
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/_update.py", line 78,
> in _atomic_update
>     sync_func()
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/_update.py", line 66,
> in <lambda>
>     self._atomic_update(lambda: ctx.sync_resource(r, since=since))
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/_tasks_async.py", line
> 65, in _wrapped
>     return loop.run_until_complete(func(*a, **k))
>   File "/opt/local/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/base_events.py", line 488, in
> run_until_complete
>     return future.result()
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/_tasks_async.py", line
> 41, in _wrapped
>     item = gen.send(r)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/peeringdb/_update.py", line 274,
> in sync_row
>     B.save(obj)
>   File"/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django_peeringdb/client_adaptor/backend
> .py", line 148, in save
>     obj.save()
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/base.py", line
> 741, in save
>     force_update=force_update, update_fields=update_fields)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/base.py", line
> 779, in save_base
>     force_update, using, update_fields,
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/base.py", line
> 870, in _save_table
>     result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/base.py", line
> 908, in _do_insert
>     using=using, raw=raw)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/manager.py",
> line 82, in manager_method
>     return getattr(self.get_queryset(), name)(*args, **kwargs)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/query.py", line
> 1186, in _insert
>     return query.get_compiler(using=using).execute_sql(return_id)
>   File
> "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line
> 1375, in execute_sql
>     cursor.execute(sql, params)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/backends/utils.py",
> line 67, in execute
>     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/backends/utils.py",
> line 76, in _execute_with_wrappers
>     return executor(sql, params, many, context)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/backends/utils.py",
> line 84, in _execute
>     return self.cursor.execute(sql, params)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/utils.py", line 89, in
> __exit__
>     raise dj_exc_value.with_traceback(traceback) from exc_value
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/backends/utils.py",
> line 84, in _execute
>     return self.cursor.execute(sql, params)
>   File
> "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line
> 71, in execute
>     return self.cursor.execute(query, args)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 209, in
> execute
>     res = self._query(query)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 315, in
> _query
>     db.query(q)
>   File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/MySQLdb/connections.py", line
> 226, in query
>     _mysql.connection.query(self, query)
> django.db.utils.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails
> (`peeringdb`.`peeringdb_network`, CONSTRAINT `peeringdb_network_org_id_404d6106_fk_peeringdb_organization_id` FOREIGN KEY
> (`org_id`) REFERENCES `peeringdb_organization` (`id`))')
> 
> Please let me know if this is a problem on my end, or a problem with the data in the master db itself...
> 
> If there is something I should do to fix this on my end, any advice would be greatly appreciated.
> (If there is any other way to grab a copy of the tables in mysql format, that's all I'm really trying to get, just a snapshot of
> the current peeringdb database to process with some perl scripts I have.)
> 
> Thanks in advance,
> 
> Brian Dickson
> 
> 
> 