After Updating to SQLITE problems changing settings.

[MRC-Karel]

Checklist

• Have you pulled and found the error with jc21/nginx-proxy-manager:latest docker image?
  • Yes
• Are you sure you're not using someone else's docker image?
  • Yes
• Have you searched for similar issues (both open and closed)?
  • Yes

Describe the bug Updated from the mariadb to sqlite using the provided tutorial. All looks ok, but when trying to add an entry it says "internal error". Looks like an issue with SQLITE: SQLITE_CONSTRAINT: NOT NULL constraint failed: proxy_host.id

Nginx Proxy Manager Version v2.9.12

** Docker Compose

version: '3'
services:
  app:
    image: 'jc21/nginx-proxy-manager:latest'
    ports:
      - '80:80'
      - '81:81'
      - '443:443'
      - '5900:5900'
    environment:
      - DB_SQLITE_FILE=/data/database.sqlite
    volumes:
      - ./data:/data
      - ./letsencrypt:/etc/letsencrypt
    restart: unless-stopped

*** Logs https://pastebin.com/zeAbPriY

[PlasmatikSteak]

Could you please link to the tutorial you used when you updated from MariaDB to SQLite?

[MRC-Karel]

I used this one. https://github.com/jc21/nginx-proxy-manager/discussions/1529

What I remember, is using dbreaver export the db, convert to sqlite file and use that in the dockder-compose file.

In the end I used a fresh instance, and rentered everything manually.

[sadrian80]

I'm encountering the same issue. I deployed a fresh instance, stopped it, migrated the MariaDB to SQLITE via the tutorial posted above. Then I copied the data and ssl volumes over. Everything is working fine but I can't make any changes as they will result in an SQLITE error:

{… data to be written …} SQLITE_CONSTRAINT: NOT NULL constraint failed: proxy_host.id

database.sqlite has permissions 644. Location unaltered. I used the default compose.yml and only altered the volumes to persistent ones and added my network.

== EDIT == I compared the structure of a fresh SQLITE database against the migrated one. They are different: https://capture.dropbox.com/JSU4fd8VVRWRJHfX

== SOLUTION == The issue comes from the different table properties after migration. I verfied it by altering the table «proxy_host». Once verfied I did the following:

1. Deploy fresh instance
2. Get the database.sqlite from the fresh instance
3. Migrate via DBeaver, either from MariaDB or SQLite
4. Don't choose "Truncate target tables" (leave unchecked!)
5. Migrate and put back the SQLite database
6. Restart Nginx container

The SQLite database from a freshly deployed instance has all tables setup as they should but no entries, so there's no need to truncate (wipe clean) the target DB. If you do, all tables will be created with the settings from origin, and there seems to be the issue.

[github-actions[bot]]

Issue is now considered stale. If you want to keep it open, please comment :+1: