[Bug 1946259] [NEW] CommandNotFound database foreign key constraint fail

Libor Martinek 1946259 at bugs.launchpad.net
Wed Oct 6 20:33:49 UTC 2021 

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
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

More information about the foundations-bugs mailing list