sqlalchemy: MySQLdb.IntegrityError

[HLFH]

Source: rseichter/automx2#12

Last update by @rseichter

By the way, on a technical note, the changes you made to populate_with_dict() do not really address a bug of automx2, but a quirk of either the particular MySQL driver you used with SQLAlchemy, or possibly of SQLAlchemy itself. Interesting.

sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(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`))')

I may have to investigate more.

And SQLite does not enforce foreign key constraints.

[HLFH]

Some discussion: sqlalchemy/sqlalchemy#6766
Some regression: sqlalchemy/sqlalchemy#4849
Some regression fix: sqlalchemy/sqlalchemy#5196

Backref is used in model.py and is a legacy behaviour since July 2022.

Current version used: 1.4.43 ; three pre-releases after that 2.0.0b1-b3
This version has the regression fix.
But with the previous behaviour, I still get HTTP 500 Internal Server Error.

[HLFH]

For sqlalchemy:

• Integrity Error:

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails.
This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

• Part of DBAPI Errors:

The Python database API, or DBAPI, is a specification for database drivers which can be located at Pep-249. This API specifies a set of exception classes that accommodate the full range of failure modes of the database.
SQLAlchemy does not generate these exceptions directly. Instead, they are intercepted from the database driver and wrapped by the SQLAlchemy-provided exception DBAPIError, however the messaging within the exception is generated by the driver, not SQLAlchemy.

The discussion gives a better hint: sqlalchemy/sqlalchemy#6766

Stacktrace:

nov. 10 17:26:50 arch-server python3.9[3868040]: File "/srv/http/automua/.venv/lib/python3.9/site-packages/automua/views/initdb.py", line 60, in post
nov. 10 17:26:50 arch-server python3.9[3868040]: return self.init_db(request.json)
nov. 10 17:26:50 arch-server python3.9[3868040]: File "/srv/http/automua/.venv/lib/python3.9/site-packages/automua/views/initdb.py", line 45, in init_db
nov. 10 17:26:50 arch-server python3.9[3868040]: populate_db(data)
nov. 10 17:26:50 arch-server python3.9[3868040]: File "/srv/http/automua/.venv/lib/python3.9/site-packages/automua/database.py", line 171, in populate_db
nov. 10 17:26:50 arch-server python3.9[3868040]: populate_with_dict(data_source)
nov. 10 17:26:50 arch-server python3.9[3868040]: File "/srv/http/automua/.venv/lib/python3.9/site-packages/automua/database.py", line 142, in populate_with_dict

[HLFH]

Tasks in model.py:

• Replace backref by back_populates
• Add explicit relationship()
• Add backref arguments in the child
• Keep Many to Many secondary argument in both parents and children

In database.py:

• Restore more or less previous commit: rseichter/automx2@02801c4

[HLFH]

Partially fixed by c02b559

[HLFH]

After mysql -uroot -p, to display the LATEST FOREIGN KEY ERROR:

SHOW ENGINE INNODB STATUS;

LATEST FOREIGN KEY ERROR
------------------------
2022-11-10 23:03:42 0x7fd0d83fe6c0 Transaction:
TRANSACTION 293635536, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
MariaDB thread id 210292, OS thread handle 140534957991616, query id 6928753 localhost automua Update
INSERT INTO domain (id, name, provider_id, ldapserver_id) VALUES (0, 'example.com', 0, NULL)
Foreign key constraint fails for table `automua`.`domain`:
,
  CONSTRAINT `domain_ibfk_1` FOREIGN KEY (`provider_id`) REFERENCES `provider` (`id`) in parent table, in index provider_id tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000000; asc     ;;
 1: len 4; hex 80000bcc; asc     ;;

But in parent table `automua`.`provider`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 800003f4; asc     ;;
 1: len 6; hex 0000118085d0; asc       ;;
 2: len 7; hex 87000001ab23e9; asc      # ;;
 3: len 8; hex 486967686c656166; asc Highleaf;;
 4: len 8; hex 486967686c656166; asc Highleaf;;

In model.py:

provider_id = db.Column(db.Integer, db.ForeignKey('provider.id'), nullable=False)

[HLFH]

The Query API is legacy.

• query.count() should be replaced by the new sqlalchemy 2.0 ORM usage.
  Fixed by 1246807.

Workaround restored: 53912b1
Until testing suite is configured for MySQL/MariaDB setup.

• Revert workaround and try out again

• query.filter_by from the Query API is therefore legacy and may request replacement.