Public bug reported: ``` $ lsb_release -rd Description: Ubuntu 20.04.3 LTS Release: 20.04 ```
I have compiled sqlite3 with enabled foreign keys support - https://sqlite.org/foreignkeys.html and replaced the system provided sqlite3 shared libraries with my own (maybe unwise, but that's for another discussion :)) When I wanted to fetch latest `apt` changes, I got the following error: $ sudo apt update Hit:1 https://aquasecurity.github.io/trivy-repo/deb focal InRelease ... Traceback (most recent call last): File "/usr/lib/cnf-update-db", line 26, in <module> col.create(db) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 104, in create raise e File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 94, in create self._fill_commands(con) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 138, in _fill_commands self._parse_single_commands_file(con, fp) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 225, in _parse_single_commands_file self._insert_command(con, command, pkg_id) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 167, in _insert_command con.execute(""" sqlite3.OperationalError: no such table: main.pkgs ``` which is clearly an incorrect naming in the `create_table_sql` in `CommandNotFound/db/creator.py`, but since sqlite3 does not enforce foreign keys, you can get away with all sorts of funky stuff: ``` sqlite> PRAGMA foreign_keys=OFF; sqlite> CREATE TABLE tbl1(a, b NOT NULL, FOREIGN KEY (b) REFERENCES "nonexistent" (b)); sqlite> sqlite> INSERT INTO tbl1 VALUES (1, 2); -- No problem! ``` The first patch attempted was therefore: ``` $ git diff diff --git a/CommandNotFound/db/creator.py b/CommandNotFound/db/creator.py index d887c9d..a52be56 100755 --- a/CommandNotFound/db/creator.py +++ b/CommandNotFound/db/creator.py @@ -34,7 +34,7 @@ create_db_sql=""" [cmdID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [pkgID] INTEGER NOT NULL, [command] TEXT, - FOREIGN KEY ([pkgID]) REFERENCES "pkgs" ([pkgID]) + FOREIGN KEY ([pkgID]) REFERENCES "packages" ([pkgID]) ); CREATE TABLE IF NOT EXISTS "packages" ( ``` which resulted in another error in `$ sudo apt update`: ``` Traceback (most recent call last): File "/usr/lib/cnf-update-db", line 26, in <module> col.create(db) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 94, in create self._fill_commands(con) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 138, in _fill_commands self._parse_single_commands_file(con, fp) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 210, in _parse_single_commands_file self._delete_pkgid(con, already_in_db[0]) File "/usr/lib/python3/dist-packages/CommandNotFound/db/creator.py", line 152, in _delete_pkgid con.execute("DELETE FROM packages WHERE pkgID=?", (pkgid,) ) sqlite3.IntegrityError: FOREIGN KEY constraint failed ``` which is understandable, since default as per sqlite docs for `FOREIGN KEY` is `NO ACTION`. Final patch is thus adding `ON UPDATE CASCADE ON UPDATE DELETE`: ``` $ git diff diff --git a/CommandNotFound/db/creator.py b/CommandNotFound/db/creator.py index d887c9d..a52be56 100755 --- a/CommandNotFound/db/creator.py +++ b/CommandNotFound/db/creator.py @@ -34,7 +34,7 @@ create_db_sql=""" [cmdID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [pkgID] INTEGER NOT NULL, [command] TEXT, - FOREIGN KEY ([pkgID]) REFERENCES "pkgs" ([pkgID]) + FOREIGN KEY ([pkgID]) REFERENCES "packages" ([pkgID]) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS "packages" ( ``` Now, as I would expect, `apt update` works as usual: ``` $ sudo apt update ... Reading package lists... Done Building dependency tree Reading state information... Done 17 packages can be upgraded. Run 'apt list --upgradable' to see them. ``` Best regards, Libor $ sudo apt update ... Reading package lists... Done Building dependency tree Reading state information... Done 17 packages can be upgraded. Run 'apt list --upgradable' to see them. ``` Best regards, Libor ** Affects: command-not-found (Ubuntu) Importance: Undecided Status: New ** Patch added: "db_create_sql_bug.patch" https://bugs.launchpad.net/bugs/1946259/+attachment/5531128/+files/db_create_sql_bug.patch -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1946259 Title: CommandNotFound database foreign key constraint fail To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/command-not-found/+bug/1946259/+subscriptions -- ubuntu-bugs mailing list [email protected] https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
