search

rseichter / automx2

Mail User Agent (email client) configuration made easy
GNU General Public License v3.0
61 stars 19 forks source link

MySQLdb.IntegrityError: Cannot add or update a child row: a foreign key constraint fails #12

Closed HLFH closed 1 year ago

HLFH commented 1 year ago

File used to populate the MariaDB database: https://github.com/rseichter/automx2/blob/master/contrib/seed-example.json Getting the same error whether I use mysqlclient or pymysql.

oct. 26 16:02:01 arch-server python[1828451]: Exception on /initdb/ [POST]
oct. 26 16:02:01 arch-server python[1828451]: Traceback (most recent call last):
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1880, in _execute_context
oct. 26 16:02:01 arch-server python[1828451]:     self.dialect.do_executemany(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 193, in do_executemany
oct. 26 16:02:01 arch-server python[1828451]:     rowcount = cursor.executemany(statement, parameters)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 230, in executemany
oct. 26 16:02:01 arch-server python[1828451]:     return self._do_execute_many(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 266, in _do_execute_many
oct. 26 16:02:01 arch-server python[1828451]:     rows += self.execute(sql + postfix)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
oct. 26 16:02:01 arch-server python[1828451]:     res = self._query(query)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
oct. 26 16:02:01 arch-server python[1828451]:     db.query(q)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
oct. 26 16:02:01 arch-server python[1828451]:     _mysql.connection.query(self, query)
oct. 26 16:02:01 arch-server python[1828451]: MySQLdb.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`automx2`.`domain`, CONSTRAINT `domain_ibfk_1` FOREIGN KEY (`provider_id`) REFERENCES `provider` (`id`))')
oct. 26 16:02:01 arch-server python[1828451]: The above exception was the direct cause of the following exception:
oct. 26 16:02:01 arch-server python[1828451]: Traceback (most recent call last):
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/flask/app.py", line 2525, in wsgi_app
oct. 26 16:02:01 arch-server python[1828451]:     response = self.full_dispatch_request()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/flask/app.py", line 1822, in full_dispatch_request
oct. 26 16:02:01 arch-server python[1828451]:     rv = self.handle_user_exception(e)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/flask/app.py", line 1820, in full_dispatch_request
oct. 26 16:02:01 arch-server python[1828451]:     rv = self.dispatch_request()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/flask/app.py", line 1796, in dispatch_request
oct. 26 16:02:01 arch-server python[1828451]:     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/flask/views.py", line 107, in view
oct. 26 16:02:01 arch-server python[1828451]:     return current_app.ensure_sync(self.dispatch_request)(**kwargs)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/flask/views.py", line 188, in dispatch_request
oct. 26 16:02:01 arch-server python[1828451]:     return current_app.ensure_sync(meth)(**kwargs)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/automx2/views/initdb.py", line 57, in post
oct. 26 16:02:01 arch-server python[1828451]:     return self.init_db(request.json)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/automx2/views/initdb.py", line 42, in init_db
oct. 26 16:02:01 arch-server python[1828451]:     populate_db(data)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/automx2/database.py", line 163, in populate_db
oct. 26 16:02:01 arch-server python[1828451]:     populate_with_dict(data_source)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/automx2/database.py", line 135, in populate_with_dict
oct. 26 16:02:01 arch-server python[1828451]:     sid = Server.query.count()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 3167, in count
oct. 26 16:02:01 arch-server python[1828451]:     return self._from_self(col).enable_eagerloads(False).scalar()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2892, in scalar
oct. 26 16:02:01 arch-server python[1828451]:     ret = self.one()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2869, in one
oct. 26 16:02:01 arch-server python[1828451]:     return self._iter().one()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2907, in _iter
oct. 26 16:02:01 arch-server python[1828451]:     result = self.session.execute(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1662, in execute
oct. 26 16:02:01 arch-server python[1828451]:     ) = compile_state_cls.orm_pre_session_exec(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 312, in orm_pre_session_exec
oct. 26 16:02:01 arch-server python[1828451]:     session._autoflush()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2259, in _autoflush
oct. 26 16:02:01 arch-server python[1828451]:     util.raise_(e, with_traceback=sys.exc_info()[2])
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
oct. 26 16:02:01 arch-server python[1828451]:     raise exception
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2248, in _autoflush
oct. 26 16:02:01 arch-server python[1828451]:     self.flush()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3429, in flush
oct. 26 16:02:01 arch-server python[1828451]:     self._flush(objects)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3568, in _flush
oct. 26 16:02:01 arch-server python[1828451]:     with util.safe_reraise():
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
oct. 26 16:02:01 arch-server python[1828451]:     compat.raise_(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
oct. 26 16:02:01 arch-server python[1828451]:     raise exception
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3529, in _flush
oct. 26 16:02:01 arch-server python[1828451]:     flush_context.execute()
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
oct. 26 16:02:01 arch-server python[1828451]:     rec.execute(self)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
oct. 26 16:02:01 arch-server python[1828451]:     util.preloaded.orm_persistence.save_obj(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
oct. 26 16:02:01 arch-server python[1828451]:     _emit_insert_statements(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1097, in _emit_insert_statements
oct. 26 16:02:01 arch-server python[1828451]:     c = connection._execute_20(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
oct. 26 16:02:01 arch-server python[1828451]:     return meth(self, args_10style, kwargs_10style, execution_options)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
oct. 26 16:02:01 arch-server python[1828451]:     return connection._execute_clauseelement(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
oct. 26 16:02:01 arch-server python[1828451]:     ret = self._execute_context(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
oct. 26 16:02:01 arch-server python[1828451]:     self._handle_dbapi_exception(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
oct. 26 16:02:01 arch-server python[1828451]:     util.raise_(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
oct. 26 16:02:01 arch-server python[1828451]:     raise exception
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1880, in _execute_context
oct. 26 16:02:01 arch-server python[1828451]:     self.dialect.do_executemany(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 193, in do_executemany
oct. 26 16:02:01 arch-server python[1828451]:     rowcount = cursor.executemany(statement, parameters)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 230, in executemany
oct. 26 16:02:01 arch-server python[1828451]:     return self._do_execute_many(
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 266, in _do_execute_many
oct. 26 16:02:01 arch-server python[1828451]:     rows += self.execute(sql + postfix)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
oct. 26 16:02:01 arch-server python[1828451]:     res = self._query(query)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
oct. 26 16:02:01 arch-server python[1828451]:     db.query(q)
oct. 26 16:02:01 arch-server python[1828451]:   File "/srv/http/automx2/.venv/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
oct. 26 16:02:01 arch-server python[1828451]:     _mysql.connection.query(self, query)
oct. 26 16:02:01 arch-server python[1828451]: sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
oct. 26 16:02:01 arch-server python[1828451]: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`automx2`.`domain`, CONSTRAINT `domain_ibfk_1` FOREIGN KEY (`provider_id`) REFERENCES `provider` (`id`))')
oct. 26 16:02:01 arch-server python[1828451]: [SQL: INSERT INTO domain (id, name, provider_id, ldapserver_id) VALUES (%s, %s, %s, %s)]
oct. 26 16:02:01 arch-server python[1828451]: [parameters: ((0, 'example.com', 0, None), (1, 'example.net', 0, None), (2, 'example.org', 0, None))]
oct. 26 16:02:01 arch-server python[1828451]: (Background on this error at: https://sqlalche.me/e/14/gkpj)
oct. 26 16:02:01 arch-server python[1828451]: 127.0.0.1 - - [26/Oct/2022 16:02:01] "POST /initdb/ HTTP/1.1" 500 -
HLFH commented 1 year ago

Fixed by https://github.com/rseichter/automx2/pull/13

rseichter commented 1 year ago

I am currently hospitalised, and depending on how my health develops it may take a couple days or even weeks before I can look into this matter.

HLFH commented 1 year ago

I wish you a speedy recovery.

I understand you don't intend to add contributors to your project.

Closing in favour of automua fork. https://github.com/HLFH/automua That fixes this bug, other tiny bugs, configuration files, docs, migrates to pyproject.toml, and add support for ArchLinux: https://aur.archlinux.org/packages/automua