issue with Django-peeringdb2.1.1 and MySQL

[jboogman]

pdb@pdb2:~$ pip3 freeze | grep peeringdb django-peeringdb==2.1.1 peeringdb==1.0.0 pdb@pdb2:~$ 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: 20 Fetching & updating all: fac Updates to be processed: 3 Fetching & updating all: net Updates to be processed: 0 Fetching & updating all: ix Updates to be processed: 3 Fetching & updating all: ixfac Updates to be processed: 0 Fetching & updating all: ixlan Updates to be processed: 1 Fetching & updating all: ixpfx Updates to be processed: 1 Fetching & updating all: netfac Updates to be processed: 47 Traceback (most recent call last): File "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute return self.cursor.execute(query, args) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 209, in execute res = self._query(query) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 315, in _query db.query(q) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 239, in query _mysql.connection.query(self, query) MySQLdb._exceptions.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (pdb.peeringdb_network_facility, CONSTRAINT peeringdb_network_fa_net_id_ca57491b_fk_peeringdb FOREIGN KEY (net_id) REFERENCES peeringdb_network (id))')

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

Traceback (most recent call last): File "/usr/local/bin/peeringdb", line 11, in sys.exit(main()) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/cli.py", line 62, in main return handler(config=cfg, vars(options)) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/commands.py", line 20, in _wrapped r = func(*a, *k) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/commands.py", line 219, in handle client.update_all(rs) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_update.py", line 66, in update_all self._atomic_update(lambda: ctx.sync_resource(r, since=since)) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_update.py", line 78, in _atomic_update sync_func() File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_update.py", line 66, in self._atomic_update(lambda: ctx.sync_resource(r, since=since)) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_tasks_async.py", line 65, in _wrapped return loop.run_until_complete(func(a, k)) File "/usr/lib/python3.6/asyncio/base_events.py", line 484, in run_until_complete return future.result() File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_tasks_async.py", line 41, in _wrapped item = gen.send(r) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_update.py", line 274, in sync_row B.save(obj) File "/home/pdb/.local/lib/python3.6/site-packages/django_peeringdb/client_adaptor/backend.py", line 153, in save obj.save() File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/base.py", line 741, in save force_update=force_update, update_fields=update_fields) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/base.py", line 779, in save_base force_update, using, update_fields, File "/home/pdb/.local/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 "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/base.py", line 908, in _do_insert using=using, raw=raw) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method return getattr(self.get_queryset(), name)(*args, *kwargs) File "/home/pdb/.local/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 "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1332, in execute_sql cursor.execute(sql, params) File "/home/pdb/.local/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 "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers return executor(sql, params, many, context) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/utils.py", line 89, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute return self.cursor.execute(query, args) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 209, in execute res = self._query(query) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 315, in _query db.query(q) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 239, in query _mysql.connection.query(self, query) django.db.utils.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (pdb.peeringdb_network_facility, CONSTRAINT peeringdb_network_fa_net_id_ca57491b_fk_peeringdb FOREIGN KEY (net_id) REFERENCES peeringdb_network (id))') UpdateTask exception was never retrieved future: <UpdateTask for (net, 23785)> Traceback (most recent call last): File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_tasks_async.py", line 45, in _wrapped r = await item File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_tasks_async.py", line 41, in _wrapped item = gen.send(r) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_update.py", line 159, in fetch_and_index data, e = fetch_func() File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_update.py", line 217, in fetch return self.fetcher.fetch_all_latest(_R, 0, dict(id__in=_pks)) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/_fetch.py", line 62, in fetch_all_latest since = backend.last_change(backend.get_concrete(R)) File "/home/pdb/.local/lib/python3.6/site-packages/peeringdb/backend.py", line 26, in wrapped return fn(args, kwargs) File "/home/pdb/.local/lib/python3.6/site-packages/django_peeringdb/client_adaptor/backend.py", line 95, in last_change upd = concrete.handleref.last_change() File "/home/pdb/.local/lib/python3.6/site-packages/django_handleref/manager.py", line 101, in last_change return self.get_queryset().last_change(kwargs) File "/home/pdb/.local/lib/python3.6/site-packages/django_handleref/manager.py", line 23, in last_change cdt = self.latest('created') File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/query.py", line 649, in latest return self.reverse()._earliest(*fields, field_name=field_name) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/query.py", line 643, in _earliest return obj.get() File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/query.py", line 402, in get num = len(clone) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/query.py", line 256, in len self._fetch_all() File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/query.py", line 1242, in _fetch_all self._result_cache = list(self._iterable_class(self)) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/query.py", line 55, in iter results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1097, in execute_sql cursor.execute(sql, params) File "/home/pdb/.local/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 "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers return executor(sql, params, many, context) File "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in _execute self.db.validate_no_broken_transaction() File "/home/pdb/.local/lib/python3.6/site-packages/django/db/backends/base/base.py", line 438, in validate_no_broken_transaction "An error occurred in the current transaction. You can't " django.db.transaction.TransactionManagementError: An error occurred in the current transaction. You can't execute queries until the end of the 'atomic' block.

[vegu]

Been looking at this this morning, came here to make a ticket but looks like you beat me to it :)

It appears to be an issue where it correctly identifies the missing relationship, but doesn't fetch it before it tries to save the offending object.

The curious thing is i can also reproduce this with django-peeringdb 2.0.0, and 1.1.0 (with version faked to 2.1 so the api accepts the request), but it also seems too coincidental that this just appeared now with the 2.1 release of django-peeringdb.

[vegu]

@jboogman what version of django-peeringdb were you using before upgrading to 2.1?

[jboogman]

I used 2.0 and upgraded to 2.1 because of the new release, and later to 2.1.1

[vegu]

Confirmed that this issue seems to be mysql specific, sqlite handles it correctly.

Steps to reproduce

1. fresh sync (optional)
2. identify the network of most recently updated network facility
3. delete that network, its netfac and netixlan relations
4. run sync

MariaDB [pdb_client_test5]> select net_id from peeringdb_network_facility order by updated desc limit 1;
+--------+
| net_id |
+--------+
|   4936 |
+--------+
1 row in set (0.02 sec)

MariaDB [pdb_client_test5]> delete from peeringdb_network_ixlan where net_id=4936;
Query OK, 5 rows affected (0.01 sec)

MariaDB [pdb_client_test5]> delete from peeringdb_network_facility where net_id=4936;
Query OK, 2 rows affected (0.01 sec)

MariaDB [pdb_client_test5]> delete from peeringdb_network where id=4936;
Query OK, 1 row affected (0.01 sec)

[vegu]

@jboogman thanks - and you were running with 2.0 for a good while without running into this issue ?

[jboogman]

I went to 2.0 end of may, and haven't seen the issue, but I haven't checked every day, just now because of the new release...

[vegu]

future: <UpdateTask for (net, 23785)>

@jboogman i pushed the offending network object onto the api update queue, can you see if sync completes for you now - if it does not, is it viable for you to do a fresh sync?

If it is you can do so by running

peeringdb drop-tables;
peeringdb sync;

[jboogman]

sync did not complete, with the same error. I dropped tables now and sync does complete