[PDB Tech] [External Mail] Re: Problem doing initial 'peeringdb sync', foreign key constraint fails?
Milton Ngan
milton at valvesoftware.com
Fri May 15 18:02:46 PDT 2020
Thanks. I saw your reply to the list. Glad you managed to get it working. I was using it for our peering managed tool, but I have since switched to Peering Manager which does its own little sync (not a complete sync) instead of using peeringdb-py, so we have not had to worry about this bug in a while. That being said, there was discussion about switching to peeringdb-py/django_peeringdb in Peering Manager but until django_peeringdb supports Django3 it is a non-starter.
________________________________
From: Brian Dickson <brian.peter.dickson at gmail.com>
Sent: Friday, May 15, 2020 4:33:34 PM
To: Milton Ngan
Cc: Chris Caputo; pdb-tech at lists.peeringdb.com
Subject: Re: [External Mail] Re: [PDB Tech] Problem doing initial 'peeringdb sync', foreign key constraint fails?
Hi, Milton,
Just want to confirm (to keep you in the loop), your suggestion did fix the problem for me.
Thanks again,
Brian
On Fri, May 15, 2020 at 3:41 PM Milton Ngan <milton at valvesoftware.com<mailto:milton at valvesoftware.com>> wrote:
I replicated the problem and added some additional debugging to illustrate the problem. The first time stamp is what is in the object and is in UTC, the second is the UTC timestamp for now, and the third is the local timestamp. You can see that the update time is in the "future" by about 37 minutes from the local time, but is about 7 hours old when compared to UTC.
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: 10
Ignoring object updated after sync began: (org-26096)
UPDATED: 2020-05-15 14:59:14, UTC: 2020-05-15 21:22:31.806911, NOW: 2020-05-15 14:22:31.806908
Ignoring object updated after sync began: (org-26092)
UPDATED: 2020-05-15 15:29:39, UTC: 2020-05-15 21:22:31.806911, NOW: 2020-05-15 14:22:31.806908
Ignoring object updated after sync began: (org-26097)
UPDATED: 2020-05-15 15:55:02, UTC: 2020-05-15 21:22:31.806911, NOW: 2020-05-15 14:22:31.806908
Ignoring object updated after sync began: (org-26094)
I think the problem is that because the code filters out objects that have "changed" after the sync time. However even if those objects existed prior to that time they may not get inserted, thus causing referential integrity issues. I think the intent was that in the worst case you would be some amount of time behind PeeringDB, but this is not the case. I think the logic that is there is pretty dangerous because within the window of you performing the sync, you might hit this case, but it is pretty rare since a full sync probably takes 3-4 minutes and an incremental less than a minute.
1. Object A gets created at time X and is referenced by Object B
2. Start sync at time X+1
3. Object A gets updated at X+2
4. Try to sync object A at X+3 but is skipped leaving Object B with a dangling reference to Object A
Now in combination with the timezone issue, the window where you can get inconsistent changes is like 8 hours for people here in on the West Coast of the US. A lot of changes can happen in 8 hours...
________________________________
From: Pdb-tech <pdb-tech-bounces at lists.peeringdb.com<mailto:pdb-tech-bounces at lists.peeringdb.com>> on behalf of Chris Caputo <ccaputo at alt.net<mailto:ccaputo at alt.net>>
Sent: Friday, May 15, 2020 2:42 PM
To: Brian Dickson
Cc: pdb-tech at lists.peeringdb.com<mailto:pdb-tech at lists.peeringdb.com>
Subject: [External Mail] Re: [PDB Tech] Problem doing initial 'peeringdb sync', foreign key constraint fails?
Any better with Milton's 'self.start_time = datetime.utcnow()' change?
Thanks,
Chris
On Fri, 15 May 2020, Brian Dickson wrote:
> On Fri, May 15, 2020 at 12:57 PM Brian Dickson <brian.peter.dickson at gmail.com<mailto:brian.peter.dickson at gmail.com>> wrote:
> The local database is empty. I'll make sure that's the case just to be safe.
>
> # peeringdb --version
>
> peeringdb 1.0.0
>
>
> # django-admin --version
>
> 2.2.12
>
>
> # pip freeze
>
> asgiref==3.2.7
>
> certifi==2020.4.5.1
>
> cfu==1.5.0
>
> chardet==3.0.4
>
> click==7.1.2
>
> Django==2.2.12
>
> django-countries==6.1.2
>
> django-handleref==0.5.0
>
> django-inet==0.4.0
>
> django-peeringdb==2.0.0
>
> future==0.18.2
>
> idna==2.9
>
> munge==1.0.0
>
> mysqlclient==1.4.4
>
> peeringdb==1.0.0
>
> PyMySQL==0.9.3
>
> pytz==2020.1
>
> PyYAML==5.3.1
>
> requests==2.23.0
>
> six==1.14.0
>
> sqlparse==0.3.1
>
> twentyc.rpc==0.4.0
>
> urllib3==1.25.9
>
>
>
> Here is the result when running the sync on a clean empty local database:
>
> bash-3.2# peeringdb drop-tables
>
> bash-3.2# peeringdb sync
>
> 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: 18439
>
> Ignoring object updated after sync began: (org-6864)
>
> Ignoring object updated after sync began: (org-13687)
>
> Ignoring object updated after sync began: (org-23582)
>
> Ignoring object updated after sync began: (org-26092)
>
> Ignoring object updated after sync began: (org-26094)
>
> Ignoring object updated after sync began: (org-26095)
>
> Ignoring object updated after sync began: (org-26096)
>
> Ignoring object updated after sync began: (org-26097)
>
> Ignoring object updated after sync began: (org-26098)
>
> Fetching & updating all: fac
>
> Updates to be processed: 3714
>
> Ignoring object updated after sync began: (fac-3634)
>
> Ignoring object updated after sync began: (fac-8262)
>
> Ignoring object updated after sync began: (fac-8582)
>
> Ignoring object updated after sync began: (fac-8583)
>
> Ignoring object updated after sync began: (fac-8584)
>
> Ignoring object updated after sync began: (fac-8585)
>
> Ignoring object updated after sync began: (fac-8586)
>
> Ignoring object updated after sync began: (fac-8587)
>
> Ignoring object updated after sync began: (fac-8588)
>
> Ignoring object updated after sync began: (fac-8591)
>
> Ignoring object updated after sync began: (fac-8592)
>
> Ignoring object updated after sync began: (fac-8593)
>
> Ignoring object updated after sync began: (fac-8594)
>
> Ignoring object updated after sync began: (fac-8595)
>
> Ignoring object updated after sync began: (fac-8597)
>
> Ignoring object updated after sync began: (fac-8598)
>
> Ignoring object updated after sync began: (fac-8599)
>
> Ignoring object updated after sync began: (fac-8600)
>
> Ignoring object updated after sync began: (fac-8601)
>
> Ignoring object updated after sync began: (fac-8602)
>
> Ignoring object updated after sync began: (fac-8603)
>
> Fetching & updating all: net
>
> Updates to be processed: 18845
>
> Ignoring object updated after sync began: (net-2913)
>
> Ignoring object updated after sync began: (net-3087)
>
> Ignoring object updated after sync began: (net-4823)
>
> Ignoring object updated after sync began: (net-5442)
>
> Ignoring object updated after sync began: (net-7482)
>
> Ignoring object updated after sync began: (net-9435)
>
> Ignoring object updated after sync began: (net-13681)
>
> Ignoring object updated after sync began: (net-14816)
>
> Ignoring object updated after sync began: (net-19500)
>
> Ignoring object updated after sync began: (net-20575)
>
> Ignoring object updated after sync began: (net-21292)
>
> Ignoring object updated after sync began: (net-21916)
>
> Ignoring object updated after sync began: (net-23222)
>
> Ignoring object updated after sync began: (net-23266)
>
> Ignoring object updated after sync began: (net-23280)
>
> Ignoring object updated after sync began: (net-23283)
>
> Ignoring object updated after sync began: (net-23284)
>
> Ignoring object updated after sync began: (net-23285)
>
> 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 287, in sync_row
>
> B.clean(obj)
>
> File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django_peeringdb/client_adaptor/backend.py", line 145, in clean
>
> obj.full_clean()
>
> File "/Users/bdickson1/Documents/projects/site-selection/pdbvenv/lib/python3.6/site-packages/django/db/models/base.py", line 1203, in full_clean
>
> raise ValidationError(errors)
>
> django.core.exceptions.ValidationError: {'org': ['organization instance with id 13687 does not exist.']}
>
>
>
> On Fri, May 15, 2020 at 10:55 AM Chris Caputo <ccaputo at alt.net<mailto:ccaputo at alt.net>> wrote:
> 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
> >
> >
> >
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.peeringdb.com/pipermail/pdb-tech/attachments/20200516/afbc34af/attachment-0001.htm>
More information about the Pdb-tech
mailing list