[Bug 1946259] Re: CommandNotFound database foreign key constraint fail

[Ubuntu Foundations Team Bug Bot]

The attachment "db_create_sql_bug.patch" seems to be a patch.  If it
isn't, please remove the "patch" flag from the attachment, remove the
"patch" tag, and if you are a member of the ~ubuntu-reviewers,
unsubscribe the team.

[This is an automated message performed by a Launchpad user owned by
~brian-murray, for any issues please contact him.]

** Tags added: patch

-- 
You received this bug notification because you are a member of Ubuntu
Foundations Bugs, which is subscribed to command-not-found in Ubuntu.
https://bugs.launchpad.net/bugs/1946259

Title:
  CommandNotFound database foreign key constraint fail

Status in command-not-found package in Ubuntu:
  New

Bug description:
  ```
  $ 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

To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/command-not-found/+bug/1946259/+subscriptions